得益于摩尔定律，计算机性能已大幅提升，加上数据库的进步以及微服务所倡导的各种反模式设计。因此，我们现在编写复杂SQL查询的机会越来越少。业界(是的，甚至包括谷歌)已经开始提倡不要进行专门的SQL优化，因为节省下来的资源并不足以抵消员工的工资成本。但是，作为工程师，我们应该在技术上努力追求卓越，成为本领域的顶尖科学家。
在这里，将介绍7个常见的SQL慢查询语句，并解释如何优化它们的性能。希望这对你有所帮助。
由DALLE-3生成
分页是最常用的方案之一，但也容易出现问题。例如，对于以下简单的语句，DBA通常建议的解决方案是添加一个包含type、name和create_time字段的复合索引。这样，条件和排序就可以有效利用索引，从而显著提高性能。
SELECT *FROM operationWHERE type = 'SQLStats' AND name = 'SlowLog'ORDER BY create_timeLIMIT 1000, 10;这可能会解决90%以上DBA的问题。但是，当LIMIT子句变成“LIMIT 1000000, 10”时，程序员仍会抱怨“为什么在只查询10条记录的时候，速度还这么慢?” 要知道，数据库不知道第1000000条记录从何处开始，所以即使有索引，它仍需要从头开始计算。在大多数情况下，这个性能问题是由于懒惰编程造成的。
在前端数据浏览或批量导出大量数据的场景中，可以使用上一页的最大值作为查询参数。SQL可以重新设计如下:
SELECT *FROM operationWHERE type = 'SQLStats'AND name = 'SlowLog'AND create_time > '2017-03-16 14:00:00'ORDER BY create_timeLIMIT 10;采用这种新设计后，查询时间保持不变，不会随着数据量的增加而变化。
SQL语句中另一个常见的错误是查询变量和字段定义的类型不匹配。以下面的语句为例：
mysql> explain extended SELECT * > FROM my_balance b > WHERE b.bpn = 14000000123 > AND b.isverified IS NULL ;mysql> show warnings;| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'在这种情况下，字段bpn被定义为varchar(20)，而MySQL的策略是在比较之前将字符串转换为数字。这会导致函数被应用到表字段上，从而使索引失效。
这种情况可能是由应用程序框架自动填充参数造成的，而不是程序员的本意。如今，应用程序框架通常都很复杂，虽然它们提供了便利，但也可能带来隐患。
尽管MySQL 5.6引入了物化，但它只优化了SELECT语句。对于UPDATE或DELETE语句，需要使用JOIN手动重写。
例如，请看下面的UPDATE语句。MySQL实际上执行了一个循环/嵌套子查询(DEPENDENT SUBQUERY)，执行时间可想而知。
UPDATE operation oSET status = 'applying'WHERE o.id IN (SELECT id FROM (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ( 'done' ) ORDER BY o.parent, o.id LIMIT 1) t);执行计划如下:
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+| 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | Using where; Using temporary || 2 | DEPENDENT SUBQUERY | | | | | | | | Impossible WHERE noticed after reading const tables || 3 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+将其重写为JOIN后，子查询的选择类型从DEPENDENT SUBQUERY变为DERIVED，执行时间显著得从7秒缩短到2毫秒。
UPDATE operation o JOIN (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ( 'done' ) ORDER BY o.parent, o.id LIMIT 1) t ON o.id = t.idSET status = 'applying';简化后的执行计划如下:
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+| 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables || 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+MySQL无法利用索引进行混合排序。但是，在某些场景下，仍然可以使用特殊方法来提高性能。
SELECT *FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC, a.appraise_time DESCLIMIT 0, 20;执行计划显示的是全表扫描:
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort || 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 122 | a.orderid | 1 | NULL |+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+由于is_reply只有0和1两种状态，我们可以将其重写如下，从而将执行时间从1.58秒缩短到2毫秒:
SELECT *FROM ((SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0 ORDER BY appraise_time DESC LIMIT 0, 20) UNION ALL (SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 1 ORDER BY appraise_time DESC LIMIT 0, 20)) tORDER BY is_reply ASC, appraisetime DESCLIMIT 20;在处理EXISTS子句时，MySQL仍然使用嵌套子查询进行执行。以下面的SQL语句为例:
SELECT *FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'WHERE n.topic_status < 4 AND EXISTS(SELECT 1 FROM message_info m WHERE n.id = m.neighbor_id AND m.inuser = 'xxx') AND n.topic_type <> 5;+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+| 1 | PRIMARY | n | ALL | | NULL | NULL | NULL | 1086041 | Using where || 1 | PRIMARY | sra | ref | | idx_user_id | 123 | const | 1 | Using where || 2 | DEPENDENT SUBQUERY | m | ref | | idx_message_info | 122 | const | 1 | Using index condition; Using where |+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+通过删除EXISTS子句并将其更改为JOIN, 我们可以避免嵌套子查询，并将执行时间从1.93秒减少到1毫秒。
SELECT *FROM my_neighbor n INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx' LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'WHERE n.topic_status < 4 AND n.topic_type <> 5;新的执行计划如下:
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition || 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where || 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+在某些情况下，外部查询条件无法下推到复杂的视图或子查询中:
请看下面的语句，其中的条件会影响聚合子查询：
SELECT *FROM (SELECT target, Count(*) FROM operation GROUP BY target) tWHERE target = 'rm-xxxx';+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| 1 | PRIMARY | n | ALL | NULL | NULL | NULL | NULL | 1086041 | Using where || 1 | PRIMARY | sra | ref | NULL | idx_user_id | 123 | const | 1 | Using where || 2 | DEPENDENT SUBQUERY | m | ref | NULL | idx_message_info | 122 | const | 1 | Using index condition; Using where |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+通过删除EXISTS子句并将其更改为JOIN，我们可以避免嵌套子查询并将执行时间从1.93秒减少到1毫秒。
SELECT *FROM my_neighbor n INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx' LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'WHERE n.topic_status < 4 AND n.topic_type <> 5;新的执行计划如下:
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition || 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where || 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+让我们看看以下经过部分优化的示例（左连接中的主表作为主查询条件）：
SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources GROUP BY resourcesid) cON a.resourceid = c.resourcesid;这条语句是否还存在其他问题？很明显，子查询c是对整个表进行聚合查询，在处理大量表时可能会导致性能下降。
事实上，对于子查询c，左连接的结果集只关心可以与主表的resourceid匹配的数据。因此，我们可以将语句重写如下，将执行时间从2秒减少到2毫秒：
SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) cON a.resourceid = c.resourcesid;然而，子查询a在我们的SQL语句中出现了多次。这种方法不仅会产生额外的成本，而且也会使语句变得更加复杂。我们可以使用WITH语句来简化它：
WITH a AS( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20)SELECT a.*, c.allocatedFROM aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) cON a.resourceid = c.resourcesid;数据库编译器生成的执行计划决定了SQL语句的实际执行方式。但是，编译器只能尽力提供服务，没有一个数据库编译器是完美的。上述情况在其他数据库中也同样存在。了解了数据库编译器的特性，我们就能绕过它的限制，编写出高性能的SQL语句。
在设计数据模型和编写SQL语句时，将算法思维或算法意识引入到这个过程非常重要。在编写复杂的SQL语句时，养成使用WITH语句的习惯可以简化语句，减轻数据库的负担。
最后，下面是SQL语句的执行顺序：
FROMON JOINWHEREGROUP BYHAVINGSELECTDISTINCTORDER BYLIMIT本文链接：http://www.28at.com/showinfo-26-90655-0.html七个常见的SQL慢查询问题，及其解决方法
声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。邮件：2376512515@qq.com
下一篇： 水下数据中心的杀手：声波攻击
Copyright © 2016-2023 天津谷骐科技有限公司 版权所有 sitemap.xml
违法及侵权请联系：2376512515@qq.com 津ICP备18001702号
津公网安备 12010102000574号