本文将着重分析一下常见CRUD 与 DDL 的执行逻辑。主要是对一些参考文章的整理，方便自己理解。
1 select simple
主要涉及到对索引的利用，可以在后面再分析。
2 select join
参考 time.geekbang.org/column/arti…
2.1 可以使用被驱动表的索引：（ Index nested-loop join）
2.1.1 步骤
1 对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；
2 而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；所以，整个执行流程，总扫描行数是 200。
2.1.2 小结
1、使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
2、如果使用 join 语句的话，需要让小表做驱动表。
2.2 不能使用被驱动表的索引：（Simple Nested-Loop Join 或者“Block Nested-Loop Join）
2.2.1 步骤
t1 是驱动表，t2被驱动表
（1）把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
（2）扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。
2.2.2 小结
应该让小表当驱动表。
如果小表在 join_buffer 中 放不下，则需要在内存中分段存储了。
2.3 优化
time.geekbang.org/column/arti…
2.3.1 针对 Index nested-loop join(NLJ) 的优化-- BKA 算法
原来 NLJ 从驱动表中每次取一行，然后去被驱动表匹配；
而 BKA 算法是 一次性从驱动表中取多行数据，然后放入临时内存 join_buffer 中排序，然后再一起传给被驱动表。排序的目的是可以利用顺序性，从被驱动表拿数据。
2.3.2 BNL 算法的优化
1 BNL 对系统的三个影响
（1）多次扫描被驱动表
（2）join 比较次数多，如果是大表就会占用非常多的 CPU 资源
（3）Buffer pool 的热数据被淘汰，影响内存命中率。
2 常见优化
2.2.1 常见做法：给被驱动表的 join 字段加上索引，把 BNL 转换为 BKA 算法。
2.2.2 如果不能加索引，就采用临时表的做法：
将被驱动表的数据放入临时表，并且给临时表增加 join 字段的索引。
BKA 算法流程
3 select limit
第7步：SELECT 查询列表
第1步：FROM 表名
第2步：JOIN 关联表
第3步：ON 关联条件
第4步：WHERE 筛选条件
第5步：GROUP BY 分组字段
第6步：HAVING 分组后筛选条件
第8步：ORDER BY 排序字段
第9步：LIMIT 输出限制
4 select group by / order by
1、www.cnblogs.com/yayazhang22…
Group By 和 Having, Where ,Order by这些关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。
select语句的执行顺序为：
1、加载表数据到内存，没有索引时，会将表中所有的数据都加载到内存；有索引时，则根据索引和where条件有针对性的取出符合条件的数据，再加载到内存
2、无索引时，执行where条件，留下符合条件的内容，删除其它不符合条件的
3、执行group by子句，使用内存中的一块区域，专门用于分组，留下select指定的需要的列内容和新产生的聚合列的内容，其它删除
4、执行having子句，对group by的结果进行条件过滤
5、执行order by子句
6、执行limit子句
根据以上的执行顺序，个人理解为：
select语句在取数据的时候，一开始是取整行数据的，然后在group by的时候会剔除其它未被选中的列，你的语句没有用到group by，所以没有剔除，最后再用order by排序的时候，num列还是存在的，所以可以排列；只是最后显示给你看的时候才把多余列剔除掉。
5 insert
5.1 insert 的大体流程 mysql.taobao.org/monthly/201…
1、给表加 IX 锁
2、插入记录
（1）获取 row_id 的索引
（2）遍历所有索引，向每个索引中插入记录
(2.1) 主键冲突检查
（2.2）插入索引。 如果正在 ONLINE_DDL ，则先记录 insert，待 online_ddl 结束的时候再执行 insert.
5.2 insert 过程中的锁
www.aneasystone.com/archives/20…
我们看 insert 语句的调用堆栈，一开始时还比较容易理解，从 mysql_parse -> mysql_execute_command -> mysql_insert -> write_record -> handler::ha_write_row -> innobase::write_row -> row_insert_for_mysql，这里就进入 InnoDb 引擎了。
然后继续往下跟：row_ins_step -> row_ins -> row_ins_index_entry_step -> row_ins_index_entry -> row_ins_clust_index_entry -> row_ins_clust_index_entry_low -> btr_cur_optimistic_insert -> btr_cur_ins_lock_and_undo -> lock_rec_insert_check_and_lock。
InnoDb 在插入记录时，是不加锁的。如果事务 A 插入记录且未提交，这时事务 B 尝试对这条记录加锁，事务 B 会先去判断记录上保存的事务 id 是否活跃，如果活跃的话，那么就帮助事务 A 去建立一个锁对象，然后自身进入等待事务 A 状态，这就是所谓的隐式锁转换为显式锁。
执行 insert 语句，对要操作的页加 RW-X-LATCH，然后判断是否有和插入意向锁冲突的锁，如果有，加插入意向锁，进入锁等待；如果没有，直接写数据，不加任何锁，结束后释放 RW-X-LATCH；
执行 select ... lock in share mode 语句，对要操作的页加 RW-S-LATCH，如果页面上存在 RW-X-LATCH 会被阻塞，没有的话则判断记录上是否存在活跃的事务，如果存在，则为 insert 事务创建一个排他记录锁，并将自己加入到锁等待队列，最后也会释放 RW-S-LATCH；
6 delete
6.1 mysql 删除其实是个假删除
cloud.tencent.com/developer/a…
只会置一个删除标识，并不会真实的删除空间。空间是会进行复用的。
删除记录的行 空间可以被复用，叫做行记录复用；
还有一种是数据页复用，是指整个数据页都被标记删除了，然后这个页被复用了。
6.2 mysql 写操作过程
读操作的流程图：
6.3 为什么表数据删掉一半，表文件大小不变？time.geekbang.org/column/arti…
delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
7 select 与 update 的执行流程
redo log, undo log, bin log 的写入顺序。
8 DDL (增加索引、增加字段、删除字段、增加多个字段)
8.1 ONLINE DDL 的整体介绍
1 ONLINE DDL 虽然可以支持 并发DML，但是也会有短暂的锁表操作。
无论任何模式下，Online DDL开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会首先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting meta data lock。同样在DDL结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行，否则一样出现连环锁表。
2 Online DDL 存在的问题：
主从复制延迟，只有主库上DDL执行成功才会写入到binlog中，而DDL操作在从库上不能并发执行，因此即使主库执行DDL时允许并发DML操作，对于大表操作，仍会引发严重的复制延迟。
主库执行Online DDL时，不能根据负载暂停DDL操作。
使用Inplace方式执行的DDL，发生错误或被KILL时，需要一定时间的回滚期，执行时间越长，回滚时间越长。
使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止。
Online DDL并不是所有时间段的Online，在特定时间段需要加元数据锁或其他锁。
允许并发DML的DDL，可能会导致Duplicate entry问题。
3 建议
执行DDL前查看下该表有没有被事务占用，防止出现MDL锁。
执行DDL前确保datadir，tmpdir磁盘空间足够。
能业务低峰期操作的DDL，都尽量安排在业务低峰期进行。
对于大表和较大表，如果对复制延迟和主库性能敏感，建议改为gh-ost或pt-osc工具。
对于并发操作较高的表，无论表数据量多少，不能在业务高峰期操作。
同个表的多个DDL语句可以合并在一起进行，避免多次table rebuild带来的消耗。但是也要注意分组，比如需要copy table和只需inplace就能完成的，应该分两个alter语句。
8.2不要在业务期间进行DDL操作
1 MDL 锁
MDL锁，全称为metadata lock，即元数据锁。MDL锁的主要作用是维护表元数据的数据一致性，当表上有活动事务（注意MDL锁伴随事务提交而释放，而不是SQL结束而释放）的时候，不可以对元数据（即表结构）进行任何修改操作。
2 MDL 锁 队列机制，先到先得
MDL锁申请遵循一个队列机制，即先到先得，因此如果一个DDL操作一直无法得到MDL X锁，那么后续所有该表上的SQL都会等待这个DDL操作拿到MDL X锁并且释放为止，这也是为啥我们经常听到不要在业务期间进行DDL操作的原因之一，DDL操作很容易因为某个慢SQL导致后续所有的SQL都被卡住（等待MDL锁）
8.3 Online DDL
1、DDL 的问题
copy：创建新表结构的临时表，锁原表禁止DML，将数据copy到临时表，完成后删除原表，重命名新表，需要拷贝原始表，执行过程中源表不允许写但可读。
inplace：在进行DDL操作时，MDL写锁会降级为MDL读锁，这样就可以支持并发DML，然后通过row_log记录原表上的DML增量操作，最后通过回放增量数据保证数据一致性。
rebuild table：部分DDL操作类型在inplace模式下，需要进行重建表（原表基础上进行更新），往往表越大越费时。
no rebuild table：部分DDL操作类型在inplace模式下，不需要重建表，往往只需要修改元数据，因此速度比较快。
instant：从 MySQL 8.0.12 才开始引入，加列操作可以不需要重建表，只需要修改元数据，可以实现秒加列。
2 Online DDL过程
PREPARE：会申请MDL X锁，然后更新数据字典并分配row_log开始记录表上的DML增量数据，这个过程如果没有被MDL锁阻塞，那么是非常快的。
DDL：将所持有的MDL X锁降级为MDL S锁。
一方面进行数据拷贝，比如建二级索引或者重建原表。
另一方面记录这期间产生的DML日志，写到row_log中后进行数据重放。
COMMIT：等到重放至最后一个block时，从MDL S锁升级到MDL X锁，回放最后一个block，最后更新数据字典，完成DDL，释放MDL锁。
8.3 MySQL Online DDL的改进与应用 www.cnblogs.com/xinysu/p/67… (写的非常棒)
1 旧的 DDL 方式
COPY 方式：copy 整个表，copy 期间不可以 DML
inplace 方式：不需要 copy 整个表格，只需要在原来的 ibd 文件上，新建所需要的索引页，减少了 IO。 但是只支持索引的创建与删除，不支持其他 DDL。
2 Online DDL
语句类型划分：
3 Online DDL 优势
Online DDL可以有效改善DDL期间对数据库的影响：
（1）Online DDL期间，查询和DML操作在多数情况下可以正常执行，对表格的锁时间也会大大减少，尽可能的保证数据库的可扩展性；
（2）允许 in-place 操作的 DDL，避免重建表格占用过多磁盘IO及CPU资源，减少对数据库的整体负荷，使得在DDL期间，能够维持数据库的高性能及高吞吐量；
（3）允许 in-place 操作的 DDL，比需要COPY到临时文件的操作要更少占用buffer pool，避免以往DDL过程中性能的临时下降，因为以前需要拷贝数据到临时表，这个过程会占用到buffer pool ，导致内存中的部分频繁访问的数据会被清理出去。
4 MySQL InnoDB Online DDL学习
(4.1) 5.6.7 之前版本的 inplace 用途及弊端：
INPLACE方式：
INPLACE方式也称为InnoDB fast index creation，是MySQL5.5及之后版本为了提高创建二级索引效率的方式，所以INPLACE方式仅限于二级索引的创建跟删除
相对于COPY方式，INPLACE方式在原表上进行，不会生成临时表，也不会拷贝原表数据，减少了很多系统I/O资源占用，但还是无法进行DML操作，也只适用于索引的创建与删除，并不适用于其他类型的DDL语句。
