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级别;

(0)

相关推荐

  • 详解MySQL下InnoDB引擎中的Memcached插件

    前些年,HandlerSocket的横空出世让人们眼前一亮,当时我还写了一篇文章介绍了其用法梗概,时至今日,由于种种原因,HandlerSocket并没有真正流行起来,不过庆幸的是MySQL官方受其启发,研发了基于InnoDB的Memcached插件,总算是在MySQL中延续了NoSQL的香火,以前单独架设Memcached服务器不仅浪费了内存,而且还必须自己维护数据的不一致问题,有了Memcached插件,这些问题都不存在了,而且借助MySQL本身的复制功能,我们可以说是变相的实现了Memca

  • 简单介绍MySQL中的事务机制

    从一个问题开始 最近银行这个事情闹的比较厉害啊,很多储户的钱放在银行,就不翼而飞了,而银行还不管不问,说是用户的责任,打官司,用户还能输了,这就是"社会主义".咱还是少发牢骚,多种树,莫谈国事. 说到银行存钱,就不得不说一下从银行取钱这件事情,从ATM机取钱这件简单的事情,实际上主要分为以下几个步骤: 登陆ATM机,输入密码: 连接数据库,验证密码: 验证成功,获得用户信息,比如存款余额等: 用户输入需要取款的金额,按下确认键: 从后台数据库中减掉用户账户上的对应金额: ATM吐出钱:

  • 几种MySQL中的联接查询操作方法总结

    前言 现在系统的各种业务是如此的复杂,数据都存在数据库中的各种表中,这个主键啊,那个外键啊,而表与表之间就依靠着这些主键和外键联系在一起.而我们进行业务操作时,就需要在多个表之间,使用sql语句建立起关系,然后再进行各种sql操作.那么在使用sql写出各种操作时,如何使用sql语句,将多个表关联在一起,进行业务操作呢?而这篇文章,就对这个知识点进行总结. 联接查询是一种常见的数据库操作,即在两张表(多张表)中进行匹配的操作.MySQL数据库支持如下的联接查询: CROSS JOIN(交叉联接)

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

    昨天处理了一则复杂关联SQL的优化,这类SQL的优化往往考虑以下四点: 第一.查询所返回的结果集,通常查询返回的结果集很少,是有信心进行优化的: 第二.驱动表的选择至关重要,通过查看执行计划,可以看到优化器选择的驱动表,从执行计划中的rows可以大致反映出问题的所在: 第三.理清各表之间的关联关系,注意关联字段上是否有合适的索引: 第四.使用straight_join关键词来强制表之间的关联顺序,可以方便我们验证某些猜想: SQL: 执行时间: mysql> select c.yh_id, ->

  • MySQL在不知道列名情况下的注入详解

    前言 最近感觉脑子空空,全在为了刷洞去挖洞,还是回归技术的本身让自己舒服些.好了,下面话不多说了,来一起看看详细的介绍吧 前提 以下情况适用于 MySQL < 5版本,或者在 MySQL >= 5 的版本[存在information_schema库],且已获取到库名和表名 ① 当只能获取到表名,获取不到列名或者只能获取到无有效内容的列名情况[例如 id] ② 当希望通过information_schema库中的表去获取其他表的结构,即表名.列名等,但是这个库被WAF过滤掉的情况 其实个人感觉这

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

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

  • MySQL异常恢复之无主键情况下innodb数据恢复的方法

    本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法.分享给大家供大家参考,具体如下: 在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复 创建模拟表-无主键 mysql> CREATE TABLE `t1` ( -> `messageId` varchar(30) character set utf8 NOT NULL, -> `tokenId`

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

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

  • MySQL问答系列之什么情况下会用到临时表

    临时表介绍 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间.为什么会产生临时表:一般是由于复杂的SQL导致临时表被大量创建 临时表分为两种,一种是内存临时表,一种是磁盘临时表.内存临时表采用的是memory存储引擎,磁盘临时表采用的是myisam存储引擎(磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,从mysql5.7.6之后

  • 浅谈Mysql在什么情况下会使用内部临时表

    union执行 为了便于分析,使用一下sql来进行举例 CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) ); delimiter ;; CREATE PROCEDURE idata ( ) BEGIN DECLARE i INT; SET i = 1; WHILE ( i <= 1000 ) DO INSERT INTO t1 VALUES ( i, i, i ); SET i = i + 1; END WHILE;

  • Mysql表数据比较大情况下修改添加字段的方法实例

    前言 如果一张表在后期的维护中,发现需要加字段以满足当下的需求,但是数据量很大有百万甚至千万级的数据,要如何修改表字段呢. 直接执行使用alter语句肯定是不现实的,这涉及到锁表重建表结构等操作,假设这时候还有其他线程在跑,等一天都改不过来. 这里整理一个比较简单的方法 1.对照要操作的表结构创建一张临时表 CREATE TABLE product_copy LIKE product; 2.将要修改的表结构改在临时表上面 3.导出表product数据,并导入到零时表product_copy 4.

  • 数据库分库分表是什么,什么情况下需要用分库分表

    数据量在什么情况下需要分表? 为了保证数据库的查询效率,当数据达成一定量时建议进行分表操作 1.oracle 当oracle单表的数据量大于2000万行时,建议进行水平分拆. 2.mysql 当mysql单表的数据量大于1000万行时,建议进行水平分拆. 单表容量到了1000W以上基本上稍微复杂一点的SQL都需要仔细优化,这时候的SQL耗时主要集中在磁盘IO上,数据命令缓存的概率降低,总之不好搞,如果是正常的互联网项目,提前分库分表,在前期能做的先做了,后面会省很多时间处理数据迁移的事情,数据操

  • MySQL JOIN关联查询的原理及优化

    目录 1 关联查询的执行 2 没有索引的算法 1 关联查询的执行 关联查询的执行过程是:先遍历关联表t1(驱动表,全表扫描),然后根据从表t1中取出的每行数据中的a值,去表t2(被关联表,被驱动表)中查找满足条件的记录,可以走t2的索引搜索.在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称NLJ.在join语句的执行流程中,驱动表是走全表扫描,而被驱动表是走索引树搜索. 假设被驱动表的行数是M.每次

随机推荐