Mysql实战45讲学习笔记(1)

01 | 基础架构：一条SQL查询语句是如何执行的？

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前
执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询
的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个
value 就会被直接返回给客户端。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清
空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新
压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很
长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

问题

我给你留一个问题吧，如果表 T 中没有字段 k，而你执行了这个语句 select * from T
where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in
‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

02 | 日志系统：一条SQL更新语句是如何执行的？

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功
能模块，最后到达存储引擎。

MySQL 可以恢复到半个月内任意一秒的状态

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘
如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

CheckPoint技术是为了协调CPU速度与缓冲速度之间的鸿沟

03 | 事务隔离：为什么你改了我还看不见？

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL
中，事务支持是在引擎层实现的。你现在知道，MySQL 是一个支持多引擎的系统，但并
不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是
MyISAM 被 InnoDB 取代的重要原因之一。

隔离性与隔离级别

提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即
原子性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”。
当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读
（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有
了“隔离级别”的概念。

隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC)。

04 | 深入浅出索引（上）

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样
对于数据库的表而言，索引其实就是它的“目录”

实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

增加新的 User时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

哈希表这种结构适用于只有等值查询的场景,有序数组在等值查询和范围查询场景中的性能就都非常优秀

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你
要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF ->
User2 这个路径得到。这个时间复杂度是 O(log(N))。
当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个
保证，更新的时间复杂度也是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引
擎中了。

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+树中的。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引
（secondary index）。

基于主键索引和普通索引的查询有什么区别？

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量
使用主键查询。

B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

05 | 深入浅出索引（下）

回到主键索引树搜索的过程，我们称为回表

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用
的性能优化手段。

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从
延迟。

备份为什么要加锁呢？
备份的时候可能表被修改

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，
MDL)。

07 | 行锁功过：怎么减少行锁对性能的影响？

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比
如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引
擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB
是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的
行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态

08 | 事务到底是隔离的还是不隔离的？

在 MySQL 里，有两个“视图”的概念：
一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结
果。创建视图的语法是 create view … ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，
用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔
离级别的实现。

09 | 普通索引和唯一索引，应该怎么选择？

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB 中，每个数据页的大小默认是 16KB。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在
内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change
buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的
时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方
式就能保证这个数据逻辑的正确性。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的
更新优化还是很明显的

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

10 | MySQL为什么有时候会选错索引？

索引选择异常和处理
一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。
第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引

第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

11 | 怎么给字符串字段加索引？

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。但，这同时带来的损失是，可能会增加额外的记录扫描次数。

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2
   的值；

2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；

3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次
   数变多。但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也
   就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查
询成本。

于是，你就有个问题：当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多
长的前缀呢？

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

小结

字符串字段创建索引的场景

12 | 为什么我的MySQL会“抖”一下？

第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页。

如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？

13 | 为什么表数据删掉一半，表文件大小不变？

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心。

14 | count(*)这么慢，我该怎么办？

在不同的 MySQL 引擎中，count(*) 有不同的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回
这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面
读出来，然后累积计数。

这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，如果加了
where 条件的话，MyISAM 表也是不能返回得这么快的。

因为不论是在事务支持、并发能力还是在数据安全方面，InnoDB 都优于 MyISAM。我猜你的表也一定是用了 InnoDB引擎。这就是当你的记录数越来越多的时候，计算一个表的总行数会越来越慢的原因。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子
节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历
哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来
遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则
之一。

MyISAM 表虽然 count(*) 很快，但是不支持事务；

show table status 命令虽然返回很快，但是不准确；

InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count()。

幻读的意思是“用一个事务里面，后一个请求看到的比之前相同请求看到的，多了记录出来”。

15 | 答疑文章（一）：日志和索引相关问题

16 | “order by”是怎么工作的？

为避免全表扫描，我们需要在 city 字段加上索引。

MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

17 | 如何正确地显示随机消息？

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作

为什么么索引用不上了呢？

下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。

案例二：隐式类型转换

1. 数据类型转换的规则是什么？
2. 为什么有数据类型转换，就需要走全索引扫描？

在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

案例三：隐式字符编码转换

小结
今天我给你举了三个例子，其实是在说同一件事儿，即：对索引字段做函数操作，可能会
破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一
样，因为要求在索引字段上做函数操作而导致了全索引扫描。

刚试了文中穿插得思考题:当主键是整数类型条件是字符串时，会走索引。
文中提到了当字符串和数字比较时会把字符串转化为数字，所以隐式转换不会应用到字段
上，所以可以走索引。
另外，select ‘a’ = 0 ; 的结果是1，说明无法转换成数字的字符串都被转换成0来处理了。

19 | 为什么我只查一行的语句，也执行这么慢？

构造一个表，并且在里面插入了 10 万行记录。

 CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into t values(i,i);
 set i=i+1;
 end while;
end;;
delimiter ;
call idata();

20 | 幻读是什么，幻读有什么问题？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

如何解决幻读？

现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

21 | 为什么我只改一行的语句，锁这么多？

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

“有行”才会加行锁。如
果查询条件没有命中行，那就加 next-key lock。当然，等值判断的时候，
需要加上优化 2（即：索引上的等值查询，向右遍历时且最后一个值不满足
等值条件的时候，next-key lock 退化为间隙锁。）。

23 | MySQL是怎么保证数据不丢的？

binlog 的写入机制
其实，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空binlog cache。状态如图 1 所示。

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

redo log 的写入机制