MySQL--MySQL索引优化

傷城~ 2022-01-14 03:43 472阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

本博客是用来记录自己学习的过程

#### 1.独立的列 ####

进行查询的时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引

例如user\_id 为索引列，改索引不能生效

select user\_id from user where user\_id +1 =3;

#### 2.多列索引 ####

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能会更好.

select user\_id ,phone from user where user\_id = 1 and phone = 138xxxxxxx

#### 3.索引列的顺序 ####

让选择性强的索引列放在前面。索引选择性是指：不重复的索引值和记录总数的比值。最大值为1，此时每个记录都有唯一的索引和它对应。选择性越高，查询效率也越高。

例如，从显示结果来看customer\_id的值比company\_id的值大，因此最好把customer\_id 放在多列索引前面

SELECT COUNT(DISTINCT company\_id)/COUNT(*) AS company\_id\_selectivity, COUNT(DISTINCT customer\_id)/COUNT(*) AS customer\_id\_selectivity, COUNT(\*) from customer a

#### 4.前缀索引 ####

对于列的值比较长，比如BLOB，TEXT，VARCHAR就必须建立前缀索引，就是把值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做ORDER BY和 group BY，也无法使用前缀索引做覆盖扫描。

例如

alter table city add key(cityname(8))

#### 5.覆盖索引 ####

跟联合索引有点类似，就是在查询Table的时候只用去读取索引而取得数据，无需进行二次查询相关表，这样的索引的叶子节点上面也包含了他们索引的数据。（就是索引包含所有需要查询的字段的值）

判断标准：使用explain，可以通过输出的extra列来判断，对应一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。

具有的优点：

1.索引通常远小于数据行的大小，只读索引能大大减少数据访问量。

2.一些存储引擎（例如MyISAM）在内存中只缓存索引。而数据依赖于操作系统来缓存。因此，只访问索引可以不适用系统调用。

3.对于InnoDB引擎，若辅助索引能够覆盖查询，则无需访问主索引。

## 索引的优点 ##

*  大大减少了服务器需要扫描的数据行数
 *  帮助服务器避免进行排序和分组，以及避免创建临时表（B+ tree 的索引是有序的，可以用于ORDER BY 和 GROUP BY操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表）
 *  将随机啊I/O变成顺序I/O

## 索引的使用条件 ##

*  对于非常小的表，大部分情况下，简单的全表扫描比建立索引更加高效
 *  对于中到大型的表，索引就非常有效
 *  但是对于特大型的表，建立和维护索引的代价会随之增长，这种情况下用到的一种技术就是区分出需要查询的一组数据，而不是一条记录一条记录睇匹配。例如可以使用分区技术。

转载于:https://my.oschina.net/u/3294228/blog/3012909

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/d615d30d59aa4f53b545f6f6b6de498c.png