mysql query optimize - explain、查看mysql自身优化后的语句、Impossible WHERE noticed after reading const tables

mysql

1、mysql query optimize - explain

1.1、简介

• MySQL 提供了一个 EXPLAIN 命令, 可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 然后让我们对 query 进行优化.
• 用法

explain select 语句

1.2、前期准备：建3张表

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
);
INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);
CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
);
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
CREATE TABLE `order_info1` (
  `id`           BIGINT(20)  NOT NULL ,
  `user_id`      BIGINT(20)           DEFAULT NULL,
);

1.3、一个简单的例子和explain结果字段：

mysql> explain select * from order_info\G   //\G制表，使格式更加美观
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 254
          ref: NULL
         rows: 9
        Extra: Using index
1 row in set (0.00 sec)
## 先大概这些字段的含义：
1、id:查询的标志，每个查询会自动分配一个id，比如使用union合并两个查询结果时没有id，为NULL。
2、select_type：当前查询的类型，比如Simple SELECT (not using UNION or subqueries)。
3、table:当前查询所对应的表，比如union对应两个表。
4、type：可能是数据中最重要的字段，展示查询/连接是如何进行的，走索引/或者全表查询等等。
5、possible_keys：当前查询可能会选择的索引，即可以使用该索引找到我们需要的行。
6、key：当前查询实际用到的索引。
7、key_len:所选择索引使用的长度。
8、ref：jion查询的时候会用到，查询结果同哪个值比较。
9、rows：当前查询必须要检测的行数。
10、extra：一些额外的信息。
11、filtered：被过滤条件过滤掉的数据的百分比。(但是根据文档上的意思应该是：过滤掉的数据为1-filtered)
12、partitions：匹配的分区。

1.4、重要的字段详解

• 参考文档： EXPLAIN Output Format
• id:每个查询的标志

mysql> explain (SELECT * FROM user_info  WHERE id IN (1, 2, 3)) UNION (SELECT * FROM user_info WHERE id IN (3, 4, 5))\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: user_info
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: user_info
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
        Extra: Using where
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

• select_type：表示当前查询的的类型
• 比如：字段参考
  
  • SIMPLE:Simple SELECT (not using UNION or subqueries)
  • PRIMARY:最原始/最开始的查询
  • UNION：Second or later SELECT statement in a UNION
  • SUBQUERY：First SELECT in subquery

// 以上面的例子的为例：
id=1的查询最先开始，所以是PRIMARY
id=2的查询是ubion中的第二个slect：所以是UNION
union操作没有id为null，但是select_type是UNION
// 比如写一个SUBQUERY
？？？

• type
  
  • The type column of EXPLAIN output describes how tables are joined. The following list describes the join types, ordered from the best type to the worst:
  • 需要重点关注的字段，展示了该查询是如何连接的，索引或者全表/是如何查询的等等：常见字段参考

# system : 当表中只有一条数据时,且是系统表时；如果不只一条数据时，分析与普通表相同。
mysql> explain select * from time_zone\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: time_zone
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)
# const : 当使用 PRIMARY KEY or UNIQUE index 去查询数据，因为他们是唯一的只用读取一次。
mysql> explain select * from order_info where id=10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)
# eq_ref : 在多表join查询中,表示对于前表的每一个结果,都只能匹配到当前表的一行结果.（且通过PRIMARY KEY or UNIQUE NOT NULL index去查找） 并且查询的比较操作通常是 =, 查询效率较高。
// exapmle：user_info.id(PRIMARY KEY)
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.user_info.id
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)
Tips1:如果操作是>，此时type为all，因为并不能走索引。
Tips2:有点意外的是，好像没有mysql并没有像我们想象的那样去连接，有机会了解一下。
# ref :  ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index .in other words, if the join cannot select a single row based on the key value.
// user_id is leftmost prefix
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: test.user_info.id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)
## fulltext：连接时使用FULLTEXT index
## ref_or_null：This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. (相比ref，多了null搜索)
// 和常数连接，但order_info.user_id是leftmost prefix = ref，同时搜索null = ref_or_null
mysql> EXPLAIN SELECT * FROM  order_info WHERE  order_info.user_id=2 OR order_info.user_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: ref_or_null
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)
## index_merge：This join type indicates that the Index Merge optimization is used. 
index_merge 可以看mysql optimize index_merge这篇日志
或者官方：https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
## unique_subquery：This type replaces eq_ref for some IN subqueries of the following form:
// value IN (SELECT primary_key FROM single_table WHERE some_expr)
## index_subquery：This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:
value IN (SELECT key_column FROM single_table WHERE some_expr)
## range：Only rows that are in a given range are retrieved, using an index to select the rows.（使用索引进行范围查找）
mysql> EXPLAIN SELECT * FROM  order_info WHERE id between 10 and 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 10
        Extra: Using where
1 row in set (0.02 sec)
## index：The index join type is the same as ALL, except that the index tree is scanned. 包含两种情况：
1、如果索引数据满足要求，仅扫描索引即可，the Extra column says Using index.
2、通过索引扫描全表。Uses index does not appear in the Extra column.
mysql> EXPLAIN SELECT id FROM  order_info\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 254
          ref: NULL
         rows: 10
        Extra: Using index
1 row in set (0.00 sec)
## all：全表扫描

• key
  
  • 当前查询所用到的索引，比较重要的字段，可以看自己的索引是否用到。
• key_len
  
  • 描述当前索引哪些被实际使用，在 multiple-part key 比较有用，可以看自己的复合索引是否都使用到了。
• extra：有时也会提供一些有用的信息
  
  • Impossible WHERE：The WHERE clause is always false and cannot select any rows.
  • mpossible WHERE noticed after reading const tables ：MySQL has read all const (and system) tables and notice that the WHERE clause is always false.
• filtered
  
  • Percentage of rows filtered by table condition.(100%表示没有过滤)

1.5、总结

• 1、比较重要的字段：type、key、key_len、rows、extra
• 2、有一小部分还不是很了解后面可以再回来看看，比如select_type：SUBQUERY没写出来。。。
• 3、其它：

## 查看mysql优化后的SQL：
mysql>  EXPLAIN extended  SELECT * FROM user_info, order_info1 WHERE user_info.id = order_info1.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.order_info1.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`user_info`.`id` AS `id`,`test`.`user_info`.`name` AS `name`,`test`.`user_info`.`age` AS `age`,`test`.`order_info1`.`id` AS `id`,`test`.`order_info1`.`user_id` AS `user_id` from `test`.`user_info` join `test`.`order_info1` where (`test`.`user_info`.`id` = `test`.`order_info1`.`id`)
1 row in set (0.01 sec)
## 一个奇怪的现象：其实就是extra的信息
mysql>  EXPLAIN SELECT * FROM  order_info WHERE id =30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)
实际上是：MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

1.6、 参考文档

• Understanding the Query Execution Plan
• MySQL 性能优化神器 Explain 使用分析