极客时间《MySQL实战45讲》-林晓斌-学习笔记
01 | 基础架构：一条SQL查询语句是如何执行的？
客户端-连接器-（缓存）-分析器-优化器-执行器-存储引擎
server层和引擎层
连接器管理连接，长连接占内存，短连接费时间，定期重连，或者5.7版本有自动重置长连接
缓存会因为写操作被清空，命中率实际上比较低，不建议使用。可以关闭，然后对特定语句加参数使用。
分析器检查词法和语法错误
优化器决定使用什么索引、连接顺序等，决定查询如何执行
执行器和存储引擎交互，具体执行，保持查询结果，记录遍历行数，返回结果
02 | 日志系统：一条SQL更新语句是如何执行的？
日志包括redo log 和bin log
redo log是InnoDB引擎引入的，类似于写磁盘的缓冲区，避免每次立即写磁盘，节省IO开销。
redo log在有限空间里重复滚动写，write pos和check point，落磁盘后被清理
redo log 用于crash-safe机制，重启不丢数据
bin log用于恢复数据，主从同步。是server层记录的，不区分存储引擎。
bin log是无限追加写的，不会清理旧的
redo log和bin log两段提交，要么都存要么都丢，类似于一个事务
03 | 事务隔离：为什么你改了我还看不见？
MyISAM引擎不支持事务，InnoDB支持
隔离级别有4种：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）、串行化（serializable）
读未提交：可以读到没提交的变更
读提交：只能读到提交了的变更
可重复读：这个事务内，后面读到的数据即使已经被别的事务修改，也依然是一开始读到的数据
串行化：对于同一条记录，读会加读锁，写会加写锁，串行执行
事务隔离通过视图实现，记录在更新的时候同时会记录回滚操作，视图通过当前值和回滚段得到视图值
长事务会导致回滚段巨大，应避免使用长事务
事务启动，可以显式begin/start transaction + commit/rollback ，或者autocommit = 0/1
可以在information_schema.innodb_trx 查找长事务
隔离是整个数据库的配置吗？四种隔离级别中读提交和可重复似乎有重叠？
04 | 深入浅出索引（上）
三种常见的索引类型：哈希表、有序数组、树
哈希表通过计算哈希值可以很快地随机寻找，但是因为不连续，如果是区间检索，就会失效
有序数组可以二分法搜索，也可以区间检索，但只适合只增不减的场景，如果中间插入，就会需要挪动所有后面的
N叉树读写都很快，而且适配磁盘，属于主流，mysql InnoDB用的是B加树
主键索引，叶子存的是整行数据，叫聚簇索引 clustered index
非主键索引，叶子存的是主键，叫二级索引secondary index
从二级索引里查找，需要先找到主键，再去主键索引查，这个叫回表
二级索引存主键，所以主键最好用小字段，不然会导致索引很占空间，所以主键尽量不用业务字段而是单独id
当只有一个索引的时候，可以用业务字段当作主键
05 | 深入浅出索引（下）
覆盖索引可以减少回表
可以针对频繁查询的字段建组合索引从而实现覆盖索引
最左前缀原则，组合索引可以左前缀查询，适当调整顺序可以减少索引数量
索引需要考虑字段大小，影响存储空间
5.6版本开始，组合索引支持索引下推优化（index condition pushdown），就是两个条件字段都在组合索引里的话，不需要回表
索引下推不是覆盖索引，是把查询条件进一步推到组合索引的其他字段因此不需要回表，覆盖索引是查询字段在组合索引里，不需要回表。
06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
锁有全局锁、表级锁和行锁
全局锁：Flush tables with read lock (FTWRL) 和 改数据库配置为readonly，但通常用前者，后者需要动配置，前者是会话级别的，会话如果断开会自动释放。而且readonly可能被用来判断是否从库。
全局锁的场景一般是在做全库备份。因为可能存在MyISAM这种不支持事务的引擎。实际不会这么干，基本都不会用这种引擎。
表级锁有两种：数据的锁，就是通常说的表锁，和meta data lock （MDL），就是表结构的锁。一般用InnoDB，不会用表锁，代价太大。所以要避免用MyISAM。在读写数据的时候，一般都会加MDL读锁，防止表结构更改。在做DDL的时候，加MDL写锁。
小表加字段的时候，如果小表被高并发读，也有可能因为MDL读锁，导致线程排队，拖慢整个数据库。首先要避免长事务，然后DDL执行的时候要设置等锁超时时间，防止无限等。
问：在从库全量备份的时候，如果对主库DDL，会怎样。如果DDL在备份的读表结构前到，没关系，会等到DDL跑完释放DML锁才读。在备份中读数据的时候到，问题不大，DDL会等DML读锁释放才执行，但是会导致binlog延迟。如果在读表结构读完，还没开始读数据的时候到，会备份中断退出，因为表结构变更了。
07 | 行锁功过：怎么减少行锁对性能的影响？
两阶段锁协议：在InnoDB中，行锁不是事务开始就加，而是执行到才加，然后事务结束才释放。
行锁是执行才开始，所以执行顺序有一点点影响，可以把重的执行放靠后一点，晚一点开始，占用时间短。
循环依赖会导致死锁，有两种解除机制。
一个是等锁超时。一个是主动死锁检测。innodb_lock_wait_timeout 默认配置是50s，实际上等不了这么久，会出大事了。也不能改小，会误杀正常的长事务锁。所以起作用的是主动检测，这也是默认开启的。
主动死锁检测，会对依赖资源的持有线程做检测，如果发现循环依赖，就会咔嚓掉其中一个。但是检测是o(n平方）的复杂度，如果同一个资源有高并发争抢，就会导致CPU快速消耗。所以如果高并发资源，要从设计上做分行，比如原本一条记录，把他拆成10条，取的时候做汇总。这样就能大大分散并发争抢。
08 | 事务到底是隔离的还是不隔离的？
默认情况下，事务的起点是在第一条InnoDB表的操作语句，如果要马上启动，需要用start transaction with consistent snapshot 这个命令。
事务启动，会创建一致性视图，类似于快照。这里有个概念叫MVCC（Multi-Version Concurrency Control）。
每个事务有tran id，严格递增，用来做类似版本号的东西。
每行数据，变更的时候都会记录row_trx_id，= tran id。
变更同时会有undo log，跟row_trx_id关联。
事务启动的时候，会记录开始的tran id，和当前所有活跃（启动了没提交）tran id。
然后对于读到具体的行的时候，取到这行所有的row_trx_id和undo log，根据当前事务id和活跃tran id做比较（得出事务先后顺序），在根据事务隔离等级，来看要不要undo。
对于读提交，查询只承认当前语句启动前，已提交完的数据。
对于可重复读，查询只承认当前事务开启前，已提交完成的数据。
09 | 普通索引和唯一索引，应该怎么选择？
这篇文章我觉得作者写得不够严谨，需要另外研究一下InnoDB Buffer Pool和Change Buffer的机制。
先知道一点，唯一索引需要判断唯一性，会有额外开销。
读取的时候，唯一索引找到目标直接终止，普通索引要继续，但因为整页已经加载到内存了，多几次读内存可以忽略不记。这里应该是InnoDB Buffer Pool，文章没有详细讲，减少读磁盘。
更新的时候，有个Change Buffer机制。记录写操作后，异步写磁盘，可以减少一次写磁盘。
insert时，唯一索引需要先读，判断冲突，所以不能用Change Buffer。普通索引直接写Change Buffer返回就完了。
update时，唯一索引也需要先读，因为可能会把key给改了，所以也不能用Change Buffer。普通索引也是直接记录Change Buffer返回，等后面再读到这条的时候，产生一次merge。
因为都有redo log，所以掉电不会丢，而且redo log是顺序写磁盘，比较省io。
Change Buffer主要是避免了一次随机写磁盘，把这次动作异步了，放到后面读的时候才merge（或者是到了时间定时刷入）
所以适合写多读少的场景，比如账单类、日志类。如果写完马上要读，会立即触发merge，所以没啥用。
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。文章最后有这句结论，其实是不准确的，要看一下其他的文章，深入了解InnoDB Buffer Pool和Change Buffer才能理解，应该是，InnoDB Buffer Pool省随机读写磁盘，Change Buffer省随机写磁盘（这是设计的主要初衷，一开始Change Buffer是只针对insert的后来才加了update和delete），但Change Buffer确实也省了一点随机读磁盘（merge后移）。
此处存疑：redo log真的是省随机写吗？它设计的初衷是故障时能够恢复。而redo log什么时候被刷掉？对数据的修改，到底是从buffer pool过来的，还是以redo log过来的，在网上很难找到准确的答案，官方文档也没有说清楚。
实际上，redo log并没有对于磁盘随机写进行优化，它的存在只为了故障恢复，确保一致性。为了优化随机写磁盘，会把数据更新记录到change buffer和innoDB Buffer Pool里的，这才是关键。redo log在空闲时被刷掉，它刷掉的动作，会结合buffer pool合并脏页，而不会直接把自己记录的数据更新写入磁盘。
附：
《MySQL各种“Buffer”之InnoDB Buffer Pool》 https://www. modb.pro/db/111341 ，
《MySQL缓冲池(buffer pool)，终于懂了！！！（收藏）》 https:// mp.weixin.qq.com/s/zMgf T_WmyYmmUuZUUr6xOA
《MySQL各种“Buffer”之Change Buffer》 https://www. modb.pro/db/112469 ，
《MySQL写缓冲(change buffer)，终于懂了！！！（收藏）》 https:// mp.weixin.qq.com/s/OMnt rDZsrDL29UhC4_DSBQ
10 | MySQL为什么有时候会选错索引？
优化器会选择索引，判断扫描行数、释放使用临时表、是否排序等等（没有展开说）
扫描行数的判断：根据索引的基数（cardinality），基数越大，区分度越好（不重复），但是基数是抽样统计得来的，并不是精确值，有时候会因为更改数据导致统计失真。
show index from t; 可以查看索引基数
变更行数超过1/M的时候会触发索引统计，也可以手动触发，analyze table t
索引选择异常通常有3种处理思路：
1.采用 force index强行制定索引。弊端是不优雅，如果索引改名这里会报错。如果迁移数据库会导致语法不兼容。
还有通常是线上发现慢了才会加force优化，而这要测试和发布，流程不敏捷。
2.改查询语句，欺骗优化器。缺点是需要对索引选择机制很熟悉才懂，每一条查询的改法都不同，而且有的甚至导致查询逻辑改变了，不够优雅，而且这种做法同样不具备通用性。
3.新建一个更合适的索引，或者删掉误用的索引。
实践上来看，不需要非常了解索引选择的原理，只需要知道几种常用思路和方向即可。遇到问题再具体分析。
通常，大点的公司会配DBA，小公司的话现在基本也是用云服务，云服务商（比如阿里云）也会有语句自动优化建议或者提工单人工给建议（有点慢就是了）。
11 | 怎么给字符串字段加索引？
可以前缀索引，就是截取前面n个字节
优势是占用空间更小，坏处是可能会有额外的扫描次数
用字段区分度来选取前缀索引的长度，比如区分度损失比例在5%以内是个不错的选择
区分度≈distinct / count，区分度损失 = 1- 截取n字节的区分度 / 全字段的区分度 和
前缀索引会导致覆盖索引失效，因为索引只有截取段，必须回表取到全字段
前缀区分度不高的时候，有2种做法：
1.可以倒序存储，再做前缀索引
2.增加一个对原字段的hash字段，索引这个字段
二者都会对范围查询失效，因为相当于顺序被打乱了
二者区别：
hash额外占用空间，需要增加字段，但也可能缩短长度，看场景，空间占用也差不多
hash有额外的cpu消耗
hash可以保证小概率的冲突（也就是高区分度），更加均匀
12 | 为什么我的MySQL会“抖”一下？
偶尔很慢，可能是在flush，刷脏页
有几种场景会触发flush：
1.redo log满了，所有更新会阻塞刷掉redo log
2.内存不够，刷掉内存里脏的缓存页
3.空闲的时候主动刷
4.关闭之前刷
1的场景会引起全部更新阻塞，需要尽量避免
2是常态，选取一个平衡就好，避免单个查询触发的flush过多
需要明确配置innodb_io_capacity 告诉mysql磁盘的iops性能
还有就是脏页比例innodb_max_dirty_pages_pct ，默认是75%，可以根据情况调整
innodb_flush_neighbors 刷脏页的时候顺便把相邻数据页刷掉，用于HDD，因为HDD的随机读写性能明显很差，相邻页刷可以变为顺序读写，提升速度。如果是SSD就要关掉。
这篇文章其实讲得不太清楚，redo log的flush跟innodb buffer pool的flush机制不一样，
redo log的flush可以看这里，在写磁盘前它也是先写内存的，这里flush是读内存到顺序写磁盘。
丁奇文章里说的刷，很容易误解为是redo log的行数据变更记录刷写到磁盘去，是顺序读磁盘到随机写磁盘。
这里需要注意，redo log并不会直接把表数据的更新写入磁盘，它的主要作用是确保崩溃时的数据一致性，数据更新是在innodb buffer pool里的，而它没有马上写磁盘，会结合redo log来记录哪些数据被更新过，刷的时候从buffer pool里刷，也就是刷脏页。
redo log本身也有内存缓冲，叫redo log buffer，所以你会看到大事务的提交也没有因为redo log而很慢，就是因为先写到了内存，然后一次flush从而加速
13 | 为什么表数据删掉一半，表文件大小不变？
InnoDB表包含表结构和数据，8.0以前表结构单独.frm文件存储，后放在系统数据表中
表数据可以放在共享表空间，也可以是单独文件，由innodb_file_per_table控制，5.6.6开始默认ON
OFF-共享空间，ON-每个表数据单独存在.ibd文件中
单独文件存储时，drop table的时候会直接删除文件
InnoDB的B+数索引，是按页存储的，删掉一行数据只是在页上标记某个磁盘位置可用，删掉一整页也只是标记这一页磁盘位置可用，不会释放空间
如果数据更新导致相邻页的利用率很小（就是页上比较空），就会合并页面，反之会拆页
所以增删改都会导致拆页，造成页空洞，难以避免
重建表可以去掉空洞（有点像早期win磁盘整理）：alter table A engine=InnoDB 命令
5.5版本之前，是建临时表的方式，非inplace，也非onlineDDL，会锁表
5.6开始引入onlineDDL，重建表是建临时文件，在引擎内完成，inplace，只会锁一小会MDL
inplace是原地执行的意思，在innoDB引擎内部执行，不会产生临时表，可能会有临时文件
online DDL意思是不阻塞增删改操作，就是不会锁表
recreate重建表从5.6开始是online的，恰好也是inplace的
analyze table是重新对表索引进行统计（优化器选择索引时用），不是重建表
optimize table t 等于 recreate+analyze
14 | count(*)这么慢，我该怎么办？
MyISAM引擎会直接存表的行数，count可以直接返回
而InnoDB会实时统计，因为有MVCC，所以没法确定准确行数
count(*) 操作是优化过的，会取最小的索引数来遍历计算
show table status里面的行数是抽样统计出来的，误差很大，可能40-50%，不能用
如果业务上有需要统计数量，最好是另外设计一个保存计数功能
如果用redis来存，因为是两套事务，不可能统一，所以没法严格确保一致性
用mysql存，放在一个事务里，可以确保一致性
count(字段)count(字段)：需要取出值来，如果字段允许null还要做非空判断
count(主键id)：会取id值，但不需要做非空判断
count(1):遍历全表但不取值
count(*):专门做了优化，遍历全表，不取值
15 | 答疑文章（一）：日志和索引相关问题
InnoDB引擎下，事务的两段提交，如何确保数据完整
redo log已经完成prepare，在写binlog之前，崩溃，事务回滚
redo log完成prepare，binlog写完，redo log还没commit，崩溃：检查redo log是否完整，完整则提交。不完整的话，如果有完整的redo log prepare，以及完整的binlog，则提交。否则回滚。
怎么知道binlog完整：statement的有commit标记，row的有XID event。且5.6.2以后有checksum。
redo log和binlog通过XID关联。
为什么要两阶段提交，redo log写完再写binlog不就可以拿来恢复了？- redo log如果提交就认定事务完成，这是前提不能违背，两阶段确认是给时间二者都准备好，一起提交。
只用binlog不就好了？- 各有各的生态，原因有很多，有历史因素也有设计考虑，都有存在的必要。redo log是InnoDB内部的，如果干掉，那binlog就需要侵入到引擎层，对磁盘数据页具备读写能力，相当于又做了一个redo log，不符合分层思想。
只用redo log不就好了？- binlog已经有很多使用场景，比如主从同步，比如跨库业务的数据监听同步等等，也不能干掉。还有binlog不是循环写而是增量写，有回溯功能，redo log是循环写，有顺序写磁盘的性能优化功能。总体来说二者各有所长各有分工，不能一概而论。
redo log一般多大？现在常见都是TB级别的磁盘，那redo log就可以4x1GB
最终落盘，是来自redo log还是buffer pool？是buffer pool里的脏页写磁盘。注意这里很容易有误解，尤其是前面的文章会导致误以为redo log记录的行数据更新，会被逐步刷入硬盘。前面说到，redo log号称把随机写优化成顺序写，有写入刷出的概念，那么也是要落盘的，那到底怎么落？这一篇也明确说了：实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。redo log是设计用于崩溃恢复的，确保数据一致性。它的擦除，会配合buffer pool里的页落磁盘，它自己并不落磁盘，所以说它对于数据从随机写优化为顺序写，是不对的。
https://
betterprogramming.pub/m
ysqls-redolog-and-binlog-1a35bc052489
redo log buffer是什么？是redo log在内存里的缓存，用于加速redo log的。
业务设计问题：需要锁对称数据的时候，发现二者都是新增，无从加锁。可以抽象出两个字段，对两个字段加唯一索引，利用唯一索引，从数据库层面确保一致性。
尽量不要用唯一索引，不是不让用，业务场景需要用就用，不要因噎废食
16 | “order by”是怎么工作的？
全字段排序：空间足够的情况下，order by语句的排序会是把所有字段放在一起排序
sort_buffer是为排序开辟的内存，sort_buffer_size配置了内存大小
如果这块内存不够放，就会用磁盘临时文件辅助排序
可以通过SET optimizer_trace='enabled=on'; SELECT * FROM `information_schema`.`OPTIMIZER_TRACE` ; 查看是否用到临时文件做排序
rowid排序：单行长度太大，只排序order by字段，其余字段回表拿
max_length_for_sort_data参数控制了排序的单行长度上限，超过的话就会使用rowid排序
rowid排序会增加回表，会造成读磁盘，不会被innodb优先选择（执行全字段排序减少磁盘访问，会被优先选择）
不是所有的order by语句都要排序操作，如果被排序字段有索引或者联合索引，就是天然排序过的
如果有覆盖索引，可以进一步优化，不光不需要排序，连回表都不需要
17 | 如何正确地显示随机消息？
explain查询语句，Extra显示using temporary表示需要使用临时表，using filesort表示需要执行排序操作
对于InnoDB表，执行全字段排序会减少磁盘访问（减少回表）因此会被优先选择。
而memory引擎的表，回表过程直接访问内存性能较好，而用于排序的行越小越好，因此优先考虑rowid排序。
临时表排序的执行步骤：1.创建临时表 2.扫描原表读出数据写入临时表 3.创建sort_buffer 4.把临时表数据导入sort_buffer 5.排序 6.按顺序从临时表取结果集
MySQL用rowid来定位行数据，有主键的话就是id，没主键的话会生成一个6字节的rowid
memory引擎的临时表无索引，直接用rowid标识行。
不是所有临时表都是内存表，如果超过tem_table_size（默认16M），就会用磁盘临时表。
5.6版本引入了新的排序算法，优先队列排序，如果是order by xx limit n的话，可能会用，这样可以省sort_buffer空间。
在表里随机取1行，有两种方法：
随机算法1： 1.取主键最大M和最小N 2.随机X=（M-N)*rand() + N 3.取>=X的第一行
随机算法2： 1.取表行数C 2.随机Y=floor(C*rand()) 3 limit Y,1取1行
算法1如果行之间有空洞，会导致大空洞后面的行更高概率被取到，不是真正的随机
算法2需要扫描C+Y+1行，扫描行数大，如果是大表就不宜使用，但比rand()要代价小很多。
18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？
存在一些sql看上去逻辑相同，但是实际运行起来性能差距巨大
案例一，条件字段函数操作：where month(t_modified)=7 这样对索引字段做函数运算的，会导致索引失效，全索引扫描。应该改为(t_modified >= '2016-7-1' and t_modified<'2016-8-1') 类似的操作。
案例二，隐式类型转换：本质上也是对索引字段做了函数运算导致索引失效。比如对字符串类型的字段，使用整型的查询条件。where tradeid=110717 等价于where CAST(tradid AS signed int) = 110717
案例三，隐式字符编码转换：字符集不同的表比如utf8和utf8mb4，在做连接查询的时候，会做字符编码转换，导致对索引字段做函数运算。where tradeid=$L2.tradeid.value; 等价于 where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
这里可以转换驱动表和被驱动表：where traideid =$R4.tradeid.value 等价于 where traideid =CONVERT($R4.tradeid.value USING utf8mb4) ，这样就没有对索引字段做函数运算，可以用到索引。
也可以修改DML： where d.tradeid=CONVERT(l.tradeid USING utf8) ，在驱动表做转换，避免了被驱动表的隐式转换。
确认驱动表，需要看执行计划，先执行查询的是驱动表，后的是被驱动表
where id+1=1000也算函数运算，会导致索引失效。
思考题（内容是在下一期，但跟这期相关，就放这里）
对字符串字段查询，如果查询条件超过字段长度，会把查询条件截断，命中之后再回表读数据出来比对，但肯定对不上，就会浪费查询。
19 | 为什么我只查一行的语句，也执行这么慢？
第一类，查询长时间不返回：
select * from t where id=1 卡住，大概率是锁表了
可以用show processlist命令查看当前执行，
Waiting for table metadata lock 就是等MDL锁，其他线程正在表上持有或者请求MDL写锁
performance_schema=on的设置下，会比off有10%的性能损耗
可以查询sys.schema_table_lock_waits查看造成阻塞的id，从而kill掉它
Waiting for table flush，有别的线程在执行表关闭操作，处理也是类似的，找到卡住的线程杀掉它
如果是行锁，5.7版本可以查sys.innodb_lock_waits表来定位，可以按表名查询
第二类：查询慢
select * from t where c=50000 limit 1 ，如果c字段无索引，会走主键顺序扫描，找到为止，如果命中靠后，就扫了几万条。
一致性读前提下，如果该行数据被另一个事务频繁更改，需要顺序undo，极端情况下可能执行上万次，也会耗费时间。
20 | 幻读是什么，幻读有什么问题？
幻读，是一个事务在前后两次查询同一个范围，后一次查询看到了前一次没有看到的行。
说明1：可重复读隔离级别下，普通查询是快照读，不会看到别的事务插入的数据。幻读只在“当前读”下才会出现。
说明2：update结果导致的前后读不一致，不能称为幻读，幻读仅专指“新插入的行”
幻读有什么问题？
1.破坏语义，破坏加锁声明。比如：我要把所有 d=5 的行锁住，不准别的事务进行读写操作”，实际上幻读的数据是不会被锁的。
2.破坏一致性。这个一致性，除了内部数据状态的一致性，还包含数据和日志在逻辑上的一致性。
如何解决幻读？
InnoDB 引入新的锁，间隙锁，gap lock，锁住两个值的间隙
但是间隙锁，可能会导致同样的语句锁住更大的范围，影响并发
简单来说，这篇文章介绍了幻读和间隙锁，没有深入讨论，在下一期会对间隙锁做具体介绍，这期只要对这两个概念有个印象就好。
21 | 为什么我只改一行的语句，锁这么多？
这篇文章是基于5.x 系列 <=5.7.24，8.0 系列 <=8.0.13的版本号的。新版本不排除会更改逻辑。
文章总结的间隙锁的加锁规则，包括2原则、2优化、1bug
原则1：加锁基本单位是next-key lock，前开后闭
原则2：查找过程中访问到的对象才会加锁
优化1：索引上的等值查询，给唯一索引加锁，next-key lock退化为行锁
优化2：索引上的等值查询，向右遍历且最后一个值不满足等值条件，next-key退化为间隙锁
bug：唯一索引上的范围查询，会访问到不满足条件的第一个值
间隙锁案例：
案例1，等值查询间隙锁
update t set d=d+1 where id = 7
表中没有id = 7的记录，前一条是5后一条是10，根据原则1和优化2，锁(5,10)
无法插入id=8，可以插入id=10
案例2，非唯一索引等值锁
select id from t where c=5 lock in share mode
前一条是5,5,5 后一条是10,10,10
给c=5的索引上读锁，给(0,5]加next-key lock，给(5,10)加间隙锁，但是id索引不会被锁
lock in share mode只锁覆盖索引，但for update会连主键索引也锁
案例3，主键索引范围锁
案例4，非唯一索引范围锁
案例5，唯一索引范围锁bug
案例6，非唯一索引上存在“等值”的例子
案例7，limit语句加锁
案例8，一个死锁的例子，要注意，next-key lock会分为间隙锁和行锁两个锁来执行
案例太多了，很难总结，我看完两三遍也很难记住，有个印象就好吧
22 | MySQL有哪些“饮鸩止渴”提高性能的方法？
连接数不够问题
如果用的是短连接，在业务高峰可能会出现连接数爆涨
超过max_connections参数定义的连接数，就会拒绝连接
如果修改参数提高连接数，要注意风险，可能负载很高已经快要撑不住，加连接数没有用，反而增加资源耗费在权限验证和建立连接上，进一步拖慢
处理方法1，干掉占着连接但是不工作的线程
间隔30秒执行多次 show processlist，找出sleep的线程，再看information_schema 库的 innodb_trx 表看事务状态，找出不在事务中的线程，可以杀掉。
注意如果 kill connection + id 命令,可能会导致客户端报错，甚至不自动重连，所以这种操作要通知对应开发人员
处理方法2，减少连接过程的消耗
直接跳过权限验证，重启数据库，并使用–skip-grant-tables 参数启动
注意如果是8.0版本，这样做的话会自动打开--skip-networking，表示只能本地客户端连接
这个操作风险很大，建议不要轻易尝试
慢查询问题
3种可能：1.索引没设计好 2.SQL语句没写好 3.MySQL选错索引
1.索引没设计好可以临时加索引，从5.6开始支持online DDL，可以直接加索引。最好是从库先加，主从切换，再做主库。如果是平时可以用gh-ost工具，紧急情况手动比较快。
2.查询语句没写好，5.7开始支持query_rewrite功能，可以从数据库层面自动偷换语句
3.选错索引，可以加force index，也是结合query_rewrite来加
一般都是前两种情况，可以在测试环境预先发现，把slow log打开，记录所有慢日志，然后看rows_examined有没有超出预期
有工具可以辅助检查SQL语句结果，比如 pt-query-digest(
https://www.
percona.com/doc/percona
-toolkit/3.0/pt-query-digest.html)
。
QPS突增问题
业务突然高峰，或者程序bug导致某个语句频繁执行
如果是新功能，就想办法下掉他，如果是新业务直接白名单开除，或者有建独立数据库用户可以直接关闭，也可以用语句重写成select 1返回，但风险很大可能会影响其他功能。
23 | MySQL是怎么保证数据不丢的？
这篇还是说高峰期临时提升性能，只不过有关binlog和redo log的写入配置
binlog的写入，会先写binlog cache，是写内存的，在事务提交的时候才写磁盘binlog，而写磁盘也有两步，1是write先写到page cache，2才fsync写磁盘
write和fsync的时机是sync_binlog参数控制的：
1.为0，每次只write不fsync
2.为1，每次提交都fsync
3.为N（>1）时，每次都write，但积累N个事务才fsync
如果是IO瓶颈，可以把sync_binlog设置成大于1的值（常见100-1000），延迟写磁盘，如果主机重启，会丢失N个事务的binlog。不建议设0，风险较高，丢失量不可控。
redo log的写入，也有缓存，叫redo log buffer，也是积累了才拿出来写磁盘，大事务的时候提升明显。这里说的是写redo log到磁盘上，不是把数据更新操作写入表数据。
也是分三步，1写buffer，2写page cache，3写磁盘
由参数innodb_flush_log_at_trx_commit控制：
1.为0，每次提交事务都只写buffer
2.为1，每次提交事务都写磁盘
3.为2，每次提交事务只写page cache
默认情况下，每隔1秒，会把buffer里面的redo log刷出来，先到page cache再写磁盘。
除此之外，还有2种场景，会刷：
1是redo log buffer 占innodb_log_buffer_size 超过一半，但因为事务没提交，所以只会write到page cache
2是并行的事务提交，会顺便把相关的buffer写到磁盘
时序上 redo log 先 prepare， 再写 binlog，最后再把 redo log commit
innodb_flush_log_at_trx_commit =1时，在prepare就会持久化，所以在后面的commit时候就不需要fsync，只需要write就行。
通常说的双一配置，值得就是sync_binlog和innodb_flush_log_at_trx_commit都是1。就是说，一个事务完整提交前，需要等两次写磁盘，一个redo log(prepare阶段)，一个binlog。
这里有个优化，叫组提交，可以按照LSN（log sequence number）长度（跟redo log长度有关）来合并写多个事务。
于是，可以在两阶段提交的时候，稍微拖一点时间，把redo log的fsync放到binlog write后，这样可以积累多个一起写。binlog也是组提交的，但不如redo log效果好，因为binlog的write和fsync间隔时间短，导致合并binlog的时间少，积累不了很多。
binlog组提交的配置：binlog_group_commit_sync_delay 表示延迟多少微妙才调用fsync
binlog_group_commit_sync_no_delay_count 表示积累多少次才调用fsync
WAL机制有两个提升性能点：1.redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快 2.组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
所以总结来说IO瓶颈，可以：
1设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电，redo log会丢，也就是数据丢了。
注意：innodb_flush_log_at_trx_commit 别设置为0，这是只写内存，这样mysql重启也会丢数据，如果是2是写page cache虽然也是内存但是是操作系统负责管理的，机器重启才会丢数据。
课后问题：什么时候会设置非双1
1.预知的业务高峰 2.主从同步延迟过高需要追赶 3.备份恢复时 4.大量导入数据时
一般情况下，非双1，是指 innodb_flush_log_at_trx_commit=2，sync_binlog=1000
24 | MySQL是怎么保证主备一致的？
MySQL的高可用，依赖于binlog
备库建议设置成只读（readonly）：1.防止误操作写 2.防止切换时双鞋 3.用readonly判断节点角色
主备库之间维持长连接。主库内有一个线程专门用于服务这个连接。
同步过程：1.备库change master命令，设置主库信息和同步位置 2.备库start slave，启动线程io_thread和sql_thread。3.主库校验权限后，按照约定的位置，从本地读binlog，发给备库 4.备库拿到binlog，写本地文件relay log，中转日志 5.sql_thread读中转日志，解析执行
binlog有三种格式：1.statement 2.row 3.mixed
statement记录的是sql语句，会存在索引选择不同导致的一致性风险
row格式会记录行数据完整信息，缺点是很占空间
mixed格式介于二者之间，由mysql自己推断，某一个事务的binlog是用语句还是数据
现在很多场景都是用row，实践中通常不会是statement，至少也要mixed
row格式存，有个明显好处是，恢复数据
增删改都会有完整的详细的记录，如果误删也可以从row里面拿数据转成insert
MariaDB有个恢复工具
https://
mariadb.com/kb/en/libra
ry/flashback/
，是基于binlog来回滚的
statement除了存语句，还会存执行时间戳，会自动替换now()，确保时间戳不会被主从延迟耽误，所以如果是手动执行statement的binlog的话，要特别注意这点。建议用mysqlbinlog工具解析出sql语句再执行。
循环复制问题：双主结构下，两节点互为主库，如果不做处理binlog会来回传，就循环复制了。
解决方式：两个库的server id配置为不同，接收到binlog的备库如果发现server id是自己就直接丢弃。
三节点主从的情况下（一般是在做数据库复制），这种方法会失效，需要手动配置IGNORE_SERVER_IDS。
25 | MySQL是怎么保证高可用的？
binlog同步只是确保一致性，高可用还需要别的机制。
主备延迟：同一个事务，主库执行完-备库执行完=延迟
在备库执行show slave status命令，返回的结果离seconds_behind_master会显示
即使主备库所在机器的时间不同，也有自动去差值的机制
主备延迟的来源：
1.备库性能比主库差
虽然一般会把备库配成“非双1”，可能会有多个备库放在同一机器。而且更新的时候也有大量读。现在这种比较少了，考虑会有主备切换的可能，会对称部署。
2.备库压力更大
虽然是对称部署，但有时担心影响业务，会把更多的查询分析语句放在备库跑
这种情况可以一主多从，增加分摊。或者数据分析类用其他的数据架构，比如用binlog同步到Hadoop之类。
另外从库也适合用来做定期全量备份。
3.大事务
如果主库一个事务执行了10分钟，那执行完才给从库，从库执行完又要重新10分钟，那就会延迟10分钟。
所以说不要一次delete太多数据，可以切开执行。
还有大表DDL，会锁表，虽然5.6开始支持online DDL，本身也是大事务。建议用gh-ost方案来做。
4.备库的并行复制，有些情况可能无法并行
主备切换的两种策略：可靠性优先、可用性优先
可靠性优先：需要等主备延时比较小才能开始，因为切换的时候主备都会禁止写入，造成短时间业务中断，如果延时很长，中断就会很长。
可用性优先：强行切，不等同步完，备库迅速成为主库，开始接受写入。代价就是数据不一致。
如果binlog是row格式，更容易发现数据不一致，比如，同步线程如果发现主键冲突就会报错停下。
绝大多数情况下都建议用可靠性优先策略，可用性优先的场景少，也有：
1.业务对数据一致性要求很低，比如只是记录操作日志，不一致并不会引发业务问题，但写不了就会严重影响业务
2.主库已经挂了，别无选择
所以说，为了保证高可用，平时需要监控主备延时，确保其在可控范围。
26 | 备库为什么会延迟好几个小时？
主库对并发的支持比较好，备库同步在5.6才开始支持多线程，在之前只能单线程，如果主库并发高就很容易出现延迟。
并行复制需要满足两个基本规则：1.更新同一行的两个事务，必须串行。2.同一个事务内的语句必须串行。
在5.5还没有并行复制的时候，只能自己实现并行复制：
1.按表分发策略：用表名hash，单表事务可以直接确保同表落到同一个worker线程。多表事务，执行前先判断会不会有冲突，如果有，分配全部挂起，等冲突解决再分配。
这种情况在没出现热点表的情况下可行，如果有热表，就会退化到单线程复制。
2.按行分发策略：要求binlog是row，key加长，变为库名+表名+键值
需要比按行分发消耗更多的计算资源，因为要解析binlog，也更耗内存，hash对每行都要存。而且有约束条件，要求必须有主键，且不能有外键。
MySQL 5.6版本的并行策略：按库并行。粒度比较粗，计算量和存储都耗费很小，而且不要求binlog格式。缺点是如果有热点库，就会退化成串行。
MariaDB的并行复制策略：组复制，为一组事务添加一个全局commit_id，整组并行完了再下一组。
缺点是备库执行的时候，需要等第一组事务完全执行才能开始，存在空隙，并发度比主库差。容易被大事务拖后腿。
不过这种方式对原系统改造很少，实现很优雅。
MySQL 5.7的并行复制策略：可以通过参数slave-parallel-type控制，=DATABASE就是按库并行，=LOGICAL_CLOCK是类似分组并行。这里的分组，比起MariaDB的设计，增加了prepare状态事务的并行度。
因此binlog的组提交延迟的配置（binlog_group_commit_sync_delay 和binlog_group_commit_sync_no_delay_count ），也能对prepare的事务数量累积产生效果，可以增加并行度。
MySQL 5.7.22 又增加了一种并行策略：WRITESET ，类似于前面的按行分配。而且他不需要解析binlog节省了很多计算。
总结：大事务容易影响同步导致延迟，要避免。
27 | 主库出问题了，从库怎么办？
这篇文章关注的是一主一备多从的故障切换
切换的时候，需要对新的主从关系，指定从库同步新主库的文件名和日志偏移量
通过时间戳寻找位点，但是不准确，需要找一个稍微往前的位置，同步过程中会遇到主键冲突停止，可以有两种方式手动跳过：1.set global sql_slave_skip_counter=1 指定跳过的事务数 2.slave_skip_errors可以设置为1032,1062，表示跳过插入数据遇到唯一冲突和删除数据找不到行，这种无损的错误
5.6版本引入了GTID可以更加优雅的判断重复不执行
GTID=Global Transaction Identifier，全局事务id，包含server_uuid和gno，前者是每次启动自动生成的全局唯一标识，后者是每次提交事务的时候累加1。注意gno不是事务id。
同步时，可以手动把需要跳过的GTID加入从库，同步到这条事务就会跳过
基于GTID做主备切换，连接时会先同步新主库的GTID set，和本地做差集判断，然后找到非重复的位点自动开始，不需要手动设置位点
双M结构下，加索引可能会先在从库做，为了避免传给主库需要关闭binlog，但是这样会造成数据和日志不一致，如果开了GTID，就不需要关闭binlog了，可以手动把GTID拿到主库就可以避免重复执行
GTID模式下主从切换很方便，版本支持的话，建议打开
28 | 读写分离有哪些坑？
读写分离一般有两种实现方式，客户端负载均衡，还有是客户端通过一个代理层proxy连接MySQL
客户端直连，少了一层proxy，性能好一点，架构简单排查问题方便。但是客户端会感知到主备切换或者库迁移，增加维护难度。
通过代理连接的话，对客户端是透明的，但是对proxy层维护的技术难度会更大，整体架构也会比较复杂。
本期文章关注的是，读写分离的时候，主从同步可能延时，如何确保读写分离时的数据一致。
防止过期读，大概有这些方案：1.强制走主库 2.sleep再读 3.判断主备无延时 4.配合semi-sync 5.等主库位点 6.等GTID
强制走主库：从业务要求看，需要确保一致性的，就强制走主库读。这个方案简单，看似有点low实际上用得很广泛。
sleep：读之前预先等一会，假定主从延时不超n秒，从概率上降低读过期数据。一定程度可以解决，但不能确保。
判断主备无延时：这里有三种，a.seconds_behind_master，看主从同步延时是不是0 b.对比位点，就是binlog的Pos c.对比GTID集合。但是如果一直没追上，这种就会一直读不到。而且主从延时就算是0，可能因为主库的binlog还没传过来，实际上没同步到。
semi-sync：从库收到binlog后要回ack，主库收到之后才完成事务。这种也只能比前一种能确保有做到同步（一主多从下做不到），但是一直追不上的情况也解决不了。
等主库位点：写完主库拿到当时的位点，然后在从库查看这个位点有没有执行到。如果超过n秒都没等到，退化成主库查询。这种方法相对前几种会比较准确。
等GTID：跟等位点差不多意思
不管用哪种方法，如果主从持续延迟，都可能会打回主库查询，读写分离失效，所以，还是得尽量想办法确保主从延时不大。
29 | 如何判断一个数据库是不是出问题了？
select 1; 返回成功，只能说明库的进程还在，不一定是没问题。
InnoDB中innodb_thread_concurrency参数默认 0，不限制并发线程数，通常建议是64-128
这是并发查询数，和并发连接数不是一回事，前者费CPU，后者费内存
等锁，包括行锁（间隙锁）之类的线程，不算在并发查询数里
可以改为建个单行数据的表在系统库，select * from mysql.health_check，可以检测出并发线程满导致的数据库不可用，但是对于binlog占满磁盘，写操作会被阻塞，但是读操作还正常，所以这条语句检测不出来这种异常
可以改为更新判断update mysql.health_check set t_modified=now();，但是对于有主从结构的，从库也要这么检测，很容易出现冲突导致主从同步中断
可以改为多行，并把server_id作为主键。用这个检测语句：insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now(); ，这是比较常用的判断语句了，可以覆盖多数场景。
但是这个判断对于IO到了100%，数据库出现卡顿的情况很难检测出来
可以检测内部的统计数据，5.6版本提供了performance_schema库，在file_summary_by_event_name表里统计了每次io的时间
如果打开所有的performance_schema项，性能会减少10%，可以选择性打开
上面提到的每一种方案代价和准确度都不同，需要根据实际情况来权衡
30 | 答疑文章（二）：用动态的观点看加锁
这期是答疑，结论性的内容不多，有比较多的细节讨论，就不详细记录了
间隙锁中，范围查询里面不是等号，为什么有等值查询？--在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法
in查询获取的多个锁，是一个个加的，不是一起加的
怎么看死锁？出现死锁后，执行 show engine innodb status 命令可以看到引擎日志输出的一些争抢锁的记录。
所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的
31 | 误删数据后除了跑路，还能怎么办？
本期内容，讨论了数据恢复的方法，更多的是如何做预案和防止误删事故的发生。
如果是delete删除了行数据，可以用Flashback工具恢复，原理是修改binlog内容，重放。前提是binlog是row格式，full范围。
涉及多个事务的话，需要逆序执行
建议不要在主库操作，可以恢复出一个备份，或者用一个从库，作为临时库，在临时库上执行，然后确认过ok再恢复回主库。
更重要的是如何预防：1. sql_safe_updates参数=on，如果delete或update语句没有where就无法执行 2.代码上线前必须审计sql
如果是truncate /drop table /drop database这种语句删除的，没有binlog，要恢复的话，就只能是全量备份+增量binlog重放，前提是定期全量备份，且有记录binlog。
也是建议用临时库，先全量恢复出一个最近的全量库，然后从那个时间点重放binlog（排除误删语句）。
如何加速？如果是有多个库，可以在mysqlbinlog命令上加database参数缩小范围
如果没有GTID模式，要定位到误删数据的pos，手动跳过。如果有GTID就简单多了，把gtid加入临时库集合即可。
如果是误删表，mysqlbinlog方法恢复没法指定解析单表日志，而且这个工具解析日志是单线程。
如何加速？可以不用mysqlbinlog，而是把这个临时库作为备库的从库，就可以指定表范围，也可以用上并行复制。
binlog文件可能被清掉了，如果有备份出来的话，要找出来放回去，然后重启备库才能识别到。
恢复数据这种事，要做成自动化工具，并且要经常拿出来演练。一来是可以轻车熟路更快操作。二来是避免手忙脚乱二次犯错。
前面的方案，仍然可能存在数据太大恢复慢，比如一周一备，刚好赶上第6天出问题，那就要重放6天日志，可能要跑一天。
还有一种恢复方式可以缩短恢复时间：搭建延迟复制的备库。从5.6开始支持。
延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。
这样的话就可以随时保持一个N秒延迟的库，只需要追一会就能恢复。
以上是恢复，那么如何预防误删？
账号分离，只给业务开发DML劝降，不给truncate/drop。DBA也有多账号，日常操作只用小权限账号。
制定操作规范，避免写错。删表之前，先对表做改名，过一段时间再删。改表名加固定后缀，删表由管理系统执行，并且限制它只能删固定后缀的表。
再强调一下，预防远比处理更重要。
32 | 为什么还有kill不掉的语句？
MySQL有两个kill命令：kill query + 线程id 和 kill connection + 线程id，前者表示终止线程中执行语句，后者是断开线程的连接（也会终止语句）。但是很容易出现kill之后还能看到这个连接（语句）。
实际上kill不会强制马上终止线程，而是发信号告诉线程，开始执行“停止的逻辑”。
执行kill query时，1.把会话的运行状态改成THD::KILL_QUERY 2.给会话的执行线程发一个信号
隐含三个意思：1.语句执行过程中有多处埋点，去判断线程状态，如果发现是KILL的，进入语句终止逻辑。2.如果是等待状态，而没被唤醒，就不会执行到埋点处 3.终止逻辑也有一个执行过程
什么情况下容易出现kill不掉：1.线程没有执行到判断状态的地方，可能是并发限制数到了，或者是IO压力过大被卡 2.终止逻辑耗时长，a.超大事务跑一半被终止，回滚慢 b.大查询。产生了临时文件需要回收等io c.DDL执行到最后阶段，回滚需要删除临时文件
有几个来自客户端的误解：
客户端退出，服务端的线程也是走类似被kill的逻辑，只是从客户端看起来好像马上停了。
库里的表很多，客户端连接会很慢。其实是客户端慢，需要做表名字段补齐，在本地建一个表结构的索引，表多就会很慢。可以加上-A参数关闭自动补全。
还有一个-quick参数，也是只加速客户端，而且，这个可能会降低服务端性能。它的作用有三：1.跳过表名补全 2.使用mysql_use_result，本地不缓存查询结果 3.不记录执行命令历史
而本地缓存查询结果（mysql_store_result）这个，如果不缓存的话，是服务端持续发送，如果客户端处理不及时可能会阻塞服务端。
总结来说，kill动作不能马上终止线程释放资源，你只能通过影响系统环境让kill掉的线程尽快结束，比如调大并发线程数或者停掉别的线程释放一些资源。然后只能等。
如果把mysql强行重启，kill掉的线程的回滚动作还是会继续的，并不能起到很直接的效果，倒是可以终止其他线程减少系统压力，加速终止逻辑。
33 | 我查这么多数据，会不会把数据库内存打爆？
在server层面，全表查询不会把表都读到内存
查询结果先放到net_buffer中缓冲，再发给客户端，大小由net_buffer_length决定
数据是边读边发的，不是全部读完才发
如果processlist里的状态是Sending to client，表示服务端的网络栈写满了，在等，如果客户端没有缓冲而且接受得慢的话，服务端就容易堵（用了-quick参数）
sending data状态不是等网络，比如可能是等锁，所以不要误会是网络卡了
在InnoDB层面，Buffer Pool可以加速查询
执行 show engine innodb status ，可以看到“Buffer pool hit rate”，表示内存命中率，越高越好
BP的大小由innodb_buffer_pool_size控制，一般是设置成物理内存的60-80%
InnoDB的BP，是链表实现的，原理是LRU，为了避免冷数据的读取冲掉BP里的热数据，基于LRU算法做了改进
把链表分成了young和old区域。热数据会放在young区，冷数据第一次加载先进old区，如果超过n秒二次被访问会被移到young区。
总结来说全表读不会导致内存暴涨，但是会消耗IO，所以要注意避免高峰执行。
客户端接收数据慢，除了IO影响，还可能导致长事务，造成连锁反应，比如占锁和undo log资源不能回收。
34 | 到底可不可以使用join？
straight_join可以指定表连接顺序
被驱动表的关联字段上有索引，叫做Index Nested-Loop Join，简称NLJ，就是循环嵌套遍历
能用NLJ的情况下，如果不join，那就是应用代码里查完表1，手动循环查表2，扫描行数一样，但是和数据库的交互会多很多，还不如数据库内join
NLJ连接查询，驱动表行数是N，被驱动表是M，时间复杂度约等于N + N*2*log2M，因此N的影响更大，所以要用小表做驱动表
如果被驱动表没法用上索引，会变成Simple Nested-Loop Join，就是被驱动表循环全扫，性能很差，但MySQL对此有个优化算法，叫Block Nested-Loop Join，BNL
BNL是用了内存join_buffer，把表1的数据放入，然后读一次表2，在内存中做2层循环遍历，判断次数一样，但扫表行数大大减少，在内存里判断速度明显快得多
扫描行数是M+N，判断次数是M*N，所以哪个作为驱动表对这个地方没影响，但是有另外一个因素，join_buffer不一定能放得下所有数据，它由join_buffer_size设定，默认256k，放不下驱动表的话就会分段。每一个分段，t2就要多读一次表。所以应该让驱动表能够放入join_buffer，还是应该由小表做驱动表。
结论：NLJ的join可以用，BNL避免用，如果要用要注意用小表做驱动表，因为buffer不够的情况非常普遍。可以从explain结果的Extra字段里看有没有出现Block Nested Loop。
小表，不一定是指行数少，在BNL里，因为会根据buffer分段，行数据整个放进buffer里，所以行数据的大小 x 行数，才能用来判断。就是两个表按照各自的条件过滤之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，是“小表”，应该作为驱动表。
35 | join语句怎么优化？
有一个针对回表的优化，叫Multi-Range Read (MRR) 。批量查询的回表，在回表之前，把查到的数据在内存中根据主键排序，再回表查，因为主键大概率是单向递增的，所以排序后回表，有利于磁盘顺序读取，比随机读性能好得多。
注意，现在优化器在判断消耗的时候，会降低使用MRR权重，如果要强制使用，可以set optimizer_switch="mrr_cost_based=off"
基于MRR的理念，在5.6版本，引入了Batched Key Acess （BKA）算法，针对NLJ进行了优化。
NLJ是把驱动表一行行读取，再逐个读被驱动表的。BKA优化，就是把对驱动表的单个读取，变为批量，放到join_buffer中，再一起去读被驱动表。
如果要启用BKA优化，把这个加到SQL前：set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
那BNL如何优化？BNL的影响有三：1. 多次扫描被驱动表，费IO 2.join操作需要M*N次对比，费CPU 3.多次扫被驱动表，可能影响buffer pool，导致热数据被淘汰
一般最简单优化就是被驱动表加索引，可以把BNL转成BKA
如果被驱动表不适合加索引（比如查询频率并不高，表又很大，加索引就浪费空间），可以用临时表，把被驱动表的数据查出来先放临时表，再用临时表去关联，避免大表多次全表扫描。
还有一种思路，hash join，要应用代码里实现，两次分别的单表范围查询，读到应用的内存里，再做匹配，比临时表方案可能还会更快。
36 | 为什么临时表可以重名？
临时表不一定是内存表。
内存表指的是用Memory引擎的表，数据都在内存，重启会清空数据，但表结构还在。
临时表可以用各种引擎，包括InnoDB，MyISAM，是会写磁盘的，也可以用Memory那就是写内存的。
临时表有几个特征：1.建表语法是create temporary table ... 2.临时表只能被创建它的session访问，对其他不可见 3.临时表可与普通表同名 4.同名时，show create语句以及其他增删改查，都是只访问到临时表 5.show tables命令不显示临时表
所以临时表特别适合BNL的join优化，因为可以重名，就不用担心多session对同一个join查询的冲突，还有就是断开session会自动清掉临时表
临时表在分库分表场景下的应用：
分库分表的范围查询排序，需要到所有分区查到所有满足条件的数据，然后放在一起排序。有两种思路。1.排序在proxy层做，优点是内存排序速度快，缺点是对proxy的开发工作量大，需要处理各种场景。而且也比较耗proxy的CPU和内存。 2.在分库中选一个，建临时表，再把所有数据做汇总排序。实现起来简单很多。
为什么临时表可以重名？表结构是放在.frm文件里，文件在临时文件目录里，命名规则是#sql{进程 id}_{线程 id}_ 序列号。表数据的存放是不同于普通表的。5.6以及之前版本，会在临时文件目录下创建临时表文件，5.7开始有专门的临时表空间，也就不用创建ibd文件了。所以临时表既不会和普通表冲突，也可以避免不同会话的同名冲突。
临时表也有写binlog的情况：如果binlog格式不是row，临时表的操作也就会记录binlog。所以这也是row格式的一个好处之一。
37 | 什么时候会使用内部临时表？
union语句取并集时，explain结果extra字段显示using temporary，临时表起到暂存作用，并且用到了主键唯一性约束实现union语义
如果union all的话，就不需要临时表
group by语句也常用临时表，在聚合运算时需要把过程中的结果临时存放，group by的排序通常也会用到filesort，如果不需要排序可以增加order by null
如果临时表空间小，会用内存表，超过tmp_table_size就会转成磁盘表默认innoDB
如果group by的字段有有序索引，就可以顺序遍历，不需要临时表。
5.7引入了generated column 机制，可以实现列数据关联更新，就是指定某一列（持续地）=另一列的运算结果
group by不会估算数据量，而是先放内存临时表，不够了才转磁盘，如果可以预判到数据量大，可以指定直接磁盘：SQL_BIG_RESULT ，而且B+树存储效率，也会直接放sort_buffer
从union,union all , group by过程可以看出，用临时表的判断依据是：
1.如果语句执行可以一边读数据一边得到结果，就不用额外空间（内存、磁盘），否则就需要空间来保存临时结果（比如运算过程数据、排序过程数据）
2.join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构（有索引）
3.如果执行逻辑需要用到表特性，就会优先考虑用临时表，比如union用到了唯一约束、group by用到了新字段来累积计数
小结：1. group不排序的话要额外指定 2.group尽量用索引 3.group数据量不大尽量用内存临时表，可以调tmp_table_size 4.数据量很大可以指定SQL_BIG_RESULT
38 | 都说InnoDB好，那还要不要使用Memory引擎？
innoDB的主键索引是有序的，并且数据直接放在主键索引上。
memory的主键索引是hash，且数据和索引是分开的，数据部分是数组存放，按插入顺序。
InnoDB这种方式叫索引组织表（Index Organizied Table）。Memory叫堆组织表（Heap Organizied Table）
异同点：
1.InnoDB的数据是有序存放的，内存表是按照写入顺序放的
2.数据文件空洞，插入时InnoDB不会理会空洞，Memory会找到空洞就插
3.位置发生变化时，InnoDB只需要修改主键索引，Memory表需要修改所有索引
4.InnoDB用普通索引需要回表，两次索引。Memory所有的索引都不需要。
5.InnoDB支持变长数据类型，不同记录长度可以不同。Memory不支持Blob和Text字段类型，而且即使varchar（N），也是实际用char（N）定长。
内存表也支持B-tree，可以对需要的字段手动创建指定类型的索引。
Memory快，因为支持hash索引，而且更重要的是所有数据在内存，读写速度肯定快于磁盘，但生产环境尽量不要用Memory表，原因是：1.锁粒度 2.数据持久化
Memory表不支持行锁，只能锁全表。并发性能不好。
持久性问题，Memory表只要重启就会清空所有数据。高可用部署时，主备重启理论上不应该影响数据一致性，但用了Memory就会。比如，在备库重启时，备库memory表清空，对主库的update会同步不到备库报错，导致主备同步全部停止。而且如果此时主备切换，客户端就会看到表数据丢失了。
更离谱的是，如果是双M结构，备库重启会清空memory表，并且写一行delete到binlog，传到主库，把主库的表也清空。
所以说不建议生产用memory。InnoDB已经充分优化能够满足大多数情况：1.表更新频繁的话，并发度高，那InnoDB支持行锁并发比Memory好。2.如果是读频繁，那InnoDB Buffer Pool也是内存缓存，能用Memory的情况不能是大表，这样的话也都能放进缓存，命中率高的情况下读也非常快。
适合memory的场景，是临时表。好处有：1.不会被其他线程访问，不用担心并发 2.临时表本来就是重启删除 3.备库临时表不影响主库用户线程
39 | 自增主键为什么不是连续的？
自增主键可以尽量保持顺序插入，且更紧凑，对B-tree更友好。但不是严格连续的，可能会有空洞。
自增值不是和表结构一起存的。MyISAM是保存在数据文件里。InnoDB是保存在内存里，5.7（包括）之前，重启数据库时第一次打开表，会去表里找自增最大值+1作为当前自增值。8.0开始，记录在redo log里，重启时从这里恢复。
自增值修改机制：字段被定义为AUTO_INCREMENT。如果插入时字段=0或null或未指定，就把当前自增值填入该字段；如果指定了值，就会直接用指定值。假设指定的值大于当前自增值，会修改当前自增值为这个值。然后生成新的自增值，以auto_increment_increment （默认为1）为步长累加。
空洞的情况：插入的时候，先拿自增值，然后再插入，可能会遇到别的字段唯一冲突而回滚，但自增值不会退回。其他的事务回滚也是类似的，已经申请了自增值，就算不用，也退回。
如果需要退回，严格保持自增无空洞，设计上会很复杂，而且要么增加判断逻辑，要么扩大锁范围，都会损失性能，得不偿失。
在原来，自增锁的范围是语句级别，要等语句执行结束才释放，并发度不高。从5.1.22开始，新增参数 innodb_autoinc_lock_mode，默认值是1。1.参数0时，跟原来一样，语句级别锁 2.参数1时，普通insert语句下申请了马上释放，insert..select这种批量插入语句等语句结束才释放 3.参数2时，所有语句都是申请后马上释放 。
批量插入的自增值，容易引起日志和数据不一致，所以要么1.锁住自增值直到语句结束 2.不锁语句，但binlog格式为row
从性能考虑，建议innodb_autoinc_lock_mode=2 ，并且 binlog_format=row.（这个row真是好处多多）
普通的insert values多行插入，是可以预估id数量的，所以可以一次批量申请。其他的查表批量插入这种无法预估，会有一个策略，第一次申请1个，第二次2个，第三次4个，一直翻倍，申请到的如果用不上就丢掉了。所以，这种情况也会导致id空洞。
40 | insert语句的锁为什么这么多？
insert..select语句，在可重复读隔离级别下，会给扫描到的记录和间隙加锁。这是为了避免出现别的insert语句并行执行，会导致一致性问题，比如statement格式下binlog和数据不一致。
insert..select语句如果目标表和扫描表是同一个表，会用临时表存放select数据，避免循环读，并且可能出现不必要的全表扫描和所有间隙加锁。可以用用户临时表来优化。
insert语句如果遇到唯一键冲突，会加上next-key lock，需要尽快提交或回滚事务，避免加锁时间过长。
41 | 怎么最快地复制一张表？
表数据复制，如果对源表扫描行数和加锁范围可控，最简单就是insert..select
mysqldump，可以把数据导出为insert语句，然后通过mysql source语句到库上执行，source只是客户端行为，发到mysql的是SQL，slow log和binlog记录的也都insert语句。
outfile语句，可以直接把查询结果导出到服务端本地目录，安全原因，这个目录位置是受限的。文件是.csv，存在服务端，在客户端完全不可见。再用load data infile，导入目标表。
这个导入语句，在binlog里比较特殊，因为备库没有这个文件，所以直接记录语句的话肯定找不到文件。所以，如果binlog是statement格式，会把文件内容整个写到binlog里，然后再记录load data local infile语句。
load data可以从客户端导入，只要加上local就行。
load data不会生成表结构文件。建表语句需要另外处理。
mysqldump也提供了生成csv的操作，-tab参数，可以同时导出表结构文件和数据csv（分开的两份文件）
5.6版本引入了可传输表空间（transportable tablespace），实现物理拷贝。相比于上面的两种逻辑导入，这个会明显快。
方法对比：
1.物理拷贝最快。可用于数据恢复。缺点是：只能全表拷贝，需要到服务器上操作（要有权限），要求源表和目标表都是innoDB。
2.mysqldump可以加where来实现部分导出，但不能join，只能单表where
3.select.. into outfile最灵活，支持所有sql语法。缺点是只能导出一张表，而且表结构也要另外处理。
42 | grant之后要跟着flush privileges吗？
很多文档grant后面都跟着flush privileges
create user语句做了2个动作：1.在磁盘上往mysql.user表插入一行 2.内存里，acl_user里插入acl_user对象
mysql有几种权限范围，最大的是全局权限
例如给ua用户赋予最高权限的语句：grant all privileges on *.* to 'ua'@'%' with grant option;这里做了两个动作：1.磁盘上，mysql.user表里ua@%这一行，权限字段全部改为Y 2.内存里acl_users将该用户的access二进制值全改为1
grant命令完成后立即生效，接下来创建的连接会使用新的权限。对于已经存在的连接，权限没有变。
revoke命令收权限，也是同时操作磁盘和内存，做相反动作
稍微小一点的是db级别的权限，例如grant all privileges on db1.* to 'ua'@'%' with grant option;也是做两个动作：1.磁盘上，mysql.db表插入一行记录，设置权限字段为Y 2.内存里，acl_dbs加入对象设置权限位为1
grant操作对于已存在的连接，在全局和db级有点不同，db的权限可以更快地生效，但会受到use db操作的影响，只要之前处于某个库里，没切之前就会一直拥有旧的权限。
再小一点是表权限和列权限，表权限放在mysql.tables_priv中，列放在mysql.columns_priv中，内存的话是column_pri_hash中，这个是hash表结构。修改权限也是同时操作磁盘表和内存缓存。所以也是立即生效。
综上所有grant修改权限都会立即生效，不需要执行flush privileges
flush privileges命令，对于全局、库、表、字段权限，都会重新清空内存缓存，从数据库里读表重建。但grant/revoke已经包含了更新缓存的动作。
flush privileges命令可以用在不规范的DML操作权限表之后的刷新，但是不建议直接DML权限表，很容易出错。
43 | 要不要使用分区表？
有些公司规范不让使用分区表
一个有n个分区的表，对于server层来说是1个表，对于引擎层来说是n个表，包含1个.frm文件和n个.ibd文件
innoDB间隙锁的加锁范围，不会跨分区，只会在当前分区的末端结束
MyISAM的表锁，也不会跨分区，只锁本分区
如果一个表的分区过多，超过了open_files_limit默认1024，MyISAM打开表就会报错
MyISAM使用的分区策略是通用分区策略（generic partitioning），由server层控制分区访问，这个策略是早期MySQL开始支持分区时实现的，做得比较粗糙，性能也不太好。
从5.7.9开始，innoDB引入本地分区策略（native partitioning），在引擎内部管理分区。
从5.7.17开始，MyISAM分区表被标记为depricated，从8.0开始就不允许使用了。只有InnoDB和NBD这两个引擎支持本地分区策略。
server层看来分区表就是一个表，所以DDL操作影响所有分区，锁住表MDL。例如，truncate一个分区时，所有分区都不可以操作，如果是手动分库分表就不会这样。
分区表的好处：对业务透明，相对手动分表，使用分区表的业务代码更加简洁。并且分区表可以很方便地清理历史数据。alter table t drop partition语句可以直接删除整个分区，速度快，影响小。
分区表除了范围分区（range）还支持hash、list分区，可以具体查手册。
实际使用时，不要创建太多分区，不是越细越好，现在单表千万行性能也没什么影响。分区也不需要提前预留太多，可以用的时候再创建。对于旧数据要及时清理。
有业务分表的中间件，如果有精力维护的话，对业务透明，对DBA直观，是更好的选择。
44 | 答疑文章（三）：说一说这些好问题
left join左边的表不一定是驱动表
一般来说left join左边的表会是驱动表，会放到join_buffer里。
如果where条件里有被驱动表的判断，会破坏left join语义，需要写到on里
join语句的on可能会被替换为where，反正语义是一样的
Simple Nested Loop Join的性能，和BNL的性能会差距很大吗？
因为有buffer pool机制，Simple NLJ也是读到内存里，都是内存判断，性能似乎也不会差距太大？
差别在于Simple会影响正常业务的buffer pool，而且在这里面遍历数据，是指针操作，不如BNL数组遍历来得顺序，所以性能是差一点的，而且影响缓存也不好。
distinct和group by如果只要去重，哪个性能好？
group by如果没有count的话，逻辑上跟distinct是一样的，执行流程也差不多，性能也差不多。我个人认为就算group by更优也没有必要用，因为会破坏可读性，并不优雅。
备库自增主键，statement情况下，如果binlog提交顺序和id获取顺序不同，岂不是id串了？
不会的，就算是statement下，binlog里面也会存真正的id，跟之前提到的now()的替换机制类似，不会出现不一致的问题。
45 | 自增id用完怎么办？
表定义自增值id：会卡在最大值不变，最大值跟字段有关，如果是4字节那就是2^32-1（4294967295），不是很大，如果预估表会很大就要创建长一点的字段。
InnoDB系统自增row_id：如果innoDB表没有主键，innoDB会隐式地创建一个6字节的row_id，由全局的dist_sys.row_id控制自增。代码里是8字节bigint unsigned实现，所以会出现溢出截断，如果超过2^48-1就会从0开始循环。
假如row_id重复，行数据会被覆盖，意味着数据丢失，可靠性问题。比主键冲突这种可用性问题，更加严重。
Xid：redo log 和binlog关联同一事务用的。由全局变量global_query_id控制。是内存变量，重启清零。所以Xid相同不会很罕见。但是MySQL重启binlog也会新建一个文件，所以同一个binlog文件里，Xid唯一。除非一种情况，达到上限后，会从0开始，但是由于长度是8字节，2^64是一个非常大的数，很难达到，而且重启就重置了，所以这个可能性微乎其微，可以忽略。
InnoDB trx_id：这个和Xid很容易混淆，他们都用来标识事务，但Xid是server层的，trx_id是引擎层的。trx_id用来做事务可见性判断，只读事务的话是一个假的唯一值，只有更新事务才会真的生成trx_id。
只读事务的trx_id的生成规则是，trx变量的指针转成整数，再加2^48，保证同一个事务执行期间这个数是不变的，而且不同的只读事务一定是不同的。加上2^48是为了肉眼容易分辨这是一个假的数。他跟真的trx_id冲突的概率很低，就算冲突了也没什么危害，所以不用担心。
只读事务不分配trx_id的好处，可以减少事务视图里活跃事务数组的大小，提升性能。在一个是减少trx_id的申请次数，避免过早用完。
一个非只读事务，trx_id可能会增加好几个值，因为会有一些关联的内部事务。
trx_id最大是2^48，超过会从0开始，但是会导致非常严重的问题，可见性会完全乱套，产生脏读。而且重启mysql也没用，这个值是持久化的。假设mysql实例上的TPS是50w/s，那17.8年就会耗尽。并不难达到，所以这是一个潜在的危险。因为现在mysql还很少达到这么长的运行时间，所以问题没有暴露。
thread_id：线程id，保存在全局变量thread_id_counter里，每新建一个会话，就会增加，大小是4字节，2^32，到了之后从0开始。耗尽问题不大。不太可能冲突。
我的 MySQL 心路历程
这篇文章是专栏的专场直播里的回顾，没有具体的技术要点，主要是分享作者和MySQL打交道的经历和心路历程：
和MySQL打交道的经历
为什么要了解数据库原理
建议的MySQL学习路径
DBA的修炼之道
虽然不是正文，算是番外吧，也挺值得一读，了解作者的心路历程，对比反观自己的职业道路，也会有很多感受。另外也更能坚定自己喜欢技术，追求技术的道理，毕竟职业路漫漫，难免会面临选择和困惑。
结束语 | 点线网面，一起构建MySQL知识网络
跟上篇类似，也是回顾和思考，但主要是总结如何学习mysql。
文章4个要点：
1.路径千万条，实践第一条：实践非常的重要，尤其是对于知识的巩固和融汇贯通，离不开实践
2.原理说不定，双手白费劲：对于原理一定是要是能说明白，那才能是真的掌握，如果遇到别人请教，那是很好的学习机会，千万要抓住机会好好对待
3.知识没体系，转身就忘记：知识要构成网络，相交相映，才能持久地记住，达到融汇贯通举一反三，吸收新知识点的状态
4.手册补全面，案例扫盲点：不要一开始就看官方手册，手册大而全，但是很难形成脉络，自己也无法总结出重点和实践。最佳的路径是先看书（包括本专栏这种），有一定的理解之后，再通过手册查漏补缺，效率最高，也更能深刻掌握。
这几个学习要点，其实是普适的，也是我个人非常推崇的，也是一直照此实践的。作者总结得非常好，引起我强烈的共鸣，非常值得参考，在此推荐大家好好阅读本节。