mysql5.7子查询优化_通过MySQL5.7子查询的坑联想到的MySQL查询优化规律与注意点

本文基于MySQL 5.7

问题背景

最近，发现业务线程卡死在读取数据库(MySQL 5.7)，数据库CPU飙升到了100%，经过定位发现是下面这样一个SQL(由用户触发的查询，可以理解为查看这个群组的所有用户买过哪些商品)导致的：

select * from merchandise where id in (

select merchandise_id from indent where customer_id in (

select id from customer where name in("zhx","ypy")

)

);

虽然听说MySQL5.6开始已经对于子查询做了很多查询优化，但是看来还是不够啊

MySQL历史追溯

MySQL针对子查询的优化一直不够好，在5.5版本及以前，根据《高性能MySQL》这本书说的，MySQL针对子查询的优化是优化为 exists 的方式来执行，变成逐条记录进行遍历：

在5.7版本，优化并不是这样简单的优化了：而是转换为临时表加半连接优化。

举例分析与半连接优化的局限

假设我们有三张表：

用户表：

drop table if exists customer;

create table if not exists customer(

id varchar(64) primary key,

name varchar(64) unique key,

address text

);

商品表：

drop table if exists merchandise;

create table if not exists merchandise(

id varchar(64) primary key,

name varchar(64) unique key,

description text

);

订单表：

drop table if exists indent;

create table if not exists indent (

id varchar(64) primary key,

customer_id varchar(64),

merchandise_id varchar(64),

key (customer_id, merchandise_id)

);

用户和商品是一种多对多的关系。

假设他们的数据如下：

mysql> select * from customer;

+----+------+--------------------------------+

| id | name | address |

+----+------+--------------------------------+

| 1 | zhx | Xueyuannanlu, Haidian, Beijing |

| 2 | ypy | Zhichunlu, Haidian, Beijing |

| 3 | tt | Dog House, Haidian, Beijing |

+----+------+--------------------------------+

3 rows in set

mysql> select * from merchandise

;

+----+------------+-------------+

| id | name | description |

+----+------------+-------------+

| 1 | Computer | Lenovo |

| 2 | Perfume | Dior |

| 3 | Chocolate | Dove |

| 4 | YummyBones | Pedigree |

| 5 | IPhone | Apple |

+----+------------+-------------+

5 rows in set

mysql> select * from indent

;

+----+-------------+----------------+

| id | customer_id | merchandise_id |

+----+-------------+----------------+

| 1 | 1 | 1 |

| 5 | 1 | 2 |

| 2 | 2 | 2 |

| 3 | 2 | 3 |

| 4 | 3 | 4 |

+----+-------------+----------------+

5 rows in set

假设我们想获取用户"zhx"购买过的所有商品，通过子查询应该是这样实现：

select * from merchandise where id in (

select merchandise_id from indent where customer_id in (

select id from customer where name in ("zhx")

)

);

那这个的查询计划是什么样呢？

通过如下两个命令获取：

explain select distinct merchandise.id, merchandise.name, merchandise.description from merchandise

join indent on merchandise.id = indent.merchandise_id

join customer on indent.customer_id = customer.id

where customer.name in ("zhx");

show warnings;

结果是：

show warnings;拆解出来的执行SQL伪代码是：

/* select#1 */ select `test`.`merchandise`.`id` AS `id`,`test`.`merchandise`.`name` AS `name`,`test`.`merchandise`.`description` AS `description` from

`test`.`customer` semi join (`test`.`indent`) join `test`.`merchandise`

where (

(`test`.`merchandise`.`id` = `test`.`indent`.`merchandise_id`)

and (`test`.`indent`.`customer_id` = '1')

and ('zhx' = 'zhx')

)

可以看到MySQL5.7针对这个子查询确实做了足够的优化，这里我们也看到了，MySQL通过半连接(semi join)优化了这次子查询。同时，由于存在性检查，先拿到了’zhx’的id为1，并且原来的customer.name in ("zhx")其实相当于customer.name = "zhx"，所以条件可以改写为test.indent.customer_id = ‘1’。

我们再来看看出问题的子查询场景，针对某组用户查询购买过得商品：

select * from merchandise where id in (

select merchandise_id from indent where customer_id in (

select id from customer where name in ("zhx","ypy")

)

);

这个语句的查询计划是：

/* select#1 */ select `test`.`merchandise`.`id` AS `id`,`test`.`merchandise`.`name` AS `name`,`test`.`merchandise`.`description` AS `description` from

`test`.`merchandise` semi join (`test`.`customer` join `test`.`indent`)

where (

(``.`merchandise_id` = `test`.`merchandise`.`id`)

and (`test`.`indent`.`customer_id` = `test`.`customer`.`id`)

and (`test`.`customer`.`name` in ('zhx','ypy'))

)

这个居然是个针对merchandise表的全扫描！怪不得线上数据库扛不住。

但是为什么呢？我们参考下MySQL5.7官方文档的查询优化章节：

https://dev.mysql.com/doc/refman/5.5/en/statement-optimization.html

针对大部分IN的子查询语句，会被优化成半连接和中间表的执行机制。

什么是半连接，A semi join B可以理解为A inner join B但是结果只包含B的数据；

中间表比较好理解，就是中间结果保存成一个临时表。

还有A inner join B inner join C和A inner join (B inner join C)的结果应该是一样的。

那么我们可以联想到一个优化，就是如果where条件里面包含C=？这样的条件，那么用A inner join (B inner join C)替换A inner join B inner join C可以提升很大效率。

获取用户"zhx"购买过的所有商品就是用这个定律，从

`merchandise` semi join (`test`.`customer` join `test`.`indent`)

变成了

`test`.`customer` semi join (`test`.`indent`) join `test`.`merchandise`

customer semi join indent表之后还是保留indent表和merchandise表进行join，相当于没有中间表

获取用户组多个用户"zhx","ypy"购买过的所有商品

由于没有优化，所以保持原有的：

`merchandise` semi join (`test`.`customer` join `test`.`indent`)

MySQL的semi join和中间表的优化缺陷在这里就体现出来了：

由于是merchandise去semi join中间结果，但是限制条件里面没有明确的merchandise限制，而且是和中间表进行join，所以针对merchandise进行全扫描。

这个其实很奇怪，为啥优化来优化去，变成了一个效果更差的扫描(这应该是三方面引起，一是semi join，二是没有明确的semi join要保留的表的有索引字段的条件限制，三是semi join的是中间表)

结论

MySQL针对多重子查询的优化还有待提升，最好还是用distinct和join的效率更好。