2025年1月18日 22:29 周六第5章 盛放记录的大盒子-InnoDB数据页结构
不同类型的页简介
#
前面我们简单提了一下页
的概念,它是InnoDB
管理存储空间的基本单位,一个页的大小一般是16KB
。InnoDB
为了不同的目的而设计了许多种不同类型的页
,比如存放表空间头部信息的页,存放Insert Buffer
信息的页,存放INODE
信息的页,存放undo
日志信息的页等等等等。当然了,如果我说的这些名词你一个都没有听过,就当我放了个屁吧~ 不过这没有一毛钱关系,我们今儿个也不准备说这些类型的页,我们聚焦的是那些存放我们表中记录的那种类型的页,官方称这种存放记录的页为索引(INDEX
)页,鉴于我们还没有了解过索引是个什么东西,而这些表中的记录就是我们日常口中所称的数据
,所以目前还是叫这种存放记录的页为数据页
吧。
数据页结构的快速浏览
#
数据页代表的这块16KB
大小的存储空间可以被划分为多个部分,不同部分有不同的功能,各个部分如图所示:

从图中可以看出,一个InnoDB
数据页的存储空间大致被划分成了7
个部分,有的部分占用的字节数是确定的,有的部分占用的字节数是不确定的。下面我们用表格的方式来大致描述一下这7个部分都存储一些什么内容(快速的瞅一眼就行了,后边会详细介绍的):
名称 中文名 占用空间大小 简单描述 File Header
文件头部 38
字节 页的一些通用信息 Page Header
页面头部 56
字节 数据页专有的一些信息 Infimum + Supremum
最小记录和最大记录 26
字节 两个虚拟的行记录 User Records
用户记录 不确定 实际存储的行记录内容 Free Space
空闲空间 不确定 页中尚未使用的空间 Page Directory
页面目录 不确定 页中的某些记录的相对位置 File Trailer
文件尾部 8
字节 校验页是否完整
小贴士:我们接下来并不打算按照页中各个部分的出现顺序来依次介绍它们,因为各个部分中会出现很多大家目前不理解的概念,这会打击各位读文章的信心与兴趣,希望各位能接受这种拍摄手法~
记录在页中的存储
#
在页的7个组成部分中,我们自己存储的记录会按照我们指定的行格式
存储到User Records
部分。但是在一开始生成页的时候,其实并没有User Records
这个部分,每当我们插入一条记录,都会从Free Space
部分,也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records
部分,当Free Space
部分的空间全部被User Records
部分替代掉之后,也就意味着这个页使用完了,如果还有新的记录插入的话,就需要去申请新的页了,这个过程的图示如下:

为了更好的管理在User Records
中的这些记录,InnoDB
可费了一番力气呢,在哪费力气了呢?不就是把记录按照指定的行格式一条一条摆在User Records
部分么?其实这话还得从记录行格式的记录头信息
中说起。
...
2025年1月18日 22:29 周六第4章 从一条记录说起-InnoDB记录结构
准备工作
#
到现在为止,MySQL
对于我们来说还是一个黑盒,我们只负责使用客户端发送请求并等待服务器返回结果,表中的数据到底存到了哪里?以什么格式存放的?MySQL
是以什么方式来访问的这些数据?这些问题我们统统不知道,对于未知领域的探索向来就是社会主义核心价值观中的一部分,作为新一代社会主义接班人,不把它们搞懂怎么支援祖国建设呢?
我们前面介绍请求处理过程的时候提到过,MySQL
服务器上负责对表中数据的读取和写入工作的部分是存储引擎
,而服务器又支持不同类型的存储引擎,比如InnoDB
、MyISAM
、Memory
什么的,不同的存储引擎一般是由不同的人为实现不同的特性而开发的,真实数据在不同存储引擎中存放的格式一般是不同的,甚至有的存储引擎比如Memory
都不用磁盘来存储数据,也就是说关闭服务器后表中的数据就消失了。由于InnoDB
是MySQL
默认的存储引擎,也是我们最常用到的存储引擎,我们也没有那么多时间去把各个存储引擎的内部实现都看一遍,所以本集要介绍的是使用InnoDB
作为存储引擎的数据存储结构,了解了一个存储引擎的数据存储结构之后,其他的存储引擎都是依葫芦画瓢,等我们用到了再说。
InnoDB页简介
#
InnoDB
是一个将表中的数据存储到磁盘上的存储引擎,所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的,所以需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB
存储引擎需要一条一条的把记录从磁盘上读出来么?不,那样会慢死,InnoDB
采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16 KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。
InnoDB行格式
#
我们平时是以记录为单位来向表中插入数据的,这些记录在磁盘上的存放方式也被称为行格式
或者记录格式
。设计InnoDB
存储引擎的大佬们到现在为止设计了4种不同类型的行格式
,分别是Compact
、Redundant
、Dynamic
和Compressed
行格式,随着时间的推移,他们可能会设计出更多的行格式,但是不管怎么变,在原理上大体都是相同的。
指定行格式的语法
#
我们可以在创建或修改表的语句中指定行格式
: ``` CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称 比如我们在
xiaohaizi数据库里创建一个演示用的表
record_format_demo,可以这样指定它的
行格式:
mysql> USE xiaohaizi; Database changed
mysql> CREATE TABLE record_format_demo ( -> c1 VARCHAR
CHARSET=ascii ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.03 sec) 可以看到我们刚刚创建的这个表的
行格式就是
Compact,另外,我们还显式指定了这个表的字符集为
ascii,因为
ascii字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符,所以我们的汉字是不能存到这个表里的。我们现在向这个表中插入两条记录:
mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES(‘aaaa’, ‘bbb’, ‘cc’, ’d’), (’eeee’, ‘fff’, NULL, NULL); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 现在表中的记录就是这个样子的:
mysql> SELECT * FROM record_format_demo; +——+—–+——+——+ | c1 | c2 | c3 | c4 | +——+—–+——+——+ | aaaa | bbb | cc | d | | eeee | fff | NULL | NULL | +——+—–+——+——+ 2 rows in set (0.00 sec)
...
2025年1月18日 22:29 周六第3章 乱码的前世今生-字符集和比较规则
字符集和比较规则简介
#
字符集简介
#
我们知道在计算机中只能存储二进制数据,那该怎么存储字符串呢?当然是建立字符与二进制数据的映射关系了,建立这个关系最起码要搞清楚两件事儿:
- 你要把哪些字符映射成二进制数据? 也就是界定清楚字符范围。
- 怎么映射? 将一个字符映射成一个二进制数据的过程也叫做
编码
,将一个二进制数据映射到一个字符的过程叫做解码
。
人们抽象出一个字符集
的概念来描述某个字符范围的编码规则。比方说我们来自定义一个名称为xiaohaizi
的字符集,它包含的字符范围和编码规则如下:
包含字符'a'
、'b'
、'A'
、'B'
。
编码规则如下:
采用1个字节编码一个字符的形式,字符和字节的映射关系如下: 'a' -> 00000001 (十六进制:0x01) 'b' -> 00000010 (十六进制:0x02) 'A' -> 00000011 (十六进制:0x03) 'B' -> 00000100 (十六进制:0x04)
有了xiaohaizi
字符集,我们就可以用二进制形式表示一些字符串了,下面是一些字符串用xiaohaizi
字符集编码后的二进制表示: 'bA' -> 0000001000000011 (十六进制:0x0203) 'baB' -> 000000100000000100000100 (十六进制:0x020104) 'cd' -> 无法表示,字符集xiaohaizi不包含字符'c'和'd'
比较规则简介
#
在我们确定了xiaohaizi
字符集表示字符的范围以及编码规则后,怎么比较两个字符的大小呢?最容易想到的就是直接比较这两个字符对应的二进制编码的大小,比方说字符'a'
的编码为0x01
,字符'b'
的编码为0x02
,所以'a'
小于'b'
,这种简单的比较规则也可以被称为二进制比较规则,英文名为binary collation
。
二进制比较规则是简单,但有时候并不符合现实需求,比如在很多场合对于英文字符我们都是不区分大小写的,也就是说'a'
和'A'
是相等的,在这种场合下就不能简单粗暴的使用二进制比较规则了,这时候我们可以这样指定比较规则:
- 将两个大小写不同的字符全都转为大写或者小写。
- 再比较这两个字符对应的二进制数据。
这是一种稍微复杂一点点的比较规则,但是实际生活中的字符不止英文字符一种,比如我们的汉字有几万之多,对于某一种字符集来说,比较两个字符大小的规则可以制定出很多种,也就是说同一种字符集可以有多种比较规则,我们稍后就要介绍各种现实生活中用的字符集以及它们的一些比较规则。
一些重要的字符集
#
不幸的是,这个世界太大了,不同的人制定出了好多种字符集
,它们表示的字符范围和用到的编码规则可能都不一样。我们看一下一些常用字符集的情况:
ASCII
字符集
共收录128个字符,包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符,所以可以使用1个字节来进行编码,我们看一些字符的编码方式: 'L' -> 01001100(十六进制:0x4C,十进制:76) 'M' -> 01001101(十六进制:0x4D,十进制:77)
ISO 8859-1
字符集
...
2025年1月18日 22:29 周六第2章 MySQL的调控按钮-启动选项和系统变量
如果你用过手机,你的手机上一定有一个设置的功能,你可以选择设置手机的来电铃声、设置音量大小、设置解锁密码等等。假如没有这些设置功能,我们的生活将置于尴尬的境地,比如在图书馆里无法把手机设置为静音,无法把流量开关关掉以节省流量,在别人得知解锁密码后无法更改密码~ MySQL
的服务器程序和客户端程序也有很多设置项,比如对于MySQL
服务器程序,我们可以指定诸如允许同时连入的客户端数量、客户端和服务器通信方式、表的默认存储引擎、查询缓存的大小等设置项。对于MySQL
客户端程序,我们之前已经见识过了,可以指定需要连接的服务器程序所在主机的主机名或IP地址、用户名及密码等信息。
这些设置项一般都有各自的默认值,比方说服务器允许同时连入的客户端的默认数量是151
,表的默认存储引擎是InnoDB
,我们可以在程序启动的时候去修改这些默认值,对于这种在程序启动时指定的设置项也称之为启动选项(startup options),这些选项控制着程序启动后的行为。在MySQL
安装目录下的bin
目录中的各种可执行文件,不论是服务器相关的程序(比如mysqld
、mysqld_safe
)还是客户端相关的程序(比如mysql
、mysqladmin
),在启动的时候基本都可以指定启动参数。这些启动参数可以放在命令行中指定,也可以把它们放在配置文件中指定。下面我们以mysqld
为例,来详细介绍指定启动选项的格式。需要注意的一点是,我们现在要介绍的是设置启动选项的方式,下面出现的启动选项不论大家认不认识,先不用去纠结每个选项具体的作用是什么,之后我们会对一些重要的启动选项详细介绍。
在命令行上使用选项
#
如果我们在启动客户端程序时在-h
参数后边紧跟服务器的IP地址,这就意味着客户端和服务器之间需要通过TCP/IP
网络进行通信。因为我的客户端程序和服务器程序都装在一台计算机上,所以在使用客户端程序连接服务器程序时指定的主机名是127.0.0.1
的情况下,客户端进程和服务器进程之间会使用TCP/IP
网络进行通信。如果我们在启动服务器程序的时候就禁止各客户端使用TCP/IP
网络进行通信,可以在启动服务器程序的命令行里添加skip-networking
启动选项,就像这样: mysqld --skip-networking
可以看到,我们在命令行中指定启动选项时需要在选项名前加上--
前缀。另外,如果选项名是由多个单词构成的,它们之间可以由短划线-
连接起来,也可以使用下划线_
连接起来,也就是说skip-networking
和skip_networking
表示的含义是相同的。所以上面的写法与下面的写法是等价的: mysqld --skip_networking
在按照上述命令启动服务器程序后,如果我们再使用mysql
来启动客户端程序时,再把服务器主机名指定为127.0.0.1
(IP地址的形式)的话会显示连接失败: ``` mysql -h127.0.0.1 -uroot -p Enter password:
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61) ``` 这就意味着我们指定的启动选项skip-networking
生效了!
再举一个例子,我们前面说过如果在创建表的语句中没有显式指定表的存储引擎的话,那就会默认使用InnoDB
作为表的存储引擎。如果我们想改变表的默认存储引擎的话,可以这样写启动服务器的命令行: mysqld --default-storage-engine=MyISAM
我们现在就已经把表的默认存储引擎改为MyISAM
了,在客户端程序连接到服务器程序后试着创建一个表: mysql> CREATE TABLE sys_var_demo( -> i INT -> ); Query OK, 0 rows affected (0.02 sec)
这个定义语句中我们并没有明确指定表的存储引擎,创建成功后再看一下这个表的结构: mysql> SHOW CREATE TABLE sys_var_demo\G *************************** 1. row *************************** Table: sys_var_demo Create Table: CREATE TABLE
sys_var_demo(
i int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.01 sec)
可以看到该表的存储引擎已经是MyISAM
了,说明启动选项default-storage-engine
生效了。
...
2025年1月18日 22:29 周六第1章 装作自己是个小白-重新认识MySQL
MySQL的客户端/服务器架构
#
以我们平时使用的微信为例,它其实是由两部分组成的,一部分是客户端程序,一部分是服务器程序。客户端可能有很多种形式,比如手机APP,电脑软件或者是网页版微信,每个客户端都有一个唯一的用户名,就是你的微信号,另一方面,腾讯公司在他们的机房里运行着一个服务器软件,我们平时操作微信其实都是用客户端来和这个服务器来打交道。比如狗哥用微信给猫爷发了一条消息的过程其实是这样的:
- 消息被客户端包装了一下,添加了发送者和接收者信息,然后从狗哥的微信客户端传送给微信服务器;
- 微信服务器从消息里获取到它的发送者和接收者,根据消息的接收者信息把这条消息送达到猫爷的微信客户端,猫爷的微信客户端里就显示出狗哥给他发了一条消息。
MySQL
的使用过程跟这个是一样的,它的服务器程序直接和我们存储的数据打交道,然后可以有好多客户端程序连接到这个服务器程序,发送增删改查的请求,然后服务器就响应这些请求,从而操作它维护的数据。和微信一样,MySQL
的每个客户端都需要提供用户名密码才能登录,登录之后才能给服务器发请求来操作某些数据。我们日常使用MySQL
的情景一般是这样的:
- 启动
MySQL
服务器程序。 - 启动
MySQL
客户端程序并连接到服务器程序。 - 在客户端程序中输入一些命令语句作为请求发送到服务器程序,服务器程序收到这些请求后,会根据请求的内容来操作具体的数据并向客户端返回操作结果。
我们知道计算机很牛逼,在一台计算机上可以同时运行多个程序,比如微信、QQ、音乐播放器、文本编辑器等,每一个运行着的程序也被称为一个进程
。我们的MySQL
服务器程序和客户端程序本质上都算是计算机上的一个进程
,这个代表着MySQL
服务器程序的进程也被称为MySQL数据库实例
,简称数据库实例
。
每个进程都有一个唯一的编号,称为进程ID
,英文名叫PID
,这个编号是在我们启动程序的时候由操作系统随机分配的,操作系统会保证在某一时刻同一台机器上的进程号不重复。比如你打开了计算机中的QQ程序,那么操作系统会为它分配一个唯一的进程号,如果你把这个程序关掉了,那操作系统就会把这个进程号回收,之后可能会重新分配给别的进程。当我们下一次再启动 QQ程序的时候分配的就可能是另一个编号。每个进程都有一个名称,这个名称是编写程序的人自己定义的,比如我们启动的MySQL
服务器进程的默认名称为mysqld
, 而我们常用的MySQL
客户端进程的默认名称为mysql
。
MySQL的安装
#
不论我们通过下载源代码自行编译安装的方式,还是直接使用官方提供的安装包进行安装之后,MySQL
的服务器程序和客户端程序都会被安装到我们的机器上。不论使用上述两者的哪种安装方式,一定一定一定(重要的话说三遍)要记住你把MySQL
安装到哪了,换句话说,一定要记住MySQL
的安装目录。 小贴士:
MySQL的大部分安装包都包含了服务器程序和客户端程序,不过在Linux下使用RPM包时会有单独的服务器RPM包和客户端RPM包,需要分别安装。
另外,MySQL
可以运行在各种各样的操作系统上,我们后边会讨论在类UNIX
操作系统和Windows
操作系统上使用的一些差别。为了方便大家理解,我在macOS
操作系统(苹果电脑使用的操作系统)和Windows
操作系统上都安装了MySQL
,它们的安装目录分别是:
macOS
操作系统上的安装目录: /usr/local/mysql/
Windows
操作系统上的安装目录: C:\Program Files\MySQL\MySQL Server 5.7
下面我会以这两个安装目录为例来进一步扯出更多的概念,不过一定要注意,这两个安装目录是我的运行不同操作系统的机器上的安装目录,一定要记着把下面示例中用到安装目录的地方替换为你自己机器上的安装目录。 小贴士:类UNIX操作系统非常多,比如FreeBSD、Linux、macOS、Solaris等都属于UNIX操作系统的范畴,我们这里使用macOS操作系统代表类UNIX操作系统来运行MySQL。
bin目录下的可执行文件
#
在MySQL
的安装目录下有一个特别特别重要的bin
目录,这个目录下存放着许多可执行文件,以macOS
系统为例,这个bin
目录的绝对路径就是(在我的机器上): /usr/local/mysql/bin
我们列出一些在macOS
中这个bin
目录下的一部分可执行文件来看一下(文件太多,全列出来会刷屏的): . ├── mysql ├── mysql.server -> ../support-files/mysql.server ├── mysqladmin ├── mysqlbinlog ├── mysqlcheck ├── mysqld ├── mysqld_multi ├── mysqld_safe ├── mysqldump ├── mysqlimport ├── mysqlpump ... (省略其他文件) 0 directories, 40 files
Windows
中的可执行文件与macOS
中的类似,不过都是以.exe
为扩展名的。这些可执行文件都是与服务器程序和客户端程序相关的,后边我们会详细介绍一些比较重要的可执行文件,现在先看看执行这些文件的方式。
...
2025年1月18日 22:29 周六第0章 万里长征第一步(非常重要)-如何愉快的阅读本小册
购买前警告⚠️
#
- 此小册并非数据库入门书籍,需要各位知道增删改查是什么意思,并且能用 SQL 语言写出来,当然并不要求各位知道的太多,你甚至可以不知道连接的语法都可以。不过如果你连
SELECT
、INSERT
这些单词都没听说过那本小册并不适合你。 - 此小册非正经科学专著,亦非十二五国家级规划教材,也没有大段代码和详细论证,有的全是图,喜欢正经论述的同学请避免购买本小册。
- 此小册作者乃一无业游民,非专业大佬,没有任何职称,只是单单喜欢把复杂问题讲清楚的那种快感,所以喜欢作者有 Google、Facebook 高级开发工程师,二百年工作经验等 Title 的同学请谨慎购买。
- 此小册是用于介绍 MySQL 的工作原理以及对我们程序猿的影响,并不是介绍概念设计、逻辑设计、物理设计、范式化之类的数据库设计方面的知识,希望了解上述这些知识的同学来错地方了。
- 文章标题中的“从根儿上理解MySQL”**其实是专门雇了 UC 震惊部小编起的,纯属为了吸引大家眼球。严格意义上说,本书只是介绍
MySQL
内核的一些核心概念的小白进阶书籍。大家读完本小册也不会一下子晋升业界大佬,当上 CTO,迎娶白富美,走上人生巅峰。希望本小册能够帮助大家解决一些工作、面试过程中的问题,逐渐成为一个更好的工程师,有兴趣的小伙伴可以再深入研究一下 MySQL,说不定你就是下一个数据库泰斗啦。
购买并阅读本小册的建议
#

- 本小册是一本待出版的纸质书籍,并非一些杂碎文章的集合,是非常有结构和套路的,所以大家阅读时千万不能当作厕所蹲坑、吃饭看手机时的所谓
碎片化读物
。碎片化阅读只适合听听矮大紧、罗胖子他们扯扯犊子,开阔一下视野用的。对于专业的技术知识来说,大家必须付出一个完整的时间段进行体系化学习,这样尊重知识,工资才能尊重你。 顺便说一句,我已经好久都不听罗胖子扯犊子了,刚开始办罗辑思维的时候觉得他扯的还可以,越往后越觉得都钻钱眼儿里了,天天在鼓吹焦虑,让大家去买他们的鸡汤课。不过听听矮大紧就挺好啊,不累~
- 本小册是由 Markdown 写成,在电脑端阅读体验十分舒服,当然你非要用小手机看我也不拦着你,但是效果打了折扣是你的损失。
- 为了保证最好的阅读体验,不用一个没学过的概念去介绍另一个新概念,本小册的章节有严重的依赖性,比如你在没读
InnoDB
数据页结构前千万不要就去读B+
树索引,所以大家最好从前看到尾,不要跳着看!不要跳着看!不要跳着看!,当然,不听劝告我也不能说什么,祝你好运。 - 大家可能买过别的小册,有的小册一篇文章可能用5分钟、10分钟读完,不过我的小册子每一篇文章都比较长,因为我把高耦合的部分都集中在一篇文章中了。文章中埋着各种伏笔,所以大家看的时候可能不会觉察出来很突兀的转变,所以在阅读一篇文章的时候千万不要跳着看!不要跳着看!不要跳着看!
- 大家在看本小册之前应该断断续续看过一些与本小册内容相关的知识,只是不成体系,细节学习的不够。对于这部分读者来说,希望大家像倚天屠龙记里的张无忌一样,在学张三丰的太极剑法时先忘记之前的武功,忘的越干净,学的越得真传。这样才能跟着我的套路走下去。
如果你真的是个小白的话,那这里头的数字都是假的: 
一篇文章能用2个小时左右的时间掌握就很不错了。说句扫大家兴的话,虽然我已经很努力的想让大家的学习效率提升n倍,但是不幸的是想掌握一门核心技术仍然需要大家多看几遍(不然工资那么好涨啊~)。
关于工具
#
本小册中会涉及很多 InnoDB 的存储结构的知识,比如记录结构、页结构、索引结构、表空间结构等等,这些知识是所有后续知识的基础,所以是重中之重,需要大家认真对待。Jeremy Cole 已经使用 Ruby 开发了一个简易的解析这些基础结构的工具,github地址是:
innodb_ruby的github地址,大家可以按照说明安装上这个工具,可以更好的理解 InnoDB 中的一些存储结构(此工具虽然是针对MySQL 5.6
的,但是幸好MySQL
的基础存储结构基本没多大变化,所以大部分场景下这个innodb_ruby
工具还是可以使用的)。
关于盗版
#
在写这本小册之前,我天真的以为只需要找几本参考书,看看 MySQL 的官方文档,遇到不会的地方百度谷歌一下就可以在 3 个月内解决这本书,后来的现实证明我真的想的太美了。不仅花了大量的时间阅读各种书籍和源码,而且有的时候知识耦合太厉害,为了更加模块化的把知识表述清楚,我又花了大量的时间来思考如何写作才能符合用户认知习惯,还花了非常多的时间来画各种图表,总之就是心累啊~ 我希望的是:各位同学可以用很低的成本来更快速学会一些看起来生涩难懂的知识,但是毕竟我不是马云,不能一心一意做公益,希望各位通过正规渠道获得小册,尊重一下版权。 还有各位写博客的同学,引用的少了叫借鉴,引用的多了就,就有点那个了。希望各位不要大段大段的复制粘贴,用自己的话写出来的知识才是自己的东西。 我知道不论我们怎样强调版权意识,总是有一部分小伙伴喜欢不劳而获,总是喜欢想尽各种渠道来弄一份盗版的看,希望这部分同学看完别忘了关注公众号【我们都是小青蛙】,给我填个粉儿也算是赞助一下我(下面是二维码,觉得有帮助的话希望可以打赏一下,毕竟本人很穷。另外,公众号中有若干篇小册的补充文章,包括三篇极其重要的语句加锁分析):
小贴士:我一直有个想法,就是如何降低教育成本。现在教育的盈利收费模式都太单一,就是直接跟学生收上课费,导致课程成为一种2C的商品,价格高低其实和内容质量并不是很相关,所以课程提供商会投入更大的精力做他们的渠道营销。所以现在的在线教育市场就是渠道为王,招生为王。我们其实可以换一种思路,在线教育的优势其实是传播费用更低,一个人上课和一千万人上课的费用区别其实就是服务器使用的多少罢了,所以我们可能并不需要那么多语文老师、数学老师,我们用专业的导演、专业的声优、专业的动画制作、专业的后期、专业的剪辑、专业的编剧组成的团队为某个科目制作一个专业的课程就好了嘛(顺便说一句,我就可以转行做课程编剧了)!把课程当作电影、电视剧来卖,只要在课程中植入广告,或者在播放平台上加广告就好了嘛,我们也可以在课程里培养偶像,来做一波粉丝经济。这样课程生产方也赚钱,学生们也省钱,最主要的是可以更大层度上促进教育公平,多好。
关于错误
#
准确性问题
#
我不是神,并不是书中的所有内容我都一一对照源码来验证准确性(阅读的大部分源码是关于查询优化和事务处理的),如果各位发现了文中有准确性问题请直接联系我,我会加入 Bug 列表中修正的。
...
2025年1月11日 16:40 周六第11章 两个表的亲密接触-连接的原理
搞数据库一个避不开的概念就是Join
,翻译成中文就是连接
。相信很多小伙伴在初学连接的时候有些一脸懵逼,理解了连接的语义之后又可能不明白各个表中的记录到底是怎么连起来的,以至于在使用的时候常常陷入下面两种误区: - 误区一:业务至上,管他三七二十一,再复杂的查询也用在一个连接语句中搞定。 - 误区二:敬而远之,上次 DBA 那给报过来的慢查询就是因为使用了连接导致的,以后再也不敢用了。
所以本章就来扒一扒连接的原理。考虑到一部分小伙伴可能忘了连接是什么或者压根儿就不知道,为了节省他们百度或者看其他书的宝贵时间以及为了我的书凑字数,我们先来介绍一下 MySQL 中支持的一些连接语法。
连接简介
#
连接的本质
#
为了故事的顺利发展,我们先建立两个简单的表并给它们填充一点数据:
mysql> CREATE TABLE t2 (m2 int, n2 char\(1)\); Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
我们成功建立了
t1、
t2两个表,这两个表都有两个列,一个是
INT类型的,一个是
CHAR(1)`类型的,填充好数据的两个表长这样:
...
2025年1月5日 19:17 周日数据库和文件系统的工具
#
数据目录的结构
#
表在文件系统中的表示
#
kjskfjksdf
s ksfd
InnoDB是如何存储表数据的1
#
系统表空间1
#
撒旦发就
系统表空间2
#
撒旦发就
系统表空间3
#
撒旦发就
InnoDB是如何存储表数据的2
#
2025年1月5日 08:42 周日
内容简介
本书是MySQL领域的经典之作,拥有广泛的影响力。第3版更新了大量的内容,不但涵盖了最新MySQL 5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有的基准测试和性能优化部分也做了大量的扩展和补充。全书共分为16章和6个附录,内容涵盖MySQL架构和历史,基准测试和性能剖析,数据库软硬件性能优化,复制、备份和恢复,高可用与高可扩展性,以及云端的MySQL和MySQL相关工具等方面的内容。每一章都是相对独立的主题,读者可以有选择性地单独阅读。
本书不但适合数据库管理员(DBA)阅读,也适合开发人员参考学习。不管是数据库新手还是专家,相信都能从本书有所收获。
©2012 by Baron Schwartz,Peter Zaitsev,Vadim Tkachenko.
Simplified Chinese Edition,jointly published by O’Reilly Media,Inc. and Publishing House of Electronics Industry,2013. Authorized translation of the English edition,2012 O’Reilly Media,Inc.,the owner of all rights to publish and sell the same.
All rights reserved including the rights of reproduction in whole or in part in any form.
本书简体中文版专有出版权由O’Reilly Media,Inc.授予电子工业出版社。未经许可,不得以任何方式复制或抄袭本书的任何部分。专有出版权受法律保护。
版权贸易合同登记号图字:01-2013-1661
图书在版编目(CIP)数据
高性能MySQL:第3版/(美)施瓦茨(Schwartz,B.),(美)扎伊采夫(Zaitsev,P.),(美)特卡琴科(Tkachenko,V.)著;宁海元等译.—北京:电子工业出版社,2013.5
书名原文:High Performance MySQL,Third Edition
ISBN 978-7-121-19885-4
...
2025年1月5日 08:42 周日第9章 操作系统和硬件优化
MySQL服务器性能受制于整个系统最薄弱的环节,承载它的操作系统和硬件往往是限制因素。磁盘大小、可用内存和CPU资源、网络,以及所有连接它们的组件,都会限制系统的最终容量。因此,需要小心地选择硬件,并对硬件和操作系统进行合适的配置。例如,若工作负载是I/O密集型的,一种方法是设计应用程序使得最大限度地减少MySQL的I/O操作。然而,更聪明的方式通常是升级I/O子系统,安装更多的内存,或重新配置现有的磁盘。
硬件的更新换代非常迅速,所以本章有关特定产品或组件的内容可能将很快变得过时。像往常一样,我们的目标是帮助提升对这些概念的理解,这样对于即使没有直接覆盖到的知识也可以举一反三。这里我们将通过现有的硬件来阐明我们的观点。
9.1 什么限制了MySQL的性能
#
许多不同的硬件都可以影响MySQL的性能,但我们认为最常见的两个瓶颈是CPU和I/O资源。当数据可以放在内存中或者可以从磁盘中以足够快的速度读取时,CPU可能出现瓶颈。把大量的数据集完全放到大容量的内存中,以现在的硬件条件完全是可行的(1)。
另一方面,I/O瓶颈,一般发生在工作所需的数据远远超过有效内存容量的时候。如果应用程序是分布在网络上的,或者如果有大量的查询和低延迟的要求,瓶颈可能转移到网络上,而不再是磁盘I/O(2)。
第3章中提及的技巧可以帮助找到系统的限制因素,但即使你认为已经找到了瓶颈,也应该透过表象去看更深层次的问题。某一方面的缺陷常常会将压力施加在另一个子系统,导致这个子系统出问题。例如,若没有足够的内存,MySQL可能必须刷出缓存来腾出空间给需要的数据——然后,过了一小会,再读回刚刚刷新的数据(读取和写入操作都可能发生这个问题)。本来是内存不足,却导致出现了I/O容量不足。当找到一个限制系统性能的因素时,应该问问自己,“是这个部分本身的问题,还是系统中其他不合理的压力转移到这里所导致的?”在第3章的诊断案例中也有讨论到这个问题。
还有另外一个例子:内存总线的瓶颈也可能表现为CPU问题。事实上,我们说一个应用程序有“CPU瓶颈”或者是“CPU密集型”,真正的意思应该是计算的瓶颈。接下来将深入探讨这个问题。
9.2 如何为MySQL选择CPU
#
在升级当前硬件或购买新的硬件时,应该考虑下工作负载是不是CPU密集型。
可以通过检查CPU利用率来判断是否是CPU密集型的工作负载,但是仅看CPU整体的负载是不合理的,还需要看看CPU使用率和大多数重要的查询的I/O之间的平衡,并注意CPU负载是否分配均匀。本章稍后讨论的工具可以用来弄清楚是什么限制了服务器的性能。
9.2.1 哪个更好:更快的CPU还是更多的CPU
#
当遇到CPU密集型的工作时,MySQL通常可以从更快的CPU中获益(相对更多的CPU)。
但这不是绝对的,因为还依赖于负载情况和CPU数量。更古老的MySQL版本在多CPU上有扩展性问题,即使新版本也不能对单个查询并发利用多个CPU。因此,CPU速度限制了每个CPU密集型查询的响应时间。
当我们讨论CPU的时候,为保证本文易于阅读,对某些术语将不会做严格的定义。现在一般的服务器通常都有多个插槽(Socket),每个插槽上都可以插一个有多个核心的CPU(有独立的执行单元),并且每个核心可能有多个“硬件线程”。这些复杂的架构需要有点耐心去了解,并且我们不会总是明确地区分它们。不过,在一般情况下,当谈到CPU速度的时候,谈论的其实是执行单元的速度,当提到的CPU数量时,指的通常是在操作系统上看到的数量,尽管这可能是独立的执行单元数量的多倍(3)。
这几年CPU在各个方面都有了很大的提升。例如,今天的Intel CPU速度远远超过前几代,这得益于像直接内存连接(directly attached memory)技术以及PCIe卡之类的设备互联上的改善等。这些改进对于存储设备尤其有效,例如Fusion-io和Virident的PCIe闪存驱动器。
超线程的效果相比以前也要好得多,现在操作系统也更了解如何更好地使用超线程。而以前版本的操作系统无法识别两个虚拟处理器实际上是在同一芯片上,认为它们是独立的,于是会把任务安排在两个实际上是相同物理执行单元上的虚拟处理器。实际上单个执行单元并不是真的可以在同一时间运行两个进程,所以这样做会发生冲突和争夺资源。而同时其他CPU却可能在闲置,从而浪费资源。操作系统需要能感知超线程,因为它必须知道什么时候执行单元实际上是闲置的,然后切换相应的任务去执行。这个问题之前常见的原因是在等待内存总线,可能花费需要高达一百个CPU周期,这已经类似于一个轻量级的I/O等待。新的操作系统在这方面有了很大的改善。超线程现在已经工作得很好。过去,我们时常提醒人们禁用它,但现在已经不需要这样做了。
这就是说,现在可以得到大量的快速的CPU——比本书的第2版出版的时候要多得多。所以多和快哪个更重要?一般来说两个都想要。从广义上来说,调优服务器可能有如下两个目标:
低延时(快速响应)
要做到这一点,需要高速CPU,因为每个查询只能使用一个CPU。
高吞吐
如果能同时运行很多查询语句,则可以从多个CPU处理查询中受益。然而,在实践中,还要取决于具体情况。因为MySQL还不能在多个CPU中完美地扩展,能用多少个CPU还是有极限的。在旧版本的MySQL中(MySQL 5.1以后的版本已经有一些提升),这个限制非常严重。在新的版本中,则可以放心地扩展到16或24个CPU,或者更多,取决于使用的是哪个版本(Percona往往在这方面略占优势)。
如果有多路CPU,并且没有并发执行查询语句,MySQL依然可以利用额外的CPU为后台任务(例如清理InnoDB缓冲、网络操作,等等)服务。然而,这些任务通常比执行查询语句更加轻量化。
MySQL复制(将在下一章中讨论)也能在高速CPU下工作得非常好,而多CPU对复制的帮助却不大。如果工作负载是CPU密集型,主库上的并发任务传递到备库以后会被简化为串行任务,这样即使备库硬件比主库好,也可能无法保持跟主库之间的同步。也就是说,备库的瓶颈通常是I/O子系统,而不是CPU。
如果有一个CPU密集型的工作负载,考虑是需要更快的CPU还是更多CPU的另外一个因素是查询语句实际在做什么。在硬件层面,一个查询可以在执行或等待。处于等待状态常见的原因是在运行队列中等待(进程已经是可运行状态,但所有的CPU都忙)、等待闩锁(Latch)或锁(Lock)、等待磁盘或网络。那么你期望查询是等待什么呢?如果等待闩锁或锁,通常需要更快的CPU;如果在运行队列中等待,那么更多或者更快的CPU都可能有帮助。(也可能有例外,例如,查询等待InnoDB日志缓冲区的Mutex,直到I/O完成前都不会释放——这可能表明需要更多的I/O容量)。
这就是说,MySQL在某些工作负载下可以有效地利用很多CPU。例如,假设有很多连接查询的是不同表(假设这些查询不会造成表锁的竞争,实际上对MyISAM和MEMORY表可能会有问题),并且服务器的总吞吐量比任何单个查询的响应时间都更重要。吞吐量在这种情况下可以非常高,因为线程可以同时运行而互不争用。
再次说明,在理论上这可能更好地工作:不管查询是读取不同的表还是相同的表, InnoDB都会有一些全局共享的数据结构,而MyISAM在每个缓冲区都有全局锁。而且不仅仅是存储引擎,服务器层也有全局锁。以前InnoDB承担了所有的骂名,但最近做了一些改进后,暴露了服务器层中的其他瓶颈。例如臭名昭著的LOCK_open互斥量(Mutex),在MySQL 5.1和更早版本中可能就是个大问题,另外还有其他一些服务器级别的互斥量(例如查询缓存)。
通常可以通过堆栈跟踪来诊断这些类型的竞争问题,例如Percona Toolkit中的pt-pmp工具。如果遇到这样的问题,可能需要改变服务器的配置,禁用或改变引起问题的组件,进行数据分片(Sharding),或者通过某种方式改变做事的方法。这里无法列举所有的问题和相应的解决方案,但是一旦有一个确定的诊断,答案通常是显而易见的。大部分不幸遇到的问题都是边缘场景,最常见的问题随着时间的推移都在服务器上被修复了。
9.2.2 CPU架构
#
可能99%以上的MySQL实例(不含嵌入式使用)都运行在Intel或者AMD芯片的x86架构下。本书中我们基本都是针对这种情况。
64位架构现在都是默认的了,32位CPU已经很难买到了。MySQL在64位架构上工作良好,尽管有些事暂时不能利用64位架构来做。因此,如果使用的是较老旧版本的MySQL,在64位服务器上可能要小心。例如,在MySQL 5.0发布的早期时候,每个MyISAM键缓冲区被限制为4 GB,由一个32位整数负责寻址。(可以创建多个键缓冲区来解决这个问题。)
确保在64位硬件上使用64位操作系统!最近这种情况已经不太常见了,但以前经常可以遇到,大多数主机托管提供商暂时还是在服务器上安装32位操作系统,即使是64位CPU。32位操作系统意味着不能使用大量的内存:尽管某些32位系统可以支持大量的内存,但不能像64位系统一样有效地利用,并且在32位系统上,任何一个单独的进程都不能寻址4 GB以上的内存。
9.2.3 扩展到多个CPU和核心
#
多CPU在联机事务处理(OLTP)系统的场景中非常有用。这些系统通常执行许多小的操作,并且是从多个连接发起请求,因此可以在多个CPU上运行。在这样的环境中,并发可能成为瓶颈。大多数Web应用程序都属于这一类。
OLTP服务器一般使用InnoDB,尽管它在多CPU的环境中还存在一些未解决的并发问题。然而,不只是InnoDB可能成为瓶颈:任何共享资源都是潜在的竞争点。InnoDB之所以获得大量关注是因为它是高并发环境下最常见的存储引擎,但MyISAM在大压力时的表现也不好,即使不修改任何数据只是读取数据也是如此。许多并发瓶颈,如InnoDB的行级锁和MyISAM的表锁,没有办法优化——除了尽可能快地处理任务之外,没有别的办法解决,这样,锁就可以尽快分配给等待的任务。如果一个锁是造成它们(其他任务)都在等待的原因,那么不管有多少CPU都一样。因此,即使是一些高并发工作负载,也可以从更快的CPU中受益。
实际上有两种类型的数据库并发问题,需要不同的方法来解决,如下所示。
逻辑并发问题
应用程序可以看到资源的竞争,如表或行锁争用。这些问题通常需要好的策略来解决,如改变应用程序、使用不同的存储引擎、改变服务器的配置,或使用不同的锁定提示或事务隔离级别。
内部并发问题
比如信号量、访问InnoDB缓冲池页面的资源争用,等等。可以尝试通过改变服务器的设置、改变操作系统,或使用不同的硬件解决这些问题,但通常只能缓解而无法彻底消灭。在某些情况下,使用不同的存储引擎或给存储引擎打补丁,可以帮助缓解这些问题。
MySQL的“扩展模式”是指它可以有效利用的CPU数量,以及在压力不断增长的情况下如何扩展,这同时取决于工作负载和系统架构。通过“系统架构”的手段是指通过调整操作系统和硬件,而不是通过优化使用MySQL的应用程序。CPU架构(RISC、CISC、流水线深度等)、CPU型号和操作系统都影响MySQL的扩展模式。这也是为什么说基准测试是非常重要的:一些系统可以在不断增加的并发下依然运行得很好,而另一些的表现则糟糕得多。
有些系统在更多的处理器下甚至可能降低整体性能。这是相当普遍的情况,我们了解到许多人试图升级到有多个CPU的系统,最后只能被迫恢复到旧系统(或绑定MySQL进程到其中某些核心),因为这种升级反而降低了性能。在MySQL 5.0时代,Google的补丁和Percona Server出现之前,能有效利用的CPU核数是4核,但是现在甚至可以看到操作系统报告多达80个“CPU”的服务器。如果规划一个大的升级,必须要同时考虑硬件、服务器版本和工作负载。
...