MysqlExplain的type字段

join type

mysql 使用 explain 命令进行分析时 type 字段会有下面几种 join type类型

查询效率由好到差

• system

  这个表只有一行（这个表是系统表），这是 join type = const 的一种特殊形式

  EXPLAIN SELECT
      * 
  FROM
      `mysql`.`proxies_priv`

  查询结果如下

  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  | —- | ———– | ———— | ———- | —- | ————- | —- | ——- | —- | —- | ——– | —– |
  | 1 | SIMPLE | proxies_priv | system | | | | | 1 | 100 | | |

• const

  表中只有一行匹配的数据，在 where 条件中和所有

  1)主键

  2) 唯一键

  进行比较时会用到 const

  比如有如下表

  CREATE TABLE `tb_name` (
    `pp1` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `pp2` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `f3` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `f4` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    UNIQUE KEY `pp1` (`pp1`,`pp2`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  
  INSERT INTO `db02`.`tb_name`(`pp1`, `pp2`, `f3`, `f4`) VALUES ('1', '2', '3', '4');
  INSERT INTO `db02`.`tb_name`(`pp1`, `pp2`, `f3`, `f4`) VALUES ('2', '3', '4', '5');

  执行如下查询

  EXPLAIN SELECT
      * 
  FROM
      `tb_name` 
  WHERE
      pp1 = '1' 
      AND pp2 = '2'
  ## 或者
  EXPLAIN SELECT
      * 
  FROM
      `tb_name` 
  WHERE
      pp2 = '2' 
      AND pp1 = '1'
  都会使用 const

  结果如下

  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  | —- | ———– | ——- | ———- | —– | ————- | —- | ——- | ———– | —- | ——– | —– |
  | 1 | SIMPLE | tb_name | | const | pp1 | pp1 | 2046 | const,const | 1 | 100 | |

• eq_ref

  前表的每一行，在后表中只有一行被扫描

  One row is read from this table for each combination of rows from the previous tables.

  有如下两张表

  CREATE TABLE `students` (
    `id` int(11) NOT NULL,
    `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `age` int(3) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  
  INSERT INTO `db02`.`students`(`id`, `name`, `age`) VALUES (1, '张三', 23);
  INSERT INTO `db02`.`students`(`id`, `name`, `age`) VALUES (2, '李四', 33);
  
  CREATE TABLE `scores` (
    `user_id` int(11) NOT NULL,
    `score` int(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;\
  INSERT INTO `db02`.`scores`(`user_id`, `score`) VALUES (1, 100);
  
  
  
  EXPLAIN SELECT
      * 
  FROM
      students t1,
      scores t2 
  WHERE
      t1.id = t2.user_id

  可见 scores 表中只有一条数据，结果如下，

  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  | —- | ———– | —– | ———- | —— | ————- | ——- | ——- | ————— | —- | ——– | —– |
  | 1 | SIMPLE | t2 | | ALL | PRIMARY | | | | 1 | 100 | |
  | 1 | SIMPLE | t1 | | eq_ref | PRIMARY | PRIMARY | 4 | db02.t2.user_id | 1 | 100 | |

  如果再往 score 表中 插入一行数据，则会变为全表扫描

• ref

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

  1) 不是 主键 也不是 唯一键

  或者

  2）使用最左匹配原则进行匹配

  ref 虽然使用了索引，但是查询结果并不唯一，但是并不需要扫描全表，因为索引时有序的

  有如下两个表

  CREATE TABLE `tb_ref` (
    `f1` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `f2` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `f3` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    `f4` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
    KEY `index_tb_ref_f1` (`f1`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  ## 一定要多添 `tb_ref` 一定要多添加几条数据，如果数据太少 mysql 直接会使用全表扫描 type=ALL
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('1', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  INSERT INTO `tb_ref`(`f1`, `f2`, `f3`, `f4`) VALUES ('2', '2', '3', '4');
  
  
  CREATE TABLE `other_table` (
    `ref_f1` varchar(255) COLLATE utf8mb4_bin NOT NULL,
    `age` int(3) DEFAULT NULL,
    `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('1', 22, '张三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('1', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');
  INSERT INTO `other_table`(`ref_f1`, `age`, `name`) VALUES ('2', 23, '张1三');

  执行如下语句

  EXPLAIN SELECT
      * 
  FROM
      tb_ref 
  WHERE
      f1='1'

  结果如下：

  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  | —- | ———– | —— | ———- | —- | ————— | ————— | ——- | —– | —- | ——– | —– |
  | 1 | SIMPLE | tb_ref | | ref | index_tb_ref_f1 | index_tb_ref_f1 | 1023 | const | 1 | 100 | |

  执行如下语句

  EXPLAIN SELECT
      * 
  FROM
      tb_ref t1,
      other_table t2 
  WHERE
      t1.f1 = t2.ref_f1

  返回结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2		ALL					9	100
1	SIMPLE	t1		ref	index_tb_ref_f1	index_tb_ref_f1	1023	db02.t2.ref_f1	1	100

• range

  range 是范围扫描，当比较索引列包含

  =, <>, >, >=, <,<=, IS NULL, <=>, BETWEEN, LIKE, or IN() 

  时，可能产生 range

  Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type. range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators: SELECT FROM tbl_name WHERE key_column = 10; SELECT FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT FROM tbl_name 1351 EXPLAIN Output Format WHERE key_column IN (10,20,30); SELECT FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

覆盖索引（covering index）

理解方式一：就是select的数据列只从索引中就能取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，欢聚话说查询列要被所建的索引覆盖。

理解方式二：索引是高校找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取真个行，毕竟索引的叶子节点存储了它们索引的数据；当能通过读取索引你就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫索引。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可 select *,

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

explan 分析执行顺序的例子

来自尚硅谷的例子