MySQL实战45讲学习笔记

极客时间 林晓斌 mysql实战45讲

MySQL实战45讲-学习笔记

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

mysql逻辑架构

MySQL逻辑架构图

mysql大体分为server层和存储引擎层。

所有跨存储引擎的功能在server层实现，比如存储过程、触发器、视图。

存储引擎层负责数据存取，提供读写接口。InnoDB是mysql5.6版本后的默认存储引擎。

连接器

连接命令：

mysql -h [ip] -P [port] -u [user] -p

负责跟客户端建立连接、获取权限、维持和管理连接。

当用户名密码认证通过后，连接器会到权限表里查询用户拥有的权限，之后这个连接里的权限判断逻辑都依赖于此。意味着连接成功建立后，即使对这个用户的权限做了修改，也不影响已经存在的连接的权限，只有新建的连接才会使用新的权限设置。

数据库里长连接指连接成功后，如果客户端持续有请求则一直用同一个连接；短连接指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

尽量使用长连接减少开销。mysql在执行过程中临时使用的内存管理在连接对象里，在连接断开才会释放内存。如果长连接长时间累积可能导致out of memory，表现为mysql异常重启。

解放方案为1.定期断开长连接或程序判断执行过占用大内存的查询后断开，之后查询再重连；2.mysql5.7后可以在执行占用大内存的查询后通过mysql_reset_connection重新初始化连接资源，不需要重连和重新验证权限，将连接恢复到刚创建时的状态。

查询缓存

经验证，mysql8.0将查询缓存功能删掉了。

查询缓存可能以key-value形式缓存在内存中，key是语句，value是结果。

如果语句不在查询缓存中，会执行后续阶段，执行结果会存入查询缓存。

只要有对一个表的更新，这个表上的查询缓存会全部清空，失效非常频繁。对于更新压力大的数据库命中率非常低。很长时间更新一次的静态表才适用查询缓存。

mysql可按需使用。将query_cache_type 设置成DEMAND 则默认不用查询缓存，需要时用select SQL_CACHE ... 显式指定。

分析器

做词法分析和语法分析，判断是否符合语法规则。一般错误会提示第一个出错的位置。

优化器

决定表里有多个索引时使用哪个索引；join语句时决定表的连接顺序。这个阶段确定语句的执行方案。

执行器

开始执行时，先判断有没有查询权限。

如果有，就打开表继续执行，打开时根据表的引擎定义，使用引擎提供的接口。

如果字段没有索引，则调用引擎接口“取表的第一行”，然后调用接口“取下一行”循环取表的各行，每一行判断字段值，不满足则跳过，满足则将这行存入结果集中，最后把结果集返回客户端。

如果字段有索引，第一次调用“取满足条件的第一行”接口，之后循环“取满足条件的下一行”接口。

慢查询日志中rows_examined 字段表示这条语句执行过程中扫描了多少行，这个值在每次调用引擎获取数据行时累加。

有些场景下执行器调用一次接口，引擎内部扫描多行，因此引擎扫描行数跟rows_examined 并不完全相同。

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

查询语句的流程，更新语句同样走一遍。在查询缓存这个阶段，更新语句清空这个表上所有缓存结果。不一样的是更新流程涉及redo log（重做日志）和binlog（归档日志）。

redo log

InnoDB引擎特有的日志。

如果每一条更新都直接写进磁盘，需要在磁盘上找到这条记录并更新，IO和查询成本很大。

类比几十页的账本，对应磁盘；一块小黑板实时记录赊账信息，对应redo log。

WAL技术，Write-Ahead Logging，先写日志，再写磁盘。

有一条记录需要更新时，InnoDB会把记录写到redo log并更新内存，更新就算完成了，InnoDB会在适当的时候把操作记录更新到磁盘里。

InnoDB的redo log大小固定，比如配置为一组4个文件（0-3号），每个文件1GB，则总共可以记录4GB的操作。从头开始写，写到末尾又回到开头循环写。

redo log 上的两个位置： write pos 是当前记录的位置，一边写一边后移，写到3号末尾就回到0号开头。checkpoint 是当前要擦除的位置，后移并循环，擦除前先把记录更新到磁盘数据文件。

write pos 和 checkpoint 中空着的部分用来记录新的操作。当write pos 追上checkpoint ，不能再执行新的更新，需要先擦除记录，把checkpoint 推进。

redo log提供了crash-safe能力，即使数据库异常重启，之前提交的记录也会被记在redo log中，恢复后可以把redo log的记录再写入数据文件。

select @innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 参数设置成1，表示每次事务的redo log直接持久化到磁盘，保证mysql异常重启后数据不丢失。

binlog

binlog是逻辑架构图中Server层的日志，没有crash-safe能力。

redo log和binlog有3点不同：

1. redo log为InnoDB特有，binlog是mysql的server层实现，所有引擎都可用。
2. redo log是物理日志，记录在某个数据页上做了什么修改（硬盘分页？）；binlog是逻辑日志，记录“给id=2这行的c字段加1”。
3. redo log是循环写入；binlog是追加写入，到一定大小后切换到下一个，不会覆盖以前的。

select @sync_binlog

sync_binlog 设置成1，表示每次事务的binlog都持久化到磁盘，保证mysql异常重启后binlog不丢失。

执行器和InnoDB引擎在执行update语句时的内部流程

update T set c=c+1 where ID=2;

1. （执行器） 调用引擎接口取ID=2这行
2. （InnoDB） 如果这一行所在数据页在内存中，直接返回，否则先从磁盘把数据页读入内存再返回。
3. （执行器） 拿到行数据，执行c=c+1，得到新的行，调用引擎接口写入新行。
4. （InnoDB） 引擎将新数据更新到内存，将更新记录到redo log，redo log处于prepare状态，告知执行器执行完成，随时可以commit事务。
5. （执行器） 生成操作的binlog，并写入磁盘。调用引擎的提交事务接口。
6. （InnoDB） 把刚刚写入的redo log改成commit状态，更新完成。

redo log的写入拆成prepare 和 commit两步，这是“两阶段提交”。

两阶段提交

为了让redo log和binlog逻辑一致。

redo log大小有限，出现灾难需要恢复例如半个月前的表状态，需要binlog。

如果不使用两阶段提交，在写完第一个日志后，第二个日志没写完时crash，会导致crash恢复时，使用redo log恢复的数据（原库），与需要使用binlog恢复的数据（临时库）不一致。

例如redo log已经写完，记录了update T set c=c+1 where ID=2的操作，然后在写binlog没写完时crash，crash恢复后原库c=1，之后误删了表，需要用全量备份和binlog恢复，由于binlog中没有记录更新逻辑，所以恢复出来的临时库c=0，与原库不一致。

数据库扩容时也常用全量备份加binlog的方式实现，例如搭建一些备库增加系统的读能力，如果不用两阶段提交，上述的不一致就会变成出现主从数据库不一致的情况。

两阶段提交是跨系统维持数据逻辑一致时常用的一个方案。

一周一备vs一天一备

指全量备份。

一天一备“最长恢复时间”更短，最坏情况需要应用一天的binlog。系统对应的指标是RTO（恢复目标时间）。更频繁的全量备份需要更多存储空间。

03 事务隔离

事务是保证一组mysql操作要么全部成功，要么全部失败。事务支持在引擎层实现，MyISAM不支持事务，这是它被InnoDB取代的重要原因之一。

隔离级别

• 读未提交(read uncommitted)，一个事务还没提交，变更就可以被别的事务看到。
• 读提交(read committed)，一个事务提交之后，变更才会被其他事务看到。
• 可重复读(repeatable read)，一个事务执行过程中看到的数据，总是跟这个事务启动时看到的一致。
• 串行化(serializable)，对同一行记录，读写都会加锁，当出现读写锁冲突时，后访问事务必须等前一个事务完成才能执行。

以下表为例

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

事务A	事务B
启动事务，查询得到值1	启动事务
	查询得到值1
	将1改成2
查询得到值V1
	提交事务B
查询得到值V2
提交事务A
查询得到值V3

当隔离级别为读未提交时，V1=V2=V3=2。

当隔离级别为读提交时，V1=1，V2=V3=2。

当隔离级别为可重复读，V1=V2=1（事务在执行期间看到的数据一致），V3=2。

当隔离级别为串行化，B执行将1改2时会拿不到写锁，直到A提交后B才可以继续执行，然后等B提交，查询V3的事务才能继续执行，所以V1=V2=1，V3=2。

实现上，数据库里会创建一个视图，访问时以视图的逻辑结果为准。可重复读下视图在事务启动时创建，整个事务期间都用这个视图；读提交下，在每个sql语句开始时创建；读未提交直接返回记录上的最新值，没有视图概念；串行化通过加锁避免并行访问。

查看隔离级别

show variables like 'transaction_isolation';

以可重复读为例，事务隔离具体实现时，每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值通过回滚可以得到前一个状态的值。

$$[1_{read-viewA}\leftarrow 2_{read-viewB}\leftarrow 3{]}_{回滚段}\leftarrow 4_{read-viewC}$$

当前值为4，但不同时刻启动的事务有不同的视图，同一记录在系统中存在多个版本，就是数据库的多版本并发控制（MVCC）。

当系统里没有比这条回滚日志更早的视图时，回滚日志被删除。因此，尽量不要用长事务。

长事务意味着很老的事务视图，在它提交前，数据库里它可能用到的回滚记录都必须保留，占用大量磁盘存储空间，还占用锁资源。mysql5.5及更老，回滚日志跟数据字典放在ibdata文件里，即使长事务提交，回滚段被清理，文件也不会变小。

事务的启动方式

1. 显式启动，begin 或start transaction ，提交commit ，回滚rollback
2. set autocommit=0 这条命令会关闭这条线程的自动提交。执行select 事务启动，持续到主动commit 或rollback 或断开连接。

建议总是set autocommit=1

频繁使用事务时，使用commit work and chain语法减少交互，commit时自动启动下一个事务。

在information_schema 库的innodb_trx 表中查询长事务，以下查询持续时间超过60秒的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(), trx_started)) > 60;

04 深入浅出索引（上）

索引用于提高查询效率。

常见的索引模型（数据结构）

哈希表

以key-value存储数据的结构，通过哈希函数把key换算成确定的位置，把value存放在这个位置。哈希碰撞时用链表处理。

哈希表增加新的key时会很快，只需往后追加。缺点是做区间查询速度很慢。例如找出key在[x, y]区间内的所有值，就需要把区间扫描一遍。

适用于等值查询（查询key等于某个值）的场景，如Memcached以及一些NoSQL引擎。

有序数组

对于等值查询和区间查询都很快，等值查询用二分法，区间查询先二分查找左边再遍历数据直到判断条件到达右边。

插入很慢，需要挪动很多数据。适用静态存储引擎，比如某年的所有人口，这类不会再修改的数据。

搜索树

二叉搜索树等值查询O(log(N))，为了维持这个复杂度，需要保持平衡二叉树

大多数数据库存储不用二叉树，因为索引要写到磁盘上。

为了让查询尽量少读磁盘，就必须尽量少访问数据块，为此数据库使用N叉树，N取决于数据块大小，通常每一层数据存在一个块中。

InnoDB的索引模型

mysql中索引在存储引擎层实现，因此并没有统一的索引标准。即使多个引擎支持同一种类型的索引，底层的数据结构可能也不同。

在InnoDB中，表根据主键的顺序以索引的形式存放，这种存储方式的表称为索引组织表。

InnoDB的索引使用B+树，数据存储在B+树中。每一个索引对应一棵B+树。

如果InnoDB引擎的表在非主键上建立索引，则至少建立两棵B+树，即主键索引和非主键索引。

主键索引的索引值是主键的值，叶子节点存的是整行数据，也称为聚簇索引（clustered index）。

非主键索引的索引值是建立索引的列的值，叶子节点存的是主键的值，在InnoDB里，也称为二级索引（secondary index）。

create table T( id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

基于主键索引和普通索引查询的区别是：

如果select * from T where ID=500 只需搜索ID的B+树

如果条件where=5 则先搜索k索引树，得到ID，再搜索ID索引树。这个过程称为回表。

基于非主键索引的查询要多扫描一棵索引树，尽量用主键查询。

B+树索引的维护

如在ID索引树插入id=400的行，则需要挪动后面的数据空出位置。

如果此时R5所在数据页已满，申请新页，挪动部分数据，这个过程称为页分裂，影响性能和空间利用率（一个空间的数据分到两页中，利用率降低约50%）

相邻两页由于删除数据，空间利用率很低后，会合并数据页。

哪些场景用自增主键

如果表内有普通索引，由于二级索引的叶子节点内容是主键，显然主键长度越小，叶节点越小，普通索引占用空间越小。从性能和存储空间考虑，自增主键往往更合理。

也有些场景适合用业务字段做主键，如：

1. 只有一个索引
2. 该索引必须是唯一索引

典型的key-value场景。不用考虑普通索引叶节点大小的问题。

05 深入浅出索引（下）

覆盖索引

如果执行select ID from T where k between 3 and 5 只需要查ID值，它已经在k索引树上，不需要回表。在这个查询里，索引k覆盖了查询需求，称为覆盖索引。

覆盖索引减少树的搜索次数，显著提升查询性能，是常用优化手段。

假设有一个记录市民信息的表，身份证号是唯一标识，如果需求是根据证号查询市民信息，只需要在身份证号上建立索引就足够。

如果有一个高频请求，根据身份证查姓名，则可以建立（身份证号、姓名）联合索引，它可以在这个高频请求上用到覆盖索引，不需要回表，提高性能。但是注意索引字段的维护也是有代价的。

联合索引的字段顺序

只要满足最左前缀，就可以利用索引加速检索。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引支持最左前缀，因此模糊查询时，尽量明确条件的开头。也因此当有了（a，b）联合索引后，一般不需要单独在a上建立索引。

因此，第一原则是，如果通过调整字段顺序，可以少维护一个索引，那么这个顺序往往是优先考虑采用的。

以市民信息为例，为高频请求创建（身份证号，姓名）这个联合索引后，可以用它支持“根据身份证号查地址”。

如果有(a,b)的联合索引，又有条件里只有b的高频语句和只有a的高频语句，就需要同时维护(a,b)、(b)两个索引。这时考虑的原则是空间 。例如a字段比b字段大，就建立(a,b)和(b)，反之建立(b,a)和(a)

索引下推（index condition pushdown）

有联合索引(name, age)，有sql语句

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

这个语句在搜索索引树时只能用“张”（最左匹配，顺序是name，age，在name这里就模糊了，后面age=10用不上），之后：

mysql5.6前，不看age，直接回表，对比字段；

mysql5.6引入索引下推优化，在索引遍历过程中，对索引包含的字段先做判断，即看age，过滤掉不满足条件的记录，减少回表次数。

06 全局锁和表锁

mysql的锁大致分为全局锁、表级锁、行锁。

全局锁

对整个数据库实例加锁。mysql加全局读锁的命令是Flush tables with read lock （FTWRL），之后其他线程的数据更新语句（增删改）、数据定义语句（建表、改表结构）和更新类事务提交语句会被阻塞。

典型使用场景是全库逻辑备份。

全局读锁让整个库只读，可能导致业务停摆、主从延迟。

官方逻辑备份工具是mysqldump，当mysqldump使用参数-single-transaction时，导出数据前会启动一个事务保证拿到一致性视图，由于支持MVCC，这个过程中数据可以正常更新。

MyISAM不支持事务，更不支持可重复读的隔离级别，因此single-transaction方法只适用所有表都使用支持事务引擎的库，否则只能通过FTWRL方法备份。

不建议使用set global readonly=true 来设置全库只读。readonly的值可能用做其他逻辑，修改global变量影响面更大；在异常处理上，如果执行FTWRL后客户端异常断开，mysql会自动释放全局锁，而设置readonly之后，客户端断开mysql会保持readonly状态。

表级锁

有两种，表锁和元数据锁（meta data lock，MDL）

表锁语法为lock tables t1 read, t2 write ，可以用unlock tables 主动释放，客户端断开也会自动释放。执行后其他线程写t1，读写t2会阻塞，本线程在unlock tables 前也只能读t1，读写t2。

InnoDB支持行锁，一般不用lock tables。

一般只有引擎不支持行锁才会用到表锁。

MDL在访问一个表时会自动加上，在mysql5.5中引入。当对一个表增删改查时加MDL读锁；当对表做结构变更时加MDL写锁。

MDL是server层的锁。读锁之间不互斥，多个线程可以同时对一张表增删改查（这里理解是如果同时对一段记录读和写，更具体的交给引擎处理）；读写锁互斥，写锁之间互斥，保证变更表结构操作的安全性。

Session A	Session B	session C	session D
begin;
Select * from t limit 1;
	Select * from t limit 1;
		alter table t add f int;(blocked)
			select * from t limit 1;(blocked)

session A先启动，对表t加MDL读锁，session B也加读锁，不互斥，正常运行。

session C要加MDL写锁，因为A的MDL读锁还没释放，所以session C被阻塞。申请写锁的请求被加入优先级队列。

session D要申请MDL读锁，也会被加入优先级队列，因为C的写锁优先级更高，所以D只能排队，一直被阻塞，此时这个表已经完全不可以读写了。

如果表上查询频繁，且客户端有重试机制，超时后会另开新的session，那么这个库的线程很快就爆满。

事务中的MDL锁，在语句执行开始时申请，等事务提交后再释放，因此要避免长事务。

如何安全地给小表加字段

1. 解决长事务。在mysql的information_schema 库的innodb_trx 表中查询执行中的事务，如果要做DDL（数据定义语句，改表结构，DML数据操作语句，增删改查）的表刚好有长事务在执行，需要推迟DDL，或kill掉长事务。
2. alter table 里设定等待时间，在等待时间里拿不到就放弃，之后开发人员再重试命令。MariaDB合并了AliSQL的这个功能，目前这两个开源分支都支持DDL NOWAIT/WAIT n的语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tlb_name WAIT N add column ...

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

两阶段协议

行锁在引擎层由各个引擎自己实现。对不支持行锁的引擎比如MyISAM，并发控制只能用表锁，同一张表上任何时刻都只能有一个更新在执行。

在InnoDB事务中，行锁在需要时加上，事务结束时释放。这就是两阶段锁协议。

因此，如果事务中需要锁多个行，调整语句顺序，把最可能造成锁冲突、最有可能影响并发度的锁的申请时机往后放，减少锁那一行的时间。

死锁和死锁检测

事务A	事务B
begin;update t set k=k+1 where id=1;	begin;
	update t set k=k+1 where id=2;
update t set k=k+1 where id =2;
	update t set k=k+1 where id = 1;

A、B互相等待对方的行锁释放，出现死锁。

出现死锁后两种策略：

1. 等待直到超时，超时时间通过innodb_lock_wait_timeout 参数设置，InnoDB中默认值50s。
2. 死锁检测，发现死锁后主动回滚死锁链条中某一个事务，让其他事务继续执行。设置参数innodb_deadlock_detect 为 on 开启。

第一种策略设置太长在线服务无法接受，太短容易误把正常等待的事务误伤。主要用第二种策略。

死锁检测需要消耗cpu，单条线程检测的时间复杂度是O(n)，n为要更新同一行的线程数，因此总的时间复杂度为$$O(n^2)$$

解决方案一是如果确保这个业务一定不会死锁，临时关闭死锁检测。如果判断错误，可能出现大量超时，有损业务；二是控制并发度，一般在中间件、服务端、修改mysql源码里实现，基本思路是对相同行的更新，进入引擎前排队，InnoDB内部就可以避免大量的死锁检测工作。

也可以从逻辑上优化，比如一条记录分成十条记录的和，更新同一记录的冲突概率变为1/10，减少锁等待个数以及死锁检测的cpu消耗，业务上需要详细设计和特殊处理。

08 事务是隔离的还是不隔离的？

在InnoDB中，begin/start transaction 不是事务的起点，在执行到第一个操作InnoDB表的语句，事务才真正启动。在可重复读下，begin\start transaction 在第一个快照读的时候，得到一致性视图。想要马上启动一个事务，使用start transaction with consistent snapshot 命令，得到一致性视图。

mysql里有两个“视图”

1. view，用查询语句定义的虚拟表，查询方法和表一样，在调用时执行查询语句并生成结果。
2. InnoDB在实现MVCC时用的一致性视图，consistent read view，用于支持读提交和可重复读。

可重复读下的快照

在可重复读级别下，事务启动时拍下一个基于整个库的快照。

InnoDB每个事务有唯一的事务id，transaction id，在事务开始时向InnoDB事务系统申请，按申请顺序严格递增。

数据表中的每行记录，可能有多个版本，每次事务更新数据，都会生成一个新的数据版本，并且数据版本的事务id，即row trx_id 被赋值为transaction id

图中3个虚线箭头就是前文提到的undo log，V1、V2、V3在物理上不存在，需要V2的时候通过V4和undo log依次执行U3、U2计算。

InnoDB利用“所有数据都有多个版本”的特性，实现了秒级创建快照的能力。在实现时，为每个事务构造一个视图数组，保存事务启动瞬间，所有启动但未提交的事务id。数组最小值记为低水位，当前系统里已经创建过的事务id的最大值加1记为高水位。当前事务的id也会加入数组内。

这个视图数组和高水位，组成了当前事务的一致性视图。

数据版本的可见性规则是基于数据的row trx_id和这个视图对比结果得到。在当前事务启动的瞬间

1. 一个数据版本的row trx_id，如果小于低水位，则这个版本是已提交的事务生成的，对当前事务可见；
2. 如果大于等于高水位，则是由将来启动的事务生成，不可见；
3. 如果低水位<=row trx_id<高水位，则看是否在视图数组中，在的话不可见，反之可见。除此之外，当前事务改动生成的数据版本必然对自己可见。

简单的东西复杂化 换句话说成下面的规则更直观：对一个事务视图，看一个数据版本，除了自己的更新总是可见外有3种情况

1. 版本未提交，不可见（将来启动的事务或者在视图数组里的事务修改的）
2. 版本已提交，但是是在视图创建后提交，不可见（将来启动的事务或者在视图数组里的事务提交的）
3. 版本已提交，而且是在视图创建前提交，可见（废话一句 可能row trx_id大于低水位，也可能小于低水位，但一定不在视图数组内。）

更新逻辑

更新数据都是先读后写，读只能读当前的值，称为“当前读” 。

用以下InnoDB下的流程举例一致性视图、当前读、行锁的逻辑

事务A	事务B	事务C
start transaction with consistent snapshot;
	start transaction with consistent snapshot;
		start transaction with consistent snapshot;update t set k=k+1 where id=1;
	update t set k=k+1 where id=1;select k from t where id=1;
select k from t where id=1;commit;		commit;
	commit;

事务C先获得这条记录的写锁，并且在事务B更新的时候仍未释放，因此B被阻塞，在C释放锁后B加锁，进行当前读，然后更新；A没有更新语句，不做当前读，按照一致性视图的规则，B和C都属于高水位之后的事务，对A不可见。

可重复读和读提交的实现

可重复读的核心是一致性读；当事务更新的时候，只能用当前读；如果当前记录的行锁被占用，需要进入锁等待。

读提交和可重复读的主要区别:

1. 可重复读在事务第一次快照读（原文是事务开始）时创建一致性视图，之后事务里的其他查询共用这个视图。begin\start transaction 并没有真正开始事务，执行第一条操作表的语句才开始事务，开始第一次快照读才创建一致性视图，如果事务第一条语句是delete\update，是不会创建一致性视图的，直到select才创建一致性视图，在select之前，别的事务insert数据，之后用select是可以看到的。
2. 读提交在每一个语句执行前重新算出新的视图。

start transaction with consistent snapshot 意思是从这句开始创建持续整个事务的一致性视图，在读提交级别下，没有意义，等价于start transaction

表结构不支持可重复读，因为没有对应行数据，也没有row trx_id，只能遵循当前读的逻辑。

Mysql8.0把表结构放在InnoDB字典里，以后可能支持表结构可重复读。

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

唯一索引指用于创建索引的列的值是唯一的。

查询过程

对查询来说，普通索引在查找到满足条件的记录后，继续遍历查找下一个记录，直到条件不满足。

对唯一索引来说，因为有唯一性，所以找到满足条件的记录直接停止检索。

两者的性能差距微乎其微。在InnoDB中，数据页的默认大小是16KB，InnoDB读写数据到内存是以页为单位的，因此对普通索引，找到记录时，所需的数据通常都已经在内存里，一次“查找和判断下一条记录”的操作，通常不需要IO，只需要一次指针寻找和一次计算。如果满足条件的记录正好是一页的最后一条，则可能需要IO，但是对于整型字段，一页可以存放近千个key，需要IO的概率很低。

更新过程

当需要更新数据页时，如果已经在内存，就直接更新；如果不在，为了避免IO操作，在不影响数据一致性的前提下，InnoDB将更新操作缓存在change buffer，下一次查询需要访问此数据页再执行change buffer中相关的操作，得到最新的结果，这个过程称为merge。

change buffer可以持久化到磁盘。

merge的时机为访问数据页，系统后台线程定期，数据库正常关闭时。

change buffer减少磁盘IO可以明显提升性能，并且减少数据读入内存占用buffer pool，避免占用内存，提高内存利用率。

对唯一索引，要判断唯一性，因此数据页必须在内存中，用不上change buffer。只有普通索引可以用change buffer。

change buffer的大小通过参数innodb_change_buffer_max_size 设置，它为50的时候，表示change buffer的大小最多占用buffer pool的50%。

如果更新的目标页在内存中，普通索引和唯一索引更新的消耗几乎没有区别，唯一索引只多一个判断的cpu时间。

如果更新目标页不在内存，则唯一索引必须读数据页，有磁盘IO，是数据库成本最高的操作之一，普通索引只需更新change buffer。

change buffer使用场景

对写多读少的业务，页面写完后马上被访问的概率小，change buffer可以缓存较多操作，每次IO的收益较大。常见账单、日志系统。

对更新后很快查询的业务，操作记录在change buffer后马上触发merge，不会减少磁盘IO，还会增加维护change buffer的代价，反而降低性能。

索引选择小结

查询过程没有区别，更新过程普通索引更优，尽量选普通索引，对于更新完就查的业务，关闭change buffer。注意先保证业务正确性，如果业务代码保证不会写入重复数据，再讨论性能，如果业务不能保证，或本身就要求数据库做唯一性约束，还是要用唯一索引。

Change buffer和redo log

insert into t(id,k) values(id1,k1),(id2,k2);

执行这条语句，假设k索引树找到位置后，k1所在数据页在内存中，k2所在数据页不在。

1. page1在内存中，直接更新
2. page2不在内存，在change buffer中记录对page2的操作
3. 前两步写入redo log

做完以上事务就完成了。

ibdata1和t.ibd是磁盘数据，虚线表示在适当的时候写入。

如果读发生的时候，内存的数据都还在，那么

1. 读page1直接从内存返回，跟redo log无关。
2. 读page2，把page2从磁盘读入内存，应用change buffer里的操作，生成正确的数据版本并返回。

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

这里理解是不需要频繁且随机地把操作记录到磁盘中，读redo log所在的数据页时一次性顺序写入操作，节省随机写；change buffer避免随机的更新操作频繁地读入并修改数据页，节省随机读。

merge执行流程

1. 磁盘读数据页到内存
2. 从change buffer 里找到这个数据页的记录，依次应用，得到新的数据页
3. 写redo log，包含数据的变更和chang buffer的变更。

到此merge结束。磁盘上的数据页和change buffer还没有更新，后续写回磁盘属于另外的过程。

10 mysql为什么会选错索引

纠错

在mysql8.0下实验，确定隔离级别为可重复读，引擎为InnoDB。

CREATE TABLE `t` (
`id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata() begin
  declare i int;
  set i=1;
  while(i<=100000)do
insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

Session A	session B
start transaction with consistent snapshot;
	delete from t;call idata();
	explain select * from t where a between 10000 and 20000;
commit;

课件说session B的查询不会再选择索引a，但实际上仍然会选择，性能并不受影响。

select * from t where a between 10000 and 20000; /*Q1*/

select * from t force index(a) where a between 10000 and 20000;/*Q2*/

课件说Q1走全表扫描，Q2用了索引a，Q2比Q1快一倍，但实际上两者执行是一样的，explain也是一样的。

可能是mysql8.0.25做了优化。

优化器的逻辑（以下内容应该是针对mysql8.0.25以下版本，因为书中例子我复现不成立）

优化器选择索引，找到最优执行方案，用最小代价执行语句。扫描行数是影响代价因素之一，扫描越少，访问磁盘越少，消耗cpu也越少。还会结合是否使用临时表、是否排序、是否需要回表等因素。

一个索引上不同值的个数，称为基数，基数越大，索引区分度越好。用show index from tbl_name; 可以查看索引基数。

InnoDB默认选择N个数据页，统计页面上的不同值，得到一个平均值，乘以索引的页面数，得到索引基数，称为采样统计。当数据表变更行数超过1/M的时候会自动触发重新做一次采样统计。

mysql中有两种存储索引统计的方式，通过参数innodb_stats_persistent 选择：

• 设置为1（on）的时候，表示统计信息持久化，默认N=20，M=10，默认选择on
• 设置为0（off）的时候，统计信息只存在内存，默认N=8，M=16

在mysql错误判断扫描行数（explain查看）的时候，可以使用analyze table tbl_name; 命令修正统计信息。

以下内容在mysql8.0.25也成立

除了扫描行数，排序也会影响索引的选择。

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

选择索引a，需要扫描索引a的前1000个值，然后回表，取值，判断；选择b，需要扫描50001行。理应选择索引a。

explain实验结果：

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a,b           | b    | 5       | NULL | 49111 |     1.02 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

索引选择错误，判断要扫描49111行。

查询时使用select * from t force index(a)... 确实会更快，扫描行数也准确，但是性能相差不大，在mac上只相差0.07秒。

优化器选择索引b时因为使用索引b可以避免排序（索引本身有序），所以即使扫描行数多，优化器也判断代价更小。

当改为...order by b,a limit 1; 后两个索引都需要排序，扫描行数成为影响的主要条件，此时优化器选择索引a。

所以，优化器选择索引错误时有三种方法：

1. 强制选择索引，force index
2. 从数据特征上，在语义不变的情况下诱导优化器，比如order by b 变为order by b,a
3. 新建更合适的索引，或在分析后发现错误索引多余时，直接删掉错误的索引。

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

给字符串创建前缀索引应该用多长的前缀

mysql支持前缀索引，如alter table SUser add index index2(email(6)); 只取email字段的前6个字符做索引，默认是取整个字符串。

建立索引时关注区分度，区分度越高，基数越大，重复的键越少，索引效果越好。

可以先算出列上有多少个不同的值

select count(distinct email) as L from SUser;

然后取不同前缀看有多少不同的值

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%，从上述结果中找出不小于95%L的值，然后选需要更少字符的。

前缀索引的缺点

用不上覆盖索引对性能的优化。如id为主键时

select id,email from SUser where email='...'; 可以用上覆盖索引的优化，不需要回表。而如果用前缀索引，由于mysql不确定前缀索引的定义是否包含了完整字段的信息，不得不回表取整行再判断email字段的值。

需求只有等值查询时的优化

倒序存储

例如身份证号前6位很多相同，倒过来存储就可以使用前缀索引来节省空间以及提高查询效率

select field_list from t where id_card=reverse('input_id_card_string');

实践中在倒序存储以及建立前缀索引前，记得用count(distinct) 的方法验证区分度。

加一个冗余的hash字段

alter table t add id_card_crc int unsigned, add index(id_card_crc);

每次插入新的记录，同时用crc32()函数得到校验码。由于crc32可能冲突，判断时还要判断id_card是否精确相同。

id_card_crc需要索引的长度只有4个字节，比身份证的长度小很多。

倒序和hash异同点

都不支持范围查询，都只能等值查询。

不同点主要有三方面：

1. hash需要增加一个字段，倒序不会消耗额外存储空间，但是如果倒序存储建立前缀索引需要的长度不够短，那么前缀长度的消耗和额外建立hash字段的消耗可能也会抵消。
2. 倒序每次读写要调用reverse函数，hash需要调用crc32函数，reverse函数消耗的cpu更小些。
3. hash的查询性能更好更稳定。crc32冲突概率小，可以认为每次查询平均扫描行数接近1，倒序存储用的还是前缀索引，相比下可能还是会增加扫描行数。

12 为什么mysql会“抖”一下

“抖”，指一条sql语句，正常执行特别快，但有时特别慢，很难复现，持续时间很短。

当内存数据页与磁盘数据页内容不一致，称这个内存页为脏页，一致称为干净页。

“抖”可能是平时在写内存和redo log，抖的时候在刷脏页（flush），把内存的内容同步到磁盘。

flush的四种场景：

1. redo log写满，所有更新都被堵塞，checkpoint需要前推，移动位置之间的日志对应的脏页需要写到磁盘。
2. 内存不够，需要淘汰数据页，如果淘汰脏页，就需要先写入磁盘。
3. mysql空闲的时候。
4. mysql正常关闭之前会把所有脏页flush到磁盘。

InnoDB用buffer pool管理内存，缓冲池中的内存页有仍未使用、干净页、脏页三种状态。

InnoDB刷脏页的控制策略

刷脏页是常态，但两种情况会明显影响性能：

1. 一个查询要淘汰的脏页太多，导致响应时间明显变长。
2. 日志写满，所有更新堵住，写性能跌到0。

InnoDB需要知道所在主机的IO能力，控制刷脏页的速度，innodb_io_capacity 参数告诉InnoDB的磁盘能力，建议设置成磁盘的IOPS，这个值可以通过fio工具测试。

同时InnoDB不能占用全部磁盘IO能力，磁盘还要响应用户请求。

InnoDB刷脏页的速度主要参考脏页比例和redo log写盘速度。

innodb_max_dirty_pages_pct 参数表示脏页比例上限，默认75%，InnoDB会根据当前的脏页比例M，用F1(M)算出一个[0,100]的数字。

InnoDB每次写入redo log都有一个序号，当前写入序号跟checkpoint对应序号之间的差值为N，根据N算出[0,100]的数字，算法为F2(N)，N越大结果越大。

$$R=\max (F1(M),F2(N))$$ ，刷脏页的速度为$$R\%\ast innodb\_io\_capacity$$

要避免mysql“抖”，要合理设置innodb_io_capacity ，并关注脏页比例，不要让它经常接近75%

脏页比例通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

mysql刷脏页的“空间关联”机制

一旦一个查询在执行过程中需要先flush一个脏页，如果这个脏页旁边的数据页也是脏页，也会一起flush，并且还会看邻居的邻居，一直外推。

在使用机械硬盘时，这个策略很有意义，减少很多随机IO，减少磁盘物理上寻址移动的时间。机械硬盘的IOPS一般只有几百，减少随机IO可以大幅提升性能。

在使用SSD这类IOPS比较高的设备时，意义不大。

这个策略用innodb_flush_neighbors 参数控制，设为1表示会flush邻居脏页。

mysql8.0中，上述参数默认为0。

13 为什么表数据删掉一半，表文件大小不变（数据库表的空间回收）

针对InnoDB引擎讨论。一个InnoDB表包含两部分：表结构定义和数据。在mysql8.0以前，表结构存在.fm后缀文件里，在8.0后，允许把表结构定义放在系统数据表中。

innodb_file_per_table

表数据既可以存在 共享表空间 里，也可以是单独文件。

innodb_file_per_table设置为off，表示表的数据放在系统共享表空间，跟数据字典放一起；设置为on，表示每个InnoDB表数据存在一个.ibd后缀文件里。

在mysql5.6.6开始，默认为on

建议一直设置为on，在不需要这个表的时候，通过drop table 系统会直接删除ibd文件，而如果放在 共享表空间 中，即使表删掉空间也不会回收。接下来基于这个参数设置为on展开讨论。

数据删除流程

InnoDB的数据用B+树存储，要删除一个记录，InnoDB引擎只会把这个记录标记为删除，位置可复用，如果之后再插入一条符合范围条件的记录，可能会复用这个位置，但是磁盘文件不会缩小。

如果InnoDB整个数据页上的所有记录被删除，那么整个数据页就可以被复用。

如果相邻两个数据页利用率都很小，会合并数据页，并标记另一个可复用。

如果用delete 把整个表删除，所有数据页都会被标记可复用，但是磁盘文件不会变小。

delete 不能回收表空间，只会标记“可复用”，这些可以复用但是没有被使用的空间，看起来就像“空洞”。

插入数据也会造成“空洞”

如果数据按照索引顺序递增插入，那么索引是紧凑的。

如果随机插入，可能造成数据页分裂。

假设pageA已满

可见pageA的位置上留下了空洞（可能不止一个）

另外，更新索引上的值，也可以理解为删除旧的值，插入新值，也会造成空洞。

重建表

重建表可以去掉空洞，收缩空间。

实际上，InnoDB重建表，不会把整张表占满，每个页留了1/16给后续的更新使用，也就是说重建完的表在空间上并不是最紧凑的。

mysql5.5之前

表A需要去掉表中的空洞。我们可以新建与A结构相同的B，然后按照主键ID递增顺序，把数据一行一行从A读出再插入B。把B作为临时表，A导入B完成后，用B替换A，就完成了收缩空间的目的。

alter table A engine=InnoDB 执行流程跟上述差不多，mysql自己完成转存数据、交换表名、删除旧表。这是DDL（数据定义语句）操作。

临时表由server层创建。

这个过程如果有新数据写入到A，会造成丢失，因此整个DDL过程中，表A不能更新，即这个DDL不是online的。

mysql5.6之后

引入online DDL。

1. 建立一个临时文件，扫描表A主键的所有数据页;

2. 用数据页中表A的记录生成B+树，存储到临时文件中;

3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件(rowlog)中，对应的是图 中state2的状态;

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的 数据文件，对应的就是图中state3的状态;

5. 用临时文件替换表A的数据文件。

由于rowlog和重放操作的存在，重建过程中允许对A增删改。

临时文件是在InnoDB内部创建。

不管是否online，都很消耗cpu和io，online操作可以考虑在业务低峰使用。

MDL写锁退化

在上述online DDL流程中，alter语句启动时要获取MDL写锁，但是在真正拷贝数据时退化成MDL读锁，不阻塞增删改操作，同时要禁止其他线程对这个表做DDL，所以不能直接解锁。

online DDL最耗时的是拷贝数据的操作，这个过程上的是MDL读锁，可以接受增删改，因此相对整个DDL过程，写锁锁上的时间非常短，对业务来说可以认为是online的。

online和inplace

mysql5.5之前，存放A临时数据的位置叫tmp_table，是server层创建的临时表。

mysql5.6之后，根据A重建的数据放在tmp_file，是InnoDB内部创建的临时文件，整个过程在InnoDB内部完成，对server层来说没有把数据挪动到临时表，因此是一个inplace操作。

这里感觉没说清楚，临时表难道只存在于内存，不会有磁盘文件？不然的话临时表不也是一个文件吗？

mysql5.6后，重建表alter table t engine=InnoDB 其实是alter table t engine=innodb,ALGORITHM=inplace; ,对应的是mysql5.5之前的拷贝表方式alter table t engine=innodb,ALGORITHM=copy;

加全文索引的操作是inplace的，会阻塞增删改操作，是非online的。

1. DDL如果是online的，一定是inplace的
2. inplace的不一定是online的，截止mysql8.0，添加全文索引fulltext index和空间索引spatial index就属于这种情况。

optimize table、analyze table、alter table区别

• mysql5.6起，默认就是上述重建（recreate）表的流程
• analyze table只是对索引信息重新统计，没有修改数据，这个过程加MDL读锁。
• optimize table等于recreate+analyze

14 count(*)这么慢怎么办？

count(*)的实现方式

不同引擎实现方式不一样

• MyISAM把一个表的总行数存在磁盘上，执行count(*)时直接返回
• InnoDB把数据逐行从引擎里读出计数

这篇讨论没有过滤条件的count，如果加了where，myisam也不能返回这么快。

InnoDB的事务默认隔离级别是可重复读，代码上通过MVCC实现。每一行都要判断是否对这个会话可见，因此只能逐行读出计算count。

在保证逻辑正确前提下，尽量减少扫描数据量，是数据库系统设计的通用法则。 InnoDB是索引组织表，主键索引的叶节点是数据，普通索引叶节点是主键值，比主键索引小很多，所以mysql优化器会找到最小的索引树遍历得到count。

show table status 输出的TABLE_ROWS是从索引统计值得到的，索引统计值是采样估算的，误差可能达到40%到50%。

InnoDB表我们只能自己计总行数

基本思路是找一个地方把行数存起来。

缓存系统保存计数

用redis保存表的总行数，读写都很快，问题是

• redis异常重启时，缓存计数的最新操作可能会丢失。但是异常重启不多见，重启后做count(*)再更新计数，成本仍可以接受
• 由于时序问题，即使redis正常工作，计数值也可能不准。比如在插入新行和写redis中间，读取最新行数以及最新更新的几行，得到的行数跟最新更新的行是对应不上的。

用数据库保存计数

InnoDB支持redo log，崩溃恢复不丢数据。

用事务来解决时序问题。

时刻	会话A	会话B
T1
T2	begin； 表C中计数值加1；
T3		begin； 读表C计数值； 查询最近100条记录； commit；
T4	插入一行数据R； commit；

可见如果是redis，因为没有事务，会出错，而InnoDB用事务可以得到正确结果。

不同count的性能

以下基于InnoDB

count是一个聚合函数，对返回的结果集，逐行判断，如果count的参数不是NULL，就累计值加1，否则不加，最后返回累计值。

count(*)、count(主键id)、count(1) 都表示返回满足条件的结果集总行数，count(字段) 表示返回满足条件的数据行里，参数“字段”不是NULL的总个数。

分析性能的原则：

1. server层要什么就给什么
2. InnoDB只给必要的值
3. 优化器只优化了count(*) 的语义为取行数，其他优化并没有做

• count(主键id) ，引擎遍历整张表，取出每一行的id值，返回给server层，server层判断id不可能为NULL，直接按行累加值
• count(1) 遍历整张表，不取值，返回给server层，server层对每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。比count(主键id) 快，后者涉及解析数据行和拷贝字段值
• count(字段) 如果字段定义是not null，则遍历整张表，取出字段值，返回给server层，server层判断不可能为null，按行累加；如果字段定于允许为null，则server层判断有可能是null，需要把字段值取出来判断不是null才累加
• count(*) 专门做了优化，不取值，server层判断肯定不是null，按行累加

效率排序count(字段) < count(主键id) < count(1) ≈ count(*)

15 答疑（一）：日志和索引相关

两阶段提交的不同瞬间crash怎么办

update T set c=c+1 where id=2;

两阶段提交参考02

1. （执行器） 调用引擎接口取ID=2这行
2. （InnoDB） 如果这一行所在数据页在内存中，直接返回，否则先从磁盘把数据页读入内存再返回。
3. （执行器） 拿到行数据，执行c=c+1，得到新的行，调用引擎接口写入新行。
4. （InnoDB） 引擎将新数据更新到内存，将更新记录到redo log，redo log处于prepare状态，告知执行器执行完成，随时可以commit事务。
5. （执行器） 生成操作的binlog，并写入磁盘。调用引擎的提交事务接口。
6. （InnoDB） 把刚刚写入的redo log改成commit状态，更新完成。

redo log的写入拆成prepare 和 commit两步，这是“两阶段提交”。

commit语句执行的时候，包含commit步骤。

时刻A，mysql崩溃，redo log未提交，binlog还没写，崩溃恢复时，这个事务回滚，binlog没写，所以也不会传到备库。

崩溃恢复时的判断规则：

1. 如果redo log里事务已经有了commit标识，直接提交。
2. 如果redo log里面事务只有完整的prepare，判断对应的事务binlog是否完整，是则提交事务，否则会滚事务。

时刻B对应2中binlog完整，所以崩溃恢复后事务会提交。

mysql怎么知道binlog完整

一个事务的binlog有完整的格式

• statement格式的binlog最后有COMMIT
• row格式的binlog最后会有一个XID event

在mysql5.6.2后引入binlog-checksum参数，mysql通过checksum校验的结果确认事务binlog完整性

redo log和binlog怎么关联

有一个共同的数据字段叫XID。崩溃恢复时顺序扫描redo log：

• 碰到有commit的redo log，直接提交事务
• 碰到只有prepare没有commit的redo log，拿着XID去binlog找对应的事务

这里没说如果binlog没有对应事务怎么办，按照前文应该是回滚。

处于prepare的redo log加完整binlog，重启就能恢复，mysql为什么要这么设计？

这里问题感觉没说清楚，问的应该是为什么prepare的redo log加完整binlog不回滚而是提交？

看前文时刻B，这时候mysql崩溃，binlog已经写入，之后会被从库或用这个binlog恢复出来的库使用，因此为了保证数据一致性，主库上也要提交这个事务。

为什么要两阶段提交？先写完redo log再写binlog，崩溃恢复的时候必须两个日志都完整才恢复，是否可行？

两阶段提交是经典的分布式系统问题，非mysql独有。

对InnoDB，如果redo log已经提交，则事务不能回滚（如果这时还允许回滚可能会覆盖掉别的事务的更新）。如果redo log不prepare，直接提交，这时binlog写入失败，因为不能回滚，数据和binlog不一致。

不引入redo log，只用binlog做崩溃恢复和归档行不行？

历史原因，mysql原生引擎MyISAM设计时就不支持崩溃恢复。

InnoDB作为mysql插件加入之前，已经是一个支持崩溃恢复和事务的引擎了。接入mysql后发现binlog不支持崩溃恢复自然就用自有的redo log。

实现上的原因，binlog是逻辑日志，没有能力恢复数据页。InnoDB使用WAL技术，执行事务时，写完内存和日志，事务就算完成。之后崩溃要依赖于日志恢复数据页，binlog做不到。

如果要优化binlog让它记录数据页的更改，那跟做一个redo log没区别。

能不能只用redo log

redo log大小有限，循环写，不能归档。比如要恢复到半个月之前，只能依赖binlog。

除此之外，mysql本身以及很多公司业务都依赖于binlog。

redo log一般多大

太小的话很快写满，会经常强行刷redo log，WAL机制的能力发挥不出来，并引起change buffer merge，表现就是数据库写性能经常下跌。

常见的几个T的硬盘直接将redo log设置成4个1G的文件。

数据最终写入磁盘，是由redo log写吗？

redo log并没有记录数据页的完整数据，并没有能力去更新磁盘数据页。

1. 如果是正常运行的实例，脏页被写入磁盘，这个过程跟redo log毫无关系。

2. 在崩溃恢复的场景中，InnoDB如果判断一个数据页在崩溃时丢失了更新，会把数据页读入内存，然后让redo log更新内存内容，数据页变为脏页，回到1。

redo log buffer是什么？

事务要在commit之后才写到redo log文件里。而一个事务的执行过程中，可能有多个语句，写多次日志，这时就把日志先写到内存，即redo log buffer。

真正把日志写到redo log文件即ib_logfile+数字文件，是在执行commit语句时。

以上说的是事务执行过程中不会主动去写磁盘，减少不必要的IO，但是如果内存不够、其他事务提交等情况，可能会被动写入磁盘。

16 order by怎么工作

全字段排序

explain 语句中Extra内容有“Using filesort”表示需要排序

mysql会给每个线程分配一块用于排序的内存，称为sort_buffer

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL, 
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;

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

通常情况下，这个语句执行流程如下：

1. 初始化sort_buffer，确定放入name，city，age三个字段
2. 从索引city找到第一个满足city条件的主键id
3. 到主键id索引取出整行，取name、city、age三个字段值，存入sort_buffer中
4. 到索引city取下一个记录的主键id
5. 重复3、4，直到city值不满足查询条件
6. 对sort_buffer中的数据按照name做快排
7. 取排序结果前1000行返回客户端

作者把这个流程称为全字段排序。

按name排序可能在内存完成，也可能需要使用磁盘临时文件辅助，取决于排序所需内存和参数sort_buffer_size ,这是mysql开辟的sort_buffer的大小。

以下方法确定排序语句是否用临时文件

SET optimizer_trace='enabled=on'; /*只对本线程有效*/

select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name='Innodb_rows_read';

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

select * from `information_schema`.`OPTIMIZER_TRACE` \G

select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name='Innodb_rows_read';

select @b-@a;

通过查看OPTIMIZER_TRACE的结果确认是否用临时文件

number_of_tmp_files 是使用的临时文件个数。内存放不下时，需要使用外部排序，一般用归并排序。mysql将需要排序的数据分成（大小适当的，我理解或许是sort_buffer的大小对应的，排序总归是要读入内存的）12份，每一份单独排序后存在这些临时文件里，然后再归并成一个有序的大文件。

sort_buffer_size 如果小于要排序的数据量，越小分的份数越多，number_of_tmp_files 越大。

exmained_rows 表示参与排序的行数，表中满足city=杭州的有4000行

sort_mode 里packed_additional_fields 表示排序过程对字符串做紧凑处理，即使name定义是varchar(16)，排序过程按实际长度分配空间。

在internal_tmp_disk_storage_engine 设置成MyISAM时，select @b-@a 的值是4000，表示整个执行过程只扫描4000行；internal_tmp_disk_storage_engine 默认是InnoDB，此时b-a的值是4001。因为查询OPTIMIZER_TRACE这个表时要用临时表，InnoDB引擎把数据从临时表取出会让Innodb_rows_read 加1。

rowid排序

如果查询要返回的字段很多，sort_buffer里要放的字段数多，同样内存里能放下的行数很少，需要