mysql 覆盖索引 简书_mysql 索引使用

索引的形式

索引是以一定数据格式存储数据的查找路径的数据形式。

这里举个例子：

最典型的就是图书馆找一本书，我们可以通过 “文学” -> “当代” -> “散文” 来缩小我们要找某一本书的范围。那么在这里书本的大分类、时代、小分类，就可以作为找书本的索引。

索引的开销

索引很消耗资源，不添加不必要的索引。这里是因为，索引是以树结构来存储的，为了加快检索的效率，减少查询的次数，mysql需要保证这个树的平衡，而这需要花销比较多的资源和时间。

对于写：索引的更新往往是最主要的开销。那么，对于写异常频繁的业务，我们可以考虑删除索引这种非常规的手段。

对于读：索引会占用内存跟硬盘空间。占用内存和硬盘的同时，也加快了检索的速度，典型的以空间换时间的做法。

字符串索引

1.本质上没差别，“AAAA” < “AAAB”(collation)

2.LIKE是特殊的范围查询

3.LIKE “ABC%” 等同于“ABC[LOWEST]”

4.LIKE “%ABC”无法使用索引

Innodb索引

1.数据按主键聚集。也就是说，根据主键可以直接获取数据。

2.主键隐藏添加在所有索引的后面，也就是说 KEY(A)等同于KEY(A,ID)

数据查找

下面语句可以使用索引 (LAST_NAME)

SELECT * FROM EMPLOYEES WHERE　LAST_NAME=“Smith”

下面语句可使用索引 (DEPT,LAST_NAME)

SELECT * FROM EMPLOYEES WHERE LAST_NAME=“Smith” AND DEPT=“Accounting”

索引含有多个字段

考虑有索引 (A,B,C)，请留意字段的顺序

以下情况能够使用索引

A>5 //用到了(A)

A=5 AND B>6 //用到了(A,B)

A=5 AND B=6 AND C=7 //用到了(A,B,C)

A=5 AND B IN (2,3) AND C>5 //用到了(A,B,C)

以下情况不够使用索引

B>5

B=6 AND C=7

以下情况只能使用到索引的部分字段

//只能用上index(A)

A>5 AND B=2

//只能用上index(A,B)

A=5 AND B>6 AND C=2

多字段索引结论

1.索引的匹配规则是左匹配的

2.有了(A,B,C)，就等于同时拥有了(A)和(A,B)

3.只要索引内，开始用范围查询，后面的索引就失效了。

这里注意：

IN 在 where 中，也属于准确查询，不会使后面索引失效。

索引用于排序

下面语句可以使用索引 (SCORE)

SELECT * FROM PLAYERS ORDER BY SCORE DESC LIMIT 10

下面语句可使用索引 (COUNTRY, SCORE)

SELECT * FROM PLAYERS WHERE COUNTRY=“US” ORDER BY SCORE DESC LIMIT 10

多字段索引与排序

考虑有索引(A, B)，留意字段顺序

以下情况能够使用索引

ORDER BY A

A=5 ORDER BY B

ORDER BY A DESC, B DESC

A>5 ORDER BY A

以下情况不能使用索引用于排序

ORDER BY B //第二个字段上面排序

A>5 ORDER BY B //第一个字段是范围查询

A IN(1,2) ORDER BY B //第一个字段是IN范围查询

ORDER BY A ASC, B DESC //排序字段的顺序不一致

最左匹配

对于一个索引来说，里面的索引是有先后顺序的。

例如：索引(A,B,C)

系统建立索引时，mysql会这么做：

首先，会根据 A 的值，构建索引，

在相同 A 索引下，建立 B 的索引，

在 B 相同的索引下，建立 C 的单独索引。

再多的字段也是如此类推。

那么

索引组内，当前索引无法在前缀不确定的情况下使用当前索引。

这也就是下面这几个语句无法使用索引或无法使用完整索引的原因：

A LIKE '%aaa' //前缀不确定，无法使用索引

A>5 AND B=2 //前缀A不确定，只能用到 (A)，无法用(A,B)

A IN(1,2) ORDER BY B //只能用到 (A)，无法用(A,B)

那为什么“A=5 AND B IN (2,3) AND C>5” 可以用到(A,B,C)呢？

实际上，mysql优化器在这里做了优化，

查询的时候拆分了两次检查索引：

A=5 AND B=2 AND C>5

A=5 AND B=3 AND C>5

那么，在IN的范围不太大的情况下，可以在很大程度上优化查询速度。

MySQL是如何选择索引的？

1.每次查询动态选择

2.估算走索引需要查询的行数

3.根据“Cardinality”的状态。作为重要参考标准。大致原理是：索引重叠的程度，重叠程度越低，这个基数越大，优化器优先选取。

show index from table 执行结果

Cardinality - 图2

此图告诉我们:

使用索引查找速度对比为：customer_id > i_gid > company_id

索引策略

1.给最频繁的语句加索引 --要整体来看，而不是一条一条语句添加

2.尽可能扩展索引，而不是新增索引

3.WHERE条件跟JOIN都能用上索引是最好的，

4.新增索引后要验证索引是否生效，是否对能提升性能

例子：

SELECT * FROM TBL WHERE A=5 AND B=6

SELECT * FROM TBL WHERE A>5 AND B=6

应该新增索引(B,A)

优化技巧

技巧1：范围查询改为枚举类型

考虑索引(A,B)，A 类型为 int

//只能用到(A)索引

SELECT * FROM TBL WHERE A BETWEEN 2 AND 4 AND B=5

改为

//可以用到整个索引

SELECT * FROM TBL WHERE A IN (2,3,4) AND B=5

技巧2：增加假的条件

考虑索引KEY (GENDER,CITY)，GENDER性别为可枚举数据

//无法使用索引

SELECT * FROM PEOPLE WHERE CITY=“NEW YORK”

改为

//能用索引

SELECT * FROM PEOPLE WHERE GENDER IN (“M”,”F”) AND CITY=“NEW

对于Gender, Status, Boolean 类型非常有效

技巧3：Unionizing Filesort

考虑索引KEY(A,B)

//无法使用索引

SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5;

改为

//能使用索引用作排序，没有file_sort

(SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL

(SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;

Bug: order by limit

考虑索引KEY(A,B)，KEY(C)

select * from tb where a=1 and b =2 order by c limit 10

有时候不能正确的选择索引，

会指定使用索引(C)，

先排序后筛选，导致全面检索。

这时候需要指定索引或者改为

select * from tb where a=1 and b =2 order by c+0 limit 10

EXPLAIN

关于EXPLAIN语句，可以帮助我们去了解一条语句的执行效率和所用到的索引，由于太复杂，可以去自行看官方的文档。有空可以继续写一篇文章去帮助大家理解，也帮助自己总结。

作者：简公介

链接：https://www.jianshu.com/p/d08f16867012

來源：简书

简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。