为什么程序员对这8种SQL的写法零容忍-每日运维

来源：developer.aliyun.com/article/72501

# 前言

MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上，甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢，CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下，供大家参考。

# 1. LIMIT 语句

分页查询是常用的场景之一，但也通常也是容易出问题的地方。比如对于下面简单的语句，一般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_time limit 10;

在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。

# 2. 隐式转换

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的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。

上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。

# 3. 关联更新、删除

虽然MySQL5.6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成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 |+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

# 4. 混合排序

MySQL不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。

SELECT *FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT , 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 = ORDER BY appraise_time DESC LIMIT , 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 , 20)) tORDER BY is_reply ASC, appraisetime DESC LIMIT 20;

# 5. EXISTS语句

MySQL对待EXISTS子句时，仍然采用嵌套子查询的执行方式。如下面的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 |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+

# 6. 条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；
含有LIMIT的子查询；
UNION 或UNION ALL子查询；
输出字段中的子查询；

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：

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 | | ref | | | 514 | const | 2 | Using where || 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NULL | 20 | Using index |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+

确定从语义上查询条件可以直接下推后，重写如下：

SELECT target, Count(*)FROM operationWHERE target = 'rm-xxxx' GROUP BY target

执行计划变为：

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

关于MySQL外部条件不能下推的详细解释说明请参考以前文章：MySQL · 性能优化 · 条件下推到物化表

# 7. 提前缩小范围

先上初始SQL语句：

SELECT *FROM my_order o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pidWHERE ( o.display = ) AND ( o.ostaus = 1 )ORDER BY o.selltime DESC LIMIT , 15

该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，后一步估算排序记录数为90万，时间消耗为12秒。

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort || 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL || 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

由于后WHERE条件以及排序均针对左主表，因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。

SELECT *FROM (SELECT *FROM my_order oWHERE ( o.display = ) AND ( o.ostaus = 1 )ORDER BY o.selltime DESC LIMIT , 15) o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pidORDER BY o.selltime DESClimit , 15

再检查执行计划：子查询物化后（select_type=DERIVED)参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT 子句后，实际执行时间变得很小。

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort || 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL || 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) || 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

# 8. 中间结果集下推

再来看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：

那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。

其实对于子查询 c，左连接后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。

SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, ) * 12345) allocated FROM my_resources r, ( SELECT resourceid FROM my_distribute d WHERE isdelete = 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 = AND cusmanagercode = '1234567' ORDER BY salecode limit 20)SELECT a.*, c.allocatedFROM aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, ) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) cON a.resourceid = c.resourcesid

AliSQL即将推出WITH语法，敬请期待。

# 总结

数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。

程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。编写复杂SQL语句要养成使用WITH语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担 ^^。

使用云上数据库遇到难点（不局限于SQL问题），随时寻求阿里云原厂专家服务的帮助。

SQL数据库触发器语法详解 (sql数据库触发器语法)

2023-08-06
数据库
语法
触发器

快速简单的删除Oracle数据库字段方法 (删除oracl数据库字段)

2023-08-06
数据库
字段
删除

如何打开社工数据库bak文件 (社工数据库bak怎么打开)

2023-08-06
数据库
打开
社工

实现数据库按拼音排序的方法和技巧 (数据库按拼音排序)

2023-08-06
数据库
排序
按拼音

探究Sybase数据库的性能和功能特点 (sybase数据库怎么样)

2023-08-06
数据库
性能
探究

SQL Server 如何成功建立自己的数据库？ (sql server 建立数据库)

2023-08-06
数据库
自己的
建立

如何在Oracle中查看数据库触发器？ (oracle查看数据库触发器)

2023-08-06
数据库
查看
触发器

数据库表数据量千万级，对性能影响有多大？ (数据库表千万级数据量多吗)

2023-08-06
数据库
级数
有多大

如何使用Oracle按时间导出表数据库？ (oracle按时间导出表数据库)

2023-08-06
数据库
导出
如何使用

数据库存储：帖子长期保存，信息永不丢失 (帖子存数据库)

2023-08-06
数据库
丢失
帖子

小米六数据库：全方位数据保障和优化方案 (小米六数据库)

2023-08-05
数据库
优化
小米

简易教程：使用dbe数据库实现数据连接 (dbe数据库数据连接)

2023-08-05
数据
数据库
连接

Oracle实现多个数据库链接的简便方法 (oracle链接多个数据库)

2023-08-05
数据库
多个
链接

数据库索引：用哪种方法建立？ (数据库索引用什么建的)

2023-08-05
索引
数据库
哪种

实现高效缓存同步：Redis数据库技巧大全 (redis 数据库缓存同步)

2023-08-05
数据库
缓存
同步

如何利用数据库实现高效的模糊匹配查询？ (数据库实现模糊查询)

2023-08-05
查询
数据库
模糊

数据库有哪些安装方式和位置？ (数据库是装在什么上)

2023-08-05
数据库
位置
装在

Lactmed数据库：妈妈们必备的喂养指南 (lactmed 数据库)

2023-08-05
数据库
必备
喂养

数据库设计中的主属性定义及作用 (数据库主属性是什么)

2023-08-05
数据库
定义
属性

数据库分区操作不当常常造成分区不清的问题 (分区不清数据库)

2023-08-05
数据库
分区
不清

为什么程序员对这8种SQL的写法零容忍

SQL数据库触发器语法详解 (sql数据库触发器语法)

快速简单的删除Oracle数据库字段方法 (删除oracl数据库字段)

如何打开社工数据库bak文件 (社工数据库bak怎么打开)

实现数据库按拼音排序的方法和技巧 (数据库按拼音排序)

探究Sybase数据库的性能和功能特点 (sybase数据库怎么样)

SQL Server 如何成功建立自己的数据库？ (sql server 建立数据库)

如何在Oracle中查看数据库触发器？ (oracle查看数据库触发器)

数据库表数据量千万级，对性能影响有多大？ (数据库表千万级数据量多吗)

如何使用Oracle按时间导出表数据库？ (oracle按时间导出表数据库)

数据库存储：帖子长期保存，信息永不丢失 (帖子存数据库)

小米六数据库：全方位数据保障和优化方案 (小米六数据库)

简易教程：使用dbe数据库实现数据连接 (dbe数据库数据连接)

Oracle实现多个数据库链接的简便方法 (oracle链接多个数据库)

数据库索引：用哪种方法建立？ (数据库索引用什么建的)

实现高效缓存同步：Redis数据库技巧大全 (redis 数据库缓存同步)

如何利用数据库实现高效的模糊匹配查询？ (数据库实现模糊查询)

数据库有哪些安装方式和位置？ (数据库是装在什么上)

Lactmed数据库：妈妈们必备的喂养指南 (lactmed 数据库)

数据库设计中的主属性定义及作用 (数据库主属性是什么)

数据库分区操作不当常常造成分区不清的问题 (分区不清数据库)

相关文章

发布评论取消回复

共饮一杯

Oracle回收DBA权限注意事项

MongoDB 入门教学贴从术语到操作（约束怎么建立内部培训贴）

RisingWave 1.10 发布！新增用户自定义聚合函数

为什么程序员对这8种SQL的写法零容忍

SQL数据库触发器语法详解 (sql数据库触发器语法)

快速简单的删除Oracle数据库字段方法 (删除oracl数据库字段)

如何打开社工数据库bak文件 (社工数据库bak怎么打开)

实现数据库按拼音排序的方法和技巧 (数据库按拼音排序)

探究Sybase数据库的性能和功能特点 (sybase数据库怎么样)

SQL Server 如何成功建立自己的数据库？ (sql server 建立数据库)

如何在Oracle中查看数据库触发器？ (oracle查看数据库触发器)

数据库表数据量千万级，对性能影响有多大？ (数据库表千万级数据量多吗)

如何使用Oracle按时间导出表数据库？ (oracle按时间导出表数据库)

数据库存储：帖子长期保存，信息永不丢失 (帖子存数据库)

小米六数据库：全方位数据保障和优化方案 (小米六数据库)

简易教程：使用dbe数据库实现数据连接 (dbe数据库 数据连接)

Oracle实现多个数据库链接的简便方法 (oracle链接多个数据库)

数据库索引：用哪种方法建立？ (数据库索引用什么建的)

实现高效缓存同步：Redis数据库技巧大全 (redis 数据库缓存同步)

如何利用数据库实现高效的模糊匹配查询？ (数据库实现模糊查询)

数据库有哪些安装方式和位置？ (数据库是装在什么上)

Lactmed数据库：妈妈们必备的喂养指南 (lactmed 数据库)

数据库设计中的主属性定义及作用 (数据库主属性是什么)

数据库分区操作不当常常造成分区不清的问题 (分区不清数据库)

相关文章

发布评论 取消回复

共饮一杯

Oracle回收DBA权限注意事项

MongoDB 入门教学贴 从术语到操作 （约束怎么建立 内部培训贴）

RisingWave 1.10 发布！新增用户自定义聚合函数

简易教程：使用dbe数据库实现数据连接 (dbe数据库数据连接)

发布评论取消回复

MongoDB 入门教学贴从术语到操作（约束怎么建立内部培训贴）