MySQL在2016年保持了一个强劲的增长数据库流行趋势。越来越多的客户在MySQL数据库上建立应用程序,它甚至从Oracle转移到MySQL。还有一些客户在使用MySQL数据库时遇到一些问题,例如慢响应时间,CPU打满等情况。阿里云RDS专家服务团队帮助云客户解决许多紧急问题。下面总结了apsaraDB专家诊断报告中出现的一些常见SQL问题:供大家参考。
常见SQL错误用法
1. LIMIT 句子
页面查询是最常见的场景之一,但它也常常是最容易陷入麻烦的地方。例如,下列简单的句子,DBA通常想将组合索引添加到类型、名称、 create_time字段中。这样条件序列可以有效地用于索引,性能迅速提升。
SELECT*FROMoperation
WHERE type='SQLStats'
AND name='SlowLog'
ORDER BYcreate_time
LIMIT 1000,10;
好吧，可能90%以上的DBA解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？
知道数据库不知道第一百万条的记录从何处开始,即使有指数, 它必须首先计算.出现这种性能问题，在大多数情况下,程序员是懒惰的。浏览前面的数据,或者大数据批量输出等等,可以使用前面的页面的最大值作为参数作为查询条件。SQL被重新设计如下:
SELECT*FROMoperation
WHERE type='SQLStats'
AND name='SlowLog'
ANDcreate_time >'2017-03-16 14:00:00'
ORDER BYcreate_timelimit 10;
在新的设计下,查询时间基本上是固定的,随数据量增加而不会改变。
2. 隐式转换
另一个常见的错误是SQL文中查询变量和字段定义类型不匹配。
mysql> explain extendedSELECT*>FROMmy_balance b
>WHEREb.bpn =14000000123
>ANDb.isverifiedISNULL ;
mysql> show warnings;
| Warning |1739| Cannot use ref accessonindex'bpn' due to type or collation conversion on field 'bpn'
其中,字段 bpn 定义为 varchar(20), MySQL 的策略是将字符串转换成数字并再比较它们。
上面的可能是应用程序框架自动填充的参数,而不是程序员原来的想法。 现在应用程序框架非常繁琐,使用方便,而且要小心它可能自己挖出一个洞。
3.相关更新和删除
虽然MySQL5.6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。
例如,在下面的更新声明中,MySQL实际上执行了一个循环/嵌入子查询(DEPENDENT SUBQUERY),其执行时间已知。
UPDATEoperation oSET status='applying'
WHEREo.idIN(SELECT id
FROM(SELECTo.id,
o.status
FROMoperation o
WHEREo.group =123
ANDo.statusNOT IN('done')
ORDER BYo.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”改为“ DEIVED”,执行速度从7秒提高到2毫秒。
UPDATEoperation oJOIN(SELECTo.id,
o.status
FROMoperation o
WHEREo.group =123
ANDo.statusNOT IN('done')
ORDER BYo.parent,
o.id
LIMIT 1) t
ONo.id = t.id
SET 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*FROMmy_order o
INNER JOINmy_appraise aONa.orderid = o.id
ORDER BYa.is_replyASC,
a.appraise_timeDESC
LIMIT 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*
FROMmy_order o
INNER JOINmy_appraise a
ONa.orderid = o.id
ANDis_reply =0
ORDER BYappraise_timeDESC
LIMIT 0,20)
UNIONALL
(SELECT*
FROMmy_order o
INNER JOINmy_appraise a
ONa.orderid = o.id
ANDis_reply =1
ORDER BYappraise_timeDESC
LIMIT 0,20)) t
ORDER BYis_replyASC,
appraisetimeDESC
LIMIT 20;
5.有句子
MySQL在处理EXISTS子句子时仍然使用嵌入式查询执行方法,例如以下的SQL声明:
SELECT*FROMmy_neighbor n
LEFT JOINmy_neighbor_apply sra
ONn.id = sra.neighbor_id
ANDsra.user_id ='xxx'
WHEREn.topic_status <4
AND EXISTS(SELECT 1
FROMmessage_info m
WHEREn.id = m.neighbor_id
ANDm.inuser ='xxx')
ANDn.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 |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
删除加入现有更改,避免嵌入式查询,并执行从1.93秒减少到1毫秒的时间。
SELECT*FROMmy_neighbor n
INNER JOINmessage_info m
ONn.id = m.neighbor_id
ANDm.inuser ='xxx'
LEFT JOINmy_neighbor_apply sra
ONn.id = sra.neighbor_id
ANDsra.user_id ='xxx'
WHEREn.topic_status <4
ANDn.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的子问题;
联合国或联合国所有子问题;
输出领域中的子问题;
例如,从执行计划中可以看出,它的条件在多项式查询后运行:
SELECT*FROM(SELECTtarget,
Count(*)
FROMoperation
GROUP BYtarget) t
WHEREtarget ='rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
|id| select_type |table|type| possible_keys |key| key_len |ref|rows| Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
|1| PRIMARY | <derived2> |ref| <auto_key0> | <auto_key0> |514| const |2|Using where|
|2| DERIVED | operation |index| idx_4 | idx_4 |519|NULL|20|Using index|
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
为了确定查询条件是否可以直接从语义中推导,重新写如下:
SELECTtarget,Count(*)
FROMoperation
WHEREtarget ='rm-xxxx'
GROUP BYtarget
执行计划变为：
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| 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*FROMmy_order o
LEFT JOINmy_userinfo u
ONo.uid = u.uid
LEFT JOINmy_productinfo p
ONo.pid = p.pid
WHERE( o.display =0)
AND( o.ostaus =1)
ORDER BYo.selltimeDESC
LIMIT 0,15
SQL声明最初的意思是:首先创建一系列左键连接,然后序列前15个记录。 如从执行计划中可以看出,最后一步估计序列记录的数目为90,00,时间消耗为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*
FROMmy_order o
WHERE( o.display =0)
AND( o.ostaus =1)
ORDER BYo.selltimeDESC
LIMIT 0,15
) o
LEFT JOINmy_userinfo u
ONo.uid = u.uid
LEFT JOINmy_productinfo p
ONo.pid = p.pid
ORDER BYo.selltimeDESC
limit 0,15
再次检查执行计划:在子查询实物化后参加JOIN(select_type=DERIVED)。虽然估计的线扫描时间仍然为90,00,但使用索引和LIMIT分项后实际执行时间变得较小。
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+| id |select_type| table |type| possible_keys |key| key_len |ref| rows |Extra|
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| 1 | PRIMARY |<derived2>| 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. 中等 结果 除去
让我们再看一下最初优化的下面的例子(左连接中的主表优先查询条件):
SELECTa.*,c.allocated
FROM(
SELECTresourceid
FROMmy_distribute d
WHEREisdelete =0
ANDcusmanagercode ='1234567'
ORDER BYsalecodelimit 20) a
LEFT JOIN
(
SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated
FROMmy_resources
GROUP BYresourcesid) c
ONa.resourceid = c.resourcesid
那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。
事实上,对于子问题c,左连结的最终结果集只关心可以匹配主资源ID的数据。 因此,我们可以如下重写句子,执行时间从原来的2秒下降到2毫秒。
SELECTa.*,c.allocated
FROM(
SELECTresourceid
FROMmy_distribute d
WHEREisdelete =0
ANDcusmanagercode ='1234567'
ORDER BYsalecodelimit 20) a
LEFT JOIN
(
SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated
FROMmy_resources r,
(
SELECTresourceid
FROMmy_distribute d
WHEREisdelete =0
ANDcusmanagercode ='1234567'
ORDER BYsalecodelimit 20) a
WHEREr.resourcesid = a.resourcesid
GROUP BYresourcesid) c
ONa.resourceid = c.resourcesid
但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用WITH语句再次重写：
WITH a AS(
SELECTresourceid
FROMmy_distribute d
WHEREisdelete =0
ANDcusmanagercode ='1234567'
ORDER BYsalecodelimit 20)
SELECTa.*,
c.allocated
FROMa
LEFT JOIN
(
SELECTresourcesid，sum(ifnull(allocation,0) *12345) allocated
FROMmy_resources r,
a
WHEREr.resourcesid = a.resourcesid
GROUP BYresourcesid) c
ONa.resourceid = c.resourcesid
AliSQL即将推出语法, 请期待.
总结
数据库编译器生成执行计划,确定SQL的实际执行方法。但是编译器只是想提供服务,并非所有的数据库编译器都是漂亮的。上述的大部分情形,其他数据库也有性能问题。了解数据库编译器的特点,才能避规其短处，编写高性能SQL声明。
在设计数据模型和编译SQL文时,程序员将算法的概念或意识引入。
编译复杂的SQL文档需要使用 WITH文档的习惯。 一个简单而有思想的SQL文档也可以减少数据库的负担。
解决云数据库的问题(不局限于SQL问题),并一直寻求阿里云工厂专家的帮助。