本文内容为:极客时间《mysql45讲》的内容

1.基础架构:一条SQL查询语句是如何执行的 ?

执行下面这个查询语句时 ,在 MySQL 内部的执行过程

select * from T where id = 10;

大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服
务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都
在这一层实现,比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、
Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成
为了默认存储引擎

连接器:

链接客户端,校验权限

缓存:

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

如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存
中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结
果,这个效率会很高。

但是很少使用缓存,因为只要更新表,表对应的缓存就全部失效

你可以将参数 query_cache_type 设置成
DEMAND,这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语
句,可以用 SQL_CACHE 显式指定,像下面这个语句一样:

需要注意的是,MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说 8.0 开始彻底没

select SQL_CACHE * from T where ID=10 ;

分析器:

分析sql语句,是否满足 MySQL 语法

如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒

优化器:

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)
的时候,决定各个表的连接顺序

执行器:

开始执行语句

开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没
有权限的错误

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T' 

如果有权限,就打开表继续执行。打开表的时候,优化器就会根据表的引擎定义,去使用这个引
擎提供的接口

2.日志系统:一条SQL更新语句是如何执行的?

流程和查询相似,一条update语句会对应表的所有删除缓存

redo log

InnoDB 引擎特有的日志

在MySQL 中,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就用了redo log(重做日志) 来提升更新效率

关键点就是先写日志,再写磁盘 ,mysql会先把记录写到redo log里面,并更新到内存,这个时候就算完成更新了,innoDB引擎会在适当的时候将这个操作记录更新到磁盘里

wirte pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据
文件

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

WAL的全称是Write-Ahead Logging,它的关键点就是先写日志,再写磁盘

binlog

binlog(归档日志) 是server层实现的,可用于恢复数据库,主从之间同步数据

Redo log是记录这个页 “做了什么改动”。
Binlog有两种模式,statement 格式的话是记sql语句, row格式会记录行的内容,记两条,
更新前和更新后都有

update 语句的执行流程图,图中浅色框表示是在 InnoDB 内部执行的,深色框表示是在执行器中执行的

两阶段提交

redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致

更多详细阅读:https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html

3.事务隔离:为什么你改了我还看不见?

SQL 标准的事务隔离级别包括:

  1. 读未提交(readuncommitted)

    一个事务还没提交时,它做的变更就能被别的事务看到

  2. 读提交(read committed)

    一个事务提交之后,它做的变更才会被其他事务看到

  3. 可重复读(repeatable read)

    一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的

  4. 串行化(serializable )

    顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。

  1. 在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。
  2. 在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候创建的。
  3. “读未提交”隔离级别下直接返回记录上的最新值,没有视图概念
  4. “串行化”隔离级别下直接用加锁的方式来避免并行访问

设置mysql事务隔离级别

将transaction-isolation 的值设置成 READ-COMMITTED

show variables like ‘transaction_isolation’; 查看当前事务隔离级别

oracle默认事务隔离级别为读提交

mysql默认事务隔离级别为可重复读

4.深入浅出索引

索引的出现其实就是为了提高数据查询的效率,就像书的目录一样

索引的常见模型

介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树

哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎。

哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎。

如果仅仅看查询效率,有序数组就是最好的数据结构了。但是,在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。

所以,有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有人口信息,这类不会再修改的数据

二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子

索引类型

分为主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引

根据上面的索引结构说明,我们来讨论一个问题:基于主键索引和普通索引的查询有什么区别?

  • 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+树;
  • 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。

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

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。
显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

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

覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。

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

如果现在有一个高频请求,要根据市民的身份证号查询他的姓名和年龄,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。

当然,索引字段的维护总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA,或者称为业务数据架构师的工作 (不建议这么做)

最左前缀原则

B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

在建立联合索引的时候,如何安排索引内的字段顺序?
这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询,又有基于 a、b 各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。
这时候,我们要考虑的原则就是空间了。比如上面这个市民表的情况,name 字段是比 age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引

索引下推

以联合索引(name, age)为例 。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:

select * from tuser where name like '张 %' and age=10 and ismale=1;

MySQL 5.6 之前 ,只能用 “张”,找到第一个满足条件的记录 ID3 ,从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。

在联合索引中,匹配name后会进行回表,然后从主键索引上找出数据行,在数据上进行age字段比对

MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

索引下推优化下,在联合索引中,先匹配name,在不回表的情况下,接着比对age字段,不满足条件的记录直接过滤,只有满足条件的的才会进行回表,减少回表次数

5.全局锁和表锁 :给表加个字段怎么有这么多阻碍

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

全局锁

全局锁就是对整个数据库实例加锁

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

全局锁的典型使用场景是,做全库逻辑备份

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。 (前提是引擎要支持这个隔离级别 )

single-transaction 方法只适用于所有的表使用事务引擎的库 (innodb)

表级锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

表锁的语法是 lock tables … read/write

与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象

另一类表级的锁是 MDL(metadata lock)

MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。

在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  1. 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  2. 读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

MDL 会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查询和更新。

我们可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁,因此可以正常执行。
之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要MDL 写锁,因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系,但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了,所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session再请求的话,这个库的线程很快就会爆满。
你现在应该知道了,事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放

6.行锁功过:怎么减少行锁对性能的影响?

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

实际上事务 B 的 update 语句会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。

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

死锁和死锁检测

事务 A 在等待事务 B 释放 id=2 的行锁,而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放,就是进入了死锁状态。当出现死锁以后,有两种策略:

  • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
  • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事
    务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑

在 InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的

所以,正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的

7.事务到底是隔离的还是不隔离的?

InnoDB 的行数据有多个版本,每个数据版本有自己的 row trx_id,每个事务或者语句有自己的up_limit_id。普通查询语句是一致性读,一致性读会根据 row trx_id 和 up_limit_id 的大小决定数据版本的可见性。

  • 对于可重复读,查询只承认在事务启动前就已经提交完成的数据;
  • 对于读提交,查询只承认在语句启动前就已经提交完成的数据;

而当前读,总是读取已经提交完成的最新版本

语句 Q1 返回的 k 的值是 3,而语句 Q2 返回的 k 的值是 1

更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读(current read) “

InnoDB 利用了“所有数据都有多个版本”的这个特性,实现了“秒级创建快照”的能力。版本号就是事务Id

8.普通索引和唯一索引,应该怎么选择?

对于查询过程来说:
a、普通索引,查到满足条件的第一个记录后,继续查找下一个记录,知道第一个不满足条件的记录
b、唯一索引,由于索引唯一性,查到第一个满足条件的记录后,停止检索

但是,两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。

对于更新过程来说:

概念:change buffer

当需要更新一个数据页,如果数据页在内存中就直接更新,如果不在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中的与这个页有关的操作。

change buffer是可以持久化的数据。在内存中有拷贝,也会被写入到磁盘上

purge:将change buffer中的操作应用到原数据页上,得到最新结果的过程,成为purge访问这个数据页会触发purge,系统有后台线程定期purge,在数据库正常关闭的过程中,也会执行purge

唯一索引的更新不能使用change buffer
change buffer用的是buffer pool里的内存,change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。
将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问,所以对更新性能的提升很明显。
change buffer使用场景
在一个数据页做purge之前,change buffer记录的变更越多,收益就越大。对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发purge过程。这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。所以,对于这种业务模式来说,change buffer反而起到了副作用。

索引的选择和实践:
尽可能使用普通索引。
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是
随机读磁盘的IO消耗。

命令:

参数:innodb_buffer_pool_size

介绍:配置缓冲池的大小,在内存允许的情况下,DBA往往会建议调大这个参数,越多数据和索引放到内存里,数据库的性能会越好。

参数:innodb_old_blocks_pct

介绍:老生代占整个LRU链长度的比例,默认是37,即整个LRU中新生代与老生代长度比例是63:37。

画外音:如果把这个参数设为100,就退化为普通LRU了。

参数:innodb_old_blocks_time

介绍:老生代停留时间窗口,单位是毫秒,默认是1000,即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件,才会被插入到新生代头部。

总结

(1)缓冲池(buffer pool)是一种常见的降低磁盘访问的机制;

(2)缓冲池通常以页(page)为单位缓存数据;

(3)缓冲池的常见管理算法是LRU,memcache,OS,InnoDB都使用了这种算法;

(4)InnoDB对普通LRU进行了优化:

  • 将缓冲池分为老生代和新生代,入缓冲池的页,优先进入老生代,页被访问,才进入新生代,以解决预读失效的问题
  • 页被访问,且在老生代停留时间超过配置阈值的,才进入新生代,以解决批量数据访问,大量热数据淘汰的问题

9.MySQL为什么有时候会选错索引?

优化器的逻辑

优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。

MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好

使用 show index 方法,看到一个索引的基数

从图中看到,这次的索引统计值(cardinality 列)虽然不够精确,但大体上还是差不多的,选错索引一定还有别的原因。
其实索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行

rows 这个字段表示的是预计扫描行数

rows 统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息

索引选择异常和处理

其实大多数时候优化器都能找到正确的索引,但偶尔你还是会碰到我们上面举例的这两种情况:原本可以执行得很快的 SQL 语句,执行速度却比你预期的慢很多,你应该怎么办呢?

第一种方法是,采用 force index 强行选择一个索引

不过很多程序员不喜欢使用 force index,一来这么写不优美,二来如果索引改了名字,这个语句也得改,显得很麻烦。而且如果以后迁移到别的数据库的话,这个语法还可能会不兼容

第二种方法是,可以考虑修改语句,引导 MySQL 使用我们期望的索引

比如,在这个例子里,显然把“order by b limit 1”改成 “order by b,a limit 1” ,语义的逻辑是相同的

![](mysql-one/force index.png)

之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。

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

10.怎么给字符串字段加索引?

比如,这两个在 email 字段上创建索引的语句:

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6)); 

第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;而第二个语句创建的index2 索引里面,对于每个记录都是只取前 6 个字节 (前缀索引)

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节(如:zhangs),所以占用的空间会更小,这就是使用前缀索引的优势

使用前缀索引后,可能会导致查询语句读数据的次数变多

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

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

mysql> select count(distinct email) as L from SUser; 

然后,依次选取不同长度的前缀来看这个值,比如我们要看一下 4~7 个字节的前缀索引,可以用这个语句:

mysql> select
    count(distinct left(email,4))as L4,
    count(distinct left(email,5))as L5,
    count(distinct left(email,6))as L6, 
    count(distinct left(email,7))as L7
from SUser; 

当然,使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6

前缀索引对覆盖索引的影响

使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素

如果使用 index1(即 email 整个字符串的索引结构)的话,可以利用覆盖索引,从index1 查到结果后直接就返回了,不需要回到 ID 索引再去查一次。而如果使用 index2(即email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值

小结

字符串字段创建索引的场景。我们来回顾一下,你可以使用的方式有:

  1. 直接创建完整索引,这样可能比较占用空间;
  2. 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
  3. 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
  4. 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不
    支持范围扫描

11.为什么我的MySQL会“抖”一下?

当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”

不难想象,平时执行很快的更新操作,其实就是在写内存和日志,而MySQL 偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)

那么,什么情况会引发数据库的 flush 过程呢?

第一种场景:对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作,把checkpoint 往前推进,redo log 留出空间可以继续写

第二种场景:系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘

第三种场景:当MySQL 认为系统“空闲” 时,会刷“脏页”

第四种场景:MySQL 正常关闭的情况下会把内存中的脏页都flush到磁盘上

分析一下上面四种场景对性能的影响

第三种属于系统空闲时间操作的没有什么压力,第四种是正常关闭也不会对性能有影响,所以我们主要分析前两种场景

第一种,“redo log 写满了,要 flush 脏页”,这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时候更新数会跌为 0

第二种,第二种是“内存不够用了,要先将脏页写到磁盘”,这种情况其实是常态。InnoDB 用缓冲池(buffer pool)管理内存

缓冲池中的内存页有三种状态:

  1. 第一种是,还没有使用的;
  2. 第二种是,使用了并且是干净页;
  3. 第三种是,使用了并且是脏页。

InnoDB 的策略是尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少。 而当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页,就直接释放出来复用;但如果是脏页呢,就必须将脏页先刷到磁盘,变成干净页后才能复用。

刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的 :

  1. 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
  2. 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;

所以,InnoDB 需要有控制脏页比例的机制,来尽量避免上面的这两种情况。

InnoDB 刷脏页的控制策略

设置innodb_io_capacity 参数,它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS 。磁盘的 IOPS 可以通过 fio 这个工具来测试

现在你知道了,InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况,你就要合理地设置 innodb_io_capacity 的值

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时,这个查询就可能要比平时慢了。而MySQL 中的一个机制,可能让你的查询会更慢:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷

在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话,我就建议你把 innodb_flush_neighbors的值设置成 0。因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。
在 MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了。

12.count(*)这么慢,我该怎么办?

count(*) 的实现方式
你首先要明确的是,在不同的 MySQL 引擎中,count(*) 有不同的实现方式。

  1. MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;
  2. 而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数

文章里讨论的是没有过滤条件的 count(*),如果加了 where 条件的话,MyISAM 表也是不能返回得这么快的

在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一

按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以我建议你,尽量使用 count(*)

13.答疑文章(一):日志和索引相关问题

主要集中在时刻 B,也就是 binlog 写完,redo log 还没 commit 前发生 crash,那崩溃恢复的时候 MySQL 会怎么处理?

我们先来看一下崩溃恢复时的判断规则

  1. 如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;
  2. 如果 redo log 里面的事务只有完整的 prepare,则判断对应的事务 binlog 是否存在并完
    整:
    a. 如果是,则提交事务;
    b. 否则,回滚事务;

这里,时刻 B 发生 crash 对应的就是 2(a) 的情况,崩溃恢复过程中事务会被提交

追问 1: MySQL 怎么知道 binlog 是完整的?

回答:一个事务的 binlog 是有完整格式的:

  1. statement 格式的 binlog,最后会有 COMMIT;
  2. row 格式的 binlog,最后会有一个 XID event;

另外,在 MySQL 5.6.2 版本以后,还引入了 binlog-checksum 参数,用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因,可能会在日志中间出错的情况,MySQL 可以通过校验 checksum 的结果来发现。所以,MySQL 还是有办法验证事务 binlog 的完整性的

追问 2:redo log 和 binlog 是怎么关联起来的?

回答:它们有一个共同的数据字段,叫 XID。崩溃恢复的时候,会按顺序扫描 redo log:

  1. 如果碰到既有 prepare、又有 commit 的 redo log,就直接提交;
  2. 如果碰到只有 parepare、而没有 commit 的 redo log,就拿着 XID 去 binlog 找对应的事务;

追问 3:处于 prepare 阶段的 redo log 加上完整 binlog,重启就能恢复,MySQL 为什么要这么设计?

回答:其实,这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 B,也就是 binlog 写完以后 MySQL 发生崩溃,这时候 binlog 已经写入了,之后就会被从库(或者用这个 binlog 恢复出来的库)使用。
所以,在主库上也要提交这个事务。采用这个策略,主库和备库的数据就保证了一致性。

追问 4:如果这样的话,为什么还要两阶段提交呢?干脆先 redo log 写完,再写binlog。崩溃恢复的时候,必须得两个日志都完整才可以。是不是一样的逻辑?

回答:其实,两阶段提交是经典的分布式系统问题,并不是 MySQL 独有的。如果必须要举一个场景,来说明这么做的必要性的话,那就是事务的持久性问题。对于 InnoDB 引擎来说,如果 redo log 提交完成了,事务就不能回滚(如果这还允许回滚,就可能覆盖掉别的事务的更新)。而如果 redo log 直接提交,然后 binlog 写入的时候失败,InnoDB 又回滚不了,数据和 binlog 日志又不一致了。
两阶段提交就是为了给所有人一个机会,当每个人都说“我 ok”的时候,再一起提交。

追问 8:正常运行中的实例,数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?

回答:这个问题其实问得非常好。这里涉及到了,“redo log 里面到底是什么“的问题。实际上,redo log 并没有记录数据页的完整数据,所以它并没有能力自己去更新磁盘数据页,也就不存在“数据最终落盘,是由 redo log 更新过去”的情况

  1. 如果是正常运行的实例的话,数据页被修改以后,跟磁盘的数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这个过程,甚至与 redo log 毫无关系。
  2. 在崩溃恢复场景中,InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,就会将它读到内存,然后让 redo log 更新内存内容。更新完成后,内存页变成脏页,就回到了第一种情况的状态

追问 9:redo log buffer 是什么?是先修改内存,还是先写 redo log 文件?

回答:这两个问题可以一起回答。
在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:

begin;
insert into t1 ...
insert into t2 ...
commit; 

这个事务要往两个表中插入记录,插入数据的过程中,生成的日志都得先保存起来,但又不能在还没 commit 的时候就直接写到 redo log 文件里。

所以,redo log buffer 就是一块内存,用来先存 redo 日志的。也就是说,在执行第一个insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。

但是,真正把日志写到 redo log 文件(文件名是 ib_logfile+ 数字),是在执行 commit 语句的时候做的。

单独执行一个更新语句的时候,InnoDB 会自己启动一个事务,在语句执行完成的时候提交。过程跟上面是一样的,只不过是“压缩”到了一个语句里面完成

14.“order by”是怎么工作的?

select city,name,age from t where city='杭州' order by name limit 1000 ; 

全字段排序

通常情况下,这个语句执行流程如下所示 :

  1. 初始化 sort_buffer,确定放入 name、city、age 这三个字段;
  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id,也就是图中的 ID_X;
  3. 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
  4. 从索引 city 取下一个记录的主键 id;
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y;
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序;
  7. 按照排序结果取前 1000 行返回给客户端

图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size

sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序

rowid 排序

在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。

SET max_length_for_sort_data = 16; 

max_length_for_sort_data,是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL 就认为单行太大,要换一个算法

新的算法放入 sort_buffer 的字段,只有要排序的列(即 name 字段)和主键 id。

但这时,排序的结果就因为少了 city 和 age 字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:

  1. 初始化 sort_buffer,确定放入两个字段,即 name 和 id;

  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id,也就是图中的 ID_X;

  3. 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;

  4. 从索引 city 取下一个记录的主键 id;

  5. 重复步骤 3、4 直到不满足 city=’杭州’条件为止,也就是图中的 ID_Y;

  6. 对 sort_buffer 中的数据按照字段 name 进行排序;

  7. 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字
    段返回给客户端

![](mysql-one/order rowid.png)

使用rowId进行排序需要进行回表,在根据主键索引查询一遍数据

max_length_for_sort_data 代表的是字节,如果查询的所有字段字节数大于设置的字节,则使用rowid排序,否则使用全字段排序

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次

可以排序更多行,但是需要再回到原表去取数据

如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就

会直接从内存里面返回查询结果了,不用再回到原表去取数据

如果内存够,就要多利用内存,尽量减少磁盘访问。

对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择

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

  1. 无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。

    因为优化器认为走二级索引再去回表成本比全表扫描排序更高

    所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序

  2. 无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.

    因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引

15.如何正确地显示随机消息?

内存临时表

首先,你会想到用 order by rand() 来实现这个逻辑。

mysql> select word from words order by rand() limit 3; 

explain 命令来看看这个语句的执行情况

Extra 字段显示 Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。

因此这个 Extra 的意思就是,需要临时表,并且需要在临时表上排序

对于 InnoDB 表来说,对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不

会导致多访问磁盘。优化器没有了这一层顾虑,那么它会优先考虑的,就是用于排序的行越小越好了,所

以,MySQL 这时就会选择 rowid 排序

这条语句的执行流程是这样的:

  1. 创建一个临时表。这个临时表使用的是 memory 引擎,表里有两个字段,第一个字段是double 类型,为了后面描述方便,记为字段 R,第二个字段是 varchar(64) 类型,记为字段 W。并且,这个表没有建索引。

  2. 从 words 表中,按主键顺序取出所有的 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中,到此,扫描行数是 10000。

  3. 现在临时表有 10000 行数据了,接下来你要在这个没有索引的内存临时表上,按照字段 R排序。

  4. 初始化 sort_buffer。sort_buffer 中有两个字段,一个是 double 类型,另一个是整型。

  5. 从内存临时表中一行一行地取出 R 值和位置信息(我后面会和你解释这里为什么是“位置信息”),分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加 10000,变成了 20000。

  6. 在 sort_buffer 中根据 R 的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。

  7. 排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出 word 值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003

应用的是归并排序算法

MySQL 的表是用什么方法来定位“一行数据”的

如果你创建的表没有主键,或者把一个表的主键删掉了,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid

来作为主键,rowid 名字的来历。实际上它表示的是:每个引擎用来唯一标识数据行的信息。

  1. 对于有主键的 InnoDB 表来说,这个 rowid 就是主键 ID;
  2. 对于没有主键的 InnoDB 表来说,这个 rowid 就是由系统生成的;
  3. MEMORY 引擎不是索引组织表。在这个例子里面,你可以认为它就是一个数组。因此,这个 rowid 其实就是数组的下标。

order by rand() 使用了内存临时表,内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小,默认值是 16M。如果临时表大小超过了 tmp_table_size,那么内存临时表就会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB,是由参数 internal_tmp_disk_storage_engine 控制的

优先队列算法

可以精确地只得到三个最小值,执行流程如下:

  1. 对于这 10000 个准备排序的 (R,rowid),先取前三行,构造成一个堆;
  2. 取下一个行 (R’,rowid’),跟当前堆里面最大的 R 比较,如果 R’小于 R,把这个(R,rowid) 从堆中去掉,换成 (R’,rowid’);
  3. 重复第 2 步,直到第 10000 个 (R’,rowid’) 完成比较。

如果需要维护的堆大小超过了我设置的 sort_buffer_size 大小,就只能使用归并排序算法。

16.为什么这些SQL语句逻辑相同,性能却差异巨大?

select count(*) from tradelog where month(t_modified)=7; 

因为对字段使用了month()函数,所以不会走索引

需要注意的是,优化器并不是要放弃使用这个索引。
在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified(走这个索引不应是查询,还有可能是遍历,这里选择的就是遍历索引 t_modified)

一定要注意,优化器会选择代价更小的方案,而这个方案并不固定

对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。

隐式类型转换

select * from tradelog where tradeid=110717; 

交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换

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

所以对于优化器来说,上面的语句相当于:

select * from tradelog where CAST(tradid AS signed int) = 110717; 

也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。

隐式字符编码转换

两个表使用的字符集不一样,会导致函数操作,进而导致优化器放弃走树搜索功能

连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因

三个例子,其实是在说同一件事儿,即:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能

索引字段不能进行函数操作,但是索引字段的参数可以玩函数

17.为什么我只查一行的语句,也执行这么慢?

第一类:查询长时间不返回

等 MDL 锁 ,导致查询阻塞

等 flush ,导致查询阻塞(不过一般不会有这个问题)

等行锁 ,导致查询阻塞

第二类:查询慢

坏查询不一定是慢查询

session A 先用 start transaction with consistent snapshot 命令启动了一个事务,之后 session B 才开始执行 update 语句

ession B 更新完 100 万次,生成了 100 万个回滚日志 (undo log)

带 lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;而 select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。

幻读是什么,幻读有什么问题?

幻读的定义

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

如何解决幻读?

间隙锁+行锁(间隙锁是在可重复读隔离级别下才会生效的)

现在你知道了,产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。
顾名思义,间隙锁,锁的就是两个值之间的空隙。比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。

*间隙锁,锁的就是两个值之间的空隙。比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。再加上 7 个间隙锁。这样就确保了无法再插入新的记录 *

这里的七个间隙值是查询语句进行了全表扫描,所以锁住了整个表,不能进行增删改操作

间隙锁之间都不存在冲突关系

间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间

使用行锁+间隙锁不会锁住表,只会锁住行锁和间隙锁之间的数据

18.为什么我只改一行的语句,锁这么多?

总结的加锁规则:

  1. 原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区
    间。
  2. 原则 2:查找过程中访问到的对象才会加锁。
  3. 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
  4. 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key
    lock 退化为间隙锁。
  5. 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。