SQL查询语句优化

在实际工作中，特别是后台统计功能，要求的数据查询往往非常复杂，经常把数据库拖慢甚至崩溃。下面介绍如何优化SQL及一些建议。以MySql数据库为例。

一、由于每个人的SQL水平不同，很少人做到一次就写出完美或高性能的SQL来。所以一开始只能根据实际情况写出能实现功能的SQL。下面的SQL是工作中遇到的，拿出来举例：

EXPLAIN

SELECT c.id, c.typeid, c.title, c.senddate, c.keywords, c.description, d.body, e.tag

FROM (SELECT id, typeid, title, senddate, keywords, description

FROM (SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description

FROM `hsd_archives` a

WHERE NOT EXISTS (SELECT b.aid FROM `hsd_synchronize_detail` b WHERE b.aid = a.id)

UNION

SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description

FROM `hsd_archives` a

WHERE EXISTS (SELECT b.aid FROM `hsd_synchronize_detail` b WHERE a.id = b.aid AND b.`status` = '0')

) temp

ORDER BY

temp.senddate

LIMIT 0,

100

) c

LEFT JOIN `hsd_addonarticle` d ON c.id = d.aid

LEFT JOIN (SELECT aid, GROUP_CONCAT(tag) tag FROM `hsd_taglist` GROUP BY aid) e ON c.id = e.aid

这条SQL语句在生产环境执行时间长达30分钟，而最大的两个表数据量才10多万条，这是无法忍受的。

二、利用Navicat等工具的解释(执行计划)功能分析SQL性能，或在SQL语句前加“EXPLAIN”关键字执行即可。

这是上面的语句执行的SQL计划，可以看出有10个表引用关系，且几乎全是全表扫描。

简单说明下分析结果，更多详情请使用百度：

1、id列：表执行顺序，值越高优先级越高，越先执行。

2、type列：all表示全表扫描，ref和eq_ref都表示使用了索引(在多表联合查询时)，const表示通过索引一次就找到了，性能顺序是：all-->index-->range-->ref-->eq_ref-->const,system，越往右性能越高。

3、key列：显示会用到的索引，Null表示未使用索引，PRIMARY表示使用主键索引，其他名称表示索引名称。

三、根据分析结果进行优化。

1、尽最大可能避免全表扫描，即分析结果中的type列值为all的情况，关系型数据库基本上是采用B+树存储数据的，在有索引的情况下，1000万条数据最多只需要查询23次即可命中，假如我们要查询两个表，而每个表的数据是10万条，在全表扫描情况下，查找次数最高是10万*10万=100亿，这是指数级的增长。举个例子：一张普通的A4纸折叠25次后有多厚?答案肯定令你惊讶，是日本最高峰富士山那么高。所以我们在经常使用的查询条件字段应该添加索引(重复数据较少的字段，像status这种就不适合加索引)。

2、减少引用的表数量，减少一个表引用就少一次表扫描，比如上面的SQL例子，发现有一段子查询是这样的：

SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description

FROM `hsd_archives` a

WHERE NOT EXISTS (SELECT b.aid FROM `hsd_synchronize_detail` b WHERE b.aid = a.id)

UNION

SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description

FROM `hsd_archives` a

WHERE EXISTS (SELECT b.aid FROM `hsd_synchronize_detail` b WHERE a.id = b.aid AND b.`status` = '0')

执行计划结果有5个表引用，发现其实这两条SQL很像，是否可以合并下呢?经优化后的结果如下，瞬间减少两个表引用：

SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description

FROM hsd_archives a

WHERE NOT EXISTS ( SELECT b.aid FROM `hsd_synchronize_detail` b WHERE b.aid = a.id )

OR EXISTS ( SELECT b.aid FROM `hsd_synchronize_detail` b WHERE a.id = b.aid AND b.`status` = '0' )

四、重复第二步和第三步，直到满意为止，上面的SQL最终优化的结果如下：

SELECT a.id, a.typeid, a.title, a.senddate, a.keywords, a.description, d.body,

(SELECT GROUP_CONCAT(tag) FROM `hsd_taglist` where aid=a.id) as tag

FROM `hsd_archives` a

JOIN `hsd_addonarticle` d ON a.id = d.aid and ifnull(d.body,'') != ''

WHERE NOT EXISTS ( SELECT b.aid FROM `hsd_synchronize_detail` b WHERE b.aid = a.id )

OR EXISTS ( SELECT b.aid FROM `hsd_synchronize_detail` b WHERE a.id = b.aid AND b.`status` = '0' )

ORDER BY a.senddate,a.id LIMIT 0, 100

执行计划结果为5个表引用，且只有一个全表扫描，执行时间缩短为2秒。当然SQL高手应该还能继续优化。

补充说明及建议：

1、一般慢查询大多集中在后台的统计数据和报表，对数据的实时性要求并不是很高，在这种情况下，除了优化SQL外，还应尽量避免在主库执行。通常是在备库查询或用ETL工具导出到另一个库进行分析、数据落地等操作。

2、合理创建索引，业内建议一个表的索引不要超过5个，索引本身会损耗性能，对数据的更新操作影响很大。如果一个表非要创建5个以上的索引，看看是否可以考虑分表了。

3、组合索引在Mysql中是有顺序的，例如有一组合索引为A+B+C列，哪些情况下会使用到索引?当查询条件为A或A+B或A+B+C时会用到该索引，当查询条件为B或B+C或A+C时不会用到索引。

4、在管理台中，对大数据表的查询功能不要点击菜单马上进行查询，通常是进入页面后让用户输入一些特定条件后再查询，这可以避免用户误点菜单或用户进入页面本身就要进行条件筛选的情况造成不必要的数据库查询损耗。