数据库索引简介

前言

在面试中，索引几乎是必问题。在此简单介绍一下，以备不时之需。
参考文章：

1. 数据库—索引面试常见问题
2. Mysql索引面试题
3. MySQL数据库面试题（2020最新版）

1. 索引是什么

索引是对数据库表中一列或多列的值进行排序的一种结构

2. 为什么数据要用索引

• 提升检索速度
• 加速表和表之间的连接

3. 索引的优缺点

优点：

• 加快数据的检索的速度
• 加速表和表之间的连接
• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

缺点：

• 时间方面：创建索引和维护索引要耗费时间
  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间

4. 索引的类型

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

• 全文索引： 是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

• 组合索引：多列值组成一个索引，专用于组合搜索，效率大于索引合并

5. 索引的数据结构

索引的数据结构和具体存储引擎的实现有关；InnoDB引擎的默认的是B+树索引。

• Hash索引

  • 对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快
  • 我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；
  • 如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

• B+ Tree索引（B树索引，B+树实现）
  B+ 树是一种多路平衡查询树 ，节点是天然有序的（左子节点小于父节点、父节点小于右子节点）。
  一个m阶的B+树具有如下几个特征：

  • 1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  • 2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  • 3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

区别

• hash索引，等值查询效率高，不能排序,不能进行范围查询
• B+ 数据有序,范围查询
• Hash索引和B+树索引的时间复杂度是o(1) 和 o(logN)

6. 索引的基本原理

索引的原理很简单，就是把无序的数据变成有序的查询

• 把创建了索引的列的内容进行排序
• 对排序结果生成倒排表
• 在倒排表内容上拼上数据地址链
• 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

7. 索引设计的原则

• 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
• 基数较小的类，索引效果较差，没有必要在此列建立索引
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

8. 创建索引的原则（重中之重）

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

9. 聚簇索引、覆盖索引

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值

• 聚簇索引 ：
  InnoDB 里，B+ Tree的叶子节点存储了整行数据的是主键索引

• 非聚簇索引：
  B+ Tree的叶子节点存储了主键的值的是非主键索引

聚簇索引查询会更快：

• 主键索引树的叶子节点直接就是我们要查询的整行数据了。
• 而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询
• 非聚簇索引不一定会回表查询，涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖

10. 数据库为什么使用B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低
  一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
• B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短
• 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

11. 聚集索引和非聚集索引区别

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致

• 聚集索引
  表记录的排列顺序和索引的排列顺序一致，所以查询效率快
• 非聚集索引
  表记录的排列顺序和索引的排列顺序不一定一致

两种索引都采用B+树结构