mysql执行计划解读--大量示例sql语句执行计划-阿里云开发者社区

1 演示环境的搭建

1.1 MySQL数据库的安装

在LInux上安装MySQL有多种方式可以选择，最简单的是使用rpm包安装，这种方式的缺点是提供的安装选项不多，不能选择自己的数据目录、安装目录等。最灵活的安装方式是源码编译安装，可以根据需要选择编译的功能模块，但是安装过程比较复杂。在灵活性和安装复杂度折衷一点的选择是二进制包安装，可以根据需要选择安装目录和数据目录，安装过程也不算太复杂，建议选择这种方式进行MySQL安装。

从MySQL官网下载数据库二机制安装包，这里选择的MySQL5.7的最新版本，下载后解压到/usr/local目录， tar的-C选项指定解压目录。

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# tar -xvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local

切换到/usr/local目录，创建一个软链接mysql连接至解压后的安装软件，创建软链接的目的是管理和使用的方便，如果在一台服务器上安装了多个版本的MySQL，只需要将mysql软链接删除，链接至不同版本的安装软件即可实现版本的切换。

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# cd /usr/local

[root@iZ2ze0t8khaprrpfvmevjiZ local]# ln -s mysql-5.7.34-linux-glibc2.12-x86_64 mysql

[root@iZ2ze0t8khaprrpfvmevjiZ local]# ls -l

total 0

drwxr-xr-x 7 root root 160 Jul 28 16:33 aegis

drwxr-xr-x. 2 root root 210 Jul 20 17:27 bin

drwxr-xr-x. 2 root root 6 Jun 22 2021 etc

lrwxrwxrwx 1 root root 35 Aug 9 13:55 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64

drwxr-xr-x 9 root root 129 Aug 9 13:53 mysql-5.7.34-linux-glibc2.12-x86_64

将MySQL执行文件所在的目录加入到PATH环境变量中，这样在运行MySQL相关命令时不用切换到mysql 命令所在的目录。

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bash_profile

运行一下用户默认登录脚本，使新设置的PATH变量生效

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# source .bash_profile

检查当前环境变量，MySQL执行文件所在目录已加入到PATH环境变量之中。

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# env|grep -i path

DBUS_SESSION_BUS_ADDRESS=unix:path=/run/user/0/busPATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/usr/local/mysql/bin

创建mysql用户组

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# groupadd mysql

创建mysql用户，用户的shell设置为/bin/false，禁止mysql用户登录。

[root@iZ2ze0t8khaprrpfvmevjiZ ~]# useradd -r -g mysql -s /bin/false mysql

创建mysql数据目录，改变目录的属主为mysql：mysql

[root@iZ2ze0t8khaprrpfvmevjiZ mysql]# mkdir /mysqldata

[root@iZ2ze0t8khaprrpfvmevjiZ mysql]# chown mysql:mysql /mysqldata

1.2 MySQL数据库初始化及启动

MySQL的安装环境准备好之后就可以对数据库进行初始化了，初始化时要指定数据库的操作系统用户，因为我这里使用的不是默认目录，所以也要指定数据目录。

[root@iZ2ze0t8khaprrpfvmevjiZ bin]# mysqld --initialize --user=mysql --datadir=/mysqldata

2022-08-09T06:07:51.802610Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2022-08-09T06:07:52.836022Z 0 [Warning] InnoDB: New log files created, LSN=45790

2022-08-09T06:07:52.936636Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2022-08-09T06:07:53.014709Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9aaac001-17a9-11ed-b423-00163e2eafc6.

2022-08-09T06:07:53.017913Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2022-08-09T06:07:54.308559Z 0 [Warning] CA certificate ca.pem is self signed.

2022-08-09T06:07:54.512118Z 1 [Note] A temporary password is generated for root@localhost: R_!Y2zfqq5kC

初始化成功后会创建一个临时的root账号密码，记下此密码后启动数据库，用mysqld_safe命令，同样需要指定操作系统用户和数据目录，放入后台运行。

[root@iZ2ze0t8khaprrpfvmevjiZ bin]# mysqld_safe --user=mysql --datadir=/mysqldata &

登录数据库，输入上面显示的临时root密码

[root@iZ2ze0t8khaprrpfvmevjiZ bin]# mysql -uroot -p

Enter password:

在运行命令时提示在执行命令之前必须重置密码

mysql> show databases;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

使用下面的命令重置root密码，退出数据库使用重置后的密码登录即可执行查询命令。

mysql> ALTER USER USER() IDENTIFIED BY "******";

Query OK, 0 rows affected (0.00 sec)

mysql> select version();

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

| version() |

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

| 5.7.34 |

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

1 row in set (0.00 sec)

1.3 导入sakila示例数据库

从MySQL官网上下载sakila示例数据库的创建脚本，解压后的脚本文件有两个，sakila-schema.sql创建sakila数据库及表和索引，sakila-data.sql数据库插入数据。这里的实验环境时ECS，最初从ECS控制台上用文件上传，试了之后发现上传文件最大限制为１６ｋ，改用psftp上传，psftp时putty的上传工具，只要服务端打开了ssh服务就可以使用，十分方便。

屏幕截图(1127).png

上传的目录是/root/,切换到这个目录，登录MySQL数据库运行这两个脚本即可。

mysql> source sakila-schema.sql

mysql> source sakila-data.sql

1.4 主要示例表介绍

本文使用sakila中的比较简答的city表来演示，这个表数据量、列数比较少，也有外键，主键，可以说是麻雀虽小，五脏俱全。下面是表的定义

mysql> show create table city\G;

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`city` varchar(50) NOT NULL,

`country_id` smallint(5) unsigned NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`city_id`),

KEY `idx_fk_country_id` (`country_id`),

CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

表上定义了两个索引

mysql> show index from city;

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

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

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

2 rows in set (0.00 sec)

2 MySQL数据库执行计划

先从一个简单的例子看一下MySQL的执行计划，查看的命令是explain，使用explain查看sql语句的执行计划并不执行sql语句，只是解释输出语句的执行计划，对数据库不会造成什么影响，可以放心使用。

mysql> explain select city from city;

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

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

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

1 row in set, 1 warning (0.00 sec)

上面的语句是MySQL数据库中最简单的语句，从上面可以看到，执行计划的输出有好多列，这些列的信息对推测判断sql语句的性能都有或大或小的帮助。有些列的信息非常重要，比如table，type，possible_keys，key，extra这些列，通过这些列的信息有时可以直接判断出sql语句的问题处在哪里，应该怎样对其优化。比如对上面的语句，从type的值ALL可以判断出语句执行了全表扫描，而扫描的对象是table的值city，语句的执行过程中没有可以使用的索引。

3 MySQL执行计划关键信息解释

3.1 id

id是执行计划的第一列，即操作的序列号，如果没有子查询或者是union，就只有一个查询，各行的id值是1，否则，内部的sql语句就会按序号标识。执行计划输出的每一行是关于一个表的操作，执行的顺序是从上到下依次执行。执行计划的id号并不总是从小到大排列，即id小的操作并不总是在id大的操作之前执行，下面是一个例子：

mysql> explain select a.customer_id, a.address_id, a.first_name, a.last_name

from customer a inner join address b on a.address_id=b.address_id

inner join city c on b.city_id =c.city_id

where c.country_id in (select country_id from country where country<>"Yemen"

union

select country_id from country where country<>"Vietnam")

and a.store_id not in (select store_id from store where manager_staff_id=2);

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

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

| 1 | PRIMARY | a | NULL | ALL | idx_fk_address_id | NULL | NULL | NULL | 599 | 100.00 | Using where |

| 1 | PRIMARY | b | NULL | eq_ref | PRIMARY,idx_fk_city_id | PRIMARY | 2 | sakila.a.address_id | 1 | 100.00 | NULL |

| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.city_id | 1 | 100.00 | Using where |

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

上面的语句既有子查询也有联合（union），执行计划输出中各行的id并不都是1，对于store子查询的id是4，却在对于country的子查询（id为2）和对于country的union（id为3）之前执行。把上面的语句的where条件的顺序变一下，再看一下执行计划

mysql> explain select a.customer_id, a.address_id, a.first_name, a.last_name

from customer a inner join address b on a.address_id=b.address_id

inner join city c on b.city_id =c.city_id

where

a.store_id not in (select store_id from store where manager_staff_id=2) and

c.country_id in (select country_id from country where country<>"Yemen"

union

select country_id from country where country<>"Vietnam");

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

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

| 1 | PRIMARY | a | NULL | ALL | idx_fk_address_id | NULL | NULL | NULL | 599 | 100.00 | Using where |

| 1 | PRIMARY | b | NULL | eq_ref | PRIMARY,idx_fk_city_id | PRIMARY | 2 | sakila.a.address_id | 1 | 100.00 | NULL |

| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.city_id | 1 | 100.00 | Using where |

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

7 rows in set, 1 warning (0.00 sec)

对于store的子查询的id变成了2，执行顺序却放到了最后。从上面的两个sql的输出来看，操作的id好像与出现在where条件的位置有关，不过不能十分肯定。但是可以肯定一点的是，对于复杂的sql语句，where条件的执行顺序可能会影响到执行计划的选择，有可能会应影响到语句的执行性能，MySQL数据库最好不要执行复杂的sql语句。

3.2 select_type，possible keys， key，table，ref

table：操作的行所在的表，可以是索引，表，union， derived（派生表），子查询（物化的子查询）。

possible_keys：可用的索引

key：实际用到的索引

ref：哪一些列或常量被用来同索引（key列）比较来从表中选择行，如果这一列的值是func，使用的值是某些函数计算的结果，具体使用哪个func可以在explain语句后使用showwarning来查看。

执行的类型，simple是指简单的select操作，没有用到子查询或联合。primary是指最外层的查询，subquery，子查询里的第一个查询，dependent subquery，子查询里的第一个查询，依赖于外层查询，derived 派生表，MATERIALIZED 物化子查询，UNCACHEABLE SUBQUERY，子查询的结果不能被缓存，对外层查询的每一行，必须重新运行子查询。依赖意味着运行了关联子查询。

在编写sql时，要尽可能避免dependent subquery，UNCACHEABLE SUBQUERY，这两个类型的子查询一是影响性能，而是优化器无法对其进行优化

除了select操作外，类型也可能时insert、delete和update，下面是一个insert的例子

mysql> explain insert into city(city, country_id) values ('beitang', 108), ('johntown', '109');

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

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

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

1 row in set (0.00 sec)

insert的执行计划里只有select type和table，其它的信息都为空。MySQL的insert要执行主键和唯一键的唯一性检测，应该会用到主键索引，执行计划里并没有显示。

mysql> explain delete from city where city_id=108;

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

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

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

1 row in set (0.00 sec)

delete的执行计划里可以看到使用了主键，从上面的执行计划可以看出，语句执行的可用键是主键（possible_keys),实际使用的键也是主键（key），用常量ref值为const和主键作比较，因此，在执行delete操作时，要考虑索引的使用，在单行删除时尽可能使用主键或唯一索引作为删除条件。

mysql> explain delete from city where country_id=108;

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

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

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

1 row in set (0.00 sec)

非唯一索引也可以用来加速delete操作。

再看一个两表关联的例子

mysql> explain select a.city, b.country from city a , country b where a.country_id=b.country_id;

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

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

| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |

| 1 | SIMPLE | a | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.b.country_id | 1 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

从上面的执行计划可以看出，mysql先对表b进行全表扫描（执行计划的第一行），可用的keys是主键，因为是全表扫描，所以实际上没有使用主键，扫描出了109行。

从执行计划的第二行可以看出，用b表的country_id（ref）同a表的索引idx_fk_country_id进行比较匹配，连接后进行输出。

在上面的sql语句中加上一个对a表的限定条件后，再看一下执行计划。

mysql> explain select a.city, b.country from city a , country b where a.country_id=b.country_id and a.city_id=1;

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

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

| 1 | SIMPLE | a | NULL | const | PRIMARY,idx_fk_country_id | PRIMARY | 2 | const | 1 | 100.00 | NULL |

| 1 | SIMPLE | b | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

在a表加了限定条件后上面的查询的意义是查询city_id为1 的城市的名称及其所属国，家，对表a（city）的查询可用的索引有两个，因为查询条件是主键，所以实际查询时采用了主键，用常量和主键进行比较，获得了一行数据。从第二行可以看出，对b表的查询也是用常量（a表的输出是一行数据）和b表的主键进行匹配。

3.3 type

type,在MySQL官方文档中是join类型，实际是dba常说的访问类型。不同的访问类型在性能上有很大的差异，在编写sql和sql诊断时，首先要判断type是否合适。在执行计划中，可以看到以下几种访问类型。

NULL：MySQL在优化阶段就会解决查询，取得查询的值，不需要访问表。

system：当表中只有一行时，访问类型是system，system可以看作是const的特例。看一个例子：

mysql> explain select * from (select now()) a;

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

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

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

2 rows in set, 1 warning (0.00 sec)

上面的执行计划中，第二个派生表的join类型是null，意味着在优化阶段就取得了now（）的值，后面的extra的值是No tables used意思是在执行阶段没有访问任何表。第一个操作的类型是system，访问的表是派生表2（第二个操作产生的派生表），这个派生表只有一行，因此访问类型是system。

const：表中最多只有一个匹配的行。由于只读一次表，const表的速度非常快，当主键或唯一键同常量比较时MySQL会用到const。用主键查询一行数据就会用到const，像下面的例子：

mysql> explain select city from city where city_id=1;

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

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

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

1 row in set, 1 warning (0.00 sec)

eq_ref:对前面每一个表的行组合，从表中读取一行。这是除了const和system之外最好的join类型，使用时的索引是主键和非空唯一键。

mysql> explain select a.city from city a , address b where a.city_id=b.city_id and b.postal_code<23616;

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

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

| 1 | SIMPLE | b | NULL | ALL | idx_fk_city_id | NULL | NULL | NULL | 603 | 33.33 | Using where |

| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.city_id | 1 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

第一个操作返回多行数据（603），在第二个操作中使用第一个操作中的city_id匹配表city中的数据，第一个操作中的每一个city_id匹配第二个操作的表（a的主键）中的一行数据，访问类型是eq_ref.

可以看出，当索引列用等号比较时，可以用到eq_ref.

ref : 对于外表中的行，从表中读取多行。可以用于等于或不等于操作符，或者是非唯一索引的情况下，比如下面的例子：

mysql> explain select a.city, b.country from city a , country b where a.country_id=b.country_id;

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

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

| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |

| 1 | SIMPLE | a | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.b.country_id | 1 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

对于b（country）表的每一country_id, city表中有多行数据对应，join类型就是ref。

range：检索指定范围的值。key列值出用到的索引。

mysql> explain select * from city where city_id<3;

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

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

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

1 row in set, 1 warning (0.00 sec)

index和all：这两个都是全扫，index扫描的对象时索引，all扫描的是表，表的全扫比较常见，这里给出一个对索引进行全扫的例子。

mysql> explain select country_id from city;

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

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

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

1 row in set, 1 warning (0.00 sec)

3.4 extra

extra列里有时会包含非常重要的信息，在查看执行计划时，尤其要关注extra列里的using filesort和using temporary ,这表示在语句的执行过程中执行了排序或临时表

的创建。

Using filesort:MySQL必须一次额外的排序操作。必须重新扫描一遍获得的数据，存储排序key和指向行的指针。然后将key排序，按照顺序检索行。可以看出这个操作非常消耗资源，对sql语句执行的性能影响是巨大的。

mysql> explain select city ,last_update from city order by last_update;

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

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

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

1 row in set, 1 warning (0.01 sec)

extra列的信息是Using filesort，MySQL尽可能使这个filesort在内存中完成，如果需要排序的行过多，就会在磁盘上执行，这回进步降低性能。可以在last_update列上创建索引以优化这个sql，由于索引是有序的，也就避免了额外的排序操作。

using temporary：为了获得sql语句的执行结果，mysql必须创建一个临时表用来存储中间结果，这个通常发生在order和group by使用了不同的列时。比如下面的例子：

mysql> explain select a.country, a.country_id, count(b.city_id) from country a, city b where a.country_id=b.country_id g

roup by a.country_id order by a.country;

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

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

| 1 | SIMPLE | a | NULL | index | PRIMARY | PRIMARY | 2 | NULL | 109 | 100.00 | Using temporary; Using filesort |

| 1 | SIMPLE | b | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.a.country_id | 5 | 100.00 | Using index |

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

2 rows in set, 1 warning (0.00 sec)

这里group的列是country_id,排序的列是country_name,extra的值是Using temporary; Using filesort。

Using join buffer：join时出现这个信息是MySQL将前面表的结果的一部分存储到join 缓冲区内，和内表进行匹配，这样比每一行做一次匹配性能上要提高不少。看例子之前先做点准备工作，创建两个没有索引的表。

mysql> create table country_no_idx as select * from country;

Query OK, 109 rows affected (0.03 sec)

Records: 109 Duplicates: 0 Warnings: 0

mysql> create table city_no_idx as select * from city;

Query OK, 600 rows affected (0.03 sec)

Records: 600 Duplicates: 0 Warnings: 0

下面的执行计划使用了join buffer

mysql> explain select a.country, b.city from country_no_idx a, city_no_idx b where a.country_id=b.country_id;

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

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

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 109 | 100.00 | NULL |

| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 600 | 10.00 | Using where; Using join buffer (Block Nested Loop) |

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

2 rows in set, 1 warning (0.00 sec)

join buffer是MySQL对嵌套连接的一种优化，优化效果未必理想，可以使用索引来避免使用join buffer，具体做法是在内表（如果是内连接，在行数较大的表上）上创建一个索引。

4 使用物化优化子查询

MySQL优化器使用物化优化子查询。所谓的物化创建一个临时表，用来缓存子查询的结果，这个表通常在内存中创建。当MySQL第一次需要子查询的结果时，对子查询进行物化，优化器也可能对物化的临时表创建哈希所以以加速查询。物化临时表尽可能存在与内存中，表太大时会在磁盘上存储。

下面是子查询物化优化的一个例子。

mysql> explain select * from city where country_id in (select country_id from country_no_idx where country_id=100);

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

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

| 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | const | 6 | 100.00 | Using index condition |

| 1 | SIMPLE | | NULL | eq_ref | | | 2 | sakila.city.country_id | 1 | 100.00 | Using where |

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

3 rows in set, 1 warning (0.00 sec)

从上面例子中的id为3的操作中看到，MySQL优化器对子查询的结果进行了物化，并且创建了哈希索引，这从id 2操作的possible keys和key的值可以看出。

对子查询进行物化虽然对sql的性能有一定提升，但是频繁的创建物化临时表对内存和性能都会有一定的压力，对这类子查询进一步优化的措施是子查询使用唯一索引或主键查询，

mysql> explain select * from city where country_id in (select country_id from country where country_id=100);

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

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

| 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | const | 6 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

mysql执行计划解读--大量示例sql语句执行计划

1 演示环境的搭建

1.1 MySQL数据库的安装

1.2 MySQL数据库初始化及启动

1.3 导入sakila示例数据库

1.4 主要示例表介绍

2 MySQL数据库执行计划

3 MySQL执行计划关键信息解释

3.1 id

3.2 select_type，possible keys， key，table，ref

3.3 type

3.4 extra

4 使用物化优化子查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

mysql执行计划解读--大量示例sql语句执行计划

1 演示环境的搭建

1.1 MySQL数据库的安装

1.2 MySQL数据库初始化及启动

1.3 导入sakila示例数据库

1.4 主要示例表介绍

2 MySQL数据库执行计划

3 MySQL执行计划关键信息解释

3.1 id

3.2 select_type，possible keys， key，table，ref

3.3 type

3.4 extra

4 使用物化优化子查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像