一直被用错的6种SQL 错误用法

　　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

　　关于 MySQL 外部条件不能下推的详细解释说明请参考以前文章：MySQL · 性能优化 · 条件下推到物化表 http://mysql.taobao.org/monthly/2016/07/08

　　6、提前缩小范围

　　先上初始 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 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 |

　　总结

　　数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。

　　上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。

　　程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。

　　编写复杂SQL语句要养成使用 WITH 语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担 。

一直被用错的6种SQL 错误用法

标签：xtend   组合   opera   date   基本   变量   cond   简化   org