索引

索引机制概述

• 索引的创建方式
• CREATE、ALTER、DDL
• 查询、删除、指定索引
• 索引的本质
  • 索引也是数据的一种，因此与表数据相同
  • 索引的数据结构决定索引的存储方式
  • 索引机制是由存储引擎实现，不同存储引擎下的索引文件，保存格式也不相同

索引分类

数据结构层次

• B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。
• Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
• R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。
• T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。
• 创建索引时，通过USING关键字显示指定索引的数据结构
  • CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;
  • 必须要为当前引擎支持的结构

字段数量层次

• 单列索引（基于单个字段建立的索引都可以被称为单列索引）
  • 唯一索引、主键索引、普通索引
• 多列索引
  • 多种叫法：组合索引、联合索引、复合索引、多值索引...
• 前缀索引
  • 创建索引时指定的length字段
  • length：如果字段存储的值过长，选用值的前多少个字符创建索引

功能逻辑层次

• 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
• 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
• 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
• 全文索引
  • 只能创建在CHAR、VARCHAR、TEXT等类型字段上
  • 字符数量必须大于3才生效
• 空间索引
  • GIS空间数据
  • GEOMETRY、POINT、LINESTRING、POLYGON四种数据类型字段建立

存储方式层次

• 聚簇索引
  • 逻辑上连续且物理空间上的连续
• 非聚簇索引
  • 逻辑上的连续，物理空间上不连续 一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引

应用

MySQL各索引的优劣分析

优势

• 查询速度直线提升，数据量越大效果越明显。
• 创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。
• 在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。
• 连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。
• 索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。
• 从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

弊端

• 建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。
• 写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。
• 写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

主键索引存在的陷阱

• 主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的
• 为什么UUID不适合做主键索引？
  • UUID是无序的
  • 每次新增都需要计算位置，节点移动，让出位置存储，确保索引的有序性
• 联合索引存在的矛盾
  • 最左原则
  • 左边第一个字段不存在，无法使用索引

前缀索引存在的弊端

• 短小精悍，利用字段前N个字符创建索引
• 由于索引中，未存储字段完整值，所以无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也无法完成覆盖扫描等操作

全文索引存在的硬伤

• 全文索引的文件会更大
  • 全文索引基于分词实现的，对字段建立全文索引后，会对该字段做分词处理，分词结果会存储在全文索引中，因此全文索引的文件会额外的大！
• 手动更新全文索引中的数据
  • 全文索引对字段值做分词，因此当修改字段值后，分词是需要时间的，所以修改字段数据后不会立马自动更新全文索引，此时需要写存储过程，并调用它手动更新全文索引中的数据。
• 中文支持不友好
  • 对中文支持不够友好，无法精准的对一段文字做分词，存在些许精准度问题

唯一索引存在的快慢问题

• 查比普通索引效率更高
• 插入比普通索引慢

哈希索引的致命问题

• 查询速度更快
• 无序的，所以无法做排序、分组等工作

建立索引的正确姿势

索引查询时的回表问题

• 索引节点存放的是指向聚簇索引的ID值
• 一条查询SQL经历了两次查询才获取到数据，这个过程则被称之为回表

建立索引时需要遵守的原则

• 经常频繁用作查询条件的字段应酌情考虑为其创建索引。
• 表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
• 建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
• 建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
• 建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
• 经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
• 对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
• 尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

其他注意点（牢记）

• 值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
• 一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。（区分性）
• 索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
• 一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
• 建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
• 当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
• 索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

索引失效与使用索引的正确姿势

索引失效的那些事儿

• 查询中带有OR会导致索引失效
• 模糊查询中like以%开头导致索引失效
• 字符类型查询时不带引号导致索引失效
  • EXPLAIN SELECT * FROM zz_users WHERE user_name = 111
• 索引字段参与计算导致索引失效
  • EXPLAIN SELECT * FROM zz_users WHERE user_id - 1 = 1;
  • 运算也包括+、-、*、/、!.....等一系列涉及字段计算的逻辑
• 字段被用于函数计算导致索引失效
  • EXPLAIN SELECT * FROM zz_users WHERE SUBSTRING(user_name,0,1) = "竹子";
  • 使用SUBSTRING函数对user_name字段进行了截取
• 违背最左前缀原则导致索引失效
• 不同字段值对比导致索引失效
  • EXPLAIN SELECT * FROM zz_users WHERE user_name = user_sex;
• 反向范围操作导致索引失效
  • >、<、between、like、in...等操作时，索引是可以正常生效的
  • NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等不生效
  • 并非所有的正向范围操作都会走索引，例如IS NULL就不会走，它的反向操作：IS NOT NULL同样不会走
• 走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据

使用索引的正确姿势

正确用法

• 查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
• 模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
• 编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
• 一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
• 对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
• 多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
• 对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
• 在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

索引覆盖

联合索引中完全包含查询字段，因此可以直接通过联合索引获取到数据

索引下推

将Server层筛选数据的工作（不需要聚簇索引中回表拿数据），下推到引擎层处理

MRR(Multi-Range Read)机制

针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率

Index Skip Scan索引跳跃式扫描

查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样

原理

MySQL索引为何使用B+树结构？

索引为什么不支持数组、链表、队列等结构

以上结构是按序并排存储，那走索引依旧等价于走全表，没意义

普通SQL的全表扫描过程

• 1、由于MySQL中会使用局部性原理的思想，所以可能只需发生一次磁盘IO就能将很多数据全部读到内存，然后会在内存中对本次读取的数据逐条判断
• 局部性原理
  • 比如目前有三块内存页x、y、z是相连的，当前在操作x页的数据，那么对于y，z这两页数据则会提前将其载入到高速缓冲区，减少磁盘IO
• 发生磁盘IO，磁盘寻道，读到数据载入内存(触发1)，根据SQL条件对读到的数据做判断，不符合，重复执行，符合，写入到结果集中，然后继续判断其他数据，直到数据没有，结果集返回

索引为何不选择二叉树？

• 如果索引的字段值是按顺序增长的，二叉树会转变为链表结构。
• 由于结构转变成了链表结构，因此检索的过程和全表扫描无异。
• 由于树结构在磁盘中，各节点的数据并不连续，因此无法利用局部性原理。

索引为何不选择红黑树？

• 虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
• 每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理

索引为何不选择B-Tree？

• 虽然对比之前的红黑树更矮，检索数据更快，也能够充分利用局部性原理减少IO次数，但对于大范围查询的需求，依旧需要通过多次磁盘IO来检索数据。

索引为何要选择B+Tree？

• 叶节点和叶子节点
  • 叶节点：不会存储数据，仅存储指向叶子节点的指针，好处让叶节点存储更多元素，确保树的高度不会由于数据增长而变得很高
  • 叶子节点：存储实际的数据。同时叶子节点之间都有一根单向指针指向下一个节点，使得叶子节点之间形成一个单向链表结构，方便范围取值
• 最下面的一排节点之间，都存在一个单向指针，指向下一个节点所在的位置
• MySQL索引底层的真正结构
  • 实际是B+Tree的变种，叶子节点之间是互存指针的，所有叶子节点是一个双向链表结构

建立索引时那些不为人知的内幕

常用存储引擎的数据存储

使用MyISAM引擎的表

• *.frm：该文件中存储表的结构信息。
• *.MYD：该文件中存储表的行数据。
• *.MYI：该文件中存储表的索引数据。
• MyISAM引擎的表数据和索引数据，是分别放在两个不同的磁盘文件中存储的
• MyISAM引擎并不支持聚簇索引（聚簇索引要求表数据和索引数据存储在同一块空间）
• MyISAM的.MYI索引文件中，存储的是表数据所在的地址指针

使用InnoDB引擎的表

• *.frm：该文件中存储表的结构信息。
• *.ibd：该文件中存储表的行数据和索引数据。
• InnoDB引擎，表数据和索引数据都一起放在.ibd文件中
  • 支持聚簇索引
  • 未主动创建聚簇索引，会自动选择表中的主键字段，作为聚簇索引的字段。如果表中未声明主键字段，则会选择一个非空唯一索引来作为聚簇索引。如果表中依旧没有非空的唯一索引，InnoDB则会隐式定义一个主键来作为聚簇索引

手动创建索引后发生的事情

• 没有数据时，直接构建一些索引的信息，写到不同引擎对应的文件中
• 有数据时，比较复杂，创建索引要做一系列判断、处理工作
  • 唯一索引：判断索引字段的每个值是否存在重复值，如果有则抛出错误码和信息。
  • 主键索引：判断主键字段的每个值是否重复、是否有空值，有则抛出错误信息。
  • 全文索引：判断索引字段的数据类型是否为文本，对索引字段的值进行分词处理。
  • 前缀索引：对于索引字段的值进行截取工作，选用指定范围的值作为索引键。
  • 联合索引：对于组成联合索引的多个列进行值拼接，组成多列索引键。

索引键的大小会随着值长度变化吗？

• 定长类型就不会变化（int）
• 变长类型就会随之发生改变(varchat)

索引内部查询与维护的过程

• 聚簇索引查找数据的过程
  • 根据条件字段，去内存中找到聚簇索引的根节点，然后根据节点中记录的地址去找次级的叶节点，最后再根据叶节点中的指针地址，找到最下面的叶子节点，从而获取其中的行数据
  • 聚簇索引中，定位到了索引键，即代表着取到了数据，毕竟索引和行数据是一起存储的
• 非聚簇索引查找数据的过程
  • 前面步骤一样，后面
    • InnoDB:最终会取到一个聚簇索引的字段值，然后再做一次回表查询
    • MyISAM:取到指针地址，直接触发磁盘IO再次读取数据
• 写SQL执行时索引的维护过程

  • 插入数据时索引的变化

    • 主键/聚簇索引的变化
      • 最后的位置插入索引值

  • 普通/非聚簇索引的变化

    • 先对插入的字段值进行计算，然后根据计算出的值，决定当前数据在B+Tree中的索引位置，计算好之后再执行插入工作
    • 插入到对应的位置，此时树的节点就会发生裂变，后续的所有叶子节点都需要往后移动，这个开销是较大的
• 删除数据时索引的变化
  • 过程大致是相同的，重点是：先删非聚簇索引信息，再删聚簇索引的信息，因为聚簇索引上存放着行数据，如果先把聚簇索引删了，就无法找到非聚簇索引上的信息了
• 更改数据时索引的变化
• 修改的本质就是先删再插入

参考文章：juejin.cn/column/7140…