MySQL索引常见问题

文章目录

问题1：用联合索引优化using filesort

首先，要给userid加索引（一般过滤条件的字段需要加索引），此时创建的是二级索引树，​​select *​​涉及回表。而且还有order by addtime，如果addtime没有建索引的话，explain分析时会出现using filesort，涉及addtime的文件排序！

我们可以添加联合索引（多列索引）：userid+addtime

这样的话，在二级索引树上用userid过滤出来的数据中，addtime就已经是有序的了

addtime不建立索引，就有using filesort，数据的外排序。用userid选择出来的数据越多，耗费的排序性能差的越大

addTime有索引，那索引树上的数据就是按照addTime排好序的

问题2：给区分度高的字段加索引

比如我们给sex建立索引，而sex就两种，所以用sex过滤出来的数据可能会占到整张表的一大部分，可能不会用到索引

问题3：select的字段会导致回表

如果不需要那么多的字段，就不要select太多，这会导致回表，影响效率。能直接在二级索引树上取的就直接取，避免回表！

问题4：多个字段有索引，使用哪个索引？

一张表的一次查询只能用到1个索引，数据库引擎会比较用a=1过滤的数据和b=2过滤的数据，哪个索引过滤出来的数据少，就用哪个索引

问题5：内连接之大表和小表

内连接的时候，小表是整表扫描，每一个都要用到，大表相关联的字段没有索引的话，小表的每一个uid在大表中搜索都是整表搜索。如果大表相关联的字段有索引，小表的每一个uid在大表中就是走索引，搜的非常快。

确定大表还是小表：先用where进行数据过滤，谁的数据少谁就是小表

问题6：模糊搜索使用索引

第一个​​zhang%​​通配符在后面，可以利用索引进行前缀搜索，可以根据前缀的匹配缩小搜索范围。对于字符串类型来说，索引长度（key_len）不需要很长，只需要能区分每个字符串数据即可，索引过长，导致索引文件变大，创建索引树的时候花费的磁盘I/O也就多了

第二个​​%zhang%​​通配符在前面，可以匹配任意的数据，所以只能整张表搜索

问题7：in和not in是否能用到索引？not in优化成range范围搜索

实际上，MySQL做了如下优化：

select age from student where age not in (20);
select age from student where age < 20 or age > 20;  -- 优化成了range范围搜索

not in 可以转化为范围range搜索查询

问题8：索引字段涉及函数以及类型转换，无法用到索引

本来是按照password排序作为索引的，经过函数计算之后就成为了另外的值，所以无法使用索引

索引字段涉及类型强转、mysql聚合函数调用、表达式计算等，不会使用索引

问题9：or不能使用索引？

or 实际上也可以用到索引，MySQL会对or进行优化成union联合查询

优化成union后的两个select应该是都可以使用索引的，究竟用不用，还得看使用索引是否能带来效率的提升

总结