创建索引的原则
- 选择唯一性索引:唯一性索引的值是唯一的。可以更快的通过该索引来确定某条记录。
- 为经常需要排序、分组。以及联台查询的列创建索引。
- 为经常作为查询条件的列创建索引。
- 限制索引的数目:越多的索引越导致表的查询效率变低,更新表数据的时候都会重新创建这个表的索引,表的数据越多么创建索引的时间消耗就越大。
- 如果索引的值很长,那么查询的速度会受到影响。
- 如果索引字段的值很长.最好使用前缀来进行索引。
- 删除不再使用用或者很少使用的索引。
- 最左前缀匹配原则。
- 尽量选择区分度高的列作为索引.
- 索引列不能参与计算,保持列”干净”:带函数的查询不参与索引。
索引失效的场景
函数计算
select count(*) from tradelog where month(t_modified)=7;
如果对字段做了函数计算,就用不上索引了。对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
但是,并不是放弃使用这个索引,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引t_modified更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。
隐式类型转换
select * from tradelog where tradeid=110717;
tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。
隐式字符编码转换
utf8mb4与utf8是有差别的,utf8mb4是utf8的超集,不能直接用索引,也是需要作转换的。
Like
like以%开头索引无效,当like以&结尾,索引有效。
or
or语句前后没有同时使用索引,当且仅当or语句查询条件的前后列均为索引时,索引生效。
组合索引
使用的不是第一列索引时候,索引失效,即最左匹配规则。
NULL判断
在索引列上使用IS NULL或者IS NOT NULL时候,索引失效,因为索引是不索引空值的。
比较
在索引字段上使用,NOT、<>、!=、时候是不会使用索引的,对于这样的处理只会进行全表扫描。
速度判断
当全表扫描速度比索引速度快的时候不会使用索引。