MySQL在关联复杂情况下所能做出的一些优化
昨天处理了一则复杂关联SQL的优化，这类SQL的优化往往考虑以下四点：
第一.查询所返回的结果集，通常查询返回的结果集很少，是有信心进行优化的；
第二.驱动表的选择至关重要，通过查看执行计划，可以看到优化器选择的驱动表,从执行计划中的rows可以大致反映出问题的所在；
第三.理清各表之间的关联关系，注意关联字段上是否有合适的索引；
第四.使用straight_join关键词来强制表之间的关联顺序，可以方便我们验证某些猜想；
SQL:
执行时间：
mysql>selectc.yh_id, ->c.yh_dm, ->c.yh_mc, ->c.mm, ->c.yh_lx, ->a.jg_id, ->a.jg_dm, ->a.jg_mc, ->a.jgxz_dm, ->d.js_dmyh_js ->froma,b,c ->leftjoindond.yh_id=c.yh_id ->wherea.jg_id=b.jg_id ->andb.yh_id=c.yh_id ->anda.yx_bj=‘Y' ->andc.sc_bj=‘N' ->andc.yx_bj=‘Y' ->andc.sc_bj=‘N' ->andc.yh_dm='006939748XX'; 1rowinset(0.75sec)
这条SQL查询实际只返回了一行数据，但却执行耗费了750ms，查看执行计划：
mysql>explain ->selectc.yh_id, ->c.yh_dm, ->c.yh_mc, ->c.mm, ->c.yh_lx, ->a.jg_id, ->a.jg_dm, ->a.jg_mc, ->a.jgxz_dm, ->d.js_dmyh_js ->froma,b,c ->leftjoindond.yh_id=c.yh_id ->wherea.jg_id=b.jg_id ->andb.yh_id=c.yh_id ->anda.yx_bj=‘Y' ->andc.sc_bj=‘N' ->andc.yx_bj=‘Y' ->andc.sc_bj=‘N' ->andc.yh_dm='006939748XX'; +—-+————-+——-+——–+——————+———+———+————–+——-+————-+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+——–+——————+———+———+————–+——-+————-+ |1|SIMPLE|a|ALL|PRIMARY,INDEX_JG|NULL|NULL|NULL|52616|Usingwhere| |1|SIMPLE|b|ref|PRIMARY|PRIMARY|98|test.a.JG_ID|1|Usingindex| |1|SIMPLE|c|eq_ref|PRIMARY|PRIMARY|98|test.b.YH_ID|1|Usingwhere| |1|SIMPLE|d|index|NULL|PRIMARY|196|NULL|54584|Usingindex| +—-+————-+——-+——–+——————+———+———+————–+——-+————-+
可以看到执行计划中有两处比较显眼的性能瓶颈：
|1|SIMPLE|a|ALL|PRIMARY,INDEX_JG|NULL|NULL|NULL|52616|Usingwhere| |1|SIMPLE|d|index|NULL|PRIMARY|196|NULL|54584|Usingindex|
由于d是leftjoin的表，所以驱动表不会选择d表，我们在来看看a,b,c三表的大小：
mysql>selectcount(*)fromc; +———-+ |count(*)| +———-+ |53731| +———-+ mysql>selectcount(*)froma; +———-+ |count(*)| +———-+ |53335| +———-+ mysql>selectcount(*)fromb; +———-+ |count(*)| +———-+ |105809| +———-+
由于b表的数据量大于其他的两表，同时b表上基本没有查询过滤条件，所以驱动表选择B的可能排除；
优化器实际选择了a表作为驱动表，而为什么不是c表作为驱动表？我们来分析一下：
第一阶段：a表作为驱动表
a–>b–>c–>d:
(1):a.jg_id=b.jg_id—>(b索引:PRIMARYKEY(`JG_ID`,`YH_ID`))
(2):b.yh_id=c.yh_id—>(c索引:PRIMARYKEY(`YH_ID`))
(3):c.yh_id=d.yh_id—>(d索引:PRIMARYKEY(`JS_DM`,`YH_ID`))
由于d表上没有yh_id的索引，索引在d表上添加索引：
altertabledaddindexind_yh_id(yh_id);
执行计划：
+—-+————-+——-+——–+——————+———–+———+————–+——-+————-+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+ |1|SIMPLE|a|ALL|PRIMARY,INDEX_JG|NULL|NULL|NULL|52616|Usingwhere| |1|SIMPLE|b|ref|PRIMARY|PRIMARY|98|test.a.JG_ID|1|Usingindex| |1|SIMPLE|c|eq_ref|PRIMARY|PRIMARY|98|test.b.YH_ID|1|Usingwhere| |1|SIMPLE|d|ref|ind_yh_id|ind_yh_id|98|test.b.YH_ID|272|Usingindex| +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
执行时间：
1rowinset(0.77sec)
在d表上添加索引后，d表的扫描行数下降到272行（最开始为：54584）
|1|SIMPLE|d|ref|ind_yh_id|ind_yh_id|98|test.b.YH_ID|272|Usingindex|
第二阶段：c表作为驱动表
d
^
|
c–>b–>a
由于在c表上有yh_dm过滤性很高的筛选条件，所以我们在yh_dm上创建一个索引：
mysql>selectcount(*)fromcwhereyh_dm='006939748XX'; +———-+ |count(*)| +———-+ |2| +———-+
添加索引：
altertablecaddindexind_yh_dm(yh_dm)
查看执行计划：
+—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+ |1|SIMPLE|a|ALL|PRIMARY,INDEX_JG|NULL|NULL|NULL|52616|Usingwhere| |1|SIMPLE|b|ref|PRIMARY|PRIMARY|98|test.a.JG_ID|1|Usingindex| |1|SIMPLE|c|eq_ref|PRIMARY,ind_yh_dm|PRIMARY|98|test.b.YH_ID|1|Usingwhere| |1|SIMPLE|d|ref|ind_yh_id|ind_yh_id|98|test.b.YH_ID|272|Usingindex| +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
执行时间：
1rowinset(0.74sec)
在c表上添加索引后，索引还是没有走上，执行计划还是以a表作为驱动表，所以我们这里来分析一下为什么还是以a表作为驱动表？
1):c.yh_id=b.yh_id—>(PRIMARYKEY(`JG_ID`,`YH_ID`))
a.如果以c表为驱动表，则c表与b表在关联的时候，由于在b表没有yh_id字段的索引，由于b表的数据量很大，所以优化器认为这里如果以c表作为驱动表，则会与b表产生较大的关联（这里可以使用straight_join强制使用c表作为驱动表）；
b.如果以a表为驱动表，则a表与b表在关联的时候，由于在b表上有jg_id字段的索引，所以优化器认为以a作为驱动表的代价是小于以c作为驱动板的代价；
所以我们如果要以C表为驱动表，只需要在b上添加yh_id的索引：
altertablebaddindexind_yh_id(yh_id);
2):b.jg_id=a.jg_id—>(PRIMARYKEY(`JG_ID`))
3):c.yh_id=d.yh_id—>(KEY`ind_yh_id`(`YH_ID`))
执行计划：
+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+ |1|SIMPLE|c|ref|PRIMARY,ind_yh_dm|ind_yh_dm|57|const|2|Usingwhere| |1|SIMPLE|d|ref|ind_yh_id|ind_yh_id|98|test.c.YH_ID|272|Usingindex| |1|SIMPLE|b|ref|PRIMARY,ind_yh_id|ind_yh_id|98|test.c.YH_ID|531|Usingindex| |1|SIMPLE|a|eq_ref|PRIMARY,INDEX_JG|PRIMARY|98|test.b.JG_ID|1|Usingwhere| +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
执行时间：
1rowinset(0.00sec)
可以看到执行计划中的rows已经大大降低，执行时间也由原来的750ms降低到0ms级别；