MySQL优化之190秒到0.005秒的神奇飞跃【单库单表】

最近在做某市的人口大数据，开发过程中测试数据只有千位级别的数据，基本看不出影响，当真是数据导入库中时，加载人口列表时页面都崩溃了。在console中粘出SQL放在数据库中运行，整个人都傻了，一条sql在数据库中整整执行了190.052s......难怪页面等待无响应。经过一系列优化步骤最后的执行速度仅为0.005秒。所以将优化过程记录下。

原来的SQL：

SELECT
	zpp.id AS id,
	zpp.citizen_name AS citizenName,
	zpp.identity_code AS identityCode,
	zpp.sex AS sex,
	zpp.nation AS nation,
	zpp.create_time AS createTime,
	zpp.update_acc_id AS updateAccId,
	zpp.update_time AS updateTime,
	zppf.identity_code AS identityCode,
	zppf.XM AS xm,
	zppf.XB AS xb,
	zppf.CSRQ AS csrq,
	zppf.HYZK AS hyzk,
	zppf.MZ AS mz,
	zppf.HKXZ AS hkxz,
	zppf.WHCD AS whcd,
	zppf.JZDDZ AS jzddz,
	zppf.JKRQ AS jkrq
FROM
	zhms_peopledata_population zpp
LEFT JOIN zhms_peopledata_population_familyplanning zppf ON zpp.identity_code = zppf.identity_code
LEFT JOIN zhms_peopledata_rel_populationtree zprp ON zppf.identity_code = zprp.resource_code
WHERE
	zpp.is_valid = 1
AND zprp.is_valid = 1
AND zppf.is_valid = 1
AND zprp.tag_code = '5bb0c3e4992f11e78d7252540096022e'
ORDER BY
	zpp.create_time DESC
LIMIT 0,
 10

看上去就是一个简单的三表的链接查询，但是当主表数据20万条，第一字表数据16万条，关系表数据18万条时，三表链接的查询速度竟然用时190秒。如果count求总数速度可想而知，估计接近7分钟。所以必须优化，并且将时间优化至秒级。

优化第一步：根据业务需求选择数据库引擎

MySQL常用的引擎就是InnoDB和MyISAM两种，根据我们的业务需求，如果表中需要很多的写入操作时可选用InnoDB引擎，因为它支持事务操作。如果多是查询，或者count(*)的操作则选用MyISAM引擎。没有where条件的count(*)时MyISAM要比InnoDB快很多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。具体可以看看两者的区别。

优化第二步：将所有的查询列加上索引

MySQL索引分为BTREE索引和HASH索引两种。BTREE顾名思义就是一个树，索引遍历方式从左到右。 HASH索引顾名思义就是HASH结构存储，与Java中hash相同。选择时也是根据自己的业务需求，HASH索引在精确查找时很快，但是缺点在于索引字段不能拆开即不能使用“LIKE”模糊查询，因为业务中有like查询，所以我选择BTREE索引。

完成以上两步，执行SQL发现时间还是慢，达不到预期的效果，于是 从SQL本身出发。

优化第三步：修改SQL中不合理的地方。

犹豫三个表的数据都非常多，遍历一遍已经很慢了 多次遍历则更慢，所以将 原有的SQL中的 Where条件 拿掉，放在了Left Join 后面，让其带着条件去链接表查询。

SELECT
	zpp.id AS id,
	zpp.citizen_name AS citizenName,
	zpp.identity_code AS identityCode,
	zpp.sex AS sex,
	zpp.nation AS nation,
	zpp.create_time AS createTime,
	zpp.update_acc_id AS updateAccId,
	zpp.update_time AS updateTime,
	zppf.identity_code AS identityCode,
	zppf.XM AS xm,
	zppf.XB AS xb,
	zppf.CSRQ AS csrq,
	zppf.HYZK AS hyzk,
	zppf.MZ AS mz,
	zppf.HKXZ AS hkxz,
	zppf.WHCD AS whcd,
	zppf.JZDDZ AS jzddz,
	zppf.JKRQ AS jkrq
FROM
	zhms_peopledata_population zpp
LEFT JOIN zhms_peopledata_population_familyplanning zppf ON zpp.identity_code = zppf.identity_code
AND zpp.is_valid = 1
AND zppf.is_valid = 1
LEFT JOIN zhms_peopledata_rel_populationtree zprp ON zppf.identity_code = zprp.resource_code
AND zprp.is_valid = 1
AND zprp.tag_code = '5bb0c3e4992f11e78d7252540096022e'
ORDER BY
	zpp.create_time DESC
LIMIT 0,
 10

然后执行SQL，查询时间从原来的190秒瞬间变为0.05秒。

这是针对单表单库优化，如果数据量再大的话就得考虑分库分表存储了。这只是个人的优化思路，如果有更好的也可以交流分享，毕竟SQL优化，任重道远。