一次SQL查询优化实录
一、背景
今年五月上上线了一个Web系统，面向公众开放使用，随着系统用户量增长，系统性能下降的问题十分明显，因此着手进行瓶颈点排查。
二、Tomcat优化
按照日常方法优化Tomcat7，启用线程池、GZIP等机制（详情请Google之），系统性能略有改善。考虑通常磁盘IO通常才是系统关键瓶颈点，因此着手优化数据库。
三、数据库优化
当前系统使用MySQL5.1数据库，当前的数据库中5个关键表中数据平均各约10万左右，2个基本数据表，3个关联关系表。
安装30天免费试用版的MONYog5.5监测数据库状态和SQL，将执行次数较多查询1秒以上的语句导出，逐个分析。
?
1.增加字段索引
找出查询条件和关联时常用的字段，为其增加索引后，有80%以上的语句性能得到巨大提升，查询效率达到50毫秒以下。
需要注意的一点是，关系表中通常使用两个字段构建主键，应当分别为每个字段创建单独的索引。
2.优化复杂关联语句
MySQL5.x早期版本的子查询的性能非常差，特别是嵌套子查询，因此在程序中对查询语句进行拆分，分为两到三次查询，然后使用程序进行结果集合并。
例如，本系统中的一个需求：在某页面要求显示选中机构下的下级机构名称列表，每个机构名称后同时需要显示其下级机构和人员的数量。由于系统中对用户进行了人员查看分级控制，因此每个用户进行时看到的数据并不致，不适合采用数据维护时计算的方式（就是在添加、修改、删除机构或人员时，在相应的字段上更新累计数量）。
版本1的语句为：
SELECT g.group_id,group_name,((SELECT COUNT(*) FROM tbl_contacts_group AS tcg,tbl_contacts AS c WHERE tcg.group_id=g.group_id AND c.cont_id=tcg.cont_id AND is_display=1 AND cont_level>='A')+(SELECT COUNT(*) FROM tbl_group_relation WHERE parent_id=g.group_id)) AS group_childs_num FROM tbl_group AS g ,tbl_group_relation AS rWHERE g.group_id=r.group_id AND g.ec_id='5515118546' AND r.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' ORDER BY row_id ;
?用时约5-10秒，当有高并发出现时可能更高
版本2调整为两条语句
SELECT g.group_id,group_name,(SELECT COUNT(*) FROM tbl_group_relation WHERE parent_id=g.group_id) AS group_childs_num FROM tbl_group_relation AS r,tbl_group AS g WHERE g.group_id=r.group_id AND r.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' ORDER BY row_id;SELECT tcg.group_id,COUNT(0) FROM tbl_contacts_group AS tcgJOIN tbl_contacts AS cWHERE is_display=1 AND c.cont_id=tcg.cont_id AND tcg.group_id IN (SELECT group_id FROM tbl_group_relation AS tgr WHERE tgr.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85') AND cont_level>='A'GROUP BY tcg.group_id
?第1条语句约20ms，第二条约500ms，使用程序合并的时间可以忽略
?
版本3中将第二条语句使用关联查询替代子查询，时间缩短为30ms
SELECT cg.group_id,COUNT(0) AS contact_count FROM tbl_contacts_group AS cg,tbl_contacts AS c,tbl_group_relation AS gr WHERE c.cont_id=cg.cont_id AND cg.group_id=gr.group_id AND gr.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' AND is_display=1 AND cont_level>='A' GROUP BY cg.group_id
?
四、总结
数据库优化的最佳效果
1.常用字段增加索引
2.拆分复杂语句
4.优化数据库参数，适当增加相关参数的缓存空间
5.升级数据库版本（在mysql5.6上，版本1的语句执行时间在2秒以内）
优化的方法
1.监控分析（MONYog是个好工具）获取执行频繁用时较长的语句
2.调优
3.重复第1步
?
