本文共 4524 字，大约阅读时间需要 15 分钟。
在实际工作中，数据库管理员（DBA）和程序员可能会遇到各种SQL语句 optimization 的问题。这些问题不仅会影响程序运行效率，还可能导致系统性能下降。本文将详细分析8种常见的SQL错误用法及其优化方法。
分页查询在数据库中是非常常见的操作，但也是容易出错的地方。通常建议在 type、name、create_time 等字段上创建组合索引，以充分发挥索引带来的性能提升。
比如，以下查询可能会因为 LIMIT子句的处理方式而性能差异很大：
SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' ORDER BY create_time LIMIT 1000, 10;
虽然 LIMIT 1000 限制查回的记录数，但如果数据库不能确定从哪一条记录开始计算，索引的效果可能会被削弱。为了更好地完成分页，可以将 WHERE 条件中的 create_time 参数传递给数据库，使其在已有索引的基础上高效查询。
修改后的查询如下：
SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time LIMIT 10;
这个修改可以显著提升查询性能。
在查询中未正确处理类型转换可能导致索引失效，从而降低查询效率。例如，bpn字段定义为 varchar(20)，但在查询中未正确处理类型转换，可能导致以下问题：
EXPLAIN EXTENDED SELECT * FROM my_balance b WHERE b.bpn = 14000000123 AND b.isverified IS NULL;
执行结果可能显示：
Warning | 1739 | Cannot use ref column on index 'bpn' due to type or collation conversion on field 'bpn'
这种情况下，需要确保字段的存储和检索类型一致，并且索引可以被正确利用，以避免性能损失。
在 MySQL 5.6 及更高版本中，默认情况下对 UPDATE 和 DELETE 语句进行物化优化。然而，在复杂的场景下，仍然需要手动重写查询以避免使用 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); 为了优化，可以将查询重写为 JOIN 操作：
UPDATE operation oJOIN (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) tON o.id = t.id SET status = 'applying'; 这种改动可以将查询时间从7秒降低到2秒。
在 MySQL 中，读 �ISTA techniques（如 ENUM 和 SET 列）不支持混合排序。虽然这种方法在大多数情况下并不常见，但在特定的场景下，可能会导致全表扫描，当要对多个字段进行排序时，需要特别注意。
以下查询可能会导致全表扫描：
SELECT * FROM my_order oINNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC, a.appraise_time DESCLIMIT 0, 20;
为了优化，可以将排序条件合并到子查询中：
SELECT * FROM (SELECT * FROM my_order oWHERE is_reply = 0ORDER BY appraisetime DESCLIMIT 0, 20) tUNION ALL(SELECT * FROM my_order oWHERE is_reply = 1ORDER BY appraisetime DESCLIMIT 0, 20) tORDER BY is_reply ASC, appraisetime DESCLIMIT 20;
这种改动可以在大数据量的场景下显著提升性能。
在 MySQL 中，EXISTS子句仍然采用替代子查询的方式，这可能导致索引无法被有效利用。例如，以下查询可能会因为 DEPENDENT SUBQUERY 而导致性能下降：
SELECT * FROM my_neighbor nLEFT 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;
为了避免 DEPENDENT SUBQUERY，可以使用 LEFT JOIN 替代 EXISTS：
SELECT * FROM my_neighbor nINNER 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;
这种改动可以将执行时间从1.93秒降低到1秒。
在 MySQL 中，对于复杂的子查询，外部查询条件无法被下推到聚合函数或包含 LIMIT 子句、UNION 或 UNION ALL 的子查询中。例如，以下查询可能会因为条件下推失败而产生额外的性能开销：
SELECT * FROM (SELECT target, COUNT(*) FROM operation GROUP BY target) t WHERE target = 'rm-xxxx';
通过调整语句结构，可以使条件能够被下推：
SELECT target, COUNT(*) FROM operation WHERE target = 'rm-xxxx' GROUP BY target;
这一改动可以显著提升查询效率。
在进行复杂的 JOIN 操作前，可以先对主表执行 LIMIT 以减少数据传输量。例如：
SELECT * FROM my_order oLEFT JOIN my_userinfo u ON o.uid = u.uidLEFT JOIN my_productinfo p ON o.pid = p.pidWHERE (o.display = 0) AND (o.ostaus = 1)ORDER BY o.selltime DESCLIMIT 0, 15;
可以改为：
SELECT * FROM ( SELECT * FROM my_order o WHERE (o.display = 0) AND (o.ostaus = 1) ORDER BY o.selltime DESC LIMIT 0, 15) oLEFT JOIN my_userinfo u ON o.uid = u.uidLEFT JOIN my_productinfo p ON o.pid = p.pidORDER BY o.selltime DESCLIMIT 0, 15;
这种改动可以将执行时间从12秒降低到1秒。
在复杂的 LEFT JOIN 操作中，子查询可以物理化后作为参数传递给主查询，以减少性能开销。例如：
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, a WHERE r.resourcesid = a.resourceid GROUP BY resourcesid) c ON a.resourceid = c.resourcesid;
可以改为：
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.resourceid GROUP BY resourcesid) c ON a.resourceid = c.resourcesid;
这种改动可以将执行时间从2秒降低到2毫秒。
数据库的编译器无法完全掌握查询计划的优化细节，因此理解数据库的执行机制是写出高性能SQL语句的关键。在编写SQL时，程序员需要结合数据模型和查询需求，避免不必要的复杂化和优化。
转载地址：http://zcvyk.baihongyu.com/