环保网站建设维护情况报告,wordpress网易音乐播放器,公司怎么做网站,福永营销型网站多少钱文章目录 一、前言二、MySQL 文件1. 参数文件2. 日志文件3. 套接字文件4. pid 文件5. 表结构定义文件6. InnoDB 存储引擎文件 二、BTree 索引排序三、InnoDB 关键特性1. 插入缓冲1.1 Insert Buffer 和 Change Buffer1.1 缓冲合并 2. 两次写2. 自适应哈希索引3. 异步IO4. 刷新邻… 文章目录 一、前言二、MySQL 文件1. 参数文件2. 日志文件3. 套接字文件4. pid 文件5. 表结构定义文件6. InnoDB 存储引擎文件 二、BTree 索引排序三、InnoDB 关键特性1. 插入缓冲1.1 Insert Buffer 和 Change Buffer1.1 缓冲合并 2. 两次写2. 自适应哈希索引3. 异步IO4. 刷新邻接页 四、页合并与页分裂1. 页合并2. 页分离3. 危害和避免方式3.1 危害3.2 避免方式 五、InnoDB 的主键生成策略六、参考内容 一、前言
最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》后续会随机将书中部分内容记录下来作为学习笔记部分内容经过个人删改因此可能存在错误如想详细了解相关内容强烈推荐阅读相关书籍。 系列文章内容目录
【MySQL00】【 杂七杂八】【MySQL01】【 Explain 命令详解】【MySQL02】【 InnoDB 记录存储结构】【MySQL03】【 Buffer Pool】【MySQL04】【 redo 日志】【MySQL05】【 undo 日志】【MySQL06】【MVCC】【MySQL07】【锁】【MySQL08】【死锁】 本篇作为一些补充性内容会夹杂各方各面的内容用于记录。
二、MySQL 文件
MySQL数据库和InnoDB存储引擎表的各种类型文件这些文件有以下这些
参数文件告诉MySQL实例启动时在哪里可以找到数据库文件并且指定某些初始化参数这些参数定义了某种内存结构的大小等设置还会介绍各种参数的类型。日志文件用来记录 MySQL实例对某种条件做出响应时写人的文件如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。socket 文件当用 UNIX域套接字方式进行连接时需要的文件。pid 文件MySQL实例的进程ID文件。MySQL 表结构文件用来存放MySQL表结构定义文件。存储引擎文件因为MySQL表存储引擎的关系每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。本篇主要介绍与InnoDB有关的存储引擎文件。
1. 参数文件
MySQL 中的参数可以分为 动态参数 和 静态参数 两类。动态参数意味着可以在 MySQL实例运行中进行更改静态参数说明在整个实例生命周期内都不得进行更改就好像是只读(readonly)的。动态变量可以通过SET命令对动态的参数值进行修改Set 语法如下 这里可以看到 global和 session 关键字它们表明该参数的修改是基于当前会话还是整个实例的生命周期。有些动态参数只能在会话中进行修改如 autocommit而有些参数修改完后在整个实例生命周期中都会生效如 binlog_cachesize而有些参数既可以在会话中又可以在整个实例的生命周期内生效如 readbuffersize。
2. 日志文件
日志文件记录了影响 MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有: 错误日志error log错误日志文件对 MySQL的启动、运行、关闭过程进行了记录。如下可以定位错误日志目录 mysql show variables like log_error;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_error | .\LAPTOP-5F5UIHVC.err |
--------------------------------------
1 row in set (0.07 sec)慢查询日志show query logMySQL 会将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。默认情况下 MySQL并不启动慢查询日志功能相关参数如下 # 查看是否启用 慢查询日志
mysql show variables like slow_query_log;
-----------------------
| Variable_name | Value |
-----------------------
| slow_query_log | ON |
-----------------------
1 row in set, 1 warning (0.00 sec)# 慢查询日志条件查询时间 大于 10s 才会被计入慢查询日志
mysql show variables like long_query_time;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
1 row in set, 1 warning (0.00 sec)MySQL 5.1 版本后慢查询日志会放入 mysql 架构下的 slow_log 表中可以通过该表查看慢日志信息。 查询日志log查询日志记录了所有对 MySQL数据库请求的信息无论这些请求是否得到了正确的执行。默认文件名为:主机名.log 二进制日志binlog二进制日志记录了对 MySQL,数据库执行更改的所有操作但是不包括 SELECT和 SHOW 这类操作因为这类操作对数据本身并没有修改。但若是其他操作即使本身并没有导致数据库发生变化那么该操作可能也会写人二进制日志比如 UPDATE 或 DELETE 语句即使没有作用于任匹配何记录也会被记录。( 查看二进制文件需要通过 MySQL 提供的工具 mysqlbinlog 工具) 通过配置参数 log-bin[name]可以启动二进制日志。如果不指定 name,则默认进制日志文件名为主机名后缀名为二进制日志的序列号所在路径为数据库所在目录如下: mysql show variables like datadir;
------------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------------
| datadir | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)从 MySQL 5.1 版本开始二进制日志文件增加了 binlog_format 参数它影响了记录二进制日志的格式。在 MySQL5.1版本之前没有这个参数,所有二进制文件的格式都是基于SQL语句(statement)级别的binlog_format 可设的值有三种 STATEMENT和之前的MySQL版本一样二进制日志文件记录的是日志的逻辑 SOL 语句ROW二进制日志记录的不再是简单的 SQL语句了而是记录表的行更改情况。如果设置了 binlogformat 为ROW可以将InnoDB的事务隔离基本设为 READ COMMITTED以获得更好的并发性。MIXED在MIXED格式下MySQL默认采用 STATEMENT格式进行二进制日志文件的记录但是在一些情况下会使用 ROW格式情况如下 表的存储引擎为 NDB这时对表的 DML操作都会以 ROW 格式记录。使用了 UUIDO、USERO、CURRENT USERO、FOUND ROWSO、ROW_COUNTO等不确定函数。使用了 INSERT DELAY 语句。使用了用户定义函数(UDF)。使用了临时表(temporary table)。
3. 套接字文件
在 UNIX系统下本地连接MySQL可以采用 UNIX域套接字方式这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp目录下名为 mysql.sock。如下
mysql show variables like socket;
----------------------
| Variable_name | Value |
----------------------
| socket | MySQL |
----------------------
1 row in set, 1 warning (0.00 sec)4. pid 文件
当 MySQL实例启动时会将自己的进程ID写入一个文件中–该文件即为 pid 文件。该文件可由参数 pid 6le控制默认位于数据库目录下文件名为主机名.pid 如下
mysql show variables like pid_file;
------------------------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------------------------
| pid_file | C:\ProgramData\MySQL\MySQL Server 5.7\Data\KingFish.pid |
------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)5. 表结构定义文件
因为 MySQL插件式存储引擎的体系结构的关系MySQL数据的存储是根据表进行的每个表都会有与之对应的文件。但不论表采用何种存储引擎MySQL都有一个以 frm 为后缀名的文件这个文件记录了该表的表结构定义。
使用 InnoDB 存储引擎的表底层会对应2个文件在文件夹中进行数据存储如下
.frm 文件frame存储表结构。.ibd 文件InnoDB Data存储表索引数据。
使用 MyISAM 存储引擎的表底层会对应2个文件在文件夹中进行数据存储。如下
.frm 文件frame存储表结构。.MYD 文件MY Data存储表数据。.MYI 文件MY Index存储表索引。
6. InnoDB 存储引擎文件
InnoDB 存储引擎文件主要包括 表空间文件和重做日志文件该内容详参【MySQL04】【 redo 日志】。
二、BTree 索引排序
在一般情况下在需要排序的时候我们只能将记录加载到内存中然后再通过一些排序算法在内存中进行排序。有时候查询的结果集可能太大导致无法再内存中进行排序此时就需要借助磁盘存放中间结果在排序操作完成后再把排好序的结果集返回给客户端。而在 MySQL 中这种在内存或磁盘中进行排序的方式统称为文件排序filesort但是如果 order by 字句中使用了索引列就可能省去在内存或磁盘中排序的步骤。
通过 explain 命令 的 Extra 列可以看到是否使用了文件排序。如下
mysql explain select * from t1 order by c;
--------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using filesort |
--------------------------------------------------------------------------------------------------------------
1 row in set (0.04 sec)注意如下使用索引排序失效的情况
使用联合索引进行排序时也需要遵循最左匹配原则。使用联合索引进行排序时 ASC、DESC 混用时无法使用索引进行排序。排序列包含多个非同一索引的列也无法使用索引进行排序。查询列和排序列并不是同一索引列也会导致无法使用索引进行排序。排序列不能被函数修饰。
三、InnoDB 关键特性
1. 插入缓冲
1.1 Insert Buffer 和 Change Buffer
在 InnoDB 中 主键是行唯一标识符通常数据按照主键递增顺序插入因此插入聚簇索引一般是顺序的不需要磁盘随机读写。并非所有的都是顺序如果主键用 UUID 或者自己手动插入了指定的id值都不是顺序的。而对于非聚簇索引来说插入的顺序性则无法得到保证如果每次数据更新或插入都随之更新辅助索引因为辅助索引的离散性效率会很低。
因此 InnoDB 设计了 Insert Buffer 来处理这种情况对 辅助索引 的插入或更新操作不是每一次直接插入到索引页的 先判断插入的辅助索引页是否在缓冲池中若在直接插入否则先放入到一个 Insert Buffer 对象中好似欺骗数据库这个辅助索引已经插入到叶子节点 再以一定频率和情况进行 Insert Buffer 和 辅助索引页子节点的合并操作这时候通常可以将多个插入操作合并到一个操作中因为在一个索引页中大大提高了辅助索引的插入性能。需要注意Insert Buffer 不仅仅存在在缓冲池中在物理页磁盘上也存在。
Insert Buffer 的实现是 BTree MySQL 4.1 前每个表有一颗 Insert Buffer BTree MySQL 4.1后公用一个 Insert Buffer BTree存放在共享表空间中默认 ibdata1中。
在 InnoDB 1.0.x 版本开始引入了Change BufferChange Buffer可以认为是 Insert Buffer 升级版。从这个版本开始InnoDB 可以对 DML 操作都进行缓冲 Insert Buffer、Delete Buffer、Purge Buffer。
使用 Insert Buffer 和 Change Buffer 需要满足下面两个条件
索引是辅助索引索引不是唯一的 : 因为在插入缓冲时数据库并不去查找索引页来判断插入记录的唯一性如果要求唯一则DB需要查找必定要去离散读取确定数据唯一性这样 插入缓冲就失去了意义。
通过 SHOW ENGINE INNODB STATUS 命令可以查看 Change Buffer 的信息如下
... 忽略部分内容
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
# size 代表已经合并记录页的数量, free list len 代表空闲列表的长度, seg size 代表当前 insert buffer的大小为 194 * 16KB, merges 代表合并的次数也就是实际读取页的次数。
Ibuf: size 1, free list len 192, seg size 194, 13726 merges
# 合并的具体操作次数
merged operations:insert 4582, delete mark 2511869, delete 1740
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 77 buffer(s)
Hash table size 276707, node heap has 515 buffer(s)
Hash table size 276707, node heap has 149 buffer(s)
Hash table size 276707, node heap has 101 buffer(s)
Hash table size 276707, node heap has 65 buffer(s)
Hash table size 276707, node heap has 43 buffer(s)
Hash table size 276707, node heap has 83 buffer(s)
Hash table size 276707, node heap has 642 buffer(s)
993.49 hash searches/s, 54.42 non-hash searches/s
---
LOG
---
... 忽略部分内容1.1 缓冲合并
Insert Buffer BTree 合并到 缓冲池中的场景 这里要注意这里会将Insert Buffer BTree 的数据从磁盘合并到缓冲池中而缓冲池会根据上面的逻辑进行自动刷新到磁盘
辅助索引页被读取到缓冲池时 如当执行Select查询时需要检查 Insert Buffer Bitmap 页确认该辅助索引页是否有记录存放于 Insert Buffer BTree 中有则将 Insert Buffer BTree 中该页的记录插入到该辅助索引页中。Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时 Insert Buffer Bitmap 至少保证要有 1/32 页的空间若插入辅助索引记录时检测到插入记录后的可用空间小于 1/32 页则会强制进行一个合并操作即强制读取辅助索引页将 Insert Buffer BTree 中该页的记录及待插入的记录插入到辅助索引页中。Master Thread 主线程每秒或每十秒进行一次 Merge Insert Buffer操作不同的是每次进行 merge 操作的页的数量不同。
2. 两次写
Insert Buffer 是性能提升 double write 则是提高数据页的可靠性。
当数据库宕机时可能 InnoDB 正在写入某个页到表中而这个页仅写了部分比如 16KB 的页只写了前 4KB之后就发生了宕机这种情况被称为部分写失效partial page write这种情况下即使通过重做日志页并不一定能恢复数据因为重做日志时对页的物理操作如果页本身已经损坏再重做也无意义。这就是说在应用重做日志前用户需要一个页的副本当写入失效发生时先通过页的副本来还原该页再进行重做即 double write。
double write 由两部分组成一部分是 内存中的 doublewrite buffer另一部分是磁盘上共享表空间中连续的128个页即两个区大小都是2MB。InnoDB 在对缓冲池的脏页进行刷新时并不直接写磁盘而是会先通过 memcpy 函数将脏页复制到内存中的 doublewrite buffer之后通过 doublewrite buffer 再分两次每次1 MB顺序写入到共享表空间的磁盘上然后马上调用 fsync 函数同步磁盘避免缓冲写带来的问题。因为这个过程 doublewrite 是连续的因此写入时顺序写入开销不大在完成 doublewrite 页的写入后再将 doublewrite buffer 中的页写入各个表空间文件中此时的写是离散的。 通过两次写机制当数据库在页写入一半是发生宕机时则在恢复过程中可以先从 共享表空间中 的 doublewrite 中找到该页的一个副本将其复制到表空间文件再应用重做日志。
2. 自适应哈希索引
InnoDB 会监控对表上各索引页的查询如果判断建立哈希索引可以提升速度BTree 树的高度一般是 3-4层所以需要3-4次查询而哈希一般情况下时间复杂度为 O(1)只需要一次查询则会建立哈希索引成为自适应哈希索引Adaptive Hash IndexAHI。
AHI 是通过缓冲池的 BTree 页构建而成因此建立速度很快不需要对全表构建哈希。这里的哈希索引是针对热点页建立的其建立是存在如下要求如下 对于建立索引的页的连续访问模式 (也就是查询的条件) 必须是一样的。如对于 (a,b) 的联合索引页其访问模式可以是下面的情况 where a xxxwhere a xxx and b yyy 如果交替进行上述两种查询则不会对该页构建 AHI 以该模式访问了 100次 页通过该模式访问了 N 次其中 N 页中记录* (1/16)
3. 异步IO
AIO Async IOAIO 除了可并发外还可以进行 IO Merge 操作如用户访问页space, page_no 为86、87、88 每个页大小为 16KB同步IO需要进行三次 IO而 AIO则会通过 space, page_no)知道这三个页是连续的AIO 底层会发起一个IO 从求读取从(8,6) 开始读取 48KB 的页。在 InnoDB 中 read ahead方式的读取、脏页的刷新即磁盘写入操作全都是由 AIO 完成。
4. 刷新邻接页
Flush Neighbor Page 其工作原理是 当刷新一个脏页时 InnoDB 会检测该页所在区(extent)的所有页如果是脏页则一起刷新这么做的好处是可以通过 AIO 将多个 IO 写入合并为一个 IO 操作。
四、页合并与页分裂
页可以空或者填充满100%行记录会按照主键顺序来排列。例如在使用AUTO_INCREMENT时你会有顺序的ID 1、2、3、4等 页还有另一个重要的属性MERGE_THRESHOLD。该参数的默认值是50%页的大小它在InnoDB的合并操作中扮演了很重要的角色。 当你插入数据时如果数据大小能够放的进页中的话那他们是按顺序将页填满的。
若当前页满则下一行记录会被插入下一页NEXT中。
1. 页合并
页合并是指将两个相邻的索引页面合并成一个更大的页面减少b树的层级提高查询性能。
在InnoDB 中记录的删除会经历下面两个阶段所使用的空间不会被回收而是被标记可重用(详参【MySQL05】【 undo 日志】
delete mark 阶段 : 将记录的 deleted_flag 标志位置为 1同时修改记录的 trx_id、roll_pointer 的值。在删除语句所在的事务提交之前被删除的记录一直都处于这种中间状态。purge 阶段当删除该语句所在的事务提交之后会有专门的线程来将记录真正的删除掉这里所谓真正的删除就是将该记录从正常链表中移除并且加到垃圾链表中这里是加入到链表的头节点并修改 PAGE_FREE 指向新删除的记录除此之外还会修改一些页面属性。
因此在 InnoDB中当索引页面中的索引记录被删除后页面可能会变得过于稀疏这时为了节省空间和提高性能可能会触发页合并操作。
2. 页分离
页分裂是指将该页面中的一部分索引记录移动到另一个新的页面中从而为新纪录腾出空间这样可以保持b树的平衡和性能。
当我们使用 UUID 作为主键 或指定主键插入记录时 记录的插入可能是乱序的这就会导致插入到 BTree 时可能是无序的因此新的记录也可能会插入到老的 Btree 节点中如果老的 BTree 节点 没有足够的空间分配则会进行页分裂操作即会创建一个新的叶子节点并将老叶子节点的部分记录移动到新的节点中并将新节点插入到老叶子节点中。
如 BTree 存在两个相邻的叶子节点 AB并且两个页面都已经没有剩余空间AB 节点之间通过双向链表连接即 A — B。A 节点中保存主键为 123 的记录 B 节点中保存主键为 567的记录此时如果新插入一条 主键为 4 的记录那么该条记录应该保存在A 节点尾部或B节点头部的位置但是由于 AB节点没有多余的存储空间此时会触发页分裂即会重新创建一个叶子节点 C并将 AB 节点中的部分记录移动到 C 节点中并且 C 节点会插入到 AB节点中。此时 A、B、C 三个节点的关联关系是 A — C — B
3. 危害和避免方式
3.1 危害
页分裂和页合并涉及大量数据移动和重组频繁进行这些操作会增加数据库的消耗影响数据库整体性能。页分裂和页合并可能导致b树索引结构频繁调整这会影响插入和删除的性能。
3.2 避免方式
主键自增可以很大程度上避免页分裂。使用批量插入代替单条插入这样可以减少页分裂的次数。频繁物理删除可能导致页面过于稀疏引起页合并所以一般建议使用逻辑删除。
五、InnoDB 的主键生成策略
在 InnoDB 中每张表都有个主键如果在建表时没有显示定义主键在按照下面方式定义
判断表中是否有非空的唯一索引如果有则该列为主键。当表中有多个非空唯一索引时 InnoDB会选择建表时的第一个定义的非空唯一索引作为主键这里需要注意主键的选择是根据定义索引的顺序而非建表时列的顺序。如果不存在非空唯一索引则使用 row_id 作为主键。(row_id 是 InnoDB 为每行记录生成的隐藏字段) 这里介绍下 row_id 的赋值方式
服务器会在内存中维护一个全局变量每当向这个包含 row_id 隐藏列的表中插入一条记录时就会把这个全局变量的值当做新纪录的 row_id 列的值并且把这个全局变量自增1。每当这个全局变量的值是 256 的倍数是就会将该变量刷新到系统表空间页号为7的页面中名为 Max Row ID 的属性中。之所以不是每次自增该全局变量就刷新到磁盘是为了避免频繁刷盘当系统启动时会将这个 Max Row ID 属性加载到内存中并将该值加上 256 之后赋值给前面提到的全局变量因为在上次系统关机后最新的全局变量值可能还没刷新到磁盘。
六、参考内容
https://blog.csdn.net/csdn_life18/article/details/135125100 https://blog.csdn.net/m0_61505483/article/details/139169842
