Table Of Content胃HJ图"
一一从根儿上理解|\l1ySQL
小孩子 4919 著
小因E信出版集团 多好吧!凹!但
第 O 章棋子一一阅读前必看……......... 1 2.1.1 在命令行上使用选项 .........19
2.1.2 配置文件中使用选项 ..21
第 1 章 装作自己是个小臼-一- 2.1.3 在命令行和配置文件中
中刀识 MySQL.................. …....3 启动选项的区别 26
' 2.2 系统变量........ … ……·…27 I
1.1 MySQL 的客户端/服务器架构 ...........3
1.2 ….....… ...............3 2.2.1 系统变量简介 .......................27 I
MySQL 的安装
1.3 启动 MySQL服务器程序 … 5 2.2.2 查看系统变量 心...............27 I
1.3.1 在类 UNIX 系统中启动 2.2.3 设置系统变量 .....…… 28 I
2.3 状态变量 ….........…· …..32 I
服务器程序 .. …5
总结
1.3.2 在 Windows 系统中启动 2.4 32 1
I
服务器程序 … 6 第 3章 字符集和比较规则........... … 34
8
1.4 启动 MySQL 客户端程序 .................7 3.1 字符集和比较规则简介… ..............34
1.5 客户端与服务器连接的过程 ………10 3.1.1
字符集简介............................34
1.5.1 TCP/lp...................................10
3.1.2 比较规则简介 ……34
1.5.2 命名管道和共享内存 10 3.1.3 -些重要的字符集 ..............35
1.5.3 UNIX域套接字 11
3.2 MySQL中支持的字符集和比较规则 36
1.6 服务器处理客户揣请求 ............11
3.2.1 My削S臼Q队州吼L中哺的u叫t甘f侣8呻惚佩和叫 ut惚怕r惚附叩
1.6.1 连接管理 12 3.2.2 字符集的查看 .川...川……@川…@川...........扼36
1.6.2 ..................12
解析与优化 3.2.3
比较规则的查看 ...................38
1.6.3 存储引擎 … …...............14
3.3 字符集和比较规则的应用 ……….39
1.7 .............14
常用存储引擎………
3.3.1 各级别的字符集和比较规则 39
1.8 关于存储引擎的一些操作· ……16
3.3.2 客户端和服务器通信过程中
1.8.1 查看当前服务器程序支持的
使用的字符集 ….44
存储引擎 16
3.3.3 比较规则的应用 … 52
1.8.2 设置表的存储引擎· ….....16
3.4 总结 .....… … - …53 I
1.9 总结… …-… …17
第 4章从一条记录说起一一
第 2 章 MySQL 的调控按钮一一
InnoDB 记录存储结构...........55 I
启动选项和系统变量.........…..19
4.1 准备工作 ……… … E55 I
2.1 启动选项和配置文件 … .............19
4.2 InnoDB ïlï简介
一二 一 →
2 目最
4.3 InnoDB行格式…..............…..….. …56 7.2 索引的代价. …….…….........…巾…...…..…川….川@
4.3.1 指定行格式的语法.................56 7.3 应用 B+树索E引l …….…….…….川……..川. … 110
4.3.2 COMPACT行格式 ...............56 7.3.1 扫锚区i间和边界条件 … 110
4.3.3 REDUNDANT行格式………..64 7.3.2 索引用于排序…… 122
4.3.4溢出~J … ......68 7.3.3 索引用于分组 … ……125
4.3.5 DYNAMIC 行格式和 7.4 回袤的代价 ..............… …… .126
COMPRESSED行格式 …70 7.5 更好地创建和使用索引 …· …127
4.4 总结 … … ……........………71 7.5.1 只为用于搜索、排序或分组的
~J创建索引.......... ….127
第 5章盛放记录的大盒子一一
7.5.2 考虑索引列中不重复值的
InnoDB 数据页结构…·……… 72
.........................127
个数
5.1 不同类型的页简介 ......................72 7.5.3 索引列的类型尽量小 ...........127
5.2 数据页结构快览.. …….............72 7.5.4 为列前缀建立索引 ..........128
5.3 记录在页中的存储 ........… ........73 7.5.5 覆盖索引 .........129
5.4 Page Direclory (页目录 ).................80 7.5.6 让索引列以列名的形式在
5.5 Page Header (页面头部).........…...85
搜索条件中单独出现。 .........129
5.6 File Header (文件头部) …........…86 7.5.7 新插入记录时主键大小对
5.7 File Trailer (文件尾部)......…………88 效率的影响 129
5.8 总结 …................…..………88 7.5.8 冗余和重复索引 ..............130
7.6
总结................................................131
第6章快速查i甸的秘籍一-
B+ 树索引…..................... 90 第 8章数据的家一一
6.1 没有索引时进行查找..............……....90 MySQL 的数据目录…......... 132
6.1.1 在一个页中查找 … 90 8.1 鼓据库和文件系统的关系................132
6.1.2 在很多页中查找 ........91 8.2 MySQL数据目录 … ........132
6.2 索引… …· ……… ……91 8.2.1 数据目录和安装目录的
6.2.1 一个简单的索引方案 ......92 区别 .....................132
6.2.2 InnoDB 中的索引方案 ......94 8.2.2 如何确定 MySOL 中的
6.2.3 InnoDB 中 B+ 树索引的 数据目录 132
注意事项 · …102 8.3 数据目录的结构， … ..133
6.2.4 MylSAM 中的索引方案简介....104 8.3.1 数据库在文件系统中的
6.2.5 MySOL 中创建和删除索引的 表示 ...................................133
语句 … 105 8.3.2 表在文件系统中的表示 .......134
6.3 总结 … …................... 巾'….106 8.3.3 其他的文件 …….............137
8.4 文件系统对数据库的影晌 … 137
第 7章 B+ 树索引的使用… …… … 107
8.5 MySQL 系统数据库简介· …......138
7.1 B+树索引示意图的简化..................107
8.6 总结 … ……· ……......138
自最 3
第 9章存放页面的大池子-一
11.1.2 连接过程简介 .......180
InnoDB 的表空间……......… 140
11.1.3 内连接和外连接 .........181
9.1 回忆-些旧知识 …… …-…..140 11.2 连接的原理 …-… … … 185
9.1.1 页面类型 .................140 11.2.1 嵌套循环连接 ....186
9.1.2 页面通用部分 .....141 11.2.2 使用索引加快连接速度......187
9.2 独立表空间结构… .................142 11.2.3 基于块的嵌套循环连接 '188
9.2.1 区的概念 ...............142 11.3 总结 川 … … 189
9.2.2 段的概念 .....'.................144 第 12章 谁最便宣就选i佳一-
9.2.3 区的分类 .......................145 基于成本的优化 … … ..190
9.2.4 段的结构 .........................149
12.1 什么是成本 ...… …190
9.2.5 各类型页面详细情况 .150 h
12.2 单表查询的成本-… … … 190
9.2.6 Segment Header结构的
12.2.1 准备工作 … 190
运用 川 … 156
12.2.2基于成本的优化步骤 … 191
9.2.7 真实表空间对应的文件
12.2.3 基于索引统计数据的
大小 … ........157
成本计算 ...........................198
9.3 系统表空间 … …… … .158
12.3 连接查询的成本 ….... … .....201
9.4 总结 …..............…… ..164
12.3.1 准备工作 ..................201
第 10章条条大路遇罗马-一
12.3.2 条件过法 ( Condition
单表访问方法… ...... …..166 Filtering 1........................... 201
10.1 访问方法的概念……"… ...........167 12.3.3 两表连接的成本分析 ......203
10.2 const……… ........167 12.3.4多表连接的成本分析 川 205
10.3 ref …·……… ….........168 12.4 调节成本常数 … ….206
10.4 ref一。r_null ............….....................170 12.4.1 mysql.server_cost表........206
10.5 range ...........……..…-…..171 12.4.2 mysql.engine_cost表 208
10.6 index....………… ................171 12.5 总结… ..... … ..............209
10.7 all ……….......................172
第 13章 兵马来动， 粮草先行一一
10.8 注意事项 ............... …........172
InnoDB 统计数据是
10.8.1 重温二级索引+团表 ….172
如何收集的....……….....210
10.8.2 索引合并 。 … .173
13.1 统计敛掘的存储方式 ....................210
10.9 总结 ........... ……….......177
13.2 基于磁盘的永久性统计敏掘 ， …211
第 11 章两个袤的亲密接触一- 13.2.1 innodb_table_stats ...........211
连攘的原理….............…. 178 13.2.2 innodb_index_stats...........214
13.2.3 定期更新统计数据 .215
11.1 连接简介........................ ….........178
13.2.4 手动更新 innodb_table_sta怡和
11.1 .1 连接的本质 … ....178
innodb_index_stats 表 216
4 自 最
13.3 基于内存的非永久性统计数据..…217 15.4 总结 q4a uQu
13.4 innodb stats method 的使用 .......217
第 16 章 神兵和J器一一寸optimizertra臼 的
13.5 总结…........…… … …......219
神奇功效……......…… …… 270
第 14章 基于规则的优化(内含
16.1 。ptimizertrace简介 ………........270
220
子查询优化二三事) …........
16.2 通过optimizertraæ分析查询优化籍的
14.1 条件化简…......……….. …..220 具体工作过程 .........…...................271
14.1.1 移除不必要的括号 ........220
第 17 章 调节磁盘和 CPU 的矛盾一一
14.1.2 常量传递 ι…!… .....220 •
InnoDB 的 Buffer Pool ..... 278
14.1.3 移除没用的条件 221
14.1.4 表达式计算........................221 17.1 缓存的重要性…… .........…… .278
14.1.5 HAVING 子句和 WHERE 17.2 InnoDB 的 Bu仔'er Pool…...............278
子句的合并… 221 17.2.1 啥是 Buffer Pool ................ 278
14.1.6 常量表检测 .....221 17.2.2 Buffer Pool 内部组成 278
14.2 外连接消除…… … ..............222 17.2.3 free链表的管理.................279 l
i
14.3 子查询优化 ……· ……… 224 17.2.4 缓冲页的晗希处理 ..280
14.3.1 子查询语法… … 225 17.2.5 flush链表的管理 281
14.3.2 子查询在 MySQL 中是 17.2.6 LRU 链表的管理… ..282 -
怎么执行的 .230 17.2.7 其他的一些链表… 286
14.4 总结........…................…....… 244 17.2.8 刷新脏页到磁盘.................287
-
17.2.9 多个 Buffer Pool 实~J.......287
第 15章 查询优化的百科全书一一
17.2.10 innodb_buffer- -p∞L
EXPLAIN 详解.................245
chunk size...... ................288
15.1 执行计划输出中各列详解· …..246 17.2.11 配置 Buffer P∞1 时的注意
15.1.1 table........ ……........246 事项… ...289
15.1.2 id.......................................247 17.2.12 查看 Buffer Pool 的
15.1.3 selecUype …….249 状态信息 … 291
15.1.4 partitions ........................... 252 17.3 总结 句，内四J句
‘
15.1.5 type...................................252
第 18 章 从猫爷借钱说起一一
15.1.6 possible_keys 和 key........255
事务简介….....................294
15.1.7 keyJen.........................….256
15.1.8 ref......................................258 18.1 事务的起源 -…... …294
15.1.9 rows ..................................258 18.1.1 原子性 ( Atomicity )........... 295
15.1.10 filtered............... ..............259 18.1.2 隔离性 ( Isolation ) … 295
15.1.11 Extra................................260 18.1.3 一致性 ( Consistency )...... 296
15.2 JSON格式的执行计划.......… .266 18.1.4持久性 ( Durability ) .......... 298
15.3 Extented EXPLAIN .......................268 18.2 事务的概念......... …..................298
目 最 5
18.3 MySQL 中事务的语法 ·…- …300 19.9 用户线程批量从 flush链表中刷出
18.3.1 开启事务 ................3∞ 脏页 ……......…-……….......335
18.3.2 提交事务 .301 19.10 查看系统中的各种 Isn值 … 335
, 18.3.3 手动中止事务 302 19.11 innodb_flush_log_at_trx_commit的
auau
18.3.4 支持事务的存储引擎 302 用法 qq
Od
崩溃
1日 3.5 自动提交 303 19.12 恢复 句。吨。
k 18.3.6 隐式提交 ...............304 19.12.1 确定恢复的起点… ........337
18.3.7 保存点 305 19.12.2 确定恢复的终点 337
,,
nu
' 18.4 晶由 结 J句 19.12.3 怎么恢复 337
19.13 遗漏的问题: LOG BLOCK
第 19章 说过的话就一定要做到一一
HDR NO是如何计算的 ….........339
redo 日志…….... … .308
19.14 总结...... …… ..340
19.1 事先说明.......................， … 308
第 20 章 后悔了怎么勿←-
19.2 redo 日志是晗 ·叫 - …308
19.3 red口日志格式 · … .......309 undo 日志 .........…-….342
19.3.1 简单的 red。曰志类型 .......309 20.1 事务回滚的需求………… 342
19.3.2 复杂一些的 red。日志 20.2 事务 id ….........................…......343
, 类型 … ..311 20.2.1 分配事务阔的时机 υ 343
19.3.3 redo 曰志格式小结 …314 20.2.2 事务 id是怎么生成的 343
19.4 Mini-Transaction …............…...315 20.2.3 trxjd 隐藏列 … ......344
19.4.1 以组的形式写入 redo 20.3 undo 日志的格式 …..…… ......344
日志 ...........................315 20.3.1 INSERT操作对应的
19.4.2 Mini-Transact旧n 的概念 ....319 undo 日志 ..............345
19.5 red。日志的写入过程.....…...… 319 20.3.2 DELETE操作对应的
19.5.1 redo log block...................319 undo 曰志 … 347
19.5.2 redo 日志缓冲区 320 20.3.3 UPDATE操作对应的
19.5.3 redo 日志写入 log buffer ....321 undo 曰志 ...353
19.6 redo 日志文件 。 ， …… 323 20.3.4 增删改操作对二级索引的
19.6.1 redo 日志刷盘时机 323 影响 … 357
19.6.2 redo 日志文件组 … ........323 20.4 通用链表结构 .....…357
19.6.3 red。日志文件格式 324 20.5 FIL PAGE UNDO LOG 页面 359
19.7 109 sequence number … .......327 20.6 Undo页面链表 … ....................361
19.7.1 flushed to disk Isn…… 328 20.6.1 单个事务中的 Undo 页面
19.7.2 Isn 值和 redo 曰志文件组中的 链表 361
偏移量的对应关系 330 20.6.2 多个事务中的 Undo页面
19.7.3 flush 链表中的 Isn … 330 链表 362
19.8 checkpoint ……...… .332 20.7 undo 曰志具体写入过程 … 363
6 自 最
20.7.1 段的概念………...........363 21.3.2 ReadView …… .......390
20.7.2 Undo Log Segment 21.3.3 二级索引与 MVCC … 397
Header.…·… .364 21.3.4 MVCC 小结 … .........397
20.7.3 Undo Log Header …........365 21.4 关于 purge… ..... ………398
20.7.4 小结 …… .........367 21.5 总结 ….....…...… .......399
20.8 重用 Und。页丽 ........... … .368
第 22 章工作面试老大难一一锁...… 401
20.9
回滚段 ……........................…........369
20.9.1 22.1 解决并发事务带来问题的
回滚段的概念…...............369
两种基本方式 ……·….......401
20.9.2 从回滚段中申请 Undo
.......................371 22.1.1 写-写情况 … ........401
页面链衰
22.1.2 读-写或写-读情况 …403
20.9.3 多个回滚段 … 372
22.1.3 一致性读 404
20.9.4 回滚段的分类 … 374
22.1.4 锁定读 … ...........-404
20.9.5 r口ILpointer 的组成 ..........374
22.1.5 写操作… -405
20.9.6 为事务分配 Undo页面
22.2 ............................406
多粒度锁
链表的详细过程 … 375
20.10 回滚段相关配置… …….......376 22.3 MySQL 中的行锁和表锁 …….......408
22.3.1 其他存储引擎中的锁 .-408
20.10.1 配置回滚段数量 376
22.3.2 InnoDB存储引擎中的锁 ..409
20.10.2 配置 undo表空间 .376
22.3.3 InnoDB 锁的内存结构 417
20.11 undo 日志在崩溃恢复时的作用…377
20.12 总结.....…...........…...… …..377 22.4 语句加锁分析…….................423
22.4.1 普通的 SELECT语句.........423
第 21 章一条记录的多副面孔一一
22.4.2 锁定读的语句 … 424
事务隔离级别和 MVCC … 379
22.4.3 半一致性读的语句 .-441
21.1 事前准备.......... … .........379 22.4.4 INSERT语句 ....................442
21.2 事务隔离级别… ....…….379 22.5 查看事务加锁情况 ………........444
21.2.1 事务并发执行时遇到的 22.5.1 使用 information_schema
一致性问题 … .382 数据库中的表获取锁信息....444
21.2.2 SOL标准中的 4种隔离 22.5.2 使用 SHOW ENINGE INNODB
级别 … ...........385 STATUS 获取锁信息 ........-446
21.2.3 MySOL 中支持的 4种隔 22.6 死锁 ….................................450
离级别...............................386 22.7 总结 .....…u …..… ..454
21.3 ….388
MVCC原理…..................... 参考资料…….....………· …… .. 455
21.3.1 版本链 ...............….388
|
第O 章昕一阅读前必看
尊敬的各位同学，本书是我花了相当长的时间写出来的，章节内容也都是经过精心编排 |
的.为了尊重用户的认知规律，给各位同学一个良好的学习体验，我甚至推敲了很久各种概念
的出场顺序对用户体验的影响.如果您对 MySQL不熟，或者不是很熟，那么请暂时忘掉已经
学到的一些关于 MySQL 的知识.就像是张无忌在学习太极剑法时那样，这样才能更好地跟着
我设计好的套路走，从而达到事半功倍的效果.在学习过程中， 大家千万千万千万要遵循下面
这 3 个建议:
· 一定要逐章学习本书，千万不要跳着阅读!
· 一定要逐章学习本书，千万不要跳着阅读!
· 一定要逐章学习本书，千万不要跳着阅读'
因为从前期读者在本书电子版的测试过程中反馈的问题来看，绝大部分问题都是因为违反
了上面这 3 个建议而产生的.
当然， 尽管我尽了自己很大的努力来让进阶 MySQL 的这个过程变得更容易些，但终究不
可能满足所有人的需求，有很多读者在阅读过程中肯定会产生这样那样的疑惑， 我知道在自己
阅读技术图书的过程中遇到了困惑而不得解是一种多么大的折磨，如果大家在阅读本书的过程
中遇到了什么不得解的困惑 ï青使用微信扫描下方的二维码迸入答疑群键问〈人数较多的微信
群只能通过个人手动拉入).另外，请大家在答疑群中提问，而不是直接私信， 一对一答疑对
我来说负担是很大的.
上面的二维码是使用我的个人域名生成的，所以在使用微信扫码时可能会提示
山
袋
非微信官方网页，大家继续吁问就好了.如果扫码不成功， 可以手动添加微信号
xiaohaizi4920. 有可能个人很仿已经加满， 此时大家也可以圭IJ t(我们都是小青蛙"公众
烟
号中获取答疑群的进入方式.另外， 由于我比较忙， 所以可能回复不及时，望见谅.
这里特别注意一下，需要提问的同学一定要先搞清楚自己到底哪里不清楚，然后用通顺的语
句把它表达出来.以往很多同学的问题都是含糊不清，表达不通顺，这样的问题真的是让人看了
要发疯.所以为了我们双方的方便，提问前请认真思考一下.另外，我只负责回答关于本书的问
题，其他问题请和其他同学讨论吧 〈一是作者很可能也不会， 二是作者精力实在有限，望理解).
-
2 第O章模子一一阅读前必疆
哇晤，看到这里，大家有没有觉得我好善良呢?买书还附赠答疑解惑服务.我当然不是这
么单纯，建立答疑群其实有两个目的:
· 对于读者来说，可以解答在学习过程中的疑惑，读者由此受益;
· 对于作者来说，可以为我的图书营造一个好的口碑，以后再写别的图书也好卖一些，
我也可以从中受益.
另外，本书的知识比较系统，需要大家花费较多的时间认真研读，不过受个人能力和篇幅
所限，无法做到面面俱到，大家也并不能把本书当作 MySQL 百科全书.为此，我开设了-个
名为"我们都是小青蛙"的微信公众号，里面会不定期地发布一些原创的技术文章，偶尔也会
"扯扯续子"希望能对大家有帮助.
"我们都是小青蛙"公众号
i F在 1 章装作自己是个小白一一初m只 MySQL
1.1 MySQL 的客户端/服务器架构
以我们平时使用的微信为例，它其实是由客户端程序〈可以简称为客户端〉和服务器程序(可
以简称为服务器〉两部分组成的.微信客户端可能具有多种形式，比如手机 App、桌面端的软件
或者网页版的微信.微信的每个客户端都有一个唯一的用户名，即微信号.另一方面， 腾讯公司
在它们的机房里运行着微信的服务楼程序.我们平时在微信上的各种操作， 其实就是使用微信客
户端与微信服务器打交道.比如狗哥使用微信给猫爷发一条微信消息的过程大致如下所示.
l 狗哥发出的微信消息被客户端进行包装，添加了发送者和接收者的信息，然后从客户
端发送到微信服务器.
2 徽信服务器从收到的消息中获取发送者和接收者信息，并据此将消息发送到猫爷的微
{言客户端.然后，猫爷的微信客户端就会显示狗哥给他发的消息.
MySQL 的运行过程与之类似，即它的服务器程序直接与要存储的数据打交道，多个客
户端程序可以连接到这个服务器程序，向服务器发送增IIJIJ查改的请求， 然后服务器程序根据
这些请求，对存储的数据进行相应处理.与微信一样. MySQL 的每一个客户端都需要使用
用户名和密码才能登录服务器，而且只有在登录之后才能向服务器发送某些请求来操作数据.
MySQL 的日常使用场景是下面这样的.
l 启动 MySQL服务器程序.
2. 启动 MySQL客户端程序，并连接到服务器程序.
3 在客户端程序中输入命令语句， 并将其作为请求发送给服务器程序.服务器程序在收
到这些请求后，根据请求的内容来操作具体的数据，并将结果返回给客户端.
众所周知，现在计算机的功能都很强大，一台计算机上可以同时运行多个程序，比如微
信、 QQ、英雄联盟游戏、文本编辑器等.计算机上运行的每一个程序也称为一个进程.运行
过程中的 MySQL服务器程序和客户端程序在本质上来说都算是计算机中的进程，其中代表
MySQL服务器程序的进程称为 MySQL数据库实例(instance).
1.2 MySQL 的安装
在安装 MySQL时，无论是通过下载源代码的方式自行编译安装， 还是直接使用官方提供的
安装包进行安装. MySQL 的服务器程序和客户端程序都会安装到机器上.无论采用上述哪种安装
方式，一定要记住 MySQL安装在哪里了.换句话说， 一定一定一定要记住MySQL的安装目录.
