那时给我们撷取两个SQL常见的“坏习惯”及强化基本上功。
SQL句子的继续执行次序：
1、LIMIT 句子
巨集查阅是最常见的情景众所周知，但也通常来说也是最难出难题的地方性。比如说对上面单纯的句子，通常 DBA 想不到的办法是在 type, name, create_time 表头上有女团检索。这样前提次序都能有效率的借助到检索，操控性快速提高。
SELECT * FROM operation WHERE type = SQLStats AND name = SlowLog ORDER BY create_time LIMIT 1000, 10;好吧，可能90%以内的 DBA 化解该难题就就此结束。但当 LIMIT 从句变为 “LIMIT 1000000,10” 时，开发人员依然会埋怨：我只取10条历史记录为何还是慢？
要知道资料库也无人知晓第1000000条历史记录从什么地方性开始，即便有检索也须要一气呵成排序一场。出现这种操控性问题，绝大多数情况下是开发人员贪心了。
在后端统计数据下载缩放，或是大统计数据先期求出等情景下，是可以将上两本的最小值当做模块做为查阅前提的。SQL 再次结构设计如下表所示：
SELECT * FROM operation WHERE 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 o SET 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.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 * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id ORDER BY a.is_reply ASC, a.appraise_time DESC 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 * 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)) t ORDER 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、前提下推
外部查阅前提不能够下推到复杂的视图或子查阅的情况有：
1、聚合子查阅；2、含有 LIMIT 的子查阅；3、UNION 或 UNION ALL 子查阅；4、输出表头中的子查阅；如上面的句子，从继续执行计划可以看出其前提作用于聚合子查阅之后：
SELECT * FROM (SELECT target, Count(*) FROM operation GROUP BY target) t WHERE 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 operation WHERE 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 | +—-+————-+———–+——+—————+——-+———+——-+——+——————–+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.pid WHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 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 o WHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 15 ) o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid ORDER BY o.selltime DESC limit 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.allocated FROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = 1234567 ORDER BY salecode limit 20) a LEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources GROUP BY resourcesid) c ON a.resourceid = c.resourcesid那么该句子还存在其它难题吗？不难看出子查阅 c 是全表聚合查阅，在表数量特别大的情况下会导致整个句子的操控性下降。
其实对子查阅 c，左连接最后结果集只关心能和主表 resourceid 能相匹配的统计数据。因此我们可以重写句子如下表所示，继续执行时间从原来的2秒下降到2毫秒。
SELECT a.*, c.allocated FROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = 1234567 ORDER BY salecode limit 20) a LEFT 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) c ON 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.allocated FROM a LEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) c ON a.resourceid = c.resourcesid总结
资料库编译器产生继续执行计划，决定着SQL的实际继续执行方式。但是编译器只是尽力服务，所有资料库的编译器都不是尽善尽美的。
上述提到的绝大多数情景，在其它资料库中也存在操控性难题。了解资料库编译器的特性，才能避规其短处，写出高操控性的SQL句子。
开发人员在结构设计统计数据模型以及编写SQL句子时，要把算法的思想或意识带进来。
编写复杂SQL句子要养成使用 WITH 句子的习惯。简洁且思路清晰的SQL句子也能减小资料库的负担 。
