1、概述

定义：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

本质： 索引是数据结构。可以简单理解为：排好序的快速查找数据结构

理解：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
MySQL索引 - 图1

  • 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树
  • 每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

    2、索引的数据结构

    B-Tree索引

    B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：
    MySQL索引 - 图2
    每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
    • 模拟查找关键字29的过程：
      1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
      2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
      3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
      4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
      5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
      6. 在磁盘块8中的关键字列表中找到关键字29

B+Tree 索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。

由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
MySQL索引 - 图3
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树的磁盘读写代价更低
    • B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了

  • B+树的查询效率更加稳定

    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

      聚簇索引（聚集索引）

      聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
      Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
      优缺点

  • 优点

    • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
    • 聚簇索引对于主键的排序查找和范围查找速度非常快
  • 缺点
    • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
    • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
    • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

MySQL索引 - 图4
与一般的B+树不同的是：叶子节点间的指针是双向的

辅助索引（非聚簇索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
MySQL索引 - 图5

总结

MySQL索引 - 图6
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
回表查询：先根据非聚簇索引定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

3、索引失效

  • or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
  • 复合索引未用左列字段;
  • like以%开头;
  • 需要类型转换; 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
  • where中索引列有运算;
  • where中索引列使用了函数;
  • 如果mysql觉得全表扫描更快时（数据少）
  • 在索引列上使用 IS NULL 或 IS NOT NULL操作，索引可能失效效

4、索引的优缺点

优点

  • 提高数据检索的效率，降低数据库的IO成本

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

    缺点

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

    5、索引的分类

    单值索引

  • 定义：即一个索引只包含单个列，一个表可以有多个单列索引

  • 语法： ```sql —和表一起创建 CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name) —单值索引 );

—单独创建单值索引 CREATE INDEX idx_customer_name ON customer(customer_name);

  1. <a name="Us3Wf"></a>
  2. ### 唯一索引
  4. - 定义：索引列的值必须唯一，**但允许有空值**
  5. - 语法：
  6. ```sql
  7. --和表一起创建
  8. CREATE TABLE customer (
  9. id INT(10) UNSIGNED AUTO_INCREMENT,
  10. customer_no VARCHAR(200),
  11. customer_name VARCHAR(200), 
  12. PRIMARY KEY(id), 
  13. KEY (customer_name), --单值索引
  14. UNIQUE (customer_no) --唯一索引
  15. );
  17. --单独创建唯一索引
  18. CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

主键索引

  • 定义：设定为主键后数据库会自动建立索引，innodb为聚簇索引
  • 语法： ```sql —和表一起创建 CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id) —主键索引 );

—单独创建主键索引 ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);

—删除主键索引 ALTER TABLE customer DROP PRIMARY KEY;

—修改建主键索引 必须先删除掉(drop)原索引，再新建(add)索引

<a name="IPPON"></a>
### 复合索引

- 定义：即一个索引包含**多个列**
- 语法：
```sql
--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no), --唯一索引
KEY (customer_no,customer_name) --复合索引
);

--单独创建复合索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

从本质上来说，复合索引还是一棵B+树，不同的是复合索引的键值数量不是1，而是大于等于2，参考下图。
另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。
MySQL索引 - 图7

6、索引的使用场景

适合索引的场景

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题，组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

  • 查询中统计或者分组字段

    不适合索引的场景

  • 表记录太少（有无索引差别不大）

  • 经常增删改的表或者字段
  • Where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引（重复性较高，比如国籍、性别之类的字段）

7、MySQL的Hash索引和B树索引有什么区别

  • hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
  • B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

它们有以下的不同：

  • hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
  • hash索引不支持使用索引进行排序，原理同上。
  • hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。
  • hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
  • hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。