数据库索引

一、索引是什么？为什么要用索引

1.1 索引是什么

• 索引就是数据库的目录。是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里记录的引用指针。
• 索引在存储引擎中实现，所以每种存储引擎支持的所以类型不一定完全相同。
  – MyISAM和InnoDB存储引擎支持BTREE索引。

1.2 索引的优点与缺点

1. 优点：
   1. 提高性能。在目录里面查询数据快
   2. 保持数据唯一性。创建唯一性索引
   3. 减少分组和排序的时间。可以直接通过索引找到某一类（目录里面的第几章而不需要遍历）
2. 缺点
   1. 额外消耗时间。创建和维护索引都要时间，随数据量递增
   2. 额外消耗空间。索引单独存储，要占据磁盘空间。
   3. 动态维护。每次在表里增删改查时都要额外维护索引，降低数据维护速度

二、索引的使用场景

2.1 适合创建索引的字段

1. 主键
2. 经常用于查询条件或排序的列
3. 用于连接的连

2.2 不适合创建索引的字段

1. 频繁更新
2. 不用于查询条件的字段
3. 数据量少的直接遍历得了呗小册子还要目录
4. 数据重复且分布均匀的字段性别
5. 参与列计算的列

三、索引的分类和说明

3.1 普通索引与唯一索引

1. 普通索引：基本索引类型、允许重复与空值
2. 唯一索引：要求值唯一、允许空值、不允许重复。组合索引则要求组合唯一。
3. 主键索引：特殊的唯一索引。不允许空值

3.2 单列索引与组合索引

1. 单列索引：索引只含一个列。一个表里可以有多个单列索引。
2. 组合索引：在表的多个字段组合创建的索引。遵循最左前缀集合。

3.3 聚集索引与非聚集索引

区别聚集、非聚集、聚簇、非聚簇索引

1. 聚集索引：索引项排列顺序与表中数据物理存储顺序一致（可能是稀疏索引也可以是稠密索引）。

   1. 查询命令回传结果以该字段为排序依据
   2. 查询结果返回一个区间的值
   3. 查询结果范围某值相同的大量结果集

2. 非聚集索引：索引顺序与物理存储顺序无关（可能恰好一样，但只是恰好）

   1. 查询数据量较少
   2. 某字段数据唯一性较高

   非聚集索引一定是稠密索引

3.4 聚簇索引与非聚簇索引

实际上不是一种单独的索引类型，而是数据存储方式。

1. 聚簇索引: 索引结构的叶子节点保存记录（行数据）

1. 唯一性。因为数据只有一份
2. 表中行的物理顺序与索引中行的物理顺序相同
3. 默认是主键（也可以不是），如果没有主键，InnoDB会选择一个唯一且非空的索引代替，如果没有就隐式创建一个主键。

2. 非聚簇索引：索引的叶子节点保存数据的位置（行数据的地址或者聚簇索引的键值）

1. InnoDB使用的是聚簇索引，主键组织到一颗B+树，行数据保存在叶子节点上，其余的索引另建一个B+树，叶子节点保存对应的主键值，再通过聚集索引找记录。
2. MyIsam使用非聚簇索引。表数据单独存储，不论是主键的B+树还是别的索引的叶子节点都是表数据的地址，没什么区别。

3.5 稠密索引与稀疏索引

1. 稠密索引：每个记录都设置一个索引
2. 稀疏索引：只为搜索码的某些值建立索引项。为数据记录文件的每个存储块设置一个键-指针对，存储块内存储单元连续。

3.6 全文索引

全文索引详细介绍
索引基本上是精确匹配，但有时需要模糊匹配，基于相似度来查询。全文索引就是为此设计的。
类型为FULLTEXT，允许在索引列中插入重复值与空值。可以在CHAR、VARCHAR或者TEXT类型的列上创建

3.7 空间索引

看看就行

四、索引的失效和原因

4.1 创建了索引一定会走索引吗

不一定。使用组合索引的时候没有遵循最左前缀原则进行搜索，则索引不起作用。

id、name、age字段建立一个组合索引。
索引可以搜索 id、（id、name）、（id、name、age）字段组合，但不能搜索（name）

4.2 如何判断索引是否生效

使用explain语句查看所有是否正在使用

possible_key给出可以选用的各个索引
key给出实际选用的索引

4.3 如何避免索引失效

1. 组合索引遵循最左前缀原则
2. 不在索引上做任何操作。例如计算、函数、类型转换，会导致索引失效转向全表扫描
3. 尽量使用覆盖索引，减少回表次数
4. mysql在使用不等于时无法使用索引会导致全表扫描
5. LIKE以通配符开头会导致索引失效
6. 字符串不加单引号会导致索引失效
7. or连接会索引失效

五、索引的实现原理

MySQL中，索引在存储引擎实现的，不同存储引擎实现方法不同。

5.1 MyISAM索引实现

1. 使用B+树作为索引结构，叶子节点存放数据记录的地址。
2. 主索引和辅助索引没有区别，只是主索引要求key唯一，辅助索引key可以重复
   

5.2 InnoDB索引实现

虽然也使用B+树作为索引结构，但实现与MyISAM完全不同

1. 数据文件本身就是索引文件（MyISAM索引文件与数据文件分离，索引文件仅保存数据记录的地址）。

主索引的叶子节点保存数据记录（聚集索引）

1. 一般默认为主键，如果没有主键；
2. 则找一个唯一且非空的索引作为主索引；
3. 如果再没有，创建一个隐式主键
   

2. 辅助索引不保存地址而是保存主键的值
   

不建议使用过长的字段作为主键——辅助索引会过大
非单调字段作为主键不是很好，插入时B+树频繁分裂调整会导致抵消

5.3 为什么要用B+树

所有叶子节点都按照键值大小顺序放在同一层叶子节点，叶子节点之间通过指针相连。
高扇出性，B+树矮胖，一般只有2~4层，查找某一键值只需要2-4次IO操作。

1. 显著减少IO次数，提高效率
2. 查询效率更加稳定（数据在同一层的叶子节点）
3. 提高范围查询的效率（叶子节点指向下一个叶子节点）

5.4 MySql的Hash索引和B树索引有什么区别

• hash索引底层就是Hash表，调用hash函数获取相应的键值，之后进行回表查询获得实际数据。
• B+树是多路平衡查找树，每一次查询都从根结点出发到叶子节点获得键值，根据查询判断是否需要回表查询数据（这里根据引擎不同、查找内容不同有区分）

1. 等值查询一般hash索引更快（hash碰撞的话不行）
2. hash索引效率不稳定，B树索引稳定
3. hash索引不支持范围查询、不支持索引排序
4. hash索引不支持模糊查询以及多列索引的最左原则匹配（hash函数不可预测）
5. hash索引任何时候都无法避免回表查询数据，B+树在某些时候可以避免回表（聚簇索引、覆盖索引）

六、索引的重构

6.1 什么时候需要重构索引

1. 表中发生了频繁的update、delete操作
2. 表上发生了alter table move操作（move操作相当于合并所有数据，rowid发生了变化，必须重构）

6.2 如何判断索引是否需要重建

1. 一般看索引是否倾斜严重，是否浪费了空间
2. 在相同的session中chaxun索引状态表（当深度>=4或者删除行占据所有行比例20%以上时考虑重建索引）

6.3 如何重建索引

1. drop原索引，再创建索引

相当耗时，一般不使用

2. rebuild重建索引

alter index indexname rebuild;
alter index indexname rebuild online;

由于rebuild 使用现有索引项重建索引，速度较快，建议使用，

1. • rebuild以索引快速扫描或者表扫描（取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中的有排序操作。
   • rebuild online 执行表扫描获取数据，重建过程中有排序的操作。
2. rebuild会阻碍DML操作，rebuild online不会
3. rebuild online会产生一个系统临时日志表，所有rebuild online时索引的变化都记录在表中，当新的索引创建完成后，把这个表的记录维护到新的索引中，drop掉旧的索引，完成rebuild online

3. 重建索引的注意事项

1. 执行rebuild操作时，检查表空间是否足够
2. online虽然允许DML，但建议在业务不繁忙的时间段进行
3. rebuild操作会产生大量的redo log