高性能MySQL

以下讲解如未特殊说明则均基于 InnoDB 存储引擎

什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

能实现快速定位数据的一种存储结构，其设计思想是以空间换时间。

常见索引数据结构和区别

• 二叉树、红黑树、B树 、B+树
• 区别：树的高度影响获取数据的性能（每一个树节点都是一次磁盘I/O)

二叉树

特点：每个节点最多有两个子节，大在右，小在左，数据随机性情况下树杈越明显。

如果数据是按顺序依次进入，树的高度则会很高（极端的情况下就是一个链表结构），此时元素的查找效率就等于链表查询O(n)，数据检索效率将极为低下。

红黑树（平衡二叉树）

虽通过自旋平衡，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更佳。但是如果数据量过大，节点个数就越多，树高度也会增高（也就是树的深度越深），会增加磁盘I/O次数，影响查询效率。

B树

B树的出现可以解决树高度的问题。之所以是B树，而并不是名称中”xxx二叉树”，就是它不再限制一个父节点中只能有两个子节点，而是允许 M 个子节点（M > 2）。不仅如此，B树的一个节点可以存储多个元素，相比较于前面的那些二叉树数据结构又将整体的树高度降低了。

B树的节点可以包含有多个子节点，所以B树是一棵多叉树，它的每一个节点包含的最多子节点数量的称为B树的阶。

查找流程

对比解析

先从根节点出发，判断7在4和8之间，根据P2存储指针6的节点，判断7大于6，最后指针找到叶子节点，也就找到有匹配7的键值

上图是一颗3阶的B树查找“7”这个的元素时的流程。可以发现一颗3阶的B树在查找叶子节点时，由于树高度只有3，所以查找过程最多只需要3次的磁盘I/O操作。

数据量不大时可能不太真切。但当数据量大时，节点也会随着增多；此时如果还是前面的自平衡二叉树的场景下，由于二叉树只能最多2个叶子节点的约束，也只能纵向的去扩展子节点，树的高度会很高，意味着需要更多的操作磁盘I/O次数。而B树则可以通过横向扩展节点从而降低树的高度，所以效率自然要比二叉树效率更高。（直白说就是变矮胖了）

看到这，相信你也知道如果B树这么适合，也就没有接下来B+树的什么事了。

的确，B树其实已经满足了我们最前面所要满足的条件，减少磁盘I/O操作，同时支持按区间查找。但注意，虽然B树支持按区间查找，但并不高效。

举个例子，B树能高效的通过等值查询 15 这个值，但不方便查询出一个区间内 3~10 所有数的结果。因为当B树做范围查询时需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换，涉及了多个节点会给磁盘I/O带来很多负担。

B+tree索引

B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的。结构图如下：

Mysql为什么要选择B+树作为默认索引的数据结构

B+tree 结构实现数据索引具有如下优点：

• 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。
• 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
• 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

索引的分类

在 MySQL 中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。常见的索引分类如下：

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引
• 按「物理存储」分类：聚集索引（聚簇索引）、非聚集索引（非聚簇索引/二级索引/辅助索引）
• 按「字段特性」分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)
• 按「字段个数」分类：单列索引、联合索引（也叫复合索引/组合索引）

Hash索引

哈希索引（hash index）基于哈希表实现。哈希索引通过 Hash 算法将数据库的索引列数据转换成定长的哈希码作为 key，将这条数据的行的地址作为 value 一并存入 Hash 表的对应位置。

在 MySQL 中，只有 Memeory 引擎显式的支持哈希索引，这也是 Memory 引擎表的默认索引结构，Memeory 同时也支持 B-Tree 索引。并且，Memory 引擎支持非唯一哈希索引，如果多个列的哈希值相同（或者发生了 Hash 碰撞），索引会在对应 Hash 键下以链表形式存储多个记录地址。

哈希索引的特点：

• 哈希索引不支持部分索引列的匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
• 哈希索引具有哈希表的特性，因此只有精确匹配所有列的查询对于哈希索引才有效，比如 =、<>、IN (因为数据的存储是无序的)，且无法使用任何范围查询。
• 因为数据的存储是无序的，哈希索引还无法用于排序。
• 对于精确查询，则哈希索引效率很高，时间复杂度为O(1)，除非有很多哈希冲突（不同的索引列有相同的哈希值），如果发生哈希冲突，则存储引擎必须遍历链表中的所有数据指针，逐行比较，直到找到所有符合条件的行。哈希冲突越多，代价就越大！

索引覆盖

索引覆盖（Index Covering）是指通过在索引中包含查询语句中所需的列，可以避免对表中的数据进行额外的访问，从而提高查询效率。(避免了回表操作)

1

SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

索引覆盖能够显著提高查询效率，因此在建立索引时应尽量考虑包含查询语句中所需的所有列。

索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

索引下推的使用条件

• ICP 目标是减少全行记录读取，从而减少 IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说
• 只能用于 range、 ref、 eq_ref、ref_or_null 访问方法
• where 条件中是用 and 而非 or 的时候
• ICP 适用于分区表
• ICP 不支持基于虚拟列上建立的索引，比如说函数索引
• ICP 不支持引用子查询作为条件
• ICP 不支持存储函数作为条件，因为存储引擎无法调用存储函数

1
2
3
4
5
6

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

索引合并

结果合并分为下面三种情况：

• 取交集（intersect）
• 取并集（union）
• 排序后取并集（sort-union）

举一个例子，在表中为 name 和 age 各自分别创建一个二级索引 idx_name 和 idx_age

取交集

查看下面 sql 执行计划

1

explain select * from `user` where name = '赵六' and age= 22;

type 是 index_merge，并且 possible_key 和 key 都是 idx_name 和 idx_age，说明使用了索引合并，并且 Extra 有 Using intersect(idx_age,idx_name)，intersect 就是交集的意思。

整个过程大致是这样的，分别根据 idx_name 和 idx_age 取出对应的主键id，之后将主键id取交集，那么这部分交集的id一定同时满足查询 name = ‘赵六’ and age= 22 的查询条件，之后再根据交集的id回表

不过要想使用取交集的联合索引，需要满足各自索引查出来的主键id是排好序的，这是为了方便可以快速的取交集

比如下面这条 sql 就无法使用联合索引

1

explain select * from `user` where name = '赵六' and age > 22;

type 是 ref，key 是 idx_name

只能用 name 这个索引，因为 age > 22 查出来的id是无序的

取并集

取并集就是将前面例子中的 and 换成 or（所以用 or 不一定会导致索引失效）

1

select * from `user` where name = '赵六' or age = 22;

前面执行的情况都一样，根据条件到各自的索引上去查，之后对查询的id取并集去重，之后再回表

同样地，取并集也要求各自索引查出来的主键id是排好序的，如果查询条件换成 age > 22 时就无法使用取并集的索引合并

排序后取并集

虽然取并集要求各自索引查出来的主键id是排好序的，但是如果遇到没排好序的情况，mysql 会自动对这种情况进行优化，会先对主键id排序，然后再取并集，这种情况就叫 排序后取并集（sort-union）。

比如上面提到的无法直接取并集的 sql 就符合排序后取并集（sort-union）这种情况

1

select * from `user` where name = '赵六' or age > 22;

会导致索引失效的一些情况

• 使用 like 并且是左边带 %， 右边可以会走索引（但是并不绝对）
• 隐式类型转换，索引字段与条件或关联字段的类型不一致。（比如字段是 int，用字符串方式去查询会导致索引失效）
• 在 where 条件里面对索引列计算或者使用函数。
• 使用 OR 且存在非索引列
• 在 where 条件中两列做比较会导致索引失效
• 使用 IN 可能不会走索引（MySQL 环境变量 eq_range_index_dive_limit 的值对 IN 语法有很大影响，该参数表示使用索引情况下 IN 中参数的最大数量。MySQL 5.7.3 以及之前的版本中，eq_range_index_dive_limit 的默认值为 10，之后的版本默认值为 200。我们拿 MySQL8.0.19 举例，eq_range_index_dive_limit=200 表示当 IN (...) 中的值 >200 个时，该查询一定不会走索引。<=200 则可能用到索引。）
• 使用非主键范围条件查询时，部分情况索引失效。
• 使用 order by 可能会导致索引失效
• is null is not null ≠ 可能会导致索引失效

通过索引排序的内部流程

首先 mysql 会为每一个线程都分配一个固定大小的 sort buffer 用于排序。它是一个具有逻辑概念的内存区域，我们可以通过 sort_buffer_size 参数来控制，默认值是 256kb。

1
2

// 输入查看最，小可以设置为 32K，最大可以设置为 4G。
show variables like 'sort_buffer_size';

由于 sort buffer 大小是固定的，但是待排序的数据量不是，所以根据它们之间的一个差值分为了内部排序和外部排序

• 当待排序的数据量小于等于 sort buffer 时，那 sort buffer 就能够容纳，MySQL 就可以直接在内存里面排序，内部排序使用的排序算法是快排
• 当待排序的数据量大于 sort buffer 时，那 sort buffer 就会不够用。这个时候 MySQL 就得要借助外部文件来进行排序了。将待排序数据拆成多个小文件，对各个小文件进行排序，最后再汇总成一个有序的文件，外部排序使用的算法是归并排序

row_id排序

在 MySQL 中专门控制用户排序的行数据长度参数 max_length_for_sort_data，默认是 4096，也就是说如果超过了这个长度 MySQL 就会自动升级成 row_id 算法。

1
2

// 默认max_length_for_sort_data的大小为4096字节
show variables like 'max_length_for_sort_data';

row_id 排序的思想就是把不需要的数据不放到 sort_buffer 中，让 sort_buffer 中只存放需要排序的字段。

我们前面说到了 sort buffer，在 sort buffer 里面进行排序的数据是我们 select 的全部字段，所以当我们查询的字段越多，那么 sort buffer 能容纳的数据量也就越小。而通过 row_id 排序就只会存放 row_id 字段和排序相关的字段。其余的字段等排序完成之后通过主键ID进行回表拿。

InnoDB和MyISAM的区别

InnoDB 和 MyISAM 都是使用的B+树实现的，但是 InnoDB 使用的是聚簇索引而 MyISAM 使用的是非聚簇索引，聚簇索引根据主键创建一颗B+树，叶子节点则存放的是数据行记录，也可以把叶子结点称为数据页。通俗点来说就是把数据和索引存在同一个块，找到了索引也就找到了数据。

• 因为叶子结点将索引和数据放在一起，就决定了聚簇索引的唯一性，一张表里面只能有一个聚簇索引。
• InnoDB 引擎默认将主键设置为聚簇索引，但如果没有设置主键，那么 InnoDB 将会选择非空的唯一索引作为代替，如果没有这样的索引，InnoDB 将会定一个隐式主键（隐藏行）作为聚簇索引。
• 因为聚簇索引特殊的物理结构所决定，叶子结点将索引和数据存放在一起，在获取数据的速度上是比非聚簇索引快的。
• 聚簇索引数据的存储是有序的，在进行排序查找和范围查找的速度也是非常快的。
• 也正因为有序性，在数据插入时按照主键的顺序插入是最快的，否则就会出现页分裂等问题，严重影响性能。对于 InnoDB 我们一般采用自增作为主键ID。
• 主键最好不要进行更新，修改主键的代价非常大，为了保持有序性会导致更新的行移动，通常设置为主键不可更新。
• 聚簇索引的缺点是，对数据进行修改或删除操作时需要更新索引树，会增加系统的开销。

而非聚簇索引是将索引和数据分开存储，那么在访问数据的时候就需要至少2次查找。InnoDB 是先查找辅助索引树，再查找聚簇索引树（这个过程也叫回表）。而 MyISAM 的主键索引叶子结点的存储的部分还是有所区别。InnoDB 中存储的是索引和主键ID，但是 MyISAM 中存储的是索引和数据行的地址，只要定位就可以获取到。

在物理存储上：

• InnoDB 的数据文件分为 .frm 和 .idb 两种，分别存储的是 表结构 和 表索引+数据
• MyISAM 的数据文件分为 .frm 和 .MYD、MYI 三种，分别对应 表结构 、表数据、表索引

总结：

1、InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，否则其他索引也会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败。

3、InnoDB 在 MySQL 5.6 之前不支持全文索引，而 MyISAM 一直都支持，如果用的是老版本，查询效率上 MyISAM 要高。

4、InnoDB 锁粒度是行锁，而 MyISAM 是表锁。

5、InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务。

6、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快，但如果上述语句还包含了 where 子句，那么两者执行效率是一样的。