《[MySQL技术内幕：SQL编程》读书笔记

2019年3月31日23:12:11

严禁转载！！！

<MySQL技术内幕：SQL编程>这本书是我比较喜欢的一位国内作者姜承尧, 早年在学mysql时就听过姜老师的开源mysql网络视频教程, 记得在视频时总是姜老师姜老师的自称, 感到十分的亲切.

这本书主要是讲mysql在应用编程时如何正确并且更高效的根据业务场景编写相应的sql语句.

从一开始对mysql的历史, 数据库类型, 分支版本, 咋一看, 还挺熟悉的, 这个逻辑顺序不就是当时视频讲课的顺序. 在本章末尾, 还推荐了几款主流的图形化SQL查询分析器.

第二章主要是介绍了各种常用的数据类型. 包括UNSIGNED和ZEROFILL要注意的事情, 对于UNSIGNED, 如果没有设置sql_mode=no_unsigned_subtraction, 连个UNSIGNED 数据(小减大)的结果不是正确的负数. ZEROFILL主要是填充占位用的, 在显示的时候会在左边填充0, 实际显示还是实际大小, 只是显示不一样. 然后就是常用的时间日期数据类型了. 这里主要的问题是闰年, 星期, 跨年, 国际化这种需要注意的. datetime 不能有默认值, timestamp可以有默认值. mysql5.6前now, datetime等会截断微秒, 5.6后可以保留微秒(microsecond()函数). 再到字符类型, 字符类型主要是有固定长和变长两种, 变长的再表记录中在额外的头有这个可变数据的长度……当然比价orange人头疼的字符集和比较规则, 这里介绍的还是很清晰的.

第三章是查询处理.查询处理包括了逻辑查询和物理查询. 逻辑查询操作顺序用一图介绍. 然后就是根据操作顺序来对每一个操作展开讲解. 主要是联合, 过滤, 排序, 分组等操作. 我们要有集合的思维来看待关系型数据库.以上操作步骤是逻辑查询的, 真正的查询还要经过物理查询, 经过server层的分析器和优化器,真正构造一条执行查询语句.

第四章是子查询。子查询分为独立子查询和相关子查询。独立子查询与外部查询是不相关的，但是要明确显式定义，如果没有显示定义子查询的列，那么就会被mysl的优化器把子查询优化成相关子查询，相关子查询的查找速度与外部查询的匹配次数有关。因此往往就会造成查询慢的原因。这章分析和提出一些办法来如何减少子查询和外部查询的匹配次数，提高效率。第一、增加一个索引提高查询速度。第二、通过派生表来减少子查询与外部查询的匹配次数。接着介绍了EXISTS谓词和其与IN的区别，后面就是介绍派生表了。派生表用好不容易，需要经验丰富。所以平时尽量避免子查询。最后介绍了MariaDB对SEMI JSON的优化。

第五章是联接与集合操作。联接有多种联合方式，针对不同的业务选择不同的联接方式，CROSS JOIN， INNER JOIN，OUTER JOIN，NATURAL JOIN， STRAIGHT JOIN，SELF JOIN ，NONEQUI JOIN，SEMI JOIN。然后介绍不同的联接算法，包括Simple Nested-Loops Join算法，Block Nested-Loops Join算法，Block Nested-Loops Join算法 ，Block Nested-Loops Join算法，Classic Hash Join算法。最后又介绍了集合操作，mysql标准只有UNION DISTINCT和UNION ALL两种，但是可以在这个基础上扩展为EXCEPT和INTERSECT。

第六章是聚合和旋转操作。mysql为我们提供了一些聚合函数，并且有多种聚合，附加聚合，连续聚合等操作，通过聚合可以减少子查询的使用。然后还介绍了开放架构设计的表可以旋转，行–>列，列–>行。这种主要是用于报表输出，直观阅读。最后介绍了CUBE和ROLLUP，主要是配合GROUP BY使用，分组聚合，提高效率。

第七章是游标。从面向过程和面向集合的两个角度来展开讲解，同时给出了游标操作的步骤。但是要慎用游标，不是不用，而是在必须的场合下才使用。游标对于扫描一次表的情况是劣势的，但是如果对于一些面向集合的解决方案所需扫描成本为O(N^2)的情况，使用游标的解决方案只需O(N)。

第八章是事务编程。了解了什么事事务，事务是原子单位，要么都成功，要么都失败回滚。一般事务需要满足ACID。然后介绍了四种隔离级别，隔离级别是存储引擎层面的。InnoDB默认是REPEATABLE READ级别（可重复读），但是有了Next-Key Lock（间隙锁），可像串行化读那样。事务完全安全。接着介绍了事务有关的控制语句和隐式提交的语句。mysql的分布式事务需要XA事务的支持，异构数据库组成分布式事务需要InnoDB的隔离级别是SERIALIZABLE隔离级别。然后引出几个不好的事务编程。这一章节，分析了事务的概念和如何应用事务。

第九章是索引。索引是数据库查询的精华。正是因为有了索引，才能使查询变得更高效，使数据库应用更广泛。这章从数据结构树开始说起，到mysql底层数据结构B+树，到B+树索引的底层实现都分析了，后面还介绍了一下其他的索引实现，T树索引，哈希索引等。总的来说，这章是从底层来分析的，获益良多。

第十章是分区。谨慎选择是否要分区，因为分区并不一定能带来效率的提升的。分区是在索引的基础上对表进行水平分区。这章中介绍了RANGE、LIST、HASH、KEY、 COLUMNS五种主要的分区。

2.数据类型

2.1 尽量不要使用unsigned, 在没有设置 sql_model=’no_unsigned_subtraction’选项时, 两个unsigned变量相减等操作会出现结果错误
2.2 zerofill 整型填充, 不足定义的字节数, 会在前面填充0, 实际显示还是实际大小, 只是显示不一样.
2.3 set_model 主要是用来控制mysql的严格度
2.4 datetime 不能有默认值, timestamp可以有默认值
2.5 mysql5.6前now, datetime等会截断微秒, 5.6后可以保留微秒(microsecond()函数)
2.6 now(), datetime(), sleep(), current_timestamp(), sysdate() sysdate返回的是当前函数执行时的时间, now是执行SQL语句的时间
2.7 使用date_format() 不会走索引. do not !!! do not!!! do not!!!

3.查询处理

3.1 查询流程中, 每个操作都产生一个虚拟表, 除了最后一个,其他对用户都是透明的.
3.2查询流程

4.子查询

4.1 子查询必须包含括号
4.2 IN /// ANY/SOME /// ALL /// NOT IN/<>ALL
4.3 独立子查询是不依赖外部查询而运行的子查询。易于调试。
4.4 mysql优化器会对 IN 子句优化, 如果 IN 不是显式的列表定义, IN 子句会被优化成EXITS的相关子查询. (解决办法: 一般是增加多一层子查询, 减少外部查询和子查询的匹配次数)
4.5 相关子查询: 引用了外部查询列的子查询, 即子查询对外部查询的每一行进行计算.
4.6 对相关子查询的处理, 减少子查询与外部查询的匹配次数

错误:

正确:

然而查询速度很慢:

优化: 增加一个索引提高查询速度:

优化: 通过派生表来减少子查询与外部查询的匹配次数:

子查询优化:

索引
派生表
使用连接(JOIN)来代替子查询
4.7 EXISTS谓词
4.7 EXISTS 的输入一般是子查询, 并关联到外部查询, 但不是必须的. 根据子查询返回行, 该谓词返回true或false, 不会返回unknown.
4.8 EXISTS 和 IN 的执行计划是一样的, 但是 NOT EXISTS 和 NOT IN的执行计划是不一样的, 因为有UNKNOWN的存在. NOT EXISTS返回TRUE和FALSE, NOT IN返回FALSE和UNKNOWN.
4.9 派生表是完全的x虚拟表, 并没有也不可能被物理化地具体化
4.10 关于子查询的总结各种连接语句

5.连接与集合操作

5.1 由于历史原因，出现了新旧查询方法：

select * from a, b where a.x = b.x;
select * from a inner join b on a.x = b.x;

5.2 JOIN方式

CROSS JOIN
INNER JOIN
OUTER JOIN
NATURAL JOIN
STRAIGHT JOIN
SELF JOIN
NONEQUI JOIN
SEMI JOIN

逻辑查询的三个步骤：

产生笛卡尔积的虚拟表
按照ON过滤条件进行数据的匹配操作
添加外部行

CROSS JOIN：全表连接，做笛卡尔积， m * n。用于快速生成重读测试数据，作为返回结果集的行号。

INNER JOIN：不会添加外部行。过滤条件在WHERE或ON是一样的，最好是表之间的过滤是在ON，一个表的过滤在WHERE。后面不跟ON，等同于CROSS JOIN。可以使用USING( )来指定根据相同名称列来匹配。

OUTER JOIN：通过OUTER JOIN添加的保留表中存在未找到的匹配数据，对未找到并添加的外部行会用NULL填充。必须配有ON子句。可以使用USING( )来指定根据相同名称列来匹配。

LEFT OUTER JOIN：以左表为基准，右表去匹配。
RIGHT OUTER JOIN：以右表为基准，左表去匹配。

NATURAL JOIN：等同于INNER JOIN 和USING的组合。将两个表相同名称列进行匹配。

STRAIGHT JOIN：强制险读左表。一般是有经验的DBA用来设定最优路径。

SELF JOIN：同一个表不同实例之间的JOIN操作。读同一个表进行连接必须先指定表别名。主要是解决层次结构问题。

NONEQUI JOIN：包含“等于”运算符之外的运算符。

SEMI JOIN：根据一个表存在的记录去找另外一个表的相关记录。

5.3 多表联接 INNER JOIN多表查询。可以用ON（）把过滤条件都放在一起。OUTER JOIN 多表的联接顺序对结果有影响。

5.4 联接算法的历史

MySql 5.5版本仅支持Nested-Loops Join算法
MySql 5.6开始支持Batched Key Access Joins算法（简称BKA）

5.5 Nested-Loops Join算法 当联接的表上有索引，是非常高效，根据B+树的特性，时间复杂度是O(N)，但是如果没有索引，会被视为最坏情况，时间复杂度是O(N^2)。

5.5 Nested-Loops Join算法的分类：

Simple Nested-Loops Join算法
Block Nested-Loops Join算法

5.6 Simple Nested-Loops Join算法：从一张表中每次读取一条记录，然后将记录与嵌套表中的记录进行比较。

    For each row r in R do 
        lookup r in S index 
            If find s == r 
                Then output the tuple <r, s>

联接两个表都有索引，并且索引高度相同，优化器会选择将记录数最少的表作为外部表，因为内部表示索引扫描，只和索引的高度有关，与记录数量无关。

5.7 Block Nested-Loops Join算法 针对没有索引联接的情况，使用join buffer（联接缓冲）来减少内部循环减少读表的次数。实质是预先一次性读取比如10条记录到内存，减少读取次数来提高效率。

5.8 Block Nested-Loops Join算法 结合索引和group（额外join buffer）。后者可以提交cache命中率。

Classic Hash Join算法 mysql暂时不支持，MariaDb支持。

5.9 集合操作

UNION DISTINCT
- 创建一张临时表，即虚拟表
- 对这张临时表的列添加唯一索引
- 将输入的数据插入临时表
- 返回临时表
UNION ALL

5.10 集合操作注意 OUTFILE只能存在于最后一个select语句中，并且虽然在最后面，但是导出的事所有的结果。

UNION DISTINCT 由于有唯一索引，对性能还是影响的，所以在确定没有重复数据时，最好是用UNION ALL。

EXCEPT 找出位于第一个输入中但不位于第二个输入中的行数据。

INTERSECT 返回在两个输入中都出现的行。

6.聚合和旋转操作

6.1 mysql只支持流聚合。
- 流聚合依赖于获得的存储在GROUP BY列中的数据，如果SQL查询中包含的GROUP BY语句多于一行，流聚合会先根据GROUP BY对行进行排列。
6.2 开放架构是一种用于频繁更改架构的设计模式。对于利用开放架构设计的表，一般使用Pivoting技术来查询。
6.3 已知属性个数，可以用静态Pivoting。
6.4 Pivoting还可以用来做格式化聚合函数，一般用于报表输出，直观。
6.5 Unpivoting，Pivoting的反向操作。把行转为列。
6.6 mysql仅支持CUKE，不支持ROLLUP。ROLLUP是根据维度在数据结果集中进行的聚合操作。ROLLUP的优点是一次可以取得N次GROUP BY的结果，提高查询效率。单个维度没啥优势，第一个维度优势就大了。
6.7 ROLLUP不能喝ORDER BY一起使用，用LIMIT的话阅读性差，没有实际意义。
6.8 CUBE只在层次上对数据聚合，对所有维度进行聚合，具有N维度的列，需要2^N次分组操作。

7游标

7.1 游标不是恶魔，存在即合理。合理利用才是硬道理。
7.2 游标可以在存储过程，函数，触发器和事件中使用。
7.3 游标三个属性：
- Asensitive：数据库也可以不复制数据集
- Read Only：不可更新
- NonScrollable：游标只能向一个方向移动，不能跳过任何行。
7.4 游标的使用步骤：
- 定义游标
- 打开定义游标的变量
- 从游标取得数据
- 关闭游标
7.5从游标放入数据到变量时，变量的名字不能和列的名字一样，不然会变成NULL。

8.事务编程

8.1 事务概述。事务应该满足ACID。（原子性，一致性，隔离性，持久性）
- 原子性：每条语句，或者一个事务，当做一个整体，要么成功，要么失败回滚。事务是一个不可分割的单位。
- 一致性：事务将数据库从一个状态转变为另外一种一致的状态，数据库的完整性约束不会被改变。
- 隔离性：别称并发控制，可串行化，锁。保证事务提交前其他事务看不见。
- 持久性：凡是提交了的事务，都应该持久化到磁盘上，即使数据库崩溃，也能通过日志来恢复。
8.2 事务的分类
- 扁平事务
- 带保存点的扁平事务
- 链事务
- 嵌套事务
- 分布式事务

扁平事务

- 优点：使用简单，广泛使用。
- 缺点：不能提交事务的一部分，或分步骤提交。

带保存点的扁平事务

定义：除了支持扁平事务外，允许在是事务执行过程中回滚到同一事务中较早的一个状态。保存点同来通知系统记住事务当前的状态，以便以后发生错误，事务能回到该状态。 - 优点：能够灵活的回滚到某个状态，避免回滚到最初状态的消耗。

链事务

多个事务连续操作时，由于保存点是非持久的。带保存点的扁平事务，在系统崩溃时，需要重头开始执行到保存点，而不是从最近的一个保存点开始。

- 优点：多个事务时，保存点更灵活
- 缺点：事务仅能回滚到当前事务的最近保存点。

嵌套事务

不是mysql原生的，可以通过带保存点的扁平事务来模拟。但是要注意各个事务锁的问题。

所有工作由叶子点来完成，只有叶子点具有访问数据，发送消息，获取其他类型的资源。父节点或顶点节点只是起到逻辑控制，何时调用子事务的事情。

分布式事务

在分布式环境下运行的扁平事务。

8.3 事务控制语句

START TRANSATION / BEGIN
COMMIT
ROLLBACK
SAVEPOINT xxx
RELEASE SAVEPOINT xxx
ROLLBACK TO [SAVEPOINT] xxx
SET TRANSATION (READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE)

在命令行下开启事务可以：

BEGIN
 。。。
END

或者

START TRANSATION
 。。。
COMMIT

但是在存储过程中，mysql数据库的分析器会自动的把BEGIN识别为BEGIN...END，所以在存储过程中只能使用START TRANSATION

8.4 隐式提交的SQL语句

执行以下语句自动回提交事务

8.5 事务的隔离级别
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE

InnoDB存储引擎支持的隔离级别是REPEATABLE READ，使用Next-Key Lock的锁算法，避免了幻读的产生。在这个级别下已经可以完全保证事务的安全性。达到SERIALIZABLE的隔离级别，但是性能比其好。

隔离级别越低，事务请求的锁越少或保持锁的时间约短。

在READ COMMITTED事务隔离级别下，除了唯一性约束检查及外键约束的检查需要Gap lock，InnoDB存储引擎不会使用Gap Lock锁算法。（注意，mysql5.1以前，这个隔离级别只能工作在Replication(复制)的二进制日志为Row格式下。mysql5.1后不会了，也可以工作在STATEMENT格式下。）

最好建议是选择Row格式的二进制日志。因为记录的是行的变更，而不是SQL语句。避免出现不同步的现象

8.6 分布式事务编程
分布式事务：允许多个独立的事务资源参与到一个全局的事务中。全局事务中的事务要么全都成功，要么都回滚。在使用分布式事务时，InnoDB的隔离级别必须是SERIALIZABLE的。

InnoDB支持XA事务，并通过XA事务来支持分布式事务。异构分布式数据库可以通过XA事务实现分布式事务。

XA事务：

一个或多个资源管理器（提供访问事务资源的方法）
一个事务管理器（协调参与全局事务中的各个事务）
一个应用程序（定义事务的边界，指定全局事务中的操作）
8.7 不好的事务编程习惯
- 不要在循环中提交。无论是显式提交还是隐式提交。
- 不要自动提交。因为不同语言的API的自动提交设置是不一样的，容易出错。
- 不要使用自动回滚。最好是在程序中控制事务。
8.8 长事务

长事务最好分为批量小事务。因为长事务如果失败，回滚的代价太大了。

9.索引

9.1 缓冲池、顺序读取与随机读取

数据库一般需要持久化，持久化就要和磁盘打交道，因此就会出现访问磁盘的操作。随机访问磁盘是比较慢的，顺序访问会快很多。所以现在基本都有缓冲池的存在。缓冲池作为内存和磁盘的中间件，主要是缓存以页的方式缓存数据页，查询和修改时会先在缓存查看有没有，有就命中，效率就很高了。或者没有的话，先把数据页调出到缓存池中，再修改数据页，然后异步写入磁盘持久化。