MySQL索引原理及优化
2021-09-28 19:54:47 0 举报
AI智能生成
从存储结构到索引原理到根据索引想到的一些优化点,觉得有用的麻烦点个赞
作者其他创作
大纲/内容
觉得有用的麻烦点个赞
图片版: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
MySQL能够缓存的最大结果,如果超出,则增加 Qcache_not_cached的值,并删除查询结果
query_cache_min_res_unit
分配内存块时的最小单位大小
query_cache_size
缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)
query_cache_type
是否打开缓存 OFF: 关闭 ON: 总是打开
query_cache_wlock_invalidate
如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回
语法解析器和预处理器
首先,MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如,它将验证是否使用错误的关键字,或者使用关键字的顺序是否正确等,再或者它还会验证引号是否能前后正确的匹配。
预处理器则根据一些MySQL规则进一步检查解析树是否合法,例如,这里讲检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义。
下一步预处理器会验证权限,这通常很快,除非服务器上有非常多的权限设置。
查询优化器
现在语法树被认为合法的了,并且由优化器将其转化为执行计划。一条查询可以由很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划的成本,并选择其中成本最小的一个。最初,成本的最小单位是随机读取一个4K数据页的成本,后来成本计算公式变得更加复杂,并且引入了一些“因子”来估算某些操作的代价,如当执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。
有很多种原因会导致MySQL优化器选择错误的执行计划,比如:
1. 统计信息不准确。
2. 执行计划中的成本估算不等同于实际的执行计划的成本。
3. MySQL的最优可能与你想的最优不一样。
4. MySQL从不考虑其他并发的查询,这可能会影响当前查询的速度。
5. MySQL也不是任何时候都是基于成本的优化,有时候也会基于一些固定的规则。
6. MySQL不会考虑不受其控制的成本,例如执行存储过程或者用户自定义的函数的成本。
MySQL的查询优化使用了很多优化策略来生成一个最优的执行的计划。优化策略可以分为两种,静态优化和动态优化。静态优化可以直接对解析树进行分析,并完成优化。例如优化器可以通过一些简单的代数变换将where条件转换成另一种等价形式。静态优化不依赖于特别的数值,如where条件中带入的一些常数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复查询也不会变化,可以认为是一种“编译时优化”。
相反,动态优化则和查询的上下文有关。也可能和很多其他因素有关,例如where条件中的取值、索引中条目对应的数据行数等,这些需要每次查询的时候重新评估,可以认为是“运行时优化”。
下面是一些MySQL能够处理的优化类型:
1. 重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行,决定关联的顺序是优化器很重要的一部分功能。
2. 将外连接转化成内连接
并不是所有的outer join语句都必须以外连接的方式执行。诸多因素,例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询,让其可以调整关联顺序。
3. 使用等价变换规则
MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。例如:(5=5 and a>5)将被改写为a>5。类似的,如果有(a<b and b=c)and a=5,则会被改写为 b>5 and b=c and a=5。
4. 优化count()、min()和max()
索引和列是否为空通常可以帮助MySQL优化这类表达式。例如,要找到一列的最小值,只需要查询对应B-tree索引最左端的记录,MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-tree索引中,优化器会讲这个表达式最为一个常数对待。类似的,如果要查找一个最大值,也只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化,那么在explain中就可以看到“select tables optimized away”。从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。
类似的,没有任何where条件的count(*)查询通常也可以使用存储引擎提供的一些优化,例如,MyISAM维护了一个变量来存放数据表的行数。
5. 预估并转化为常数表达式
6. 覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无需查询对应的数据行。
7. 子查询优化
MySQL在某些情况下可以将子查询转换成一种效率更高的形式,从而减少多个查询多次对数据进行访问。
8. 提前终止查询
在发现已经满足查询需求的时候,MySQL总是能够立即终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外,MySQL还有几种情况也会提前终止查询,例如发现了一个不成立的条件,这时MySQL可以立即返回一个空结果。
9. 等值传播
10. 列表in()的比较
在很多数据库系统中,in()完全等同于多个or条件的字句,因为这两者是完全等价的。在MySQL中这点是不成立的,MySQL将in()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个o(log n)复杂度的操作,等价转换成or的查询的复杂度为o(n),对于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
结构图
结构图
表、段、区、页、行
表空间(table space)
表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
在 InnoDB 中存在两种表空间的类型:共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间,就意味着每张表有一个独立的表空间,也就是数据和索引信息都会保存在自己的表空间中。
独立的表空间可以在不同的数据库之间进行迁移。可通过命令查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。
show variables like 'innodb_file_per_table';
InnoDB把数据保存在表空间内,表空间可以看作是InnoDB存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。
InnoDB用表空间并不只是存储表和索引,还保存了回滚段、双写缓冲区等。
段(segment)
段(Segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在 InnoDB 中是连续的 64 个页),不过在段中不要求区与区之间是相邻的。
段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在。
当我们创建数据表、索引的时候,就会相应创建对应的段,比如创建一张表时会创建表段,创建一个索引时会创建索引段。
默认情况下,InnoDB 会给每个索引分配两个段(Segment)。一个用于存储索引中的非叶子结点,另一个用于存储叶子结点。
区(extent)
在 InnoDB 存储引擎中,一个区会分配 64 个连续的页。
因为 InnoDB 中的页大小默认是 16KB,所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为1MB,为了保证页的连续性,InnoDB存储引擎每次从磁盘一次申请4-5个区。
默认情况下,InnoDB存储引擎的页大小为16KB,即一个区中有64个连续的页。
页(Page)
页是InnoDB存储引擎磁盘管理的最小单位,每个页默认16KB;InnoDB存储引擎从1.2.x版本碍事,可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成,则所有表中页的大小都为innodb_page_size,不可以再次对其进行修改,除非通过mysqldump导入和导出操作来产生新的库。
innoDB存储引擎中,常见的页类型有:
数据页(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)
行(row)
InnoDB存储引擎是按行进行存放的,每个页存放的行记录也是有硬性定义的,最多允许存放16KB/2-200,即7992行记录。
page的整体结构
Innodb引擎中的Page完整结构图
注意箭头
为了方便理解,可以按下面的分解一下Page的结构
Page结构示意图1
Page结构示意图2
每部分的意义
页结构整体上可以分为三大部分,分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。
第一部分通用部分,主要指文件头和文件尾,将页的内容进行封装,通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的。
第一部分通用部分,主要指文件头和文件尾,将页的内容进行封装,通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的。
在文件头中有两个字段,分别是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT,它们的作用相当于指针,分别指向上一个数据页和下一个数据页。连接起来的页相当于一个双向的链表,如下图所示:
需要说明的是采用链表的结构让数据页之间不需要是物理上的连续,而是逻辑上的连续。
第二个部分是记录部分,页的主要作用是存储记录,所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。
另外空闲空间是个灵活的部分,当有新的记录插入时,会从空闲空间中进行分配用于存储新记录,如下图所示:
一个页内必须存储2行记录,否则就不是B+tree,而是链表了。
第三部分是索引部分,这部分重点指的是页目录(示意图2中的s0-sn),它起到了记录的索引作用
因为在页中,记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便,但是检索效率不高,最差的情况下需要遍历链表上的所有节点才能完成检索,因此在页目录中提供了二分查找的方式,用来提高记录的检索效率。
这个过程就好比是给记录创建了一个目录:
将所有的记录分成几个组,这些记录包括最小记录和最大记录,但不包括标记为“已删除”的记录。
第 1 组,也就是最小记录所在的分组只有 1 个记录;
最后一组,就是最大记录所在的分组,会有 1-8 条记录;
其余的组记录数量在 4-8 条之间。
这样做的好处是,除了第 1 组(最小记录所在组)以外,其余组的记录数会尽量平分。
在每个组中最后一条记录的头信息中会存储该组一共有多少条记录,作为 n_owned 字段。
第 1 组,也就是最小记录所在的分组只有 1 个记录;
最后一组,就是最大记录所在的分组,会有 1-8 条记录;
其余的组记录数量在 4-8 条之间。
这样做的好处是,除了第 1 组(最小记录所在组)以外,其余组的记录数会尽量平分。
在每个组中最后一条记录的头信息中会存储该组一共有多少条记录,作为 n_owned 字段。
页目录用来存储每组最后一条记录的地址偏移量,这些地址偏移量会按照先后顺序存储起来,每组的地址偏移量也被称之为槽(slot),每个槽相当于指针指向了不同组的最后一个记录。如下图所示:
https://blog.haohtml.com/wp-content/uploads/2019/08/innodb-page-dir-1024x959.jpg
页目录存储的是槽,槽相当于分组记录的索引。我们通过槽查找记录,实际上就是在做二分查找。
这里我以上面的图示进行举例,5 个槽的编号分别为 0,1,2,3,4,我想查找主键为 9 的用户记录,我们初始化查找的槽的下限编号,设置为 low=0,然后设置查找的槽的上限编号 high=4,然后采用二分查找法进行查找。
首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2,这时我们取编号为 2 的槽对应的分组记录中最大的记录,取出关键字为 8。因为 9 大于 8,所以应该会在槽编号为 (p,high] 的范围进行查找
接着重新计算中间位置 p’=(p+high)/2=(2+4)/2=3,我们查找编号为 3 的槽对应的分组记录中最大的记录,取出关键字为 12。因为 9 小于 12,所以应该在槽 3 中进行查找。
遍历槽 3 中的所有记录,找到关键字为 9 的记录,取出该条记录的信息即为我们想要查找的内容。
首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2,这时我们取编号为 2 的槽对应的分组记录中最大的记录,取出关键字为 8。因为 9 大于 8,所以应该会在槽编号为 (p,high] 的范围进行查找
接着重新计算中间位置 p’=(p+high)/2=(2+4)/2=3,我们查找编号为 3 的槽对应的分组记录中最大的记录,取出关键字为 12。因为 9 小于 12,所以应该在槽 3 中进行查找。
遍历槽 3 中的所有记录,找到关键字为 9 的记录,取出该条记录的信息即为我们想要查找的内容。
数据结构
B+树
B+ 树是如何进行记录检索的?
如果通过 B+ 树的索引查询行记录,首先是从 B+ 树的根开始,逐层检索,直到找到叶子节点,也就是找到对应的数据页为止,将数据页加载到内存中,页目录中的槽(slot)采用二分查找的方式先找到一个粗略的记录分组,然后再在分组中通过链表遍历的方式查找记录。
索引
子主题
聚簇索引
非聚簇索引
索引方式
聚簇索引
聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
优点
数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
缺点
插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键。(所以不推荐UUID做主键)
更新主键的代价很高,因为将会导致被更新的行移动
非聚簇索引
辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
回表
一张表最多可以创建 64 个非聚簇索引,创建非聚簇索引的列不能超过16个!
explain
id
定义:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
3种情况
id相同
执行顺序从上至下
id不相同
如果是子查询,id的序号会递增,id的值越大优先级越高,越先被执行
id相同又不同
id如果相同,可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
select_type(查询类型)
SIMPLE
简单查询,不包含子查询或Union查询
PRIMARY
查询中若包含任何复杂的子部分,最外层查询则被标记为主查询
SUBQUERY
在select或where中包含子查询
DERIVED
在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中
UNION
若第二个select出现在union之后,则被标记为UNION
若UNION包含在FROM子句的子查询中,外层select将被标记为DERIVED
UNION RESULT
从UNION表获取结果的select
table
数据来自哪张表
type(访问类型)
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL(最好到最差)
常见类型(10种)
NULL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引
system
表只有一行记录(等于系统表),这是const类型的特例,平时不大会出现,可以忽略
const
表示通过索引一次就找到了,const用于比较primary key或uique索引,因为只匹配一行数据,所以很快,如主键置于where列表中,MySQL就能将该查询转换为一个常量
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
ref
非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行,应该属于查找和扫描的混合体
ref_or_null
类似ref,但是可以搜索值为NULL的行
index_merge
表示使用了索引合并的优化方法
range
只检索指定范围的行,使用一个索引来选择行,key列显示使用了哪个索引。
一般就是在你的where语句中出现between、<>、in等的查询
这种范围扫描索引扫描比全表扫描要好,因为只需要开始于索引的某一点,而结束另一点,不用扫描全部索引
index
Full Index Scan,Index与All区别:index只遍历索引树,通常比ALL快
因为索引文件通常比数据文件小(也就是虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘读的)
ALL
Full Table Scan,将遍历全表找到匹配行
possible_keys
显示可能应用在这张表中的索引,一个或多个查询涉及到的字段若存在索引,则该索引将被列出,但不一定被实际使用
key
实际使用到的索引,如果为NULL,则没有使用索引
查询中若使用了覆盖索引(查询的列刚好是索引),则该索引仅出现在key列表
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
在不损失精确度的情况下,长度越短越好
key_len显示的值为索引字段最大的可能长度,并非实际使用长度
即key_len是根据定义计算而得,不是通过表内检索出的
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数
partitions
匹配的分区
filtered
查询的表行占表的百分比
extra
定义:包含不适合在其他列中显示但十分重要的额外信息
8种
Using filesort
说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取
MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary
使用了临时表保存中间结果,MySQL在对结果排序时使用了临时表,常见于排序order by和分组查询group by
Using index
表示相应的select操作中使用了覆盖索引(Coving Index),避免访问了 表的数据行,效率不错!
覆盖索引:select的数据列只用于从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,即查询列要被所建的索引覆盖
如果同时出现using where,表明索引被用来执行索引键值的查找
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
Using where
使用了where条件
Using join buffer
使用了连接缓存
impossible where
where子句的值总是false,不能用来获取任何元组
distinct
找到了与行相联合匹配的行,就不再搜索了
Select tables optimized away
SELECT操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了)
不走索引的情况
索引列参与计算/使用了函数
索引列使用了Like "%XXX"
隐式转换:字符串列与数字直接比较
数据库存在name=“0123”
查询where name=123
可以查询到数据
相当于where CONVERT(name,SIGNED) = 123
尽量避免 OR 操作,只要有一个字段没有索引,该语句就不走索引
除非每个列都建立了索引,否则不建议使用OR,在多列OR中,可以考虑用UNION 替换
复合索引a-b-c,a用到,b用不到,c用不到,ab有效,ba有效,a or b无效,ac有效,bc无效,abc有效
MySQL认为全表扫描比使用索引更kuai的一些情况
比如查询性别为男的数据
全表扫描只需要读取所有页
如果走索引,先查询所有索引页,再查询数据页,然后走聚簇索引再查询所有页,其实数据几乎还是存在于所有页的
这种情况不应建立索引
is null/is not null/in/not in
查询出来的数据量太大,不如全表扫描快的时候
但是如果查询出来数据不多,还是会走索引的
mysql一次查询只能使用一个索引。如果要对多个字段使用索引,建立复合索引。
一些其他优化
Limit 操作
select * from table_name limit 10000,10
1.从数据表中读取第N条数据添加到数据集中
2.重复第一步直到 N = 10000 + 10
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 10 条数据
2.重复第一步直到 N = 10000 + 10
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 10 条数据
第一次优化(利用自增主键)
select * from table_name where (id >= 10000) limit 10
几乎不现实
存在删除的行
分布式ID
第二次优化(利用索引)
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
相比较结果是(500w条数据):耗时约为第一条的 1/3 左右。
如果in的数据量比较大,也很耗时
第三次优化(利用join)
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
想到的一些表设计原则
字段不要太多
可以最大化利用聚簇索引
一些大字段可以抽离为子表
预先定义字段长度,尤其是varchar
可以一页存更多数据
索引长度也有限制
收藏
0 条评论
下一页