千字看懂MySQL索引

索引

1 索引基础

索引是什么？

• 帮助MySQL快速获取数据的数据结构
• 索引往往是存储在磁盘上的文件中（可独立存储、可与数据一起存储）
• 基本默认都是使用B 树结构组织（多叉搜索平衡树）

索引的优点是什么？

• 提高数据库的检索效率，降低数据库的IO成本
• 可通过索引列对数据进行排序，降低了数据排序的成本
  • 被索引的列会自动进行排序（包括单列索引、组合索引）
  • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多

索引的劣势是什么？

• 索引本身也很大，会占用磁盘空间
• 会降低更新表的效率，例如：DML操作时，MySQL不仅要保存数据还要还要更新对应的索引文件。

索引类型

1. 主键索引 ： 唯一、NOT NULL
2. 唯一索引 ：唯一 、NULL
3. 普通索引 ： 可重复、NULL
4. 全文索引
5. 前缀索引
6. 其他（按索引列数量分类）
   • 单列索引
   • 组合索引：要遵循最左前缀匹配的原则

2 索引的数据结构

二叉搜索树—>平衡二叉树—>B树

（1）B树的结构

演进为B树的原因是什么？（逻辑层序递进）

• MySQL的数据是存储在磁盘上的，查询时，需要先把磁盘中的数据先加载到内存中;
• 磁盘IO操作非常耗时==（优化重点就是减少磁盘iO操作）==；
• 如过是在二叉树上查数据，可能需要访问多个节点发生多次IO（二叉树一个节点只有两条路可走）
• 如果需要减少磁盘的IO操作，就需要降低树的高度！

二叉树节点大小：假如索引key占8子节，两个指针各占4子节，则一个二叉树节点占用空间为16子节

一次IO的读取大小：InnoDB一次IO读取一页(默认16KB)

**【缺点显现】：**在二叉树上访问一个节点执行的一次IO有效读取数据才是16子节…这空间利用率太低了！

为了最大化利用一次IO空间，我们将二叉平衡搜索树

优化为B树

• 在每个节点存储多个元素（例如：每个节点可以存储1000个索引：16KB / 16子节 = 1000）；
• 这样二叉树就变成了多叉树；
• 因此整棵树变矮了！从高瘦树—>矮胖树
• 例如：构建100万条数据，树高仅需要2层即可（1000 * 1000），也就是只需要2次磁盘IO就能查询到数据
• 磁盘IO变少了，查询数据的效率也就提高了。

B树缺点

1. 不支持范围查找，需要从根节点多次遍历（数据放在每个节点中）
2. 如果data存储的是行数据，占用空间较大。此时，一个页中可存储的数据量就会变少，树就会变高，磁盘IO的次数又变大了。

（2）B 树的结构

改造B树：B 树和B树最大的区别在于非叶子节点是否存储数据

• B树：非叶子节点和叶子节点都会存储数据；
• B 树：仅叶子节点才会存储数据，非叶子节点只存储键（索引）。叶子节点之间使用双向指针连接。

• B 树的最底层叶子节点包含了所有的索引 and 数据；
• 非叶子节点值仅存放索引，使其能放更多的索引 —> 树高更矮

InnoDB的Data存储的是行数据；Mylsam中的Data存储的是磁盘地址。

eg：等值查询

eg：范围查询

可见B 树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B 树的数据结构。

3 InnoDB索引

（1）主键索引（聚簇索引）

• 每个InnoDB表都有一个聚簇索引（使用B 树构建）
• 叶子节点存储的数据是行记录
• 当一个表没有定义主键时，InnoDB会选择一个不为Null的唯一索引作为主键索引；或什么都没有时，InnoDB使用一个6字节的长整型隐式字段构建聚簇索引（自增）
• 在检索时，InnoDB使用指定的主键在聚簇索引中搜索行记录

以如下案例理解：

/*user_innodb表，user_innodb的id列为主键，age列为普通索引*/
CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

• InnoDB的数据和索引存储在文件：**t_user_innodb==.ibd==**中；
• 主键索引的叶子节点会存储行数据；辅助索引只会存放主键值

主键索引：

eg：等值查询

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次

（2）辅助索引

• 除了聚簇索引之外的所有索引都是辅助索引；
• InnoDB的辅助索引的叶子节点只会存储主键值

继续以上面的案例为例：id列是主键索引、age列是辅助索引

• 底层叶子节点按照（age：id）的顺序排序：先按age从小到大，age相同则按id从小大小排；
• 使用辅助索引需要检索2遍索引：首先是检索辅助索引获得主键，再使用主键到聚簇索引中检索获得行记录！

来看案例分析：

执行sql

SELECT * FROM t_user_innodb WHERE age = 19;

通过在辅助索引的B 树上检索到主键id，再到聚簇索引B 树上检索行数据，此过程叫做**回表查询**。

磁盘IO数：辅助索引3次 主键索引3次

如果不用辅助索引通过age查询，就需要做全表查询了，数据一多磁盘IO数会很大。

（3）组合索引

强调最左匹配原则：（最左前缀匹配原则 <----息息相关---->组合索引的索引存储结构和检索方式）

• 存储结构：

  1. 最底层叶子节点先按第一列a从左到右排序，b、c列无序
  2. 如果a列值相同：按b递增有序
  3. 如果a和b列值都相同：按c递增有序

• 检索方式：

  1. 先比较a列来确定下一步应该搜索的方向，往左还是往右；
  2. 如果a列相同，再比较b列…

最左前缀匹配原则

• 使用组合索引查询时，MySQL会一直向右匹配直到遇到范围查询（>,<,between,like)就停止；
• =和in可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
• 如果建立的索引顺序是（a，b），那么直接采用where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性；

案例理解：创建一个表abc_innodb，id为主键索引，创建一个联合索引idx_abc（a,b,c)

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

1、插入数据后执行sql查看：

SELECT * FROM abc_innodb order by a, b, c, id;

在组合索引树中，最底层的叶子节点按如下规律存储**（存储结构如下图）**

• 第一列a从左到右递增排序，但b和c列无序
• b列仅在a列值相同的情况下小范围内递增有序
• c列仅在a、b列都相等的情况下小氛围内递增有序

因此在此组合索引自动排序的结构基础上通过Order by去select会减少很多时间！如上图检索结果

组合索引的数据结构：

2、组合索引的查询过程（检索方式如下图)

执行sql：

SELECT * FROM abc_innodb WHERE a=13 and b=16 and c=4;

再次强调：组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

（4）覆盖索引（联合索引最容易触发）

• 并不是索引结构

• 是一种优化手段

  因为在辅助索引的时候我们检索只能拿到主键值，获取行数据还需根据主键查询主键索引才获得数据。

  如果在上面组合查询的案例中，通过（a，b，c）索引查询时，如果我们只需要查询出abc字段，那么就意味着我们在辅助索引查询到的叶子节点就是我们最终的查询结果！不需要回表了，这种情况就是覆盖索引。

执行计划如下：

覆盖索引的情况：

未使用到覆盖索引的情况：

看到这里就感觉可以对自己的sql语句里面的索引进行优化了，比如：

避免回表：

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）

如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用==联合索引（name，sex）==这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

联合索引的使用：

1. 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
2. 当前索引查询结果存在频繁作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

这篇好文章是转载于：学新通技术网