备案控制台
探索云世界
开发者社区 数据库 文章 正文

Mysql优化之explain你真的会吗？

2023-08-02 43
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： Mysql优化之explain你真的会吗？

一、explain是什么？


1、 定义


EXPLAIN是mysql中的一个命令，可以模拟优化器执行SQL语句并返回执行计划。通过执行计划，我们可以分析查询语句或表结构的性能瓶颈，从而进行SQL优化。


2、用法


mysql> explain select * from employee;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


通过上面的执行计划我们可以了解：

  1. 表的执行顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 每张表有多少行被优化器查询


看到这个表，也许你会两眼一抹黑，这咋看呀？接着往下看，你一定可以从执行计划中轻松得出以上结果。


二、名词段解释


名词


1. id


定义：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

存在以下三种情况：


  1. id相同，执行顺序由上至下
mysql> desc select e.*,p.* from employee e,performance p where p.user_id = e.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


  1. id不同，如果是子查询，id序号对递增，id值越大优先级越高，越先执行
mysql> explain select e.* 
    -> from employee e
    -> where id = ( select user_id
    -> from performance
    -> where performance.score > 80 and performance.year = 2020 and performance.quarter = 2);
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | performance | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)


  1. id相同而不同（不同的：id越大越先执行；相同的：由上至下按顺序执行）
mysql> EXPLAIN SELECT
    -> e.*, d.depart_name
    -> FROM employee e,department d
    -> WHERE
    -> e.depart_id = d.id
    -> AND e.id = (
    -> SELECT user_id
    -> WHERE
    -> performance.score > 80
    -> AND performance. YEAR = 2020
    -> AND performance. QUARTER = 2
    -> );
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
| id | select_type | table       | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | Using where |
|  1 | PRIMARY     | d           | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.e.depart_id |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | performance | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)


2、select_type


定义：查询的类型，主要用于区别普通查询、联合查询和子查询等复合查询。


  1. SIMPLE：简单的select查询，查询中不包含子查询和联合查询（union）


mysql> explain select * from employee;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


  1. PRIMARY：若查询中包含任何的子查询，最外层查询则标记为PRIMARY
  2. SUBQUERY：在select或where列表中包含的子查询


mysql> explain select e.* 
    -> from employee e
    -> where id = ( select user_id
    -> from performance
    -> where performance.score > 80 and performance.year = 2020 and performance.quarter = 2);
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | performance | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)


  1. DERIVED：在FROM列表中包含的子查询被标记为（DERIVED）,Mysql会递归执行这些子查询，把结果放在临时表里。


mysql> EXPLAIN select employee.* FROM (SELECT distinct department.id FROM department) s1,employee WHERE s1.id = employee.depart_id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
|  1 | PRIMARY     | employee   | NULL       | ALL   | NULL          | NULL        | NULL    | NULL                  |    1 |   100.00 | Using where |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | db.employee.depart_id |    2 |   100.00 | Using index |
|  2 | DERIVED     | department | NULL       | index | PRIMARY       | PRIMARY     | 4       | NULL                  |    4 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)


  1. UNION：如果第二个SELECT出现在UNION之后，则标记为UNION;如果UNION包含在FROM子句的子查询中，外层SELECT被标记为DERIVED
  2. UNION RESULT：从UNION表获取结果的SELECT


mysql> explain select * from employee union select * from employee_asu;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | employee     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|  2 | UNION        | employee_asu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain select * from (select * from employee union select * from employee_asu) s;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
|  2 | DERIVED      | employee     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|  3 | UNION        | employee_asu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)


3、table


定义：顾名思义，表名。


4、partitions（分区）


定义：显示表分区的分区名


mysql> explain select * from tk;
+----+-------------+-------+------------+------+---------------+------+---------+---
| id | select_type | table | partitions | type | possible_keys | key  | key_len | re
+----+-------------+-------+------------+------+---------------+------+---------+---
|  1 | SIMPLE      | tk    | p0,p1,p2   | ALL  | NULL          | NULL | NULL    | NU
+----+-------------+-------+------------+------+---------------+------+---------+---
1 row in set, 1 warning (0.00 sec)


5、type


定义：type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all，常见的有system > const > eq_ref > ref > range > index > all


  1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可忽略不计。


mysql> EXPLAIN select * FROM (SELECT distinct department.id FROM department where id=2) s1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| 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     | department | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


  1. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，只匹配一行数据，速度很快。


mysql> explain SELECT distinct department.id FROM department where id=2;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


  1. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条数据与之匹配，常见于主键或唯一索引扫描。


mysql> explain SELECT * from employee,department where employee.depart_id  = department.id ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | employee   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    7 |   100.00 | Using where |
|  1 | SIMPLE      | department | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.employee.depart_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


  1. ref：非唯一性索引扫描，返回匹配某个单独值的所有行


mysql> show index from employee; #查看表索引
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY               |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          1 | idx_employee_username |            1 | username    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
mysql> explain select * from employee where username = "john";
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_employee_username | idx_employee_username | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)


  1. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引，一般是where语句中出现了between、<、>或in等查询。


mysql> explain select * from employee where id between 1 and 3;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


  1. index：full index scan,全索引树扫描，通常比all快，因为索引文件比数据文件小很多。


mysql> explain select id from department;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | index | NULL          | PRIMARY | 4       | NULL |    4 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


  1. all：全表扫描，当数据量达到百万级别，性能会下降明显，需要建立索引；当然小数据量的全表扫描是没有问题的。


mysql> explain select * from department;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | department | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


6、possible_keys


定义：显示可能应用在这张表中的索引，一个或多个（查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）


mysql> explain select id,username,age,salary from employee where username='a';
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


补充说明：从执行计划关键字possible_keys中可以看出，sql语句执行可能用到idx_username和idx_username_age_salary两个索引。如果为null，就是没有可能使用的索引。


7、key


定义：实际使用的索引。如果为null，则没有使用索引。


  1. 常规举例


mysql> explain select username,age,salary from employee where username = 'a' group by age,salary;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


补充说明：possible_keys，可能使用的所以为idx_username,idx_username_age_salary; key,实际使用的索引为idx_username_age_salary


  1. 覆盖索引


看到覆盖索引也许你两眼一抹黑，别急，先记住这么一个名词，继续往下看。


mysql> explain select username,age,salary from employee;
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | index | NULL          | idx_username_age_salary | 93      | NULL |    7 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


补充说明：如果查询中使用了覆盖索引，则该索引只会出现在key中，不会出现在possible_keys中。


8、key_len


定义：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的情况下，key_len越短越好。换句话说，key_len显示的值为索引字段的最大可能长度，即key_len是通过表定义计算而得，不是通过表内检索出来的，不过具体怎么算出来的没有研究。


9、ref


定义：显示索引哪一列别使用了，如果可能的话，是一个常数。指明哪些列或常量被用于查找索引列上的值。


mysql> EXPLAIN SELECT employee.username, employee.age, employee.salary FROM employee, department WHERE employee.id = department.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL             |    4 |   100.00 | Using index |
|  1 | SIMPLE      | employee   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.department.id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


补充说明：ref对呀的值为db.department.id,表示数据库db中的department表中的id列。


mysql> EXPLAIN SELECT username,age,salary FROM employee where username = 'john';
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


补充说明：username对应的值为john,所以是常量const


10、rows


定义：根据表的统计信息及索引使用情况，大致估算出找到所需的记录需要读取的行数。


11、filtered


定义：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。值越大越好。


12、Extra


定义：包含不适合在其它列中显示但十分重要的的额外信息


  1. Using filesort：说明Mysql使用一个外部索引排序，而不是按照表内的索引进行排序（Mysql中无法使用索引完成的排序操作称为“文件排序”）。


mysql> EXPLAIN SELECT username,age,salary FROM employee where username = 'john' order by id;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)


补充说明：虽然使用了idx_username_age_salary索引，但是排序规则不是按照这个索引来的，而是索引文件外排序。因此sql语句的排序规则尽量按照索引的字段来排序，避免文件外排序，降低性能。


  1. using temporary：表示使用了临时表保存了中间结果，Mysql在对查询结果排序时使用临时表，常见于排序order by和分组gruop by。


mysql> explain select age from employee where username in ('john','asd') group by age;
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                                              | key                     | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_username_age_salary,idx_username,idx_username_departid | idx_username_age_salary | 83      | NULL |    2 |   100.00 | Using where; Using index; Using temporary |
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)


补充说明：产生临时表很消耗性能


  1. using index：表示相应的select操作中使用了覆盖索引，避免访问了数据行，效率不错。如果同时出现了using where，表示在索引中查找；如果没有，表明是从索引中读取数据，而非查找。


覆盖索引/索引覆盖(Covering index)：select数据列只需要从索引中就可以全部获取，不必读取数据行，换句话说查询列要被所建的索引覆盖。


mysql> explain select username,age,salary from employee where username ='john' order by age,salary;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


注意：要想使用覆盖索引，一定要从select列表中获取自己想要的列，不可使用select *;如果使用所有列做索引会导致索引过于庞大。


  1. using where：sql语句中使用了where过滤
  2. using join buffer：使用了连接缓存
  3. impossible where：where子句中总是false，不可能查到任何数据
  4. select table optimized away：在没有group by的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储优化count(*)操作，不必等到执行阶段在进行计算，执行计划完成的阶段就完成优化。
  5. distinct：优化distinct操作，再找到第一匹配的元组后就停止找同样值的操作。
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
缓存
存储
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版explain
云数据库 RDS MySQL 版优化explain
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
java菌
目录
相关文章
1094930106622855
|
4天前
|
关系型数据库 MySQL 测试技术
MySQL的`IN`的优化经验
限制IN列表的长度：IN子句中的元素数量较多时，会显著降低查询性能。尽量减少IN中的项数量。
1094930106622855
17 0
yma16
|
8天前
|
关系型数据库 MySQL Unix
linux优化空间&完全卸载mysql——centos7.9
linux优化空间&完全卸载mysql——centos7.9
yma16
40 7
请回答1024
|
9天前
|
SQL 存储 关系型数据库
不允许你不知道的 MySQL 优化实战（三）
不允许你不知道的 MySQL 优化实战（三）
请回答1024
14 1
请回答1024
|
9天前
|
SQL 关系型数据库 MySQL
不允许你不知道的 MySQL 优化实战（二）
不允许你不知道的 MySQL 优化实战（二）
请回答1024
16 2
还在路上的秃头
|
8天前
|
SQL 关系型数据库 MySQL
【MySQL】SQL优化
【MySQL】SQL优化
还在路上的秃头
54 3
渐暖
|
9天前
|
SQL 关系型数据库 MySQL
【mysql】mysq优化全方面分析
【mysql】mysq优化全方面分析
渐暖
13 0
渐暖
|
9天前
|
存储 缓存 关系型数据库
【Mysql】Schema与数据类型优化
【Mysql】Schema与数据类型优化
渐暖
9 0
请回答1024
|
9天前
|
SQL 关系型数据库 MySQL
不允许你不知道的 MySQL 优化实战（一）
不允许你不知道的 MySQL 优化实战（一）
请回答1024
15 2
请回答1024
|
9天前
|
SQL 存储 关系型数据库
MySQL SQL优化
MySQL SQL优化
请回答1024
11 0
李振良_阿良
|
10天前
|
存储 缓存 关系型数据库
掌握MySQL数据库这些优化技巧，事半功倍！
掌握MySQL数据库这些优化技巧，事半功倍！
李振良_阿良
21 0

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    【mysql】—— 用户管理
    31
  • 2
    【mysql】—— 视图
    32
  • 3
    【mysql】—— 表的增删改查
    42
  • 4
    【mysql】—— 表的约束
    33
  • 5
    【MYSQL】—— MySQL 在 Centos 7环境安装
    59
  • 6
    MySQL模糊查询二三事
    26
  • 7
    MySQL的JOIN到底是怎么玩的
    108
  • 8
    MySQL怎样删除重复数据，只保留一条？
    36
  • 9
    【怒怼大厂面试官】你先说说知道哪些MySQL的高级特性
    49
  • 10
    Flink CDC产品常见问题之读分布式mysql报连接超时如何解决
    165

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考