MySQL的索引

---

1、什么是索引？

答：索引是对数据库表的一列或者多列的值进行排序的一种数据结构，使用索引可以快速访问数据表中的特定信息。

2、讲一下索引的优缺点是什么？

答：

优点

• 大大加快数据检索的速度。
• 将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)。
• 加速表与表之间的连接。

缺点

• 从空间角度考虑，建立索引需要占用物理空间。
• 从时间角度考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

3、索引的数据结构是什么？

答：索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。

InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

• 因为B+树具有有序性，并且所有的数据都存放在叶子节点，所以查找的效率非常高，并且支持排序和范围查找。
• B+树的索引又可以分为主索引和辅助索引。其中主索引为聚簇索引，辅助索引为非聚簇索引。
  • 聚簇索引是以主键作为B+ 树索引的键值所构成的B+树索引，聚簇索引的叶子节点存储着完整的数据记录；
  • 非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引，非聚簇索引的叶子节点存储着聚簇索引（主键值）。
  • 所以使用非聚簇索引进行查询时，会先找到主键值，然后到根据聚簇索引找到主键对应的数据域（回表）。

哈希索引

• 哈希索引是基于哈希表实现的。
• 对于每一行数据，存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，
• 将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。

4、Hash索引和B+树索引的区别？

答：区别如下：

1. 因为两者数据结构上的差异导致它们的使用场景也不同：
   • 哈希索引一般多用于精确的等值查找。
   • B+索引则多用于除了精确的等值查找外的其他查找。
   • 在大多数情况下，会选择使用B+树索引。
2. B+索引支持排序，哈希索引不支持排序，因为哈希表是无序的。
3. B+索引持范围查找，哈希索引不支持范围查找。
4. 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
5. 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

5、索引的类型有哪些?

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT

• FULLTEXT为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

• 全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

• HASH为哈希索引，由于HASH的唯一及类似键值对的形式，很适合作为索引。
• HASH索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。
• 但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

• B-TREE为B树索引，就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树）。
• 每次查询都是从树的入口root开始，依次遍历node，获取leaf。
• 这是MySQL里默认和最常用的索引类型。

4. RTREE

• R-TREE为空间索引，在MySQL很少使用。
• 仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
• 相对于BTREE，RTREE的优势在于范围查找。

6、索引的种类有哪些？

答：索引有以下几种：

• 主键索引：数据列不允许重复，不能为NULL，一个表只能有一个主键索引。
• 组合索引：由多个列值组成的索引。
• 唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。
• 全文索引：对文本的内容进行搜索。
• 普通索引：基本的索引类型，可以为NULL。

7、B树和B+树的区别？

答：B树和B+树最主要的区别主要有两点：

• B树中的内部节点和叶子节点均存放键和值。而B+树的内部节点只有键没有值，叶子节点存放所有的键和值。
• B＋树的叶子节点是通过相连在一起的，方便顺序检索。而B树并不是。
  两者的结构图如下。

8、数据库为什么使用B+树而不是B树？

答：有以下四个原因：

• B树适用于随机检索，而B+树适用于随机检索和顺序检索；
• B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。
• B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便。
• B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。

那在什么情况适合使用B树呢?

• 因为B树的内部节点也可以存储值，所以可以把一些频繁访问的值放在距离根节点比较近的地方，这样就可以提高查询效率。

9、什么是聚簇索引，什么是非聚簇索引？

答：聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

• 聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。
• 非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引，MyISAM中无聚簇索引。

10、非聚簇索引一定会进行回表查询吗？

答：不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们以employee的age字段创建了非聚簇索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

11、索引的使用场景有哪些？

答：索引的使用场景有：

1. 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
2. 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
3. 如何表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
4. 一般不会出现在where条件中的字段就没有必要建立索引了。
5. 多个字段经常被查询的话可以考虑联合索引。
6. 字段多且字段值没有重复的时候考虑唯一索引。
7. 字段多且有重复的时候考虑普通索引。

12、索引的设计原则？

答：索引的设计原则有：

1. 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。
2. 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。
   • 比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。
3. 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。
4. 尽量利用最左前缀。
5. 不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

13、如何对索引进行优化？

答：对索引的优化其实最关键的就是要符合索引的设计原则和应用场景，将不符合要求的索引优化成符合索引设计原则和应用场景的索引。

除此之外还有：

• 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，因为这样无法使用索引。例如 select * from table_name where a + 1 = 2。
• 将区分度最高的索引放在前面。
• 尽量少使用select *。

使用索引查询时性能一定会提升吗？
答：不一定，前面在索引的使用场景和索引的设计原则中已经提到了如何合理地使用索引，因为创建和维护索引需要花费空间和时间上的代价，如果不合理地使用索引反而会使查询性能下降。

14、什么是前缀索引？

答：前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

使用场景

• 前缀的区分度比较高的情况下。

建立前缀索引的方式

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

这里面有个prefix_length参数很难确定，这个参数就是前缀长度的意思。

通常可以使用以下方法进行确定，先计算全列的区分度

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

然后在计算前缀长度为多少时和全列的区分度最相似。

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

不断地调整prefix_length的值，直到和全列计算出区分度相近。

15、什么是最左匹配原则？

答：最左匹配原则就是从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。

---

例如：建立索引(a,b,c)，大家可以猜测以下几种情况是否用到了索引。

第一种

select * from table_name where a = 1 and b = 2 and c = 3
select * from table_name where b = 2 and a = 1 and c = 3

上面两次查询过程中所有值都用到了索引，where后面字段调换不会影响查询结果，因为MySQL中的优化器会自动优化查询顺序。

第二种

select * from table_name where a = 1
select * from table_name where a = 1 and b = 2  
select * from table_name where a = 1 and b = 2 and c = 3

答案是三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。

第三种

select * from table_name where b = 1
select * from table_name where b = 1 and c = 2

答案是这两个查询语句都没有用到索引，因为不是从最左边开始匹配的

第四种

select * from table_name where a = 1 and c = 2

这个查询语句只有a列用到了索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的。

第五种

select * from table_name where a = 1 and b < 3 and c < 1

这个查询中只有a列和b列使用到了索引，而c列没有使用索引，因为根据最左匹配查询原则，遇到范围查询会停止。

第六种

select * from table_name where a like 'ab%'; 
select * from table_name where  a like '%ab'
select * from table_name where  a like '%ab%'

对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。

---

16、索引在什么情况下会失效？

答：在上面介绍了几种不符合最左匹配原则的情况会导致索引失效，除此之外，以下这几种情况也会导致索引失效。

• 条件中有or，例如 select * from table_name where a = 1 or b = 3。
• 在索引上进行计算会导致索引失效，例如 select * from table_name where a + 1 = 2。
• 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设select * from table_name where a = '1' 会使用到索引，如果写成 select * from table_name where a = 1 则会导致索引失效。
• 在索引中使用函数会导致索引失效，例如 select * from table_name where abs(a) = 1。
• 在使用like查询时以%开头会导致索引失效。
• 索引上使用！、=、<>进行判断时会导致索引失效，例如 select * from table_name where a != 1。
• 索引字段上使用 is null/is not null判断时会导致索引失效，例如 select * from table_name where a is null。