第一章
Mysql也是基于客户端和服务端的架构,由客户端连接上服务端,进行登录,而后在客户端输入命令到服务端,由服务端来处理这些命令,对数据进行处理.Mysql服务端进程被称为数据库实例.
Mysql的服务端和客户端连接也就是进程之间的通信,主要的方式有TCP、命名管道、共享内存、Unix套接字.
服务端对客户端请求的处理主要分为三个步骤:连接管理,解析与优化,存储引擎
连接管理:
客户端与服务端进行连接的时候,服务端会创建一个线程来进行处理客户端的请求,而这些线程由线程池管理,避免重复创建销毁线程和管理连接.客户端首先携带主机信息,用户名,密码等信息进行请求连接,与服务端会根据这些信息进行认证.客户端和服务端之间的通信可以通过TLS协议来进行加密.建立连接后服务端对应的线程会一直等待客户端的请求,进行处理.
解析与优化比较主要的部分:
查询缓存:
如果最近服务端所接收到的查询语句一模一样,并且查询语句中不包含一些系统表或是函数,则会从之前的查询结果直接返回给后一个查询请求,如果有其他改变数据的行为也会将该查询缓存删除,因为维护缓存会造成许多开销,查询缓存在5.7.20已经不推荐使用了,在8.0版本已经删除.
语法解析:
分析客户端命令中的语法是否有错误,并且将命令中有关的表,查询条件等放到Mysql内部所使用的数据结构上
查询优化:
可能客户端的Sql语句有效率不足或是效率可优化的情况,该步骤会将对应的语句进行一个优化,如外连接转换成内连接,表达式简化等.
存储引擎:
到这一步服务端才开始真正的进行访问表中的数据,不同存储引擎为服务端提供了统一的对数据的存取和更新操作接口,不同的存储引擎有不同的长处.服务端在寻找到符合要求的数据时,会先将数据发送到缓存区,当缓存区满的时候统一发送数据到客户端.
第二章
启动项和配置文件
Mysql的服务端和客户端都有一些选项进行配置,可以在启动脚本命令增加对应的属性或是在配置文件中进行配置来进行设置.启动项中配置的只对当次启动生效,而配置文件中配置的每次启动都生效,只需要配置一次.
如果在不同的配置文件中配置了相同的设置,会按照顺序来进行配置,保留的就是最后一个,如果配置文件和命令行都有配置相同的设置,一般已命令行中的为准
系统变量
Mysql服务端程序有许多变量会影响程序行为, 如最大客户端连接数(max_connection)和查询缓存大小(query_cache_size)等,我们将这些变量称之为系统变量.
每个系统变量都有一个默认的值,我们可以通过命令行或是配置文件来进行这些系统变量的配置, 从而来影响我们的服务端程序.
查看系统变量和该系统变量的当前值:
SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式]
可以使用通配符,如LIKE '%关键字%' 来进行匹配.
大部分的系统变量都可以在运行的时候进行更改,而无需去重启服务端程序.
系统变量范围:
Session(会话):对应客户端连接的操作
Global(全局):对应服务端全局的操作
修改系统变量语法:
SET [GLOBAL|SESSION] 系统变量名=值;
如果省略指定系统变量范围则默认为SESSION,这点SHOW命令也一样,增加范围修饰可以使显示出对应系统变量所对应的全局值或是会话值.
有些系统变量只具有SESSION范围或是GLOBAL范围,这要依据情况而定,比如只针对服务端有效或是只针对客户端有效的变量一般具有该特性.
状态变量
状态变量是用于描述MYSQL服务器运行情况的变量,如Threads_connected(当前客户端建立连接数)等等.
状态变量的查询语法与系统变量相似:
SHOW [GLOBAL|SESSION] STATUS[LIKE 匹配的模式]
注意:状态变量只能查询,不能设置.
第三章:字符集和比较规则
字符集是一种表示处理字符与二进制之间编码和解码的规则.
字符之间有时需要进行大小比较,一种字符集可以有多种比较规则.
有许多字符集可以不同的字符可能会有不同的字节长度来存储,比如GB2312字符集,该字符集包含的ASCII字符集,如果对应的字符是ASCII字符集中的字符,则只会用一个字符来存储.那如何判断用几个字节来存储呢?如果是ASCII字符集(0-127)的字符,则最高位会为0,如果为0则表示是ASCII字符集的字符,否则该字节就是其他字符的一部分.我们可以称这种编码为可变长编码方式.
不同字符集可能对相同的字符有不同的编码方式,这一点要注意.
MYSQL支持的字符集
utf8和utf-8mb4
utf8使用1-3个字节来存储字符
utf8mb4使用1-4个字节
如果需要存储emoji表情或是四个字节存储的字符,请使用utf8mb4,该字符集在Mysql8.0版本已经成为了默认字符集
查看当前数据库所支持的字符集:
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
查看MYSQL中支持的比较规则:
SHOW COLLATION [LIKE 匹配的模式];
字符集和比较规则的应用
字符串和比较规则有四个级别:服务器级别,数据库级别,表级别,列级别.
服务器级别:在该服务端创建表所对应的字符集和比较规则.
SHOW VARIABLES LIKE 'character_set_server';
数据库级别:在创建数据库或是修改数据库的时候所指定的字符集和比较规则.想要查看当前数据库所对应的字符集和比较规则:
USE 数据库名;
SHOW VARIABLES LIKE 'character_set_database';
表级别:在创建表和修改表时所指定的字符集和比较规则.
列级别:对于存储字符串的列,同一个表中不同列可以使用不同的字符集和比较规则,这需要在创建列或是修改的时候进行指定.
注意:当修改字符集或是比较规则的时候,所对应的另外一个也会跟着变化,比如修改了字符集,比较规则会变成该字符集默认的比较规则.如果修改了比较规则, 则字符集会被修改为该比较规则适用的字符集.
客户端和服务器通信过程中所使用的字符集
编码和解码所使用的的字符集不一致可能会导致出现不理想的结果.如utf-8编码的字符,服务端使用ISO字符集去解码.从而获取了不同的字符.
字符集转换
当接收字节序列时,先使用编码的字符集进行解码,而后再使用目标的字符集进行编码、存入,我们称这是字符集转换
在客户端进行请求服务端,服务端返回数据,可能服务端会对这次请求的字节序列进行多次字符集转换处理.
在客户端与服务端进行连接之后,服务端会单独为客户端维护一个character_set_client的(SESSION)变量来表示客户端的字符集.
如果客户端所实际使用的字符编码方式和character_set_client所表示的字符集不能解码,会发生错误.
服务端在真正处理的时候会将对应字节序列 character_set_client变量的字符集进行转换为character_set_connection变量对应的字符集.这俩个变量都是SESSION级别.
在服务端返回数据给客户端的时候也可以通过character_set_result这个变量来指定所返回的字符集.
字符集有关系统变量(都是SESSION范围,即对会话进行维护的)
character_set_client 客户端的字符集
character_set_connection 服务器在处理请求时所使用的字符集
character_set_result 服务器返回给客户端的编码的字符集
上述系统变量在客户端成功和服务端进行连接后,服务端会单独对每个客户端连接维护一组相应的变量.
第四章 InnoDB记录存储结构
存储结构或是对表的读取、操作这些都是由表的存储引擎来决定的.所以一般不同的存储引擎的存储结构也不同.
InnoDB页
InnoDB引擎是一个将数据存储在磁盘的存储引擎,而数据处理都是发生在内存的,如果每次读取数据和新增或是修改数据都是一条一条的操作,那会让操作变得非常慢.
所以InnoDB引擎将页作为与磁盘和内存交互的基本单位,将数据分成若干个页.一般来说InnoDB页的大小(innodb_page_size)是16KB.也就是一次操作最少使用16KB的内容.
InnoDB行
我们在表中操作数据都是以记录为单位进行操作的,也可以称为行格式.
行格式有:COMPACT REDUNDANT DYNAMIC COMPRESSED
我们可以通过ROW_FORMAT属性来进行指定对于表的行格式
CREATE TABLE 表明 (列的信息) ROW_FORMAT=行格式;
ALTER TABLE 表明 ROW_FORMAT=行格式;
COMPACT
额外信息
变长字段长度列表
用于存放某些可变长数据类型(如VARCHAR,VARBINARY,BLOB等)字段的真实占用的字节数,并且该条数据的该字段非null.按照列的顺序逆序来存储.
存储变长字段长度字节数判断方式:如果(字符集表达字符最大字节数 * 变长数据类型的字符数 ) > 255 字节 ,并且真实存储的数据字节数>127 ,则采取2个字节来表示字节长度,否则则用1个字节来表示.
如果某个字段特别长,InnoDB可能会将该字段的部分值存放到所谓的溢出页,该长度就会只是存放在本页面的长度了.而溢出字段的占用字节数长度就有另一种特殊表示方式了.
NULL值列表
如果该条记录的某个字段为NULL值,再将其NULL值存储,会造成空间浪费,所以我们将为NULL值的字段在NULL值列表中作表示,而在真实数据部分忽略这些字段的存储.
1:统计某个表允许存放NULL值的字段
2:表示该条记录的对应列是否为NULL值,每个允许存放NULL值的列都会有个对应的二进制位,如果为1则该列为NULL值,为0则不为NULL值.二进制和变长字段长度列表同样按照列的顺序逆序排位.
3:NULL值列表必定为整数字节表示,比如如果一个表允许NULL值的字段有3个,则NULL值列表为1字节,而没有使用到的字节中的高位补0.
记录头信息
记录头信息固定为5个字节组成.用于描述一些记录的属性.
真实数据
这一段是用来存储这条记录真实的值
除了我们自己定义的列之外, MYSQL还为每个记录默认添加了一些列(也可以说是隐藏列)
列名 是否必须 占用空间(字节) 描述
row_id 否 6 行ID,唯一标识一条记录
trx_id 是 6 事务ID
roll_pointer 是 7 回滚指针
其中row_id是如果用户创建表的时候没有自定义主键,并且也没有使用UNIQUE约束并不允许NULL值的字段作为主键,则会给表默认创建一个row_id隐藏列来充当该表的主键.
可变长数据
虽然一些数据类型并不是可变长度类型,但是根据字符集的不同还是可能会给他分配变长字段长度.
比如CHAR(M)类型,当他采用ASCII字符集时候,就不会给他表示对应的占用字节长度.但是如果CHAR(M)的字符集使用UTF8这类使用变长编码来表示字符的字符集的话,也会给该字段进行表示占用字节长度.
同时对于该字段在真实数据空间中至少占用M个字节,即使只是空字符串.这样在更新该记录的该字段时,只要该字段的值大于旧值的字节长度又小于M个字节时,就可以直接进行更新,而不需要重新分配新的记录空间.
REDUNDANT
REDUNDANT是MYSQL5.0之前就在使用的行格式.
字段长度偏移列表
这个行格式会将所有的字段的长度信息都逆序存储到这个列表中,并且他和COMPACT直接存储长度信息不一样,他是通过俩个相邻偏移量的差值来计算长度的.
比如一个记录的字段长度偏移列表(在真实数据中的列开始位置和结束位置)是
06 0C 13
那对应的长度就是
0x13 - 0x0C = 7字节
0X0C - 0x06 = 6字节
0x06 - 0x00 = 6字节
那第一
记录头信息
该行格式记录头信息占用6字节(48位)
比COMPACT多了n_filed(列的数量)和1byte_offs_flag(标记字段偏移列表偏移量使用1个字节还是2个字节表示)这个俩个属性,少了record_type(当前记录的类型)属性
1byte_offs_flag(1:1字节 0:2字节):如果该条记录真实的数据所占用的字节数<=127 则每个列对应的偏移量为1个字节.
大于127但不大于32767时,则每个列对应的偏移量占用2字节.
如果大于32767则存放到所谓的溢出页中.在本页中只保留前768字节和20字节的溢出页地址.偏移量还是用俩个字节来存储.
NULL值处理
如果某列对应的偏移量第一个bit位是1,那该列就是NULL值,所以如果真实数据不大于127就用1字节来表示偏移量,因为首位要表示是否为NULL.
如果某列是定长类型(如CHAR(M)),则NULL值也会占用真实数据部分空间,并且全用0x00字节填充.
如果某列为不定长类型如(VARCHAR(M)),则NULL值不在真实数据部分占用任何空间.
存储格式字符集
REDUNDANT对于变长编码的处理方式十分简单粗暴, 直接按照该字符集所使用的表示一个字符的最大字节数*对应长度的真实空间进行分配.
好处:更新时不用申请新的存储空间,可以直接在原空间分配 坏处:会浪费一些存储空间
溢出列
InnoDB操作单位一个页的大小一般为16KB,但是不排除某些记录的数据大小超过16KB,而这个时候某些特别长的字段就不会全部存放在该条记录的真实记录处了,会存放在该字段的真实数据处位置放置一部分的真实数据,然后使用20字节来存放真正存储数据的页地址和分散在其他页面上数据的占用字节数.从而找到剩余数据.(一般来说只会存储该字段的前768字节和一个指向其他页的地址,我们称该其他页为溢出页,该字段为溢出列)
那么在什么时候才会产生溢出列呢?
MYSQL中规定到一页至少要有俩条记录
页中存放额外的信息占用132字节
每个记录额外信息为27字节
如果我们有一个demo表,该demo表中只有一个varchar字段.
那不发生溢出现象得满足:
132+2*(27+n)<16384
n<8099
长度得小于8099,这条记录才不会出现溢出列现象.
不过一般表中不止有一个字段,一般不需要去关注这个临界点,只要了解如果一个列过长,就有可能会出现溢出列.
DYNAMIC 和 COMPRESSED
DYNAMIC和COMPRESSED与COMPACT行格式的差别就是对于溢出列的处理不同,他不像COMPACT会将溢出列的一部分留存在真实数据空间,而是会只留存一个20字节的溢出页地址和真实数据字节数,而真实数据全部存放在对应的溢出页中.
并且COMPRESSED会采用压缩算法对页面进行压缩.
第五章 InnoDB数据页结构
页:InooDB管理存储的基本单位,一般大小为16KB
页有存在许多种,比如存放数据的页,存放undo日志的页,这章主要讲的是存放表中记录的页(索引页).
数据页结构
页中的存储
当一个页刚生成的时候,是没有User Records部分的,只有Free Space空间,有新的记录要插入到该页中时, 会在该页的Free Space申请一个记录大小的空间,然后占用该区域(即划分为User Records部分).
当该页的Free Space空间使用完,如果还有新的记录,就会申请新的页去存储该记录.
记录头信息
delete_flag
当一条记录被删除的时候,不会从磁盘上移除,而是在这条记录打上一个删除标志(即值为1),这些删除的记录会形成一个垃圾链表(用于记录可重用空间),当有新记录插入时,可以直接在这些空间上覆盖掉被删除的记录空间.
这样避免了直接删除时, 其他记录所要进行的重新排列.
mini_rec_flag
如果该记录是B+树每层非叶子结点中最小的目录项,则该值为1.
n_owned
当该记录是槽中最大的元素时记录了该槽的记录条数,否则为0.
heap_no
我们可以称记录一条条亲密排列的结构伪堆(heap).heap_no就是为了方便管理该堆而设计的.
heap_no记录了记录在数据页中的物理相对位置.每条记录的heap_no都比物理位置在他前面的那条记录heap_no大1,而比后面的小1.
在数据页中有俩个自动创建的记录.infimum和supremum记录,他们的heap_no值为0和1,所以他们在堆中的最前面
InnoDb有一个规定,在该页面中任何用户记录都比infimum记录大,而任何用户记录都比supremum小(对于一条完整记录来说,这个大小指的是主键的大小)
heap_no值一旦分配,就不会修改了,即使被删除.
record_type
表示记录的类型.
0 普通记录
1 B+树非叶结点的目录项记录
2 Infimum记录
3 supremum记录
next_record
表示了当前记录的真实数据到下一条记录的真实数据空间的距离(字节).即对应的位置左边是下一条记录的信息头,右边是下一条信息的真实数据(NULL值列表和变长字段长度列表都是逆序存放的,这样可能能提高高速缓存的命中率).
这个下一条记录是指按照主键值从小到大排列的顺序.
next_record是为了维护页中的记录的一个单向链表,链表汇总的各个结点通过主键值从小到大串在一起.
该链表的起点是infimum 终点是supremum.
如果删除了某个结点,会将该结点的next_record值置为0和delete_flag置为1,并且将指向这个结点的next_record值指向下一个结点,跳过该结点.
在进行增删改的时候,InnnoDb也会维护记录的该链表.
可以理解,heap_no表示了该页中该记录在堆中的位置.而next_record记录了该页中该记录的逻辑(即根据主键排列)顺序.
Page Directory(页目录)
页目录是一种InnoDb为了加快查询速度而应用的一种数据结构.在存放在靠近页尾部的地方.
页目录将页面中的记录(包括infimum和supremum)给按照从小到大依次分组.
我们将每一个分组称为槽(slot),槽记录其最大记录的页面偏移量,而槽中最大的那个记录的n_owned记录了该槽中记录的数量.每个槽占用2个字节.
在infimum所在的槽,只能有一条记录,而supremum所在的槽只能有18条记录,其余的槽只能有48条记录.
新记录的插入
当所对应槽的记录的now_owned大小为<8时,会寻找对应记录的主键值比待插入记录的主键值大,但是差值最小的槽,然后会将对应槽的n_owned值+1,表示该槽又多了一条记录.
当所对应槽的记录的now_owned大小=8时,再插入一条记录,会将原有槽拆分成俩个槽,一个槽4条记录,一个5条记录,会在页目录中新增一个槽,并记录该槽最大记录的偏移量.
查询记录时
因为在页目录中的槽是按照从小到大排序的,所以可以使用二分法在槽中寻找对应记录所在的槽的位置.
当找到槽的位置后,找到该槽中主键值最小的那条记录(从上个槽的末尾记录的next_record寻找),而后从那条记录的next_record属性遍历寻找该槽的记录从而寻找到对应的记录.
Page Header(页头部)
这部分主要存储和存储在数据页记录相关的状态信息,如数据页中的记录条数,Free Space在页面汇总的地址偏移量,页目录中的槽个数等.
比如:
PAGE_DIRECTION:表示最后插入一条数据的方向,如果新插入的数据的主键值比上一条记录主键值的大,那我们称这条记录的插入方向是右边, 否则反之.
PAGE_N_DIRECTION:一个方向连续插入的数量,如果有某条记录插入方向相反,该值会清零而后重新统计.
File Header(文件头部)
页头部所描述的是数据页的信息,而File Header所描述的是通用于各种文件的页信息.
文件头部固定占用38个字节.
FIL_PAGE_SPACE_OR_CHKSUM:MYSQL 4.014之前是本页面所在的表ID,之后为页的校验和, 可以在长字符串比较前先校验该值,如果该值不同就对应的字符串肯定也不同,能够节省时间.
FIL_PAGE_OFFSET:页号,用于让Innodb来唯一定位一个页.
FIL_PAGE_TYPE:用于区分不同的页类型.比如undo日志页,change Buffer空闲列表,溢出页,索引页(数据页)等.
FIL_PAGE_PREV,FIL_PAGE_NEXT:用于关联上一页和下一页,使这些页逻辑上形成一个双向链表串联起来.
File Trailer(文件尾部)
和文件首部一样通用于各种的页.
该部分主要是为了防止当数据更新时,数据页在内存数据刷新到磁盘中,出现断电或是其他中断情况出现.只刷新了一部分数据.
文件尾部分成俩部分
页的校验和(8字节):在数据页刷新到磁盘前,要先将校验和计算出来,如果出现中断情况,因为文件首部会首先刷新到磁盘,文件首部的校验和(FIL_PAGE_SPACE_OR_CHKSUM)就会和该校验和(文件尾部的)不相等.这样我们就可以判断出刷新出现错误了.
页面最后被修改时对应的LSN的后4字节(8字节):和FIL_PAGE_LSN(页面被最后修改时对应的LSN(LOG Sequence Number 日志序号值)值)正常情况下相同.也是用来校验页的完整性.
第六章 快速查询的秘籍 B+树索引
InnoDB上数据页中存储着记录,数据页之间通过双向链表关联,在数据页中快速找到某条记录可以通过页目录来通过二分法寻找对应的槽,而后遍历获取.
暂时讨论查询某一列等于某个常数的情况.
没有索引时查询
以主键为搜索条件,在单个数据页查询会根据主键值寻找对应的槽,然后去遍历记录寻找对应的值.但是如果不是以主键为搜索条件,就不能使用页目录来快速搜索,就只能一条一条去遍历记录,效率很低.
如果需要在许多页中查询那条记录,还需要先定位到该记录所在的页,然后才能查找相应的记录,但是没有索引的情况下,即使使用主键或是其他条件进行条件查询,也没有办法定位到记录所在的页,就只能一页一页的寻找下去,需要遍历所有的数据页,效率同样很低.
索引
我们可以先自己制定一个简易索引方案,该方案的要点如下:
一个数据页中的记录一定比上一个数据页中的记录主键值大,比下一个页中的数据小(通过主键大小比较).这样可以避免数据页上的主键值没有规律,不利于我们寻找对应记录主键值所在的记录页.
我们可以模仿页目录,给所有的页建造对应的目录项,用于记录数据页(page_no)的顺序,因为数据页可能并不是连续的.并且还要记录对应数据页中的最小主键值(key),这样就有助于让我们快速寻找到对应主键值记录所在的页.
这样一个简易的索引方式就成立了,我们查询数据的时候,先在目录项中根据主键值二分查找寻找到对应的页.当插入或是修改数据的时候,就根据得根据主键值寻找到对应的页进行插入,或是从原本的页移动到新的页中,保持数据页间的记录大小约束成立.
我们称这个目录为:索引.
InnoDB的索引方案
我们在上面制作的简易索引方案有俩个缺点:InnoDB使用页来作为管理存储空间的基本单位,最多只能保证16KB的连续存储空间,虽然一个目录项占用不了多少空间,但是当表中数据量膨胀,此时需要十分大的连续存储空间才能放下所有的目录项,这不太现实.
另一个:当对记录进行修改操作(增删改)时,比如有一整个页的数据被删除,那其数据页对应的目录项也就失去了意义,如果要移除对应的目录项就需要将后面的目录项进行向前移动,这种牵一发而动全身的设计不好,或者是将该空间冗余,又浪费了空间.
改进的方法:这些目录项可以看作是列只有主键和页号的用户记录,可以将这些目录项存储在存储用户记录的数据页中统一管理,为了与用户记录区分开来,使用记录头的record_type来区分.
record_type
0:普通的用户记录
1:目录项记录
2:infimum记录
3:superemum记录
目录项记录的min_rec_flag(B+树的每层非叶子结点的最小目录项记录都会添加该标记)属性可能为1, 而用户记录的该属性值都为0
这样查询的流程大致为:先找到对应表的存储目录项记录的页,通过主键值确定该记录所在的页,而后在该页中寻找记录所在的槽,在槽中遍历寻找到对应的记录.
将目录项存储在数据页中可使数据页存储超过16KB时,可以直接分配新的数据页进行存储.通过FIL_PAGE_PREV,FIL_PAGE_NEXT关联.
但是这样又有了另一个问题,当存放页面项的页很多的时候,那我们怎么通过主键值去快速定位到对应存储页面项的页呢?
这里给出的解决办法是:对于页面项记录存储的页,建造更高级的目录,就像高级目录一样.
每次查询从根部,即最高级的目录项数据页进行查询,然后循环渐进,寻找到对应的记录.
我们也称这种数据结构为B+树.我们真正存放用户记录的页都在B+树最底层的结点(叶子结点).其余结点(非叶子结点,内结点)都用来存储目录项记录.最高层的那个结点称为根结点.
最大存储的用户记录数=((B+树层级-1)每个页面能存储的目录项个数)每个页能存储的用户记录条数.
数据页面头的PAGE_LEVEL就代表该数据页作为结点在B+树的层级
聚簇索引
特点:1.使用记录主键值的大小进行记录和页的排序 2.B+树的叶子结点存储的是完整的用户记录.
二级索引
虽然聚簇索引查询速度非常快,但是只能根据主键值来查找对应的记录,如果有别的查询条件怎么办?
我们可以根据对应的查询条件的列,再创建对应的B+树.
二级索引的特点:1.使用指定列(非主键)的大小进行记录和页的排序 2.B+树的叶子结点只存储了对应记录的主键值和指定列 3.指定的列除了有唯一约束外能够重复.
查询过程:
先从根目录开始寻找到C2值对应所在的目录项中,寻找到对应的目录项,查询到C2值所在的页,根据C2值寻找到对应的槽位进行遍历查询记录,如果找到了符合条件的记录,使用对应的记录中的主键去聚簇索引中寻找到完整的用户记录信息(称这种行为为回表),而后回到该二级索引的叶子结点,继续向后遍历,符合条件就回表获得完整记录,直到下一条记录不满足条件为止.
为什么二级索引不存储下完整的用户记录,而是每次要进行回表呢?
因为太浪费存储空间了,并且在每个索引下都存储完整的用户记录都会造成空间的冗余,重复存储了相同的记录.
联合索引
我们可以使用使用多个列来创建索引.
比如我们使用C2列和C3列来创建索引.该索引中就会先根据C2列来排序,如果C2列相同,就会采用C3进行排序.
特点:每个目录项由 索引列和页号组成,如例子中就是C2和C3和页号
叶子结点中存储了索引列和主键列.
InnoDB B+树索引的注意事项:
B+树的形成过程
1.首先每当为一个表创建B+树索引的时候,都会给这个索引分配一个根结点的页面.最开始的时候表中没有数据,根结点也是没有目录项或是用户记录的.
2.而后向表中插入数据的时候,会先把用户记录存储到根节点中(如果根节点页面空间足够存储用户记录).
3.当插入到一定数量,根节点页面可用空间用完之后,继续插入记录,此时会将该根节点页面的所有记录复制到一个新分配的页(比如页a),然后对该新页(页a)进行页分裂操作得到另一个新页(比如页b).
这个时候再将新插入的根据索引列值来选择插入到页a还是页b中.而根节点升级为目录项记录页,将页a和页b所对应的目录项插入到根节点中.
一个B+树的索引的根节点页面从创建开始,就不会再移动了(即页号不会更改),然后记录到某个地方.当有需要用到该索引的时候,直接取出对应根节点的页号即可.
内结点的目录项具有唯一性
为什么需要具有唯一性呢?我们可以设想一个场景:
如果我们二级索引建造的目录项以索引列和页号作为内容记录,那要是有某个索引值数量非常多,导致一整个数据页里或者甚至更多个数据页里都是相同索引值的记录,所对应的目录项记录的索引值也是相同的.
这样如果有一个数据插入进来,或是想要查询对应的数据,我们怎么样才能定位到对应的数据页呢?难道第一个页一个个遍历吗?
所以我们在建立二级索引的目录项时,需要将主键也给记录下来,这样就保证了目录项的唯一性,我们先使用索引列去寻找对应目录项,而后再使用主键再进行筛选过滤,这样就可以确保寻找到一个页所对应的目录项记录.
所以二级索引建造B+树目录项的时候不止会记录索引列,还会记录主键列.
对于二级索引记录项来说,会先根据二级索引的索引列进行排序,当索引列值相等时,就根据主键值去排序.
建立了一个二级索引,就相当于对(索引列,主键列)建立了一个联合索引.
一个页面至少容纳2条记录
如果一个页面只包含一个子目录,会导致页面的层级非常多,所以Mysql规定了一个数据页最少得能容纳俩条数据(如果超出会形成溢出列,保证能够容纳2条).这样能够确保记录的查询速度.
MYISAM的索引方案
MYISAM的记录是按照插入顺序单独存储到一个文件之中,并不进行划分数据页,而是有多少记录就往文件中塞多少记录.我们可以通过行号来进行快速访问一条记录.
MYISAM的索引是单独存储到另一个索引文件中,以表的追歼单独创建一个索引,只不过叶子结点存储的不是完整的用户记录,而是主键值和行号的组合,寻找到对应的行号后再去数据文件中寻找完整数据.
也就是MYISAM引擎即使使用主键查询,也需要进行一次回表操作.相当于MYISAM引擎索引都相当于二级索引.
MYISAM的二级索引叶子结点中也是存储的列+行号.
MYISAM的索引和数据是分开的,而Innodb中索引即数据,数据即索引.
第七章 B+树索引的使用
B+树的特点总结:
谈及B+树索引的使用,我们要先明确其B+树索引的特点,这样能更好的帮助我们熟悉使用时所要注意的点
- 每一个索引都有其对应的B+树.而B+树的有许多层,叶子结点存储用户记录,而其他结点(内结点)存储目录项.
- InnoDB引擎会为自动为主键生成聚簇索引(如果创建表的时候没有生成主键,会寻找表中有没有不可为NULL值并且UNIQUE约束的键,则会默认该列为索引,否则InnoDB会默认添加一个row_id主键列),聚簇索引的B+树的叶子结点存储完整的用户记录.
- 我们可以为其他可能成为查询条件的列创建一个二级索引,二级索引B+树的目录项包括(最小索引列值,最小主键值,页码),而叶子结点包括索引列和主键.如果想要使用二级索引查询到完整的用户记录,需要先查询该二级索引树查询到对应记录的主键,再使用主键去聚簇索引对应的B+树中再进行查询,我们称这种行为叫回表.
- B+树的每层结点(数据页)都按照索引列的大小来进行排序并且形成一个双向链表.而每个页内的记录也根据索引列形成了一个单向列表.如果是联合索引就根据先后顺序来进行排序.如果前一个条件相等就按照后一个条件来继续排序.因为要保证目录项的唯一性,所以当索引相关的列值都相等时,还会根据主键来排序.
- 通过索引查找记录时,先通过根节点一层一层向下寻找,当寻找到对应记录存在的页后,再使用索引列在页目录中寻找到对应的slot(槽),而后去遍历槽内的记录获取到对应的记录.
索引的代价
虽然索引对于我们在寻找一条记录根据某些条件获取他所应该在的数据页时十分好用,但是我们还是需要了解索引所带来的负面代价.
- 空间代价:每创建一个索引,都要为其索引创建一个对应的B+树,每一颗B+树的每一个结点都是一个数据页,一个数据页默认占用16KB,如果B+树结点很多,则占用的空间会很多.
- 时间代价:在增删改操作的时候,存储引擎需要对该表所关联的B+树进行维护更新,包括页分裂、页面回收等操作.以此保证B+树的每层结点都按照索引列值的大小从小到大的顺序排序组成双向链表.并且当查询查询语句在执行之前要先生成一个执行计划,在生成执行计划的时候,存储引擎要去需要去计算不同索引进行查询所需要的成本,从中选取一个成本最低的索引进行查询,如果索引过多会导致成本分析计算所花费的时间过多,影响查询的执行性能.
应用B+树索引
扫描区间和边界条件
我们称一个查询语句中根据条件而在索引B+树中划分的符合条件的区域叫做扫描区间,而那些条件叫做边界条件.
比如:
select * from t where id>=10 and id<=100;
其中[10,100]就是这条语句的扫描区间,而id>=10 and id<=100就是边界条件.
而如果是使用=符号充当条件或是IN之类的,判断某列等于某个常数的,我们称之为单点扫描区间.
如果有一个查询语句直接通过扫描聚簇索引B+树来从头到为进行遍历查询,然后比较查询条件,符合就将完整记录丢给客户端,否则就继续遍历,我们称这种查询为全表扫描.虽然这样是一种很笨的并且没有效率的查询方式,但是什么查询都可以这样使用.
只要索引列和常数使用=,<=>,IN,NOT IN, IS NULL,IS NOT NULL,>,<,>=,<=,BETWEEN,!=或者LIKE操作符连接起来,就会产生扫描区间
当我们执行一条查询语句的时候,需要找出所有可用的索引和它们所对应的扫描区间.
对于每一个搜索条件都可以生成合适的扫描区间的情况.
在使用某一个索引的时候,所有的条件都可以生成对应的扫描区间.
比如:
select * from t where key>=10 and key>=20;
这条语句的俩个搜索条件就各生成了一个[10,+∞)和[20,+∞)的扫描区间,而and是对着俩个扫描区间取交集,获取的最后的扫描区间就是[20,+∞)了,条件就是key>=20
如果我们将语句中的and换成or:
select * from t where key>=10 or key>=20;
这俩个搜索条件对应的搜索区间还是一样,但是操作符换成了or,对这俩个扫描区间取并集,结果就是[10,+∞)了.条件就是key>=10
这俩条语句都使用key对应的索引来对对应的扫描区间进行查询.
有的搜索条件不能生成合适的扫描区间的情况.
当使用某一个索引进行查询的时候,有些条件根据该索引无法生成扫描区间.
比如:
select * from t where key1>=10 and common_field = 10;
这里使用key1对应的二级索引来进行查询数据,可以发现该B+树中根本就没有记录common_field相关的值,或者根据该值排序.
在使用该索引进行查询的时候(因为根据common_field = 10条件无法缩减在对应索引树上的扫描范围),对应生成的扫描区间就为key1>=10,使用该索引查询需要先获取到符合key1>=10的记录,然后再去聚簇索引树上找到对应的记录,再去判断记录是否符合common_field = 10条件,然后再返回给客户端.
那如果我们操作符换成or呢?
select * from t where key1>=10 or common_field = 10;
如果这个时候还使用key1的索引来进行查询,common_field = 10在语句中没起然后作用,化简为:
select * from t where key1>=10 or common_field = true;
or操作符取并集,扫描区间就是[10,+∞)∪(-∞,+∞) = (-∞,+∞)
可以看到,如果使用该索引查询,该sql语句的条件并不能很好的缩减扫描区间,还需要扫描整颗树的叶子结点,并且二级索引还需要回表,这比直接进行全表扫描消耗的性能还要多.
这种情况下我们不会采取使用该索引进行查询的.
从复杂的搜索条件中找到扫描区间
有时候查询语句中会有多个搜索条件,并且搜索条件之间嵌套着许多的条件,包含不同列的条件.
这种时候我们不要先被复杂的条件吓到,可以尝试先简化条件,先查看条件中的列是否都是索引包含的列,然后去查看相应的条件是否可以简化,分析它们的扫描区间.
比如
SELECT * FROM t
WEHRE
(key1>'xyz' AND key2=748) OR
(key1 LIKE '%suf%' AND key1> 'zzz' AND (key2<8000 OR common_filed='abc'));
如果我们想使用key1索引来进行查询,获取他的扫描区间.
先简化条件中其他的列为TRUE,比如key2和common_field
SELECT * FROM t
WEHRE
(key1>'xyz' AND TRUE) OR
(key1 LIKE '%suf%' AND key1> 'zzz' AND (TRUE OR TRUE));
再省略TRUE:
SELECT * FROM t
WEHRE
(key1>'xyz' ) OR
(key1 LIKE '%suf%' AND key1> 'zzz');
再根据条件简化,key1 LIKE '%suf%'这个条件对应的是(-∞,+∞),与key1> 'zzz'对应的(zzz,+∞)取交集,为(zzz,+∞)
然后简化为:
SELECT * FROM t
WEHRE
(key1>'xyz') OR
(key1>'zzz');
而(key1>'xyz') OR (key1>'zzz');这个条件也是可以继续化简的,他们的并集为(xyz,+∞).
这个语句使用key1所对应的扫描区间就是(xyz,+∞)了.
使用联合索引执行查询时的扫描区间
使用联合索引进行查询的扫描区间有许多情况,因为联合索引的记录排序和多个条件的大小有关.
比如我们对表t建立了一个联合索引(part1,part2,part3)
在记录排序的时候,会先去判断part1列的值,如果相同则按照part2的值来排序,如果part2的值也相同,则按照part3的值来排序.都相同则按照主键列的值排序.
我们接下来根据对应的查询语句来判断联合索引的扫描区间(以下语句默认都使用part1,part2,part3建立的联合索引来查询):
SELECT * from t where part1 = 1;
因为该联合索引中先根据part1来进行排序,所以可以根据该查询条件来筛选扫描区间,为[1,1]
SELECT * from t where part1 = 1 and part2 = 2;
该语句中的条件有part1和part2,因为先根据part1来排列,所以可以根据part1=1得出[1,1]这个扫描区间,而后在part1值已经确认的前提下,根据part2来缩减扫描区间,最后得到的扫描区间是[(1,2),(1,2)]
SELECT * from t where part1 = 1 and part2 = 2 and part3=3;
该语句中的条件有part1和part2,因为先根据part1来排列,所以可以根据part1=1得出[1,1]这个扫描区间,而后在part1值已经确认的前提下,根据part2来缩减扫描区间,得到的扫描区间是[(1,2),(1,2)],
再根据part3进行缩减,获得的是[(1,2,3),(1,2,3)]这个扫描区间
SELECT * from t where part1>1;
因为该查询中索引中是先按照part1来进行排序的,所以根据part>1这个条件可以得知扫描区间为(1,+∞)
SELECT * from t where part1>1 and part2>10 and part2<20;
因为该查询中索引中是先按照part1来进行排序的,而后根据part2排序,所以我们可以获得扫描区间((1,10),(+∞,20))
先前我们的查询语句的条件中都有包含前面的索引列,比如part1,part1和part2,part1和part2和part3.但要是只出现part2呢?
SELECT * from t where part2=10;
当我们查询的时候发现,因为该索引先使用part1排序,才会使用part2,如果没有指定part1的值,part2在索引中是无序的,也就是可能一个part2=10的记录会在part2=11的后面(如果后者的part1值比前者小),并不相邻
所以如果只有part2条件,我们就需要进行全表扫描.所以不会使用该联合索引查询
SELECT * FROM t where part1=1 and part3=3;
虽然这个查询语句中,有part1条件,但是没有part2条件,在part3排序之前还有按照part2排序,我们无法通过part3=3这个条件来缩减扫描区间.
所以该语句的扫描区间为[1,1].
SELECT * FROM t where part1<10 and part2=2;
这个查询语句中虽然有part1和part2条件,数据先按照part1条件排序,获得扫描区间(-∞,10),而后看到part2条件,因为对于符合该扫描区间的记录来说,并不是直接按照part2条件来进行排序的,还是得先根据part1来排序,而后再根据part2排序.
所以该语句的扫描区间还是(-∞,10),part2条件无法影响到该扫描区间.
SELECT * FROM t where part1<=10 and part2=2;
和上一条语句比较,part1条件变成了<=,这个时候part2就能稍微影响到扫描区间了,当part1为10的时候,在这个区间的记录都是按照part2条件来排序.
所以扫描区间就为((-∞,-∞),[10,2]),当到达part1值为10而part2值为2之后的记录,如果不符合条件就会直接结束,而不需要将所有part1为10的记录扫描完.