索引 - Explain

作者: 诺之林 | 来源:发表于2018-09-07 23:25 被阅读7次

本文的示例代码参考index_explain

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中使用前缀索引

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

参考

网友评论

本文标题：索引 - Explain

本文链接：https://www.haomeiwen.com/subject/kkehgftx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

索引 - Explain

目录

startup

explain

select_type

聚簇索引

覆盖索引

type

唯一索引

主键索引

key_len

联合索引

前缀索引

参考

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读