MySQL 45 讲学习笔记

在学习《MySQL 45 讲》的时候加上网上一些资料总结的笔记。

基础架构

首先看 MySql 的基础架构示意图：

连接器：第一步，连接数据库，就是由连接器来做的，连接器负责跟客户端建立连接、获取权限、维持和管理连接。
1. 如果用户名或密码不对，就会收到一个 "Access denied for user" 的错误；
2. 如果认证通过，之后权限判断逻辑都依赖于此时读到的权限，说明连接成功之后再修改权限，也不会影响已经存在连接的权限。
查询缓存：以 key-value 的形式存储，key 就是 sql 语句，但是尽量不要使用查询缓存，因为只要有更新，这个表上的查询缓存就会清空，对经常更新的表来说，缓存命中率很低。
分析器：分析器的作用就是解析语句，判断语法是否错误、指定列是否存在等。
优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引。
执行器：MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
开始执行之前，会先判断有没有对表的查询条件：如果没有会返回没有权限，如果有就会根据表的引擎定义，去使用这个引擎提供的接口。

日志系统

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。这里例如我们要执行 update T set c = c + 1 where id = 2，那么执行过程应该是：

执行器找到 id = 2 这一行，如果在内存中有，直接返回给执行器；否则需要先从磁盘读入内存，然后再返回；
执行器拿到行数据，把指定字段的值加上 1 ，得到新的数据，调用引擎接口把新数据写入内存，这时候，该数据被称为“脏页”；
然后将这个更新记录到 redo log 中，此时 redo log 处于 prepare 状态。然后告知执行器执行完成，随时可以提交事务；
执行器生成这个操作的 binlog ，如果 sync_binlog = 1（sync_binlog 是 binlog 持久化到磁盘的策略：sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将binlog_cache 中的数据强制写入磁盘），并将 binlog 写入磁盘；
执行器调用引擎的提交事务接口，redo log 状态改为 commit ，更新完成。等从 redo log 同步到磁盘后，“脏页” 变为 “干净页” 。

如下图，浅色是 InnoDb 内部执行的，深色是执行器执行的

1、Redo log（重做日志）

上面可以看到，Redo log 和 binlog，那么下面介绍这两个 log 的作用。

Redo log 是 InnoDb 引擎特有的日志，主要用于崩溃恢复、保证已提交的事务记录不会丢失，也可用于数据恢复；是保证持久化的关键。
binlog 是 Server 层的日志，主要用于数据恢复。

redolog 的大小是固定的，在mysql中可以通过修改配置参数 innodb_log_files_in_group 和innodb_log_file_size 配置日志文件数量和每个日志文件大小，redolog 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。

举个例子：Redo log 就像是临时的小账本，记录账目变动情况，在空闲时间或临时小账本满了之后再往全量账本上同步，这样的效率比每次都找出全量账本再修改速度要快得多。

MySQL 的数据最终是要持久化到磁盘的，但是磁盘读写分随机读写与顺序读写。顺序读写的速度是比随机读写快的。MySQL 的数据页读写是随机读写，如果每次都直接数据落地到磁盘，那么每次都是随机读写，所以 MySQL 引入了 Redo log 来作为临时账本，而且 Redo log 是顺序写的方式。
有了 Redo log ，InnoDb 就可以记录某个页做了什么变动，在空闲时间把数据落地到磁盘，这样保证即使数据库发生异常，之前的数据也不会丢失，这个能力被称为 crash-safe。

Redo log 实现图

Redo log 分为两部分：一是 redo log buffer ，该部分在内存中，是易失的，二是 redo log file，这才是持久化到磁盘上的，当数据到这里时，就保证了持久化。
为了确保每次日志都能写入到事务日志文件中，在每次将日志缓存( log buffer)中的日志写入日志文件的过程中会先经过操作系统内核空间的 os buffer，然后会调用一次操作系统的 fsync 操作刷入到 log file 中。

MySQL的 log buffer 写入到 log file 有如下三种磁盘写入策略，可以使用 innodb_flush_log_at_trx_commit 来控制，默认是 1。

innodb_flush_log_at_trx_commit=0，在提交事务时，InnoDB 不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。

innodb_flush_log_at_trx_commit=1，在每个事务提交时，InnoDB 立即将缓存中的 redo 日志回写到日志文件，并调用操作系统 fsync 刷新 IO 缓存。

innodb_flush_log_at_trx_commit=2，在每个事务提交时，InnoDB 立即将缓存中的 redo 日志回写到日志文件，但并不马上调用 fsync 来刷新 IO 缓存，而是每秒只做一次磁盘IO缓存刷新操作。

当设置为0，该模式速度最快，但不太安全，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1，该模式是最安全的，但也是最慢的一种方式。在 mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

redo log buffer 是全局共用的。

InnoDb 有一个后台线程，每隔一秒会把 redo log buffer 中的日志调用 write 写到文件系统的 page cache 也就是 OS Buffer 中，然后调用 fsync() 把 page cache 中的数据持久化到 logFiles 中。
这个定时操作会把 buffer 中所有的数据都持久化到磁盘上，不管有没有 commit，所以说一个没有提交的事务的 redo log 可能被持久化到磁盘上的。

还有两种别的情况，也会使一个没有提交的事务的 redo log 写入到磁盘中：

redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会去主动写盘，但是这个写盘动作只是 write ，而没有调用 fsync ，也就是只留在了文件系统的 page cache。（innodb_log_buffer_size 是 redo log 的写缓存大小）。
并行的事务提交时，而 innodb_flush_log_at_trx_commit 是 1，那么事务 A 写盘的时候会把事务 B 的 redo log buffer 一起持久化到磁盘。

2、binlog（归档日志）

主要用于主从复制和数据恢复，主从复制的场景中在Master 端会开启binlog ，然后将 binlog 发送到各个Slave 端，Slave 端重放binlog 从而达到Slave 端的数据和Master端的数据保持一致。
在 mysql5.1 及以后版本，隔离级别建议使用 RC ，binlog 使用 row 格式，因为可以避免特定情况下主从复制不一样的情况，且 RR 存在间隙锁， RC 比 RR 出现死锁的几率更大，还有就是在 RR 级别下条件列未命中索引会锁表，RC 会调用 un_lock 方法，把不满足条件的记录放锁。

blog.csdn.net/weixin_4246… www.cnblogs.com/fengtingxin…

数据恢复

mysqlbinlog --start-position=554 --stop-position=1074 /var/lib/mysql/binlog.000009 | mysql -u root –p 然后输入密码

启动 binlog 的方式：修改 my.ini 文件，添加以下配置然后重启 MySQL：

# Binary Logging
# MySQL 5.7.3 及以后版本,如果mysqld里没有设置server-id, 那么设置binlog后无法开启MySQL服务
log-bin = mysql-bin
# binlog日志格式，默认为STATEMENT：每一条SQL语句都会被记录；ROW：仅记录哪条数据被修改并且修改成什么样子，是binlog开启并且能恢复数据的关键；
binlog-format=Row
# binlog过期清理时间；
expire_logs_days=7
# binlog每个日志文件大小；
max_binlog_size=100m
# binlog缓存大小；
binlog_cache_size=4m
# 最大binlog缓存大小
max_binlog_cache_size=512m

一个事务的 binlog是不能被拆开的，不管事务多大，都会被一次性写入。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占的内存大小，如果大于这个大小，就要被暂存到磁盘。
在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。如下图：

write 指的是把日志写入到文件系统的 page cache（OS Buffer），并没有把数据持久化到磁盘。
fsync 才会持久化到磁盘。

sync_binlog

sync_binlog 是 binlog 持久化到磁盘的策略

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync，也就是说主机发生异常重启，会丢失 N 个事务的 binlog。

3、Redo log 和 binlog（归档日志）的区别

	Redo log	binlog
日志类型	物理日志，记录的是在某个数据页做了什么操作，包括页分裂、页合并等动作	逻辑日志，主要记录了这个语句的原始逻辑，比如 “给 id = 2 的这一行 c 字段 + 1”
适用场景	崩溃恢复(crash-safe)	适用于主从复制和数据恢复
实现方式	InnoDB 引擎层实现的	Server 层实现的，所有引擎都可以使用 binlog 日志
文件大小	大小是固定的，可以通过修改配置参数 innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件数量和每个日志文件大小	Server 层实现的，所有引擎都可以使用 binlog 日志
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志	通过追加的方式记录，当文件尺寸大于给配置值后，后续的日志会记录到新的文件上

4、undo log

是实现原子性的关键。

4.1、多版本并发控制（ MVCC ）

上面说到，同一条记录在系统中可以存在多个版本。MVCC 就是一种并发控制的方法，通过多个版本实现对数据库的并发访问，主要是为了在 MySQL InnoDb 的 RC、RR 两种隔离级别中提高数据库并发读写性能。
好处是：读不阻塞写，写也不阻塞读，利用 undo log 实现读已提交和可重复读，但是事务 A 先更新 name = ‘张三’，事务 B 更新name = '李四' 这种事务 A 更新丢失的问题不能解决。

4.2、当前读与快照读

当前读：像 select lock in share mode (共享锁)、select for update、update、 insert、delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读：单纯的 select 就是快照读，前提是隔离级别不是串行化，串行化读写都会加锁，就退变成了当前读，快照读的出现是为了更高的并发读性能。

所以有可能别的事务更新特别大数量，然后快照读的时候需要根据回滚日志大量操作才能获取到应该读的数据，也会影响一些效率。

如下图所示，就是一个记录被多个事务连续更新后的状态：

undo log 是逻辑日志，可以理解为：

· 当delete一条记录时，Undo Log中会记录一条对应的insert语句

· 当update一条记录时，它记录一条相反的update记录

实际上，InnoDb 每次进行增删改操作都会生成回滚日志，会记录与操作相反的逻辑与 row trx_id（创建这条记录的事务 ID），上图中的虚线就是 undo log，而上面的 V1、V2、V3 并不是真实存在的，每次获取都需要通过当前版本与 undo log 计算出来的，所谓的快照读，读的就是这种 “快照” 。
事务ID 是在事务开始的时候向 InnoDb 申请的，严格按照顺序递增，需要注意的是，事务真正启动的时机是开启事务之后第一条进行快照读语句的时候，如果想要直接启动一个事务，可以使用：start transaction with consistent snapshot 这个命令。
当然回滚日志不会一直存在，在系统里没有比这个回滚日志更早的 read-view 的时候，会把之前的回滚日志删除。

4.3、怎么实现事务回滚

上面说到，每个事务都有自己的 “快照”，那么事务回滚就只需要根据 undo log 里的属于自己的逻辑日志操作一遍即可。
假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以根据 undo log 计算出多个版本，这就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

Read View 它的结构如下：

4.4、怎么实现 RC、RR 隔离级别的读已提交和可重复读

事务的四种隔离级别

读未提交：一个事务可以读取另一个未提交事务的数据；
读已提交（RC）：两个不同的事务，事务 A 还没结束，事务 B 修改后并 commit ，再次查询可以读到 B 修改后的数据；
可重复读（RR）：两个不同的事务，事务 A 还没结束，事务 B 修改后并 commit ，再次查询，只能查到和事务开始的时候表里的数据，但有问题就是在当前读下会出现幻读（也可以说只是当前读产生一种的情况，因为快照读因为 MVCC 的存在，一般情况不可能出现幻读），即新事务 insert 并且 commit，事务 A 当前读可以查询到多了一条记录，但是在 RR 级别下，会因为有间隙锁的存在，解决部分当前读的幻读问题；
- 因为在先快照读，线程 2insert，线程 1 再当前读时是会出现幻读的 Innodb的RR到底有没有解决幻读？看不懂你打我！_rr能解决幻读吗_程序一逸的博客-CSDN博客
串行化：读写都会加锁。

生产环境隔离级别建议使用 RC ，binlog 使用 row 格式 zhuanlan.zhihu.com/p/59061106

怎么实现读已提交？

首先，InnoDb 的 RC、RR 两种隔离级别中，select 进行的都是快照读，而快照读首先是通过 ReadView 来判断读 undo log 中哪个版本中的快照数据的，那么判断方式如下：

RR 隔离级别可重复读，和 RC 隔离级别读已提交都是通过 MVCC 来解决的，只是生成 ReadView 的时机不一样。

读已提交是通过：每次 select 都生成一个新的 ReadView，里面的 m_ids 也就是活跃的事务 ID 集合不一样，所以在另外一个事务 commit 之后，再次 select 会读到已经提交的数据。

可重复读是通过：仅在第一次 select 时生成一个 ReadView，所以在这个事务中，只会查到同样的数据。

RR 隔离级别下的快照读没有幻读，因为每次都根据 ReadView 访问快照，当前读解决幻读是通过间隙锁进行加锁。间隙锁

5、flush 脏页

上面说到内存数据页和磁盘数据页内容不一致的时候，我们称这个内存为脏页。从 redo log 或者从内存中写入到磁盘后，称为干净页。

5.1、什么时候 flush 脏页

redo log 满了，需要 flush 脏页，出现这种情况后，整个系统就不接收更新操作了

内存不够用了，需要 flush 脏页

MySQL 空闲时

数据库关闭时

第三种和第四种对 MySQL 来说不需要关注“性能问题”。
第一种情况是尽量要避免的，因为出现了之后整个数据库都不能接收更新了。
第二种情况是常态。InnoDb 用缓冲池（ buffer pool ）管理内存，缓冲池中的内存页有三种状态：

第一种，是还没有使用的；

第二种，是使用了并且是干净页

第三种，是使用了并且是脏页

InnoDb 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
当读入的数据页没有在内存的时候，就必须从缓冲池中申请一个数据页，这时候需要把最久不使用的数据页从内存中淘汰：如果要淘汰的是干净页，那么直接释放出来；但如果是脏页，需要先把脏页刷到磁盘，变成干净页才能释放复用。
刷脏页的时候出现以下两种情况，都可能影响性能：

1.  一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2.  redo log 写满，写性能为 0，对敏感业务来说，不能接受。

5.2、InnoDb 刷脏页的控制策略

InnoDb 有刷脏页的控制策略来减少刷脏页对性能的影响。
需要 innodb_io_capacity 这个参数。它会告诉 InnoDb 你的磁盘能力，这个值尽量设置成磁盘的 IOPS。
合理的设置 innodb_io_capacity 的值，并且平时多关注脏页比例，不要让它经常接近 75% 。其中脏页比例可以通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到，具体命令参考如下：

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;

在 InnoDb 中，还有一个参数 innodb_flush_neighbors ，这个参数值为 1 的时候，会有连坐机制：

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，如果旁边的数据页刚好是脏页，就会把这个“邻居”带着一起刷掉，可以继续蔓延。
这个优化可以减少很多随机 IO，对机械硬盘时代很有意义，因为机械硬盘的随机 IOPS 一般只有几百，减少随机 IO 可以让系统性能大幅度提升。
而如果使用 SSD 这类 IOPS 比较高的设备，建议把 innodb_flush_neighbors 设置成 0，因为一般这时候 IOPS 不是瓶颈，这样可以更快的执行完必要的刷脏页数据，减少 SQL 响应时间。

在 MySQL8.0 默认值就是 0 了。

6、slow log（慢查询日志）

MySQL 慢查询日志是排查问题 SQL 语句，以及检查当前 MySQL 性能的一个重要功能。

索引

1、索引的好处

大大减少了服务器要扫描的数据量
帮助服务器避免排序临时表
将随机IO变成顺序IO

2、索引的常见模型

hash 表：等值查询快，范围查询需要一个个对比；
有序数组：等值查询与范围查询很快，但是往中间插入数据，后面的所有数据必须挪动，插入成本高；
二叉树：查询和插入都是 O(log(N)) ，但是索引是要在磁盘上的，数据量多的时候，树高变深，最差可能一次树高需要进行一次磁盘随机 IO，这时候耗时较长；
N 叉树： InnoDb 使用的就是这样的结构作为索引结构。

InnoDb 使用的是 B+ Tree，是 N 叉树的一种，结构如下图

3、B+ Tree 的优点

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
从上图可以看到，data 都是在叶子节点上的，这样的好处是每个页存储空间是有限的，如果是 B-Tree， data 和 key 存储在一块，data 太大的时候，每个页存储的节点会变少，那么就会导致多占用磁盘块；
每个节点不存 data，可以存更多的 key，这样有两个好处：一是降低树的高度，二是将数据范围变为多个区间，区间越多，数据检索越快。
叶子结点呈双向循环链表结构，顺序查询和分页查询速度会很快；

4、MySQL 里的 B+Tree

1. MyISAM 的 B+Tree

MyISAM 的叶子节点存放的是一个地址，地址指向的才是数据存放的位置。查找过程是根据主键找到地址，再根据地址查询对应数据。
存储引擎是 MyISAM, 在 data 目录下会看到3类文件：.frm、.myi、.myd

（1）.frm--表定义，是描述表结构的文件。

（2）.MYD--数据信息文件，是表的数据文件。

（3）.MYI--索引信息文件，是表数据文件中任何索引的数据树

2. InnoDb 的 B+Tree

主键索引的叶子节点存放的是整行数据，主键索引也被称为聚簇索引。如果没有主键，那么会生成一个长度为6字节的全局自增 row_id 来作为主键，对于用户是不可见的，Oracle的是可见的；
如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表，非主键索引也被称为二级索引。

5、怎么避免回表？

5.1、索引覆盖

- 假设 id 是主键，num 有索引，执行 select id from T where num between 1 and 5 ，我们只需要 id 
的值，id 在 num 索引的叶子节点上，无需再去查主键索引的树，这样的操作被称为索引覆盖。
- 再例如有一个高频请求：根据身份证号查询 name ，那么我们可以建立一个（身份证号，name）的索引，这样
也可以避免回表。当然这样可能会造成冗余索引，这时候就需要权衡了。

5.2、最左匹配组合索引：name,age

  //使用索引
  select * from table where name = ? and age =? ; 
  //使用索引,mysql有自己的优化器，会自动调整顺序
  select * from table where age = ? and name =?;
  //不会使用
  select * from table where age = ?;
  //最左匹配，使用索引
  select * from table where name = ?;
  // 使用索引，因为索引是顺序排序的，找到第一个满足条件的往后遍历到不满足条件
  select * from table where name like '王%';

5.3、索引下推

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

以联合索引（name, age）为例，检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”

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

在 MySQL 5.6 之前，只能从第一个满足 name like 张% 的条件开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
第一张图是<无索引下推执行流程>，第二张是<索引下推执行流程>，可以看到索引下推之后回表次数减少到了两次。

6、索引维护

假设现在的索引结构如下：

如果要插入700，那么直接在后面追加就可以了，但是如果要插入400，就需要把后续的数据往后挪，更糟糕的情况是：R5 所在的数据页已经满了，这时候就会申请新的数据页，然后挪动部分数据过去，这个过程称为页分裂，消耗性能。而且本来一个页上的数据分到两个页上，还会浪费数据页利用率。当两个页利用率很低的时候，还会进行页合并。
重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，所以自增主键是个不错的选择。
如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。
不论是删除主键还是创建主键，都会将整个表重建，所以需要重建主键索引不如使用：alter table TableName engine=InnoDB。

7、索引选择

索引选择是优化器的事，优化器找到自认为代价最小的方式去执行语句。

7.1、索引选择的因素

扫描行数

使用 show index 方法，能看到一个索引的基数，其实并不准确，那么MySQL是怎么得到索引的基数呢？答案是采样统计，InnoDb 会默认选择 N 个数据页，统计这些页面的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数值。

而数据表是在变化的，所以当变更的数据行数超过 1/M 的时候，就会自动触发重新做一次索引统计。在MySQL中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是20，M 是10。

设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是8，M 是16。

但是索引统计只是一个输入，对一个具体的语句来说，优化器还要判断执行这个语句本身要扫描多少行。可以通过 explain 语句里面的 rows 命令来查看预计扫描行数，这个扫描行数如果是普通索引，MySQL 还会把回表的代价计算进去，但是不在 rows 里展示。

如果索引统计信息不对，那么可以使用 analyze table tableName 命令重新统计索引信息。

SQL 语句

比如我们现在有表 t，t 有 a,b 两个字段，以同样的值插入 10W 数据，我们执行下面的语句

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

如果选择索引 a，那么回表获取到数据之后，只需要在这 1000 条数据的基础上判断 b，而如果选择索引 b，就需要回表获取到 50000 条数据，再进行判断 a，所以这样看着选择索引 a 是比较好的，但是如果用 explain 看就会发现使用的是索引 b，这是为什么呢？答案是 order by b ，因为索引是有序的，所以优化器认为选择索引 b 不用进行排序，即使扫描行数多，但还是被判定使用索引 b。

那么在要同样的查询结果的情况下，我们可以使用以下几种方法：

把“order by b limit 1” 改成 “order by b,a limit 1”，这样 a，b 都会进行排序，扫描行数成了影响决策的主要条件，那么就会使用索引 a，这样这样会是修改语义，不常用。
使用 force index(a) 来指定使用索引 a，这样如果要改列的名字，或者迁移到别的数据库，还可能不兼容，也不常用.
诱导优化器使用某个索引，如以下语句，我们用 limit 100 让优化器意识到，使用 b 索引代价是很高的，也不具备通用性。
在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

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

8、如何给字符串加索引？

1、前缀索引

比如给 email 字段加索引，后面几位都是 @XXX.XXX 类似的，基本相同，那么我们可以使用前缀索引，定义好长度，既能节省空间，又不用增加太多的查询成本。

1.1 但是我们怎么确定应该使用多长的长度作为索引呢？

比如我们要看一下 4~7 个字节的前缀在这个列上有多少不重复的，可以用这个语句：

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;

这个 SQL 语句可以查询出不重复的有多少条，与总行数越接近说明越合适做索引。当然，需要设定一个可以接受的损失比例，然后选择占用空间最小的前缀索引。

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

使用前缀索引的话，不管你的前缀是多长的，InnoDb 都需要回到主键索引再回表查一次，就意味着无法用上索引覆盖对查询性能的优化了。

1.3 样例

如果表里只有一个市的公民身份证信息，首先同一个县的身份证前 6 位是相同的，如果使用前缀索引，那么至少需要 12 位前缀索引才能满足区分度要求，那我们有什么方法呢？

使用倒序存储。存的时候倒序存，查的时候使用 reverse('input_id_card_string') 转换一下，那么这时候前六位可能就满足了区分度要求。
使用 hash 字段。在表上再加一个字段，来保存身份证的校验码，同时在这个身份证号加索引。

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

每次插入新纪录的时候，都是用 crc32() 这个函数得到校验码到这个新字段，由于这个校验码可能冲突，所以查询语句 where 部分需要判断 id_card 的值是否精确相同。

 select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样索引的长度就变成了 4 个字节。

倒序存储和使用 hash 字段这两种方法的比较：

首先它们都不支持范围查询，只支持等值查询，区别体现在以下 3 个方面：

从占用额外空间来看，hash 需要新加一个字段

从 CPU 消耗方面，倒序方式每次额外调用 reverse 函数，而 hash 方式需要调用 crc32() ，从函数的的复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

从查询效率来看，使用 hash 方法的查询性能相对稳定些。因为 crc32() 虽然会重复，但是概率非常小；而倒序存放使用的还是前缀索引，扫描行数相比来说会增加。

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

答1：因为后缀都是相同的 @gmail.com ，所以可以使用数字类型来存储前面的九位数字，使用 bigint 才占用了 8 个字节，其实可以把这看成一种 hash。
答2：因为一个学校一年就算 2W 个学生，五十年才 100W，这个表肯定是个小表，直接存原来的字符串，包含了“优化成本和收益”的思想。

9、放弃走树索引搜索的情况

首先说一个特殊的例子：如果有字段 b varchar(10)，有索引，10W 个值都是 1234567890，然后用 where b = '1234567890asd' 查询，这时候并不会因为条件超过了 varchar 定义而直接返回结果，而是会传给引擎执行的时候截取前十个字符，那就会进行 10W 次回表，回表之后把数据返回给 server 层，到 server 层判断都不等于 1324657890asd，返回空。

like 查询条件是以 % 开头时不会使用索引；

条件中有 or，有一处条件没有索引或者是联合索引，则不会使用索引，如果全部都有索引，那么按第 3 条规则；

Mysql 估计使用索引比全表扫描慢的时候，不管所有规则，都以这一条为前提；

<> 有几率会使索引失效，会根据符合哪个条件的数据少，而使用某一个索引，主要还是见第 3 条

例如 num1、2、3 都是有索引的，三列最小的数据都是2，num1 最小的数据为49995、num2、num3 最小的为49998，但是如果是联合索引，还是会走索引的

比如 SELECT * FROM test where num2 > 2 and num3 < 2 and num1 > 3，会使用 num3 索引；

比如 SELECT * FROM test where num2 > 49990 and num3 < 3 and num1 > 2，会使用 num2 索引

比如 SELECT * FROM test where num2 > 2 and num3 < 3 and num1 > 3，不使用索引；

对索引字段做函数操作会使索引的有序性失效；

is null， is not null 也可以走索引在查询的列上使用函数或者有运算会导致索引失效，MySQL 中决定使不使用某个索引执行查询的依据就是成本够不够小，如果 null 值很多，还是会用到索引的。一个大概 3 万数据的表，如果只有 10 多个记录是 null 值，is null 走索引，not null 和 != 没走索引，如果大部分都是 null 值，只有部分几条数据有值，is null，not null 和 != 都走索引。

当 in() 中的数据很大时，不走索引，其实就是和第 3 条一样；

显式或隐式的对索引字段函数操作，如下情况：

1、条件字段函数操作

假设有一个交易系统，其中交易表 tradelog 表包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果已经记录了 2016-2018 年的数据，有10W 条数据，这时要查询所有 7 月份的交易记录总数，如果是以下写法，会发现效率很慢

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

看一下执行计划：

key=t_modified 说明使用了"t_modified"索引，而使用这个索引的原因是因为这个索引树比主键索引树更小，Using index 说明使用了索引覆盖，row = 100335 说明扫描了索引的所有数据，以上情况说明：使用了索引，但是是全索引扫描，所以效率较慢。

如果改为以下语句就会走索引过滤条件，避免全索引扫描：

select count(*) from tradelog where
     (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
     (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
     (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
或     
select count(*) from tradelog where
     (t_modified between '2016-7-1' and '2016-8-1') or
     (t_modified between '2017-7-1' and '2017-8-1') or 
     (t_modified between '2018-7-1' and '2018-8-1');

这个原因是因为：B+Tree是有序的，如果指定时间的话，MySQL 会使用树搜索功能，按照以下图绿色剪头里的路线：

还有就是对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加1操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1才可以。

总的来说就是：索引字段如果被函数操作，放弃使用索引优化了，但是索引字段 = 后面的参数使用计算，是不会影响索引使用的。

2、隐式类型转换

select * from tradelog where tradeid=110717;

上述语句是不会走索引的，因为在 MySQL 中，数字和字符串比较的话，会将字符串转换为数字，所以上述语句相当于：

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

索引字段使了函数操作，自然不会走索引了，如果使用以下方式，可以正常使用：

select * from tradelog where tradeid='110717';
或
select * from tradelog where tradeid=cast(110717 as char);

3、隐式字符编码转换

创建交易详情表，主义 CHARSET=utf8：

CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询 id=2 的所有操作步骤详情的 SQL 语句：

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

看查询计划

驱动表和被驱动表

驱动表在 SQL 语句执行的过程中，总是先读取。而被驱动表在 SQL 语句执行的过程中，总是后读取。简单来看在计划中排在第一行的就是驱动表。
上述语句执行顺序为：首先按 l.id = 2 筛选出驱动表要的数据，然后把被驱动表的 tradeid 转换为 urf8mb4 字符格式，相当于：

-- $L2.tradeid.value 是 l 表筛选出的一行数据里的 tradeid
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

被驱动表是 d 表，而这样就是被驱动表被隐式转换了字符集，所以无法使用索引，驱动表的函数操作是没事的，即以下语句是能正常走索引的：

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

综上所述，如果优化不能走索引的那个语句，可以修改字符集的话最好，不可以的话可以把语句改为：

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

锁

锁都是不影响正常 select 的。

1、server 层锁

以下锁都是在 server 层进行加锁的。

1.1、全局锁

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

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

// 加锁 FTWRL，与客户端断开连接之后，MySQL会自动释放锁
Flush tables with read lock; 
// 解锁
unlock tables

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
但是优缺点就是只适用于所有的表使用事务引擎的库。

1.2、表级锁

MySQL 里有两种表级锁：表锁和元数据锁（meta data lock，MDL）。

表锁

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

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2的操作。连写t1都不允许，自然也不能访问其他表。
unlock tables 只有在 lock tables 已经获取到表锁时，会隐式提交任何活动事务，不管是 FRWRL 命令之后使用 unlock tables 不会提交事务。

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

执行 show processlist，看看有哪些session，这些session在做什么事情。当你看到 waiting for table metadata lock 时，那就是遇到MDL元数据锁了。

MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从 MySQL5.5 版本开始引入了 MDL 锁，来保护表的元数据信息，用于解决或者保证 DDL 操作与 DML 操作之间的一致性。元数据锁是 server 层的锁，表级锁，每执行一条 DML、DDL 语句时都会申请 MDL 锁

增删改查操作需要 MDL 读锁。
DDL（alter 等）操作需要 MDL 写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻续塞该表的所有操作。5.6 版本之后，MySQL 加入了 Online DDL 的方式，解决了获取 MDL 锁之后不能执行 DML 语句的问题。Online DDL

事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放（这里有种特殊情况如果事务中包含 DDL 操作，mysql 会在 DDL 操作语句执行前，隐式自己进行提交 commit，以保证该 DDL 语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。

2、InnoDb 引擎层锁

2.1、行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。只有 InnoDb 支持行锁。
如果执行 delete 、 update 语句，并且条件是索引的时候，或者 insert 语句就会获取行锁，但是行锁获取是在执行之前获取的，执行之后并不会直接释放，是到事务结束之后才释放。

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

3、select lock in share mode 、select for update

// c 没索引
select * from t where c=5 for update;

在 RC（读已提交）隔离级别下，执行上面两种语句会逐行锁住全部表，然后语句执行完毕之后会把不满足条件的行释放锁，满足条件的锁会在 commit 之后才会释放锁。
而在 RR（可重复读）隔离级别下，会给所有行加锁，并且会加 gap 锁，即 next-key lock 在 commit 之后释放所有 next-key lock，所以 insert 也会被阻塞。

LOCK IN SHARE MODE 是共享锁，多个事务允许同时持有一行的读锁。 FOR UPDATE 是独占锁，事务用 FOR UPDATE 锁定行后，会阻塞其他事务对该行的写锁和读锁的获取。

4、间隙锁（Gap Lock）

间隙锁是为了解决幻读的问题，和 next-key lock 一样，只在 RR（可重复读）级别才存在。

而幻读有什么问题呢？

session 1： begin； update t set a=1000 where d = 5;
session 2： insert into(id,a,d) values (10,10,5); binlog 写入一条记录
session 2： commit; 这时候 binlog 才会写入 update XXX where d = 5

这时候如果用 binlog 去克隆库或者拿到备份库去执行，所有 d = 5 的数据 a 都变成了 1000，新插入的这个数据的数据是错误的。
上述问题也不是没有办法解决，如果必须使用读已提交的隔离级别，可以使用 binlog_format=row 的组合。

当前读时如何解决幻读？

间隙锁（Gap Lock）：锁住两个值中间的间隙，首先创建有 6 条数据

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这样就会有 7 个间隙。

如果这时候 session 1 已经持有间隙 (5,10)的锁，session 2 再插入这个区间的数据将会被阻塞。

间隙锁虽然解决了幻读的问题，但是也有一些问题，比如死锁：

select 时不 block 这是因为间隙锁之间是不会冲突的，session 1 可以获取间隙锁，session b 也可以获取这个间隙锁，只有在 insert 的时候才会造成冲突。

insert 会加锁吗

insert 会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非 next-key 锁（当然更不是 gap 锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向 gap 锁（简称I锁吧），并发的事务可以对同一个 gap 加 I 锁。如果 insert 的事务出现了duplicate-key error ，事务会对 duplicate index record 加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的 insert 都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的 insert 操作是会发生死锁的。

5、next-key lock

next-key lock 相当于间隙锁 + 行锁的组合，是前开后闭区间，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]。
而且 next-key lock 是分间隙锁和行锁两步来获取的。

6、加锁的规则

可重复读级别下的加锁规则。
锁是在执行过程中一个一个加上去的，不是一次性加上去的
例如：

事务1：
begin;
select id from t where c in(5,20,10) lock in share mode;
事务2：
select id from t where c in(5,20,10) order by c desc for update;

我们可以知道，这条语句在索引 c 上加的三个记录锁的顺序是：先加 c=5 的记录锁，再加c=10 的记录锁，最后加 c=20 的记录锁，而事务 2 注意 order by c ，加锁顺序是和事务1反过来的，在并行情况下，可能出现死锁。

两个“原则”、两个“优化”和一个“bug”

原则 1 ：加锁的基本单位是 next-key lock，前开后闭区间。

原则 2 ：查找过程中访问到的才会加锁，访问不到不会加锁（如果使用上索引覆盖，主键索引不会加锁，否则是会加上锁的）。

优化 1 ：索引上的等值查询在给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2 ：索引上的等值查询，向右遍历时且最后一个被遍历到的值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

建议： 修改和删除的时候加 limit。
因为加了 limit 之后，那么加锁的范围可以减少，而且让操作更安全，但是如果 binlog 是 statement 的，在主从模式里带 limit 语句可能出现主备不一样的问题。

change buffer

1、change buffer 的应用

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不再需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。change buffer 在内存中有拷贝，也会被写入到磁盘上。

把 change buffer 应用到旧的数据页，得到新的数据页的过程，称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行merge 操作。

merge的执行流程是这样的：

从磁盘读入数据页到内存（老版本的数据页）；

从 change buffer 里找出这个数据页的 change buffer 记录(可能有多个），依次应用，得到新版数据页；

写 redo log。这个redo log 包含了数据的变更和 change buffer 的变更。

2、change buffer 的好处

可以减少读磁盘，语句返回响应的速度明显提升，而且数据读入内存需要占用 buffer pool，所以还能避免内部占用。

3、什么条件下可以使用 change buffer

首先只会在更新数据（insert、update、delete）的时候会用 change buffer 。在 MySQL5.5 之前只针对 insert 做了优化；现在对 delete 和 update 也有效。

唯一索引不会使用 change buffer。因为唯一索引每次都需要把数据页读到内存中判断是否存在，才能进行插入，那么就没必要使用 change buffer 了,但是可以缓存删除操作。

普通索引应该插入到的数据页不在内存中，使用 change buffer。

4、change buffer 和 redo log

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

比如我们要执行一个插入操作：

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

我们假设当前k索引树的状态，查找到位置后，k1 所在的数据页在内存( InnoDB buffer pool )中，k2 所在的数据页不在内存中。如下图所示是带 change buffer 的更新状态图。

分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1 在内存中，直接更新内存；

Page 2 没有在内存中，就在内存的 change buffer 区域，记录下 “我要往 Page 2 插入一行” 这个信息；

将上述两个动作记入redo log中（图中3和4）。

在后续读 page2 的时候，就会把 page2 的数据读入内存，然后应用 change buffer 到 page2 的操作日志，再返回结果。
上面也可以看到，change buffer 的变化也被记录到 redo log 中了，所以当遇到机器断电等意外情况也不会导致数据丢失。

删除数据

1、为什么表数据删除一半，表文件大小不变？

一个 InnoDB 包含两部分，即：表结构定义和数据。在 MySQL 8.0 之前，表结构是存在以 .frm 为后缀的文件里，而 MySQL 8.0 已经允许把表结构定义放在系统数据表中了。表结构占用的空间很小。

1.1、参数 innodb_file_per_table

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

为 OFF 时，表示表的数据放在系统共享表空间，也就是和数据字典放在一起。

为 ON 时，表示每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件里

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。
一般建议不管使用哪个版本，都将这个值设置为 ON，因为一个表单独存储更容易管理，并且使用 drop table 时，会直接删除掉这个文件；而如果放在共享表空间，即使表被删除，空间也不会回收。

1.2、数据删除流程

首先，InnoDB 中的数据都是用 B+ 树的结构组织的。

假设，我们要删除 R4，InnoDB 引擎并不会真正的删除，而是把 R4 标记为删除，如果之后再插入一个 ID 在300 到 600 之间的记录时，可能会复用这个位置，但是磁盘文件的大小并不会缩小。如果整个数据页被删除，那么整个数据页就可以被复用了，同样的整个表被 delete 掉，所有的数据页都可以被复用。
记录的复用只限于符合条件的数据，而整个页的复用，可以复用到任何位置。比如 page A 整页可以被复用，插入一条 ID = 50 的记录，Page A 是可以被复用的。
除了上面说到的 delete 会造成空洞，插入数据也会，如果数据是随机插入的会造成页分裂。
如果 page A 已经满了，这时再插入数据，会发生以下情况：

重建表

optimize table、analyze table、alter table区别

上面讲了新增和删除可能会造成空洞，而 MySQL 也有能达到压缩表的目的的操作。

可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 之前，MySQL 会自动完成创建一个空间紧凑的临时表、转换数据、交换表明、删除旧表的操作。
但是在重建表的时候，InnoDB 不会把整张表占满，每个页会留 1/16 给后续更新用，也就是说，重建表之后不是“最”紧凑的。

很明显，花时间最多的步骤是往临时表插入数据的过程，而且按照上面的方式，在这个过程中，有新的数据插入，就会造成数据丢失，所以在整个 DDL 过程中，表 A 不能有更新。
在 MySQL 5.6 版本开始引入了 Online DDL，对这个操作流程做了优化。

Online DDL

引入 Online DDL 之后重建表的流程：

申请空间，建立一个临时文件，扫描表 A 主键的所有数据页；

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

生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；

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

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

之前还说过 DDL 时，会获取 MDL 写锁，这里支持 DDL 之后增删改查操作还能继续执行并不是 MDL 锁失效了，而是因为 Online DDL 是仅针对 DDL 语句获取到 MDL 锁之后，可以进行 DML 操作的优化， 而如果先执行增删改查的操作，获取到 MDL 读锁，DDL 以及后续的不管是 DDL 还是增删改查语句都会被阻塞。Online DDL 的过程是：

拿 MDL 写锁

降级成 MDL 读锁

真正做 DDL，即 Online DDL 除最后一步外的重建表的步骤

申请升级成 MDL 写锁，这时候开始阻塞别的事务后面的操作

拿到写锁，执行 Online DDL 最后一步，替换表结构

释放 MDL 锁

看一个例子，来源：blog.csdn.net/q2878948/ar…

session 1 : 
	begin ;
	select * from t;

session 2 : 
	begin ;
	select * from t;

// 只有等 session 1、2、4 都 commit 之后，才会继续执行
session 3 : 
	alter  table  t add  versionqq  int(1) not  NULL  DEFAULT  0

// session 4 会有一种伪插队的情况出现
session 4 : 
	begin ;
	select * from t;

Count 函数

count 函数的实现方式

count(*)

MyISAM 引擎把一个表的总行数存在磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高。但是带 where 条件的 count() 也不会很快。
InnoDb 因为 MVCC 的存在，需要每一行都判断该事务是否可见，所以不能把总行数存储起来。
count(*) 时，优化器会找到最小的那棵树进行遍历，所以一般会去遍历普通索引。在保证逻辑正确的情况下，尽量减少扫描的数据量，这是数据库系统设计的通用法则之一。

show table status

执行 show table status 之后会返回 table_rows 不能代替 count(*) ，因为这个数值是通过采样估算来的，不准确。采样估算见索引选择。

count(XXX)

count()、count(1)、count(id) 和 count(字段)是有差别的，count()、count(主键 ID) 和 count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

首先要记住以下几个原则：

server 层要什么就给什么；

InnoDb 只给必要的值；

现在的优化器只优化了 count(*) 的语义为取行数，其他“显而易见”的优化没有做。

count(主键 ID) ： InnoDb 引擎会遍历整张表，把每一行的 ID 取出来，返回给 server 层，server 层拿到 ID 之后，因为 ID 不可能为 null ，所以直接按行累加。

count(1) ： InnoDb 引擎遍历整张表，不取值，server 层对于返回的每一行，直接返回个 1 进去，判断也不可能为 null ，也是直接按行累加。由此可见 count(1) 比 count(主键 ID) 效率高点，因为 count(1）不需要取值。

count(字段) ：

这个字段是 not null 的话，取出这个字段，判断不可能为 null，直接按行累加。

这个字段是可以为 null 的话，取出这个字段，进行判断，不是 null 才会进行累加。

count(*) ： 专门做了优化，不取值，count(*) 肯定不为 null ，直接按行累加。

由此可见，这就是前面的第一条原则， server 层要什么字段，InnoDb 就返回什么字段。
结论是： 按照效率排序的话，count(字段) < count(主键 ID) < count(1) ≈ count()，所以建议，尽量使用count()。

order by

假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。首先有一个表结构定义：

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;

全字段排序

Extra 字段中的“Using filesort” 表示需要排序，MySQL 会给每一个线程分配一块内存用来排序，称为 sort_buffer。

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

初始化 sort_buffer，确定放入 name、age、city 三个字段；

从索引 city 找到第一个满足 city='杭州' 条件的主键 id；

到主键 id 索引取出整行，取 name、age、city 三个字段的值，存入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到 city 的值不满足查询条件；

对 sort_buffer 中的数据按照字段 name 做快速排序；

按照排序结果取前 1000 行给客户端。

sort_buffer_size

“按照 name 排序”这个动作，可能在内存中完成，也可能使用外部排序，而这个取决于所需的内存和参数 sort_buffer_size。
sort_buffer_size，就是 MySQL 为排序开辟的内部（sort_buffer）的大小。如果需要排序的数据量小于 sort_buffer_size ，排序就在内存中完成，如果排序数据量大，那么就会使用磁盘临时文件辅助排序，可以使用 set sort_buffer_size=32768; 设置。

可以使用下面的方法来确定一个排序语句是否使用了临时文件

查询物理读数据行数

/*主要是 3、4 步，1、5、6 三步是计算在查询过程中读取的行数 */

/* 1、打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 2、@a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 3、执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 4、查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* 5、@b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 6、计算Innodb_rows_read差值 */
select @b-@a;

/* 7、关闭optimizer_trace */
SET optimizer_trace="enabled=off";

通过查看 OPTIMIZER_TRACE 的结果来确认，可以从 number_of_tmp_files 中看到是否使用了临时文件。

图中看到用到了 12 个文件，这是因为外部排序一般使用归并排序， 可以这样理解：MySQL 将需要排序的数据分成 12 份，每一份单独排序后放到这些临时文件中，然后再把十二个有序文件合成一个有序的大文件。

如果sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。
示例表中有4000条满足city='杭州’的记录，所以你可以看到 examined_rows=4000，表示参与排序的行数是4000行。
sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使name字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。
最后一个查询语句select @b-@a 的返回结果是4000，表示整个执行过程只扫描了4000行。

rowid 排序

上述算法过程，有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够放下的行数很少，要分成多个临时文件，排序的性能会很差，所以如果 MySQL 认为排序的单行长度太大的话会使用另外一种算法。

可以设置一个参数，来控制用于排序的行数据的长度，如果大于这个值就换排序方式。

SET max_length_for_sort_data = 16;

新的方式可以被称为 rowid 排序，放入 sort_buffer 的字段只会有要排序的字段和主键 id，并不是归并排序叫做 rowid 排序，而是因为 sort_buffer 中存放的是想要的字段与 rowid（主键ID或者自生成的 ID），所以叫 rowid 排序，流程如下：

初始化 sort_buffer，确定放入两个字段，name 和 id；

从索引 city 中找到第一个满足 city='杭州' 条件的主键 id；

到主键 id 索引中取出整行，取 name、id 两个字段，放入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到不满足 city='杭州' 位置；

对 sort_buffer 中的数据按照字段 name 排序；

遍历排序结果，取前 1000 行，并按照 id 的值回到原表主键索引中取出 city、name 和 age 三个字段给客户端。

可以发现，这个方式多访问了一次表的主键索引，就是步骤 7 。
而 select @b-@a 结果变成了 5000，因为满足条件的是 4000 条，而第 7 步，又用 id 去主键索引上取了 1000 条，因此是 5000。

从 OPTIMIZER_TRACE 的结果中，能看到另外两个信息也变了。

sort_mode 变成了 <sort_key,rowid>，表示参与排序的只有 name 和 id 两个字段。

number_of_tmp_files 变成了 10，因为参与排序的行数仍是 4000，但是每一行都变小了，索引临时文件也变小了。

可以看到 MySQL 的一个设计思想：如果内存够，就多利用内存，尽量减少磁盘访问。对于 InnoDb 来说，rowid 排序会要求回表多造成磁盘度，因此不会被优先选择。

索引排序

在这个市民表上创建一个city和name的联合索引之后，查询的流程就变成了：

从索引（city,name）找到第一个满足 city='杭州' 条件的主键 id；

到主键 id 索引取出整行，取 name 、city、age 三个字段的值，作为结果集的一部分直接返回；

从索引（city,name）取下一条记录主键 id

重复步骤 2、3，直到查询到第 1000 调数据，或者不满足条件循环结束。

可以看到 Extra 字段没有 Using filesort 了，就是不需要排序了，而且由于联合索引本身有序，所以这个查询只要找到满足条件的前 1000 条数据就可以退出了。但是如果条件是 where city in ('杭州','上海') order by name 那么就需要数据库进行排序了，因为在一个 city 里 name 是有顺序的，但是可能两个城市的 name 需要重新排序。
而如果 city、name、age 是联合索引，满足索引覆盖，那也就不用去主键索引里取数据了。explain 如下：

Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多，当然是否需要这样的组合索引是需要权衡的。

排序算法

一般来说，取到的数据能全部放到内存里，就使用快速排序；
如果需要取出来排序的数据不能全部放到内存里，即需要的数据大于 sort_buffer_size 就会使用归并排序；sort_buffer_size
在 MySQL 5.6 之后，如果排序语句中有 limit ,而且 limit 后需要排序的数据小于 sort_buffer_size 会使用优先队列排序算法（堆排序），如下图，OPTIMIZER_TRACE 结果中，filesort_priority_queue_optimization 这个部分的chosen=true，就表示使用了优先队列排序算法。

order by rand()

以下例子，从一个单词表中随机选三个，表中有 10000 条数据

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

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

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。
因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。
这个语句的执行流程是这样的：

创建一个内存临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，方便描述，记为字段 R ，第二个字段是需要查询的数据，记为 W，并且这个临时表没有建索引；

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

初始化 sort_buffer，sort_buffer 中有两个字段，和临时表一样

从内存临时表中一行一行的取出 R 值和位置信息，分别把这两个字段放入 sort_buffer 中，这个操作需要对内存临时表做全表扫描，此时扫描行数增加 10000，变为 20000；

在 sort_buffer 中根据字段 R 排序，这个过程没有对表进行操作，不会增加扫描行数；

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

在索引一节的 4 部分中说到如果没有主键，那么会生成一个长度为6字节的全局自增 row_id 来作为主键，而这里的临时表是没有主键索引的，所以上面取出的是 R 值和位置信息，最后排序结束后用这个位置信息去定位数据，所以使用的也是 rowid 排序，而上面说到 MySQL5.6 只有对 limit 语句进行了优化，所以使用的排序算法是<堆排序>。
可以看到，我们用这个方式随机查询三条数据，消耗的资源很多，扫描行数是全表的两倍 + 要取的条数，还需要创建临时表，所以需要更优的方式。

随机排序算法

// 取总共有多少条数据
mysql> select count(*) into @C from t;
// 取随机三条，也可用代码取 Y1、Y2、Y3 的值
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
//在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行，当然要防止出现重复值
select * from t limit @Y1，1； 
select * from t limit @Y2，1；
select * from t limit @Y3，1；

这样随机取三条的扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)，不需要临时表。

优化方案：

假设Y1，Y2，Y3是由小到大的三个数，则可以优化成扫描行数为C +Y3 + 3：

id1 = select * from t limit @Y1，1；
id2 = select * from t where id > id1 limit @Y2-@Y1，1；
select * from t where id > id2 limit @Y3 - @Y2，1；

也可以取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，然后执行下面这条 SQL 语句：
select * from t limit N, M-N+1;
然后取其中三条，这样扫描行数是 C + Y3 + 1。

内存临时表和磁盘临时表

上面讲的例子使用的是内存临时表，但是还可能使用磁盘临时表，tmp_table_size 这个配置限制了内存临时表的大小，默认是 16M，如果临时表超过了这个大小，就会使用磁盘临时表。
而磁盘临时表默认使用的引擎是 InnoDb，可以由参数 internal_tmp_disk_storage_engine 控制，当使用磁盘临时表，那么排序对应的就是一个没有显式索引的 InnoDb 表的排序过程。

Join 关键字

Join 算法

// straight_join 固定关键字前面为驱动表，后面为被驱动表，假如两个表里都有 100 行数据
select * from t1 straight_join t2 on (t1.a=t2.a);

Index Nested-Loop Join

使用的条件是：可以使用被驱动表的索引。

对驱动表 t1 做全表扫描，这个过程扫描 100 行；

对于每一行数据 R（假设叫 R）根据字段 a 去表 t2 查找，走的时候树搜索过程，所以每次搜索也只搜索一行，总共扫描 100 行；

所以整个流程扫描 200 行。

Block Nested-Loop Join

还是上的 sql 语句，如果 t2.a 没有索引，那么算法的流程是：

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此把整个 t1 表放入 join_buffer 中；

扫描表 t2 ，把表 t2 的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的作为结果集的一部分返回。这时候一次 t2 表是全表遍历，100 行，总共就是扫描 10000 行数据。

当然 join_buffer 中可能会放不下，那么执行流程就变成了：

扫描表 t1，顺序读取数据放入 jon_buffer 中，放满 join_buffer 后，进行第 2 步；

扫描表 t2 ，把 t2 中的每一行读取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果的一部分返回；

清空 join_buffer ；

继续扫描表 t1，继续往后顺序读取，继续第 2 步。

join_buffer 的大小是通过 join_buffer_size 参数设定的。

这个算法虽然使用了 join_buffer ，省的多次去磁盘读取驱动表的数据，进行了优化，但还是应避免使用。 explain 结果里面 Extra 字段有没有出现“Block Nested Loop”字样，如果出现使用的就是 Block Nested-Loop Join。

建议

如果要使用 join ，尽量使用 Index Nested-Loop Join。
在使用 join 的时候，尽量让小表做驱动表，因为在走索引的情况下，只需要取小表的数据各一遍，而大表走索引每次只查一行。

问题

业务设计问题

双向关注成为好友设计

information_schema 库

INNODB_TRX

查看当前正在运行的事务。

METADATA_LOCKS

存放 MDL 锁相关信息的表。

query_rewrite 功能

需要安装插件，可以把输入的一种语句改写成另外一种模式。可以在线上语句不走索引等情况，而不能重启服务的时候使用该功能，增加一个语句改写功能，但是此方法要谨慎使用。

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "testdb");

call query_rewrite.flush_rewrite_rules();

这样就把查询语句 select * from t where id + 1 = ? 执行的时候改成了 select * from t where id = ? - 1。

MySQL 45 讲学习笔记

MySQL 45 讲学习笔记

基础架构

日志系统

1、Redo log（重做日志）

2、binlog（归档日志）

3、Redo log 和 binlog（归档日志）的区别

4、undo log

4.1、多版本并发控制（ MVCC ）

4.2、当前读与快照读

4.3、怎么实现事务回滚

4.4、怎么实现 RC、RR 隔离级别的读已提交和可重复读

5、flush 脏页

5.1、什么时候 flush 脏页

5.2、InnoDb 刷脏页的控制策略

6、slow log（慢查询日志）

索引

1、索引的好处

2、索引的常见模型

3、B+ Tree 的优点

4、MySQL 里的 B+Tree

5、怎么避免回表？

5.1、索引覆盖

5.2、最左匹配 组合索引：name,age

5.3、索引下推

6、索引维护

7、索引选择

7.1、索引选择的因素

8、如何给字符串加索引？

1、前缀索引

9、放弃走树索引搜索的情况

1、条件字段函数操作

2、隐式类型转换

3、隐式字符编码转换

驱动表和被驱动表

锁

1、server 层锁

1.1、全局锁

1.2、表级锁

2、InnoDb 引擎层锁

2.1、行锁

3、select lock in share mode 、select for update

4、间隙锁（Gap Lock）

insert 会加锁吗

5、next-key lock

6、加锁的规则

change buffer

1、change buffer 的应用

2、change buffer 的好处

3、什么条件下可以使用 change buffer

4、change buffer 和 redo log

删除数据

1、为什么表数据删除一半，表文件大小不变？

1.1、参数 innodb_file_per_table

1.2、数据删除流程

重建表

Online DDL

Count 函数

count 函数的实现方式

count(*)

show table status

count(XXX)

order by

全字段排序

sort_buffer_size

查询物理读数据行数

rowid 排序

索引排序

排序算法

order by rand()

随机排序算法

内存临时表和磁盘临时表

Join 关键字

Join 算法

Index Nested-Loop Join

Block Nested-Loop Join

建议

问题

业务设计问题

information_schema 库

5.2、最左匹配组合索引：name,age