一、MySQL的体系结构
本人用一张树状图来通俗易懂的展现MySQL的体系结构
从下图可以看到MySQL的体系结构分为MySQL Sever层和存储引擎层,以及往下的细分和功能
二、Query Cache详解
- Query Cache在生产中建议关闭,其只能缓存静态数据信息,一旦数据发生变化,又经常读写,Query Cache就显得力不从心了
- 一般数据仓库之类的可以考虑开启Query Cache
- MySQL5.6之前的版本默认开启Query Cache,5.6之后的默认关闭
1、介绍一款压力测试软件sysbench
- sysbench是一个开源的,模块化的,跨平台的多线程性能测试工具,用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试
- 软件包下载地址:https://downloads.mysql.com/source/sysbench-0.4.12.14.tar.gz
sysbench的安装过程
# tar -zxf sysbench-0.4.12.14.tar.gz
# cd sysbench-0.4.12.14/
# ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
# make && make install
# find / -name 'libmysqlclient*' #找到libmysqlclient.so.20文件做一个软链接
# ln -s /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20
# vim /etc/ld.so.conf
/usr/local/lib
# /sbin/ldconfig -v
2、如何彻底关闭Query Cache
彻底关闭Query Cache需要关注两个核心参数:
如下:
root@localhost [(none)]>show variables like "%query_cache_size%";
- 将query_cache_sizes设置为0以及将 query_cache_type设置为OFF
root@localhost [(none)]>show variables like "%query_cache_type%";
三、存储引擎
最主流的两个引擎:InnoDB 和 MyISAM,现在数据库默认的存储引擎是InnoDB
1、InnoDB体系结构
数据库:MySQL数据库是一个单进程多线程模型的数据库
数据库实例:进程加内存的组合
就比如一个杯子里装了水,杯子相当于数据库,水就相当于数据库实例
- InnoDB体系结构由内存结构、线程、磁盘文件组成,以下细讲
2、InnoDB存储结构
InnoDB逻辑存储单元主要分为表空间、段、区和页
a)表空间
- InnoDB存储引擎表中的所有数据都存放在表空间中
- 表空间分为系统表空间和独立表空间
系统表空间:以ibdata1来命名,在安装数据库初始化时就是系统在建立一个ibdata1的表空间文件,它会储存所有数据的信息以及回滚段(undo)的信息
- 数据库默认的ibdata1的大小是10M,在遇到高并发事物时,会受到不小的影响,建议将ibdata1的大小调整到1G
独立表空间:默认使用独立表空间文件,每个表有自己的表空间文件不用储存在ibdata1中
- 储存 对应表的B+树数据、索引和插入缓冲
- 每个表都有自己的表空间,可以实现表空间的转移,回收表空间方便
- 缺点是每个表文件都有.frm和.ibd文件两个文件描述符,如果单表增加过快就容易出现性能问题
共享表空间:数据和文件放在一起管理
- 无法在线回收空间,回收空间的将全部InnoDB表中的数据备份,删除原表,然后再把数据导回到与原表结构一样的新表中
b)段
- 表空间由段组成,也可以将一个表理解成4个段
- 每个段由N个区和32个零散的页组成
- 段空间扩展以区为单位扩展
- 一般来说,创建一个索引同时会创建两个段(非叶子节点和叶子节点段)
- 一个表有四个段,是索引个数的两倍
c)区
- 区有连续的页组成的,在物理上是连续分配的一段空间,每个区的大小固定是1M
d)页
- InnoDB的最小物理存储分配单位是page
- 一般来说,一个区由64个连续的页组成,页的默认大小为16K
- 一个page页会默认预留1/16的空间用于更新数据。真正使用的是15/16的空间
e)行
- 页里面记录着行记录的信息
- InnoDB存储引擎面向的是行,也就是数据是按照行存储的
- 行记录数据按照行格式进行存放
- 有两种文件格式,Antelope和Barracuda
- Antelope有compact和redundant两种行记录格式
- Barracuda有compressed和dynamic两种行记录格式
- 5.7默认使用 dynamic行记录格式和 Barracuda文件格式
- 行溢出:
需要存储的数据在当前储存界面之外,拆分到多个页进行储存 - 目前生产环境中建议使用dynamic行记录格式进行存储
3、Buffer状态及其链表结构
- page是InnoDB磁盘I/O的最小单位,数据存放在page中,对应到内存就是一个个buffer
- buffer有三种状态:
free buffer:这个状态下的buffer没有被使用,是空闲的。但是在生产环境中,数据库很繁忙的情况下,free buffer基本不存在
clean buffer:内存中buffer里的数据和磁盘中page的数据一致
dirty buffer:内存中新写入的数据还没有刷新到磁盘,uffer里的数据和磁盘中page的数据不一致 - buffer在内存中,需要被chain(链)组织起来
- InnoDB是双向链表结构,由三种不同的buffer状态衍生出三条链表
free list:把free buffer都串联起来。数据库跑起来的时候,每次把page调到内存中,都会判断free buffer是否够用,不够用的话从lru list和flush list中释放free buffer
lru list:把与磁盘数据一致,并且最近最少被使用的buffer串联起来,释放出free buffer
flush list:把dirty buffer串联起来,方便刷新线程将脏数据刷到磁盘。规则是将那些最近最少被弄脏的数据串起来,刷新到磁盘后,释放出更多的free buffer
4、各大刷新线程及其作用
a)master thread线程
后台线程中的主线程,优先级别最高
内部有四个循环:
- 主循环loop
- 后台循环backgroud loop
- 刷新循环flush loop
- 暂定循环suspend loop
根据数据的运行状态在这四个循环之间进行切换
- 主循环loop中包含了每1s和每10s的操作
- 每1s操作:
1、日志缓冲刷新到磁盘,即使这个事务还没有被提交
2、刷新脏页到磁盘
3、执行合并插入缓冲的操作
4、产生checkpoint
5、清除无用的table cache
6、如果当前没有用户活动,就可能切换到background loop - 每10s操作:
1、日志缓冲刷新到磁盘,即是事务还没有被提交
2、执行合并插入缓冲操作
3、刷新脏页到磁盘
4、删除无用的undo页
5、产生checkpoint
b)四大I/O线程
read/write thread:数据库的读写请求线程,默认值是4个,如果使用高转速磁盘,可适当调大该值
redo log thread:把日志缓存中的内容刷新到redo log文件中
change buffer thread:把插入缓存(change buffer)中的内容刷新到磁盘中
c)page cleaner thread
负责脏页刷新线程,可增加多个
d)purge thread
负责删除无用的undo页
- 由于进行DML语句操作都会生成undo,系统需要定期对undo页进行清理,这时就需要purge操作
- purge默认线程个数是1个,最大可调整至32个
e)checkpoint线程
在redo log发生切换时,执行checkpoint。
redo log 发生切换或文件快写满时,会触发把脏页刷新到磁盘,确保redo log刷新到磁盘,实现真正的持久化,避免数据丢失
- error monitor thread :负责数据库报错的监控线程
- lock monitor thread :负责锁的监控线程
5、内存刷新机制
MySQL这种关系型数据库,讲究日志先行策略,就是一条DML语句进入数据库之后,先写日志,再写数据文件
a)redo log
redo log 又称重做日志文件,用来记录事务操作的变化,记录数据修改之后的值,不管事务是否被提交,都会被记录下来
- 在实例和介质失败时,如数据库掉电,这时候InnoDB存储引擎会使用重做日志,恢复到掉电前的时刻
默认情况至少有两个redo log文件,在磁盘用ib_logfile(0-N)来命名
- redo log 写的方式是顺序写、循环写
顺序写、循环写是什么意思呢?
也就是说,第一个文件写满之后,写第二个文件,这样依次写到最后一个文件,直至最后一个文件写满之后,又重新写第一个文件,就这样循环 - 写满日志文件会产生切换操作,并执行checkpoint,触发脏页的刷新
- 数据库重启过程中,如果参数文件中的redo log值大小和当前redo log值大小不一致,那就删除当前redo log文件,按照参数文件中的redo log值大小,生成新的redo log文件
在生成redo log之前,数据写在redo log buffer中
redo log buffer刷新到磁盘的条件:
(1)通过innodb_flush_log_at_trx_commit 参数来控制
- 0:redo log thread 每隔1s会将redo log buffer中的数据写入redo log中,同时进行刷盘操作
每次事务提交并不会触发redo log thread 将日志缓冲中的数据写入redo log中去 - 1:每次事务提交时,都会触发redo log thread 将日志缓冲中的数据写入redo log,并flush到磁盘
- 2:每次事务提交时,把redo log buffer的数据写入redo log中,不刷到磁盘
这三种模式:0的性能 最好,但不安全,有丢失1s的数据的风险。1的安全性最高,不会丢失任何已经提交的事务,但就是数据库的性能最慢。2介于两者之间
(2)master thread :每秒进行刷新
(3)redo log buffer:使用超过一半的时候就触发刷新
b)binlog
MySQL的二进制文件,用于备份恢复和主从复制
- 从binlog cache刷新到磁盘binlog文件中所需条件:
sync_binlog=0时,当事务提交后,MySQL不做fsync之类的磁盘同步指令、将binlog_cache中的信息刷新到磁盘,而是让Filesystem自行决定什么时候来做同步,或cache满了之后,才同步到磁盘
sync_binlog=n时,每进行n次事务提交后,MySQL进行一次fsync之类的磁盘同步指令、将binlog_cache中的信息刷新到磁盘 - sync_binlog设置为1,更安全
- sync_binlog设置为0,性能最好
c)redo log 和 binlog的区别
redo log 和 binlog都记录了数据真实修改的语句,那他们为何要并存呢?
- 1、记录内容不同
binlog是逻辑日志,记录所有数据的改变信息
redo log是物理日志,记录所有InnoDB表数据的变化 - 2、记录内容的时间不同
binlog记录commit完毕之后的DML和DDL SQL语句
redo log记录事务发起之后的DML和DDL SQL语句 - 3、文件使用方式不同
binlog不是循环使用,在写满或者实例重启之后,会生成新的binlog文件
redo log是循环使用,最后一个文件写满之后,会重新写第一个文件 - 4、作用不同
binlog可以作为恢复数据使用,主从复制搭建
redo log作为异常宕机或者介质故障后的数据恢复使用
d)脏页刷新条件
- 1、重做日志ib_logfile文件写满之后,在切换的过程中会执行checkpoint,会触发脏页的刷新
- 2、innodb_max_dirty_pages_pct参数控制,其含义是在 buffer pool 中 dirty page 所占百分比,达到设置的值,会触发脏页的刷新
- 3、innodb_adaptive_flushing 参数控制,其影响每秒刷新脏页的数目,默认开启
6、InnoDB的三大特性
- 1、插入缓冲
把普通索引上的DML操作从随即I/O变成顺序I/O,提高I/O效率
原理是:先判断插入的普通索引页是否在缓冲池中,在就直接插入,不在就先放到change buffer 中,进行change buffer 和普通索引的合并操作,可将多个插入合并到一个操作中,提高普通索引的插入性能 - 2、两次写(double write)
保证写入的安全性,防止MySQL实例发生宕机的时候,InnoDB发生数据页部分页写的问题,如果页损坏了,redo log 是无法恢复的,所以需要有页的副本,如果实例宕机了,先通过副本把原来的页还原出来,再通过redo log进行恢复、重做 - 3、自适应哈希索引