高性能MySQL(第3版)

购买链接

第一章:MySQL架构与历史


第二章:MySQL基准测试
第三章:服务器性能剖析

第四章:Schema与数据类型优化

第五章:创建高性能的索引

第六章:查询性能优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
语法解析器和预处理
- 通过关键字将SQL解析,生成“解析树”。
- 解析器:验证关键字,或关键字顺序
- 预处理:表、列是否存在,字段和别名是否歧义,验证权限,

查询优化器
一条查询有很多种方式执行,最后都返回相同的结果。优化器就是找出其中最好的执行计划。
基于成本的,这里的成本不等于实际执行的成本,它可能不是最优的,但成本是更小的。

静态优化
- 对解析树分析,并完成优化。
- 只需要一次
动态优化(运行时优化)
- 和查询上下文有关(WHERE条件的取值,索引命中的行数)。
- 每次执行都需要重新评估

优化:
- 等价变换 (5=5 AND a>5) 改写为 a>5; (a<b AND b=c) AND a=5 改写为 b>5 AND b=c AND a=5
- MIN()、MAX() MIN找到索引最小记录,最左边; MAX最大,最右边;
- 提前终止查询 例如:limit
- IN() IN在MySQL不等于多个OR,MySQL先把 IN排序,再二分查找,IN比较多的话,MySQL会更快

索引和统计信息
MySQL架构是分层的,服务层有查询优化器,却没有数据和索引的统计信息。
这些统计信息需要向存储引擎获取,包括:每个表或索引有多少个页面,每个表的每个索引基数是多少、数据行和索引长度、索引分布信息等。
查询优化器再根据这些信息来选择一个最优的执行计划。

关联查询
- 关联:多表,子查询,
- UNION 将一系列的单个查询放临时表,再重新读出临时表数据来完成 UNION查询。
- MySQL关联执行策略(嵌套循环关联):参考下图

排序
- EXPLAIN Extra
1. "Using filesort"直接排序
2. "Using temporary; Using filesort"关联结果放临时表,关联结束后,再排序

返回结果给客户端
结果集是增量、逐步返回给客户端的。
===> 例如:关联查询伪代码中的output,一旦处理完最后一个关联,开始生成第一条结果时,MySQL就开始向客户端发送结果集了。
===> 好处:Server不用存太多结果,节省内存; Client也可以第一时间收到结果;
===> 结果集的每一行都会封包通过TCP发送,TCP传输过程中,可能会对MySQL封包缓存然后批量传输。
  • MySQL关联查询,inner join、left join伪代码,多表关联
    在这里插入图片描述

1
2
3
4
5
6
7
8
9
union
使用子查询LIMIT会产生更少临时表数据,如果合并后需要排序,需要在全局加上 ORDER BY
(SELECT name FROM actor ORDER BY name) UNION ALL (SELECT name FROM actor ORDER BY name) LIMIT 20;
(SELECT name FROM actor ORDER BY name LIMIT 20) UNION ALL (SELECT name FROM actor ORDER BY name LIMIT 20) LIMIT 20;

松散索引
例如:复合索引(a, b) SELECT ... FROM table WHERE b BETWEEN 2 AND 3
因为此SQL不满足最左前缀原则,所以使用不了索引,需要进行全部扫描。
231页
  • 松散索引
    在这里插入图片描述

236页


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 6.7 优化特定类型的查询

1. 优化 COUNT() 查询
两种作用:
1. 指定了列或列的表达式,则统计这个表达式有值的结果数(只统计非NULL)。
2. 统计结果集的行数。括号内不为空,实际就是在统计行数。
===> COUNT(*) 不是统计所有的列,而是忽略所有的列直接统计所有的行数。
===> 错误用法:希望统计行数,却指定了某个列。如果希望统计行数,最好使用 COUNT(*),它字面意义清晰,性能也更好。

案例:
select count(message_tag) from message -- 2297
select count(1) from message WHERE message_tag is NOT NULL -- 2297

select count(*) from message -- 4384 推荐
select count(1) from message -- 4384 ===> 即使这样写,这里也会优化为 COUNT(*),因为 MySQL确定它不可能为 NULL

2. 优化 LIMIT 分页
- 在子查询 limit 2000,20
- 使用 id<2000 limit 20

3. 优化通常三管齐下:不做、少做、快速地做。

258页
【日期标记】2022-12-09 09:10:22 以上同步完成

第七章:MySQL高级特性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# 分区表
一个表按照键来进行分区。
分区键和索引键最好相同。
分区表的列不可以使用函数来查询。 YEAR(date) < 2022 ===> date BETWEEN '2022-01-01' AND '2022-12-31'
分区表的增删改查,都会锁一下全部分区表。(InnoDB会锁定指定的分区表,再到行锁。)
# 合并表
多个表合成一个表来对外提供访问
# 视图
简介:视图是一个虚拟表,不存放任何数据(所以,它也不支持创建索引)。
- 临时表算法:创建一个临时表,再查询临时表
- 合并算法:定义视图 where a='a',查询视图 where b='b',合并查询(真正执行的SQL) where a='a' and b='b'
# 物化视图
简介:视图结果数据存放一个表中,并定期从原始表刷新数据到这个表中。
# MySQL内部代码
存储过程、存储函数 => 接收参数,执行后并返回数据
触发器、事件 => 不接收参数,不返回数据

存储过程:一段共享的代码片段,在服务器内部执行;
触发器:执行 INSERT、UPDATE、DELETE,可以在执行SQL前、会执行后触发;
事件:某个时候,或每隔一段时间执行一段SQL代码;
# 字符集
数据库、表、列;(它们都有默认值,最终以列为准)

字符集转换 Client与Server通信时,可能会使用不同的字符集。
Client(SQL语句) => 转换编码 => Server
Server 执行SQL语句
Server(结果) => 转换编码 => Client
# MyISAM全文索引
共两层:第一层所有关键字,每个关键字的第二层链接相关的“文档指针”;
307页
【日期标记】2022-12-12 09:31:35 以上同步完成
# 缓存
事务是否可以访问查询缓存取决于当前事务ID,以及对应数据表上是否有锁。
每个InnoDB表的内存数据字典都保存了一个事务ID,如果当前事务ID小于该事务ID,则无法读取缓存。
===> 例如:当前系统事务ID是5,且获取该表的某些记录的锁,然后进行提交事务,那么事务1至4,都不应该再读、写缓存。
如果表上有任何锁,那么对这个表的任何查询语句都无法缓存。
===> 例如:某个事务执行了 SELECT FROM UPDATE语句,那么在这个锁释放之前,任何其他事务都无法读取这个表的缓存结果。

MySQL一开始会申请一大块内存,自己来管理,每次申请一点(查询缓存最小单元)缓存都存放数据,不够就继续申请。
由于缓存的失效,或申请的太多了,会产生间隙,称为“碎片”(这在内存管理、文件系统管理是经典问题)。

减少碎片:查询缓存最小单元,这个值太大,碎片会很多;这个值很小,会减少碎片,在内存不够时,但会一直分配内存;

缓存不是越大越好,缓存需要看增删改与查询的情况。

InnoDB告诉MySQL使某个表缓存失效。
  • MySQL 7.13 总结
    在这里插入图片描述

323页
【日期标记】2022-12-13 09:57:33 以上同步完成

第八章:优化服务器设置

1
2
3
4
有人问“我服务器32GB内存,12核CPU,怎么配置好?”,很抱歉,这没有标准答案。
服务器配置不单单是根据硬件来的,也跟工作负载、数据、应用需求有关。

配置应从MySQL内核和行为开始,然后用这些知识来配置MySQL。

386页(一脸懵,读完,像是没读一样的,)
【日期标记】2022-12-14 09:23:37 以上同步完成

第九章:操作系统和硬件优化

432页

第十章:复制

1
2
3
4
5
6
复制方式(都是基于binlog二进制日志来的)
=> 基于SQL语句:存在弊端,有一些变量或函数就无法执行了:当前用户,当前时间
=> 基于行(默认,推荐):执行全表更新的话,用此方式代价较大。
主库开销:binlog开销,从库网络I/O开销

扩展性:扩展读,不扩展写
  • MySQL 复制
    在这里插入图片描述

1
2
3
从兼主(可从可主):如果从设置了 log_slave_updates,可以让从变为其他服务器的主。

Server ID唯一(为了避免循环:与自己Server ID一致,不进行复制)
  • MySQL 可从可主、唯一服务器ID
    在这里插入图片描述

1
2
复制拓扑:
很多种,基本常用就是一主多从
  • MySQL 复制拓扑,拓扑的基本原则
    在这里插入图片描述
  • MySQL 复制拓扑的多种方式
    在这里插入图片描述

1
2
3
4
5
6
7
8
9
10
11
扩展性:读扩展性(非线性扩展),写扩展(分区)

过大的复制延迟(两种延迟)
1、突然延迟然后跟上
2、稳定延迟增大

复制有多快
理论上非常快(例如,仅仅受限于网络速度)。
MySQL 二进制日志转储线程并没有通过轮询的方式从主库请求事件,而是由主库来通知备库新的事件,因为前者低效且缓慢。
从主库读取一个二进制日志事件是一个阻塞型网络调用,当主库记录事件后,马上就开始发送。
因此可以说,只要复制线程被唤醒并且能够通过网络传输数据,事件就会很快到达备库。
  • MySQL 读扩展性(非线性扩展),写扩展(分区)
    在这里插入图片描述

499页
(看多少没有,关键在于吸收多少,我感觉我吸收甚少。)
【日期标记】2022-12-19 16:21:18 以上同步完成