mysql进阶笔记
说明：此文章并非原创，参考极客时间文章《MySQL实战45讲》做的一些笔记，方便自己查阅，有兴趣可以自行去极客时间阅读，内容非常给力。
mysql引擎
Innodb：
Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型：数据页（B-Tree Node）
Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等；
每个数据页的大小为16kb，每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib)
1）可以通过自动增长列，方法是auto_increment。 2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。 3）使用的锁粒度为行级锁，可以支持更高的并发； 4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。 5）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度； 6）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上； InnoDB的存储表和索引也有下面两种形式： 1：使用共享表空间存储：所有的表和索引存放在同一个表空间中。 2：使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可
以查看我的其他文章。使用分区表的好处在于提升查询效率。
------------------------------------------------------------------------------------------------------------------------------------------------------
MyISAM
MyISAM拥有较高的插入、查询速度，但不支持事物，不支持外键，锁的最小粒度为表锁，不支持行锁。使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件；
（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引
------------------------------------------------------------------------------------------------------------------------------------------------------
Memory
Memory 采用的方案式使用内存当存储介质，优点式响应速度快。但存储到内存上也导致了一个致命的问题就是当mySql进程崩溃的时数据会丢失。此外Memory对存储的数据有一定的要求，要求存储的是长度不变的数据。
Memory索引支持 1）散列索引：散列索引的使用场景是数据查找时使用 == 匹配，但范围查询（<=, >=, <, >）较慢 2）B树索引：B树索引可以使用部分查询和通配查询，范围查询较快 Memory使用场景： 1）数据量小、访问非常频繁、在内存中存放数据，数据量过大会导致内存溢出。可以通过参数max_heap_table_size控制Memory
表的大小，限制Memory表的最大的大小。 2）数据是临时数据，而且立即可用到。那么就比较合适存放在内存中。 3）存储在表中的数据如果丢失也没太大关系，不会造成损失。
mysql数据保存机制
注意：这里讲的是基于Innodb引擎下的情况。
redo log 和 binlog 区别 1：redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 2：redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的
原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。 3：redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定
大小后会切换到下一个，并不会覆盖以前的日志。
两阶段提交图：
先把这次更新写入到redolog中，并设redolog为prepare状态，然后再写入binlog,写完binlog之后再提交事务，并设redolog为commit状态。也就是把relolog拆成了prepare和commit两段！
为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。
不使用两阶段提交的情况下：
1）先写 redo log 后写 binlog
假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即
使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没
有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复
临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。 2）先写 binlog 后写 redo log
如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog
里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就
是 1，与原库的值不同。
mysql崩溃恢复时的判断规则： 1：如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交； 2：如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整： a. 如果是，则提交事务； b. 否则，回滚事务。
redo log 和 binlog 是怎么关联起来的?
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
1) 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
2) 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
一. redo log 日志
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。
innodb事务日志包括 redo log 和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。
undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据
页(恢复数据页，且只能恢复到最后一次提交的位置)。 2.undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。 write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满
了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
写入机制
redo log 可能存在的三种状态说起。这三种状态，对应的就是图 中的三个颜色块。
这三种状态分别是：
-
存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
-
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
-
持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。
日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。
为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值： 1) 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ; 2) 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘； 3) 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache(硬盘缓存区，mysql挂掉数据不会丢失，只有断电时才会丢失)。 InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
----------------------------------------------------------------------------------------------------------------------
实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。
1. 一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，
所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
2. 另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到
buffer 中，这时候有另外一个线程的事务 B 提交，如果innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer
里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。
我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。
如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？ 针对这个问题，可以考虑以下三种方法： 1) 设置 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。
当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 无效。
2) 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。 3) 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
二. binlog日志
redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
binlog 的写入机制
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
write：指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync：才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog 控制的： 1）sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync； 2）sync_binlog=1 的时候，表示每次提交事务都会执行 fsync； 3）sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync 在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。将 sync_binlog 设
置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
事务
事务的四大特性：原子性、一致性、隔离性、持久性
事务隔离级别：（隔离级别越高，并行性能依次降低，安全性依次提高），mysql InnoDB引擎默认隔离级别是可重复读
- 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
- 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
读已提交（不可重复读）：事务启动之后，事务内的查询语句获取到的数据是已经提交的最新数据，可能造成幻读，也就是多次读取到的可能不一致。
可重复读：查询会获取在事务启动前就已经提交完成的数据，不过更新的时候会先获取最新的数据版本号，然后再更新，避免丢失其他事务已经提交的数据，并且更新完成之后，自己成为最新的版本号，所以更新语句之后的查询的数据是更新语句执行完的数据，也就是最新的数据。
幻读：幻读仅专指“新插入的行”，一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
mysql使用中版本快照（事务视图）来实现可重复读：InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1 版本未提交，不可见；
2 版本已提交，但是是在视图创建后提交的，不可见；
3 版本已提交，而且是在视图创建前提交的，可见。
mysql锁
表锁
行锁（Record Lock）
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁，InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
两阶段锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时（提交或回滚）才释放。这个就是两阶段锁协议，所以同一个事务中，将update操作放到最后，减少行锁时间，提高并发。
使用条件：InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁，所以建议所有增删改语句的条件最好使用索引字段，避免行锁转换成表锁。
死锁和死锁检测：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁
如上图（图来自极客时间mysql实战45讲）所示，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。
死锁解决两种策略：一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on（默认值），表示开启这个逻辑。死锁应该尽量避免，不出现则最好。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
InnoDB行锁的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）。
四种锁之间的冲突关系：如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预（隐式加锁），对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁，但是用户可以主动加锁（显式加锁）如下：
共享锁(S)： SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(X)： SELECT * FROM table_name WHERE ... FOR UPDATE
共享锁（读锁）：事务都加，都能读。修改是惟一的（发生覆盖索引的情况下除外），必须等待前一个事务 commit，才可继续执行
排他锁（写锁）：事务之间不允许其它排他锁或共享锁读取，修改更不可能，一次只能有一个排他锁执行 commit 之后，其它事务才可执行
间隙锁（GAP Lock）：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。
和间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，比如在索引字段1和5之间的间隙插入2/3/4。
Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。InnoDB默认加锁方式是next-key 锁。
总结：
可重复读隔离级别行锁的加锁规则：
原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
唯一索引上的范围查询会访问到不满足条件的第一个值为止。
测试数据：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
案例一：等值查询间隙锁 update t set d=d+1 where id = 7 解释：由于表 t 中没有 id=7 的记录。根据原则 1，加锁单位是 next-key lock， 加锁范围就是 (5,10]；同时根据优化 2，这是一个等值查询
(id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此在事务未提交之前加锁的范围是 (5,10)。
---------------------------------------------------------------------------------------------------------------
案例二：非唯一索引等值锁 select id from t where c = 5 lock in share mode 解释：这里要给索引 c 上 c=5 的这一行加上读锁。根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。要注意 c
是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10]
加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。 根据原则 2
只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。
需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来
要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖
索引的优化，在查询字段中加入索引中不存在的字段。例如：
select id，d from t where c = 5 lock in share mode
-----------------------------------------------------------------------------------------------------------------
案例三：主键索引范围锁 select * from t where id=10 for update; select * from t where id>=10 and id<11 for update; 解释：第二句sql开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。根据优化 1， 主键 id 上的等值条件，退化成行锁，
只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。这时候锁的范围就是主键索引上，
行锁 id=10 和 next-key lock(10,15]。所有第一条和第二条的锁是不一样的范围。
------------------------------------------------------------------------------------------------------------------
案例四：非唯一索引范围锁
select * from t where c>=10 and c<11 for update;
解释：这次用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由
于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。
---------------------------------------------------------------------------------------------------------------------
案例五：非唯一索引上存在"等值"的例子
insert into t values(30,10,30);
新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个
c=10 的记 录之间，也是有间隙的。
delete from t where c = 10
解释：这时在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。然后向右查找，
直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间
隙锁。即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。
-----------------------------------------------------------------------------------------------------------------------
案例六：limit 语句加锁
delete from t where c = 10 limit 2
解释：这个例子里的delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。
这是因为delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就
变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。
！这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
索引
索引的分类：
普通索引和唯一索引选择：
按照业务需要，如果某个字段的值是唯一的，比如身份证号码，那么可以使用唯一索引去实现唯一性，也可以在业务代码里面做到唯一性。不过阿里的java开发手册泰山版里面提到 “即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生”。
对于唯一索引来说，需要将数据页读入内存（所以不需要使用change buffer），判断到没有冲突，然后再插入值；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。当需要更新一个数据页时，
如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。change buffer是可以持久化的数据，也就是说，change buffer 在内存中有拷贝，
也会被写入到磁盘上。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置
总结：对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge（持久化） 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。
marge：change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
字符串加索引
-
直接创建完整索引，这样可能比较占用空间；
-
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
-
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
-
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
索引触发和失效
函数中使用：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
隐式类型转换：索引字段是字符串类型，传入进去的值是int，就会对索引字段做函数操作，将字符串转成int再去判断，优化器会放弃走树搜索功能。但是如果字段是int，传入的值是字符串，则不受影响，因为直接把传入的字段转成int即可，不需要多次转换。
字符集不同：utf8的字段在utf8mb4表中无法使用索引，会先进行字符集转换，然后再比较值。
SQL优化
explain语句详解：
id：id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select type：select type表示对应行是简单还是复杂的查询。
simple：简单查询。查询不包含子查询和union。
primary：复杂查询中最外层的select
subquery：包含在select中的子查询（不在from子句中）
derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
union：在union关键字随后的selelct。
table：这一列表示explain的一行正在访问哪个表。
当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。
当有union时，UNION RESULT的table列的值为<union 1,2>，1和2表示参与union的select行id。
type：这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行对应的大概范围。
依次从最优到最差的分别为：system>const>eq_ref>ref>range>index>All
一般来说，得保证查询达到range级别，最好达到ref。
为null时，MySQL能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需在执行时访问表。
const，system：
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。用于primay key或unique key的所有列与常数 比较时，所以表最多有一个匹配行，读取1次，速读较快。system 是const的特例，表中只有一行元素匹配时为system。 EXPLAIN select * from film where id= 1 ; eq_ref：
primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这是const之外最好的联接类型，简单的select查询不会出现这种type。 ref：
相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 简单select查询，name是普通索引（非主键索引或唯一索引） EXPLAIN select * from film where name='film1'; range：
range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于 索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描 index：
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且 index要先读索引找到主键id，然后回表读取数据。 all：
即全表扫描，意味着MySQL需要从头到尾去查找所需要的行。这种情况下需要增加索引来进行优化。
possible_keys：这一列显示select可能会使用哪些索引来查找。
explain时可能会出现possible_keys有列，而key显示为NULL的情况，这种情况是因为表中的数据不多，MySQL认为索引对此查询帮助不大，选择了全表扫描。
如果该列为NULL，则没有相关的索引。这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果
key：这一列显示MySQL实际采用哪个索引对该表的访问。
如果没有使用索引，则改列为NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、 ignore index。
key_len：这一列显示了mysql在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。
ken_len计算规则如下：
字符串：char(n)：n字节长度，varchar(n)：n字节存储字符串长度，如果是utf-8， 则长度是3n+2
数值类型：tinyint：1字节，smallint：2字节，int：4字节，bigint：8字节
时间类型：date：3字节，timestamp：4字节，datetime：8字节
如果字段允许为NULL，需要1字节记录是否为NULL
索引最大长度是768字节，当字符串过长时，MySQL会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引
ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const(常量)，字段名等。
一般是查询条件或关联条件中等号右边的值，如果是常量那么ref列是const，非常量的话ref列就是字段名。
rows：这一列是mysql估计要读取并检测的行数，注意这个不是结果集的行数
Extra：这一列是额外信息
Using index：使用覆盖索引，结果集的字段是索引，索引除了有本索引包含的字段，还有主键id（主键索引）的记录。
explain select id from film_actor where film_id=1;
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导的范围
explain select * from film_actor where film_id > 1;
Using where：使用where语句来处理结果，查询的列未被索引覆盖
explain select * from actor where name ='a'
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化，首先要想到是索引优化。
sing filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
explain select * from actor order by name;
select tables optimized away：使用某些聚合函数（比如：max、min）来访问存在索引的某个字段
select min(id) from film ;
count函数：
在不同的 MySQL 引擎中，count(*) 有不同的实现方式。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。所以，count(*)，
count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
mysql对count(*)做了专门的优化，不取值，不判空。所以按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)
order by语句
1)全字段排序：
mysql的排序有可能在内存，也有可能在外部，取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。
如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
select city,name,age from t where city='杭州' order by name limit 1000 ;
可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;
这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看到是否使用了临时文件。
number_of_tmp_files 表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要多个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。
可以这么简单理解，MySQL 将需要排序的数据分成n 份，每一份单独排序后存在这些临时文件中。然后把这 n 个有序文件再合并成一个有序的大文件。如果 sort_buffer_size超过了
需要排序的数据量的大小，number_of_tmp_files就是 0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。
2)rowid 排序：
查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。max_length_for_sort_data，是
MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）
和主键 id。但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了。 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果。
order by 优化
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据，所以可以利用覆盖索引这一特性，将需要返回的字段创建一个联合索引，这样可以避免查询回表。 并且因为索引本身就已经排序了，这样就不用再次排序，直接返回就好了。不过这种办法只适合字段较少的场景。
例如：alter table t add index city_user_age(city, name, age); 索引排序：除了索引覆盖，还可以利用索引本身就有序，将排序的字段设置成索引，这样就可以避免再次排序。需要注意的是，如果order by 的
字段很多，需要保证全部字段都加了索引，并且统一升序或降序，否则失效
举例：SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描。
2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。
3)bigint和int加数字都不影响能存储的值。
bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是2^32-1。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。
目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节。
group by
-
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
-
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
-
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
-
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
根据计算的条件去创建索引：如果group by 的字段是计算出来的字段，那么将无法利用索引排序。
解决办法：使用 generated column 创建一计算之后的列，然后在计算后的列上加入索引。
例：alter table t1 add column z int generated always as(id % 100), add index(z);
直接使用：select z, count(*) as c from t1 group by z;
group by 优化方法 -- 直接排序 一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”。 所以我们可以直接走磁盘临时表。 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。
MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。所以最终是直接输出排序结果，不需要使用临时表。 使用：select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
!= 和not in
尽量避免使用因为条件范围过大会导致数据库引擎放弃索引进行全表扫描。
SELECT * FROM t WHERE id IN (2,3) 索引有效
SELECT * FROM t1 WHERE name NOT IN (SELECT username FROM t2) 索引失效
select * from t where status!=0 and stauts!=1 索引失效
or：
当or两边的字段都有索引时，索引生效，任一字段无索引，索引失效，走全文搜索。此时可以使用 UNION 代替，这样有索引的字段触发索引，无索引的字段则走全文搜索。
例： SELECT * FROM broadcast WHERE id =100 OR money = '2000' 改成 SELECT * FROM broadcast WHERE id =100 UNION SELECT * FROM broadcast WHERE money = '2000'
limit：
确定搜索一条记录的时候，加上limit 1 ，以此让游标查到第一条结果时停止，不需要遍历下面的结果。
A：SELECT * FROM broadcast WHERE request_id = '1587458829595059d3ce6cfcf4b7f8'
B：SELECT * FROM broadcast WHERE request_id = '1587458829595059d3ce6cfcf4b7f8' limit 1
实测（表数据60万）不建立索引情况下：A：0.233s B：0.038s
可以看到相差还是很明显的。如果request_id建立了索引，那么A和B基本上是没什么区别。所以在一些比较复杂的查询，并且知道返回的只有一条的情况下加一个limit可以避免继续搜索后面的数据。
limit覆盖索引
A：SELECT * FROM broadcast ORDER BY id LIMIT 500000,100 B：SELECT * FROM broadcast WHERE id >= (SELECT id FROM broadcast ORDER BY id LIMIT 500000,1) limit 100 也可以用join代替 >=
SELECT * FROM broadcast a JOIN (select id from broadcast ORDER BY id limit 500000, 100) b ON a.id = b.id;
使用覆盖索引先查出数据是50000的id，然后直接在id的范围内查询数据，所以不需要遍历前面的50000条数据
A：0.2s B：0.114s
join
能不能使用 join 语句？
join算法：
- Simple Nested-Loop Join：SNLJ，简单嵌套循环连接
简单嵌套循环连接实际上就是简单粗暴的嵌套循环，如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢，mysql
已经不在使用。使用BNLJ算法代替
- Index Nested-Loop Join：INLJ，索引嵌套循环连接
索引嵌套循环连接是基于索引进行连接的算法，索引是基于被驱动表的，通过驱动表匹配条件直接与被驱动表索引进行匹配，避免和被驱动表的每条记录
进行比较， 从而利用索引的查询减少了对被驱动表的匹配次数，优势极大的提升了 join的性能
原来的匹配次数 = 外层表行数 * 内层表行数
优化后的匹配次数= 外层表的行数 * （内层表索引的高度+索引回表查询）
- Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接
缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减
少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。
- Batched Key Access算法：BKA，对Nested-Loop类型算法的优化
----------------------------------------------------------------------------------------------------------------------------------------------
在选择Join算法时，会有优先级，理论上会优先判断能否使用INLJ、BNLJ：
Batched Key Access > Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
1: 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，可以使用；
2: 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
-----------------------------------------------------------------------------------------------------------------------------------------------
如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？（其实优化器会帮我们选择合适的表做驱动表，但不排除翻车的可能性，使用straight_join代替join让优化器按照我们写的顺序去执行join）
如果是 Index Nested-Loop Join（可以使用被驱动表的索引)算法，应该选择小表做驱动表；
如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
如果join_buffer 不够大，需要对被驱动表做多次全表扫描，也就造成了“长事务”。被驱动表就被多次读，而被驱动表又是大表，循环读取的间隔肯定得超1秒，就会导致“数据页在LRU_old的存在时间超过1秒，就会移到young区”。
最终结果就是把大部分热点数据都淘汰了，导致“Buffer pool hit rate”命中率极低，其他请求需要读磁盘，因此系统响应变慢，大部分请求阻塞。
总结：不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用,使用也要用小表做驱动表。
所以，总是应该使用小表做驱动表。在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
join优化总结：
1：用小结果集驱动大结果集，减少外层循环的数据量：
2：如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
为匹配的条件增加索引：争取使用INLJ，减少内层表的循环次数
3：增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么外层表循环的次数就越少
4：减少不必要的字段查询：
（1）当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；
（2）当用到INLJ时，如果可以不回表查询，即利用到覆盖索引
5：Multi-Range Read（MRR） 优化：
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。MRR 能够提升性能的核心在于，查询语句在索引上做的一个范围查询（也就是说，这是一
个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
执行流程：根据索引 a 定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ，将 read_rnd_buffer 中的 id 进行递增排序，排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。
需要注意的是：查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"
6：Batched Key Access(BKA)算法
mySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ 算法的优化。 NLJ 算法执行的逻辑是从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来
说，每次都是匹配一个值，这时，MRR 的优势就用不上了。既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存也是 join_buffer。
如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置，并且被驱动表需要索引。（前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。）
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
BNL 转 BKA
一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。但是，有时候你确实会碰到一些不适合在被驱动表上建索引的情况。比如下面这个语句： select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000； 表 t2 中插入了 100 万行数据，但是经过 where 条件过滤后，需要参与 join 的只有 2000 行数据。如果这条语句同时是一个低频的 SQL 语句，那么再为这个语句在表 t2 的字段 b 上创建一个索引就很浪费了 sql执行流程： 1：把表 t1 的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。 2：扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比， 3：如果不满足 t1.b=t2.b，则跳过；如果满足 t1.b=t2.b, 再判断其他条件，也就是是否满足 t2.b 处于 [1,2000] 的条件，如果是，就作为结果集的一部分返回，否则跳过。 判断 join 是否满足的时候，都需要遍历 join_buffer 中的所有行。因此判断等值条件的次数是 1000*100 万 =10 亿次 explain 结果里 Extra 字段显示使用了 BNL 算法，整体sql语句的执行时间超过了1分钟。 这时候，我们可以考虑使用临时表。使用临时表的大致思路是： 1：把表 t2 中满足条件的数据放在临时表 temp_t 中； 2：为了让 join 使用 BKA 算法，给临时表 temp_t 的字段 b 加上索引； 3：让表 t1 和 temp_t 做 join 操作。 create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
