深入理解MySQL
文章平均质量分 82
本专栏适合于对于系统管理、网络和类 Unix 的操作系统都有一些了解,以及有了一定的 MySQL 基础的MySQL应用开发者、 MySQL数据库管理员。
三月微风
天行健,君子以自强不息;地势坤,君子以厚德载物。
展开
-
深入理解MySQL——master thread分析
在很多论坛上都有对这个问题的讨论,有人甚至将这个值调到了20或10,然后测试发现性能会有所提高,但是将innodb_max_dirty_pages_pct调到20或10会增加磁盘的压力,对系统的负担还是会有所增加。大于innodb_max_dirty_pages_pct时,刷新100个脏页,而innodb_adaptive_flushing参数的引入,InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。原创 2023-01-05 10:01:57 · 449 阅读 · 1 评论 -
MySQL复制底层技术——LOGICAL_CLOCK并行复制、WRITESET并行复制
细心的读者可能已经发现了last_committed=2的两个事务的时间戳并不是同一个时刻的,并且在last_committed=2的两个事务之间还夹了一个last_committed=9的事务,这在以往的LOGICAL_CLOCK 并行复制中几乎不可能出现这种情况。后续 如果有新事务的行记录计算出的hash值在hash表中无匹配记录,那么新事务不会导致产生 新的last_committed值,即相当于新事务和之前的事务被归并到了同一个binlog 队列中 (即,last_committed值相同);原创 2023-01-04 16:37:40 · 1022 阅读 · 2 评论 -
MySQL复制底层技术——单线程复制、DATABASE并行复制
对于从库而言,主要的改进点是在从库复制的SQL线程上增加了一个SQL协调器(Coordinator)线程,真正干活的SQL线程被称之为Worker(工作)线程,当Worker线程数量为N(N>1)以及主库的schema数量为N时,从库就可以根据基于多个schema之间相互独立(彼此之间无锁冲突)的语句来实现并行复制;从理论上讲,一前一后的时差必然会导致从库复制延迟,如果碰到大事务,则会急剧放大从库延迟(例如:主库执行一个大事务耗费1小时完成,从库收到这个事务之后开始执行时,就已经落后于主库1小时了)。原创 2023-01-04 16:25:01 · 563 阅读 · 0 评论 -
MySQL复制技术方案——组复制
前面说过,MGR是基于主从复制基础架构实现的,主要是在事务提交的过程中嵌入单独的binlog封装逻辑,并通过专门的 group_replication_recovery复制通道进行数据传输,组复制插件使用Paxos协议的原子广播特性来保证集群内的大多数节点都能接收到数据包,当节点接收到write set(写集)之后,每个节点上的分布式状态机按照相同的规则对事务进行排序,并进行事务的冲突认证检测。当S1节点崩溃之后,在集群内部重新选举S2作为写节点, 发起写请求的客户端就可以继续对集群发起写请求了。原创 2023-01-04 16:05:58 · 1048 阅读 · 0 评论 -
MySQL复制技术方案——GTID复制配置
如何跳过一个GTID在复制中,偶尔会遇到主键冲突或从库找不到该条记录等错误。那么如何解决呢?在传统的复制模式中,经常通过设置 sql_slave_skip_counter参数跳过一个事件,如下。但是在GTID模式中,如果继续执行上述操作,就会有如下错误产生。在GTID模式的复制情况下,如果SLAVE发生错误,则可以通过跳过该事务的方式恢复主从复制,如图13.1所示。从图13.1中可以看出,出错事务的Binlog文件为mysql-bin.000003,end_log_pos为426,其开始位置为194。原创 2023-01-03 10:59:35 · 1815 阅读 · 1 评论 -
MySQL复制技术方案——半同步复制配置
Google为MySQL和InnoDB设计了一个大规模补丁集以量身打造服务器和存储引擎。其中一个修补程序可用于MySQL5.0版本,是半同步的复制补丁。MySQL已经打上了该补丁并在MySQL5.5中发布了。半同步复制的理念是在允许更改操作继续执行前,确保更改操作至少被写入一个Slave 的磁盘。这意味着对于每一个连接,最多只有一个事务会由于Master崩溃而丢失。重要的是要明白半同步复制补丁没有暂停提交事务,它只是在事务已被写入到至少一个Slave的中继日志中之前,避免发送一个答复给客户端。原创 2022-12-30 11:36:53 · 933 阅读 · 0 评论 -
MySQL复制技术方案——异步复制配置
为MySQL服务器配置复制非常简单。但由于场景不同,基本的步骤还是有所差异。最基本的场景是新安装的主库和备库,总的来说分为以下几步。原创 2022-12-29 17:17:58 · 1039 阅读 · 0 评论 -
深入理解MySQL——分库分表种类与原则
拆分的标准很多,按用户的、按时间的、按用途的,不再一一举例。另一方面,Partitioning的结果受到MySQL实例,或者说MySQL单实例的数据文件无法分布式存储的限制,不管怎么分区,所有的数据还是都在一个服务器上,没办法通过水平扩展物理服务的方法把压力分摊出去。关于TEXT的拆分,这里多说一句,如果一个查询表里存在TEXT或BLOB字段,而且这个查询需要创建内部临时表的话,那它不能使用内存临时表,必须使用磁盘临时表,不然会对性能造成巨大影响,也会占用物理磁盘大量IO,最终导致性能剧烈下降。原创 2022-12-26 14:34:45 · 1707 阅读 · 0 评论 -
深入理解MySQL——理清访问权限控制系统
MySQL的mysql系统库提供了user、db、tables_priv、columns_priv、procs_priv、proxies_priv几个表,用于存放不同权限范围的用户账号相关数据,这些表共同组成了MySQL的访问权限控制系统。 MySQL访问权限控制系统的主要功能是对从给定主机连接到MySQL服务器的用户进行身份验证,并校验该用户在该服务器中的数据库对象访问权限(如SELECT、 INSERT、UPDATE和DELETE)。原创 2022-11-23 09:44:40 · 458 阅读 · 0 评论 -
深入理解MySQL——mysql库中表字段含义
我们先介绍mysql系统库中的权限系统表。 在mysql系统库中,MySQL访问权限系统表包含如下几个表。原创 2022-11-22 17:09:42 · 1916 阅读 · 0 评论 -
深入理解MySQL——配置半同步复制
使用半同步复制要求master和slave都支持,所以master和slave都必须是MySQL5.5 或之后的版本而且启用了半同步复制。原创 2022-07-29 15:31:41 · 383 阅读 · 0 评论 -
深入理解MySQL——中继日志的结构
中继日志是连接master和slave的信息———它是复制的核心。明白如何使用中继日志,以及如何通过中继日志来协调slave线程,这点很重要。原创 2022-07-29 14:17:08 · 1013 阅读 · 0 评论 -
深入理解MySQL——MySQL事务和锁
在关系型数据库管理系统中,一个逻辑工作单元要成为事务,必须满足这 4 个特性,即所谓的 ACID:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。原子性:事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。 修改—>Buffer Pool修改—>刷盘。...原创 2022-06-30 14:47:08 · 229 阅读 · 0 评论 -
深入理解MySQL——哈希索引
哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。对干每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希素引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。......原创 2022-06-09 11:38:29 · 1402 阅读 · 0 评论 -
深入理解MySQL——数据库分区
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起,另外,如果想一次批量删除整个分区的数据也会变得很方便。原创 2022-04-28 16:46:06 · 2369 阅读 · 0 评论 -
深入理解MySQL——MySQL复制的原理
1. 基于语句的复制在 MySQL 5.0及之前的版本中只支持基于语句的复制(也称为逻辑复制),这在数据库领域是很少见的。基于语句的复制模式下,主库会记录那些造成数据更改的查询,当备库读取并重放这些事件时,实际上只是把主库上执行过的 SQL再执行一遍。这种方式既有好处,也有缺点。最明显的好处是实现相当简单。理论上讲,简单地记录和执行这些语句,能够让主备保持同步。另一个好处是二进制日志里的事件更加紧凑,所以相对而言,基于语句的模式不会使用太多带宽。一条更新好几兆数据的语句在二进制日志里可能只占几十个字节。原创 2021-12-01 16:03:26 · 257 阅读 · 0 评论 -
深入理解MySQL——锁的概念
锁的概念锁的并发控制准则1.不应该出错2.性能比单线程要来得快3.更高的吞吐率 >= 单线程事务隔离级别1.read uncommitted 解决脏读2.read committed 不解决不可重复读3.repeatable read 解决幻读4. 序列化得事务隔离级别 serializable适用于分布式隔离级别越低事务请求的锁越少或者保持锁的时间就越短脏读不可重复读两阶段加锁,读和写都加锁幻读:连续执行两次同样的SQL语句可能导致不同的结果,第二次的SQL语句可能会返原创 2021-08-13 14:12:57 · 240 阅读 · 0 评论 -
深入理解MySQL——MySQL跨行事务模型
MySQL事务原子性保证事务原子性要求事务中的一系列操作要么全部完成,要么不做任何操作,不能只做一半。原子性对于原子操作很容易实现,就像HBase中行级事务的原子性实现就比较简单。但对于多条语句组成的事务来说,如果事务执行过程中发生异常,需要保证原子性就只能回滚,回滚到事务开始前的状态,就像这个事务根本没有发生过一样。如何实现呢?MySQL实现回滚操作完全依赖于undo log,多说一句,undo log在MySQL除了用来实现原子性保证之外,还用来实现MVCC,下文也会涉及到。使用undo实现原子性在原创 2022-04-15 09:18:59 · 296 阅读 · 0 评论 -
深入理解MySQL——复制是如何工作的
1. 在主库上把数据更改记录到二进制日志(Binary Log)中(这些记录被称为二进制日志事件)。2. 备库将主库上的日志复制到自己的中继日志(Relay Log)中。 3. 备库读取中继日志中的事件,将其重放到备库数据之上。原创 2022-04-13 17:02:46 · 114 阅读 · 0 评论 -
深入理解MySQL——初识Buffer pool
Buffer Pool在数据库里的地位1、回顾一下Buffer Pool是个什么东西?数据库中的Buffer Pool是个什么东西?其实他是一个非常关键的组件,数据库中的数据实际上最终都是要存放在磁盘文件上的,如下图所示。但是我们在对数据库执行增删改操作的时候,不可能直接更新磁盘上的数据的,因为如果你对磁盘进行随机读写操作,那速度是相当的慢,随便一个大磁盘文件的随机读写操作,可能都要几百毫秒。如果要是那么搞的话,可能你的数据库每秒也就只能处理几百个请求了! 在对数据库执行增删改操作的时候,实际上主要原创 2022-04-12 11:20:41 · 312 阅读 · 0 评论 -
深入理解MySQL——LRU、Free和Flush 链表
首先,缓冲池申请的内存空间一定是页大小(默认16KB)的倍数,换句话说,虽然缓冲池是一块很大的内存区域,然而在使用时是根据固定的页大小进行管理的。如图1-1所示∶缓冲池有一个 free 链表,其中保存着未被使用的内存页空间。当 free 链表中的页都已分配完毕,当再要申请空间时,则需要根据LRU(Latest Recent Used 最近最少使用)算法淘汰已经使用的页。通常来说,数据库中的缓冲池都是通过 LRU(Latest Recent Used 最近最少使用)算法来进行管理的。即最频繁使用的页在原创 2022-04-07 11:57:46 · 2516 阅读 · 0 评论 -
深入理解MySQL——INNODB_LOCKS表和INNODB_LOCK_WAITS表详解
INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX是MYSQL中事务和锁相关的表。通常我们遇到事务超时或锁相关问题时,可以查询这几张表来获取详细信息。原创 2022-03-30 16:07:46 · 3050 阅读 · 0 评论 -
深入理解MySQL——多版本并发控制
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。原创 2022-03-25 10:15:30 · 107 阅读 · 0 评论 -
深入理解MySQL——INNODB_TRX表字段详解
INNODB_TRX 表提供了信息关于在InnoDB中执行的当前的每个事务,包含是否事务是等待一个锁,当事务开始后事务正在执行的SQL语句。原创 2022-03-24 11:59:52 · 3143 阅读 · 0 评论 -
深入理解MySQL——分区表
分区表由多个相关的底层表实现,这些底层表也是由句柄对象(Handler object)表示,所以我们也可以直接访问各个分区。MySQL 支持多种分区表。我们看到最多的是根据范围进行分区,每个分区存储落在某个范围的记录,分区表达式可以是列,也可以是包含列的表达式。理解分区时还可以将其当作索引的最初形态。...原创 2022-03-17 14:50:40 · 1834 阅读 · 0 评论 -
深入理解MySQL——InnoDB事务隔离级别的实现原理
InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。原创 2022-03-14 16:21:59 · 487 阅读 · 3 评论 -
深入理解MySQL——关于checkpoint机制
当重做日志出现不可用时,因为当前事务数据库系统对重做日志的设计都是循环使用的,并不是让其无限增大的,重做日志可以被重用的部分是指这些重做日志已经不再需要,当数据库发生宕机时,数据库恢复操作不需要这部分的重做日志,因此这部分就可以被覆盖重用。如果重做日志还需要使用,那么必须强制Checkpoint,将缓冲池中的页至少刷新到当前重做日志的位置。原创 2022-03-02 14:35:13 · 1895 阅读 · 1 评论 -
深入理解MySQL——InnoDB透明页压缩分析
从MySQL 5.7版本开始,MySQL不仅支持原有的压缩表格式(Table Compression),还支持一种称为透明页压缩的特性(Transparent Page Compression)。通过阅资料和源码,我对这个特性有了一定的了解。以下我将从它的使用方法、实现原理等方面对它进行简单分析,并同压缩表格式进行一些对比。1. 开启方法官方文档对于透明页压缩的特性的说明仅仅一页,主要说明了它的使用方法,我也对这页官方文档进行过翻译,详见:InnoDB Page Compression MySQL文档翻原创 2022-02-28 17:11:01 · 1070 阅读 · 0 评论 -
深入理解MySQL——详细分析MySQL事务日志(redo log和undo log)
innodb事务日志包括redo log和undo log。redo log是重做日志,提供前滚操作,undo log是回滚日志,提供回滚操作。undo log不是redo log的逆向过程,其实它们都算是用来恢复的日志:1.redo log通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)。2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志,根据每行记录进行记录。1.redo原创 2022-01-20 15:27:56 · 458 阅读 · 0 评论 -
深入理解MySQL——配置文件常用变量说明
key_buffer_size 设置这个变量可以一次性为键缓冲区(key buffer,也叫键缓存key cache)分配所有指定的空间。然而,操作系统不会真的立刻分配内存,而是到使用时才真正分配。例如设置键缓冲的大小为1GB,并不意味着服务器立刻分配 1GB的内存。 MySQL 允许创建多个键缓存,这一章后面我们会探讨这个问题。如果把非默认键缓存的这个变量设置为0,MySQL 将丢弃缓存在该键缓存中的索引,转而原创 2022-01-10 11:55:43 · 291 阅读 · 0 评论 -
深入理解MySQL——InnoDB数据页结构
数据页结构的快速浏览页的本质就是一块16KB大小的存储空间,InnoDB为了不同的目的而把页分为不同的类型,其中用于存放记录的页也称为数据页,我们先看看这个用于存放记录的页长什么样。数据页代表的这块16KB大小的存储空间可以被划分为多个部分,不同部分有不同的功能,各个部分如图所示:从图中可以看出,一个InnoDB数据页的存储空间被划分成了7个部分,每个部分又可以被划分为若干小部分。下边我们用表格的方式来大致描述一下这7个部分(快速的瞅一眼就行了,后边会详细唠叨的):名称中文名占用空间大小原创 2021-08-13 11:09:15 · 281 阅读 · 0 评论 -
深入理解MySQL——二进制日志 3 种不同的格式
MySQL 中二进制日志 (binlog) 3 种不同的格式(Mixed,Statement,Row)MySQL 5.5 中对于二进制日志 (binlog) 有 3 种不同的格式可选:Mixed,Statement,Row,默认格式是 Statement。总结一下这三种格式日志的优缺点。MySQL Replication 复制可以是基于一条语句 (Statement Level) ,也可以是基于一条记录 (Row Level),可以在 MySQL 的配置参数中设定这个复制级别,不同复制级别的设置会影响到原创 2021-08-12 11:05:07 · 862 阅读 · 0 评论 -
深入理解MySQL——InnoDB master架构图
原创 2021-12-23 10:22:25 · 649 阅读 · 0 评论 -
深入理解MySQL——逻辑日志
根据每个数据库系统实现的不同,重做日志可分为以下几种类型 ∶物理日志;逻辑日志;物理逻辑日志。 物理日志(physical logging)保存一个页中发生改变的字节,也称这种方式为 old value-new value logging。通常来说,其数据结构可参考下面的实现∶struct value_ log{ int opcode; long page_no; long offset; long length; char old_value[原创 2021-07-30 10:09:31 · 543 阅读 · 0 评论 -
深入理解MySQL——事务上
事务是数据库区别于文件系统的重要特性之一。在文件系统中,如果你正在写文件,但是操作系统突然崩溃了,这个文件就很有可能被破坏。当然,有一些机制可以把文件恢复到某个时间点。不过,如果需要保证两个文件同步,这些文件系统可能就显得无能为力了。如当你需要更新两个文件时,更新完一个文件后,在更新完第二个文件之前系统重启了,你就会有两个不同步的文件。 这正是数据库系统引入事务的主要目的;事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时,可以确保其要么所有修改都已经保存了,要么所有修改都不保存。原创 2021-12-09 14:36:27 · 86 阅读 · 0 评论 -
深入理解MySQL——查看没有创建索引的表
SELECT *FROM information_schema.`TABLES` tLEFT JOIN information_schema.STATISTICS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMAAND t.table_name = s.TABLE_NAMEAND s.INDEX_NAME = 'PRIMARY'WHERE t.TABLE_SCHEMA NOT IN ( 'mysql', 'performance_schema', 'inf原创 2021-08-13 23:37:12 · 642 阅读 · 0 评论 -
深入理解MySQL——Master Thread
InnoDB存储引擎的主要工作都是在一个单独的后台线程master thread中完成的。接下来我将具体解释该线程的具体实现以及该线程可能存在的问题。 master thread的线程优先级别最高。其内部由几个循环(loop)组成∶主循环(loop)、后台循环(background loop)、刷新循环(flush loop)、暂停循环(suspend loop)。master thread会根据数据库运行的状态在loop、background loop、flush loop和suspend loo原创 2021-07-15 11:11:52 · 314 阅读 · 0 评论 -
深入理解MySQL——重做日志
简介 InnoDB 是事务的存储引擎,其通过 force log at commit机制实现事务的持久性。即当事务 commit(提交)时,必须先将该事务的所有日志写入到重做日志文件进行持久化,待完成后事务 commit 操作才算完成,而这里的日志是指重做日志。在 InnoDB 存储引擎中,日志由两部分组成,即 redo log和 undo log。redo log 用来保证事务的持久性,undo log 用来帮助事务回滚以及 MVCC 的功能。redo log 基本上都是顺序写的,原创 2021-07-30 09:43:04 · 245 阅读 · 0 评论