Mysql学习一 Explain

最新推荐文章于 2024-09-19 16:23:27 发布
最新推荐文章于 2024-09-19 16:23:27 发布
阅读量228 收藏
点赞数
文章标签： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yanyubang/article/details/98752663
版权

EXPLAIN提供了关于MySQL如何执行语句的信息。EXPLAIN可以用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。

下面我们新建张表，看下EXPLAIN输出的信息。

#查看mysql版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.27    |
+-----------+
1 row in set (0.01 sec)

#新建测试表并插入数据
mysql> CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL DEFAULT "",
  `last_name` varchar(40) NOT NULL DEFAULT "",
  `position` varchar(25) NOT NULL DEFAULT "",
  `home_address` varchar(50) NOT NULL DEFAULT "",
  `home_phone` varchar(12) NOT NULL DEFAULT "",
  `employee_code` varchar(25) NOT NULL DEFAULT "",
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `phone` (home_phone),
  KEY `full_name` (`first_name`,`last_name`)
) ;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');
Query OK, 0 rows affected (0.30 sec)

Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

#查看查询语句的执行信息
mysql> explain select * from employees_example;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.06 sec)

#以JSON格式输出查询语句的执行信息
mysql> explain FORMAT=JSON select * from employees_example;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.20"
    },
    "table": {
      "table_name": "employees_example",
      "access_type": "ALL",
      "rows_examined_per_scan": 6,
      "rows_produced_per_join": 6,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.20",
        "prefix_cost": "2.20",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "id",
        "first_name",
        "last_name",
        "position",
        "home_address",
        "home_phone",
        "employee_code"
      ]
    }
  }
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Explain输出的列和其基本含义

列名JSON 格式名称基本含义
idselect_id标识符
select_typeNone语句类型
tabletable_name输出结果的表
partitionspartitions匹配的分区
typeaccess_type连接类型
possible_keyspossible_keys可能选择的索引
keykey实际用到的索引，NULL表示没有用到索引
key_lenkey_length使用索引的字节数(标识是否只使用多列索引的一部分)
refref表示哪些列或常量被用于用于查找索引列上的值
rowsrows预估查找出的数据量
filteredfiltered条件筛选行的百分比
ExtraNone额外信息

id:

 表示查询中的执行顺序

#id相同，执行顺序由上至下
mysql> explain select * from employees_example t1,employees_example t2,employees_example t3 where t1.id = t2.id and t2.employee_code = t3.employee_code;
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys         | key           | key_len | ref                   | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY               | NULL          | NULL    | NULL                  |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY,employee_code | PRIMARY       | 4       | test.t1.id            |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | employee_code         | employee_code | 77      | test.t2.employee_code |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+-----------------------+---------------+---------+-----------------------+------+----------+-------+
3 rows in set (0.06 sec)

#如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
mysql> explain select t1.* from employees_example t1 where t1.id = (select t2.id from employees_example t2 where t2.employee_code = (select t3.employee_code from employees_example t3 where t3.first_name like 'H%' limit 1));
+----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+
|  1 | PRIMARY     | t1    | NULL       | const | PRIMARY       | PRIMARY       | 4       | const |    1 |   100.00 | NULL                  |
|  2 | SUBQUERY    | t2    | NULL       | const | employee_code | employee_code | 77      | const |    1 |   100.00 | Using index           |
|  3 | SUBQUERY    | t3    | NULL       | range | full_name     | full_name     | 92      | NULL  |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+
3 rows in set (0.06 sec)

#id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
mysql> explain select t1.* from employees_example t1 where t1.id = (select t2.id from employees_example t2 where t2.employee_code in (select t3.employee_code from employees_example t3 where t3.first_name like 'H%')  limit 1);
+----+-------------+-------+------------+--------+-------------------------+---------------+---------+-----------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys           | key           | key_len | ref                   | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+-------------------------+---------------+---------+-----------------------+------+----------+-----------------------+
|  1 | PRIMARY     | t1    | NULL       | const  | PRIMARY                 | PRIMARY       | 4       | const                 |    1 |   100.00 | NULL                  |
|  2 | SUBQUERY    | t3    | NULL       | range  | employee_code,full_name | full_name     | 92      | NULL                  |    2 |   100.00 | Using index condition |
|  2 | SUBQUERY    | t2    | NULL       | eq_ref | employee_code           | employee_code | 77      | test.t3.employee_code |    1 |   100.00 | Using index           |
+----+-------------+-------+------------+--------+-------------------------+---------------+---------+-----------------------+------+----------+-----------------------+
3 rows in set (0.06 sec)

select_type:

 查询的类型

select_type 的值JSON 名称含义
SIMPLENone查询中不包含子查询或者聚合查询（union）
PRIMARYNone查询中若包含任何复杂的子部分，最外层查询为：PRIMARY
UNIONNone聚合查询中的第二个或最后一个查询
DEPENDENT UNIONdependent (true)聚合查询中的第二个或最后一个查询，依赖于外层查询出的数据
UNION RESULTunion_result聚合查询的结果
SUBQUERYNone子查询中的第一个查询
DEPENDENT SUBQUERYdependent (true)子查询中的第一个查询，依赖于外层查询出的数据
DERIVEDNone衍生表
（若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED）
MATERIALIZEDmaterialized_from_subquery物化子查询
UNCACHEABLE SUBQUERYcacheable (false)无法缓存结果的子查询，必须为其他查询重新评估
UNCACHEABLE UNIONcacheable (false)聚合查询中的第二个或最后一个查询，无法使用缓存
#UNION和SUBQUERY
mysql> explain select t1.* from employees_example t1 where t1.first_name = 'Mustapha' union select t2.* from employees_example t2 where t2.employee_code = (select t3.employee_code from employees_example t3 where t3.employee_code = 'HW1');
+------+--------------+------------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY      | t1         | NULL       | ref   | full_name     | full_name     | 92      | const |    1 |   100.00 | NULL            |
|    2 | UNION        | t2         | NULL       | const | employee_code | employee_code | 77      | const |    1 |   100.00 | NULL            |
|    3 | SUBQUERY     | t3         | NULL       | const | employee_code | employee_code | 77      | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL          | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------+
4 rows in set (0.07 sec)

#DEPENDENT UNION和DEPENDENT SUBQUERY
mysql> explain select t3.* FROM employees_example t3 where t3.id IN (select t1.id from employees_example t1 where t1.first_name = 'Mustapha' union select t2.id from employees_example t2 where t2.employee_code = 'HW1');
+------+--------------------+------------+------------+--------+-----------------------+---------------+---------+-------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type   | possible_keys         | key           | key_len | ref   | rows | filtered | Extra           |
+------+--------------------+------------+------------+--------+-----------------------+---------------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY            | t3         | NULL       | ALL    | NULL                  | NULL          | NULL    | NULL  |    6 |   100.00 | Using where     |
|    2 | DEPENDENT SUBQUERY | t1         | NULL       | eq_ref | PRIMARY,full_name     | PRIMARY       | 4       | func  |    1 |    16.67 | Using where     |
|    3 | DEPENDENT UNION    | t2         | NULL       | const  | PRIMARY,employee_code | employee_code | 77      | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL                  | NULL          | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+--------+-----------------------+---------------+---------+-------+------+----------+-----------------+
4 rows in set (0.06 sec)

#DERIVED 在from后where前的子查询 
mysql> explain select t2.* FROM (select t1.id from employees_example t1 where t1.first_name = 'Mustapha' limit 1) t2;
+----+-------------+------------+------------+--------+---------------+-----------+---------+-------+------+----------+-------------+
| 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     | t1         | NULL       | ref    | full_name     | full_name | 92      | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------+---------+-------+------+----------+-------------+
2 rows in set (0.07 sec)

#MATERIALIZED
mysql> explain select t1.* from employees_example t1 where home_phone in (select home_phone from employees_example where employee_code like 'H%');
+----+--------------+-------------------+------------+-------+---------------------+---------------+---------+------------------------+------+----------+-----------------------+
| id | select_type  | table             | partitions | type  | possible_keys       | key           | key_len | ref                    | rows | filtered | Extra                 |
+----+--------------+-------------------+------------+-------+---------------------+---------------+---------+------------------------+------+----------+-----------------------+
|  1 | SIMPLE       | <subquery2>       | NULL       | ALL   | NULL                | NULL          | NULL    | NULL                   | NULL |   100.00 | NULL                  |
|  1 | SIMPLE       | t1                | NULL       | ref   | phone               | phone         | 38      | <subquery2>.home_phone |    1 |   100.00 | NULL                  |
|  2 | MATERIALIZED | employees_example | NULL       | range | employee_code,phone | employee_code | 77      | NULL                   |    2 |   100.00 | Using index condition |
+----+--------------+-------------------+------------+-------+---------------------+---------------+---------+------------------------+------+----------+-----------------------+
3 rows in set (0.06 sec)

#UNCACHEABLE SUBQUERY和UNCACHEABLE UNION
mysql> set @var1 = 'Mustapha';
Query OK, 0 rows affected (0.00 sec)

mysql> set @var2 = 'MM1';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select t3.* FROM employees_example t3 where t3.id = (select t1.id from employees_example t1 where t1.first_name = @var1 union select t2.id from employees_example t2 where t2.employee_code = @var2);
+------+----------------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id   | select_type          | table      | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+------+----------------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|    1 | PRIMARY              | t3         | NULL       | ALL   | NULL          | NULL          | NULL    | NULL |    6 |   100.00 | Using where              |
|    2 | UNCACHEABLE SUBQUERY | t1         | NULL       | index | NULL          | full_name     | 214     | NULL |    6 |   100.00 | Using where; Using index |
|    3 | UNCACHEABLE UNION    | t2         | NULL       | index | NULL          | employee_code | 77      | NULL |    6 |   100.00 | Using where; Using index |
| NULL | UNION RESULT         | <union2,3> | NULL       | ALL   | NULL          | NULL          | NULL    | NULL | NULL | NULL     | Using temporary          |
+------+----------------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
4 rows in set (0.05 sec)

table

 输出行的来源。
 <unionM,N>: 来源于聚合查询，M和N是查询标识符id的值。

 <derivedN>: 来源于派生表的结果，N是查询标识符id的值。例如From中的子查询

 <subqueryN>: 来源于物化子查询，N是查询标识符id的值。

partitions

 查询将匹配记录所在的分区。对于非分区表，该值为NULL。

type

 描述表是如何连接，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
 一般来说，得保证查询至少达到range级别，最好能达到ref。

值描述
system只有一行数据的表（=system table），是一个特殊的const类型
const表中最多有一个匹配的行。因为只有一行，行中的列值将被优化器当作常数。const非常快，因为只读一次。
eq_ref表之间的组合，行行对应。这是除system和const外最合适的连接类型。通常关联条件为主键和不为NULL的唯一索引。常用=运算符来比较
ref使用非唯一索引或主键、唯一索引的前缀查询。匹配的行数越少，越合适。
fulltext使用全文索引
ref_or_null与ref类似，但是增加了对于NULL值的额外查询。通常发生在子查询中
index_merge表示使用了索引合并优化方法，key列表示使用了那些索引
unique_subquery与eq_ref相似，但是使用的是子查询来查找 value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery与ref相似，但是使用的是子查询来查找 value IN (SELECT key_column FROM single_table WHERE some_expr)
range一个或多个索引的范围查询
index对于索引树的完全扫描，有两种发放重现1、覆盖索引的查询全表（Extra - Using index），比ALL快，因为索引文件较小。2、全表扫描，使用索引进行排序
ALL全表扫描
#ALL
mysql> explain select * from employees_example;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | NULL  |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

#index
mysql> explain select employee_code from employees_example;
+----+-------------+-------------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees_example | NULL       | index | NULL          | employee_code | 77      | NULL |    6 |      100 | Using index |
+----+-------------+-------------------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set

mysql> explain select * from employees_example ORDER BY id desc;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | index | NULL          | PRIMARY | 4       | NULL |    6 |      100 | NULL  |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set

#range
mysql> explain select * from employees_example where id between 1 and 4 and employee_code like 'H%';

+----+-------------+-------------------+------------+-------+-----------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys         | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+-----------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees_example | NULL       | range | PRIMARY,employee_code | PRIMARY | 4       | NULL |    4 |    33.33 | Using where |
+----+-------------+-------------------+------------+-------+-----------------------+---------+---------+------+------+----------+-------------+
1 row in set

#ref
mysql> explain select * from employees_example where first_name = 'Mustapha';
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | ref  | full_name     | full_name | 92      | const |    1 |      100 | NULL  |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set

#eq_ref
mysql> explain select * from employees_example t1, employees_example t2 where t1.id = t2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    6 |      100 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 |      100 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set

#const和system
mysql> explain select * from(select * from employees_example where id = 1 limit 1) t1;
+----+-------------+-------------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 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 | NULL  |
|  2 | DERIVED     | employees_example | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
+----+-------------+-------------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set

possible_keys

 MySQL查找数据可能使用到的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key

 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

mysql> explain select id,first_name from employees_example;
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees_example | NULL       | index | NULL          | full_name | 214     | NULL |    6 |      100 | Using index |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set

key_len

 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

mysql> explain select * from employees_example where first_name = 'Mustapha';
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | ref  | full_name     | full_name | 92      | const |    1 |      100 | NULL  |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set

mysql> explain select * from employees_example where first_name = 'Mustapha' and last_name = 'Mond';
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees_example | NULL       | ref  | full_name     | full_name | 214     | const,const |    1 |      100 | NULL  |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

ref

 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

filtered

 显示了通过条件过滤出的行数的预估百分比。

Extra

 显示MySQL如何解析查询的额外信息

  1. Using index
    只需要查找索引就可以查找出所需的信息，不需要执行其他查询。（覆盖索引）
mysql> explain select id,first_name,last_name from employees_example;
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees_example | NULL       | index | NULL          | full_name | 214     | NULL |    6 |      100 | Using index |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set
  1. Using where
    在查找结果之后进行过滤，许多where条件里涉及索引中的列，当它读取索引时，就能被检验，因此不是所有带where字句的查询都会显示"Using where"。
mysql> explain select id,first_name,last_name from employees_example where first_name like 'H%';
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table             | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees_example | NULL       | range | full_name     | full_name | 92      | NULL |    2 |      100 | Using where; Using index |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set
  1. Using temporary
    需要创建一个临时表来保存结果。通常发生在使用GROUP BY、DISTINCT或ORDER BY时。
mysql> explain select distinct(position) from employees_example where id between 2 and 4;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | employees_example | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |      100 | Using where; Using temporary |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
1 row in set
  1. Using filesort
    MySQL中无法利用索引完成的排序操作称为“filesort”
mysql> explain select * from employees_example order by last_name;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | employees_example | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | Using filesort |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set
  1. Using join buffer
    在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该根据查询的具体情况来添加索引来改进能。
mysql> explain select * from employees_example t1 left join employees_example t2 on t1.position = t2.position;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set
  1. Impossible WHERE、Impossible HAVING
    where或having语句始终为false，因此select不会返回任何数据
mysql> explain select * from employees_example where 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set

mysql> explain select position,count(1) as cnt from employees_example group by position having 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
| 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     | Impossible HAVING |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
1 row in set
  1. Using index condition
    与“Using where”相似，在索引级别进行内部优化。（索引下推）
mysql> explain 
select * from employees_example where first_name like 'H%' and last_name like 'w%';
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table             | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees_example | NULL       | range | full_name     | full_name | 214     | NULL |    2 |    16.67 | Using index condition |
+----+-------------+-------------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set
  1. Using index for group-by
    表明可以在索引中找到分组所需的所有数据，不需要查询实际的表
  2. no matching row in const table
    对于只有一条数据的表，没有返回匹配的数据
mysql> explain select * from employees_example where id = 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 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     | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set
  1. No tables used
    没有from或from dual语句
mysql> explain select curdate();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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     | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set
  1. Not exists
    查找到一行匹配数据后不再去查询其他数据。通常被用在左联接中，显式地搜索左联接表中不存在的行。SELECT * FROM t1 LEFT JOIN t2 on (…) WHERE t2.not_null_column IS NULL
mysql> explain select * from employees_example t1 left join employees_example t2 on t1.id = t2.id where t2.employee_code is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | NULL                                                           |
|  1 | SIMPLE      | t2    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    6 |    16.67 | Using where; Not exists; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
2 rows in set
  1. Select tables optimized away
    这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
mysql> explain select max(id) from employees_example;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set
  1. Zero limit
    查询有一个LIMIT 0子句并且没有任何数据返回。
mysql> explain select * from employees_example limit 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| 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     | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set
  1. Index merges
    当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
    Using sort_union(…)
    Using union(…)
    Using intersect(…)

参考：

  1. EXPLAIN 命令详解
  2. mysql explain用法和结果的含义
  3. mariadb EXPLAIN
  4. mysql EXPLAIN Output Format
  5. MySQL派生表(derived)优化一例
  6. MySQL · 新特性分析 · 5.7中Derived table变形记
  7. explain之key_len计算
aBang686
关注
mysql学习资源1111
03-27
这个"mysql学习资源1111"的压缩包很可能是为初学者或想要深入理解MySQL的人提供的一系列教程或者课程资料。"mysql45讲"可能是一个包含45个不同主题或章节的系列教程，涵盖了MySQL的基础到高级概念。 首先，让我们从...
mysql-explain之Using temporary
戳一下有机会获得一份额外加个蛋的蛋炒饭
10-28 2274
今天同事问到我一个问题，说某个带有join的查询语句创建视图后，查询视图时带有order by字段时，某些字段会很快，某些会稍微慢一些。 drop view if exists test1; create view test1 as select a.*,b.b_info_outstandingbalance,ifnull(b.new_count,0) new_count,ifnull(b.new_amount,0) new_amount,b.adj_term_year,b.private_percent
mysql explain用法和结果的含义
weixin_34214500的博客
08-10 372
转自：http://blog.chinaunix.net/uid-540802-id-3419311.html explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如： explainselectsurname,first_namefo...
MySQL内部临时表（Using temporary）案例详解及优化解决方法
DBdoctor_off的博客
08-29 1810
内部临时表是MySQL用来辅助复杂SQL聚合计算使用的，会优先占用内存。可用内存大小受会话级参数 tmp_table_size 和 max_heap_table_size同时限制，内存不够时会将内存临时表转化为磁盘临时表，也可以通过SQL_SMALL_RESULT修饰来强制只使用磁盘临时表。
四、MySql中explain的时候出现Using where； Using index； Using temporary； Using filesort
IT蜗牛的专栏
03-11 2万+
通过explain查看sql的执行计划时，Extra字段的值往往会看到Using where; Using index; Using temporary; Using filesort 一、using filesort 在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。 【这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作而已】。 此时，可以进行的优化： ...
mysql use temporary_MySQL优化:explain using temporary
weixin_36364044的博客
02-07 319
什么时候会使用临时表:group/order没设计好的时候1.order没用索引2.order用了索引, 但不是和where相同的索引3.order用了两个索引, 但不是联合索引4.order用了联合索引, 但排序顺序不同序原因:1.不用说2.mysql一次只能用一个索引3.4 其实原因一样, 和索引的结构有关:索引是一个B+树, 比如一个联合索引如下(x, y):(2, 5) - (6, 10)...
MySql调优（三）Query SQL优化（2）explain优化
最新发布
w_t_y_y的博客
09-19 1601
6、索引优化：上面讲到子查询的优化，以及如何建立索引，而且在多个字段索引时，分别对字段建立了单个索引，后面发现其实建立联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下。这一条虽然order by后面的字段和索引中字段顺序相同，但是一个是降序，一个是升序，所以也会产生using filesort，同时升序和同时降序就不会产生using filesort了。order by后面的字段顺序不符合组合索引中的顺序，所以order by后面的不会走索引，即会产生using filesort。
一千行 MySQL 学习笔记.pdf
06-14
### MySQL学习笔记知识点详解 #### 一、MySQL服务管理 **1. 启动MySQL服务** 在Windows环境中，可以通过以下命令启动MySQL服务： ```sql net start mysql ``` **2. 创建Windows服务** 若要手动创建一个...
Mysql Mysql学习资料
01-13
这份"Mysql Mysql学习资料"涵盖了关于MySQL的基础到高级的知识点，对于想要深入理解数据库管理和开发的人员来说是一份宝贵的资源。 1. **MySQL基础知识** - 数据库概念：理解数据库是什么，以及它在存储和管理数据...
Using temporary与Using filesort
热门推荐
祈雨v的博客
06-13 4万+
通过explain查看sql的执行计划时，Extra字段的值往往会看到Using where; Using index; Using temporary; Using filesort，其中此次重点关注Using temporary; Using filesort。 Using temporary Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等...
关于MySql中explain结果filtered的理解
yuxxz的专栏
12-28 4万+
帮忙理解filtered值的含义以及如何评估一个filtered值的好坏
Mysql 调优记： INNER JOIN查询 Using temporary; Using filesort 问题优化
不能说的秘密的博客
03-17 1万+
近期笔者在生产环境中发现一条执行非常慢的sql。大概时间为5s左右，于是乎对改SQL场景进行EXPLAIN 分析，发现一个在执行过程中出现对“Using temporary; Using filesort ”。即在执行过程中产生了临时表来存储结果，并在排序时根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序。经过合理对优化，最终EXPLAIN后，没有再发现明细对SQL性能瓶颈，并且SQL...
mysql temp sort_Mysql-explain之Using temporary和Using filesort解决方案
weixin_33945685的博客
01-19 237
第一条语句explainselect * from tb_wm_shop where is_delete != 1 and is_authentication = 1 ORDER BY create_time DESC大家应该知道使用order by的 字段要使用索引，这条语句中create_time已经创建了索引，但是计划中并没有使用该索引，导致出现了Using filesort文件排序，使其查...
MySQL Explain 中 filtered 列知多少？
杨建荣的学习笔记
07-24 2951
最近同事问，MySQL Explain 中 filtered 列也是表示过滤，为什么过滤是100%的时候，查询效率也很高呢？一开始以为是个别同事遇到的困惑，没太当回事，就进行了一对一解答，后来发现团队不少同学都有相同的疑惑，于是就有了这篇文章。先说结论：对于 MySQL Explain 结果，filtered 列不并需要太关心，重点关注type、key、rows以及extra等列。尤其是rows列...
MySQL中using filesort 与 using temporary
m0_49151953的博客
04-06 1381
使用了文件排序，一般情况下是因为order by 排序索引失效或者没使用索引，在order by 排序之前需要对数据进行过滤，否则索引不会生效，“无过滤不索引”，原因可以查看我之前博客 MySQL开发优化细节 一文。使用了临时表保存结果，一般情况下是因为连表操作时 group by 中索引失效，优化技巧就是需要关注我们的sql执行顺序。以及我们的小表驱动大表，表驱动的 nested loop 原理。
mysql filtered_【Explain】mysql之explain详解（分析索引的最佳使用）
weixin_39819283的博客
01-19 1万+
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain 这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们 深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种...
MySQL EXPLAIN深度解析与应用
在数据库管理中，MySQL的`EXPLAIN`命令是一个非常重要的工具，它可以帮助我们理解SQL查询的执行过程，从而优化查询性能。当我们运行一个`SELECT`语句前加上`EXPLAIN`，MySQL会返回一个执行计划，显示了查询将如何...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值