高性能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 以上同步完成