时间:2021-05-23
昨天处理了一则复杂关联SQL的优化,这类SQL的优化往往考虑以下四点:
第一.查询所返回的结果集,通常查询返回的结果集很少,是有信心进行优化的;
第二.驱动表的选择至关重要,通过查看执行计划,可以看到优化器选择的驱动表,从执行计划中的rows可以大致反映出问题的所在;
第三.理清各表之间的关联关系,注意关联字段上是否有合适的索引;
第四.使用straight_join关键词来强制表之间的关联顺序,可以方便我们验证某些猜想;
SQL:
执行时间:
这条SQL查询实际只返回了一行数据,但却执行耗费了750ms,查看执行计划:
mysql> explain-> 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' ;+—-+————-+——-+——–+——————+———+———+————–+——-+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——–+——————+———+———+————–+——-+————-+| 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 |+—-+————-+——-+——–+——————+———+———+————–+——-+————-+可以看到执行计划中有两处比较显眼的性能瓶颈:
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where || 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index |由于d是left join的表,所以驱动表不会选择d表,我们在来看看a,b,c三表的大小:
mysql> select count(*) from c;+———-+| count(*) |+———-+| 53731 |+———-+mysql> select count(*) from a;+———-+| count(*) |+———-+| 53335 |+———-+mysql> select count(*) from b;+———-+| count(*) |+———-+| 105809 |+———-+由于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表上添加索引:
执行计划:
执行时间:
在d表上添加索引后,d表的扫描行数下降到272行(最开始为:54584 )
| 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上创建一个索引:
添加索引:
查看执行计划:
执行时间:
在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的索引:
2):b.jg_id=a.jg_id—>( PRIMARY KEY (`JG_ID`) )
3):c.yh_id=d.yh_id—>( KEY `ind_yh_id` (`YH_ID`) )
执行计划:
执行时间:
可以看到执行计划中的rows已经大大降低,执行时间也由原来的750ms降低到0 ms级别;
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
查询mysql表是否被损坏命令,如下:#CHECKTABLE表名mysql的长期使用,肯定会出现一些问题,一般情况下mysql表无法访问,就可以修复表了,优化时
MySQL升级是非常必要的.我们在PerconaSupport上列出了关于MySQL升级最佳实践的各种问题.这篇文章推荐了一些不同情况下升级MySQL的方法.为
在使用matplotlib画图时,少不了对性能图形做出一些说明和补充。一般情况下,loc属性设置为'best'就足够应付了plt.legend(handles=
在天猫店铺售卖的情况下,商家一般会被顾客掌握许多內容的,有一些是和店面拥有立即的关联的,可是有一些可能是看起来没什么很大的关联的,可是顾客问的难题实际上全是有针
前言众所周知在默认情况下,我们安在阿里云上的mysql是不支持远程连接的,但是我们还需要通过一些工具来连接mysql,如navicat,这时就需要我们来修改my