Detailed explanation of MySQL 5.7 optimization: Explain execution plan
mysql video tutorialColumn introductionExplain execution plan
##Recommended (free ): mysql video tutorial
Table of contents
- 1. Introduction
- 2. Explain the result column Detailed explanation
- 2.1 id
- 2.2 select_type
- 2.3 table
- 2.4 partitions
- 2.5 type (very important )
- 2.6 possible_keys
- 2.7 key
- 2.8 key_len
- 2.9 ref
- 3.10 rows
- 2.11 filtered
- 2.12 Extra
##[Note]
- MySQL 5.7
- , other versions are omitted If there are differences, I will take time to explain them separately later.
Only common scenarios are introduced. Other rare scenarios will not be studied for the time being. If necessary, you can find them in the official documents.
- Not for beginners, you need to have a certain understanding of MySQL
- ’s underlying data structure
B
tree.
MySQL official Explain document1. Introduction
Usage
The EXPLAIN keyword can simulate the optimizer's execution of the SQL
statement and analyze the performance bottleneck of the query statement.
2.1 id
- id
- column The serial number is
select
. Generally, there are as manyselect
as there areid
(Joint table query will have duplicateid
), and the order ofid
increases in the order in whichselect
appears. id - The larger the value, the higher the priority of execution. If
id
is the same (usually appears in joint table query), the execution will be from top to bottom.id
isNULL
and is executed last.
select_type
Indicates whether the corresponding row is a simple or complex query. Common values are:
- simple
: simple query, the query does not include subqueries and unions.
- primary
: The outermost select in a complex query.
- subquery
: subquery contained in select (not in the from clause)
- derived
: For subqueries contained in the form clause, MySQL will place the results in a temporary table, also called a derived table.
- union
: The second or subsequent select in the union.
[Note] In
, derived tables will be merged and optimized. If you want to visually view the value of select_type
, you need to Temporarily turn off this function (it is turned on by default). This operation is required for all derivative tables in the following introduction
. # 关闭衍生表的合并优化(只对该会话有效)set session optimizer_switch='derived_merge=off'; # 打开衍生表的合并优化(只对该会话有效)set session optimizer_switch='derived_merge=on';
The table corresponding to the row query.
【Note】
When there is a subquery in the- from clause
- , the table column is in the format of
<derivenN>
, indicating that this row executes the query of theid = N
row.When there is
union - , the table data is in the format of
<union M,N>
,M and N
represent participation##select
rowid
of #union.
2.4 partitions
To be continued. . .
2.5 type (very important)
type
- Indicates the association type (access type, or query type) of this row of queries, This value can be used to understand the approximate range of query data records for this row.
The common values from best to worst are:
system > const > eq_ref > ref > range > index > ALL ; Generally, we need to ensure efficiency If so, we need to optimize our statements to at least reach the - range
level, and if possible optimize to
ref;
rangeis generally used for range searches, so change In other words, in addition to range search, we'd better optimize other query statements to the
reflevel.
Common value description
- NULL
- : Indicates that
MySQL
can be optimized The query statement is decomposed in stages, and tables and indexes do not need to be accessed during the execution stage.
system / const - : MySQL can optimize a certain query part and convert it into a constant (you can view it through
show warnings
Optimized results), mainly query the records corresponding to the primary key (
Primary Key) or the unique key index (
Unique Key). Because there are no duplications, only one record can be queried at most. , so the speed is relatively fast.
systemis a special case of
const, which is
systemwhen there is only one record in the temporary table.
# 表里有一个主键id为1的记录 - constexplain select * from student where id = 1# 派生表里面只有一条记录 - systemexplain select * from (select * from student where id = 1) tmp# 注: 如果查询的列中有 text 类型,那么在这里 type 会变为 ALL ,# 因为无法使用内存临时表,只能在磁盘上创建临时表,所以性能上会有所损耗,效果等同于全表查询 ALL。
Copy after login req_ref
:当主键或唯一键索引的相关列并联接使用时(联表查询),最多匹配一条符合条件的记录。这是除了const
之外的最好的联接类型,简单的select
查询不会出现req_ref
,更多出现在联表查询。# 虽然返回结果中有多条记录,但是在查询中一个学生id只对应一个班级,所以查询班级的时候为 req_ref,# 但是查询 student 的时候是 ALL,全表查询explain select * from student left join banji on student.id = banji.student_id
Copy after login
【注】在查询的过程中的返回结果如下:
当联接表查询时候会看作是一条查询 SQL
,所以它们对应的 id
是一样的,当 id
都是一样的时候,按照从上到下
的顺序依次执行,这里是先查询班级所有的学生(全表查询 ALL
),然后根据学生id
查找出学生对应的班级信息(req_ref
)。
ref
:当使用普通索引(Normal)
或者是联合索引的部分前缀
时,索引要和某个值进行比较,可能会找到多个符合条件的记录行,从辅助索引的根节点开始对比并找到相应的记录。# 简单的 select 查询,name 是普通索引(Normal Index)explain select * from student where name = '张三';# 简单 select 查询,banji_id (第一个) 和 student_id (第二个) 的联合索引EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = 3# 关联表查询# 包含 banji 表,banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引,# 以下查询只用到了联合索引的 banji_id (第一个)explain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id
Copy after loginrange
:范围扫描,通常出现在in,between,>,<,>=
等操作中,使用一个索引来检索给定范围的行。# 查询 id 大于 1 的学生信息explain select * from student where id > 2;
Copy after loginindex
:- 扫描全索引就能拿到结果,一般是扫描某个
二级索引
(辅助索引,除了主键之外的索引
)。这种索引不会从主键索引树根节点开始查找,而是直接对二级索引的叶子节点遍历和扫描,从而查找出相应的记录行,速度比较慢; - 这种查询方式一般为使用
覆盖索引
,查询所需的所有结果集在二级索引
与主键索引
中都有的情况下,由于二级索引
一般比较小(因为二级索引
是非聚集
的,其叶子节点是存放的主键索引
相应的地址,而主键索引
是聚集的,其叶子节点存放的是完整的数据集),所以优先走二级索引,这种情况通常比ALL
快一些。 - 在某些情况下,如果表的列数特别多,这个时候通过
辅助索引
查询的性能就不如直接使用主键索引
效率高(如果查询了辅助索引
的话,还会返回到主键索引中进行查找更多的字段,也就是回表查询
,当然在某些情况下使用回表查询
的性能也会比只使用主键索引
的性能高),这个时候会走主键索引,这种情况也比ALL
快。
# student 表只有id主键,name 普通索引select * from student;# 这个时候会走 name 索引# 因为 name 是普通索引,所以如果加 where 的话可以达到 ref 级别select * from student where name = 'Ana'
Copy after login覆盖索引
定义:覆盖索引一般针对于辅助索引,并不是真正的索引,只是索引查找的一种方式。如果select
查询的字段都在辅助索引树中全部拿到,这种情况一般是使用了覆盖索引
,不需要通过辅助索引树
找到主键
,再通过主键
去主键索引树
里获取其它字段值。- 扫描全索引就能拿到结果,一般是扫描某个
ALL
:全表扫描,扫描主键(聚簇、聚集)索引树的所有叶子节点,通常这种情况下要根据业务场景来增加其他索引进行优化。# id 为主键的 student 表,没有其他索引,该查询为 ALL.select * from student
Copy after login
2.6 possible_keys
possible_keys
主要显示查询可能用到哪些索引来查找,只是可能会使用,并不代表一定会使用。
常见值说明:
NULL
: 没有相关索引,如果是NULL
的话,可以考虑在where 子句
中创建一个适当的索引来提高查询性能,然后继续用explain
查看其效果;也有可能出现possible_keys
为NULL
,但是key
有值,实际走了索引。- 有列值:如果显示表中的某列,则表示可能会走这一列对应列值的索引;如果
possible_keys
有值,但是key
显示NULL
,这种情况一般存在于表中数据量不大的情况,因为MySQL
语句优化器认为索引对此查询的帮助不大,从而选择了全表查询
。
2.7 key
key
表示MySQL
实际采用哪个索引来优化对该表的查询。- 如果没有使用索引,则该列为
NULL
,如果想强制MySQL
使用或忽略possible_keys
列中的索引,可以在查询中使用force index
或ignore index
.
2.8 key_len
显示了 MySQL
索引所使用的字节数
,通过这个数值可以计算具体使用了索引中的哪些列(主要用于联合索引的优化)。
【注】索引最大长度是 768 字节
,当字符串过长时,MySQL
会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。
示例:一个学生与班级的关系表:
banji_student
,存在使用banji_id
与student_id
两个列组合的联合索引,并且每个索引int
都是4
字节,通过key_len
值为4
可以知道只使用了联合索引的第一列:banji_id
来执行索引查找。
# 只使用了联合索引的第一列select * from banji_student where banji_id = 2
key_len
的计算规则如下:
字符串:常见的是
char(n)
和varchar(n)
,从MySQL 5.0.3
之后,n
均表示字符数
,而不是字节数
,如果是UTF-8
,一个数字或字母占1
个字节,一个汉字占3
个字节。描述 char(n)
非汉字长度为 n
,如果存放汉字长度为3n
字节varchar(n)
非汉字长度为 n+2
,如果存放汉字长度为3n+2
字节;因为varchar
是可变长字符串,需要2
字节来存储字符串长度数值类型:
描述 tinyint
长度为 1
字节smallint
长度为 2
字节int
长度为 4
字节bigint
长度为 8
字节时间类型:
描述 date
长度为 3
字节timestamp
长度为 4
字节datetime
长度为 8
字节NULL
如果字段允许设置为
NULL
,则需要1
字节来记录是否为NULL
;Not NULL
的列则不需要。
2.9 ref
显示了在使用 key
列中实际的索引时,表查找时所用到的列名和常量;常见的为 const
常量或索引关联查询的字段(列)名
。
# 使用了常量 2,所以在查询的时候 ref 为 constselect * from student where id = 2# 关联表查询# 包含 banji 表,banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引# 这里的 ref 为 test.id ,也就是指的是 banji.idexplain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id
3.10 rows
显示预计查询的结果数,并不是真正的结果集中的记录(行)数,仅供参考。
2.11 filtered
未完待续。。。
2.12 Extra
这一列展示的是额外的信息,存在很多值,且在不同的场景下以及不同版本的 MySQL
所表示的意思也不同,只能是表示大概的意思并且仅做优化参考,这里只介绍常见的值。
Using index
:使用覆盖索引,在type
相同的情况下,Extra
的值为Using index
要比为NULL
性能高。比如
banji
表,存在id,name,create_time
列,存在id 主键
与name 普通索引
。# 覆盖索引,直接查询 name 对应的索引树就可以满足 select 后面的查询列select id,name from banji# 非覆盖索引,虽然也走了索引,但是进行了回表查询,以查询出 create_time 字段。select * from banji where name = '二年级'
Copy after loginUsing where
:使用where
关键字来查询,并且对应的列没有设置索引,对应的key
为NULL
。这种情况一般要对查询的列添加相对应的索引来进行优化。
Using index condition
:非覆盖索引查询并进行了回表,并且辅助索引使用了条件查询语句(where
或其他)。比如
banji_student
关系表,存在id,banji_id,student_id,create_time
列,存在id 主键
和banji_id 与 student_id 的组合(联合)索引
。# 进行了回表查询,以查询出 create_time 列,并且组合索引进行了范围查找select * from banji_student where banji_id > 3
Copy after loginUsing temporary
:MySQL
需要创建创建一个临时表来处理查询,出现这种情况一般要添加索引进行优化处理。# 如果 name 没有添加普通索引的话,则需要创建一个临时表来进行去重,Extra 值为 Using temporary# 如果添加了索引,则会走 name 对应的索引树,并且是覆盖索引,Extra 值为 Using indexexplain select distinct name from student
Copy after loginUsing filesort
:使用外部排序而不是索引排序,当数据较小的时候采用的是内存排序,当数据量较大的时候会频繁的访问磁盘,并将排序后的数据写入磁盘。# 如果 name 没有添加普通索引的话,则需要创建一个临时表来进行去重,Extra 值为 Using filesort# 如果添加了索引,则会走 name 对应的索引树,并且是覆盖索引,Extra 值为 Using indexexplain select name from student order by name
Copy after loginSelect tables optimized away
:使用聚合函数
(例如max
、min
等)来访问存在索引的字段时,只访问索引树中已排好序的叶子,节点性能很高。# 比如使用聚合函数 min 查询最小的学生 id(主键)explain select min(id) from student
Copy after loginThe above is the detailed content of Detailed explanation of MySQL 5.7 optimization: Explain execution plan. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

Installing MySQL on CentOS involves the following steps: Adding the appropriate MySQL yum source. Execute the yum install mysql-server command to install the MySQL server. Use the mysql_secure_installation command to make security settings, such as setting the root user password. Customize the MySQL configuration file as needed. Tune MySQL parameters and optimize databases for performance.

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
