《MySQL 实战 45 讲》笔记 基础架构：一条 SQL 查询语句是如何执行的？
连接器 ：连接器负责跟客户端建立连接、获取权限、维持和管理连接。查询缓存 ：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。分析器
词法分析 ：解析 SQL 关键字语法分析 ：生成一颗对应的语法解析树
优化器
根据语法树生成多种执行计划
索引选择 ：根据策略选择最优方式
执行器
校验读写权限
根据执行计划，调用存储引擎的 API 来执行查询
存储引擎 ：存储数据，提供读写接口
日志系统：一条 SQL 更新语句是如何执行的？ 更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：
redo log（重做日志）
binlog（归档日志）
redo log 如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 采用了 WAL 技术（全程是 Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe 。
binlog redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
redo log 和 binlog 的差异：
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
再来看一下：update 语句时的内部流程
执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
两阶段提交 为什么日志需要“两阶段提交”
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。
先写 redo log 后写 binlog 。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log 。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
事务隔离：为什么你改了我还看不见？
深入浅出索引 索引数据结构 哈希索引 适用：只能用于等值查询
哈希索引的限制
无法用于排序 ：因为哈希索引数据不是按照索引值顺序存储的。不支持部分索引匹配查找 ：因为哈希索引时使用索引列的全部内容来进行哈希计算的。不能用索引中的值来避免读取行 ：因为哈希索引只包含哈希值和行指针，不存储字段。只支持等值比较查询 （包括 =、IN()、<=>）；不支持任何范围查询哈希索引非常快，除非有很多哈希冲突：
出现哈希冲突时，必须遍历链表中所有行指针，逐行比较匹配
如果哈希冲突多的话，维护索引的代价会很高
哈希索引的应用
Mysql 中，只有 Memory 存储引擎显示支持哈希索引。
有序数组索引 有序数组索引在等值查询和范围查询场景中的性能都非常优秀。
如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，更新数据的时候，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎 。
B+ 树索引 在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为 InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
每一个索引在 InnoDB 里面对应一棵 B+ 树。
根据叶子节点的内容，索引类型分为主键索引（聚簇索引）和非主键索引（非聚簇索引）。
聚簇索引 ：叶子节点存储行数据。
可以把相关数据保存在一起
数据访问更快
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值
非聚簇索引 ：叶子节点存储主键。访问需要两次索引查找。
第一次获得对应主键值
第二次去聚簇索引中查找对应行，即回表
B+ 树为了维护索引有序性，在插入新值的时候需要做动态调整。
插入位置如果不是末尾，需要挪动后面的数据，空出位置。
更糟的情况是，如果待插入位置所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
如果相邻的两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
由于每个非主键索引的叶子节点上都是主键的值。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小 。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
什么场景适合用业务字段直接做主键的呢？
为什么不用二叉树？
要考虑尽量减少磁盘扫描。
索引策略
索引基本原则
索引不是越多越好，不要为所有列都创建索引
要尽量避免冗余和重复索引
要考虑删除未使用的索引
尽量的扩展索引，不要新建索引
频繁作为 WHERE 过滤条件的列应该考虑添加索引
独立索引
索引列不能是表达式的一部分，也不能是函数的参数
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
前缀索引和索引选择性
索引的选择性是指：不重复的索引值和数据表记录总数的比值。
选择性越高，查询效率越高使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
order by 无法使用前缀索引，无法把前缀索引用作覆盖索引
最左前缀匹配原则
将选择性高的列或基数大的列优先排在多列索引最前列
匹配联合索引最左前缀的时候，如果遇到了范围查询，比如（<）（>）和 between 等，就会停止匹配。
覆盖索引：索引上的信息足够满足查询请求，不需要回表查询数据。
使用索引扫描来排序：ORDER BY 的字段作为索引，这样命中索引的查询结果，不需要额外排序
= 和 in 可以乱序：不需要考虑 =、IN 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。
全局锁和表锁 ：给表加个字段怎么有这么多阻碍？ 根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类 。
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。 也就是把整库每个表都 select 出来存成文本。
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 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。
行锁功过：怎么减少行锁对性能的影响？ MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁 。
当出现死锁以后，有两种策略：
进入等待，直到超时 。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果此策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行 。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
极端情况下，如果所有事务都要更新同一行：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
减少死锁的主要方向，就是控制访问相同资源的并发事务量。
事务到底是隔离的还是不隔离的 “快照”在 MVCC 里是怎么工作的？ InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。
图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。
当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：
如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况
InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
更新逻辑 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
普通索引和唯一索引，应该怎么选择？ 普通索引和唯一索引的查询性能相差微乎其微 。
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
change buffer 的使用场景 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。
因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。
如果一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。
索引选择和实践 如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。
在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。
特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。
change buffer 和 redo log
图 - 带 change buffer 的更新过程
图 - 带 change buffer 的读过程
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。
MySQL 为什么有时候会选错索引 选择索引是优化器的工作。
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。但是，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用 analyze table t 命令来重新统计索引信息 。
对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。
怎么给字符串字段加索引？
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。
可以通过下面的方式来测试不同前缀长度的区分度：
1 2 3 4 5 6 7 select count (distinct email) as L from SUser;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。
需要注意：使用前缀索引就用不上覆盖索引对查询性能的优化了，必须回表才能拿到该索引字段的完整信息。
如果前缀的区分度不够好的情况时如何处理？
第一种方式是使用倒序存储
1 select field_list from t where id_card = reverse('input_id_card_string' );
第二种方式是使用 hash 字段
可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
1 alter table t add id_card_crc int unsigned, add index(id_card_crc);
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
1 select field_list from t where id_card_crc=crc32('input_id_card_string' ) and id_card='input_id_card_string'
这两种方式的对比：
它们的相同点 是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。
它们的区别 ：
从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
小结：
直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
为什么我的 MySQL 会“抖”一下？ 利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。
但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。
为什么表数据删掉一半，表文件大小不变？ 表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。
如果删掉了一个数据页上的所有记录，则整个数据页就可以被复用了。
如果把整个表的数据删除，则所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。
delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。页分裂完成后，就可能产生空洞。另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
也就是说，经过大量增删改的表，都是可能是存在空洞的。
重建表 那么，如何收缩表空间，去除空洞呢？
可以使用 alter table A engine=InnoDB 命令来重建表。MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。
在MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
用临时文件替换表 A 的数据文件。
对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。
optimize table、analyze table 和 alter table 这三种方式重建表的区别：
从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 4 的流程了；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。
count(*)这么慢，我该怎么办？ 不同的 MySQL 引擎中，count(*) 有不同的实现方式。
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢
因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。
InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。
MyISAM 表虽然 count(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。
保存计数 可以使用 Redis 保存计数，但存在丢失更新一集数据不一致问题。
可以使用数据库其他表保存计数，但要用事务进行控制，增/删数据时，同步改变计数。
不同的 count 用法 对于 count(主键 id) 来说 ，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说 ，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
对于 count(字段) 来说 ：
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
但是 count(*) 是例外 ，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
order by 是怎么工作的？用 explain 命令查看执行计划时，Extra 这个字段中的“Using filesort”表示的就是需要排序。
全字段排序 1 select city,name,age from t where city= '杭州' order by name limit 1000 ;
这个语句执行流程如下所示 ：
初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。
按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。
rowid 排序 如果表的字段太多，导致单行太大，那么全字段排序的效率就不够好。
这种情况下，Mysql 可以采用 rowid 排序，相比于全字段排序，它的主要差异在于：
取行数据时，不取出整行，而只是取出 id 和用于排序的字段。当排序结束后，再根据 id 取出要查询的字段返回给客户端。
全字段排序 VS rowid 排序 如果内存足够大，Mysql 会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
如果内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。 如果能保证排序字段命中索引，那么就无需再排序了。
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
为什么这些 SQL 语句逻辑相同，性能却差异巨大？ 函数操作会破坏索引有序性 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
示例：
1 2 3 4 5 6 7 8 9 10 11 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; select count (* ) from tradelog where month (t_modified)= 7 ;
由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。
1 2 3 4 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' );
隐式转换 下面两个 SQL 的执行流程相同：
1 2 select * from tradelog where tradeid= 110717 ;select * from tradelog where CAST (tradid AS signed int ) = 110717 ;
交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。这是由于这条语句隐式增加了转换函数，而对索引字段做函数操作，优化器会放弃走树搜索功能。
隐式字符编码转换 示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 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; insert into tradelog values (1 , 'aaaaaaaa' , 1000 , now());insert into tradelog values (2 , 'aaaaaaab' , 1000 , now());insert into tradelog values (3 , 'aaaaaaac' , 1000 , now());insert into trade_detail values (1 , 'aaaaaaaa' , 1 , 'add' );insert into trade_detail values (2 , 'aaaaaaaa' , 2 , 'update' );insert into trade_detail values (3 , 'aaaaaaaa' , 3 , 'commit' );insert into trade_detail values (4 , 'aaaaaaab' , 1 , 'add' );insert into trade_detail values (5 , 'aaaaaaab' , 2 , 'update' );insert into trade_detail values (6 , 'aaaaaaab' , 3 , 'update again' );insert into trade_detail values (7 , 'aaaaaaab' , 4 , 'commit' );insert into trade_detail values (8 , 'aaaaaaac' , 1 , 'add' );insert into trade_detail values (9 , 'aaaaaaac' , 2 , 'update' );insert into trade_detail values (10 , 'aaaaaaac' , 3 , 'update again' );insert into trade_detail values (11 , 'aaaaaaac' , 4 , 'commit' );SELECT d.* FROM tradelog l, trade_detail dWHERE d.tradeid = l.tradeid AND l.id = 2 ;# 等价于 select * from trade_detail where CONVERT (traideid USING utf8mb4)= $L2.tradeid.value;# 不需要做字符编码转换 EXPLAIN SELECT l.operatorFROM tradelog l, trade_detail dWHERE d.tradeid = l.tradeid AND d.id = 2 ;
字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。
为什么我只查一行的语句，也执行这么慢？ 查询长时间不返回 查询结果长时间不返回。
一般碰到这种情况的话，大概率是表被锁住了。接下来分析原因的时候，一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图
出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
幻读是什么，幻读有什么问题？ 产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
幻读是什么 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
说明：
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
幻读专指“新插入的行”。
幻读有什么问题
语义上的破坏
数据一致性的问题
即使把所有记录都加上锁，还是阻止不了新插入的记录
如何解决幻读
间隙锁（gap lock）
select * from t where d=5 for update，不止给数据库已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新纪录next-key lock 是前开后闭区间
间隙锁和行锁合称为 next-key lock
跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作
间隙锁是在可重复读隔离级别下才会生效的 ，所以把隔离级别设置为读提交的话，就没有间隙锁了。但同时，要解决可能出现的数据和日志不一致的问题，需要把binlog格式设置为row。
间隙锁和next-key lock的引入，帮我们解决了幻读问题，但同时也带来了一些困扰
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响并发度的
为什么我只改一行的语句，锁这么多？ 加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
MySQL 有哪些“饮鸩止渴”提高性能的方法？ 短连接风暴 短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。
MySQL 建立连接的成本很高。
除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。
短连接模型存在一个风险：一旦数据库处理速度很慢，连接数就会暴涨。
max_connections 控制一个 MySQL 实例同时存在的连接数的上限
超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
解决方法 1：先处理掉那些占着连接但是不工作的线程
show processlist 查看 sleep 的线程，然后干掉空闲的连接。注意：可能会误杀事务。应该优先断开事务外空闲的连接。
通过查 information_schema 库的 innodb_trx 表判断是否处于事务中。
再考虑断开事务内空闲太久的连接。
解决方法 2：减少连接过程的消耗 如果想短时间创建大量数据库连接，有一种做法是跳过权限验证。
跳过权限验证的方法是：重启数据库，并使用 –skip-grant-tables 参数启动。
注意：此方法风险极高，不建议使用。
慢查询性能问题 一般有三种可能：
索引没有设计好；
SQL 语句没写好；
MySQL 选错了索引。
可以通过 force index 强制使用某个索引
QPS 突增问题 有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。
应对方法：
一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成”select 1”返回。
个人观点：以上方法都是基于 DBA 视角的处理方式。实际环境中，应该做好数据库 QPS、CPU 监控，如果发现请求量激增，快要达到瓶颈，可以先紧急弹性扩容，保障业务不损失。然后排查原因，是否是新业务设计不当导致、是否是大数据在也业务高峰期进行数据分析导致，等等。
Mysql 是怎么保证数据不丢的 只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。
binlog 的写入机制 binglog 写入逻辑：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。
一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。
write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
redo log 的写入机制 redo log 要先写到 redo log buffer
innodb_flush_log_at_trx_commit 参数用于控制 redo log buffer 写入 page cache 和写入磁盘的时机
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。
还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。
一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。
如果 MySQL 出现了 IO 性能瓶颈，可以通过哪些方法来提升性能 WAL 机制主要得益于两个方面：
redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
有三种方法提升 Mysql IO 性能：
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
Mysql 是怎么保证主备一致的 MySQL 主备的基本原理 MySQL 主备切换流程
客户端的读写都直接访问主库，备库只是将主库的更新都同步过来，到本地执行。
建议将备库设为 readonly 模式：
有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
可以用 readonly 状态，来判断节点的角色。
readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。
备库跟主库之间维持了一个长连接。主库内部有一个线程，专门用于服务备库的这个长连接。一个事务日志同步的完整过程是这样的：
在备库上通过 change master 命令，设置主库的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库校验完用户名、密码后，开始按照备库传过来的位置，从本地读取 binlog，发给备库。
备库拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。
binlog 三种格式对比 binlog 有两种格式，一种是 statement，一种是 row
当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。
当 binlog_format=row 时，binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。
Table_map event，用于说明接下来要操作的表是 test 库的表 t;
Delete_rows event，用于定义删除的行为。
为什么会有 mixed 这种 binlog 格式的存在场景？
循环复制问题 如果两个节点互为主备，就可能出现循环复制问题。
如何解决循环复制问题：
规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
Mysql 是怎么保证高可用的 主备延迟 所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。
show slave status 命令可用于显示备库延迟（seconds_behind_master），其计算方式如下：
每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。
主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。
主备延迟的来源
备库的机器性能比主库的机器性能差。
备库的压力大。
因为一般会采用读写分离架构，备库承担读请求，反而导致备库压力过大。
解决方法：
一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
大事务
不要一次性地用 delete 语句删除太多数据
大表 DDL：计划内的 DDL，建议使用 gh-ost 方案
备库的并行复制
可靠性优先策略
判断备库现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
把主库改成只读状态，即把 readonly 设置为 true；
判断备库的 seconds_behind_master 的值，直到这个值变成 0 为止；
把备库改成可读写状态，也就是把 readonly 设置为 false；
把业务请求切到备库。
这个切换流程，一般是由专门的 HA 系统来完成的，我们暂时称之为可靠性优先流程。
可用性优先策略 如果强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库，并且让备库可以读写，那么系统几乎就没有不可用时间了。
我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。
备库为什么会延迟好几个小时 按表分发策略
每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。hash 表的 key 是“库名. 表名”，value 是一个数字，表示队列中有多少个事务修改这个表。
在有事务分配给 worker 时，事务里面涉及的表会被加到对应的 hash 表中。worker 执行完成后，这个表会被从 hash 表中去掉。
事务在分发时，有三种情况：
如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。
这种方案在多个表负载均匀的场景里应用效果很好。但是，如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
按行分发策略 要解决热点表的并行复制问题，就需要一个按行并行复制的方案。按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。
按行复制和按表复制的数据结构差不多，也是为每个 worker，分配一个 hash 表。只是要实现按行分发，这时候的 key，就必须是“库名 + 表名 + 唯一键的值”。
这个“唯一键”只有主键 id 是不够的，还需要考虑唯一键。即 key 应该是“库名 + 表名 + 索引 a 的名字 +a 的值”。因此，coordinator 在解析这个语句的 binlog 的时候，这个事务的 hash 表就有三个项:
key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变，出现了两次。
key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表 a=2 的行。
key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表 a=1 的行。
可见，相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。
MySQL 5.6 版本的并行复制策略 官方 MySQL5.6 版本，支持了并行复制，只是支持的粒度是按库并行。
MariaDB 的并行复制策略 MariaDB 的并行复制策略利用的就是这个特性：
能够在同一组里提交的事务，一定不会修改同一行；
主库上可以并行执行的事务，备库上也一定是可以并行执行的。
在实现上，MariaDB 是这么做的：
在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；
commit_id 直接写到 binlog 里面；
传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
这一组全部执行完成后，coordinator 再去取下一批。
MySQL 5.7 的并行复制策略 由参数 slave-parallel-type 来控制并行复制策略：
配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。不过，MySQL 5.7 这个策略，针对并行度做了优化。这个优化的思路也很有趣儿。
主库出问题了，从库怎么办？
A 和 A’互为主备， 从库 B、C、D 指向的是主库 A
基于位点的主备切换 1 2 3 4 5 6 7 CHANGE MASTER TO MASTER_HOST= $host_name MASTER_PORT= $port MASTER_USER= $user_name MASTER_PASSWORD= $password MASTER_LOG_FILE= $master_log_name MASTER_LOG_POS= $master_log_pos
MASTER_HOST、MASTER_PORT、MASTER_USER 和 MASTER_PASSWORD 四个参数，分别代表了主库 A’的 IP、端口、用户名和密码。
最后两个参数 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要从主库的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。
主备切换时，由于找不到精确的同步位点，所以只能采用直接跳过指定错误这种方法来创建从库和新主库的主备关系。
通过 sql_slave_skip_counter 跳过事务
通过 slave_skip_errors 忽略错误
GTID GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。GTID 由两部分组成：GTID=server_uuid:gno
server_uuid ：是一个实例第一次启动时自动生成的，是一个全局唯一的值；gno ：是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
启动 Mysql 时，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以启动 GTID 模式。在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。
在 GTID 模式下，备库 B 要设置为新主库 A’的从库的语法如下：
1 2 3 4 5 6 CHANGE MASTER TO MASTER_HOST =$host_name MASTER_PORT =$port MASTER_USER =$user_name MASTER_PASSWORD =$password master_auto_position =1
找位点的工作，由 Mysql 内部完成。
读写分离有哪些坑 读写分离的主要目标就是分摊主库的压力。
还有一种架构是，在 MySQL 和客户端之间有一个中间代理层 proxy，客户端只连接 proxy， 由 proxy 根据请求类型和上下文决定请求的分发路由。
客户端直连 vs. 带 proxy 的读写分离
客户端直连方案：因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
你可能会觉得这样客户端也太麻烦了，信息大量冗余，架构很丑。其实也未必，一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
带 proxy 的架构：对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。
解决主从延迟的方案：
强制走主库方案；
sleep 方案；
判断主备无延迟方案；
配合 semi-sync 方案；
等主库位点方案；
等 GTID 方案。
强制走主库方案 强制走主库方案其实就是，将查询请求做分类。
对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。
对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。
Sleep 方案 主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。
判断主备无延迟方案 show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短。
所以第一种确保主备无延迟的方法是， 每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。
第二种方法， 对比位点确保主备无延迟：
Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。
第三种方法， 对比 GTID 集合确保主备无延迟：
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
可见，对比位点和对比 GTID 这两种方法，都要比判断 seconds_behind_master 是否为 0 更准确。
配合 semi-sync semi-sync replication 即半同步复制。
semi-sync 做了这样的设计：
事务提交的时候，主库把 binlog 发给从库；
从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。
如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。
semi-sync 配合判断主备无延迟的方案，存在两个问题：
一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
在持续延迟的情况下，可能出现过度等待的问题。
等主库位点方案 1 select master_pos_wait(file , pos[, timeout ]);
命令的逻辑如下：
它是在从库执行的；
参数 file 和 pos 指的是主库上的文件名和位置；
timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。
GTID 方案 如果你的数据库开启了 GTID 模式
1 select wait_for_executed_gtid_set (gtid_set, 1 )
这条命令的逻辑是：
等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；
超时返回 1。
如何判断一个数据库是不是出问题了 select 1 判断 select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题。
查表判断 为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：
1 select * from mysql.health_check;
使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。
更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据的。
更新判断 1 2 3 4 5 6 7 8 CREATE TABLE `health_check` ( `id` int (11 ) NOT NULL , `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`) ) ENGINE= InnoDB; insert into mysql.health_check(id, t_modified) values (@@server_id , now()) on duplicate key update t_modified= now();
由于 MySQL 规定了主库和备库的 server_id 必须不同（否则创建主备关系的时候就会报错），这样就可以保证主、备库各自的检测命令不会发生冲突。
更新语句，如果失败或者超时，就可以发起主备切换了，为什么还会有判定慢的问题呢？
IO 利用率 100% 表示系统的 IO 是在工作的，每个请求都有机会获得 IO 资源，执行自己的任务。而我们的检测使用的 update 命令，需要的资源很少，所以可能在拿到 IO 资源的时候就可以提交成功，并且在超时时间 N 秒未到达之前就返回给了检测系统。
检测系统一看，update 命令没有超时，于是就得到了“系统正常”的结论。
内部统计 MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。
打开 redo log 的时间监控
1 update setup_instruments set ENABLED= 'YES' , Timed= 'YES' where name like '%wait/io/file/innodb/innodb_log_file%' ;
可以通过 MAX_TIMER 的值来判断数据库是否出问题了。
1 select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file' ,'wait/io/file/sql/binlog' ) and MAX_TIMER_WAIT> 200 * 1000000000 ;
发现异常后，取到你需要的信息，再通过下面这条语句：
1 truncate table performance_schema.file_summary_by_event_name;
把之前的统计信息清空。这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值了。
答疑文章（二）：用动态的观点看加锁 误删数据后除了跑路还能怎么办？ 误删行 事后处理：
使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。
Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。
事前预防：
把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
代码上线前，必须经过 SQL 审计。
误删库 / 表 这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。
恢复数据的流程如下：
取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
用备份恢复出一个临时库；
从日志备份里面，取出凌晨 0 点之后的日志；
把这些日志，除了误删除数据的语句外，全部应用到临时库。
延迟复制备库 一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。
延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。只要在延迟时间内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。
预防误删库 / 表的方法 第一条建议是，账号分离。这样做的目的是，避免写错命令。比如：
我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。比如：
在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。
rm 删除数据 对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。
这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。
为什么还有 kill 不掉的语句 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。
收到 kill 以后，线程做什么？ 当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：
把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
给 session B 的执行线程发一个信号。
kill 不掉语句的情况
线程没有执行到判断线程状态的逻辑 终止逻辑耗时较长
超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。
关于客户端的误解 如果库里面的表特别多，连接就会很慢。
客户端在连接成功后，需要多做一些操作：
执行 show databases；
切到 db1 库，执行 show tables；
把这两个命令的结果用于构建一个本地的哈希表。
我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。
我查了这么多数据会不会把数据库内存打爆 全表扫描对 server 层的影响 MySQL 是“边读边发的”
InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。
查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。
全表扫描对 InnoDB 的影响 对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。
到底可不可以使用 join
如果可以使用被驱动表的索引，join 语句还是有其优势的；
不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用；
在使用 join 的时候，应该让小表做驱动表。
join 语句如何优化 大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
MRR MRR 优化后的语句执行流程：
根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
将 read_rnd_buffer 中的 id 进行递增排序；
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。
这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。
MRR 能够提升性能的核心 在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。临时表在使用上有以下几个特点：
建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。
为什么临时表可以重名 临时表在使用上有以下几个特点：
建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。
临时表特别适合 join 优化这种场景 ，原因是：
不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。
临时表的应用 由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。
分库分表两种实现思路：
第一种思路是， 在 proxy 层的进程代码中实现排序。
这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：
需要的开发工作量比较大。我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；
对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。
另一种思路就是， 把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。
比如上面这条语句，执行流程可以类似这样：
在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；
在各个分库上执行
1 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100 ;
把分库执行的结果插入到 temp_ht 表中；
执行
1 select v from temp_ht order by t_modified desc limit 100 ;
得到结果。
在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。
什么时候会使用内部临时表
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
都说 InnoDB 好，那还要不要使用 Memory 引擎 InnoDB 和 Memory 引擎的数据组织方式是不同的：
InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表 （Index Organizied Table）。
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表 （Heap Organizied Table）。
内存表不支持行锁，只支持表锁。
数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。
自增主键为什么不是连续的 表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。
在 MyISAM 引擎里面，自增值是被写在数据文件上的。而在 InnoDB 中，自增值是被记录在内存的。
InnoDB 中，只保证了自增 id 是递增的，但不保证是连续的。这么做是处于性能考虑：语句执行失败也不回退自增 id。
insert 语句的锁为什么这么多 insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。
怎么最快地复制一张表 mysqldump 方法 使用 mysqldump 命令将数据导出成一组 INSERT 语句。
1 mysqldump -h$host -P$port -u$user --add-locks =0 --no-create-info --single-transaction --set-gtid-purged =OFF db1 t --where ="a>900" --result-file =/client_tmp/t.sql
这条命令中，主要参数含义如下：
–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
–add-locks 设置为 0，表示在输出的文件结果里，不增加” LOCK TABLES t WRITE;” ；
–no-create-info 的意思是，不需要导出表结构；
–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。
导出 CSV 文件 另一种方法是直接将结果导出成.csv 文件
1 select * from db1.t where a>900 into outfile '/server_tmp/t.csv' ;
物理拷贝方法 假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：
执行 create table r like t，创建一个相同表结构的空表；
执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
执行 unlock tables，这时候 t.cfg 文件会被删除；
执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
grant 之后为什么要跟着 flush privilege grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。
flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。
要不要使用分区表 自增 ID 用完了怎么办 参考资料
