MySQL学习总结

MySQL 主从复制原理
主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。
接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。
应用发起增加、删除、修改数据的操作时，master节点上会产生相关操作的binlog。
master节点会有一个专门的线程 log dump thread 会通知所有的slave接待有新的binlog产生。

slave上会有一个I/O线程去master上拉取最新的binlog，并写到本地，也就是图中的relayLog。同时slave也会有个专门的sql线程读取回放relayLog，通过这种方式达到主备同步。

所谓同步复制就是一个事务在Master和Slave都执行后，才返回给用户执行成功。虽然同步复制的数据一致性很高，但它带来的性能开销也很大，
因此没必要为了极低的故障概率而采用这么激进的方式。

半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，
接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
半同步复制就是介于异步复制和同步复制之间的模式：

1、它不要求Slave执行同步过来的binlog，而仅仅是接收到日志后，返回消息给master即可，性能比同步复制高。

2、它可以确保一个事务在master节点执行的binlog，会同步到salve节点。因此可靠性相比较异步复制而言大大提高

半同步复制主要有两种模式，AFTER_SYNC模式和AFTER_COMMIT模式。
两种方式的主要区别在于master节点在等待slave的响应之前提交还是响应之后提交事务。

在AFTER_COMMIT中，可以看到commit和binlog是同步进行的。有这么一个特殊的场景，如果master commit成功，在等待slave ack的过程中挂掉了，
而此时binlog还未完全同步到slave上，那么master库就会比备库多一个事务，这种情形下主备之间还是产生了不一致，虽然出现的概率会很极端，但情况不能完全避免。

那么AFTER_SYNC模式就可以避免这一点，它的数据一致性相比较而言比AFTER_COMMIT要好，可以看到master在同步binlog后，就开始等待salve的ack反馈，
只要在slave有结果返回后，才会进行提交操作。可靠性相比较AFTER_COMMIT大大提高。

并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

解决延迟方法
分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。
打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你要是这么搞，读写分离的意义就丧失了

索引
B+tree
非叶子节点只存索引，只有叶子节点才存索引和data（data中可能存对应这行数据的地址，也有可能存整行数据）
最左原则

explain命令
返回字段：id，select_type,table,type,possible_keys,key,key_len,ref,rows,extra
id:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在,id值越大优先级越高，越先被执行

select_type:
SIMPLE 简单的select查询，查询中不包含子查询或者UNION

PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY 在SELECT或WHERE列表中包含了子查询

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT 从UNION表获取结果的SELECT

type:所显示的是查询使用了哪种类型
从最好到最差依次:system > const > eq_ref > ref > range > index > all
system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，
然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，
这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all Full Table Scan 将遍历全表以找到匹配的行

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key:
实际使用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref
显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值

rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

Extra
包含不适合在其他列中显式但十分重要的额外信息

MySQL事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是 无锁
读已提交（read-committed） 否 是 是 行锁
可重复读（repeatable-read） 否 否 是 MVVC (多版本并发控制) 可重复读是在事务开始的时候生成一个当前事务全局性的快照 Next-Key（行+间隙锁）锁解决幻读
串行化（serializable） 否 否 否 读，共享锁。写，排它锁

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

在mysql当中，关于innodb的锁类型总共可以分为四种，包含了行锁和表锁，分别是

基本锁 - [ 共享锁（Shared Locks：S锁）和排它锁（Exclusive Locks：X锁）]

意向锁 - [ intention lock,分为意向共享锁（IS锁）和意向排他锁（IX锁）]

行锁 - [ record Locks、gap locks、next-key locks、Insert Intention Locks ]

自增锁 - [ auto-inc locks ]

InnoDB三种行锁的算法：

Record Lock：单个行记录上的锁，只锁定记录本身

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。 目的是为了防止同一个事物的两次当前读，出现幻读的情况

Next-Key Lock：1+2,锁定一个范围，并锁定记录本身。目的：解决幻读

redo log 作用：write-ahead-logging预写日志 也可用于重启后，重放日志
1.事务开始后先有redo log buffer 内存缓冲
2.将内存中的数据写入磁盘ib_logfile0
三种情况：1.master线程每1秒写入 2.事务commit的时候写入 3.log buffer使用超过一半时写入
3.记录磁盘数据页修改状态
4.对应事务数据写入磁盘后，可覆盖
5.找到最近的checkpoint，重放时，只需要执行checkpoint之后的操作

undo log 作用：回滚，版本链，mvcc
1.事务开始后，先写 undolog buffer缓存 再写 redolog buffer 缓存
2.写入磁盘先写undo log 再写 redo log
3.记录的是反向的 便于理解是这样的：事务T1，A原有余额，200

bin log 作用：用于主从同步
1.事务提交的之后，一次性将事务中的sql子句按照一定的格式记录到binlog中
2.先写redo log，再写binlog
3.两个日志都提交（刷入磁盘），事务才算真正的完成