MySQL 数据库索引知识点

MySQL数据库索引知识点总结

1. 什么是索引？优缺点？

索引：是对数据库表的一列或者多个列进行排序的一种结构，可快速查找表中的特定信息。

优点：

1. 加快检索速度；
2. 通过创建唯一性索引，可以保证表中的每一行数据的唯一性；
3. 加速表与表之间的连接。

缺点：

1. 创建和维护索引消耗时间；
2. 索引占用物理空间；
3. 对表进行CURD要动态维护索引。

2. 索引的底层数据结构

• 哈希表：

  通过哈希表的key可以快速检索到data；

  哈希表容易产生hash冲突，且不支持顺序和范围查询；

  一般用于精确查找。

• B树（多路平衡查找树），B+树：

  B+树具有有序性，数据都存放在叶子节点上，查询效率非常高，且支持顺序和范围查询；

  在MySQL中，InnoDB和MyISAM都是使用B+树作为默认索引结构，但二者的实现方式不同：

  • 在MyISAM引擎中，

    B+树的叶子节点data域存放的是数据记录的地址；查询时，首先根据key找到数据的地址，在根据地址找到对应的数据。这属于”非聚簇索引“。

  • InnoDB引擎中：

    B+树索引又分为主键索引和辅助索引，主键索引属于聚簇索引，辅助索引属于非聚簇索引；

    主键索引以主键作为B+树索引的key，叶子节点存储着完整的数据记录；辅助索引以非主键作为索引的key，叶子节点存储着主键。所以，使用辅助索引查找时，首先找到主键值，然后回表根据主键索引找到主键对应的数据。

3. 聚簇索引和非聚簇索引

• 聚簇索引：将数据和索引放在一起存储，索引结构的叶子节点存储数据；
• 非聚簇索引：将数据和索引分开存储，索引结构的叶子节点存储的是数据的地址；

4. 索引的类型

• 哈希索引
• B+树索引

5. 索引的种类

1. 唯一索引：唯一索引的列的数据不允许重复，但允许为null，一张表可以创建多个唯一索引；通过创建唯一索引，可保证每一行数据的唯一性。
2. 普通索引：列的数据允许重复，允许为null；一张表可创建多个普通索引；主要用于快速查询。
3. 前缀索引：只适用于字符串类型的数据；前缀索引只对文本的前几个字符创建索引，这样索引的长度更短，查询速度更快。
4. 全文索引：主要为了查询大文本数据中的关键字，是目前搜索引擎数据库使用的一种技术。
5. 联合索引：使用表中的多个字段创建索引。

6. B树和B+树的区别

B树的叶子节点和内部节点既存储key也存储data，而B+树的内部节点只存储key；

B+树的叶子节点通过链表相连，方便顺序和范围查询。

7. 数据库为什么使用B+树不使用B树？

1. B树适用于随机索引，而B+树适用于随机索引和顺序索引（链表的作用）；
2. B+树的空间利用率更高，B+树的内部节点只存储key，这样一个节点可存储更多的索引，使得树的高度降低，加快查询；
3. B+树叶子节点通过链表相连，方便顺序和范围查询；
4. B+树性能更稳定，每次查询都是从根节点到叶子节点，而B树内部节点也存储data，有时候没到叶子节点就找到了data。使用B树时，把频繁访问的data放在根节点附近的地方，可以提高查询效率。

8. 非聚簇索引一定会回表查询吗（索引覆盖）？

不一定

如果查询的数据能在辅助索引上完全获取到就不必回表。比如：一张表存储着id，name，age字段，聚簇索引是以id为key构建的索引，非聚簇索引是以name为key构建的索引，那么“select id, name from user where name = 'Wangwu'”这个查询就不需要回表，通过非聚簇索引就能检索到需要的全部数据。

9. 索引的使用场景

1. 对中大型表建立索引非常有效，对小表或者超大表维护代价相对更高；
2. 对查询较多的表建立索引，对增删改较多的不要建立索引；
3. 不会出现在where语句中的字段不必要建立索引；
4. 多个字段经常被一起查询的话建立联合索引；
5. 字段多且字段值不重复的时候考虑建立唯一索引，字段多有重复的建立普通索引；
6. 索引列的基数越大，索引效果越好，即索引列的区分度越高，索引效果越好。

10. 如何对索引进行优化？

除了考虑9. 索引的使用场景外，还可以从以下方面考虑：

1. 删除长期不用的索引；
2. 考虑在字符串类型的字段上使用前缀索引，会占用更小的空间；
3. 在进行查询时，索引列不能是表达式的一部分，也不能说函数的参数，否则无法使用索引；
4. 将区分度高的索引放在左边（最左前缀匹配原则）。

11. 最左前缀匹配原则

在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配；如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤掉一批数据，直至联合索引全部字段匹配完成，或遇到范围查询，如<, >,between, 以%开头的like等条件，才会停止匹配。

因此，优化索引的时候，可以将区分度高的字段放在最左边，这样能够过滤更多数据。

12. 索引在什么时候失效？

1. where条件中有or；
2. 索引是表达式的一部分；
3. 索引是函数的参数；
4. 在索引的类型上进行隐形数据转换；
5. 使用like查询时以%开头；

13. MySQL如何为表字段添加索引？

1. 添加主键索引：

   alter table 'users' add primary key ('id')

2. 添加唯一索引

   alter table 'users' add unique ('id')

3. 添加普通索引：

   alter table 'users' add index indexName ('id')

4. 添加全文索引:

   alter table 'users' add fulltext ('id')

5. 添加联合索引：

   alter table 'users' add index indexName ('id', 'name', 'age')

6. 删除主键索引：

   alter table 'users' drop primary key

7. 删除其他索引：

   alter table 'users' drop key indexName