面试系列-innodb聚簇索引及非聚簇索引

聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

优势：

（1）可以把相关数据保存在一起

（2）数据访问更快，数据和索引保存在同一个 B-Tree

（3）使用覆盖索引扫描的查询可以直接使用页节点的主键值

缺点：

（1）插入速度严重依赖插入顺序：按照主键插入的方式是InnoDB 速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载后最好使用OPTIMIZE TABLE 命令重新组织一下表

（2）更新聚簇索引列的代价很高：因为会强制InnoDB 将每个被更新的行移动到新的位置

辅助索引（非聚簇索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

覆盖索引

1.mysql 可以使用索引直接来获取列的数据，这样就可以不再需要读取数据行。 如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（覆盖）所有要查询的字段的值，那么就称为“覆盖索引”

2.覆盖索引可以提高查询的性能，不需要回表，好处是：

（1）索引条目通常小于数据行，如果只需读取索引，那么mysql 就会减少访问量

（2）索引是按照列值顺序存储的，索引I/O 密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要少得多

（3）一些存储引擎如MyISAM 在内存只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中最大开销的场景

（4)InnoDB 的聚簇索引，覆盖索引对InnoDB 表的特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

exmp：（id主键）

select id from order where user_id between 1 and 3 这时候只需要查ID 的值，而ID 已经在user_id 索引树上，因此可以直接提供查询结果，不需要回表。

select * from order where user_id between 1 and 3 一旦用了select *，就会有其他列需要读取，这时在读完index以后还需要去读data才会返回结果。

这两种处理方式性能差异非常大，特别是返回行数比较多，并且读数据需要 I/O 的时候，可能会有几十上百倍的差异。因此建议根据需要用select *

回表

非主键索引的叶子节点内容是主键的值。在InnoDB 里。非主键索引也被称为二级索引。如：select* from order where user_id=3; user_id是普通索引。则会先搜索user_id 的索引树，得到id=5,再到id 索引树搜索一次，这个过程就是 “回表”。也就是说非主键索引需要查询2次

exmp：

INSERT INTO xttblog(id, k, name) VALUES(1, 2, 'xttblog'), (2, 1, '业余草'), (3, 3, '业余草公众号');

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少，如果数据量大的话，你能很明显的看出两次查询所用的时间，很明显使用主键查询效率更高。