mysql创建非聚集索引_一文让你明白聚集索引和非聚集索引？

我们知道Mysql底层使用的B+树来存储索引的，而且数据都存在叶子节点上。对于innodb来说，它的主键索引和行记录是存储在一起的，因此叫做聚集索引。

ps：MyISAM的行记录是单独存储的，不和索引在一起，因此MYISAM是没有聚集索引的。

除了聚集索引，其他的索引都叫非聚集索引。(普通索引，唯一索引等)

另外需要注意的，在innodb中有且只有一个聚集索引。它有三种情况：

若表中存在主键，那主键索引就是聚集索引。

若表中没有主键，那第一个非用空的唯一索引就是聚集索引。

否则，就会隐式的定义一个rowid作为聚集索引。

为了方便理解，下边以 InnoDB 的主键索引和普通索引为例，看下它们的存储结构。

创建一张表，结构如下，并添加几条记录(张三，李四，王五，孙七)：

CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_stu` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bininsert into student(id,name,age) values(1,'zs',12);insert into student(id,name,age) values(5,'ls',14);insert into student(id,name,age) values(9,'ww',12);insert into student(id,name,age) values(11,'sq',13);

在 InnoDB 中，主键索引的叶子节点存储的是主键和行记录，而普通索引的叶子节点存储的是主键(对于 MyISAM来说主键索引的叶子节点存储的是主键和对应行记录的指针，普通索引的叶子节点存储的是当前索引列和对应行记录的指针)。

什么是回表查询？

从上面的索引存储结构，我们可以看到，在主键索引树上，通过主键就可以一次性查出我们所需要的数据，速度非常快。

因为主键和行记录就存储在一起，定位到了主键，也就定位到了所要找的记录，当前行的所有字段都在这(这也是我们为什么说，在创建表的时候，最好是创建一个主键，查询时也尽量用主键来查询)。

对于普通索引，如例子中的name，则需要根据name的索引树(非聚集索引)找到叶子节点对应的主键，然后在通过主键索引树查询一遍，才可以得到要找的记录，这就是回表查询。

什么是覆盖索引？

对于回表查询来说，无疑会降低查询的效率。那么有什么办法让他不回表呢？

那就是索引覆盖

什么索引覆盖，就是在用这个索引查询的时候，使他的索引树查询到的叶子节点的数据可以覆盖你查询的所有字段，这样就可以避免回表。

还是以上边的表为例，现在 zs 对应的索引树上边，只有它本身和主键的数据，并不能覆盖到 age 字段。那么，我们就可以创建联合索引，如 KEY(name,age)。并且，查询的时候，显式的写出联合索引对应的字段(name和age)。

创建联合索引如下，

KEY `idx_stu` (`name`,`age`)

查询语句修改如下，

-- 覆盖联合索引中的字段select id,name,age from student where name='zs' and age=12;

这样，当查询索引树的时候，就不用回表，可以一次性查出所有的字段。对应的索引树结构如下：

PS：图中，联合索引中的字段(name，age)都应该出现在索引树上的，这里为了画图方便，且因数据量太小，没有画出来。只表现出了：叶子节点存储了所有的联合索引字段。

学习的过程是一件痛苦的事，一但学习有了结果，那就变成了快乐既兴奋的事！

让我们红尘作伴、活的潇潇洒洒，对酒当歌、学习快乐！