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

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

原来的SQL：

SELECTzpp.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 jkrqFROMzhms_peopledata_population zppLEFT JOIN zhms_peopledata_population_familyplanning zppf ON zpp.identity_code = zppf.identity_codeLEFT JOIN zhms_peopledata_rel_populationtree zprp ON zppf.identity_code = zprp.resource_codeWHEREzpp.is_valid = 1AND zprp.is_valid = 1AND zppf.is_valid = 1AND zprp.tag_code = '5bb0c3e4992f11e78d7252540096022e'ORDER BYzpp.create_time DESCLIMIT 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 后面，让其带着条件去链接表查询。

SELECTzpp.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 jkrqFROMzhms_peopledata_population zppLEFT JOIN zhms_peopledata_population_familyplanning zppf ON zpp.identity_code = zppf.identity_codeAND zpp.is_valid = 1AND zppf.is_valid = 1LEFT JOIN zhms_peopledata_rel_populationtree zprp ON zppf.identity_code = zprp.resource_codeAND zprp.is_valid = 1AND zprp.tag_code = '5bb0c3e4992f11e78d7252540096022e'ORDER BYzpp.create_time DESCLIMIT 0, 10

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

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