mysql覆盖索引与回表描述及优化

2019-10-06 21:02:43mysql覆盖索引与回表描述及优化

您现在的位置是: 首页 > 数据 > mysql覆盖索引与回表描述及优化

无意间在博客园上看到了一篇标题很有意思的文章，不懂数据库索引的底层原理？那是因为你心里没点b树，吸引了我，对数据库的存储涉及二叉树，平衡二叉树，B - tree有了了解，文章结尾留下了几个问题，让我想去探索下。

我想探寻的问题：

1、什么是回表查询？

2、什么是索引覆盖？

3、如何实现索引覆盖？

4、like的模糊查询以%开头，会导致索引失效。

5、一个表建的索引尽量不要超过5个。

6、尽量使用覆盖索引。

7、尽量不要在重复数据多的列上建索引。

一：什么是回表查询？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)

普通索引(secondary index)

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值

举个栗子，不妨设有表：

t(id PK, name KEY, sex, flag);

画外音：id是聚集索引，name是普通索引。

两个B+树索引分别如上图：

（1）id为PK，聚集索引，叶子节点存储行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

通常情况下，需要扫码两遍索引树。先查询普通索引树，获取聚集索引，再根据聚集索引去获取普通索引树没有的列数据。

这边我为了测试，本地建了一张表，跑了一千万数据。

CREATE TABLE `ys_test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`age` int(10) NOT NULL DEFAULT '0',
`type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '类型 1关闭',
`add_time` int(10) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=10000046 DEFAULT CHARSET=utf8;

先建个索引，根据age：alter table ys_test add index index_name(`age`)

先来看下不根据索引查询一个信息：

在根据索引查询所有信息：

再看下使用普通索引没有回表即是通过普通索引查询索引树就可以获取到所需要的信息，不用再去聚集索引树查询：

EXPLAIN列的解释：

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

key：实际使用的索引。