TodayMySQL Database column introduces related execution plans.
The third blog in the MySQL series, the main content is the analysis of the Explain execution plan in MySQL. If you already know how to analyze the execution plan, then for SQL Tuning is easy.
Looking at the job requirements of first- and second-tier major manufacturers, those who design databases will definitely require experience in SQL tuning. This has almost become an "eight-part essay" interview question on par with Spring. .
To perform SQL tuning, you first need to know the execution status of SQL. The most intuitive feeling is of course the execution time of the SQL statement. However, in addition, we can also analyze the SQL statement through the execution plan. execution to perform tuning.
The Explain statement can view how MySQL executes this SQL statement, including the use of indexes, the number of scanned rows, etc. This information is very useful for SQL tuning. It is important, so you must first understand the execution plan.
mysql> explain select * from user where name='one'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| 1 | SIMPLE | user | NULL | ref | a | a | 13 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码
The above is the execution plan of a simple query statement. This table has a total of 12 fields, each representing different meanings. They are described one by one below.
id: Indicates the order of SQL execution. The larger the value, the higher the priority. If the values are the same, the order of execution is determined by the optimizer. select_type: Indicates the type of the select query statement table: The table name (or alias of the table) queried by the SQL statement, or possible It is a non-existent table such as a temporary tablepartitions: Partition information involved in the query statementtype: Association type (access type), determined Learn how MySQL finds rows in a table. The performance from worst to best is ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
possible_keys: Shows that the query statement can be used All indexes key: Shows the index name decided by the optimizer key_len: Shows the number of bytes used by MySQL for the index length ref: Column or constant used to find a value in the index of the key column record rows: Scan Estimation of the number of rowsfiltered: The percentage of the number of rows that ultimately satisfy the query statement to the total number of rows returned by the storage engineExtra: Other execution information The above is just a noun explanation of each field of the execution plan table. Next, I will use actual examples to help everyone (myself) better understand themselect_type, type, key_len, rows, Extra These important fields.
First introduce the sample table structure and data rows that will be used in this article:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(36) DEFAULT NULL COMMENT '姓名', `age` int(11) NULL DEFAULT NULL COMMENT '年龄', `email` varchar(36) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_age_name`(`age`, `name`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1;复制代码
Insert 1,000,000 pieces of test data into the table through a function.
CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin
declare i int;
set i=1;
while(i<=1000000)do
insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'name@email.cn')); set i=i+1;
end while;end复制代码The select_type field in the execution plan indicates the type of the select query statement. Common types are:
SIMPLE: Simple query statement, excluding subqueries and associations, such as: mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
If the query statement contains any complex subparts , then the outermost part will be marked as PRIMARY, such as:
mysql> explain select * from user where id=(select id from user where id=1); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| 1 | PRIMARY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)复制代码
In the execution plan of this SQL statement, the first executed SQL is select * from yser where id = (...) is marked as PRIMARY
The subquery contained in the select or where content will be marked as SUBQUERY. For example, the second statement in the execution plan of the previous example SQL, that is, select_type of select id from user where id=1 will be marked as SUBQUERY. Tagged
2.2.3 DERIVEDThe subquery contained after the FROM keyword (that is, the result of the subquery is regarded as a "table"), the subquery that is regarded as a "table" will be marked is
, and the result will be stored in a temporary table, such as: <pre class="brush:php;toolbar:false">mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码</pre>As you can see from the execution plan, the second executed SQL is select id, The query type of name,count(*) from user where id=1 is
.There are 12 query types in total. For specific explanations, please see the official document-explain_select_typeselect_type
2.3 type in Explain The
type 字段的值性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。
为了能更好地理解各个类型的含义，我对上述每一种类型都举出了相应的示例。
并未全部列出，完整的解释可以看官方文档-EXPLAIN Join Types
ALL 表示全表扫描，意味着存储引擎查找记录时未走索引，所以它是性能最差的一种访问类型，如
mysql> explain select * from user where age+2=20; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1002301 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码
可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值实际为估算），如果在生产环境有这样的 SQL，绝对是要优化的。
我们知道在 where 查询条件中，不应该对查询字段使用函数或表达式（应该写在等号不等号右侧），不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。
这条查询语句在优化后应该是: select * from user where age=18，去掉等号左侧的表达式，优化后的执行计划如下:
mysql> explain select * from user where age=18; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39360 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
index 表示全索引树扫描，由于扫描的是索引树，所以比 ALL 形式的全表扫描性能要好。
同时，由于索引树本身就是有序的，可以避免排序。
mysql> explain select id,age from user where name='name1'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| 1 | SIMPLE | user | NULL | index | NULL | idx_age_name | 116 | NULL | 1002301 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码
示例查询语句如上所述，当查询条件存在于联合索引 idx_age_name 中，但又无法直接使用该索引（由于最左前缀原则），同时查询列 id,age 也存在于联合索引中，无须通过回表来获取时，执行计划中的访问类型 type 列就会是 index。
range 表示范围扫描，准确的说是基于索引树的范围扫描，扫描的是部分索引树，所以性能比 index 稍好。
需要注意的是，若使用 in 或者 or 时，也可以使用范围扫描。
mysql> explain select * from user where age>18 and age<20; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 36690 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec) mysql> explain select * from user where age=18 or age=20; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 78720 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码
index_merge 即索引合并，它表示在查询时 MySQL 会使用多个索引。
MySQL 在 where 语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的结果进行合并，如：
mysql> explain select * from user where id=1 or age=18; +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| 1 | SIMPLE | user | NULL | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4 | NULL | 39361 | 100.00 | Using sort_union(idx_age_name,PRIMARY); Using where | +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码
上面这条查询语句中的 id=1 和 age=18 分别使用到了 PRIMARY 主键索引和 idx_age_name 联合索引，最后再将满足这两个条件的记录进行合并。
ref 表示索引访问（索引查找），这种访问类型会出现在查询条件中以非聚簇索引列的常量值进行查询的情况。
比如在介绍全表扫描中优化后 SQL 的访问类型就是 ref。
eq_ref 这种访问类型会出现在连接查询时，通过聚簇索引进行连接的情况，此类型最多只返回一条符合条件的记录。若表的聚簇索引为联合索引，所有的索引列必须是等值查询，如：
mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| 1 | SIMPLE | user1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1002301 | 100.00 | NULL | | 1 | SIMPLE | user2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | all_in_one.user1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码
const 这种访问类型会出现在通过聚簇索引进行常量等值查询的情况，如：
mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已经提到过关于索引长度的计算方式，这里再来总结一下。
字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 字符长度 + 是否默认NULL + 是否是变长字段*，其中：
字段定义长度 就是定义表结构时跟在字段类型后括号中的数字字符长度 是常数，utf8=3, gbk=2, latin1=1
是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0是否是变长字段 也是常数，若该字段为变长字段，该值为2；否则该值为0所谓的变长字段就是 varchar，它所占用的就是字段实际内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。
举个例子，为上述实例表中添加一个字符类型字段的索引。
alter table user add index idx_name(`name`);复制代码
然后通过 name 字段去做查询，查看执行计划。
mysql> explain select * from user where name='name1'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_name | idx_name | 111 | const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)复制代码
可以看到，执行计划中 key_len 一列的值为 111。
根据上述索引长度的计算公式，name 列字段定义长度为36，字符集类型为默认的 utf8，该字段默认允许 NULL，同时该字段是可变长字段 varchar。
所以 idx_name 索引的索引长度=36*3+1+2=111，恰如执行计划中显示的值。
对于定长类型的字段，其索引长度与它的数据类型长度是一致的。
| 数据类型 | 长度 |
|---|---|
| int | 4 |
| bigint | 8 |
| date | 3 |
| datetime | 8 |
| timestamp | 4 |
| float | 4 |
| double | 8 |
需要注意的是，若该字段允许默认值为 NULL，与字符类型一样，其索引长度也需要加上1。
mysql> explain select * from user where age=1; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39366 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但由于 age 字段默认允许为 NULL，所以它的索引长度就变成了5。
扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。
扫描行数就是优化器考量索引执行效率的因素之一，一般而言扫描行数越少，执行效率越高。
执行计划中 Extra 字段的常见类型有：
Using index: 使用了覆盖索引，以避免回表Using index condition: 使用了索引下推，具体可以看我的上一篇博客 —— 我所理解的MySQL(二)索引Using where: 表示MySQL 会通过 where 条件过滤记录Using temporary: MySQL 在对查询结果排序时会使用临时表
Using filesort: 对结果进行外部索引排序（文件排序），排序不走索引相关免费学习推荐：mysql数据库(视频)
The above is the detailed content of My understanding of MySQL Part 3: Execution plan. For more information, please follow other related articles on the PHP Chinese website!