说明

文章的图片来源《MySQL是怎么运行的：从根儿上理解MySQL》,本篇文章只是个人学习总结，欢迎大家买一本看看，对于mysql是由浅入深的讲解非常细致


目录

  • 说明
  • 8.MySQL 的数据目录
  • 数据库和文件系统的关系
  • Mysql的数据目录
  • 数据目录和安装目录的区别
  • 如何确定mysql的数据目录
  • 数据目录的结构
  • 数据库在文件系统的表示
  • 表在文件系统的表示
  • Innodb如何存储表数据
  • 系统表空间
  • 独立表空间
  • MyISAM是怎么存储数据的
  • 视图在文件系统的表示
  • 其它文件
  • 文件系统对数据库的影响
  • Mysql系统数据库简介
  • 总结
  • 9.InnoDB的表空间
  • 数据页的组成
  • 独立表空间结构
  • 区
  • 段的概念
  • 区的分类
  • XDES Entry链表
  • 链表基节点
  • 链表总结
  • 段的结构
  • 各个类型页面的详细情况
  • FSP_HDR类型
  • File Space Header
  • XDES Entry页类型部分
  • IBUF_BITMAP页类型
  • INODE类型
  • Segment Header的运用
  • 真实表空间大小
  • 系统表空间
  • Innodb数据字典
  • SYS_TABLES
  • SYS_COLUMNS
  • SYS_INDEXS
  • SYS_FIELDS
  • 总结
  • 链表部分总结
  • 页部分总结
  • 最后就是系统表空间部分
  • 10.单表访问的方法
  • 访问方法
  • const
  • ref
  • ref_of_null
  • range
  • index
  • all
  • 明确range访问方法的使用范围区间
  • 所有查询条件都可以使用某个索引
  • 有的查询条件无法使用的情况
  • 复杂搜索条件找出的范围匹配区间
  • 索引合并
  • Intersection合并
  • 情况1：索引的索引列都被用上，并且都是等值匹配。
  • 情况二：主键列可以是范围匹配
  • Union
  • 情况1：就是索引列是等值匹配，而且索引中索引列都出现了
  • 情况2：如果是主键那么就可范围匹配
  • 情况3：使用Intersection索引合并的搜索条件
  • Sort-Union合并
  • 联合索引代替Intersection
  • 总结
  • 联合索引代替Intersection
  • 总结


8.MySQL 的数据目录

数据库和文件系统的关系

  • 操作系统管理磁盘的系统是文件系统，存储引擎需要把表存入文件系统

Mysql的数据目录

  • mysql启动会向文件系统某个目录上加载文件，这个目录就是数据目录

数据目录和安装目录的区别

  • bin这种就是安装目录，各种启动执行命令
  • 数据目录是使用mysql产生的数据存储的目录

如何确定mysql的数据目录

  • show variables like '%datadir%'可以通过这个语句来进行查看数据目录的位置

数据目录的结构

  • 产生的数据是数据库、表、存储过程、触发器和视图

数据库在文件系统的表示

  • 数据库就是数据目录下面的一个子目录，或者说是文件夹
  • 创建数据库的过程
  1. 数据目录下创建子目录
  2. 并且创建一个db.opt文件，包含数据库各种属性

表在文件系统的表示

  • 数据以记录的方式插入表
  1. 表结构定义
  2. 表中的数据
  • 表名.frm描述表的结构

Innodb如何存储表数据

  • 以页为单位管理数据
  • 索引对应着B+树，每个节点就是数据页，数据页不需要连续
  • 聚簇索引的叶子节点管理着所有的数据
  • 为了方便管理创建了表空间和文件空间的概念，表空间的概念其实就是一个表空间对于文件系统上多个文件，而且占据了很多个页。表数据就是存储在表空间的这些页上面
系统表空间
  • 数据目录下面的ibdata1，也就是系统表空间在文件系统上面对应的文件，数据多，那么文件也会进行拓展

[server] innodb_data_file_path=data1:512M;data2:512M:autoextend

  • 上面的意思就是创建两个512M的文件作为系表空间，data2会进行拓展
  • innodb_data_file_path和innodb_data_home_dir可以修改文件的位置。
独立表空间
  • 之后的版本并不会默认把数据存储到系统表空间中，而是存储到独立的表空间，这个表空间在数据库的子目录下
  • 就是每个表都有自己的一个表空间，数据库对应的子目录里面会创建.ibd这样的表数据文件
  • 这个时候就有两个文件，一个是frm结构，另一个就是ibd存储数据和索引
  • innodb_file_per_table=0表示的是把数据放到系统表空间，还是把数据放到独立表空间。
  • ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;把数据从系统表空间移动到独立表空间
  • ALTER TABLE 表名 TABLESPACE [=] innodb_system;把独立表空间的表数据转移到系统表空间

MyISAM是怎么存储数据的

  • 他并没有表空间这一说，直接为表创建三个文件frm、myd、myi，表数据全部都是存放到数据库的子目录下
  • myd是数据文件，myi是索引文件
  • 而且索引全部都是二级索引

视图在文件系统的表示

  • 虚拟表就是视图，查询某个语句的一个别名，但是只会存储一个frm，因为视图没有真实数据。

其它文件

  • 服务进程文件，服务id
  • 服务器日志文件
  • 默认生成SSL和RSA证书和密钥文件

文件系统对数据库的影响

  • 数据库和表名不能超过文件系统限制的长度
  • 特殊字符问题（为了支持数据库和表名与文件系统的特殊字符不支持，mysql把处理数字和拉丁字母都进行编码）
  • 文件长度受文件系统影响

Mysql系统数据库简介

  • mysql存储用户信息和权限信息
  • inforamtion_schema数据库保存的表、存储过程、视图等信息
  • performance_schema保存mysql运行状态信息，性能监控
  • sys通过视图方式把information_schema和performance_schema结合起来，可以了解到mysql服务器的性能信息

总结

  • 数据目录其实就是文件系统的一个目录
  • 在innodb目录里面子目录是数据库，有系统表空间的文件，数据库子目录下面的是独立表空间存储的文件

9.InnoDB的表空间

  • 对于系统表空间来说就是对应几个文件，但是对于独立表空间来说就是对应着一个表名是.ibd的文件
  • 表空间是切分了很多个数据页的池子，插入数据的方式就是捞一个页然后再内存处理之后再存入
  • 通常我们使用的页都是FIL_PAGE_INDEX

mysql能直接运行代码吗_数据库

数据页的组成

  • INDEX类型有7个部分，其中两个部分是通用的。
  • File Header：记录通用信息
  • File Tailer：用于保证数据的完整性

mysql能直接运行代码吗_数据库_02

  • 表空间每个页都有一个页号，而且页号是4个字节组成，也就是说支持2^32个数据页存储
  • FIL_PAGE_PREV和FILE_PAGE_NEXT用于连接数据页形成双向链表
  • 每个数据页的类型是FIL_TYPE表示

mysql能直接运行代码吗_database_03

独立表空间结构

区

为什么要有区？原因就是页之间的一个距离如果太远就是一个随机IO，但是区是一个能够分配64个连续内存页的概念。也就是能够提高IO的效率

  • 连续64个页就是一个区，占用了1MB，因为每个页是24KB,那么64就是26相乘之后就是1MB了

mysql能直接运行代码吗_数据库_04

  • 每256个区就是一个组
  • 从图可以看出第一个组的前3个页面是固定的
  1. FSP_HDR:记录表空间整体属性和本组有多少个区
  2. IBUF_BITMAP:这个页存储的是insert buffer的信息
  3. inode：存储了很多inode的数据结构
  • 其它组都是固定两个页面
  1. XDES:记录256个区的属性,FSP_HDR和这个相似，但是存储了表空间的额外信息
  2. IBUF_BITMAP类型

mysql能直接运行代码吗_mysql能直接运行代码吗_05

段的概念

为什么要使用段？原因就是使用端能够区分叶子节点和非叶子节点，如果不能区分那么遍历的时候还是会进行随机IO，每次遍历到非叶子节点，那么找他的下一层，这个数据页可能不知道会在什么地方。

  • 区是物理上连续的64个页，能够提升IO效率
  • 如果只是使用页的话，可能两个页之间的物理距离非常长。那么这种范围查询就变成了一个随机的IO。
  • 这里的段其实就是叶子节点分成一个区，非叶子节点一个区，这种就是段，一个索引分成两个段
  • 段不仅仅是某些区的集合（叶子节点区），他也是零散的页面集合。因为后面mysql创建了碎片区的概念，里面的页可以用于各种数据，而段就能够通过使用碎片区里面的页面存储自己的数据。碎片区不属于任何一个段。
  • 如果某个段占用了碎片区32个页面之后就会分配多一个区出来。碎片区还是区，但是不只是分配给一个段，碎片区出现的原因就是如果一个区直接分配给相对来说比较小的表，那么它的两个段就占用了两个区导致空间浪费太多。

区的分类

  • 空闲的区：还没使用
  • 有剩余空间的碎片区：碎片区中还有可以使用的页
  • 没有空间的碎片区：页面都被使用
  • 附属于某个段的区：数据量大的时候一个区可以分配给一个段

mysql能直接运行代码吗_mysql_06

为了方便管理区使用的数据结构XDES Entry，每个区对应着一个这样的数据结构

  • Segment Id：每个段有唯一的编号，区所在的段，意思就是区被分配给某个段
  • ListNode：组成一个双向链表，连接每个区
  • State：区的状态
  • Page State BitMap：一共是16个字节，也就是128个bit，区的64个页每个页对应2个bit。第一个bit代表的是这个页是不是空闲的。

mysql能直接运行代码吗_mysql_07

XDES Entry链表

段使用空间过程

  • 先去找FREE_FLAG的区然后取一些零碎的页放入对应的数据，如果没有那么就开一个FREE接着修改区的状态为NOT_FULL也是取出零碎的页存入数据

那么怎么找这些状态的区？

  • 这个时候就需要使用到XDES Entry，FREE的存入一条链表，FREE_FLAG存入另一条，还有就是FULL_FLAG页存入另一条，那么查找的时候就不需要遍历所有的区，而是通过遍历不同状态的链表找到对应的区存入数据
  • 当段存入的数据等于32个页的时候就可以独立开出一个区来存储这些数据

那么问题来了怎么知道哪些区属于某个段？

  • 能不能直接把FSEG状态的区连接起来，很明显不行，原因就是FSEG可能属于不同的段。
  • 解决方案就是通过三个链表还是上面三个状态但是他们隶属于一个段而不是一个表空间（FREE,NOT FULL,FULL三种状态）
  • 比如一个聚簇索引和一个二级索引的表，那么就会有四个段，这些段都维护了3个链表，如果加上隶属于表空间的那么一共就是维护了15个链表。这样可以快速知道这些链表实际上就是属于某个段。

那么碎片区的段怎么处理？

链表基节点

那么如何找到对应的链表？使用了下面这个链表基节点寻找

  1. List Length：链表的节点个数
  2. First Node Page Number和First Node Offset指向头结点
  3. 下面那个就是指向尾节点在表空间的位置
  4. 通常会把这个节点放到表空间的固定位置

mysql能直接运行代码吗_database_08

链表总结

一个表空间里面有很多个区，区里面的结构XDES Entry是这些区的链表结构节点，段下面有好几个区，需要通过这个节点来进行对区的管理。然后查找某个链表的时候可以通过List Base Node

段的结构

  • 零散页+区组成
  • 区有XDES Entry管理对应的属性，段也有一个INODE Entry进行管理。

组成

  1. Segment ID：INODE Entry对应的段的编号
  2. Not_FULL_N_USED:这个字段记录not_Full链表使用到的页，下次能够直接找到对应的节点，而不是从第一个页来遍历。
  3. 3个List Base Node：就是FREE链表，NOT FULL链表和FULL链表对应的ListBaseNode
  4. Magic Number：标记INODE Entry是否被初始化
  5. Fragment Array Entry：零散页的页号

mysql能直接运行代码吗_database_09

各个类型页面的详细情况

FSP_HDR类型
  • 第一组区的第一个页面，存储的是256个区的XDES Entry结构
  • File Header：页通用信息
  • File Space Header：表空间头部，表空间的一些整体信息
  • XDES Entry：存储组内256个区的属性信息
  • Empty Space：用于页结构拓展
  • File Tailer：校验页是否完整

mysql能直接运行代码吗_数据库_10

File Space Header
  • 表空间的整体属性。

mysql能直接运行代码吗_mysql能直接运行代码吗_11

  • 字段的意思
  • List Base Node for FREE List、List Base Node for FREE_FRAG List、List Base Node for FULL_FRAG List。

这三个链表存储的位置就是File Space Header

  • FRAG_N_USED

表示FREE_FLAG的链表使用的页数量，方便去找到空闲的页

  • FREE LIMIT

相当于就是一个初始化的方式，第一种是先把所有空闲区的XDES Entry存入FREE链表，第二种就是只存入一部分，如果需要使用那么才会继续存入和分配。在这个字段表示的范围之内的页号已经都被初始化。后面的没有

  • Next Unused Segment ID

字段表名下一个最大的索引号，防止重复。

  • Space Flags

mysql能直接运行代码吗_表空间_12

  • List Base Node for SEG_INODES_FULL List和List Base Node for SEG_INODES_FREE List
    段如果太多那么INODE Entry也会非常多，这些INODE形成两个列表
  1. SEG_INODES_FULL链表，表示这个节点上面的所有XDES Entry和零碎页都被用完了
  2. SEG_INODES_FREE链表，表示这个链表上面的INODE仍然有空间

mysql能直接运行代码吗_mysql能直接运行代码吗_13

XDES Entry页类型部分
  • 和FSP_HDR类型比较就是少了对表空间的属性描述的File Space Header

mysql能直接运行代码吗_数据库_14

IBUF_BITMAP页类型
  • 存储了change Buffer的信息
INODE类型
  • 第一个分组的第三个页
  • 用于管理INODE Entry

mysql能直接运行代码吗_表空间_15

  • List Node for INODE Page List一个表空间可能不止有85个段。（每个页只能存储85个段）
  • SEG_INODES_FULL链表：就是记录这些INODE的信息
  • SEG_INODES_FREE链表：记录还是空闲的INODE信息。
  • 下次创建INODE Entry的时候
  1. 先去看看SEG_INODES_FREE中找到对应的INODE页来存储INODE Entry。
  2. 如果SEG_INODES_FREE不够了，那么就要去FREE_FLAG去申请一个INODE页面过来，并且存入这个链表。

mysql能直接运行代码吗_mysql_16

Segment Header的运用

怎么知道哪个段对应哪个INODE Entry？

  • 在index数据页上面的Page Header上面的两个数据PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP
  • PAGE_BTR_SEG_LEAF叶子节点头部信息，PAGE_BTR_SEG_TOP非叶子节点头部信息他们其实对应着一个Segment Header结构
  • 这个结构上面意思就是这个叶子节点或者是非叶子节点在哪个表空间，哪个页面的页号，页的偏移位置。这两个结构只需要记录在索引的根页面。

mysql能直接运行代码吗_mysql_17

mysql能直接运行代码吗_表空间_18

mysql能直接运行代码吗_表空间_19

真实表空间大小

会逐步自我拓展

系统表空间

mysql能直接运行代码吗_mysql_20

  • 对于系统的extent1和2的64~191的数据页是双写缓冲区

mysql能直接运行代码吗_mysql_21

Innodb数据字典

插入数据需要校验表是否存在，表索引存在于哪个表空间哪个页面，所以还需要保存一些额外信息，元数据

  • 表属于哪个表空间，表里面多少个列
  • 表的列类型
  • 表多少个索引，对应哪几个字段，索引跟页面在哪个表空间的页
  • 表有什么外键，对应哪些表的哪些列
  • 表空间对应的文件系统的哪个文件

通过下面的内部系统表来对这个信息进行一个管理。

mysql能直接运行代码吗_database_22

SYS_TABLES

mysql能直接运行代码吗_database_23

SYS_COLUMNS

mysql能直接运行代码吗_mysql能直接运行代码吗_24

SYS_INDEXS

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4U3EhR5E-1635775688729)(…/…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20211031184622029.png)]

SYS_FIELDS

mysql能直接运行代码吗_数据库_25

  • 有了上面的表就可以定位到某个表的元数据信息，先查出tableID
  • 然后再去找表的列
  • 找索引信息和索引的位置
  • 最后就是通过index_id获取索引的所有索引列和位置信息

最后这些元数据表的聚簇索引和二级索引都被放到了一个固定的页，Data Dictionary Header

mysql能直接运行代码吗_database_26

  • 这个7号页上面有Segment Header，也就是把对应的数据存入了一个段

mysql能直接运行代码吗_database_27

  • 最后就是Data Dictionary Header里面的字段基本上就是给表和索引还有列定义一个唯一的主键id

mysql能直接运行代码吗_表空间_28

总结

链表部分总结

  • 对于XDES Entry来说每个区对应这一个这样的节点，这个节点包括了页的状态，区的状态，还有就是这个区属于哪个段的信息，还有就是一个ListNode结构来连接各个区,占用了40个字节，而且通过双向链表的方式连接每个区。之所以做出这样的节点，是为了让相同状态的节点连在一起，并且能通过表空间的基节点找到链表头的位置，快速定位这些需要使用的区的位置。那么这个地方其实还有一个问题就是关于节点的定位问题，定位其实就是靠ListNode的偏移，它不仅仅是一个指针。所以这就和区关联起来了。所以忽略任何一个属性的结果就是理解不到位（这是链表相关的）
  1. 对于隶属于表空间的区链表有三条FREE,FREE_FLAG,FULL_FLAG，FSEG(附属某个段的区)
  2. 对于段来说就是FREE,NOT_FULL,FULL三个链表
  • 那么怎么找到这些链表，可以通过List Base Node找到区的三种状态链表
  • 对于段来说就有INODE Entry它能够迅速找到段的三种链表的原因就是ListBaseNode（指向段三个状态的XDES Entry链表），然后就是那些零散的页entry

页部分总结

  • 第一个要解决的就是这个链表和节点到底存储在哪里，他们其实并不是每个节点都会进行相连接。这些节点实际上会存放到每个组的第一个区的前几个页上面。而且如果要找到链表可以通过File Space Header类型的页描述信息来寻找。对于每个页来说都会有这样的类似描述信息
  • 最后就是表空间的FSP_HDR记录了表空间的属性，包括File Space Header表空间信息和表空间的三种链表的位置，这三个链表是基于表空间的。相对应的就是段的INODE Entry对应的那三种链表在页的位置。而且因为数据区是连续存储的，所以对应的节点也是能够通过计算迅速找到的。而且还能够管理段的id。
  • XDES类型这个类似与FSP_HDR但是没有管理段的信息，只是管理这个组里面所有区的XDES Entry
  • 然后就是INODE的页面，存储的就是所有的INODE Entry节点的信息，所有的段基本上都存在了这里，如果段存储的INODE Entry不够用的时候就会去请求INODE来开辟更多的空间。所以总的来说他们的关系就是INODE ENTRY包括了XDES ENTRY和零碎的页。然后INODE又管理着这些节点，INODE存在第一个页上。这个页就是用来管理所有段的信息。回忆一下段节点具备的字段，包括segmentid，关联的XDES Entry也就是属于段的区，还有属于段的零碎页。问题就是一个段可能一个段节点并不能处理完，那么还需要一个结构页来管理所有段节点的位置和信息。而这个INODE就是管理所有的INODE Entry，分别有两个状态是FREE和FULL。并且可以通过FSP_HDR能够迅速找到链表位置，给新的段分配节点。
  • 其实这里还有一个问题如何通过id快速定位。
  • PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP对应着Index类型页的Page Header上面的两个字段，他们只存在B+的根节点，这两个字段的结构又是一个Segment Header也就是说这个可以迅速找到叶子节点和非叶子节点所在的表空间，页的位置。这个就是索引能够通过段来管理的原因，段实际上就是通过区来管理这些索引页，索引页的根节点又可以通过seg leaf快速定位叶子段节点的位置，然后就可以通过直接取出这些数据到内存进行一个查询。段就是一个为了让存取速度更快的结构，索引是能够通过字段信息找到这些存储结构加载进内存形成查询的索引树。

最后就是系统表空间部分

  • 这里的一个问题其实就是你要怎么找到索引根的位置？从而能够找到INODE Entry的位置。而系统表空间就是全局解决了这样的一个定位问题。段和区只是方便管理空间，存取方便。而且能够通过链表和节点迅速定位区和页的位置。但是问题就来了，如果要查找某个索引，只依靠这些结构够吗？比如一个表的索引在哪，列在哪，外键是什么，存在哪个系统文件上。
  • 系统表都是通过B+树的形式存储在系统表空间的页上面，因为系统表空间是可以存储多个表的。他依靠四个基本表SYS_TABLES,SYS_INDEX,SYS_COLUMNS,SYS_FIELDS,分别就是保存所有表的信息，所有表的索引，所有表的列，所有表索引列的位置。
  • DataDictionaryHeader页面，上面四个基础表就是定位其它数据表的位置和索引还有列。然后怎么记录这些表中表的位置？只能编码到代码中也就是7号页就是DataDictionaryHeader页面。记录了四张表索引的根索引位置（因为系统表查询表位置也是需要索引快速定位的），并且直接通过一个段来存储这个页的信息，但是所需要的数据很少，索引存储的段所在的区是一个碎片区

mysql能直接运行代码吗_表空间_29

10.单表访问的方法

访问方法

  • 使用全表扫描查询：表的每一行记录都进行一次查询
  • 使用索引进行查询
  • 针对主键和唯一二级索引
  • 针对普通二级索引
  • 索引的范围查询
  • 直接扫描整个索引

mysql查询语句就是访问方法的类型。同一个语句可能会有不同的路线。

下面参考这个表

CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

const

SELECT * FROM single_table WHERE id = 1438;

  • 直接通过聚簇索引获取到数据

mysql能直接运行代码吗_mysql_30

SELECT * FROM single_table WHERE key2 = 3841;

  • 通唯一二级索引找到主键id，然后再通过聚簇索引找到对应的记录
  • 这种就像坐火箭一样快，一次就能查询成功。就不需要查询多次，这种级别就是const，相当于就是查询了一条记录。列和常数的一个等值比较。
  • 如果是唯一二级索引和主键索引是多个索引列，那么这些列都需要有值才能够是一个const级别的查询，只查询一条记录
  • 但是唯一索引不会限制NULL的情况，所以如果有这种NULL的情况就可以能是查询到多条

mysql能直接运行代码吗_mysql能直接运行代码吗_31

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-92JMQvQW-1635775688736)(…/…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20211101122847871.png)]

ref

SELECT * FROM single_table WHERE key1 = ‘abc’;

  • 普通二级索引的索引列和常数的等值比较
  • 普通索引并不会限制记录的唯一性，所以可能会查询到多条，那么他的查询代价取决于这个值到底有多少个记录，然后进行回表
  • 二级索引如果访问的key1 is null都是ref的级别，也就是查询多个

mysql能直接运行代码吗_mysql_32

mysql能直接运行代码吗_database_33

SELECT * FROM single_table WHERE key_part1 = ‘god like’;

SELECT * FROM single_table WHERE key_part1 = ‘god like’ AND key_part2 = ‘legendary’;

SELECT * FROM single_table WHERE key_part1 = ‘god like’ AND key_part2 = ‘legendary’ AND key_part3 = ‘penta kill’;

  • 如果是多个索引列，那么只要是左边对应的索引列那么基本上都是ref的查询级别

ref_of_null

SELECT * FROM single_demo WHERE key1 = ‘abc’ OR key1 IS NULL;

  • 这种就是等值列+null的查询，也是扫描好几条数据

mysql能直接运行代码吗_数据库_34

mysql能直接运行代码吗_database_35

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

  • 这种和上面那种不同的地方就是不能够一下子定位到数据，而是需要查询一个范围的数据，如果使用索引的话那么可能需要回表条件，或者是直接使用聚簇索引的全表扫描。这样还不需要去回表。

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = ‘abc’;

  • 这种的意思其实就是后面的key_part2并不是索引的最左边的索引列，导致索引的排序失效。
  • 只能够直接去扫描整个二级索引
  • 但是这里的key_part1, key_part2, key_part3三个刚好就是要查询的列都在索引列里面，所以不需要进行回表。
  • 比起上面那种，这种就是全索引遍历，但是不需要回表操作。相当于就是覆盖索引的遍历。而且二级索引存储的数据少

all

SELECT * FROM single_table WHERE key1 = ‘abc’ AND key2 > 1000;

这种就是全表扫描，这里可以使用key1或者是key2的索引

  • 假设使用的是key1的索引
  • 先找到key1=abc的所有二级索引记录
  • 然后回表查询，再根据key2>1000来进行过滤
  • 这里其实就只是使用了索引key1所以key2是没有作用的，对于key1的索引来说只有key1和主键，但是没有key2，所以需要通过在二级索引找到所有key1相关的记录，然后回表才会使用到key2.相反其实也是，先找到key2>1000的所有记录，然后再回表对应key1。也就是看这两个索引到底谁的扫描行数越少，那么回表次数就会越少

明确range访问方法的使用范围区间

  • =、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=这些符号可以做到一个range的范围
  • 使用range要找出可以使用的索引和范围区间

所有查询条件都可以使用某个索引

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

  • 相当于key2的交集和使用key2的索引

mysql能直接运行代码吗_表空间_36

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

  • 这个就是使用并集

mysql能直接运行代码吗_database_37

有的查询条件无法使用的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = ‘abc’;

  • 索引idx_key2并不包括common_field = ‘abc’;这个条件相当于就是回表的时候才会去使用，也就是在索引上不会过滤这个条件，他也没办法过滤
  • SELECT * FROM single_table WHERE key2 > 100 AND TRUE;在索引上面这个common_field = ‘abc’;就变成了这个查询，也就是不加以判断

复杂搜索条件找出的范围匹配区间

SELECT * FROM single_table WHERE

(key1 > ‘xyz’ AND key2 = 748 ) OR

(key1 < ‘abc’ AND key1 > ‘lmn’) OR

(key1 LIKE ‘%suf’ AND key1 > ‘zzz’ AND (key2 < 8000 OR common_field = ‘abc’)) ;

  • 第一个就是看看where条件有哪些列，可以使用什么索引，可以使用key1和key2的索引
  • 分析他们的范围区间，把用不到这个索引的条件变成true过滤掉

(key1 > ‘xyz’ AND TRUE )

OR (key1 < ‘abc’ AND key1 > ‘lmn’)

OR (TRUE AND key1 > ‘zzz’ AND (TRUE OR TRUE))

  • 然后取出这些区间的值，回表查询
  • 如果使用index2来进行查询

(TRUE AND key2 = 748 ) OR

(TRUE AND TRUE) OR

(TRUE AND TRUE AND (key2 < 8000 OR TRUE))

  • 对于index2来说范围就是true也就是查询整个索引，那么还不如直接去到聚簇索引进行全表扫描好了。

索引合并

使用到多个索引进行查询

Intersection合并

SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;

  • 现在key1索引找a
  • 然后再key3索引找b
  • 然后计算这两个索引找出来的主键做一个交集
  • 最后回表操作。
  • 有了交集就不需要回表那么多次了。
  • 虽然读取多个索引也需要性能消耗，但是索引是顺序IO但是回表是随机IO。因为回表你不知道自己查询了什么ID可能数据页之间相隔很远。但是索引的数据页一定是碰到一起的（回忆一下区的概念）

可以使用这个合并的情况

情况1：索引的索引列都被用上，并且都是等值匹配。

SELECT * FROM single_table WHERE key1 = ‘a’ AND key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;

  • 这里是idx_key1的索引和idx_key_part的索引列都被用上，可以使用合并交集

SELECT * FROM single_table WHERE key1 > ‘a’ AND key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’;

SELECT * FROM single_table WHERE key1 = ‘a’ AND key_part1 = ‘a’;

  • 上面这两种就不行。第一个使用了范围匹配。第二个没使用全等值。
情况二：主键列可以是范围匹配

SELECT * FROM single_table WHERE id > 100 AND key1 = ‘a’

  • 这种其实就是因为合并的时候本来就是依靠主键来进行合并，而且主键对于相同的索引列来说就是升序的排序。所以id>100不需要去到聚簇索引获取记录，而是能够直接和二级索引下面的主键进行一个有序的合并

Union

相当于就是两个索引的并集主键

情况1：就是索引列是等值匹配，而且索引中索引列都出现了

SELECT * FROM single_table WHERE key1 = ‘a’ OR ( key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’);

  • 上面这个就是全部索引列都出现了，可以使用idx_key1和idx_key_part

SELECT * FROM single_table WHERE key1 > ‘a’ OR (key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’);

SELECT * FROM single_table WHERE key1 = ‘a’ OR key_part1 = ‘a’;

  • 这两种就不行，范围和不匹配索引列。

情况2：如果是主键那么就可范围匹配

情况3：使用Intersection索引合并的搜索条件

SELECT * FROM single_table WHERE key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’ OR (key1 = ‘a’ AND key3 = ‘b’);

  • 相当于就是intersection在key1和key3
  • 然后Union就是(key1,key3)和key_part索引的搜索交集之后再回表

Sort-Union合并

SELECT * FROM single_table WHERE key1 < ‘a’ OR key3 > ‘z’

  • 这种不能够直接并集的原因就是不是等值操作，可能会造成主键变多，还不如看看谁扫描的少，另一个回表再查询。
  • 但是这种可以使用Sort-Union合并，就是先查出两个索引记录，然后进行主键排序再Union并集最后在回表。
  • 使用与查询的记录比较少的时候
  • 但是Intersaction相对来说就是查询记录多的情况，为了压缩才会使用。如果使用Sort-Intersaction还需要进行大量排序，可能还不如直接全表扫描或者是直接按照一个索引来查询回表继续查。

联合索引代替Intersection

SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;

  • 直接创建一个联合索引，比你查询两个索引之后合并快多了。

总结

  • 查询代价
  • const查询一个记录，通常是等值操作
  • ref查询多条记录，通常也是等值
  • ref_of_null也是查询多条记录
  • range:范围查询，就是某个范围而不是等值了
  • index:能够通过二级索引直接获取全表扫描的数据，也可以说是覆盖索引
  • all：全表扫描，二级索引不适用，可能回表代价太大。
  • range的范围
  • 第一种所有条件都可以使用某个索引
  • 第二种就是1个索引，但是有的列无法使用，可以回表后再进行比较这个列
  • 第三种就是各种索引，需要挑一个扫描少的，回表后才继续对比
  • 各种合并
  • Intersection交集，数据多的时候可以合并两个索引
  • Union和Sort-Union可以在使用数据少的时候进行并集。
  • 联合索引代替Intersection
  • 减少查询多一个索引的查询代价

如看看谁扫描的少，另一个回表再查询。

  • 但是这种可以使用Sort-Union合并，就是先查出两个索引记录，然后进行主键排序再Union并集最后在回表。
  • 使用与查询的记录比较少的时候
  • 但是Intersaction相对来说就是查询记录多的情况，为了压缩才会使用。如果使用Sort-Intersaction还需要进行大量排序，可能还不如直接全表扫描或者是直接按照一个索引来查询回表继续查。

联合索引代替Intersection

SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;

  • 直接创建一个联合索引，比你查询两个索引之后合并快多了。

总结

  • 查询代价
  • const查询一个记录，通常是等值操作
  • ref查询多条记录，通常也是等值
  • ref_of_null也是查询多条记录
  • range:范围查询，就是某个范围而不是等值了
  • index:能够通过二级索引直接获取全表扫描的数据，也可以说是覆盖索引
  • all：全表扫描，二级索引不适用，可能回表代价太大。
  • range的范围
  • 第一种所有条件都可以使用某个索引
  • 第二种就是1个索引，但是有的列无法使用，可以回表后再进行比较这个列
  • 第三种就是各种索引，需要挑一个扫描少的，回表后才继续对比
  • 各种合并
  • Intersection交集，数据多的时候可以合并两个索引
  • Union和Sort-Union可以在使用数据少的时候进行并集。
  • 联合索引代替Intersection
  • 减少查询多一个索引的查询代价