1、慢SQL语句的几种常见原因
1)、无索引
2)、索引失效
索引会失效原因:未遵循左前缀原则;在索引列上做操作比如计算、函数、自动或者手动类型转化;范围检索也会让索引失效比如联合索引 (a,b,c) 在sql查询的时候 where a = x and b > y and c = z 这样会导致索引c失效,原因是索引在b处已经开始排序了;使用!= or <> 无法使用索引; is null 和 is not null 也会无法使用索引,为了避免这种情况为数据库字段赋值默认值是个好习惯;like "%abc" 会走全表扫描;字符串需要加索引,否则会索引失效;少用or,or条件在数据库上很难优化,能在代码里优化的逻辑,不至于拖垮数据库。
3)、锁等待
InnoDB存储引擎在批量更新操作的时候,行锁就可能转化升级为表锁。行锁是基于索引加的,如果在更新操作时,条件索引失效,行锁会升级为表锁;另外Mysql认为如果对一张表使用大量行锁,会导致事务效率下降,可能导致其他事务长时间锁等待或者更多的锁冲突,导致性能下降,所以会锁升级。
4)、使用了不恰当的SQL语句同样会导致SQL变慢 例如 count(*) selecy * 。
2、SQL优步骤
1、explain 分析SQL执行计划
explain 停留在分析Sql外部执行情况,深入到mysql内核中,比如考虑执行线程和执行时间来分析考虑 show profile
2、show profiles 分析SQL执行性能
查看MySQL是否支持 show profile select @@have_profiling
show profiles 只显示最近发给服务器的sql语句默认最近执行的15条记录,可以设置profiling_history_size 增大改存储记录 最大值100。
5、优化分页
分析如下sql
select * from `demo`.`user` order by order_no limit 10000, 10; 耗时0.57s。
该sql使用了索引,扫码行数为10010行,时间偏长。可以考虑利用子查询来优化。以上分页查询的问题在于,查询10010行数据都返回了,是否可以只查询所需要的10条数据中最小id值,然后通过偏移量返回所需要的10行数据。优化后的sql如下
SELECT * FROM `demo`.`user`
where id >= (
select id from `demo`.`user` order by created_at limit 10000, 1
) limit 10;
执行时间为 0.068s。虽然子查询遍历索引的范围跟上一个查询差不多,而主查询扫描了更多的行。但是执行效率缺有很高提升,原因就是返回行数变少了。
6、优化count(*)
使用近似值:业务场景不需要返回一个精确count值的时候,可以通过explain对表进行估算,执行explain并不会真正执行查询,而是返回一个近似值。
使用统计表或者缓存字段:提供精确值。
7、优化or
使用union来替换or操作,其中union的操作会去重,union all 不会去重,会把所有结果全部查询出来。