MySQL在关联复杂情况下所能做出的一些优化

这篇文章主要介绍了MySQL在关联复杂情况下所能做出的一些优化,作者通过添加索引来不断优化查询时间,需要的朋友可以参考下

昨天处理了一则复杂关联SQL的优化，这类SQL的优化往往考虑以下四点：

第一.查询所返回的结果集，通常查询返回的结果集很少，是有信心进行优化的;

第二.驱动表的选择至关重要，通过查看执行计划，可以看到优化器选择的驱动表,从执行计划中的rows可以大致反映出问题的所在;

第三.理清各表之间的关联关系，注意关联字段上是否有合适的索引;

第四.使用straight_join关键词来强制表之间的关联顺序，可以方便我们验证某些猜想;

SQL:

执行时间：

1. mysql> select c.yh_id, 
2. -> c.yh_dm, 
3. -> c.yh_mc, 
4. -> c.mm, 
5. -> c.yh_lx, 
6. -> a.jg_id, 
7. -> a.jg_dm, 
8. -> a.jg_mc, 
9. -> a.jgxz_dm, 
10. -> d.js_dm yh_js 
11. -> from a, b, c 
12. -> left join d on d.yh_id = c.yh_id 
13. -> where a.jg_id = b.jg_id 
14. -> and b.yh_id = c.yh_id 
15. -> and a.yx_bj = ‘Y' 
16. -> and c.sc_bj = ‘N' 
17. -> and c.yx_bj = ‘Y' 
18. -> and c.sc_bj = ‘N' 
19. -> and c.yh_dm = '006939748XX' ; 
20.  
21. 1 row in set (0.75 sec) 

这条SQL查询实际只返回了一行数据，但却执行耗费了750ms，查看执行计划：

1. mysql> explain 
2. -> select c.yh_id, 
3. -> c.yh_dm, 
4. -> c.yh_mc, 
5. -> c.mm, 
6. -> c.yh_lx, 
7. -> a.jg_id, 
8. -> a.jg_dm, 
9. -> a.jg_mc, 
10. -> a.jgxz_dm, 
11. -> d.js_dm yh_js 
12. -> from a, b, c 
13. -> left join d on d.yh_id = c.yh_id 
14. -> where a.jg_id = b.jg_id 
15. -> and b.yh_id = c.yh_id 
16. -> and a.yx_bj = ‘Y' 
17. -> and c.sc_bj = ‘N' 
18. -> and c.yx_bj = ‘Y' 
19. -> and c.sc_bj = ‘N' 
20. -> and c.yh_dm = '006939748XX' ; 
21.  
22. +—-+————-+——-+——–+——————+———+———+————–+——-+————-+ 
23. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
24. +—-+————-+——-+——–+——————+———+———+————–+——-+————-+ 
25. | 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where | 
26. | 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index | 
27. | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where | 
28. | 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index | 
29. +—-+————-+——-+——–+——————+———+———+————–+——-+————-+ 

可以看到执行计划中有两处比较显眼的性能瓶颈：

1. | 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where | 
2.  
3. | 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index | 

由于d是left join的表，所以驱动表不会选择d表，我们在来看看a,b,c三表的大小：

1. mysql> select count(*) from c; 
2. +———-+ 
3. | count(*) | 
4. +———-+ 
5. | 53731 | 
6. +———-+ 
7.  
8. mysql> select count(*) from a; 
9. +———-+ 
10. | count(*) | 
11. +———-+ 
12. | 53335 | 
13. +———-+ 
14.  
15. mysql> select count(*) from b; 
16. +———-+ 
17. | count(*) | 
18. +———-+ 
19. | 105809 | 
20. +———-+ 

由于b表的数据量大于其他的两表，同时b表上基本没有查询过滤条件，所以驱动表选择B的可能排除;

优化器实际选择了a表作为驱动表，而为什么不是c表作为驱动表?我们来分析一下：

第一阶段：a表作为驱动表

a–>b–>c–>d:

(1):a.jg_id=b.jg_id—>(b索引:PRIMARY KEY (`JG_ID`,`YH_ID`) )

(2):b.yh_id=c.yh_id—>(c索引:PRIMARY KEY (`YH_ID`))

(3):c.yh_id=d.yh_id—>(d索引:PRIMARY KEY (`JS_DM`,`YH_ID`))

由于d表上没有yh_id的索引，索引在d表上添加索引：

1. alter table d add index ind_yh_id(yh_id); 

执行计划：

1. +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+ 
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
3. +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+ 
4. | 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where | 
5. | 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index | 
6. | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where | 
7. | 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index | 
8. +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+ 

执行时间：

1. 1 row in set (0.77 sec) 

在d表上添加索引后，d表的扫描行数下降到272行(最开始为：54584 )

1. | 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index | 

第二阶段：c表作为驱动表

d

^

|

c–>b–>a

由于在c表上有yh_dm过滤性很高的筛选条件，所以我们在yh_dm上创建一个索引：

1. mysql> select count(*) from c where yh_dm = '006939748XX'; 
2. +———-+ 
3. | count(*) | 
4. +———-+ 
5. | 2 | 
6. +———-+ 

添加索引：

1. alter table c add index ind_yh_dm(yh_dm) 

查看执行计划：

1. +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+ 
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
3. +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+ 
4. | 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where | 
5. | 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index | 
6. | 1 | SIMPLE | c | eq_ref | PRIMARY,ind_yh_dm | PRIMARY | 98 | test.b.YH_ID | 1 | Using where | 
7. | 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index | 
8. +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+ 

执行时间：

1. 1 row in set (0.74 sec) 

在c表上添加索引后，索引还是没有走上，执行计划还是以a表作为驱动表，所以我们这里来分析一下为什么还是以a表作为驱动表?

1):c.yh_id=b.yh_id—>( PRIMARY KEY (`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的索引：

1. alter table b add index ind_yh_id(yh_id); 

2):b.jg_id=a.jg_id—>( PRIMARY KEY (`JG_ID`) )

3):c.yh_id=d.yh_id—>( KEY `ind_yh_id` (`YH_ID`) )

执行计划：

1. +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+ 
2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
3. +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+ 
4. | 1 | SIMPLE | c | ref | PRIMARY,ind_yh_dm | ind_yh_dm | 57 | const | 2 | Using where | 
5. | 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 272 | Using index | 
6. | 1 | SIMPLE | b | ref | PRIMARY,ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 531 | Using index | 
7. | 1 | SIMPLE | a | eq_ref | PRIMARY,INDEX_JG | PRIMARY | 98 | test.b.JG_ID | 1 | Using where | 
8. +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+ 

执行时间：

1. 1 row in set (0.00 sec) 

可以看到执行计划中的rows已经大大降低，执行时间也由原来的750ms降低到0 ms级别;