MySQL索引优化指南_索引列使用函数怎么优化-CSDN博客

本文链接：https://blog.csdn.net/qq_44039966/article/details/105360678

索引对于MySQL来说是一个非常重要的东西，在特定情况下能有效地提升MySQL的查询效率，但如果使用不当则会降低效率。所以我在这里记录一下这几天学习的MySQL索引优化的知识

使用索引有几个需要非常注意的点

1、全值匹配
2、最佳左前缀法则
3、不在索引列上做任何操作（计算、函数（自动或手动）类型转换）
4、存储引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询，也就是索引列和查询列一致），减少select * 的使用
6、MySQL在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描（存在争议，是否正确？详细请往下看）
7、is null、is not null 无法使用索引（存在争议，是否正确？详细请往下看）
8、使用 like 时以通配符开头（‘%abc’）会导致索引失效变成全表扫描的操作
9、字符串不加单引号会导致索引失效
10、少用 or，使用 or 连接时会导致索引失效

接下来就一个个详细说明

一、全值匹配

全值匹配没什么好说的，和正常的条件查找相似
在这里插入图片描述

// 创建索引
create index idx_name_age_deptId on emp(name,age,deptId)

在这里插入图片描述
由于我们的索引为name,age,deptId，而我们的查询条件也包含索引且是固定值，所以可以成功使用索引。

重点：因为我们建立的索引顺序为name,age,deptId，如果条件不按顺序会怎么样？
在这里插入图片描述
还是可以正常使用索引！！
原因：MySQL中的优化器会优化我们where之后的sql语句

在这个例子中会将我们的 WHERE deptId=2 AND emp.name=‘abc’ AND age=30
优化为 WHERE emp.name=‘abc’ AND age=30 AND deptId=2
使得sql语句中的条件顺序符合索引顺序，通过使用索引提高查询效率

二、最佳左前缀法则

在这里插入图片描述按全值匹配来看，这三条查询语句是完全没有问题的，但 第一个查询语句并没有用到索引，而 第三句只用到了索引中的name
这是因为查询字段与索引字段的个数不同的前提下，顺序不同，导致索引无法充分使用，甚至索引失效

使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，需要遵循最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

三、不在索引列上做任何操作（计算、函数（自动或手动）类型转换）

如果违背了这个原则，会导致索引失效而转向全表扫描

1）在查询列上使用了函数
在这里插入图片描述可以看到在查询列上使用了函数之后，索引失效，变为了全表扫描

结论：等号左边不能有计算

2）在查询列上做了转换
在这里插入图片描述原本name是varchar类型的，但如果我们查询时使用3000而不是使用’3000’，MySQL会自动帮我们在name列上做一次转换，使索引失效，变为全表扫描

结论：等号右边不能有类型（自动或手动）转换

四、存储引擎不能使用索引中范围条件右边的列

在这里插入图片描述可以看到原本查询类型是ref，但条件变成范围后查询变成了range级别，导致查询效率降低

结论：在设置索引时，将可能做范围查询的字段索引顺序放到最后

五、尽量使用覆盖索引（只访问索引的查询，也就是索引列和查询列一致），减少select * 的使用

即 查询列和索引列相同，不要写select *
在这里插入图片描述
可以看到Extra中出现了Using index，说明我们的sql得到了优化

六、MySQL在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描

在这里插入图片描述 这里存在一个争议，就是关于!=使索引失效对不对的问题，详细请看第七点

七、关于is null、is not null 使用索引

字段允许为null时：
在这里插入图片描述
但这样是否就说明了is not null用不到索引，is null可以用到索引呢？（!=也有相同的问题）

答案并不是，MySQL中决定使不使用某个索引执行查询的依据就是成本够不够小，如果null值很多，还是会用到索引的，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询

如果在索引列上改条件为 Is Not Null ，因为索引列的所有非空值都存储在索引中，按道理也是可以走索引的。但是，为了解析查询语句，优化程序需要从索引中读取每一个值，在映射到表中索引返回的行。
在大多数情况下，执行全表扫描比为索引返回的所有值执行索引扫描（相关的Table Access By Index Rowid操作）效率更高

有一点非常重要，通过观察SQL语句而推断执行计划是很不现实的，需要综合考察SQL语句所涉及表的索引、数据分布、统计信息，才能综合判断，用通俗的话来说要结合具体场景

结论：如果谓词上面建立有索引的话，基本上都会走索引，至于是走索引查找还是索引扫描与索引类型有一定关系，也与字段位于联合索引中位置有关系。另外，数据分布倾斜得非常厉害也会导致其走全表扫描而不走索引，但是这并不是说IS NULL 和 IS NOT NULL导致索引失效

附上两篇参考博客
MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗？
SQL SERVER 中is null 和 is not null 将会导致索引失效吗？

八、使用 like 时以通配符开头（‘%abc’）会导致索引失效变成全表扫描的操作

在这里插入图片描述

结论：前缀不能出现模糊匹配

九、字符串不加单引号会导致索引失效

虽然说前面提到过了，但这一条非常重要，很有可能在你没有意识到的情况下拖慢你程序的速度，所以再拿出来提一嘴

详细见第三条

十、少用 or，使用 or 连接时会导致索引失效

在这里插入图片描述

结论：可以使用 union all 或者 union 来代替 or

小结

往下看之前先记住一句话：分组之前必排序

假设index(a,b,c,d)

Where语句	索引是否被使用
where a=3	使用到a
where a=3 and b=5	使用到a、b
where a=3 and b=5 and c=6	使用到a、b、c
where a=3 or b=5 or c=6	索引不被使用
where a=3 and c=6	使用到a，但c没被使用，因为没有b，中间断了
where c=6 and a=3 and b=5	使用到a、b、c
where a=3 and b>5 and c=6	使用到a和b，c不能用在范围之后
where a=3 and c>5 and b=5	使用到a、b、c
where abs(a)=3	索引不被使用
where a=3 and b=5 and d=7 order by c	查找索引只用到了a、b，索引c被用于排序
where a=3 and b=5 order by c	（与上一条类比，与d无关）
where a=3 and b=5 order by d	查找索引只用到了a、b，并且Extra中出现了Using filesort
where a=3 and d=7 order by b,c	查找索引只用到了a，索引b、c被用于排序，无filesort
where a=3 and d=7 order by c,b	（类比上一条）出现Using filesort（MySQL不会order by语句顺序）
where a=3 and b=5 and d=7 order by c,b	（类比上一条）无filesort（此时b=5，在order by中b可以当成一个常量）
where a=3 and d=7 group by b,c	只使用了a，无filesort
where a=3 and d=7 group by c,b	（类比上一条）只使用了a，出现filesort和temporary
where a=3 and b like ‘kk%’ and c=4	使用到a、b、c
where a=3 and b like ‘%kk’ and c=4	（类比上一条）只使用到a
where a=3 and b like %‘kk%’ and c=4	（类比上一条）只使用到a
where a=3 and b like ‘k%kk%’ and c=4	（类比上一条）使用到a、b、c