一、MySQL组成结构
MySQL结构图:
1、MySQL常用引擎有哪些
常用引擎有InnoDB、MyISAM和memory
扩展:memory浅谈
memory是MySQL中一类非常特殊的存储引擎,与MySQL中其它的存储引擎不同的是:Memory存储引擎包含存储在内存中的内容的专用表。由于数据容易受到崩溃,硬件问题或断电的影响,因此只能将这些表用作临时工作区或从其它表中提取数据的只读缓存。
Memory引擎的典型用例涉及以下特征: 1.涉及瞬态非关键数据的操作,例如会话管理或缓存.当MySQL服务器暂停或重新启动时,memory表中的数据将丢失。
2.内存存储,可以实现快速访问和低延迟。数据卷可以完成适应内存,而不导致操作系统交换虚拟内存页。
3.只读或读取是主要数据访问模式(有限更新).
每个基于memory存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm类型。该文件中只存储表的结构。而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。值得注意的是,服务器需要足够的内存来维持memory存储引擎的表的使用。如果不需要了,可以释放内存,甚至删除不需要的表。
MEMORY默认使用哈希索引。速度比使用B型树索引快。如果想用B型树索引，可以在创建索引时指定。
MEMORY性能受到处理更新时单线程执行和表锁开销导致的争用的限制。这会限制负载增加时的可伸缩性，特别是对于包含写的混合语句。尽管对MEMORY表进行了内存处理，但它们不一定比繁忙服务器上的InnoDB表，通用查询或读/写工作负载更快。特别是，执行更新所涉及的表锁定会减慢来自多个会话的MEMORY表的并发使用。根据在MEMORY表上执行的查询类型，您可以创建索引作为默认hash数据结构（用于基于唯一键查找单个值）或通用B树数据结构（适用于所有类型涉及等于，不等式或范围运算符（例如小于或大于）的查询）。以上部分说明了创建这两种索引的语法。常见的性能问题是在工作负载中使用默认哈希索引，但是B树索引更有效。
Memory存储引擎将数据存在内存,为了提高数据的访问速度,每一个表实际上和一个磁盘文件关联。这在某种程度上决定了memory存储引擎的特性:
(1)、支持的数据类型有限制,比如:不支持text和blob类型,对于字符串类型的数据,只支持固定长度的行,varchar会被自动存储为char类型;
(2)、支持锁粒度为表级锁,所以在访问量比较大时,表级锁会成为memory存储引擎的瓶颈;
(3)、由于数据是存放在内存中,一旦服务器出现故障,数据都会丢失;
(4)、查询的时候,如果有用到临时表,而且临时表中有blob,text类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低;
(5)、默认使用hash索引;
(6)、如果一个内部表很大,会转化为磁盘表。
memory浅谈来自: blog.csdn.net/qf2019/arti…
2、InnoDB与MyISAM区别有哪些
(1)、它们的表结构都是存储在.frm文件中,InnoDB的数据和索引都是存储在.ibd文件中,而MyISAM是索引存储在.myi文件中,数据存储在.myd文件中,概况的来说:MyISAM的索引和数据不是存储在同一个文件中,即非聚簇索引,InnoDB与之相反;。
(2)、InnoDB支持行锁和表锁,而MyISAM只支持表锁;
(3)、InnoDB支持事务,MyISAM不支持事务; MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
(4)、它们都采用B+Tree作为索引的数据结构;
(5)、InnoDB支持外键,而MyISAM不支持外键;
(6)、InnoDB查询具体行数需要进行全表扫描,而MyISAM用一个变量存储了整个表的行。 即在执行:SELECT COUNT(1) FROM TABLE时,InnoDB全表扫描,MyISAM直接读取值,注意的是,当COUNT(1)查询语句中包含WHERE条件时MyISAM也需要扫描全表;
(7)、是否支持MVCC,仅InnoDB支持MVCC,而MyISAM不支持。
3、MYSQL中常用命令
1、查看引擎:show engines
2、查看MySQL当前默认的存储引擎 show variables like '%storage_engine%';
3、查看表的存储引擎 show table status like 'table_name';
4、查看资源消耗情况
SHOW PROFILES
磁盘io耗时,cpu等待耗时,发送数据耗时,拷贝数据到临时表的耗时
SHOW PROFILE CPU,BLOCK IO FOR QUERY QUERY_ID
4、MYSQL事务
事务的四大特性(ACID):
(1)、原子性(Atomicity):事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
(2)、一致性(Consitency):执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
(3)、隔离性(Isolation):并发访问数据库时,一个用户的事务不被其它事务所干扰,各并发事务之间数据库是独立的;
(4)、持久性(Durability):一个事务被提交之后。它对数据库中的数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
并发事务带来的问题
(1)、脏写:比如:有一个初始值为1的数据,现在有两个事务对其数据进行+1操作,A事务先将其值改为2,a事务还没有完成结束,这时B事务读取值2改成3,并进行提交之后,a事务出现异常,将数据进行回滚,导致值还是1。(对于事务 B 看到的场景而言，就是自己明明更新了，结果值却没了，这就是脏写),MySQL最低隔离级别(RUC:READ-UNCOMMITTED)杜绝了脏写。 所谓脏写，就是我刚才明明写了一个数据值，结果过了一会却没了。而它的本质就是事务 B 去修改了事务 A 修改过的值，但是此时事务 A 还没提交，所以事务 A 随时会回滚，导致事务 B 修改的值也没了，这就是脏写的定义。
(2)、脏读:一个事务修改了数据,但是还没提交事务,此修改的值被另外一个事务所读取到,如果前者出现回滚,那么后者的操作结果就不正确啦。
(3)、丢失修改:两个事务同时读到这条数据,都对其进行修改操作,导致其中一个事务修改的结果出现丢失情况。
(4)、不可重复读:指在一个事务内多次读同一个数据。在这个事务还没结束时,另外一个事务也访问该数据进行更新操作,从而在同一个事务中前后读取值不一致现象。
(5)、幻读:一个事务T1读取了几行数据,接着另外一个并发事务T2插入了一些数据。随后查询中,T1事务发现多了一些原本不存在记录,就好像出现幻觉一样,故称为幻读。
MySQL引擎(InnoDB)事务隔离级别:
读取未提交(脏读);
读取已提交(幻读,不可重复读);
可重复读(幻读);
串行化。
5、一条SQL在MySQL中如何执行:
(1)、查询语句
(2)、更新语句
引用来源: 一条SQL语句在MySQL中如何执行的 (qq.com)
针对其MySQL执行数据的增、删、改(CUD)操作,须了解其Buffer Pool(缓冲池)
6、Buffer Pool介绍
我们对MySQL执行增删改操作的时候,不可能直接更新磁盘上的数据,因为对磁盘进行随机IO操作,速度相当的慢,随便一个大磁盘文件的随机读写操作,可能都要几百毫秒,如果这样操作的话,MySQL每秒也就只能处理几百个请求了! 所以MySQL引入了Buffer Pool内存缓冲池,主要针对你实际对数据库的内存里面的数据结构进行增删改操作,只是最终刷盘到磁盘文件上啦。
增删改操作buffer pool执行图:
Buffer Pool总结:
因为增删改操作首先针对这个内存中的Buffer Pool里面的数据执行的,同时配合了后续的redo log,刷磁盘等机制操作。所以Buffer Pool就是数据库的一个内存组件,里面缓存了磁盘上的真实数据，然后我们Java系统对数据库执行的增删改操作,其实主要就是对这个内存数据结构中的缓存数据执行的。
Buffer Pool的大小
因为Buffer Pool本质其实就是数据库的内存组件,所以这个内存数据结构肯定是有大小的。 Buffer Pool默认情况下是128MB,我们在实际生产环境下可以对其进行调整,比如:16核32G的机器,你可以给其分配2GB的内存,修改/etc/my.cnf文件中的innodb_buffer_pool_size = 2147483648
数据是如何存放在Buffer Pool中的
MySQL对数据存储抽象出一个数据页的概念,他把很多行数据放在了一个数据页中,也就是说我们的磁盘文件中就是会有很多的数据页,每一页数据里面存放很多行数据。
A、先了解一下数据页是什么:
数据页(16KB)结构图
1、File Header(文件头部)
记录各种类型数据页的通用信息,如下表图所示:
FIL_PAGE_PREV和FIL_PAGE_NEXT就是分别代表本页的上一个和下一个页的页号,这样通过建立一个双向链表把许许多多的页就都串联起来啦。
注意:并不是所有类型的页都有上一个和下一个页的属性
2、Page Header(页面头部)
用来记录数据页中存储的各种状态信息,组成部分如下表图所示:
3、Infimun + Supremum(最大与最小记录)
MySQL定义了两条伪记录,分别存放在Infimun + Supermum中,由5个字节大小的记录头信息和8个字节大小的固定部分组成,如下图所示:
注意:为了更加体现next_record的作用,Infimum记录的下一条记录就是本页中主键值最小的用户记录,而本页中主键值最大的用户记录的下一条记录就是Supermum记录,如下图所示:
4、User Recorder(用户记录);Free Space(空闲时间)
MySQL存储的记录会按照我们指定的行格式存储到User Records部分。数据页刚开始是没有User Records这部分的,每次插入一条行数据,就会从Free Space尚未使用的存储空间中申请该行数据大小空间划分到User Records中,当Free Space存储空间使用完后,再插入新行数据,就需要申请新的数据页啦!
注意:User Records并不是按照插入顺序进行排序,而是根据主键值由小到大的顺序进行排序。
5、Page Directory(页目录)
为什么需要页目录:
记录在页中是按照主键值从小到大的顺序串联成为一个单向链表,一直往下查询,时间复杂度是O(n),性能不是很好,所以需要页目录来解决此问题。
页目录生成规则与步骤
1)、目录分组规则如下:
1.最小记录所在分组只能有1条记录;
2.最大记录所在分组只能在1~8条之间;
3.其它分组中记录的条数只能在4~8之间。
2)、分组步骤如下:
1、初始情况下,一个数据页只有最小记录(Infimum)和最大记录(Supermum)两个分组。
2、之后每插入一条记录,都会从页目录中找到主键值并且差值最小的槽,然后把改槽对应的记录的n_owned值+1,表示本组内有添加一条记录,直到该组中的记录数等于8个。
3、在一个组中的记录数等于8个后再插入一条记录时,会将组中的记录拆分成两个组,一个组中4条记录,另个组中5条记录,这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。
4、每个组的最后一条记录,行数据中记录的数据n_owned值记录该组条数,该组其它记录的n-owned都为0。
5、将每个组的最后一条记录的地址偏移量,取出来按顺序存储到靠近文件尾部(File Trailer)。
如图所示:
所以在一个数据页中查找指定主键值得记录过程分为两步:
1、通过二分法确定该记录的槽,并找到该槽中主键值最小的那条记录;
2、通过那条记录的next_record属性遍历该槽所在的组中的各个记录。
6、File Trailer(页面尾部)
为什么需要File Trailer
InnoDB存储引擎会把数据存储到磁盘上,但是磁盘速度太慢了,需要以页为单位把数据加载到内存中处理,如果该页中的数据在内存中被修改了,那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候中断了电咋办,为了检测一个页是否完整,就在每个页尾部加一个File Trailer部分,这部分由8个字节组成。
File Trailer的组成
1>、前4个字节代表页的校验和:每当一个页面在内存中修改了,在同步之前就要把它的校验和算出来,因为File Header在页面的前边,所以校验和会被首先同步到磁盘,当完全写完时,校验和也会被写到页的尾部,如果完全同步成功,则页的首部和尾部的校验和应该是一致的,不一致说明同步过程中出了问题
2>、后4个字节代表页面被最后修改时对应的日志序列位置(LSN):这个部分也是为了校验页的完整性的,如果首部和尾部的LSN值校验不成功的话,就说明同步传输过程出现了问题。
数据页说明原文来自:www.cnblogs.com/joeysh/p/10…
B、再回到buffer pool
所以实际上假设我们要更新一行数据,此时数据库会找到这行数据所在的数据页,然后从磁盘文件里把这行数据所在的数据页直接加载到Buffer Pool里面去。 也就是说buffer pool中存放的是一个个的数据页,如下图所示:
磁盘上的数据页和Buffer Pool中的缓存页是如何对应起来
实际上默认情况下,磁盘中存放的数据页的大小是16KB,也就是说,一页数据包含了16KB的内容。而Buffer Pool默认128MB,则可以容纳8192个数据页。
而Buffer Pool中存放一个一个的数据页,我们通常叫做缓存页,因为Buffer Pool是一个缓冲池,里面的数据都是从磁盘缓存到内存去的。
而Buffer Pool中默认情况下,一个缓存页的大小和磁盘上的一个数据页的大小是一一对应起来的,都是16KB。
缓存页的描述信息
对于每个缓存页,它实际上都会有一个描述信息,这个描述信息大体可以认为是用来描述这个缓存页的(这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址等。)
每个缓存页都会对应一个描述信息,这个描述信息本身也是一块数据,在buffer pool中,每个缓存页的描述数据放在最前面,然后每个缓存页放在后面。
Buffer Pool中的描述数据大概相当于缓存页大小的5%左右,也就是每个描述数据大概是800个字节左右的大小,然后你假设你设置的buffer pool大小是128MB,实际上Buffer Pool真正的最终大小会超出一些,可能有个130多MB的样子,因为它里面还有存放每个缓存页的描述数据。
数据库启动的时候,是如何初始化Buffer Pool的
1、申请空间: 数据库只要一启动,就会按照你设置的Buffer Pool大小,稍微再加大一点,去找操作系统申请一块内存区域,作为Buffer Pool的内存区域。
2、划分空间 : 然后当内存区域申请完毕之后,数据库就会按照默认的缓存页的16KB的大小以及对应的800个字节左右的描述数据的大小,在Buffer Pool中划分出来一个一个的缓存页和一个一个的它们对应的描述数据。此时Buffer Pool的缓存页都是空的,里面什么都没有,要等数据库运行起来之后,当我们要对数据执行增删改查的操作的时候,才会把数据对应的页从磁盘文件里面读取出来,放入Buffer Pool中的缓存页中。
Free链表
当数据库运行起来之后,会为Buffer Pool设计一个Free链表,它是一个双向链表数据结构,这个free链表里,每个节点就是一个空闲的缓存页的描述数据块的地址。也就是说,只要你一个缓存页是空闲的,那么它的描述数据块就会被放入这个free链表中。
free链表: 这个free链表里面就是各个缓存页的描述数据块,只要缓存页是空闲的,那么它们对应的描述数据块就会加入到这个free链表中,每个节点都会双向链接自己的前后节点,组成一个双向链表。这个free链表有一个基础节点,它会引用链表的头节点和尾结点,里面还存储了链表中有多少个描述数据块的节点,也就是有多少个空闲的缓存页,是40字节大小的节点。
如何将磁盘上的页读取到Buffer Pool的缓存页中去?
借助free链表,从free链表里获取一个描述数据块,然后对应的找到这个描述数据块对应的空闲缓存页,然后把磁盘上的数据页读到该缓存页中,同时把相关描述数据写到描述数据块中,并将该描述数据块从free链表中除去。
数据页缓冲哈希表
如何确定数据页是否被缓存?
数据库还会有一个hash表数据结构,它会用表空间号 + 数据页号,作为一个key,然后缓存页的地址作为value。
当你要使用一个数据页的时候,通过"表空间号 + 数据页号"作为key去这个hash表里面查一下,如果没有就读取数据页,如果有,就说明数据页已经被缓存啦!也就是说,每次你读取一个数据页到缓存之后,都会在这个hash表中写入一个key-value对,key就是"表空间号+数据页号",value就是缓存页的地址,那么下次如果你再使用这个数据页,就可以从hash表里直接读取出来,它已经被放入一个缓存页。
Buffer Pool的内存碎片
Buffer Pool会有内存碎片,因为Buffer Pool大小是你自己设置的,很可能Buffer Pool划分全部的缓存页和描述数据块之后,还剩一点点的内存,这一点点的内存放不下任何一个缓存页了,所以这点内存就只能放着不能用,这就是内存碎片。
如何减少内存碎片:
数据块在Buffer Pool中划分缓存页的时候,会让所有的缓存页和描述数据块都紧密的挨在一起,这样尽可能减少内存浪费,就可以尽可能的减少内存碎片的产生啦!
flush链表
缓存页是脏数据/脏页:
你更新的数据页都会在Buffer Pool的缓存页里,供你在内存中直接执行增删改的操作。接着你肯定会去更新Buffer Pool的缓存页中的数据,此时一旦你更新了缓存页中的数据,那么缓存页里面的数据和磁盘上的数据页里的数据,就不一致啦。我们就说缓存页是脏数据,脏页。
为什么区分脏页
因为在内存里更新的脏页的数据,都是要被刷新回磁盘文件的。但是,不可能所有的缓存页都刷回磁盘的,因为有的缓存页可能是因为查询的时候被读取到Buffer Pool里去的,可能根本没修改过。
哪些缓存页是脏页:
flush链表,这个flush链表本质也是通过缓存页的描述数据块中的两个指针,让被修改的缓存页的描述数据,组成一个双向链表。凡是被修改过的缓存页,都会把它的描述数据块加入到flush链表中去,后续都是要flush刷新都磁盘上去的。故flush链表就是记录脏数据页的。
Buffer Pool缓存页满了,怎么办?
淘汰缓存页:即把一个缓存页里被修改过的数据,给它刷到磁盘上的数据页里去,然后这个缓存页就可以清空了,让它重新变成了一个空闲的缓存页。接着你再把磁盘上你需要的新的数据页加载到这个腾空的空闲缓存页中去。
缓存命中率:一个缓存页的数据,经常被修改与查询,比如100次请求,30次操作缓存,不需要从磁盘中加载数据,这个缓存命中率就比较高了。
另一个缓存页里的数据,就是刚从磁盘加载到缓存页之后,被修改和查询过1次,之后100次请求中没有一次是修改和查询这个缓存页的数据的,那么此时我们就说缓存命中率有点低,因为大部分请求可能还需要走磁盘查询数据,他们操作的数据不在缓存中。
怎么知道哪些缓存页经常被访问,哪些缓存页很少被访问
LRU:一个新的LRU链表,这个所谓的LRU就是least recently userd,最近很少使用的意思。
LRU工作原理:假设我们从磁盘加载一个数据页到缓存页的时候,就把这个缓存页的描述数据块放到LRU链表头部去,那么只要有数据的缓存页,他都会在LRU里,而且最近被加载数据的缓存页,都会放到LRU链表的头部。 然后假设某个缓存页的描述数据块本来在LRU链表的尾部,后续你只要查询或者修改了这个缓存页的数据,也要把这个缓存页挪动到LRU链表的头部去。
也就是说最近被访问过的缓存页,一定在LRU链表的头部。
当你的缓存页没有一个空闲的时候,是不是要找出那个最近最少被访问的缓存页去刷入磁盘?
此时你就直接在LRU链表的尾部找到一个缓存页,它一定是最近最少被访问的那个缓存页!然后你就把LRU链表尾部的那个缓存页刷入磁盘中,然后把你需要的磁盘数据页加载到腾出来的空闲缓存页中就可以啦。
LRU问题
MySQL的预读机制:当你从磁盘上加载一个数据页的时候,它可能会连带着把这个数据页相邻的其它数据页,也加载到缓存里去!例如:现在有两个空闲缓存页,然后在加载一个数据页的时候,连带着把它的一个相邻的数据页也加载到缓存里去了,正好每个数据页放入一个空闲缓存页!实际上只要一个缓存页是被访问了,另外一个通过预读机制加载的缓存页,其实并没有人访问,此时这两个缓存页可都在LRU链表的前面。
触发MySQL的预读机制场景:
(1)、有一个参数是innodb_read_ahead_threshold,它的默认值是56,意思就是如果顺序的访问一个区里的多个数据页,访问的数据页的数量超过了这个阈值,此时就会触发预读机制,把下一个相邻区中的所有数据页都加载到缓存里去。
(2)、如果Buffer Pool里缓存了一个区里的13个连续的数据页,而且这些数据页都是比较频繁会被访问的,此时就会直接触发预读机制,把这个区里的其它的数据页都加载到缓存里去。这个机制是通过innodb_random_read_ahead来控制的,它默认是OFF,也就是这个规则是关闭的。
所以默认情况下,主要是第一个规则可能会触发预读机制,一下把很多相邻区的数据页加载到缓存里去,这些缓存页如果一下子都放在LRU链表的前面,而且它们其实并没什么人会访问,那就会如上图,导致本来就在缓存里的一些频繁被访问的缓存页在LRU链表的尾部。
全表扫描机制:能导致频繁被访问的缓存页被淘汰的场景,那就是全表扫描。
例如:SELECT * FROM USERS 此时它没有加入任何一个where条件,会导致它直接一下子把这个表里的所有的数据页,都从磁盘加载到Buffer Pool里去。这个时候它可能会一下子就把这个表的所有数据页都 一 一 装入各个缓存页里去!此时可能LRU链表中排在前面的一大串缓存页,都是全表扫描加载进来的缓存页!
那么如果这次全表扫描过后,后续几乎没有用到这个表里的数据呢?
此时LRU链表的尾部,可能全部都是之前一直被频繁访问的那些缓存页!然后当你要淘汰一些缓存页腾出空间的时候,就会把LRU链表尾部一直被频繁访问的缓存页给淘汰掉了,而留下了之前全表扫描加载进来的大量的不经常访问的缓存页!
LRU优化 --- 基于冷热分离
真正的MySQL在设计LRU链表的时候,采取的实际上是冷热数据分离的思想。
之前一系列的问题都是因为所有缓存页都混在一个LRU链表里,才导致的么?
真正的LRU链表,会被拆分为两个部分,一部分是热数据,另外一部分是冷数据,这个冷数据的比例是由InnoDB_old_blocks_pct参数控制的,它默认是37,也就是说冷数据占比37%。
原理:数据页第一次被加载到缓存页之后,这个缓存页是放在LRU链表的冷数据区域的头部的,然后必须是1s过后访问换个缓存页,它才会被移动到热数据区域的链表头部。
MySQL设定一个规则,它设计了一个 innodb_old_blocks_time参数,默认值1000,也就是1000毫秒。
因为那种预读机制以及全表扫描机制加载进来的数据页,大部分都会在1s之内访问一下,之后可能就再也不访问了,所以这种缓存页基本上都会留在冷数据区域里。然后频繁访问的缓存页还是会留在热数据区域里。当你要淘汰缓存的时候,优先就是会选择冷数据区域的尾部的缓存页,这就是非常合理的!
在LRU链表的冷数据区域中的都是什么样的数据?
大部分应该都是预读加载进来的缓存页,加载进来1s之后都没人访问的,然后包括全表扫描或者一些大的查询语句,加载一堆数据到缓存页,结果都是1s之内访问了一下,后续就不再访问这些表的数据啦!
如果在redis里存放了很多缓存数据,那么此时会不会有类似冷热数据的问题?应该如何优化和解决?
我们在设计缓存机制的时候,经常会考虑热数据的缓存预加载。
也就是说,每天统计出来哪些商品被访问的次数最多,然后晚上的时候,系统启动一个定时作业,把这些热门商品的数据,预加载到redis里。那么第二天是不是对热门商品的访问就自然会优先走redis缓存啦!
定时刷新
并不是在缓存页满的时候,才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘,而是有一个后台线程,它运行一个定时任务,这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页,刷入磁盘里去,清空这几个缓存页,把它们加入回free链表去!
这个后台线程同时也会在MySQL不怎么繁忙的时候,找个时间把flush链表中的缓存页都刷入磁盘中,这样被你修改过的数据,迟早都会刷入磁盘的!
总结: 注意:我们写SQL时对应表+行,在MySQL内部是表空间+数据页。
当我们操作增删改数据时,首先通过"表空间号 + 数据页号"作为key去数据页缓存hash表查一下,如果有说明已经缓存了,如果没有读取数据页时:从free链表找到一个空闲的缓存页,从磁盘读取数据页到缓存页,从磁盘读取数据页到缓存页,写入描述数据,从free链表移除这个描述数据块。
Buffer Pool讲解来自: blog.csdn.net/mingyuli/ar…
二、MySQL回滚日志(undo log)总结
1>、undo log的概念
undo log是mysql中比较重要的事务日志之一,顾名思义,undo log是一种用于撤销回退的日志,在事务没提交之前,MySQL会先记录更新前的数据到undo log日志文件里面,当事务回滚时,或者数据库崩溃时,可以利用undo log来进行回退。
2>、undo log的作用
在MySQL中,undo log日志的作用主要有两个:
1、提供回滚操作 [undo log实现事务的原子性];
2、提供多版本控制(MVCC)[undo log 实现多版本并发控制(MVCC)]
MVCC,即多版本控制,在MySQL数据库InnoDB存储引擎中,用undo log来实现多版本并发控制(MVCC)。当读取的某一行被其它事务锁定时,它可以从undo log中分析出该行记录以前的数据版本是怎么样的,从而让用户能够读取到当前事务操作之前的数据[快照读]。
快照读:
SQL读取的数据是快照版本[可见版本],也就是历史版本,不用加锁,普通的SELECT就是快照读。
当前读:
SQL读取的数据是最新快照版本。通过锁机制来保证读取的数据无法通过其它事务进行修改UPDATE、DELETE,INSERT、SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE都是当前读。
3>、undo log的存储机制
undo log的储存由InnoDB存储引擎实现,数据保持在InnoDB的数据文件中。在InnoDB存储引擎中,undo log是采用分段(segment)的方式进行存储的。rollback segment 称为回滚段,每个回滚段中有1024个undo log segment。在MySQL5.5之前,只支持1个rollback segment,也就是只记录1024个undo操作。在MySQL5.5之后,可以支持128个rollback segment,分别从resg slot0 - resg slot127,每一个resg slot,也就是每一个回滚段,内部由1024个undo segment组成,即总共可以记录128 * 1024个undo操作。
下面以一张图来说明undo log日志里面到底存了哪些信息?
如上图,可以看到,undo log 日志里面不仅放着数据更新前的记录,还记录着RowID,事务ID,回滚指针。其中事务ID每次递增,回滚指针第一次如果是INSERT语句的话,回滚指针为NULL,第二次UNPADTE之后的undo log 的回滚指针就会指向刚刚那一条undo log日志,依次类推,就会形成一条undo log的回滚链,方便找到该条记录的历史版本。
4>、undo log工作原理
在更新数据之前,MySQL会提前生成undo log日志,当事务提交的时候,并不会立即删除undo log,因为后面可能需要进行回滚操作,要执行回滚(rollback)操作时,从缓存中读取数据。undo log日志的删除是通过后台purge线程进行回收处理的。
同样,通过一张图来理解undo log的工作原理。
如上图:
1、事务A执行update操作,此时事务还没提交,会将数据进行备份到对应的undo buffer,然后由undo buffer持久化到磁盘中的undo log文件中,此时undo log保存了未提交之前的操作日志,接着将操作的数据,也就是teacher表的数据持久保存到InnoDB的数据文件.IBD。
2、此时事务B进行查询操作,直接从undo buffer缓存中进行读取,这时事务A还没提交事务,如果回滚rollback事务,是不读磁盘的,先直接从undo buffer缓存读取。
用undo log实现原子性和持久化的事务简化过程: 假设有A、B两个数据,值分别为1,2。
A、事务开始;
B、记录A=1到undo log中;
C、修改A=3;
D、记录B=2到undo log中;
E、修改B=4;
F、将undo log写到磁盘 ---- undo log持久化;
G、将数据写到磁盘 --- 数据持久化;
H、事务提交 --- 提交事务;
之所以能同时保证原子性和持久性,是因为以下特点:
1、更新数据前记录undo log
2、为了保证持久性,必须将数据在事务提交前写到磁盘,只要事务成功提交,数据必然已经持久化到磁盘。
3、undo log必须先于数据持久化到磁盘。如果在G、H之间发生系统崩溃,undo log是完整的,可以用来回滚。
4、如果在A - F之间发生系统崩溃,因为数据没有持久化到磁盘,所以磁盘上的数据还是保持在事务开始前的状态。
缺陷:
每个事务提交前将数据和undo log写入磁盘,这样会导致大量的磁盘IO,因为性能较差。如果能够将数据缓存一段时间,就你减少IO提高性能,但是这样会失去事务的持久性。
undo log日志属于逻辑日志,redo log日志是物理日志,所谓逻辑日志是undo log是记录一个操作过程,不会物理删除undo log ,SQL 执行DELETE或者UPDATE操作都会记录一条undo log日志。
5>、undo log的相关参数:
1、innodb_undo_directory : 指定undo log日志的存储目录,默认值为./。
2、innodb_undo_logs
在MySQL5.6版本之后,可以通过此参数自定义多少个rollback segment,默认值为128。
3、innodb_undo_tablespaces
指定undo log平均分配到多少个表空间文件中,默认值为0,即全部写入一个文件中。不建议修改为非0值,我们直接使用默认值即可。
在InnoDB存储引擎中,在启动日志中也会提示:不建议修改innodb_undo_tablespace为非0的值。
undo log日志讲解来源: blog.csdn.net/Weixiaohuai…
三、MVCC讲解
MVCC讲解来源:blog.csdn.net/popofzk/art…
InnoDB引擎下,事务具有ACID(原子性,一致性,隔离性,持久性),一致性其实是目的,有原子性、隔离性和持久性共同来保证!原子性是由undo log来进行保证的(回滚的时候采用undo log),持久性由redo log 、undo log、binlog来保证,而隔离性指的是它有四个隔离级别,分别是:读取未提交;读取已提交;可重复读;串行化。
MVCC是基于 undo log版本链 + ReadViwe 对并发事务进行访问。
1、undo log版本链:
其中trx_id代表导致当前数据版本的事务id,db_roll_ptr存储着上一个数据版本的数据地址。
undo log 回滚的过程:
比如:INSERT 一条记录,得把这个记录的Id记录下来,回滚的时候直接删除这个id即可。 DELETE记录,则要把记录的内容保留,回滚的时候插入即可。 UPDATE记录,则把旧值记录,回滚时直接把旧值写入。
undo log 不是会被删除嘛?中间数据万一被删了版本链不就断了?
undo log版本链不是立即删除,mysql确保版本链数据不再被"引用"后再进行删除!
2、ReadView
ReadView就是读视图,是"快照读"SQL执行时MVCC提取数据的依据。 快照读就是最普通的SELECT 查询SQL语句
ReadView的数据结构,包含四个字段:
m_ids:当前活跃的事务编号集合
min_trx_id:最小活跃事务编号;
max_trx_id:预分配事务编号(当前最大事务编号+1)
creator_trx_id: ReadView创建者的事务编号
读取已提交(RC)下:
每执行一次快照度的时候,都去生成一个ReadView,所以两次生成了两个不同的ReadView读视图。
版本链数据访问规则:
1、判断当前事务id是否等于Redview创建者的事务id(trx_id == creator_trx_id):
成立,说明数据就是自己这个事务更改的,可以访问;
2、判断事务id是否小于最小活跃事务id(trx_id < min_trx_id):
成立说明数据已经提交了,可以访问啦。
3、判断事务id 大于 预分配事务编号(trx_id > max_trx_id)
成立说明该事务是在ReadView生成以后才开启,不允许访问。
4、判断事务编号 是否大于 最小活跃事务编号 且 小于等于最大预分配事务编号( min_trx_id < trx_id <= max_trx_id)
成立,在m_ids(当前活跃事务编号集合)中的数据对比,不存在数据,则代表数据已提交了,可以访问。
可重复读(RR)下:仅在第一次执行快照读时生成ReadView,后续快照读复用前面的ReadView。
RR级别下使用MVCC能避免幻读吗?
能,但不完全能!
1、连续多次快照读,ReadView会产生复用,没有幻读问题;
2、当两次快照读之间存在当前读,ReadView会重新生成,导致幻读。
比如:当事务B中两次SELECT快照读的中间有一个更新语句(当前读),这种情况下,第二次的快照读生成的ReadView就会重新生成,而不是复用,否则在同一个事务中前后就会造成不一致!
四、binlog日志
1>、定义
binlog日志被称为归档日志,以二进制形式进行存储,是MySQL Server层的日志,而不是存储引擎自带的日志,它记录所有的DDL和DML语句,而且是以事件形式记录,还包含语句所执行的消耗时间等。
注意:
1、binlog是一种逻辑日志,它里面记录的是一条SQL语句原始逻辑。
2、binlog文件写满后,会自动切换到下一个日志文件继续写,而不会覆盖以前的日志,这个区别于redo log,redo log是循环写入的,即后面写入可能会覆盖前面的写入。(MySQL数据恢复,通过使用mysql binlog工具再结合binlog文件,可以将数据恢复到过去的某一时刻。)
2>、binlog的格式
binlog有三种格式:
a、statement:每一条会修改数据的SQL都会记录在binlog中;
好处:statement模式只记录SQL,不需要记录每一行数据的变化,因此极大的减少了binlog的日志量,避免大量的IO操作,提升了系统性能。
坏处:由于statement模式只记录SQL,而如果一些SQL中包含了函数,那么可能会出现执行结果不一致的情况。比如:SQL中使用了uuid函数,在master记录了uuid,当同步到slave之后,再次执行,就会得到另外一个结果。
b、row:不记录SQL语句上下文信息,仅保存那条记录被修改。
好处:row格式的日志内容会非常清楚记录下每一行数据修改的细节,这样就不会出现statement中存在那种数据无法被正常复制的情况。
坏处:row格式也有一个很大的问题,就是日志量太大了,特别是批量操作,大量的日志也会带来IO性能问题。
c、mixed:statement与row的结合体。 在mixed模式中MySQL会根据执行的没一条具体的SQL语句来区别对待记录的日志格式,也就是说系统会自动判断该statement还是row。
binlog日志讲解来源:blog.csdn.net/whatday/art…
五、relay log
中继日志(relay log) 只在主从服务器架构的从服务器上存在。 默认保存在从服务器的数据目录下。
文件名的格式是:从服务器名 - relay-bin.序号。
中继日志还有一个索引文件:从服务器名 - relay-bin.index,用来定位当前使用的中继日志。
从服务器I/O线程将主服务器的二进制日志(binlog)读取过来记录到从服务器本地文件(relay log),然后从服务器SQL线程会读取中继日志的内容并应用到从服务器,从而是从服务器和主服务器的数据保持一致。
作用: 中继日志用于主从服务器架构中,从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容,来同步主服务器上的操作。
中继日志是链接master和slave的信息,它是复制核心,I/O线程将来自master的binlog存储到中继日志中,中继日志充当缓冲,这样master不必等待slave执行完成就可以发送下一个binlog。
中继日志来源:www.51cto.com/article/744…
六、MySQL中的锁
1>、快照读和锁定读
a、一致性读/快照读
事务的读取操作如果使用MVCC的方式读取就是快照读,它是一种无锁的读。所有的SELECT查询语句在可重复读、读已提交的级别下都是快照读。由于一致性读不会对记录进行加锁的操作,所以在并发环境下其它事务可以对表中的记录进行写操作。
b、锁定读
1、共享锁和独占锁
共享锁:简称S锁,事务要读取一条记录时需要获取该记录的S锁。读读允许。
独占锁/排他锁:简称X锁,事务要改动一条记录需要获得该记录的X锁。读写、写读、写写都会阻塞。类似Java中的ReentrantReadWriteLock。
锁定读的语句
锁定读:读取记录信息前对记录进行加锁(S/X锁都行)的行为就叫做锁定读。
LOCK IN SHARE MODE 对记录加S共享锁
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE,这段语句就会对该记录进行加S锁,允许其它事务的读取,其它事务也可以继续加S锁,但是不允许其它事务加X锁,否则会阻塞,直到当前事务提交后释放了S锁,其它线程才能加X锁。
FOR UPDATE 对记录加X独占锁
SELECT * FROM user WHERE id = 1 FOR UPDATE,对该记录进行加X独占锁,其它事务要加S锁或者X锁都会被阻塞。生产环境严谨使用FOR UPDATE。
2>、表锁和行锁、X锁/S锁、IX锁、IS锁
MyISAM、MEMORY这些存储引擎它们支持表锁不支持行锁。InnoDB支持表锁和行锁。
意向锁Intention LOCK
意向共享锁 IS锁:当事务准备在某条记录上加S锁时,需要先在表上加IS锁。
意向独占锁(IX锁):当事务准备在某条记录上加X锁时,需要先在表上加IX锁。
InnoDB的表锁
一般情况InnoDB引擎在做CRUD时,不会对表进行加表级别的S锁或X锁。
但是对某个表执行DDL语句就会阻塞其它的事务对表记录的修改。同样,一个正在对表记录进行修改的事务会将DDL操作阻塞。这个过程是通过Service层的元数据锁metadata lock来完成的。
AUTO-INC锁:主要用来对表进行插入记录,系统记录赋予自增ID的作用,INSERT语句执行时需要对表进行加Auto-inc锁方式,如果不采用这种方式就会采用一种轻量级锁的方式来保证自增ID的准备:简单来说就是为了INSERT语句修饰自增属性的列获取一把锁然后分片ID,分配完成后立即释放,无需等待INSERT语句结束才释放。
一般对于确定INSERT多少条记录的场景使用轻量级锁,对于不确定数量的INSERT来说采用AUTO_INC锁的方式。
3>、InnoDB的行锁
InnoDB的锁都是基于索引加锁的,如果修改语句没有使用索引,那么将升级为表锁。
1、Record LOCK记录锁
最普通的记录锁、普通记录锁是区分S锁和X锁的,使用唯一性的索引进行等值查询且精准匹配到一条记录时,例如:SELECT * FROM student WHERE id = 2 FOR UPDATE;就会将李四的记录锁定.
2、GAP LOCK间隙锁
间隙锁和临键锁的出现就是为了解决可重复读隔离级别下的幻读问题(快照读遇到当前读会有幻读问题)。间隙锁就是在目标记录行的上一条集合和当前目标记录之间的缝隙加一个锁。
仅在可重复读级别下生效,使用等值查询/范围查询时,并且没有命中存在的记录,那么在索引对应区间生成间隙锁。例如:select * from student where id = 5 for update 或 select * from t where id > 2 and id < 7 for update就会将(2,7)区间锁住。
因为InnoDB的锁是基于索引的,索引时根据字段进行排序的,所以通过间隙锁可以来阻止其它事务向该间隙插入记录。
3、NEXT-KEY LOCK 临键锁
临键锁是记录锁 + 间隙锁的组合形式,用来保证当前记录不被修改和这个间隙不予许插入。
临键锁适用于非唯一索引,且仅在可重复读级别下生效。和间隙锁不同的是,临键锁是SQL命中了部分记录的场景,除了会锁定间隙外还好锁定命中记录的下一个区间,是一个左开右闭的模式。在可重复读级别下,MySQL默认就是使用的临键锁,当在非唯一索引下如果SQL没有命中记录则就是间隙锁,命中了就是临键锁。
如果用唯一索引且SQL命中了记录,那么加的就是普通记录锁,否则就是间隙锁。
Insert Intention LOCK 插入意向锁 --- 无实用
当事务A要插入一条记录时,要判断索引在这个位置区间是否被加了间隙锁或者临键锁,如果有的话,A事务就会阻塞,直到其它事务提交释放了锁为止。
但是A事务处在等待过程中也需要在内存中生成一个锁结构,来表明A事务处于锁的等待过程中,这个锁就是插入意向锁。
4>、锁的内部结构
行锁的n_bits:一个页面中存在多条记录,用不同的比特位来区分到底是哪一条记录加了锁。
MySQL锁讲解来源:blog.csdn.net/qq_42290561…
七、MySQL的EXPLAIN分析
1>、EXPLAIN简介
使用EXPLAIN命令可以获取以下结果:
id ; select_type; table ; type; possible_keys; key ;key_len;ref; rows; extra;
2>、字段说明
1、id
定义:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
id的结果共有二种情况:
A、id相同,执行顺序由上至下;
B、id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
2、select_type
定义:用来表示查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。 其类型有如下五种: a、SIMPLE:简单的SELECT查询,查询中不包括子查询或者UNION。
b、PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY。
c、SUBQUERY:在SELECT或WHERE列表中包含了子查询。
d、DERIVED:在FROM列表中包含的子查询被标记为DERIVIED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中。
e、UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含FROM字句的子查询中,外层SELECT将被标记为:DERIVED。
f、UNION RESULT:从UNION表获取结果的SELECT。
3、table
定义:指的是当前执行的表。
4、type
定义:所显示的是查询使用了哪种类型,type包含以下类型: ALL;INDEX;RANGE;REF;EQ_REF;CONST;SYSTEM;NULL
其性能排序如下所示:
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。
类型说明:
a、system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
b、const:表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
c、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
d、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
e、range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
f、index:index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。
d、all:将遍历全表以找到匹配的行。
5、possible_keys
定义:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
6、key
定义:实际使用的索引，如果为NULL，则没有使用索引,可能原因包括该字段没有建立索引或者导致索引失效啦!
7、ken_len
定义:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
8、ref
定义:显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
9、rows
定义:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。
10、Extra
定义:包含不适合在其他列中显示但十分重要的额外信息。额外信息如下所示:
a、using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"。
b、using temporary
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
c、using index
表示相应的select操作中使用了覆盖索引（covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
d、using where
表明使用了where过滤。
e、using join buffer
表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
f、impossible where
where子句的值总是false，不能用来获取任何元组。
g、select tables optimized away
在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(1)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
h、distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
文章来源:blog.csdn.net/weixin_4188…
