重建表
alter table t engine = InnoDB（也就是recreate），而optimize table t 等于recreate+analyze，让表大小变小
重建表的执行流程
- 建立一个临时文件，扫描表 t 主键的所有数据页；
- 用数据页中表 t 的记录生成 B+ 树，存储到临时文件中；
- 生成临时文件的过程中，将所有对 t 的操作记录在一个日志文件（row log）中；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 t相同的数据文件；
- 用临时文件替换表 t 的数据文件。
注意：在重建表的时候， InnoDB不会把整张表占满， 每个页留了1/16给后续的更新用。 也就是说， 其实重建表之后不是“最”紧凑的。
Online DDL
ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;
LOCK选项
- SHARE：共享锁，执行DDL的表可以读，但是不可以写。
- NONE：没有任何限制，执行DDL的表可读可写。
- EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
- DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT，那就是交给MySQL子句去觉得锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。
准备：
1、对表加元数据共享升级锁，并升级为排他锁；（此时DML不能并行）
2、判断使用inplace算法
3、判断语句是“rebuild table” 还是 “no-rebuild”，rebuild 在原表所在的路径下创建.frm和.ibd临时中转文件；
【在引擎层克隆，而不是像copy那样，在server层创建（create like）】
注意：加列的操作是需要rebuild table的
【rebuild table】---生成中转文件表
++++
no-rebuild的情况：
除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，
但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中
++++
4、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）【在innodb_sort_buffer块中】
执行：（online）
1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行）
2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；【在引擎层扫描，最耗时】
3、将所有对原表的DML操作记录在日志文件row log中，并回放部分row_log。
提交阶段：
1、升级元数据共享升级锁，产生排他锁锁表；（此时DML不能并行了）
2、重做row log中的内容；（no-rebuild不需要）
3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件；
4、提交事务，变更完成。
在 InnoDB 引擎中 count(*)、count(1)、count(主键)、count(字段) 哪个性能最高？
count(字段)<count(主键 id)<count(1)≈count(*) 题目解析：
- 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
- 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
- 对于 count(字段) 来说，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
- 对于 count(*) 来说，并不会把全部字段取出来，而是专门做了优化，不取值，直接按行累加。
所以最后得出的结果是：count(字段)<count(主键 id)<count(1)≈count(*)。
–single-transaction逻辑备份
当mysqldump使用参数–single-transaction的时候， 导数据之前就会启动一个事务， 来确保拿到一致性视图。
single-transaction方法只适用于所有的表使用事务引擎的库。 如果有的表使用了不支持事务的引擎， 那么备份就只能通过FTWRL（全局锁）方法。
这往往是DBA要求业务开发人员使用InnoDB替代MyISAM的原因之一。
当备库用–single-transaction做逻辑备份的时候， 如果从主库的binlog传来一个DDL语句会怎么样？
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */1. 如果在Q4语句执行之前到达， 现象： 没有影响， 备份拿到的是DDL后的表结构。
2. 如果在“时刻 2”到达， 则表结构被改过， Q5执行的时候， 报 Table definition has changed,please retrytransaction， 现象： mysqldump终止；
3. 如果在“时刻2”和“时刻3”之间到达， mysqldump占着t1的MDL读锁， binlog被阻塞， 现象：主从延迟， 直到Q6执行完成。
4. 从“时刻4”开始， mysqldump释放了MDL读锁， 现象： 没有影响， 备份拿到的是DDL前的表结构。
删除一个表里面的前10000行数据
要删除一个表里面的前10000行数据， 有以下三种方法可以做到：
第一种， 直接执行delete from Tlimit 10000;
第二种， 在一个连接中循环执行20次 delete from Tlimit 500;
第三种， 在20个连接中同时执行delete from Tlimit 500。
分析：
第一种方式（即： 直接执行delete from Tlimit 10000） 里面， 单个语句占用时间长， 锁的时间也比较长； 而且大事务还会导致主从延迟。
第二种方式是相对较好的。
第三种方式（即： 在20个连接中同时执行delete from Tlimit 500） ， 会人为造成锁冲突。
change buffer
当需要更新一个数据页时， 如果数据页在内存中就直接更新， 而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中， 这样就不需要从磁盘中读入这个数据页了。 在下次查询需要访问这个数据页的时候， 将数据页读入内存， 然后执行change buffer中与这个页有关的操作。 通过这种方式就能保证这个数据逻辑的正确性。将change buffer中的操作应用到原数据页， 得到最新结果的过程称为merge。 除了访问这个数据页会触发merge外， 系统有后台线程会定期merge。 在数据库正常关闭（shutdown） 的过程中，也会执行merge操作。对于唯一索引来说， 所有的更新操作都要先判断这个操作是否违反唯一性约束。 比如， 要插入(4,400)这个记录， 就要先判断现在表中是否已经存在k=4的记录， 而这必须要将数据页读入内存才能判断。 如果都已经读入到内存了， 那直接更新内存会更快， 就没必要使用change buffer了。
因此， 唯一索引的更新就不能使用change buffer， 实际上也只有普通索引可以使用。change buffer用的是buffer pool里的内存， 因此不能无限增大。 change buffer的大小， 可以通
过参数innodb_change_buffer_max_size来动态设置。 这个参数设置为50的时候， 表示changebuffer的大小最多只能占用buffer pool的50%。将数据从磁盘读入内存涉及随机IO的访问， 是数据库里面成本最高的操作之一。 change buffer因为减少了随机磁盘访问， 所以对更新性能的提升是会很明显的。
merge的执行流程是这样的：
1. 从磁盘读入数据页到内存（老版本的数据页） ；
2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个） ， 依次应用， 得到新版数据页；
3. 写redo log。 这个redo log包含了数据的变更和change buffer的变更。
redo log 主要节省的是随机写磁盘的IO消耗（ 转成顺序写） ， 而change buffer主要节省的则是随机读磁盘的IO消耗。
字符集不同连表查询用不上索引
performance_schema库优化
MySQL 5.6版本以后提供的performance_schema库， 就在file_summary_by_event_name
表里统计了每次IO请求的时间
Buffer Pool
Buffer Pool对查询的加速效果， 依赖于一个重要的指标， 即： 内存命中率。
你可以在show engine innodb status结果中， 查看一个系统当前的BP命中率。 一般情况下， 一个稳定服务的线上系统， 要保证响应时间符合要求的话， 内存命中率要在99%以上。
执行show engine innodb status ， 可以看到“Buffer pool hit rate”字样， 显示的就是当前的命中率。
InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的， 一般建议设置成可用物理内存的60%~80%。
所以， innodb_buffer_pool_size小于磁盘的数据量是很常见的。 如果一个 Buffer Pool满了， 而又要从磁盘读入一个数据页， 那肯定是要淘汰一个旧数据页的。
InnoDB管理Buffer Pool的LRU算法， 是用链表来实现的。
1. 在图的状态1里， 链表头部是P1， 表示P1是最近刚刚被访问过的数据页； 假设内存里只能放下这么多数据页；
2. 这时候有一个读请求访问P3， 因此变成状态2， P3被移到最前面；
3. 状态3表示， 这次访问的数据页是不存在于链表中的， 所以需要在Buffer Pool中新申请一个
数据页Px， 加到链表头部。 但是由于内存已经满了， 不能申请新的内存。 于是， 会清空链表
末尾Pm这个数据页的内存， 存入Px的内容， 然后放到链表头部。
4. 从效果上看， 就是最久没有被访问的数据页Pm， 被淘汰了。
实际上， InnoDB对LRU算法做了改进
在InnoDB实现上， 按照5:3的比例把整个LRU链表分成了young区域和old区域。 图中LRU_old指向的就是old区域的第一个位置， 是整个链表的5/8处。 也就是说， 靠近链表头部的5/8是young区域， 靠近链表尾部的3/8是old区域。改进后的LRU算法执行流程变成了下面这样。
1. 图7中状态1， 要访问数据页P3， 由于P3在young区域， 因此和优化前的LRU算法一样， 将其移到链表头部， 变成状态2。
2. 之后要访问一个新的不存在于当前链表的数据页， 这时候依然是淘汰掉数据页Pm， 但是新插入的数据页Px， 是放在LRU_old处。
3. 处于old区域的数据页， 每次被访问的时候都要做下面这个判断：
若这个数据页在LRU链表中存在的时间超过了1秒， 就把它移动到链表头部；
如果这个数据页在LRU链表中存在的时间短于1秒， 位置保持不变。 1秒这个时间， 是由参数innodb_old_blocks_time控制的。 其默认值是1000， 单位毫秒。
MySQL采用的是边算边发的逻辑， 因此对于数据量很大的查询结果来说， 不会在server端保存完整的结果集。 所以， 如果客户端读结果不及时， 会堵住MySQL的查询过程， 但是不会把内
存打爆。而对于InnoDB引擎内部， 由于有淘汰策略， 大查询也不会导致内存暴涨。 并且， 由于InnoDB对LRU算法做了改进， 冷数据的全表扫描， 对Buffer Pool的影响也能做到可控。
InnoDB对Bufffer Pool的LRU算法做了优化，
即： 第一次从磁盘读入内存的数据页， 会先放在old区域。 如果1秒之后这个数据页不再被访问了， 就不会被移动到LRU链表头部， 这样对Buffer Pool的命中率影响就不大。
但是， 如果一个使用BNL算法的join语句（无索引join,参考https://www.cnblogs.com/booksea/p/17380941.html）， 多次扫描一个冷表， 而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候， 把冷表的数据页移到LRU链表头部。
这种情况对应的， 是冷表的数据量小于整个Buffer Pool的3/8， 能够完全放入old区域的情况。如果这个冷表很大， 就会出现另外一种情况： 业务正常访问的数据页， 没有机会进入young区
域。由于优化机制的存在， 一个正常访问的数据页， 要进入young区域， 需要隔1秒后再次被访问到。 但是， 由于我们的join语句在循环读磁盘和淘汰内存页， 进入old区域的数据页， 很可能在1秒之内就被淘汰了。 这样， 就会导致这个MySQL实例的Buffer Pool在这段时间内， young区域的数据页没有被合理地淘汰。也就是说， 这两种情况都会影响Buffer Pool的正常运作。
大表join操作虽然对IO有影响， 但是在语句执行结束后， 对IO的影响也就结束了。 但是，对Buffer Pool的影响就是持续性的， 需要依靠后续的查询请求慢慢恢复内存命中率。
判断要不要使用join语句时， 就是看explain结果里面， Extra字段里面有没有出现“BlockNested Loop”字样。