mysql 聚簇索引和非聚簇索引_Mysql面试题：聚簇索引与?聚簇索引、覆盖索引、组合索引...

MySQL数据库面试题

1、 聚簇索引与⾮聚簇索引 ‍‍

聚簇索引和⾮聚簇索引是建⽴在B+树的基础上 。

聚簇索引：key为主键，value为其余列的数据。 ⼀个表只能有⼀个聚簇索引。

⾮聚簇索引：除了聚簇索引外的都叫⾮聚簇索引 。

对于MyISAM的主键索引来说，它的⾮聚簇索引是key为主键，value为⾏号(不⼀定) 。

对于MyISAM的⼆级索引来说，它的⾮聚簇索引是key为其他列，value为⾏号(不⼀定) 。

对于InnoDB的⼆级索引来说，它的⾮聚簇索引是key为其他列，value是主键。

⾮聚簇索引也叫⼆级索引

⾮聚集索引与聚集索引的区别在于⾮聚集索引的叶⼦节点不存储表中的数据，⽽是存储该列对应的主键(⾏号)。

对于InnoDB来说，想要查找数据我们还需要根据 主键再去聚集索引中进⾏查找，这个再根据聚集索引查找数据的过程，我们称为回表。第⼀次索引⼀般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使⽤全表扫描。

通常情况下，主键索引查询只会查⼀次，⽽⾮主键索引(⾮聚簇索引)需要回表查询多次。当然，如果是覆盖索引的话，查⼀次即可 。

注意：MyISAM⽆论主键索引还是⼆级索引都是⾮聚簇索引，⽽InnoDB的主键索引是聚簇索引，⼆级索引是⾮聚簇索引。我们⾃⼰建的索引基本都是⾮聚簇索引。

2、 覆盖索引

如果⼀个索引包含(覆盖)所有需要查询字段的值，我们就称之为"覆盖索引"。如 select id from tab where id = 1 ，并且id是 tab 的索引，这就是⼀个覆盖索引。

当⾮聚簇索引是覆盖索引的时候，就只⽤查询⼀次即可。

3、组合索引(新浪微博)

因为每个select只能选择⼀个索引，当where条件过多时，我们可以考虑建⽴联合索引，即把多个列作为索引：

1 create index inx_col1_col2 on tab (col1,col2);

问这个问题主要是引出下⾯的最左前缀原则

4、 MySQL的最左前缀原则

这⾥主要是向⾯试官说明组合索引在B+树上如何被创建的，对于索引 (a,b,c) ，引擎会先按照a排序，当a相等时，再按照b排序，当b相等时，再按照c排序。

对于索引 (a,b,c) 来说，能命中的where语句有

1. where a = 1 , where a = 1 and b = 1 和

where a = 1 and b = 1 and c = 1

2. where a like '1%' ，对于这个，可能会引出前缀索引 。

注意：

对于表tab id,name,gender 来说：

当SQL为 select name, id from tab where name = 'stalern' ，索引为 id, name ，此时索引是有效的，⾛得是覆盖索引，但是命中率特别低

当SQL为 select * from tab where id > 0 and id < 10 and name > 'stalern' ，索引为id,name ，此时并不会⽤到 id,name ，⽽是其中的 id 索引，没有 name 。但是当SQL为 select * from tab where id = 0 and name >'a' and name < 'c' 时，这个索引是可以全部⽤到的。