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),