一、深入理解MySQL索引

系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

索引是什么：

• 帮助MySQL高效获取数据的排好序的数据结构，MySQL默认用的是B+Tree

磁盘中的数据存储：

• 相对于内存，磁盘的读写（I/O）操作会消耗更多的性能
• 存入磁盘中的数据在空间上可能不是连续的（两次存储操作的过程中可能有其他应用数据的插入）
• 可以在存入磁盘前，一次请求更多的空间，保证一次存入的数据在空间上是连续的，比如将连续的数据存入同一磁盘页中

一、索引数据结构详解

1.1 Hash索引

简介：对索引数据进行一次hash计算，根据计算结果将数据放入对应的hash桶中，且存入的数据包括索引值+行数据的磁盘地址
特性：

• 能满足“=”、“in”，但不支持范围查询
• 存在hash冲突问题，当hash冲突少时（链表长度短）查询速度会很快（一定程度上会比B+树效率高），但是冲突较大时查询效率就会慢很多。
• 当

是

1.2 B+树索引

1.2.1 B-Tree简介

特性：

• 区别于二叉树，树的高度会低很多（一个节点能存多个元素，也可以有多个子节点）
• 叶子结点有相同的深度
• 叶子结点指针为空
• 每个结点内的数据从左到右递增
• 子结点数据从左到右递增

1.2.2 B+Tree简介

特性：

• 基于B-Tree的变种
• 叶子结点间用指针连接，提高叶子节点间的访问性能（当进行范围查找时效率会非常高）

1.3 MySQL中的B+Tree索引

1.3.1 特性

• 非叶子结点只存储索引值
• 叶子结点除了索引值外，还存储了data（索引对应行在磁盘中的位置，或者索引对应行的所有其他列，跟具体的存储引擎有关具体在下文中介绍）
• 子节点的首位元素会被冗余到父节点中，所以叶子节点中包含了所有的索引值

1.3.2 MySQL为什么使用B+树

我们在前言中简单了解了为什么要使用磁盘页，所以为了优化查询时间，在mysql中一般将B+树中的一整个节点作为一个磁盘页进行存储。

1.3.2.1 索引数据的查询过程

在测试B+树的性能前，先简单介绍下索引数据的查询过程：

假设要查询索引值=30对应的数据，步骤如下：

1. 将根节点放入内存（加快查询），查询30（使用二分查找等快速查询的算法），发现在字节点中
2. 重复查询步骤，直到在叶子节点中找到对应的位置并返回数据

1.3.2.2 使用索引（B+Tree）带来的性能提升

我们先来看下mysql中默认文件页的大小：

SHOW GLOBAL STATUS like 'Innodb_page_size';

Q：为什么mysql文件页默认16K？
A：mysql经过了大量优化后得出的结论，能改但是不建议改，改小了可能达不到性能瓶颈，改大了可能导致内存压力过大

知道了mysql文件页大小后，现在我们来计算下使用B+树能带来多大的性能提升：

• 叶子节点
  • 假设我们一行数据大小为1K（一般不会超过1K，除非字段特别多或者特别大）
  • 那么一页就能存16行数据，也就是一个叶子结点能存16行数据
• 非叶子节点
  • 假设索引的字段类型为bigint，那么大小就是8B
  • 在Innodb中指针索引的大小为6B
  • 那么一页就能存16384/(8+6)=1170行数据。

那么一个高度为3的B+树能够存储的索引值个数（也就是行数）为：1170 *1170*16=2千万

由此可见，在2千万的数据下，使用索引只需要查询3次，而如果没有使用索引的话最多需要遍历2千万次。而且MySQL会让根节点常驻内存中，甚至在较大版本中将所有非叶子节点都放到内存中。

二、存储引擎简介

2.1 MySQL中的文件存储

存储引擎主要是是针对表的

mysql中每个数据库对应在OS中都会有一个目录：

• Linux中：/var/lib/mysql/*

以及库内对应会有一些表相关的文件：

• 存储引擎是MyISAM, 会有3类文件
  • *.frm，表定义，是描述表结构的文件。
  • *.MYD，"D表示Data"数据信息文件，是表的数据文件。
  • *.MYI，"I表示Index"索引信息文件，是表数据文件中任何索引的数据树
• 存储引擎是InnoDB, 会有2类文件：
  • *.frm–表结构的文件。
  • *.ibd–表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

2.2 MyISAM简介

索引特性：

• 非聚集索引（聚集也可以叫聚簇）
  • 定义：索引文件根数据文件是两个文件
  • 叶子节点中的data为，索引值对应行在磁盘中的地址
• 数据查找步骤：
  • 在*.MYI索引文件中找到索引值对应数据的地址
  • 根据数据地址去*.MYD文件中匹配对应的数据

2.3 InnoDB简介

索引特性：

• 聚集索引
  • 定义：索引跟数据是同一个文件
  • 叶子节点中的data为，一整行的完整数据（包含当前索引列的数据以及所有其他列在当前行的数据）
  • primary key就是聚集索引
• 表数据文件（*.ibd）也是由B+树组织的
• 数据查找步骤：
  • 在*.ibd文件中找到索引值对应的位置，并返回完整数据

经过对比能够发现，Innodb的索引查询速度会更快，因为找到索引位置后就能拿到数据了，也不需要再去跨文件查询

2.4 Q & A

Q：为什么建议Innodb表必须建主键，并且推荐使用整型的自增主键？
A：减少mysql的工作，具体原因如下：

1. *.ibd文件由B+树进行组织，所以需要相应的key
2. 如果表中有主键，那么就用主键作为key
3. 如果表中没有主键，那么就会按顺序选择一个列（列中的数据不是全部相等的）作为key，如果没有找到的话，那么就会自动建一个隐藏列（唯一ID，类似rawID）作为key
4. 整型自增key的优势
   • 方便比较（B+树的查询/构建都需要用到）
   • 占用空间小
   • 自增能减少维护B+树的额外操作（自增插入时元素都往最右侧插入，当节点满了只需开辟一个新节点。当索引是非自增插入时，索引可能会插入到中间位置，这时候如果节点元素满了，那么就需要对节点进行分裂以及平衡等一系列操作）

三、非主键索引

主键索引：

InnoDB表中只能有一个聚集索引（一般是用主键）

二级索引（非主键索引）：
叶子节点中的data存储的是对应聚集索引的值（主键值），原因有：
- 节省存储空间
- 保证一致性，变更数据时先变更主键索引中的数据，然后再去维护非主键索引
查询时需要先拿到对应的主键值，然后再去主键索引中查数据（这个过程被称为回表）

联合索引（复合索引）
一张表中不推荐建太多的单建索引，利用少量的复合索引将绝大部分的sql查询都覆盖到

数据结构：

联合主键索引：
联合索引的值能唯一确定一条数据
data中存储的是所有数据

联合辅助索引：
联合索引的值不能确定唯一一条数据
data中存储的是主键
拿到主键后需要进行回表操作

索引最左前缀原则：