索引 - Explain

本文的示例代码参考index_explain

目录

• startup

• explain

• select_type

  • 聚簇索引

  • 覆盖索引

• type

  • 唯一索引

  • 主键索引

• key_len

  • 联合索引

  • 前缀索引

startup

vim user_info.sql

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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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);

vim order_info.sql

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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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');

mycli -uroot -p123456

DROP DATABASE IF EXISTS `explain`;
CREATE DATABASE IF NOT EXISTS `explain`;
USE explain;

source ./user_info.sql;
source ./order_info.sql;

explain

EXPLAIN (SELECT * FROM user_info WHERE id = 2);

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | user_info | <null>     | const | PRIMARY       | PRIMARY | 8       | const | 1    | 100.0    | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+

• select_type: SIMPLE表示此查询不包含 UNION 查询或子查询

• type: const针对主键或唯一索引的等值查询扫描 最多只返回一行数据

• possible_keys: PRIMARY表示可以使用Primary Key优化查询

• key: PRIMARY表示使用Primary Key优化查询

• key_len: 表示查询优化器使用了索引的字节数为8 (BIGINT8字节)

• rows: 表示为了找到所需的行而要读取的行数为1

select_type

EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
UNION
(SELECT * FROM user_info WHERE id IN (3, 4, 5));

+--------+--------------+------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra           |
+--------+--------------+------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY      | user_info  | <null>     | range | PRIMARY       | PRIMARY | 8       | <null> | 3      | 100.0    | Using where     |
| 2      | UNION        | user_info  | <null>     | range | PRIMARY       | PRIMARY | 8       | <null> | 3      | 100.0    | Using where     |
| <null> | UNION RESULT | <union1,2> | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary |
+--------+--------------+------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+

• select_type: PRIMARY表示此查询是最外层的查询 / UNION表示此查询是 UNION 的第二或随后的查询 / UNION RESULT表示UNION的结果

• type: range表示使用索引范围查询 / ALL表示全表扫描

• Extra: Using where表示优化器通过索引回表查询数据 / Using temporary表示使用临时表来存储结果集

什么叫索引回表查询数据? 即先找到索引 再通过索引找到数据所在位置

聚簇索引

• 聚簇索引是指数据存储的方式

• 聚簇索引的索引和数据存储在一起

• 一个表只能有一个聚簇索引

• 聚簇索引不能手动设置 它通常即是主键或非空唯一索引

• 优点

数据访问速度更快 因为索引和数据存储在一起

• 缺点

插入更新代价更高 因为索引和数据存储在一起

覆盖索引

• 覆盖索引是指查询的数据列只用从索引中就能够取得 而不必读取数据行

• 覆盖索引可以进一步提高查询效率

EXPLAIN (SELECT id FROM user_info  WHERE id IN (1, 2, 3));

+----+-------------+-----------+------------+-------+---------------+------------+---------+--------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+------------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | user_info | <null>     | index | PRIMARY       | name_index | 152     | <null> | 10   | 30.0     | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+--------+------+----------+--------------------------+

• Extra: Using index表示直接访问索引就能获取到所需要的数据 不需要通过索引回表查询数据

type

EXPLAIN (SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id);

+----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys             | key                       | key_len | ref                        | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------+
| 1  | SIMPLE      | order_info | <null>     | index  | user_product_detail_index | user_product_detail_index | 254     | <null>                     | 9    | 100.0    | Using where; Using index |
| 1  | SIMPLE      | user_info  | <null>     | eq_ref | PRIMARY                   | PRIMARY                   | 8       | explain.order_info.user_id | 1    | 100.0    | <null>                   |
+----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+----------------------------+------+----------+--------------------------+

• type: index表示全索引扫描 注意ALL是全表扫描 即index则仅仅扫描所有的索引而不扫描数据 / eq_ref表示唯一性索引扫描 即表连接时 前表的每一个结果 都只能匹配到后表的一行结果

唯一索引

• 唯一索引指表中没有两个数据行具有完全相同的键值

主键索引

• 主键索引是一种特殊的唯一索引

• 主健不可为空 而唯一索引可以

• 主键只有一个 而唯一索引可多个

• 主健可作外健 而唯一索引不可以

• 主键即是聚簇索引 没有主键的非空唯一索引是聚簇索引

key_len

EXPLAIN (SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH');

+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+--------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys             | key                       | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | order_info | <null>     | range | user_product_detail_index | user_product_detail_index | 9       | <null> | 5    | 11.11    | Using where; Using index |
+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+--------+------+----------+--------------------------+

• key: user_product_detail_index表示表中的联合索引

• key_len: 9 = 8 (user_id BIGINT) + 1 (product_name NULL)

根据最左前缀匹配原则 查询仅仅使用到了联合索引的user_id字段 因此效率并不高

联合索引

• 联合索引又称复合索引或多列索引

• 联合索引遵守最左前缀匹配原则

EXPLAIN (SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1');

+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+
| 1  | SIMPLE      | order_info | <null>     | ref  | user_product_detail_index | user_product_detail_index | 161     | const,const | 2    | 100.0    | Using index |
+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+

前缀索引

• 前缀索引又称压缩索引或前缀压缩索引

• 前缀索引是一种优化索引大小的解决方案

• 前缀索引对文本的前几个字符建立索引 这样索引更小因此查询更快

• 优点

索引更小查询更快

• 缺点

不能在ORDER BY或GROUP BY中使用前缀索引

不能把前缀索引用作覆盖索引

参考

• 高性能MySQL

• MySQL 性能优化神器 Explain 使用分析

• MySQL 执行计划(Using where,Using index 和 Using index condition)

• 前缀索引，一种优化索引大小的解决方案