朝花夕拾 - Mysql那些事

IT行业相对于一般传统行业，发展更新速度更快，一旦停止了学习，很快就会被行业所淘汰。所以我们需要踏踏实实的不断学习，精进自己的技术，尤其是初学者。今天golang学习网给大家整理了《朝花夕拾 - Mysql那些事》，聊聊MySQL，我们一起来看看吧！

Mysql那些事
关键词：事务与日志、隔离级别、锁（死锁）、索引、InnoDB_buffer_pool、双写机制、主从同步、集群方案、分库分表、explain分析

1. MySQL事务：
   mysql事务只能存在与Innodb引擎之下，InnoDB引擎为了保证事务的ACID特性，专门有redo_log和undo_log来保证持久性和原子性，隔离性是靠事务隔离级别和锁机制来保证的，而持久性、原子性、隔离性都是为了保证一致性。
   什么是一致性？是指数据从一个确定的状态转移到另外一个确定的状态。这样太抽象了，可以直接举例：A给B转了100块，A账户要少100块，B账户上要多100块，并且还能表示出这100块是从A转给B的，这样就是一个确定的状态。
   redo_log：重做日志，保证持久性而存在的产物，有固定的文件大小和数量，轮询写入，一般是2个文件，大小为128M，记录的是关于页的物理逻辑日志，所以恢复起来会非常快，里面的轮询写入是指，当一个文件写完了，就直接跳到另一个文件的头部重新覆盖，这样做得好处就是节省磁盘空间，因为对redo来说，数据只要刷盘成功，redolog的历史记录就失去了他的意义，所以只记录有限大小，如果在高并发数据库中写，可以适当增加文件数量。注意这里的物理逻辑日志，等会说双写机制的时候会提到，redo_log的刷盘策略是根据innodb_flush_log_at_trx_commit=0、1、2来确定的。

   • 0: 每秒从redo_buffer->os_cache->flush(fsync操作)
   • 1: 每次事务提交 redo_buffer->os_cache->flush
   • 2: 每次提交事务 redo_buffer->os_cache 每秒flush

undo_log是保证事务的原子性的，也是随同事务的周期来写入的，在RC隔离级别以上，会有MVCC来避免脏读，RR隔离级别之上，会有快照读和当前读+next-key_lock来避免幻读。
MVCC: 多并发版本控制，当我们读取到一行数据时，MVCC会在这行数据多加两个列：当前事务ID和回滚ID。当需要查询数据的时候，会查找事务ID小于当前事务ID和回滚ID大于当前事务ID的数据。当更新时，会将旧行数据复制一行，并将更新后的值更改掉，加上当前事务ID，将旧行的回滚ID写入当前事务Id，当新插入数据时，会直接将当前事务ID加在对应字段上。
快照读和当前读：
快照读：事务开始查询时，将查询结果缓存在buffer中，后续的读取就从buffer+undolog结合来进行。
当前读：事务在执行update、insert、for update、lock in share mode时，会直接操作现有数据，通过next-key lock来避免幻读。

2.双写：
双写机制是为了避免脏页刷到磁盘上因为机器断电而出现部分写失效的问题，所以为了解决这个问题，双写的过程如下：

• 将数据脏页写在double_write_buffer中，这个buffer大小一般是2M，
• 在共享表空间上分配连续两个区，也是2M
• 将double_write_buffer中的数据刷入共享表空间中的两个区上，因为是顺序写，性能很快
• 再将double_write_buffer中的数据刷入硬盘中，此时是离散写，性能会很慢。

通过利用共享表空间的顺序写机制，刷盘动作不容易出现写一半出错的情况，下面说恢复时的过程：
redo_log用来记录数据页上的物理日志，所以redolog必须要定位到页，然后校验check_sum，才能进行数据恢复。所以在恢复时，首先会找到破损页，因为check_sum不通过，所以会从共享表空间中最近写入的两个区找到对应页的数据重写入这个页中，然后再校验checksum，判断check_point，开始从事务开始的地方开始重做。

3.锁
锁的分类可以由以下几种：
表锁：IS锁，IX锁
行锁：Record lock
间隙锁：Gap lock
临键锁：next-key lock
共享锁、排他锁（读锁、写锁）
乐观锁、悲观锁（概念）
死锁（现象）
死锁的几种情况：

• 行X锁互相等待，导致死锁
• Gap锁与插入意向锁互相等待，导致死锁
• X锁与S锁互相等待，导致死锁

如何表面死锁？除了上述说的几种情况之外，要尽量减少锁粒度，给事务加锁的种类，要尽量单一，不要出现一个事务多种锁的情况。

4.索引
B+树索引，哈希索引。
B+树索引，叶子节点存放所有数据，子节点存放索引冗余和其他节点的指针，查找时从头部节点开始向下查找，查找的时间复杂度为O(lgN)，这个值会直接影响索引检索时的硬盘IO次数，树的深度越深，IO次数越多。在B+树上，数据是从小到大排列的，全局有序，所以可以用来做排序，范围查找。
哈希索引：查找时间复杂度为O(1)，理想状况下，但是，哈希索引只能是查找单个元素比较快，如果放在范围，条件判断这类查找时，哈希索引就不行了，更别说还要满足排序了。

5.Innodb_buffer_pool
Innodb_buffer_pool的三个比较重要的list: free_list、lru_list、flush_list，free_list就是空间内存块链表，lru_list就是顾名思义，最近访问内存链表，flush_list就是脏页刷新链表，当需要从这个buffer_pool中加载数据的时候，首先会根据spaceId+pageNumber来确定要访问的页，然后从自己的pool中查看是否有该页的数据，（lru_list），如果有该页数据的话，那么查找到之后，直接从中加载对应数据，查找不到的话，需要从硬盘中加载数据，那么加载完数据之后，需要放入到buffer_pool中，buffer_pool中需要找到对应的空闲内存块，于是先从free_list中寻找可用的空闲内存块，如果没有可用的空闲内存块，则从lru_list中的尾部开始向前查找，是否有可以删除掉的空闲内存快，如果有就放入到free_list中，同样，这里查找不到的话， 就从flush_list中查找是否可以刷盘，刷盘后的内存块会放入到free_list中去。

注意这里的脏页，刷到磁盘上时，其实是先要刷到double_write_buffer中的，然后才开始写入磁盘。下面的，就如上双写内容了。

6.主从同步
从库有一个IO thread会向master发起一个同步请求，master会将本地的binlog发送给这个线程，这个线程会将binlog写入到relay_log中，slave还会启动一个sql thread来专门重放relay_log，这样的话，slave就相当于重做了一遍主库的数据操作。

7.集群方案
一般为M-S读写分离的集群架构，但是单单的M-S是不支持故障转移的，所以需要借助其他的工具，如一些代理组件来接入数据库，路由并完成故障转移。
M-S架构中，S同步时可能会对M同步时造成M的硬盘负载比较大，所以需要用其中一个slave机器用作binlog中继，即一个blackhole引擎的节点，这个blackhole引擎的节点负责同master复制binlog，然后，其他的slave从这个blackhole节点中同步binlog，blackhole因为其只同步binlog但不写入表的特性，硬盘IO较小。

其他的集群方案，有MHA，将各个M-S分成一个独立的group，然后各个group之间会有同步，这样的话，可以提高故障时的高可用，也可以完成读写分离的需要。MHA是日本某公司基于MySQL-cluster做出来的一个集群方案，可用性较好。

MySQL-Cluter，是MySQL官方出的一个集群方案， 这个集群方案中，需要将引擎换为NDB，这一点对于老的数据节点来说很不友好。

8.分库分表
对于单表而言，分库分表也即将一张大表拆分成小表，有两种办法：水平拆分和垂直拆分。
水平拆分：将数据量大的表，拆分成多个业务表，前端可以路由获取对应的表中的数据，路由规则可以是对全局主键hash处理。
对于业务来说，应该是不同的业务采用不同的库，避免不同业务压力聚集到单个库中。业务量大的库，可以单独部署集群或拆分成小业务并读写分离。

9.explain
其中几个比较关键的列：
select_type type key possible_keys key_len rows
select_type查询类型：simple、unioin、subquery之类的类型
type systemconstrefeq_refrangeindexall system
key 用到的索引
possible_keys 可能索引
key_len 索引长度
rows影响行数

本篇关于《朝花夕拾 - Mysql那些事》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！