【MySQL性能优化 Explian用法与结果集详细分析-每日运维

EXPLAIN详细介绍

😄生命不息，写作不止

🔥 继续踏上学习之路，学之分享笔记

👊 总有一天我也能像各位大佬一样

🏆 博客首页 @怒放吧德德 To记录领地

🌝分享学习心得，欢迎指正，大家一起学习成长！

执行计划.jpg

简介

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈。在日常工作中，我们经常会使用到Explain来查看那些执行慢的SQL语句，根据查询的结果集进行一系列优化。本文的测试基于MySQL 8.0。

Explain使用

这里有一直普通的表，没有创建任何索引，只有一个主键。这里先来简单认识一下Explain执行得出的结果集。其结果包括以下的列：

列名	JSON 名称	含义
id	select_id	查询标识符
select_type	None	查询类型
table	table_name	输出行的表
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可以选择的索引
key	key	实际选择的索引
key_len	key_length	所选密钥的长度
ref	ref	列与索引的比较
rows	rows	估计要检查的行数
filtered	filtered	按表条件过滤的行的百分比
Extra	None	附加信息

具体内容以及含义下文会一一详细介绍。

数据准备

这里笔者使用了三张表来演示，分别是用户表、产品表、订单表。

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATE
);
INSERT INTO users (user_id, username, email) VALUES
(1, 'user1', 'user1@example.com'),
(2, 'user2', 'user2@example.com'),
(3, 'user3', 'user3@example.com'),
(4, 'user4', 'user4@example.com'),
(5, 'user5', 'user5@example.com'),
(6, 'user6', 'user6@example.com'),
(7, 'user7', 'user7@example.com'),
(8, 'user8', 'user8@example.com'),
(9, 'user9', 'user9@example.com'),
(10, 'user10', 'user10@example.com');
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Product A', 19.99),
(2, 'Product B', 29.99),
(3, 'Product C', 39.99),
(4, 'Product D', 49.99),
(5, 'Product E', 59.99),
(6, 'Product F', 69.99),
(7, 'Product G', 79.99),
(8, 'Product H', 89.99),
(9, 'Product I', 99.99),
(10, 'Product J', 109.99);
INSERT INTO orders (order_id, user_id, product_id, order_date) VALUES
(1, 1, 3, '2023-01-01'),
(2, 2, 5, '2023-02-02'),
(3, 3, 7, '2023-03-03'),
(4, 4, 9, '2023-04-04'),
(5, 5, 2, '2023-05-05'),
(6, 6, 4, '2023-06-06'),
(7, 7, 6, '2023-07-07'),
(8, 8, 8, '2023-08-08'),
(9, 9, 10, '2023-09-09'),
(10, 10, 1, '2023-10-10');

Explain各列的解读

接下来对执行计划的每个列字段进行详细解析。

1)、id

查询的唯一标识符，用于标识查询的每个步骤。对于复杂的查询，可能会有多个步骤，步骤之间通过 id 进行关联。id是select的序列号，有几个select也就会有几个id，这个id是根据查询语句中的顺序依次递增。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
而复杂查询又分为三类：

简单子查询

派生表（from语句中的子查询）

union 查询。

简单子查询，在SELECT中查询的子查询，以下的id是会有值的，也可能是为NULL，如果结果集是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

mysql> EXPLAIN SELECT * FROM orders o WHERE o.user_id = (SELECT u.user_id FROM users u WHERE u.email = 'user4@example.com');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
|  2 | SUBQUERY    | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

查询指定邮箱的用户的订单数据，首先需要查询用户表指定邮箱的用户标识，进行条件查询订单的数据。用户表的id会比较大，是要优先执行的。

2)、select_type

查询的类型，是每个SELECT语句的类型，表示对应行是简单还是复杂的查询。①、SIMPLE简单的SELECT 查询，不包含子查询或 UNION。

mysql> EXPLAIN SELECT * FROM users WHERE user_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

②、PRIMARY查询中包含着复杂的子部分，最外层查询就会标记成PRIMARY，就如下的查询。

mysql> EXPLAIN SELECT * FROM orders o WHERE o.user_id = (SELECT u.user_id FROM users u WHERE u.email = 'user4@example.com');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
|  2 | SUBQUERY    | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

③、SUBQUERY子查询，包含在 select 中的子查询但不在 from 子句中。如②的例子，这样的查询就会被标记SUBQUERY。④、DERIVED派生表，通常是从子查询中派生的表。包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中。⑤、UNION在 union 中的第二个后后面的select语句。⑥、DEPENDENT UNIONUNION中的第二个或后面的SELECT语句，取决于外面的查询。⑦、UNION RESULT从 union 临时表检索结果集的select。

3)、table

查询所涉及的表名。

4)、type

表示连接类型。即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。NULLmysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql> EXPLAIN SELECT min(user_id) FROM users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

const, systemmysql能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system。

mysql> EXPLAIN SELECT * FROM users WHERE user_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

eq_ref唯一索引的等值查询，primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

mysql> EXPLAIN SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.user_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |   10 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.o.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

ref使用的不是唯一索引，而是普通索引，索引要和某个值进行对比，可能会找到多个符合条件的行。先在最开头准备的数据中进行，先创建一个普通索引CREATE INDEX idx_name ON users(username);

mysql> EXPLAIN SELECT * FROM users WHERE username = 'user2';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_name      | idx_name | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

还有一种例子，我在orders表中将user_id和product_id做了联合索引，关联表查询走了最左前缀优化的user_id部分。

mysql> EXPLAIN SELECT users.user_id FROM users LEFT JOIN orders ON orders.user_id = users.user_id;
+----+-------------+--------+------------+-------+------------------------------------+------------------------+---------+--------------------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys                      | key                    | key_len | ref                | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------------------------+------------------------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | users  | NULL       | index | NULL                               | idx_name               | 202     | NULL               |   10 |   100.00 | Using index |
|  1 | SIMPLE      | orders | NULL       | ref   | idx_user_id_product_id,idx_user_id | idx_user_id_product_id | 5       | test.users.user_id |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+------------------------------------+------------------------+---------+--------------------+------+----------+-------------+

range范围扫描通常出现在 in(), between ,> ,<, >= 等操作中，使用一个索引来检索给定范围的行。

mysql> EXPLAIN SELECT * FROM users WHERE user_id IN (1, 2, 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

index通过索引全表扫描，效率会比ALL扫描全表快。

index是从索引中读取的，而ALL是从硬盘中读取。

mysql> EXPLAIN SELECT user_id FROM users;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | idx_name | 202     | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+

ALL扫描全表，最low的一种，意味着mysql需要从头到尾去查找所需要的行。正常来说需要用索引来进行优化。

mysql> EXPLAIN SELECT * FROM users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

5)、possible_keys

这个字段是显示了可能使用的索引。列出了查询可能使用的索引，但不一定会选择使用。

在EXPLAIN时候，会发现有时候possible_keys中是有值的，但是key的值却是NULL，这情况是因为表中的数据比较少，MySQL认为索引对查询帮助不大，就选择了全表查询。
如果这字段的值是NULL，那就可以看看where子句是否可以创建一个良好的索引来提升性能。

6)、key

实际使用的索引。如果列出了具体的索引，表示查询使用了该索引。

如果没有用到索引，就是NULL，可以让MySQL强制使用或者忽略possible_keys的索引。在查询中使用 force index、ignore index。

7)、key_len

这字段显示使用的索引的长度。表示索引的字节数。举个例子说明，通过user_id=1查询数据，因为user_id是int类型，占4字节，key_len=4可以推断出使用了user_id作为索引查询。

mysql> EXPLAIN SELECT * FROM users WHERE user_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

key_len计算规则

字符串

char(n)：n字节长度

varchar(n)：2字节存储字符串长度，如果是utf-8，则是3n+2

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

*注：索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分

的字符提取出来做索引。

8)、ref

表示连接匹配的列或常数。这是描述索引如何被使用的关键信息。常见的有：const（常量），字段名。

9)、rows

估计的行数。表示查询执行时，从表中读取的行数的估计值，注意这个不是结果集里的行数。

10)、Extra

包含其他的一些附加信息，如是否使用了文件排序、临时表等。常见的 Extra 值包括 Using filesort、Using temporary 等。如下介绍其不同值。NULL查询的列没有被索引覆盖。

mysql> EXPLAIN SELECT * FROM `orders` WHERE user_id = 1;
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys                      | key                    | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id_product_id,idx_user_id | idx_user_id_product_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------+

以上例子where筛选的条件是索引的前导列，其实也就是使用到了索引，但是部分字段没有在索引中，就必须通过“回表”来得到其他字段数据。SELECT *，获取了索引以及其他数据，type=ref，证明用到了索引，而后面的Extra=NULL表示还有其他数据是不在索引上，因此执行了回表获取其他数据。Using index此步骤是通过覆盖索引的方式完成的，而无需访问实际的数据行。通常出现在覆盖索引查询中，可以提高查询性能。一般就是使用了覆盖索引（查询的列都在索引上）。如果作为辅助索引的话，对性能是很不错的。

mysql> EXPLAIN SELECT user_id FROM `orders` WHERE user_id = 1;
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                      | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id_product_id,idx_user_id | idx_user_id_product_id | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+-------------+

以上例子其where子句使用索引的前导列，获取的字段也在索引里面。这样的性能是很高的。Using where使用了where查询，查询的列没有被索引覆盖，where筛选条件也不是索引的前导列。

mysql> EXPLAIN SELECT * FROM `users` WHERE email = 'user4@example.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

Using where; Using index查询的结果是被索引覆盖，但是where条件不是索引的前导列。

mysql> EXPLAIN SELECT user_id FROM `orders` WHERE product_id = 1;
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | index | idx_user_id_product_id | idx_user_id_product_id | 10      | NULL |   10 |    10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+

Using temporary使用了临时表来处理结果，遇到这种情况一般是需要进行优化的。

mysql> EXPLAIN SELECT DISTINCT product_name FROM products;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+

根据product_name创建索引，EXPLAIN SELECT DISTINCT product_name FROM products;

mysql> EXPLAIN SELECT DISTINCT product_name FROM products;
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | index | idx_product_name | idx_product_name | 202     | NULL |   10 |   100.00 | Using index |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+

Using filesort这说明查询中包含了排序操作，但无法使用索引完成排序，而需要额外的文件排序操作。

mysql> EXPLAIN SELECT * FROM products ORDER BY product_name;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

此时的product_name并没有创建索引，有使用其来进行排序。然后我们可以通过对其进行添加索引，再次执行执行计划，就能看到Extra的值是Using indexUsing index condition查询中的 WHERE 子句中的条件可以由索引的条件推导（index condition）来满足，而不需要进一步检查实际的数据行。这通常发生在覆盖索引的情况下。

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith' AND salary > 66799.74;
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_first_name_last_name_salary | idx_first_name_last_name_salary | 2052    | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+

以上例子是在下文的例子。

11)、filtered

表示此步骤的结果集中的行的过滤百分比。这个百分比表示经过这一步骤后，剩下的行数占原始行数的百分比。

索引优化实战

通过上面所介绍的执行计划，我们通过一张原始没有任何索引的表进行索引优化实战。这里准备了10条数据，其中employee_id是主键，此时添加一个first_name、last_name、salary的联合索引。

全值匹配

EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane';
EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith';
EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith' AND salary = 66799.74;

first_name、last_name、salary是联合索引，其每个的ref都是const（常量）。

最佳左前缀法则

最佳左前缀法则（Best Leftmost Prefix Rule）是关于索引设计的一个原则，它指导在创建复合索引（composite index）时应该考虑使用最优的左前缀。最左边的索引需要先走，才能走索引。

EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane';
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND salary = 66799.74;

以上查询就没有遵循最左索引开始，把first_name跳过了，就会导致不走索引，选择了全表扫描。

索引列上操作

不要在索引列上进行操作（函数、计算、类型转换），这些操作都会使得索引失效，而进行全表扫描。

mysql> EXPLAIN SELECT * FROM employees WHERE LEFT(first_name,2) = 'Jane';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上例子，first_name通过LEFT(str,len)函数操作，就会导致执行计划type=ALL走了全表扫描。

存储引擎不能使用索引中范围条件右边的列

这句话的意思是，对于使用复合索引的查询，存储引擎在处理范围条件时只能使用索引中范围条件左边的列，而无法使用右边的列。

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith' AND salary > 66799.74;
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_first_name_last_name_salary | idx_first_name_last_name_salary | 2052    | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------------+---------------------------------+---------+------+------+----------+-----------------------+

就比如这个例子，假如联合索引顺序是（first_name、salary、last_name）。first_name是等值条件，salary是范围条件，MySQL 存储引擎可以使用索引来加速 first_name = 'Jane'这个等值条件的过滤，但在处理范围条件 salary > 66799.74时，只能使用索引中左边的列 first_name。但如果联合索引顺序是（first_name、last_name、salary），就能使用到first_name、last_name两个索引。

B+ 树是一种树状数据结构，按照顺序存储索引的键值，而范围查询需要在树上进行范围扫描。为了进行范围扫描，存储引擎必须按顺序遍历索引的键，但无法直接跳到右边的列。

如果查询包含多个条件，其中有范围条件，最好将范围条件放在索引的最右侧，以使得左侧的条件能够更好地利用索引。如果范围条件在索引的左侧，存储引擎可能无法充分使用索引，导致性能下降。

尽量使用覆盖索引

只访问索引的列，减少SELECT *的语句，这样有利于性能提高。

EXPLAIN SELECT * FROM employees WHERE first_name = 'Jane';
EXPLAIN SELECT first_name FROM employees WHERE first_name = 'Jane';

不走索引的操作

①、在使用不等于操作(!= or <>)，是不会走索引，而会导致全表扫描。

mysql> EXPLAIN SELECT * FROM employees WHERE first_name <> 'Jane';
+----+-------------+-----------+------------+------+---------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys                   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_first_name_last_name_salary | NULL | NULL    | NULL |   10 |    90.00 | Using where |
+----+-------------+-----------+------------+------+---------------------------------+------+---------+------+------+----------+-------------+

②、模糊查找'%Jan'，通配符开头不会走索引，会扫描全表，但是通配符结尾可以。

注：通配符在右边代表常量，在左边或两边代表范围

EXPLAIN SELECT * FROM employees WHERE first_name LIKE '%Jan';
EXPLAIN SELECT * FROM employees WHERE first_name LIKE 'Jan%';

对于通配符开头不会走索引，可以采用覆盖索引的方式。

mysql> EXPLAIN SELECT first_name, salary FROM employees WHERE first_name LIKE '%Jan';
+----+-------------+-----------+------------+-------+---------------+---------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_first_name_last_name_salary | 2052    | NULL |   10 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------------------------------+---------+------+------+----------+--------------------------+

③、数字字符串不加单引号索引失效。

EXPLAIN SELECT * FROM employees WHERE first_name = '11';
EXPLAIN SELECT * FROM employees WHERE first_name = 11;

注：这里来记录一下关于IS NULL和IS NOT NULL走不走索引的问题。很多往上大多都说不会走索引，但是官网却是标注了IS NULL是不会影响索引的。其实这里在innodb内部会自动统计索引列的null值的数量，如果超过80%的数据是null，那么IS NOT NULL就会走索引；如果是80%的数据都不是null，那么IS NULL就会走索引。

总结

在开发中要多用EXPLAIN来进行查看自己的SQL，以便优化，要养成良好习惯。要自主分析、识别SQL如何优化，尤其是开发复杂SQL的时候。要学会看懂EXPLAIN各列以及每列字段对应的各种参数分别包含了什么数据什么意思，这样才能发挥主观能动性，调优SQL。

👍分享是提升的动力，创作不易，如有错误请指正，感谢观看！记得点赞哦！👍

【MySQL性能优化 Explian用法与结果集详细分析

简介

Explain使用

数据准备

Explain各列的解读

1)、id

2)、select_type

3)、table

4)、type

5)、possible_keys

6)、key

7)、key_len

8)、ref

9)、rows

10)、Extra

11)、filtered

索引优化实战

全值匹配

最佳左前缀法则

索引列上操作

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引

不走索引的操作

总结

相关文章

发布评论取消回复

大猫

开启3389端口命令，要怎么开启3389端口命令

win10优化驱动器怎么启动

MySQL DNS查找和主机缓存解读

【MySQL性能优化 Explian用法与结果集详细分析

简介

Explain使用

数据准备

Explain各列的解读

1)、id

2)、select_type

3)、table

4)、type

5)、possible_keys

6)、key

7)、key_len

8)、ref

9)、rows

10)、Extra

11)、filtered

索引优化实战

全值匹配

最佳左前缀法则

索引列上操作

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引

不走索引的操作

总结

相关文章

发布评论 取消回复

大猫

开启3389端口命令，要怎么开启3389端口命令

win10优化驱动器怎么启动

MySQL DNS查找和主机缓存解读

发布评论取消回复