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

《Mysql必读MySQL在关联复杂情况下所能做出的一些优化》要点：
本文介绍了Mysql必读MySQL在关联复杂情况下所能做出的一些优化，希望对您有用。如果有疑问，可以联系我们。

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

MYSQL数据库??? 第一.查询所返回的结果集,通常查询返回的结果集很少,是有信心进行优化的；

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

MYSQL数据库??? 第三.理清各表之间的关联关系,注意关联字段上是否有合适的索引；

MYSQL数据库??? 第四.使用straight_join关键词来强制表之间的关联顺序,可以方便我们验证某些猜想；

MYSQL数据库SQL:
执行时间：

MYSQL数据库
mysql> select c.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_dm yh_js
-> from a,b,c
-> left join d on d.yh_id = c.yh_id
-> where a.jg_id = b.jg_id
-> and b.yh_id = c.yh_id
-> and a.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yh_dm = '006939748XX' ;
1 row in set (0.75 sec)

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

MYSQL数据库
mysql> explain
-> select c.yh_id,c
-> left join d on d.yh_id = c.yh_id
-> where a.jg_id = b.jg_id
-> and b.yh_id = c.yh_id
-> and a.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yh_dm = '006939748XX' ;
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index |
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+

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

MYSQL数据库
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index |

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

MYSQL数据库
mysql> select count(*) from c;
+―――-+
| count(*) |
+―――-+
| 53731 |
+―――-+
mysql> select count(*) from a;
+―――-+
| count(*) |
+―――-+
| 53335 |
+―――-+
mysql> select count(*) from b;
+―――-+
| count(*) |
+―――-+
| 105809 |
+―――-+

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

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

MYSQL数据库第一阶段：a表作为驱动表
aC>bC>cC>d:
(1):a.jg_id=b.jg_id―>(b索引:PRIMARY KEY (`JG_ID`,`YH_ID`) )

MYSQL数据库(2):b.yh_id=c.yh_id―>(c索引:PRIMARY KEY (`YH_ID`))

MYSQL数据库(3):c.yh_id=d.yh_id―>(d索引:PRIMARY KEY (`JS_DM`,`YH_ID`))
由于d表上没有yh_id的索引,索引在d表上添加索引：

MYSQL数据库
alter table d add index ind_yh_id(yh_id);

MYSQL数据库执行计划：

MYSQL数据库
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+

MYSQL数据库执行时间：

MYSQL数据库
1 row in set (0.77 sec)

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

MYSQL数据库
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |

MYSQL数据库第二阶段：c表作为驱动表

MYSQL数据库d
^
|
cC>bC>a
由于在c表上有yh_dm过滤性很高的筛选条件,所以我们在yh_dm上创建一个索引：

MYSQL数据库
mysql> select count(*) from c where yh_dm = '006939748XX';
+―――-+
| count(*) |
+―――-+
| 2 |
+―――-+

MYSQL数据库添加索引：

MYSQL数据库
alter table c add index ind_yh_dm(yh_dm)

MYSQL数据库查看执行计划：

MYSQL数据库
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY,ind_yh_dm | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+

MYSQL数据库执行时间：

MYSQL数据库
1 row in set (0.74 sec)

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

MYSQL数据库1):c.yh_id=b.yh_id―>( PRIMARY KEY (`JG_ID`,`YH_ID`) )

MYSQL数据库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的索引：

MYSQL数据库
alter table b add index ind_yh_id(yh_id);

MYSQL数据库2):b.jg_id=a.jg_id―>( PRIMARY KEY (`JG_ID`) )

MYSQL数据库3):c.yh_id=d.yh_id―>( KEY `ind_yh_id` (`YH_ID`) )
执行计划：

MYSQL数据库
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+
| 1 | SIMPLE | c | ref | PRIMARY,ind_yh_dm | ind_yh_dm | 57 | const | 2 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 272 | Using index |
| 1 | SIMPLE | b | ref | PRIMARY,ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 531 | Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY,INDEX_JG | PRIMARY | 98 | test.b.JG_ID | 1 | Using where |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+

MYSQL数据库执行时间：

MYSQL数据库
1 row in set (0.00 sec)

MYSQL数据库可以看到执行计划中的rows已经大大降低,执行时间也由原来的750ms降低到0 ms级别；

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!