如何解决SQL查询速度太慢?

如何解决SQL查询速度太慢?,第1张

1. 执行计划中明明有使用到索引,为什么执行还是这么慢?

2. 执行计划中显示扫描行数为 644,为什么 slow log 中显示 100 多万行?

a. 我们先看执行计划,选择的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。结合 sql 来看,因为有 "ORDER BY TASK_ID DESC" 子句,排序通常很慢,如果使用了文件排序性能会更差,优化器选择这个索引避免了排序。

那为什么不选 possible_keys:INDX_BIOM_ELOCK_TASK 呢?原因也很简单,TASK_DATE 字段区分度太低了,走这个索引需要扫描的行数很大,而且还要进行额外的排序,优化器综合判断代价更大,所以就不选这个索引了。不过如果我们强制选择这个索引(用 force index 语法),会看到 SQL 执行速度更快少于 10s,那是因为优化器基于代价的原则并不等价于执行速度的快慢;

b. 再看执行计划中的 type:index,"index" 代表 “全索引扫描”,其实和全表扫描差不多,只是扫描的时候是按照索引次序进行而不是行,主要优点就是避免了排序,但是开销仍然非常大。

Extra:Using where 也意味着扫描完索引后还需要回表进行筛选。一般来说,得保证 type 至少达到 range 级别,最好能达到 ref。

在第 2 点中提到的“慢日志记录Rows_examined: 1161559,看起来是全表扫描”,这里更正为“全索引扫描”,扫描行数确实等于表的行数;

c. 关于执行计划中:“rows:644”,其实这个只是估算值,并不准确,我们分析慢 SQL 时判断准确的扫描行数应该以 slow log 中的 Rows_examined 为准。

4. 优化建议:添加组合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

优化过程:

TASK_DATE 字段存在索引,但是选择度很低,优化器不会走这个索引,建议后续可以删除这个索引:

select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+

在这个 sql 中 REL_DEVID 字段从命名上看选择度较高,通过下面 sql 来检验确实如此:

select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+

由于有排序,所以得把 task_id 也加入到新建的索引中,REL_DEVID,task_id 组合选择度 100%:

select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+

在测试环境添加 REL_DEVID,TASK_ID 组合索引,测试 sql 性能:alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

添加索引后执行计划:

这里还要注意一点“隐式转换”:REL_DEVID 字段数据类型为 varchar,需要在 sql 中加引号:AND T.REL_DEVID = 000000025xxx >>AND T.REL_DEVID = '000000025xxx'

执行时间从 10s+ 降到 毫秒级别:

1 row in set (0.00 sec)

结论

一个典型的 order by 查询的优化,添加更合适的索引可以避免性能问题:执行计划使用索引并不意味着就能执行快。

没有索引或者没有用到索引。

这是查询慢最常见的问题,是程序设计的缺陷)2、I/O吞吐量小,形成了瓶颈效应。

查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)。


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/9652044.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-30
下一篇 2023-04-30

发表评论

登录后才能评论

评论列表(0条)

保存