数据库索引

0、概要

1、索引有哪些使⽤场景（重点）

2、索引的数据结构（b树，hash）

3、创建索引的原则是什么?（重中之重）

4、使⽤索引查询⼀定能提⾼查询的性能吗？为什么

5、索引有哪些优缺点？

6、讲⼀讲聚簇索引与⾮聚簇索引？

7、百万级别或以上的数据如何删除

8、什么是最左前缀原则？什么是最左匹配原则

9、数据库为什么使⽤B+树⽽不是B树

10、⾮聚簇索引⼀定会回表查询吗？

11、有哪些情况, 索引会失效, 可以简单说说吗?

1、索引有哪些使⽤场景

出现概率: ★★★★★

1)、应该创建索引的场景

主键应该创建主键索引。

频繁作为查询条件的字段应该创建索引。

查询中需要与其他表进⾏关联的字段应该创建索引。

需要排序的字段应该创建索引。

需要统计或分组的字段应该创建索引。

优先考虑创建复合索引。

2)、不应创建索引的场景

数据记录较少的表。

经常需要增删改操作的字段。

数据记录重复较多且分布平均的字段（如性别、状态等）。

索引的选择性是指索引列中不同值的数⽬与表中记录总数的⽐。

索引的选择性越接近于1，创建索引的价值就越⾼。反之就越低。

2、索引的数据结构（B+树，hash）

出现概率: ★★★★★

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全⽂索引，R-Tree索引。这⾥所描述的是索引存储时保存的形式，MySQL默认采⽤的B+Tree， 这⾥主要讲讲B+树的特点:

1.⾮叶⼦节点不存储data，只存储索引(冗余)，可以放更多的索引

2.叶⼦节点包含所有索引字段
3.叶⼦节点⽤指针连接，提⾼区间访问的性能 （快速定位范围查询，例如查询⼤于20，第⼀次io从根节点查询三次定位到20，然后通过后⾯的指针查询⼤于20的数据，就不⽤再从根节点的重新再查询，提⾼性能，叶⼦节点开始结束节点也是⽤指针连接串起来的

3、创建索引的原则是什么?

出现概率: ★★★★

1)、选择唯⼀性索引

2)、为经常需要排序、分组和联合操作的字段建⽴索引

3)、为常作为查询条件的字段建⽴索引

4)、限制索引的数⽬
索引的数⽬不是越多越好。每个索引都需要占⽤磁盘空间，索引越多，需要的磁盘空间就越⼤。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5)、尽量使⽤数据量少的索引
如果索引的值很⻓，那么查询的速度会受到影响。例如，对⼀个CHAR(100)类型的字段进⾏全⽂检索需要的时间肯定要⽐对CHAR(10)类型的字段需要的时间要多。

6)、尽量使⽤前缀来索引
如果索引字段的值很⻓，最好使⽤值的前缀来索引。例如，TEXT和BLOG类型的字段，进⾏全⽂检索会很浪费时间。如果只检索字段的前⾯的若⼲个字符，这样可以提⾼检索速度。

7)、最左前缀匹配原则

8)、查询时使⽤计算，会导致索引失效

4、使⽤索引查询⼀定能提⾼查询的性能吗？为什么

出现概率: ★★★★

不是所有的查询使⽤查询都能提⾼性能， ⽐如下⾯⼏个案例

像 like % xxx% 、不满⾜最左匹配原则的情况下并不能使⽤到建好的索引
MySQL 在可以使⽤多个索引的情况下，查询优化器会根据查询范围的数据量估算索引代价，最坏的是估算完毕后，发现这些索引的字段区分度不⾼，还不如扫全表，于是 Mysql 扫全表了

如果索引的列⽐需要查询的列少，Mysql 会通过聚簇索引回表查询其他字段

如果索引的字段很⼤，每个⻚能存的条⽬就很少，读取时 IO 会消耗更多，⻚ Buffer 轮替的更快

5、索引有哪些优缺点？

出现概率: ★★★

1)、索引的优点

可以⼤⼤加快数据的检索速度，这也是创建索引的最主要的原因。

通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。

2)、索引的缺点
时间⽅⾯：创建索引和维护索引要耗费时间，具体地，当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执⾏效率；空间⽅⾯：索引需要占物理空间。

6、讲⼀讲聚簇索引与⾮聚簇索引？

出现概率: ★★★★
在 InnoDB ⾥，索引B+Tree的叶⼦节点存储了整⾏数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了⼀块，找到索引也就找到了数据。

⽽索引B+Tree的叶⼦节点存储了主键的值的是⾮主键索引，也被称之为⾮聚簇索引、⼆级索引。

聚簇索引与⾮聚簇索引的区别：

⾮聚集索引与聚集索引的区别在于⾮聚集索引的叶⼦节点不存储表中的数据，⽽是存储该列对应的主键（⾏号）
对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进⾏查找，这个再根据聚集索引查找数据的过程，我们称为回表。第⼀次索引⼀般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使⽤全表扫描 。

通常情况下， 主键索引（聚簇索引）查询只会查⼀次，⽽⾮主键索引（⾮聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查⼀次即可注意：MyISAM⽆论主键索引还是⼆级索引都是⾮聚簇索引，⽽InnoDB的主键索引是聚簇索引，⼆级索引是⾮聚簇索引。我们⾃⼰建的索引基本都是⾮聚簇索引。

7、百万级别或以上的数据如何删除

出现概率: ★★★

⽅法⼀:

索引是单独的⽂件，增删改时，当存在索引，会消耗额外io。删除速度和索引数量成正⽐

删除索引

删除需要删除的数据

重新建⽴索引
这个⽅法有⼀个很明显的缺点, 就是在正式环境这个表如果访问频率⽐较⾼的话， 删除索引后有⼤量的SQL查询会导致数据库IO和CPU特别⾼⽅法⼆:

之前我在正式环境的做法是 将删除任务拆分为⼀次删除1w条， 然后把删除任务重新压⼊的异步任务队列⾥⾯。

8、什么是最左前缀原则？什么是最左匹配原则

出现概率: ★★★★
在mysql建⽴联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例： 对列col1、列col2和列col3建⼀个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 testcol1col2_col3 实际建⽴了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上⾯这个查询语句执⾏时会依照最左前缀匹配原则，检索时会使⽤索引(col1,col2)进⾏数据匹配。

9、数据库为什么使⽤B+树⽽不是B树

出现概率: ★★★

B树和B+树的区别主要有两点：
在B树中，你可以将键和值存放在内部节点和叶⼦节点，但在B+树中，内部节点都是键，没有值。叶⼦节点同时存放键和值B+树的叶⼦节点有⼀条链相连，⽽B+树的叶⼦节点各⾃独⽴。

使⽤B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，⼀次读取，可以在内存⻚中获取更多的键，有利于更快地缩⼩查找范围。 B+树的叶节点由⼀条链相连，因此，当需要进⾏⼀次全数据遍历的时候，B+树只需要使⽤O(logN)时间找到最⼩的⼀个节点，然后通过链进⾏O(N)的顺序遍历即可。⽽B树则需要对树的每⼀层进⾏遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

数据库的数据读取都是需要进⾏代价巨⼤的磁盘IO操作，因此，更快地缩⼩范围和更少的读取次数是数据库需要关注的重点。⽽B+树在这些点上⽐B树做的更好。这就是为什么数据库要选⽤B+树作为底层实现。

10、⾮聚簇索引⼀定会回表查询吗？

出现概率: ★★★
不⼀定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进⾏回表查询。⼀个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例⼦，假设我们在学⽣表的成绩上建⽴了索引，那么当进⾏ select score from student where score > 90 的查询时，在索引的叶⼦节点上，已经包含了score 信息，不会再次进⾏回表查询。