8 种最坑的SQL错误用法，你有没有扯过？

---+---------+-------+------+-----------------------------------------------------+| 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 DESCLIMIT 0, 20

继续执行计划书清高示为全表格图像：

+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+| 1 | PLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort || 1 | PLE | 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;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 | PLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition || 1 | PLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where || 1 | PLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+6、前提理应

举例来说查找前提不可够理应到复杂的视图或姪查找的具体情况有：

1、姪查找；2、含有 LIMIT 的姪查找；3、UNION 或 UNION ALL 姪查找；4、可用标识符当中的姪查找；

如后面的语义，从继续执行计划书可以说明了其前提依赖性于姪查找在此之后：

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 | PLE | 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 = 0 ) AND ( o.ostaus = 1 )ORDER BY o.selltime DESCLIMIT 0, 15

该SQL语义原义是：先要用一系列的右下通到，然后依序取前15条纪录。从继续执行计划书也可以说明了，仍要一步推估依序纪录数为90万，一段时间消耗为12秒。

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+| 1 | PLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort || 1 | PLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL || 1 | PLE | 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 = 0 ) AND ( o.ostaus = 1 )ORDER BY o.selltime DESCLIMIT 0, 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 0, 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、当尾端结果集理应

于是又来看后面这个早已初步简化过的例姪(右下通到当中的主表格前提依赖性查找前提)：

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概括

元数据集codice_产生继续执行计划书，暂时着SQL的理论上继续执行方式则。但是codice_只是竭尽全力客户服务，所有元数据集的codice_都不是尽善尽美的。

上述引用的多数场景，在其它元数据集当中也共存稳定性情况。知晓元数据集codice_的适应性，才能避规其长处，写出高稳定性的SQL语义。

解释器在的设计数据集模型以及执笔SQL语义时，要把算法的思想或精神带进来。

执笔复杂SQL语义要教导可用 WITH 语义的习惯性。简洁且思路清晰的SQL语义也能减小元数据集的负担 。

来源：yq.aliyun.com/articles/72501

。

小孩子经常便秘怎么办
看病人
颈椎痛用什么药好得快
英太青凝胶去哪能买到
三精葡萄糖酸锌
拉肚子益生菌可以和蒙脱石散一起吃吗
普适泰片和坦洛新的区别
髋关节肿胀怎么治疗
儿科医院
皮肤粗糙吃什么药可以改善