最近上完了极客时间的MySQL的课程，收获很多！课程链接：https://time.geekbang.org/column/intro/100020801

为了让自己更好的梳理知识点，这里总结了这门课所学到的内容，希望对大家也有帮助。

MySQL 总结

1. MySQL的目的

MySQL是一个数据库软件，主要目的是为了实现数据的CRUD，同时需要支持一些其它的业务场景。MySQL的原理用了不同的软件设计思路，这些思路的主要目的是为了支撑不同的用户场景和需求。 下面先总结用户场景，再总结设计思路和原理。 最后在考虑实际操作中可能存在的优化手段。 

2. 用户场景

1. 单thread crud

2. Concurrent threads crud

3. 表结构改变

4. 主从架构

5. 数据库备份

3. 数据库设计原理

数据库为了满足这些用户场景，主要使用了三种基本设计思路： 

1） 索引（加快查询）

2） 锁（处理concurrent threads crud)

3） WAL结构（redo log + bin log) 

3.1 数据库一个query的流程

1） 客户端：发起链接，发出请求

2） 连接器：管理链接，权限验证

3）分析器：分析语法

4）缓存：如果分析器判断缓存里有查询的数据，直接进入缓存提取数据

5）优化器：执行计划生成，索引选择

6）执行器：操作引擎，返回结果

7）存储引擎：存储数据，提供读写接口.存储引擎有innodb, 缓存，myISAM等。

3.2 单thread crud的设计

单thread crud有两个问题需要解决：

1） 怎么加速查询

2） 怎么做到crash safe.

3.2.1 加速查询

1） 查缓存。 

这个很好理解，但是其实最好不要turn on 缓存，因为缓存可能被频繁更新，挺贵的。 

2）索引

索引的基本结构是B+ tree，一般一个node默认有1024个children, 主要十亿行数据的索引树，也只有3层树。索引在物理结构上是另外的磁盘文件，因此索引会消耗额外的磁盘空间，同时数据被更新时，索引结构可能也会改变，因此也会增加额外的operation cost，但是由于大多数业务场景是读多写少，因此可以用索引。 

索引分为主键索引和非主键索引。 

主键索引的leaves指向数据，非主键索引的leaves指向主键。因此非主键索引需要作回表操作。

非主键索引的优化有： 

1） 覆盖索引。对于联合索引，使用前面几个key columns，查询联合索引的后一个columns，不需要回表。

2）主键查询：根据非主键索引查询主键值，不需要回表。 

3）最左前缀：可以用联合索引的最左边的columns作where 查询。 

需要注意的是，优化器选择索引的时候，是根据索引列的统计信息做选择，有时候统计信息不准确，会让优化器作出错误的选择。这时候的优化手段有：

1） force index

2) rebuild index. 

普通索引还有唯一索引和非唯一索引两类，它们的区别在于update语句，唯一索引更慢，因为它需要去磁盘确定这个索引是否存在，而非唯一索引直接把这个更新值放在 内存的change buffer里。 

3） change buffer

Update语句将结构先放在内存的一块change buffer里，再查询的时候可以直接访问change buffer，这样可以加快update和select语句。同时change buffer满了以后才会写入磁盘。 change buffer的大小是可以控制的。

3.2.2 Crash safe

怎么确保数据库可以crash safe呢。 

为了保证crash safe，如果是INNODB的引擎，主要采用两阶段commit办法。 

Step 1: redo log prepare (阶段1）

Step 2: binlog写入操作

Step 3: redo log commit (阶段2）

如果Step 1结束后失败，那么transaction失败

如果Step 2结束后失败，transaction可以判定为成功，因为MySQL会根据redo log and binlog自动commit redo log. 

两阶段commit可以configure每多少个command写入磁盘，这样可以优化数据库performance. 

3.3 concurrent threads crud

数据库会维持一个thread pool,每个connection都会pick一个thread(thread and connection 区别？)。 在concurrent情境下，一个问题是多个crud transaction可能会操作同样的数据，那怎么确保它们的操作顺序呢？为此引入了锁的观念。 

为了理解锁，需要理解transaction。 一组被同时发起的操作会在一个transaction里，这些操作要么全部成功，要么全部失败。在transaction的基础上，进一步引入锁的概念。需要注意理解的是，在锁的基础上，怎么理解一组transaction互相影响后的结果。 

3.3.1 四种隔离界别的transaction

1. 可重复读，默认级别， 一个transaction执行过程中看见的数据，综合它开启式看见的一样。未提交变更，其它事务不可见

2. 读提交，未提交变更，其它事务不可见

3. 读未提交。未提交变更，其它事务可以看见变更

4. 串行，同一行记录，写有写锁，读又读锁，任何一个锁，必须在事务结束后才释放，并且会阻碍其它事务访问这行记录。 

可重复读和读提交的区别在哪里呢：假设两个事务A和B，A只有读操作，B有更新操作，更新1到2. 如果是可重复读，A从头至尾只读到1， 如果是读提交，A在B提交后，读到2. 

事务隔离实现是用MVCC（多版本控制）实现的，也就是每一个事务会对数据库加入一个版本数，数据库会记录每一行数据的版本历史，数据库会根据每个事务的版本数提供给其应得的数据。 

3.3.2 理解锁

在事务基础上，又有了锁这个概念，锁分为全局锁，表级锁，行锁。 

全局锁：数据库只可读，可以用于全库逻辑备份，但是不建议这样做，可重复读级别的事务就可以满足这个要求

表级锁：分为表锁和元表锁，又分为读锁和写锁。读锁不互斥，写锁排斥其它锁。 表锁需要显式使用，元数据锁自动加上。 

行锁：行锁在引擎层实现。在可重复读下，行锁的读是一致性读，但是行锁的更新是当前读（这一点很重要！） update, delete, insert会自动加入排他锁，select for update也会加入排他锁。select in share mode会加入共享锁，update/share mode也是当前读。 

幻读：一个transaction先后两个读，后一个读读到了新的行，叫做幻读。这是由于其它transaction insert and commit，这个transaction当前读读到了不该读的行数。 这叫幻读。 为了解决幻读，有间隙锁的概念。间隙锁在可重复读级别才有。 间隙锁和行锁够成next-key lock. Next-key lock会影响并发度。在实际操作中，可能会用读提交和binlog = row 格式？。 

间隙锁加锁规则： 

1. 加锁基本单位是next-key lock(间隙锁加行锁)。 

2. Select中访问到的对象才会加锁（覆盖索引时不会进入主键索引）

3. 索引的等值查询，在给唯一索引加锁的时候，next-key lock退化为行锁

4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件，next-key lock退化为间隙锁。 (其它query的等值update就可以执行)

5. 一个bug: 唯一索引上的范围查询会访问到不满足条件的第一个值位置。

详细解释： https://time.geekbang.org/column/article/75659 要注意覆盖索引和主键索引被锁的区别

3.3 表结构改变

表结构改变会加元数据锁。 

3.4 主从架构

主从架构中用长链接连接master and slave,用binlog传递数据。binlog 传递数据的最好格式是row,而不是statement.因为statement可能会导致主从不一致（比如使用limit语句，可能会在主从数据库里产生不同效果）。

主备之间可能有延迟，一般binlog传输很快，所以延迟主要出现在备库relay log.那么可能有以下原因： 

1） 备库性能差

2） 备库压力大（运维查询）

3）大事务。 

目前备份数据库使用了多线程进行备份，遵循两个原则：1. 同一行的更新由同一个worker执行

2. 同一个事务由同一个workder执行。 

多线程备份几种策略

1. 按表

2. 按行

3. 按库

4. 按commit_id 

一主多从模式在切换主库的时候需要判断从库从哪里开始读，MYSQL的GTID完美解决了这个问题。GTID基本是每个transaction有一个全局唯一的ID， 通过比较新主库和从库的ID的差集，来判断从库从哪里开始读。

3.4数据库备份

在可重复的情况下，可以直接备份。 

4.实际操作中的优化手段。 

4.1 order by. 

全字段排序和rowid排序。全字段排序是把符合条件的数据全部取出来，进行排序。rowid排序，是只取需要排序的数据column，排好序，再会数据库按序取数据。 

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择

尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

4.2 JOIN

第一个问题：能不能使用 join 语句？如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。第二个问题是：如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。所以，这个问题的结论就是，总是应该使用小表做驱动表。

Join优化：1. 对index nested loop join. MRR优化。将主键排序放在内存里， 然后一条条去查询，增加顺序读的可能性。 由此演化出BKA优化。MRR针对非join，BKA针对join. 

2. 对block nested loop join. 问题是: BNL 算法对系统的影响主要包括三个方面：可能会多次扫描被驱动表，占用磁盘 IO 资源；判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

解决办法： 1. 在原表上加索引，2. 还是用有索引的临时表，

3. hash join需要在业务层实现。mysql不支持hash join. 

4.3 临时表

适用场景是分库分表，查询到的数据在临时表中做聚合

内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。

临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

1. 建表语法是 create temporary table …。

2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。

3. 临时表可以与普通表同名。

4. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。

5. show tables 命令不显示临时表

创建临时表以后，要考虑是否需要drop temp_table manually.如果server自己会做，就不用，否则就用。 这涉及到主备一致的问题。 

有时候一些query会自动创建临时用表：

1. Union

2. Groupby 

相应的也有一些优化： 1. Groupby column加索引, 2. 直接排序。 

4.4脏页

由于change buffer的存在，磁盘数据和内存数据不一样时，磁盘数据为脏页。如果数据库崩了，那么内存数据就丢了。

数据变更一开始只写入磁盘的redo log,在必要的时候，数据库会根据redo log 更新磁盘数据。 必要的情景：

1. Flush语句会迫使数据库更新磁盘数据。 

2. Redo log快写满了。redo log逻辑结构是一个闭环的四个内存区

3. 内存快满了，需要淘汰脏页。 

4. 数据库不忙的时候

5. 数据库要关闭的时候

脏页控制策略： 告诉InnoDB的IO能力，参数： innodb_io_capacity,设置成磁盘的IOPS。 引擎是按照一个比例乘以innodb_io_capaicty来刷磁盘。 

Mysql在刷脏页还有一个默认的策略，就是把邻居脏页页刷掉，在机械硬盘时代有意义，SSD可以关掉这个功能。innodb_flush_neighbors是这个参数。 

4.5函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

4.6 KILL process

Mysql可以kill两种东西：一个线程的query,和一个线程的connection. 

一个线程被终止以后，需要进入线程判断逻辑以后才能确定是否需要终止query/command,然后进而进行回收操作。如果一个线程在等待状态，那么kill connection是不会kill query的，需要线程开始进入inno db 才会判断出需要kill. 同时对于大事务/大表DDL的kill， 需要清除临时表，因此也比较慢。 

一个问题是kill table structure update query，怎么kill