MySQL索引原理及优化

图片版：http://assets.processon.com/chart_image/6142b91d1e0853078fd9e7d8.png

觉得有用的麻烦点个赞

版本：5.7

InnoDB

索引方式B+Tree

基础环境

1. 客户端发送一条查询给服务器；

2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5. 将结果返回给客户端。

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况查询会进入下一个阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

query_cache_limit

分配内存块时的最小单位大小

query_cache_min_res_unit

query_cache_size

是否打开缓存 OFF: 关闭 ON: 总是打开

query_cache_type

query_cache_wlock_invalidate

缓存配置参数

查询缓存

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确的匹配。

预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里讲检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

下一步预处理器会验证权限，这通常很快，除非服务器上有非常多的权限设置。

语法解析器和预处理器

现在语法树被认为合法的了，并且由优化器将其转化为执行计划。一条查询可以由很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。

1. 统计信息不准确。

2. 执行计划中的成本估算不等同于实际的执行计划的成本。

3. MySQL的最优可能与你想的最优不一样。

4. MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度。

5. MySQL也不是任何时候都是基于成本的优化，有时候也会基于一些固定的规则。

6. MySQL不会考虑不受其控制的成本，例如执行存储过程或者用户自定义的函数的成本。

有很多种原因会导致MySQL优化器选择错误的执行计划，比如：

MySQL的查询优化使用了很多优化策略来生成一个最优的执行的计划。优化策略可以分为两种，静态优化和动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过一些简单的代数变换将where条件转换成另一种等价形式。静态优化不依赖于特别的数值，如where条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复查询也不会变化，可以认为是一种“编译时优化”。

相反，动态优化则和查询的上下文有关。也可能和很多其他因素有关，例如where条件中的取值、索引中条目对应的数据行数等，这些需要每次查询的时候重新评估，可以认为是“运行时优化”。

数据表的关联并不总是按照在查询中指定的顺序进行，决定关联的顺序是优化器很重要的一部分功能。

1. 重新定义关联表的顺序

并不是所有的outer join语句都必须以外连接的方式执行。诸多因素，例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询，让其可以调整关联顺序。

2. 将外连接转化成内连接

MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如：（5=5 and a>5）将被改写为a>5。类似的，如果有（a<b and b=c）and a=5，则会被改写为 b>5 and b=c and a=5。

3. 使用等价变换规则

索引和列是否为空通常可以帮助MySQL优化这类表达式。例如，要找到一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会讲这个表达式最为一个常数对待。类似的，如果要查找一个最大值，也只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化，那么在explain中就可以看到“select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。

类似的，没有任何where条件的count(*)查询通常也可以使用存储引擎提供的一些优化，例如，MyISAM维护了一个变量来存放数据表的行数。

4. 优化count()、min()和max()

5. 预估并转化为常数表达式

当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。

6. 覆盖索引扫描

MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。

7. 子查询优化

在发现已经满足查询需求的时候，MySQL总是能够立即终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外，MySQL还有几种情况也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立即返回一个空结果。

8. 提前终止查询

9. 等值传播

在很多数据库系统中，in()完全等同于多个or条件的字句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个o(log n)复杂度的操作，等价转换成or的查询的复杂度为o(n)，对于in()列表中有大量取值的时候，MySQL的处理速度会更快。

10. 列表in()的比较

下面是一些MySQL能够处理的优化类型：

查询优化器

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口就是我们称为“handler API”的接口。实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等。

查询执行引擎

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，MySQL仍然会返回这个查询的一些信息，如查询影响到的行数。

如果查询可以被缓存，那么MySQL在这个阶段，会将结果存放到查询缓存中。

MySQL将结果返回客户端是一个增量、逐步返回的过程。例如，在关联表操作时，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

这样处理有两个好处：服务器无需存储太多的结果，也就不会因为要返回太多的结果而消耗太多的内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输过程中，可能对MySQL的封包进行缓存然后批量传输。

返回结果给客户端

查询过程

参考（很详细）：https://www.cnblogs.com/gered/p/13803642.html

结构图

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。

show variables like 'innodb_file_per_table';

独立的表空间可以在不同的数据库之间进行迁移。可通过命令查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。

InnoDB把数据保存在表空间内，表空间可以看作是InnoDB存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。

InnoDB用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等。

表空间（table space）

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。

段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。

当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建表段，创建一个索引时会创建索引段。

默认情况下，InnoDB 会给每个索引分配两个段（Segment）。一个用于存储索引中的非叶子结点，另一个用于存储叶子结点。

段（segment）

在 InnoDB 存储引擎中，一个区会分配 64 个连续的页。

因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。

默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页。

区（extent）

页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本碍事，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。

数据页（B-tree Node)

undo页（undo Log Page）

系统页 （System Page）

事物数据页 （Transaction System Page）

插入缓冲位图页（Insert Buffer Bitmap）

插入缓冲空闲列表页（Insert Buffer Free List）

未压缩的二进制大对象页（Uncompressed BLOB Page）

压缩的二进制大对象页 （compressed BLOB Page）

innoDB存储引擎中，常见的页类型有：

页（Page）

InnoDB存储引擎是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。

行（row）

表、段、区、页、行

注意箭头

Innodb引擎中的Page完整结构图

Page结构示意图1

Page结构示意图2

为了方便理解，可以按下面的分解一下Page的结构

每部分的意义

在文件头中有两个字段，分别是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT，它们的作用相当于指针，分别指向上一个数据页和下一个数据页。连接起来的页相当于一个双向的链表，如下图所示：

需要说明的是采用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续。

第一部分通用部分，主要指文件头和文件尾，将页的内容进行封装，通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的。

另外空闲空间是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录，如下图所示：

一个页内必须存储2行记录，否则就不是B+tree，而是链表了。

第二个部分是记录部分，页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。

因为在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索，因此在页目录中提供了二分查找的方式，用来提高记录的检索效率。

这个过程就好比是给记录创建了一个目录：

将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。第 1 组，也就是最小记录所在的分组只有 1 个记录；最后一组，就是最大记录所在的分组，会有 1-8 条记录；其余的组记录数量在 4-8 条之间。这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分。在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。

https://blog.haohtml.com/wp-content/uploads/2019/08/innodb-page-dir-1024x959.jpg

页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。如下图所示：

页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找。

第三部分是索引部分，这部分重点指的是页目录(示意图2中的s0-sn)，它起到了记录的索引作用

页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。

page的整体结构

物理存储

如果通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

B+ 树是如何进行记录检索的？

子主题

索引

聚簇索引

非聚簇索引

B+树

数据结构

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此获取数据比非聚簇索引更快

聚簇索引对于主键的排序查找和范围查找速度非常快

优点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。（所以不推荐UUID做主键）

span style=\

回表

辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

一张表最多可以创建 64 个非聚簇索引，创建非聚簇索引的列不能超过16个！

索引方式

原理

定义：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

执行顺序从上至下

id相同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

id不相同

id如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id值越大，优先级越高，越先执行

id相同又不同

3种情况

id

简单查询，不包含子查询或Union查询

SIMPLE

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询

PRIMARY

在select或where中包含子查询

SUBQUERY

在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

DERIVED

若第二个select出现在union之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外层select将被标记为DERIVED

UNION

从UNION表获取结果的select

UNION RESULT

select_type（查询类型）

数据来自哪张表

table

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL(最好到最差)

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

NULL

表只有一行记录（等于系统表），这是const类型的特例，平时不大会出现，可以忽略

system

表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量

const

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

eq_ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体

ref

类似ref，但是可以搜索值为NULL的行

ref_or_null

表示使用了索引合并的优化方法

index_merge

只检索指定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。

一般就是在你的where语句中出现between、<>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为只需要开始于索引的某一点，而结束另一点，不用扫描全部索引

range

Full Index Scan，Index与All区别：index只遍历索引树，通常比ALL快

因为索引文件通常比数据文件小（也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的）

index

Full Table Scan，将遍历全表找到匹配行

ALL

常见类型（10种）

type（访问类型）

显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

possible_keys

实际使用到的索引，如果为NULL，则没有使用索引

查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

key

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

在不损失精确度的情况下，长度越短越好

key_len显示的值为索引字段最大的可能长度，并非实际使用长度

即key_len是根据定义计算而得，不是通过表内检索出的

key_len

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

rows

匹配的分区

partitions

查询的表行占表的百分比

filtered

定义：包含不适合在其他列中显示但十分重要的额外信息

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

MySQL中无法利用索引完成的排序操作称为“文件排序”

Using filesort

使用了临时表保存中间结果，MySQL在对结果排序时使用了临时表，常见于排序order by和分组查询group by

Using temporary

覆盖索引：select的数据列只用于从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，即查询列要被所建的索引覆盖

表示相应的select操作中使用了覆盖索引（Coving Index），避免访问了 表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

Using index

使用了where条件

Using where

使用了连接缓存

Using join buffer

where子句的值总是false，不能用来获取任何元组

impossible where

找到了与行相联合匹配的行，就不再搜索了

distinct

SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）

Select tables optimized away

8种

extra

explain

索引列参与计算/使用了函数

索引列使用了Like \"%XXX\"

数据库存在name=“0123”

查询where name=123

可以查询到数据

隐式转换：字符串列与数字直接比较

除非每个列都建立了索引，否则不建议使用OR，在多列OR中，可以考虑用UNION 替换

尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引

全表扫描只需要读取所有页

如果走索引，先查询所有索引页，再查询数据页，然后走聚簇索引再查询所有页，其实数据几乎还是存在于所有页的

这种情况不应建立索引

比如查询性别为男的数据

查询出来的数据量太大，不如全表扫描快的时候

但是如果查询出来数据不多，还是会走索引的

is null/is not null/in/not in

MySQL认为全表扫描比使用索引更kuai的一些情况

mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。

不走索引的情况

1.从数据表中读取第N条数据添加到数据集中2.重复第一步直到 N = 10000 + 103.根据 offset 抛弃前面 10000 条数4.返回剩余的 10 条数据

存在删除的行

分布式ID

几乎不现实

select * from table_name where (id >= 10000) limit 10

第一次优化（利用自增主键）

相比较结果是（500w条数据）：耗时约为第一条的 1/3 左右。

如果in的数据量比较大，也很耗时

第二次优化（利用索引）

第三次优化（利用join）

Limit 操作

可以最大化利用聚簇索引

一些大字段可以抽离为子表

字段不要太多

可以一页存更多数据

索引长度也有限制

预先定义字段长度，尤其是varchar

想到的一些表设计原则

一些其他优化

MySQL索引原理及优化