Mysql调优实战

原查询语句：explain select DISTINCT asset.*, u1.name checkerName ,u2.name managerName,useDept.name useDeptName,
manageDept.name manageDeptName , u3.name newestCheckerName,u4.name inspectorName,
insp.name inspAssetTypeName from asset
LEFT JOIN user u1 ON u1.id=asset.checker_id
LEFT JOIN user u3 ON u3.id=asset.newest_checker
LEFT JOIN user u2 ON u2.id=asset.manager_id
LEFT JOIN user u4 ON u4.id=asset.inspector_id
LEFT JOIN insp_asset_type insp ON insp.code=asset.inspection_property_template_code
LEFT JOIN dept useDept on useDept.code = asset.use_dept_id
LEFT JOIN dept manageDept on manageDept.code= asset.manage_dept_id
WHERE 1=1 and asset.parent_property_id IS NULL order by asset.newest_check_time , asset.newest_check_time limit 0,10;
10 rows in set (4.16 sec)
大概花了4S(哭)。

表名	数据量(条)
asset	3700
user	100
insp_asset_type	20
dept	50

优化一 只输出select需要的属性,无关属性不输出。

输出的asset属性由90减少为30条。
10 rows in set (1.73 sec)
速度还是很慢

Explain

查看该Sql的查询语句。

百度了一下 Using join buffer(Block Nested loop)是由于没有添加正确的索引。
注:Block Nested loop表示嵌套循环查询，从主表取一条，再从从表遍历(若没有添加索引则，将所有行读到内存-using join buffer)。

添加索引

先为dept添加对应的索引(code)。
添加之后，查看查询计划。

果然dept那两张表不需要using join buffer了。
10 rows in set (0.625 sec)
提高了不少，看来再把insp_asset_type加上索引应该就差不多了。
10 rows in set (0.609 sec)
没提高多少，并且rows=2(mysql预期会查找两行)，由于insp_asset_type这个表中加的这个索引并不是unique的，所以导致提高的很少。

接下来看asset表。

using temporary：表示使用了连接后，会创建一张临时表。
using filesort:表示在内存中对数据表进行了排序。
如果对待排列的数据添加索引

10 rows in set (0.625 sec)
竟然更慢了。。。。
究其原因

记录条数有3768条，而Cardinality这个属性仅为3，表示可取值范围很小，称为低选择性，不适合用索引。
查看了一下newest_check_time这个属性，发现大部分的值仍然为空，所以查找3个时，基于索引的查找基本没有作用，反而由于多了查找索引这一步，花费了更多时间。

当该数据被填充的时候，索引仍然会保持较好的查询速度，但若未添加newest_check_time索引的