索引的形式
索引是以一定数据格式存储数据的查找路径的数据形式。
这里举个例子:
最典型的就是图书馆找一本书,我们可以通过 “文学” -> “当代” -> “散文” 来缩小我们要找某一本书的范围。那么在这里书本的大分类、时代、小分类,就可以作为找书本的索引。
索引的开销
索引很消耗资源,不添加不必要的索引。这里是因为,索引是以树结构来存储的,为了加快检索的效率,减少查询的次数,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
來源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。