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

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

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

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

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

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

SQL:
执行时间：

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)

这条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表上添加索引：

alter table d add index ind_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 | 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 |
+—-+————-+——-+——–+——————+———–+———+————–+——-+————-+

执行时间：

1 row in set (0.77 sec)

在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上创建一个索引：

mysql> select count(*) from c where yh_dm = '006939748XX';
+———-+
| count(*) |
+———-+
| 2 |
+———-+

添加索引：

alter table c add index ind_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 | 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 |
+—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+

执行时间：

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的索引：

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`) )
执行计划：

+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
| 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 | 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 |
+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+

执行时间：

1 row in set (0.00 sec)

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

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
mysql 关联查询优化、mysql关联子查询优化、mysql 大表关联优化、mysql 多表关联优化、solr 关联 复杂 查询，以便于您获取更多的相关知识。

时间： 2024-09-24 14:25:20

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

mysql字段设置某种情况下的特殊值

问题描述 mysql字段设置某种情况下的特殊值 是这样的,上传视频转码成功后url设置到videochangeurl字段,但是有不成功的情况,现在前辈想设置成false,这个字段是varchar类型的,而且前端页面判断是否转码失败jstl的话eq 'false',看着也奇怪,我心里觉得设置false不适合,但是又不好反抗前辈,就算反抗人家也根本不理会你,本着对自己负责,学习的态度,问问大家,这种情况怎么处理好. ps:看了前两个回答,补充下,如果不上传视频的话,没视频的话为为空的,要区别开没不上

mysql不重启的情况下修改参数变量_Mysql

通常来说,更新mysql配置my.cnf需要重启mysql才能生效,但是有些时候mysql在线上,不一定允许你重启,这时候应该怎么办呢? 看一个例子: mysql> show variables like 'log_slave_updates'; +-------------------+-------+| Variable_name     | Value |+-------------------+-------+| log_slave_updates | OFF   |+---------

MySQL在有索引列情况下select *的输出结果顺序

创建一个表格,一个是主键列,一个是索引列.然后插入一批数据,调用select * from test_b,可以发现输出结果并没有按照Id有序,而是按照Type有序. 如果希望按照Id有序,可以使用force index (primary)这一hint语句. mysql> CREATE TABLE `test_b` ( -> `Id` int(11) NOT NULL, -> `Type` int(11) DEFAULT NULL, -> PRIMARY KEY (`Id`), -&

mysql c# 代理-使用代理上网情况下c#如何连接到外网mysql进行查询

问题描述 使用代理上网情况下c#如何连接到外网mysql进行查询 开发环境:vs2010语言:c# 网络环境:使用代理上网由于公司设置不能直接上网,只能通过设置代理进行访问网络.请问c#中如何写mysql连接语句

EF 自关联表主键自增情况下，不能添加记录的问题，有谁解决过？

问题描述 EF 自关联表主键自增情况下,不能添加记录的问题,有谁解决过? 如题,我的系统中因为一个特定原因,需要一张自关联表(thisTable)来简化逻辑, 该表主键是自增的,结果发现无法添加记录, 错误消息:无法确定"db.thisTable"关系的主体端.添加的多个实体可能主键相同. 请问,有人解决过这个问题吗? 解决方案 自己解决了,顺便分享给大家: 在EF的设定里,自关联的表主键外键,都不能为空(为空就会破坏数据的完整性), 这在两表关联时没问题,但在自关联就有一个悖论,那就

mysql在什么情况下免费啊

问题描述 mysql在什么情况下免费啊 mysql在什么情况下免费啊,licence中的内容看不懂. 解决方案 mysql本来就是开源的.免费的啊. 解决方案二: 在自己用的情况下啊,只要不作为商业用途 都可以的啊 解决方案三: MySQL是一种非常流行的开放源代码数据库系统.它不但是一种用途广泛的大型数据库工具,而且完全免费,用户甚至可能不用掏一个子儿就可以很容易地掌握它 解决方案四: 直接就是免费,商业也可以.没什么问题. 解决方案五: 社区版是免费的,商业版的不被发现也没什么问题. 解决方

Yii+MYSQL锁表防止并发情况下重复数据的方法_php实例

本文实例讲述了Yii+MYSQL锁表防止并发情况下重复数据的方法.分享给大家供大家参考,具体如下: lock table 读锁定 如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作. lock tables user read;//读锁定表 unlock tables;//解锁 lock tables user read local;//本地读锁定表,其他线程的insert未被阻塞,update操作被阻塞 lock table 写锁定 如果一个线

服务器上的Mysql表全丢了情况下恢复数据

任务: web项目是在linux的Tomcat部署,Mysql也在上面,不知明原因下数据库宕机,启动不了,数据库表也突然没了,全空了!!!!!!!! mysql安装目录 惊喜的发现var/目录下有类似丢失数据库的表 接下来就是怎么恢复进去了,首先我们用的是mysql的InnoDb引擎,找了下其资料: 两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用. 我作为使用MySQL的用户角度出发,Innodb和M

mysql-unsha1：在未知密码情况下，登录任意MYSQL数据库

本文讲的是mysql-unsha1:在未知密码情况下,登录任意MYSQL数据库, 摘要 这个POC用于在不知道明文密码的情况下对启用了密码安全认证插件(默认开启插件:mysql_native_password)的MYSQL数据库进行登录. 前提条件为: 1.为了获取到已知用户的hash,我们需要读取到目标数据库中的mysql.user表. 2.能够拦截到上述已知用户执行成功的认证信息(即通过SSL认证无法攻击成功). 注意:这并不是MYSQL的一个漏洞,只是认证协议工作的直接后果.如果攻击者已经