关于mysql:MySQL知识整理

重新认识MySQL

客户端/服务器架构

MySQL的装置

bin目录下的可执行文件

bin目录下有好几种启动MySQL的可执行文件

启动MySQL服务器程序

UNIX里启动服务器程序

mysqld 能够启动一个服务器过程
mysqld_safe 启动脚本,间接调用mysqld,顺便启动一个监控线程,在服务器过程挂了的时候，帮忙重启。
mysql.server 启动脚本,间接调用mysql_safe,在调用mysql.server时在后边指定start参数就能够启动服务器程序。 mysql.server start
mysqld_multi 对每一个服务器过程的启动和进行进行监控
启动MySQL客户端程序
```
mysql -h主机名 -u用户名 -p明码
```
本上是一个过程间通信的过程
客户端与服务器连贯的过程
TCP/IP
如果应用TCP/IP网络来连贯到服务器过程,就须要在启动客户端程序时在-h前面应用IP地址来作为须要连贯的服务器过程所在主机的主机名。
服务器解决客户端的申请
启动选项和零碎变量
启动选项和配置文件
在命令行上应用选项
例如,启动时禁止应用TCP/IP网络通信。
```
mysql --skip-networking
```
指定启动选项时须要加上--前缀。另外如果选项名是由多个单词形成的,他们之间能够由短划线-连接起来。比方,skip-networking和skip_networking示意的含意是雷同的。
还有一个例子是启动时设置表的存储引擎
书上还强调了一下选项的长模式和短模式,例如--host -h主机名这样式儿的
配置文件应用选项
因为启动时命令行选项只在本次起作用,所以放在配置文件中永恒失效
配置文件门路
这块对于类unix的门路进行形容
配置文件内容
分组进行配置,各个启动命令也有能读取的组的限度。
注:如果同一个启动选项即呈现在命令行中,又呈现在配置文件中,那么以命令行的启动选项为准。
零碎变量
简介
在服务器程序运行过程中能对其造成影响的变量。
查看零碎变量
```
show variables like 'default_storage_engine'
```
设置零碎变量
通过启动选项设置
服务器运行过程中设置
设置不同作用范畴的零碎变量
书上提到了两个概念或者说是两个范畴,一个是global,另一个是session。
GLOBL:全局变量,影响服务器的整体操作
SESSION:会话变量,影响某个客户端连贯的操作
服务器启动时,会将每个全局变量初始化为其默认值。服务器会为每个客户端连贯保护一组会话变量,客户端的会话变量在连贯时应用相应全局变量的以后值初始化。
如果在设置零碎变量的语句中省略了作用范畴,默认的作用范畴就是SESSION(这次的客户端连贯)。
查看不同作用范畴的零碎变量
同样也是应用show varaibles like 'default_storage_engine'
状态变量
为了检测服务器程序的运行状况而存在的变量,由此而知,它们的值是由服务器程序来设置的。
show status like 'thread%'
状态变量也有global和session两个作用范畴。
字符集和比拟规定
字符集和比拟规定介绍
字符集简介
将二进制数据转换为字符所用的这套转换规则就是字符集
比拟规定简介
a-0x01 b-0x02
下面的字符如果以二进制数据本身的比拟规定来进行比拟的话,那么大多数时候是不合乎场景需要的。
'a'和'A'进行比拟时:
将两个大小写不同的字符全副转换为大写或者小写
再比拟这两个字符对应的二进制数据。
字符集和比拟规定的利用
各级别的字符集和比拟规定
客户端和服务器通信过程中应用的字符集
比拟规定的利用
总结
字符集
比拟规定
在MySQL中,一个字符集能够有若干种比拟规定,其中有一个默认的比拟规定,一个比拟规定必须对应一个字符集
查看MySQL反对的字符集和比拟规定语句:
show (charcter set|charset)[like 匹配的模式]
show collation [like 匹配的模式]
MySQL有四个级别的字符集和比拟规定
- 1 服务器级别
- 2 数据库级别
- 3 表级别
- 4 列级别
从发送申请到接管后果过程中产生的字符集转换
比拟规定的作用通常体现在比拟字符串大小的表达式和对某个字符串进行排序中
InnoDB记录存储构造
尽管书上曾经讲得非常艰深了,奈何我学的还是十分吃力,找了一篇博客,感觉还不错。
https://draveness.me/mysql-in...
简介
将数据划分为若干个页,以页作为磁盘和内存之间交互的工作单位,InnoDB中页的大小个别为16KB.
Compact格局
Redundant行格局
两者的异同
最大的不同就是compact在行记录的第一局部倒序寄存了一行数据中列的长度(Length),而Redundant中存的是每一列的偏移量(Offset)
行溢出数据
当InnoDB应用Compact或者Redundant格局存储极长的VARCHAR或者BLOB这类大对象时,咱们并不会间接将所有的内容都寄存在数据页节点中,而是将行数据中的前768个字节存储在数据页中,前面会通过偏移量指向溢出页。
当应用新的记录格局Compressed或者Dynamic时都会只在行记录中保留20个子节的指针,理论的数据都会寄存在溢出页面中。
InnoDB数据页构造
不同类型的页
为了不同的目标设计了许多种不同类型的页,比方寄存头部信息的页,寄存Insert Buffer信息的,寄存index信息的页,寄存undo日志信息的页等
数据页构造的疾速浏览
数据页代表的这块16KB大小的存储空间能够被划分多个局部,不同的局部有不同的性能
File Header
Page Header
Infimum+supremum 两个虚构的行记录最大记录和最小记录
User Records 实在存储行记录内容
Free Space
Page Directory 页中的某些记录的绝对地位
File Tailer
记录在页中的存储
不得不再提及一下记录的形成,上面展现了行记录的记录头格局
记录头的形成
预留位1
预留位2
delete_mask 标记该记录是否被删除
min_rec_mask B+树的每层非叶子结点的最小记录
n_owned 示意以后领有的记录数
heap_no 示意以后记录在记录堆中的地位
record_type 示意以后记录的类型
next_record 示意下一条记录的绝对地位
其中比拟重要的3个字段n_owned、heap_no和next_record书上讲的十分的具体
Page Directory(页目录)
Page Header(页面头部)
File Header(文件头部)
File Trailer(文件尾部)
总结
InnoDB为了不同的目标设计了不同类型的页,咱们把用于寄存记录的页叫做数据页
数据页大抵被分为7个局部:
File Header 示意页的一些通用信息,占固定的38字节
PageHeader 示意数据页专有的一些信息,占固定的56个字节
Infimum+Supremum 两个虚构的伪记录,别离示意页中的最小和最大记录,占26个子节
User Records 实在存储咱们插入的记录的局部,大小不固定
Free Space 页中尚未应用的局部
Page Diretory 页中某些记录的地位,对于数据页来说是每一组的最初一条记录
File Trailer 用于测验数据页是否残缺(备份到磁盘过程断电的测验)
记录会造成单链表
查找记录的过程(必须齐全了解)
提醒:Page Directory+二分+next_record
FileHeader会使得所有数据页组成一个双链表
校验和以及LSN值
B+树索引
通过后面的学习咱们晓得查问到记录是寄存在数据页中的,通过FileHeader和FileTrail将其连贯为一个双向链表。数据记录是next_record指针连接起来的链表。
为什么须要索引
暴力遍历:从最小记录开始遍历
二分法:依据数据页记录槽中的数据来疾速定位到(这是在数据页中疾速查找)
如何疾速找到是哪个数据页
这个时候就须要借助某种数据结构来帮忙咱们疾速定位到数据页
页决裂
先从简略的索引计划开始
为每个数据页建设一个目录项,这些目录项页保留在数据页中,目录项的格局为key-value,key用的是主键id,value保留的是页的地址(书上用的名词叫做页号),通过目录项能够疾速的拜访到页。
InnoDB中的索引计划
下面的计划有一个痛点就是数据页的大小只有16KB,如果用户记录很多的时候那么目录项也会有很多,这个时候会有多个存储目录项的数据页,又遇到了找哪个页的问题。
所以,InnoDB采纳了树结构的形式来保留目录项和用户记录(记录格局中有个属性叫做record_type),而这种数据结构也被称为B+树
聚簇索引
B+树自身就是一个聚簇索引,也就是说索引和数据都在树中,且叶子结点保留用户记录。
二级索引
用某个列作为目录项,叶子结点存储的的是主键,拿到主键之后还要去聚簇索引中查记录,这个过程称为回表
联结索引
多个列形成的二级索引
InnoDB的B+树索引的注意事项
根结点不挪动,挪动的是页中的数据
内节点(除去叶子节点)目录项记录的唯一性
一个页面起码存储2条记录
MyISAM中的索引计划简略介绍
索引与数据是离开,索引信息会存储在被称为索引文件的文件中。MyISAM会独自为表的主键创立一个索引,存储的是主键和行号。
创立和删除索引的语句
在建表时创立索引
```
create table index_demo{ c1 int, c2 int, c3 char(1), primary key(c1), index idx_c2_c3 (c2,c3)}
```
批改表来增加索引
```
alter table table_name add [index|key] 索引名(须要被索引的单个列或多个列)
```
删除时应用drop
学会应用B+树索引
索引的代价
B+树索引的实用条件
全值匹配
索引的每个列都用到了,这种状况被称为全值匹配
匹配右边的列
用到了联结索引中的右边的列
匹配前缀
```
SELECT * FROM person_info WHERE name LIKE 'As%';
```
能够看到依据前缀进行了匹配,在索引的排序也是这样的,在索引变动时某个列也会依据字母程序排序
匹配范畴值
```
select * from person_info where name >'Asa' and name<'Barlow';
```
找到name的值为Asa的记录
找到name的值为Barlow的记录
拿到两头的所有记录
准确匹配某一列并范畴匹配另一列
准确匹配到的列必须是最右边的列
用于排序
```
select * from person_info order by name,birthday,phone_number limit 10;
```
留神:排序的列必须要和联结索引的列的程序保持一致
用于分组
```
select name,birthday,phone_number,count(*) from person_info group by name,birthday,phone_number
```
不出预料的是索引也会失效,因为首先会找到最右边的列,而后再按联结索引前面的索引进行分组。
MySQL的数据目录
文件系统
操作系统用来治理磁盘的叫做文件系统
MySQL数据目录
装置目录与数据目录
装置目录的话一开始就说过这个文件,上面的bin目录有许多可执行文件;数据目录是用来存储运行过程中产生的数据
如何查看数据目录
show variables like 'datadir';
数据目录的构造
数据库在文件在文件系统中的示意
新建数据库时会在数据目录下产生一个子目录
表文件在文件系统中的示意
我在本人电脑上没有发现与书上统一的表构造文件和表数据文件,只有一个.ibd文件
表构造文件
表数据文件
零碎表空间
5.5.7-5.6.6之间的时候,数据会被存储到零碎表空间,我在电脑上试了一下并不是一个文件夹,同时在启动的时候也能够配置零碎表空间的地位和大小
独立表空间
MySQL5.6.6之后数据会被存储到独立表空间
咱们本人能够指定是否应用独立表空间还是零碎表空间
MyISAM是如何存储数据的
表构造、索引(在介绍索引时有提到过)和表数据三者离开存储
test.frm test.MYD test.MYI
试图在文件系统中的示意
虚构表,所以只用存储表构造文件
其余的文件
服务器过程文件:MySQL服务器会把本人的过程ID写入到一个文件中
服务器日志文件:各种日志文件,例如查问日志、谬误日志、二进制日志和redo日志
默认/主动生成的SSL和RSA证书和密钥文件:为了客户端和服务器端的平安通信而创立的一些文件
文件系统对数据库的影响
数据库收到文件系统的束缚
数据库名称和表名不能超过文件系统所容许的最大长度
表构造文件中会有特殊字符
文件长度受到文件系统最大长度限度
寄存页面的池子-InnoDB的表空间
这一章的内容可能难以了解,所以须要借助图片来更加直观的展现
温习无关页的常识
页面类型
书上大略讲了一下这部分,我听的多的是Undo日志页
页面通用局部
FileHeader的组成部分
书中对于各个组成部分形容
FIL_PAGE_OFFSET 页号
独立表空间
用书上的例子来说的话,表空间就是一个军团,段就是一个个师,而区既能够隶属于师,也能够间接作为独立团属于表空间
区(Extend)
区是由物理上的64个页组成的,具备物理存储构造
256个区形成一个组,独立表空间中第一组的前三个页是非凡的,它的内容是固定的
FSP_HDR:表空间整体属性和本组所有的区
IBUF_BITMAP:???
INODE:还是没了解这块儿
为什么引入区？
防止随机I/O
段
为什么引入段的概念?
书上的理由是为了辨别叶子结点和非叶子结点(在范畴扫描时起作用),为什么辨别了就快???
所以将索引的叶子结点与非叶子结点辨别(逻辑上的，至于物理上的必定还是通过链表连贯)
为了节俭空间同时就有了碎片区这一概念,直属于表空间,有些页用于段A,有些页用于段B。
尔后,为某个段调配存储空间的策略:
在刚开始向表中插入数据的时候,段是从某个碎片区以单个页面为单位来调配存储空间
当某个段曾经占用了32个碎片页的时候,就会以残缺的区为单位来调配存储单位
区的分类
闲暇的区(FREE):未应用的区
有残余空间的碎片区(FREE_FRAG):示意碎片区中还有可用的页面
没有残余空间的碎片区(FULL_FRAG):曾经齐全被应用
隶属于某个段的区(FSEG):之前提到过如果某个区被调配给某个段的状况
FREE、FREE_FLAG和FULL_FLAG都是直属于表空间的
XDES Entry()的构造,是这一章呈现的第一种构造(数据结构),其中有个组成部分就是ListNode,链表,用来将多个XDES Entry连接起来
与下面3种类型对应的就是FREE链表、FREE_FRAG链表和FULL_FRAG链表
下面提到过给段调配存储空间如果占用了超过32个碎片页的时候就会调配残缺的区,联合3种类型的链表,无非就是批改链表的next node指针操作了
哪些区属于哪个段
下面的问题针对于碎片区来说的,那么段中的页是什么状况?
每个段有三个链表:FREE、NOT_FULL和FULL链表
假如一个表有两个索引,共需保护15个链,段在数据量比拟大时插入数据的话,会先获取NOT_FULL链表的头节点,间接将数据插入这个头节点对应的区中即可,如果该区的空间应用完则会被移到FULL链表中。
链表基节点
如何找到某个XDES Entry,这个时候用的就是List Base Node
段的构造
ListNode链表
各类型页面详细情况
后面讲了那么多链表,那么它们的List Base Node在哪儿呢?
FSP_HDR类型
File Space Header
3种类型的List Base Node
Free Limit
Segment Header
用来辨别叶子结点和非叶子结点
实在表空间对应的文件大小
.ibd自扩大,随着表中的数据的增多,表空间对应的文件也逐步增大
零碎表空间
和独立表空间相似,然而比表空间在第一组中多了三个页
单表拜访办法
在此之前,书上有建了一个表,这是前提。
在执行一条SQL语句的时候有多种计划,MySQL Server有个查问优化器的模块,这个模块最终会将查问语句优化成一个所谓的执行打算,执行打算前面会具体解说,当初先有这么一个概念。
执行办法(access method)
MySQL的查问形式大抵有两种:
应用全表搜寻
应用索引进行查问,能够细分为很多品种
- 针对主键或惟一二级索引的等值查问(惟一)
- 针对一般二级索引的等值查问(范畴)
- 针对索引列的范畴查问(范畴)
- 间接扫描整个索引
针对下面的这几种细分的查问形式,MySQL将其称为拜访办法或者拜访类型。
const(常量级别)
指的就是针对主键或者惟一二级索引,执行几次二分查问就可失去数据,所以就是const
ref
```
select * from single_table where key1='abc'
```
搜寻条件为二级索引与常数值等值比拟,采纳二级索引来执行查问的拜访办法称为:ref。
ref_or_null
与下面的差异是将NULL的等值也包含了
range
下面说的都是等值查问,那么如果是范畴查问的状况该是什么样子呢？
索引列会匹配范畴内的值(等值匹配)
index
不须要回表,通过查问二级索引即可获取全副的数据
all
注意事项
二级索引+回表
明确range范畴
下面的拜访办法中range看起来是执行起来最简单的一个,上面就是MySQL的一些优化伎俩
所有搜寻条件都能够应用某个索引的状况
间接应用二级索引就能够确定所有查问的值
有的查问条件无奈应用索引的状况
```
select * from single_table where key2>100 and common_field='abc'
```
key2>100查问条件能够应用idx_key2来进行匹配,前面的查问条件则无奈应用索引。此时将前面的查问条件设置为true,间接依据后面的条件查问到记录。而后在回表的时候再依据common_field的条件进行过滤。
简单搜寻条件下找出范畴匹配的区间
简单条件其实也能够进行化简,可能用聚簇索引的话必定就会应用聚簇索引,缩小回表的操作。
索引合并
个别一次查问只会应用单个二级索引,然而非凡状况下也可能在一个查问中应用到多个二级索引
Intersection合并
AND关系
多个二级索引查问的后果取交加再回表
实用状况
二级索引等值匹配
主键列范畴匹配(主键列指定范畴,二级索引等值)
Union合并
OR关系
取并集
实用状况
二级索引等值匹配
主键列范畴匹配(主键列指定范畴,二级索引等值)
取交加的索引合并的搜寻条件
Sort-Union合并
二级索引范畴匹配(这种状况有点玄乎),对主键进行排序。
连贯的原理
连贯简介
连贯的实质
连贯过程
驱动表
笛卡尔乘积在多表时的排列组合后果指数级增长,所以就有了左连贯和右连贯。首先须要明确的是驱动表的概念,就是最先满足查问的条件的表,像左连贯就是右边的表就是驱动表。
被驱动表
在驱动表中查完数据之后,须要到被驱动表中去找满足连贯条件和其余条件的数据,这类表被称为被驱动表。
内连贯和外连贯
区别
对于内连贯来说,驱动表在被驱动表中找不到匹配的记录,那么就不会退出到后果集中。
对于外连贯来说,驱动表的记录即便在被驱动表中没有匹配的记录,也依然须要退出到后果集中
where过滤条件与on过滤条件
对于外连贯来说,on会将不匹配退出,而where都不会退出
连贯的原理
嵌套循环连贯
驱动表只拜访一次,被驱动表拜访的次数取决于对驱动表执行单表查问后的后果集中的记录条数,这种形式称之为嵌套循环连贯(Nested-Loop Join)
应用索引放慢查问速度
从驱动表中到被驱动表中查数据的时候相当于一次条件查问,那么这个时候就能够应用索引放慢查问的速度。
基于块的循环嵌套连贯
总结:
1.内连贯和外连贯
2.MySQL的设计者总想用更少的资源和更快的访问速度来晋升查问的效率,把握住这个就是这几章的思维
MySQL基于老本的优化
什么是老本
CPU老本和I/O老本?
从磁盘加载到到内存过程的损耗的工夫称为I/O老本
读取及检测后果是否满足对应的搜寻条件、对后果集进行排序等操作称为CPU老本;
单表查问的老本
基于老本的优化步骤
在一条单表查问语句真正执行之前,MySQL的查问优化器会找出所有可能应用的计划,而后抉择一个老本最低的计划,这个老本最低的计划就是所谓的执行打算,过程如下
1.依据搜寻条件,找出所有可能应用的索引
2.计算全表扫描的代价
3.计算应用不同索引执行查问的代价
4.比照各种执行计划的代价,找出老本最低的那一个
show index
Non_unique 索引列的值是否惟一
Seq_in_index 索引列在索引中的地位
Collation 索引列中的值是按何种排序形式寄存的,值为A时代表升序寄存
Cardinality 索引列不反复值的数量
Sub_part 对于存储字符串或者字节串来说,只想对前n个字符或字节建设索引
连贯查问的老本
扇出:从驱动表进行查问后的记录条数称为驱动表的扇出,这个概念有什么用呢,必定跟被驱动表有关系呗
调节老本常数
server层和存储引擎层,在server层进行**连贯治理、查问缓存、语法解析和查问优化等操作,在存储引擎层执行具体的数据存取操作。
1.mysql.server_cost表
2.mysql.engine_server表
InnoDB的统计数据如何收集
上一章中有个index dive的概念,就是从索引的B+树中查问满足条件的索引记录的过程。然而这种状况在某些状况下(书上举的例子是在in中的参数十分多)就会十分慢,还有一种计划就是靠MySQL的统计数据,然而误差十分大。
两种不同的统计数据存储形式
1.永久性的统计数据
2.非永久性的统计数据
InnoDB是以表为单位来收集和存储统计数据的。咱们能够在创立和批改表的时候通过指定
STATS_PERSISTENT属性来指明表的统计数据存储形式:
alter table 表名 Engine=InnoDB,STATS_PERSISTENT=0
基于磁盘的永久性统计数据
实际上将这些统计数据存储到了两个表里:
innodb_table_stasts 每一条记录对应着一个表的统计数据
innobb_index_stats 每一条记录对应着一个索引的统计项的统计数据
MySQL基于规定的优化
条件化简
去除多余的括号
常量传递
等值传递
移除没用的条件
表达式计算
留神:表达式中是常量时才会失效
HAVING子句和WHERE子句的合并
如果查问语句中没有呈现诸如sum、max等等的汇集函数时,那么优化器就会将where和having子句进行合并
常量表检测
针对主键索引和惟一二级索引的等值匹配查问时失效,如下
```
select * from table1 inner join table2 on table1.column1=table2.column2where table1.primary_key=1;
```
将会做如下优化
```
select table1表记录的各个字段的常量值,table2.* from table1 inner join table2 on table1表column1列的常量值=table2.column2;
```
外连贯打消
首先外连贯和内连贯和区别是什么不必多说。通过where条件不容许被驱动表的列为空,这样就和内连贯查问的后果统一。
子查问优化
子查问语法
子查问类型
按返回的后果集区分子查问
标量子查问
返回繁多值的子查问称之为标量子查问,一行一列的数据
行子查问
一条记录
列子查问
表子查问
多条记录
按与外层查问关系来区分子查问
不相干子查问
子查问独自能够出后果
相干子查问
子查问在布尔表达式中应用
子查问语法注意事项
子查问在MySQL中如何执行
讲在后面,书上先讲了猜测的执行的实在样子
如果是不相干子查问的话,那么就是先执行子查问中的语句，将后果集作为外层查问可能将要用到的数据集。
如果是相干查问，那么先从外层查问中拿出一条数据，用对应的值和子查问的列做关联。
事实也正是如此,然而MySQL会在此基础上进行优化
IN子查问优化
不间接将不相干子查问的后果集当作外层查问的参数,而是将该后果集写入到一个长期表里。
该长期表的列就是子查问后果集中的列。
写入长期表的记录会被去重。
如果数据不是大的离谱,那么就会建设基于内存的应用Memory存储引擎的长期表,而且会为该表建设哈希索引;如果过大就会建设基于磁盘的存储引擎。
下面这种计划被称之为物化表,能够将物化表转换为连贯
MySQL设计者又提出半连贯(semi-join),这是MySQL外部的一种查问形式,上面是书上对于半连贯的详细描述
对于s1表的某条记录来说,咱们只关怀在s2表中是否存在与之匹配的记录，而不关怀具体有多少条记录与之匹配,最终的后果集中只保留s1表的记录。
有5种实现semi-join的策略
总结
如果in子查问合乎转换semi-join的条件,查问优化器会优先把该子查问为semi-join,而后从5种执行策略中抉择老本更低的一种。
如果in子查问不合乎转换为semi-join的条件,那么查问优化器会从下边两种策略抉择一种老本更低的形式
先将子查问物化之后再查问
执行in to exists
[NOT] EXISTS子查问的执行
如果不相干,会用true或者false来替换掉exists条件;
如果相干,就只能用一开始的形式来执行，然而会用索引放慢
对于派生表的优化
将派生表物化
将派生表和外层的表合并,就是将查问重写为没有派生表的模式
Explain
执行打算输入各列详解
table 表名
id 每呈现一个select就会调配一个id,在连贯查问的执行打算中,每个表都会对应一条记录,这些记录的id的值是雷同的,呈现子在前边的表示意驱动表，后边的表示意被驱动表
select_type 在整个大查问中表演了什么角色
partitions
type 对应的是之前的拜访办法是哪种
possible_keys和key 可能用到的索引
key_len 当优化器决定应用某个索引进行查问时,该索引记录的最大长度
ref 当应用索引列等值匹配的条件去执行查问时,ref列展现的就是与索引列作等值匹配的是什么,比方一个常数或者某个列。
rows 当全表扫描的形式对某个表执行查问,预计要扫描的索引记录行数。
filtered 计算扇出(驱动表中的记录条数)时的一个策略,通过百分比来预测。
extra 提供一些额定信息
Json格局的执行打算
应用时只须要在原来的语句中退出FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.co
Extend EXPLAIN
执行打算的扩大信息
Optimizer trace
explain只提供了局部信息,如果想要查看更加具体的优化过程就得借助optimizer_ trace表
查看变量
show variables like 'optimizer_trace';
enable默认是off,所以须要关上
set optimizer_trace="enabled=on";
//执行本人的查问语句
select * from s1 where
key1 > 'z' and
key2 > 1000000 and
key3 in ('a','b','c') and
common_field='abc';
而后查看optimizer表来察看优化过程
select * from information_schema.OPTIMIZER_TRACE\G .
InnoDB的Buffer Pool
缓存的重要性
即便拜访一条记录也要把整个页的数据加载到内存中,读写访问之后不会立刻开释内存。
InnoDB的Buffer Pool概览
Buffer Pool介绍
MySQL服务器在启动的时候向操作系统申请了一片间断的内存,这块内存就叫做Buffer Pool(缓冲池)。
Buffer Pool外部组成
InnoDB设计者为每一个缓存页都创立了管制信息,书中将每一个页对应的管制信息占用的内存称为一个管制块。
管制块和缓存页是一一对应的,它们都被寄存到Buffer Pool中,其中管制快被寄存到后面,缓存页被放在后边

free链表的治理

用来记录Buffer Pool中哪些缓存页是可用的,能够将所有的闲暇页对应的管制块作为一个节点放到一个链表中。
同时有一个基节点用来统计free链表的数据,外面蕴含着头节点地址,尾节点地址和链表中的节点数量等信息。

缓存页的哈希解决

哈希key-value
key就是表空间号+页号,value就是对应的缓存页;

flush链表的治理

将批改的缓存页(脏页)的对应的管制块信息所形成的链表,和free链表类似。

LRU链表的治理

缓存不够用

缓存页太多,内存必定是不够用的

简略的LRU链表

LRU:Least Recently Used

如果该页不在Buffer Pool中,把该页从磁盘加载到Buffer Pool的缓存页时,就把该缓存页对应的管制块作为节点塞到链表的头部
如果该页曾经缓存在Buffer Pool中,则将页对应的管制块挪动到LRU链表的头部。
划分区域的LRU链表
LRU面临的问题:
预读
可能用到的页面,加载到Buffer Pool中。
线性预读
间断(程序拜访)读取某个区的指定数量(零碎变量)的页面,就会触发异步读取下一个区的全副页面到Buffer Pool中,异步不会影响当前工作线程的失常运行。
随机预读
如果缓存了某个区的13个间断的页面,会读取本区中的所有其余页面到Buffer Pool中,是否开启可由零碎变量配置。
全表扫描
下面两种状况都会导致Buffer Pool中大量的页被替换掉
解决方案
将LRU链表分为两局部
Yong区域和Old区域
innodb_old_blocks_pct零碎变量来配置old区域在链表中所占比例。
针对预读的优化
当磁盘上的某个页面在首次加载到Buffer Pool中的某个缓存页时,该缓存页对应的管制块会被放到old区域的头部
针对全表扫描的优化
全表扫描的特点是执行频率特地低
对某个处在old区域内第一次拜访会有一个拜访工夫,前面再次拜访时的工夫与首次拜访工夫的差值如果超过某个值就会将其挪动至yong区域首部。
更进一步优化
yong区域中也不是每次拜访都会将对应的管制块挪动到首部,而是后3/4处的管制块才会挪动至首部。
刷新脏页到磁盘
从LRU链表的冷数据(old)中刷新一部分页面到磁盘。
从flush链表中刷新一部分页面到磁盘。
多个Buffer Pool实例
多线程状况下Buffer Pool的各种链表须要加锁,繁多的Buffer Pool可能会影响申请的处理速度。
innodb_buffer_pool_chunk_size
Buffer Pool的实例是由若干个chunk(一片间断的内存空间)组成,innodb_buffer_pool_size的值只能在服务器启动的时候指定,在服务器运行过程中不能够批改
配置Buffer Pool注意事项
innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的
倍数(这次要是想保障每一个 Buffer Pool 实例中蕴含的 chunk 数量雷同)
查看Buffer Pool的状态信息
show engine innodb status\G;
常见的几个值
Total memory allocated 向操作系统申请的间断内存空间大小,包含全副管制块、缓存页和碎片的大小
Buffer pool size 代表Buffer pool能够包容多少缓存页
Free buffers 代表LRU链表中页的数量
总结
磁盘太慢,内存作为缓存很有必要
Buffer Pool实质上是InnoDB向操作系统申请的一段间断的内存空间
Buffer Pool向操作系统申请的间断内存由管制块和缓存页组成,每个管制块和缓存页都是一一对应的,在填充足够多的管制块和缓存页的组合后,Buffer Pool残余的空间可能不够填充一组管制块和缓存页,这部分空间不能被应用,也被称为碎片。
InnoDB应用了许多链表来治理Buffer Pool。LRU、Free、Flush
free链表中每一个节点都代表一个闲暇的缓存页
为了疾速定位到某个页是否被加载到Buffer Pool,应用表空间号+页号作为key,缓存页作为value,建设哈希表
Buffer Pool中被批改的页称为脏页,不是立刻刷新，而是退出到flush链表
yong区域与old区域,能够通过innodb_old_blocks_pct来调节old区域所占的比例。
通过指定innodb_buffer_pool_instance来管制Buffer Pool实例的个数
5.7.5之后,能够在服务器运行过程中调整Buffer Pool的大小。
查看Buffer Pool状态命令:show engine innodb status\G;
事务
ACID
原子性
隔离性
一致性合乎事实世界的束缚(书上是这么说的,我还没有看其余的)
持久性
事务的概念
一组数据库操作,满足下面4个个性。
事务的语法
开启事务
两种形式
begin
start transaction
提交事务
commit
手动停止事务
rollback
反对事务的存储引擎
InnoDB和NDB存储引擎
主动提交
SHOW VARIABLES LIKE 'autocommit';
默认值是ON,每一条语句都是独立的一个事务。
显式的应用start transaction或者begin开启一个事务
把零碎变量autocommit的值设置为OFF
隐式提交
定义或者批改数据库对象的数据定义语言(Data definition language)
隐式应用或批改mysql数据库中的表
还有其余,这里只有晓得某些语句或者操作会触发事务的隐式提交
保留点
savepoint 保留点名称
在事务中增加这条语句即可
redo日志
当时阐明
因为一条记录刷新一个页面到磁盘有点过于节约,所以将这个批改操作记录一下。
例如,更新操作
将第0号表空间的100号页面的偏移量为1000处的值更新为2。这就是一条残缺的记录,称为redo log。
redo日志格局

各个局部释义
type 日志类型
space ID 表空间ID
page number 页号
该条日志的具体内容

下面这些只是所有redo日志共有的,上面还有一些不同类型redo日志特有的属性。

简略的redo日志类型

说在后面,InnoDB会保护一个全局变量,每当向某个蕴含暗藏的row_id列(没有主键或者惟一索引的时候会有这个列)的表中插入一条记录时,就会把该变量的值当作新新纪录的row_id列的值。
每当这个变量的值为256的倍数时,就会把该变量的值刷新到零碎表空间的页号为7的页面中一个称之为Max Row ID的属性处。
redo log的类型

MLOG_1BYTE:示意在页面的某个偏移量处写入1个字节的redo日志。
MLOG_2BYTE:示意在页面的某个偏移量处写入2个字节的redo日志
前面还有4个字节和8个字节的redo日志。
不出意外的话必定还有多个字节的,
MLOG_WRITE_STRING 示意在页面的某个偏移量处写入一串数据。

属性

offset,下面提到了那么屡次偏移量,那么redo日志中必定还有偏移量的属性。
1字节2字节4字节的还有一个属性就是len,示意具体数据占用的字节数。
总而言之,redo日志会把事务在执行过程中对数据库所做的批改都记录下来,在之后零碎解体重启后能够把事务所做的任何批改都复原进去。
Mini-Transaction
以组的模式写入redo日志
页面更改产生的redo相应的日志被记录下来,这些redo日志被InnoDB设计者划分成为了若干个不可分割的组
更新Max Row ID属性时产生的redo日志时不可分割的
向聚簇索引对应的B+树的页面中插入一条记录时产生的redo日志是不可分割的
向某个二级索引对应的B+树的页面中插入一条记录时产生的redo日志是不可分割的
还有其余的一些对页面的拜访操作时产生的redo日志是不可分割的
下面的不可分割的意思是插入的时候可能会呈现页决裂(乐观插入)的状况,所以一个操作会产生多条记录。
通过MLOG_MULTI_REC_END标记来代表以后redo日志是否为一组redo日志的结尾。
redo日志的写入过程
redo log block

是不是有点像页的构造,只不过没有那么简单
redo日志缓冲区
写入redo日志时也不能间接写到磁盘上,实际上服务启动时就向操作系统申请了一大片称之为redo log bufferr的间断内存空间。这片内存空间被划分为若干个间断的redo log block,能够通过启动参数innodb_log_buffer_size来指定log buffer的大小。
redo日志写入到log buffer
写入的第一个问题就是写入到哪个block的哪个偏移量处
InnoDB提供了一个称之为buf_free的全局变量,该变量指明后续写入的redo日志应该写入到log buffer中的哪个地位
不同事务可能是并发执行的,所以T1、T2之间的mtr可能是交替执行的。mtr产生的log buffer交替写入到log buffer
redo日志文件
刷盘机会
log buffer空间有余
事务提交时
后盾线程刷
失常敞开服务器时
check point
redo日志文件组
我的数据库中没有书上的两个文件ib_logfile0和ib_logfile1
innodb_log_group_home_dir
该参数指定了redo日志文件所在的目录,默认值就是以后的数据目录
innodb_log_file_size
指定每个redo日志文件的大小
innodb_log_files_in_group
该参数指定redo日志文件的个数,,默认值为2,最大值为1000
redo日志文件格式
前2048个字节,即前4个block用来存储一些治理信息
从2048字节往后用来存储log buffer中的block镜像
Log Sequeue Number日志序列号】
LSN从8704开始,header+trailer+body的日志序列号会成为新的值。
flushed_to_disk_lsn
buf_next_to_write标记以后log buffer中曾经有哪些日志被刷新到了磁盘中,书上还提到了flush_to_disk_lsn示意刷新到磁盘的lsn,这个才是重点。
lsn值和redo日志偏移量的关系
lsn从8704开始,redo日志偏移量从body开始(跳过header2048)
flush链表中的LSN
说在后面,mtr完结时还有一件十分重要的事件要做,就是把在mtr执行过程中可能批改过的页面退出到Buffer Pool的flush链表。
管制块有两个属性oldest_modification(第一次批改该页面mtr开始的lsn值写入这个属性)和newest_modification(每批改一次页面,都会将批改该页面的mtr完结时对应的lsn值写入这个属性) 。
checkpoint
redo日志只是为了零碎解体后复原用的,如果对应的脏页曾经刷新到了磁盘,那么redo日志也用不到了。
比如说页a被刷新到了磁盘,mtr_1生成的redo日志就能够被笼罩了,所以咱们能够进行一个减少checkpoint_lsn的操作,这个过程被称为一次checkpoint。
flush链表尾节点,oldest_modification赋值给checkpoint_lsn(之前的都能够删掉);
将checkpoint_lsn和对应的redo日志文件组偏移量以及此次checkpoint的编号写到日志文件的治理信息。
每实现一次checkpoint就会有个全局变量checkpoint_no的变量值加1,同时会有一个checkpoint_offset来示意在日志文件组中的偏移量。
存储到check_point1中还是2中取决于checkpoint_no的值。
批量从flush链表中刷出脏页
查看零碎中的各种LSN值
解体复原
确定复原的终点
比拟checkpoint1和checkpoint2的checkpoint_no,哪个更大就阐明哪个更加靠近，作为终点。
确定复原的起点
应用页中的某个属性来示意当前页中应用了多少字节的空间。
怎么复原
应用哈希表
SpaceID+PageNumber
跳过曾经刷新到磁盘的
File Header中有个属性FIlE_PAGE_LSN,就是new_modification的值。
undo日志
事务回滚的需要
懂的都懂
把回滚时所需的货色都记录下来
事务id
调配机会
事务分为只读事务和读写事务
只读事务能够批改长期表而读写事务能够批改一般表,这个时候会调配事务id
如何生成
和row_id相似,全局变量,每次加1,当这个值是256的倍数时就会跟新到零碎表空间的页号为5的一个称之为Max Trx ID的属性处
trx_id暗藏列
和之前的格局串起来了
roll_point指向undo日志版本链
undo日志格局
undo日志在操作之前生成
undo_no来保障唯一性,寄存在零碎表空间中
insert、delete和update对应的日志格局不雷同
事务提交之后就无奈复原了
书上的例子十分具体
insert
delete
阶段一 delete_mark
阶段二退出垃圾链表
update
不更新主键
更新列所占用的空间不变
就地更新
更新列占用空间变动
删除掉,这里说的删除是间接退出垃圾链表
更新主键
如果更新主键的话,那么就像delete一样做delete_mark操作(MVCC,为了其余事务可能失常拜访)
依据更新后各列的值创立一条新纪录,并将其插入到聚簇索引中
通用链表构造
在写入undo日志的时候会应用到许多链表构造,这些列表的节点有通用的属性
Pre Node PageNumber和Pre Node offset指向上一个节点
Next Node PageNumber和Next Node offset指向下一个节点
除此之外,还会有一个基节点,这个构造外面多了个count,示意以后链表的个数
FILE_PAGE_UNDO_LOG(Undo页面)
因为insert日志和update日志的差异,所以这两种日志记录也是别离存储在不同类型的页中。
那么页面中还会有什么属性呢?(猜想redo页面是否也有这些属性只不过我之前没有留神过)
PAGE_TYPE:页面类型
PAGE_START:页面的什么中央开始存储日志记录,集体感觉是通过offset(页面偏移量)来示意的
PAGE_FREE:可用的日志记录的偏移量
Undo页面链表
单事务Undo页面链表
按需创立,只有真正用到的时候才会创立
多事务Undo页面链表
InnoDB设计者规定,有4种链表类型
一般表的Insert类型的链表
一般表的Update类型的链表
长期表的Insert类型的链表
长期表的Update类型的链表
那么头节点的地位必定会有对于整个链表的属性,例如,Segment ID段(每个段对应一个INODE ENTRY构造,通过Segment Header(表空间的时候有提到过)来确定)的id等等。这些属性寄存在undo页链表的首个节点。
Undo日志的写入过程
Unod Log Segment Header
页链表首个节点中Segment Header属性,所有的页面在申请的时候都是从这个段中去申请的
Undo Log Header
每一次minitranscation都会产生一组undo日志,这些undo日志退出到Undo链表中的时候须要有中央来记录一下这个组的属性,Undo Log Header的作用就在于此。
几个重要的属性
trx_undo_trx_id: 事务id
trx_undo_trx_no: 事务提交时的顺序号
其余一些属性就是常见的例如上一组下一组的偏移量
重用undo日志
为了实现并发执行(提高效率),会为每个事务都创立链表,如此一来便会产生许多额定数据,节约空间不说,保护起来也相当麻烦,所以有了重用这个妙手
页链表中只有一个页的链表能重用
Insert类型在事务提交之后就能够重用
Update类型的会在原来的undo页面前面持续加,这里说的是事务提交之后,原来的记录不能删除是为了保障MVCC
回滚段-RollBack Segment
为了治理Undo页链表(数量过多),须要有个中央来集中统一的展现这些信息,于是就有了这么一个概念。
RollBack Segment Header页面
undoslot指向页链表的首个页节点
RollBack Segment的分类
通过下面的形容能够晓得其实回滚段就是这些RollBack Segment Header页,一个页面只有1024个undoslot,所以会有很多的这种页面
分为零碎、一般、长期表(长期表不须要redo日志,然而会有undo日志)
为事务调配Undo日志的具体过程
首先为事务调配一个RollBackHeader页
查看RollBack页中是否有可重用的页链表(放在Insert Undo Cache和Update Unddo Cache中)
- 如果有的话,那么就将undoslot调配给事务
- 如果没有的话IS_FILL,那么就新创建一个段,将段的First Page Number给事务
  回滚段配置
  配置回滚段的数量
  配置回滚段的空间
  MVCC
  遇到的问题
  脏写: A事务批改了a字段,B事务rollback,A事务的操作数据不见了,这就是脏写
  脏读: A事务读到了B事务未提交的数据
  不可反复读: A事务针对a执行了几次查问操作,B事务批改了a几次,A事务每次的查问后果都不一样
  幻读: A事务同一个查问条件查问了几次,B事务新增了记录,后果每次都能查到新的记录
  4种隔离级别
  有人设计一套SQL规范
  read uncommited
  read commited
  repeated read
  serilizable
  MVCC版本链
  每次对于数据的操作都会有一条undo日志,记录中用roll_pointer指向最新的一条undo日志
  
  从下面的图中能看进去,每条undo日志中也有roll_pointer这个属性,指向上一条的undo日志。
  上面看一下针对各个隔离级别MySQL是如何实现的
  ReadUncommited
  间接读取最新的记录就能够
  Read Committed
  首先须要理解一个概念ReadView,这是版本链中一个十分重要的数据结构,有几个重要的属性(可能名称不肯定精确):
m_ids
生成ReadView时沉闷的事务id列表
min_trx_id
沉闷的最小事务id
max_trx_id
再次生成ReadView时应该调配的事务id
creator_trx_id
生成以后ReadView的trxid
比拟规定
如果拜访的trx_id和creator_id相等,阐明拜访的就是以后版本,能够拜访
如果trx_id小于min_trxid,阐明之前曾经提交,能够拜访
如果trx_id大于max_traxid,阐明以后记录版本在创立事务之后,能够拜访
如果trx_id在[min,max]之间,trx_id是否在m_ids列表中,如果在的话阐明事务trx_id还是沉闷的,没有提交,所以不能被反诘
Select之前生成一个ReadView
trx_id只有在insert,update,delete的时候才会被调配。
Repeated Read
只有第一次查问的时候生成一个ReadView
Serilizalbe
采纳锁的形式
锁
下面提到了Serilizable这个隔离级别,就不得不提到锁了
几个概念
锁的构造 trx_id+is_waiting
不加锁
不在内存中生成锁
获取锁胜利
为记录在内存中创立一个锁,is_waiting为true
获取锁失败
为记录在内存中创立一个锁,is_waiting为false
如何解决下面事务中存在的问题
通过MVCC读和锁写
读写都加锁
一致性读
指的就是不加锁的读的形式
共享锁和排他锁
共享锁S-shared
拍他锁X-exclude
读操作
- 对读记录加S锁
- 对读记录加X锁
写操作
转换成为对记录加X锁的读操作
多粒度锁
提出了表锁和行锁的概念,当然这是通用的概念,上面会将MySQL如何实现
有个问题就是加表X锁的时候要求行没有锁,所以InnoDB就提出了IS和IX锁,即意向锁。咱们只须要晓得事务执行对行加锁前肯定会有表锁(意向锁)。
细说MySQL的表锁和行锁
不同的存储引擎采纳的锁的实现计划是不一样的,上面来看一下InnoDB的锁设计
表锁
S锁
X锁
下面的两个锁十分鸡肋,基本上不必到
IS锁和IX锁
下面有提到过,用来判断以后表的记录是否上锁
Auto-Inic锁
新增的时候会设置自增,所以须要保障id的唯一性,也就是说当插入一条记录的时候会将记录加锁从而阻塞别的线程的更改。
行锁
Record Locks
书上给的名称是正经记录锁,我的了解是针对某条记录(Record)的锁,所以会有S锁和X锁
Gap Locks
不容许在以后记录和上一条记录的区间内插入
这个锁的提出是为了避免幻影读(从根上解决,不让你插入)
Next-Key Locks
相当于Record Locks+Gap Locks的性能
Insert intention Locks
插入意向锁,说的是事务在插入记录时会学生成一个插入意向锁,如果记录此时有之前的Gap锁或者Next-key锁,那么is_waiting=true
隐式锁
新增一条记录之后,其余事务能够对其做批改,这样会产生脏读和脏写的问题
这个时候暗藏的trx_id事物id就施展了作用
- 批改的是聚簇索引的记录时(事务B批改),事务A新增,那么B事务会查看记录的trx_id,如果属于沉闷事务就会为记录加事务A的锁,而后再加本人的锁并处于期待状态
- 批改的二级索引, 会应用页外面的某个属性来做判断查看trx_id是否沉闷,否则就只能回表执行下面的步骤
InnoDB的锁的内存构造
1条记录针对一个事务生成一个锁构造,这样有点过于节约,所以锁是能够重用的

参考
《MySQL是怎么的运行的》

重新认识MySQL

客户端/服务器架构

MySQL的装置

bin目录下的可执行文件

启动MySQL服务器程序

UNIX里启动服务器程序

启动MySQL客户端程序

客户端与服务器连贯的过程

TCP/IP

服务器解决客户端的申请

启动选项和零碎变量

启动选项和配置文件

在命令行上应用选项

配置文件应用选项

配置文件门路

配置文件内容

零碎变量

简介

查看零碎变量

设置零碎变量

通过启动选项设置

服务器运行过程中设置

设置不同作用范畴的零碎变量

查看不同作用范畴的零碎变量

状态变量

字符集和比拟规定

字符集和比拟规定介绍

字符集简介

比拟规定简介

字符集和比拟规定的利用

各级别的字符集和比拟规定

客户端和服务器通信过程中应用的字符集

比拟规定的利用

总结

4 列级别

InnoDB记录存储构造

简介

Compact格局

Redundant行格局

两者的异同

行溢出数据

InnoDB数据页构造

不同类型的页

数据页构造的疾速浏览

记录在页中的存储

记录头的形成

Page Directory(页目录)

Page Header(页面头部)

File Header(文件头部)

File Trailer(文件尾部)

总结

B+树索引

为什么须要索引

先从简略的索引计划开始

InnoDB中的索引计划

聚簇索引

二级索引

联结索引

InnoDB的B+树索引的注意事项

MyISAM中的索引计划简略介绍

创立和删除索引的语句

学会应用B+树索引

索引的代价

B+树索引的实用条件

全值匹配

匹配右边的列

匹配前缀

匹配范畴值

准确匹配某一列并范畴匹配另一列

用于排序

用于分组

MySQL的数据目录

文件系统

MySQL数据目录

装置目录与数据目录

如何查看数据目录

数据目录的构造

数据库在文件在文件系统中的示意

表文件在文件系统中的示意

表构造文件