表姐告诉我学习数据库这一篇就差不离了

来源：左耳君（ID:qaqzuoer）

作者： Captain

正如大家所预料，接下来我们针对数据库的知识点进行全方位的分析，其实数据库应该是程序员的必备技能了，也是大家最最常用的了，大家不可能在开发中不会用到数据库吧，那我有必要怀疑你是个假程序员

这一系列其实我很早之前就想写了，但是也只是处于一个想的阶段，一个构思，思考如何才能更好的把这一系列写好，其实吧，本来吧我这人写作就不太擅长，不过感觉现在比之前好多了，最起码也可以和大家瞎聊聊了

我也会坚持写下去，虽然现在的粉丝并不多，但是我写这个的初衷也是为了记录自己的学习知识点，我相信每一个程序员对于庞大的后端知识体系都不可能是手到擒来，都需要进行一段时间的回忆和思考，所以呢，正是由于这个，我就产生了写文章的这个想法，把自己对于知识点的汇总和理解记录下来，如果后续遇到一些不清楚的，可以利用自己的文章迅速的捡起来

所以呢，大家也可以在之前的rocketmq系列文章也可以看出来，我会自己记录并且转发一些自己认为比较好的文章，为的就是以后用到该知识点可以手到擒来!

数据库呢，大家用的最多的肯定就是mysql了，当然，我们接下来要分析的知识点也是围绕mysql来分析

1、数据库三大范式

2、mysql常用存储引擎

3、树结构和索引类型

4、查询性能优化、explain、索引优化

5、事务、锁类型

6、常用日志类型

7、MVCC多版本并发控制

8、分库分表策略

9、JSON

10、主从复制、读写分离

01 数据库三大范式

数据库三大范式

第一范式：确保每一列的原子性，也就是表中的每个字段都是不可分解的原子值，这个属于最基本的范式

第一范式的合理遵循需要根据系统来设定，比如有些系统中的地址属性，在系统中的出现永远都是整个地址属性，那就属于满足原子性，满足第一范式。

但是，如果如果我们系统中可能会经常用到地址中的省、市、区，而且还可能经常单独出现其中某一个属性，那这就属于不满足第一范式了

所以，是否满足第一范式不能单纯的按照我们的日常习俗去断定，需要根据系统的设定来决定是否真正满足第一范式

第二范式：确保表中的每一列都和主键相关，而不能只和主键中的其中一部分相关，主要是针对联合主键

也就是一个数据库表中，只能保存一种数据，不可能把多种数据保存到同一张数据表中，比如要统计一个地区的多个学校的学生，因为地区有多个学校，每个学校中的学生有相应的编号，所以要把学校ID和学生编号设计成一个联合主键

第三范式：确保每一列和主键都是直接相关，而不是间接相关

再说上面的例子，我们可以通过学校ID作为外键和学校教师表建立相应的关系，而不能直接把学校教师相关信息存储到学生表，这样就属于违反了第三范式了

02 mysql常用存储引擎

存储引擎

存储引擎其实就是属于对数据库文件的一种存取的方式，如何实现底层的存储数据，以及如何为存储的数据建立索引

存储引擎属于表级别，这一点可千万要知道，难道你没见过在建表的时候让你选择该表的存储引擎的吗

mysql常用存储引擎有四种：MyISAM存储引擎、innoDB存储引擎、MEMORY存储引擎、ARCHIVE存储引擎

// 查看mysql所支持的存储引擎，以及从中得到mysql默认的存储引擎
show engines; 
// 查看mysql 默认的存储引擎
show variables like '% storage_engine'; 
 // 查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename ;
//准确查看某个数据库中的某一表所使用的存储引擎
show table status from database where name="tablename" 

MyISAM存储引擎

不支持事务，不支持行级锁，只能对整张表进行加锁，只支持并发插入的表锁，主要用于高负载的select，读取的时候对需要读的表加共享锁，写入时则对表加排他锁

在表有读取操作的同时，也支持往表中插入新的记录，这是并发插入

底层结构也是B+树，但是属于非聚簇索引，也就是说只要查询的数据不止主键列，就需要记性回表查询，这一特性和innodb引擎还是不一样的，innodb采用的是聚簇索引，这两个概念会在下面介绍，大家先简单了解下

强调了快速读取操作，占用空间小，处理速度快，表支持三种不同类型的存储结构：静态型、动态型和压缩型

静态型：表的列的大小固定，自动使用静态的存储，性能比较高，因为在维护和访问一预定格式存储数据时需要的开销低，但是这种高性能是用空间为代价换来的，因为定义的时候固定，不管列中的值有多大，都会以最大值为准，占据整个空间

动态型：如果列定义是动态的，就会自动使用动态存储，虽然动态型的表占用了较少的空间，但是会带来性能的降低，因为某个字段内容发生改变其位置就需要移动，可能导致内存碎片的产生

对于内存碎片一般两种解决方案：

1、尽量使用静态数据类型

2、经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失

压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用。

innoDB存储引擎

mysql的默认存储引擎，支持事务，回滚以及系统崩溃的修复能力

底层存储索引也是使用B+树，和上面不同的是使用的聚簇索引，减少了回表这一操作，可以直接通过主键找到整条数据，因此性能较高

innodb内部优化，在磁盘读取数据的时候采用的可预测性读，能够在内存中创建hash索引用来加速读操作，以及能够加速插入操作的插入缓冲区

支持自增长列，自增长列的值不能为空，支持外键

采用MVCC多版本并发控制支持高并发，而且实现了四种隔离级别，每一种隔离级别对应着该等级存在的问题，可以根据业务场景选择合适的隔离级别

MEMORY存储引擎

在内存中创建表，所有数据也是存在在内存中

每个基于内存存储引擎的表都实际对应一个磁盘文件，文件名和表名是相同的，类型为.frm，该文件只存储表的结构，而数据文件都是存储在内存中，有利于对数据的快速处理，提高整个表的处理能力

默认使用hash索引，速度比B+树快，缺点就是对于范围查询不友好咯，这个下面会对数据结构分析一波，大家就会明白B+树索引和hash索引的区别

如果进程发生异常重启或者关闭机器，这些数据都会消失，所以该存储引擎中的表的数据的生命周期都很短，一般也只使用一次

ARCHIVE存储引擎

该存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些

03 树结构和索引类型

二叉树

N个有限元素的集合组成，集合为空的时候，则是一个空的二叉树

树中的节点的度不大于2的有序树，二叉树的每个节点最多有两颗字数，且有左右之分

左子树元素小于父节点元素，右子树节点元素大于父节点元素，次序不能颠倒

即使某个节点只有一个子树，也要区分左子树和右子树

优点：有序，可以更快的查询到相应元素

缺点：如果选取根节点不准确，则容易出现左右子树的高度偏差情况，极限情况下，可能会出现链式的结构，下面AVL树也是为了解决这个情况而出现的

AVL二叉平衡树

是一个空树或者左右子树的高度差的绝对值不超1，并且左右两个子树也都是一颗平衡二叉树，为的就是解决上述说的退化成链表的情况

插入、查找和删除的时间复杂度的最好情况和最坏情况都维持在O(logN)，但是最好不要用于修改数据比较多的情况下，一般用于修改少查询多的场景

红黑树

它一种特殊的二叉查找树。

红黑树的每个节点上都有存储位表示节点的颜色，可以是红(Red)或黑(Black)。

相较于严格要求平衡的AVL树，它的旋转的保持平衡次数较少，适合查找少和修改次数较多的场景

红黑树的特性:

(1)每个节点或者是黑色，或者是红色。

(2)根节点是黑色。

(3)每个叶子节点(NIL)是黑色。这里叶子节点，是指为空(NIL或NULL)的叶子节点

(4)如果一个节点是红色的，则它的子节点必须是黑色的。

(5)从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

叶子节点，是只为空(NIL或null)的节点

确保没有一条路径会比其他路径长出俩倍，因而，红黑树是相对是接近平衡的二叉树

B树

上面说到的都是属于两个子节点，这种结构如果用于文件系统有一个很大的缺点，就是树的高度会比较高，会使文件系统多次的进行磁盘IO，效率极其低

多路查找树，体态矮胖，可以更少的进行磁盘IO，想象一下，树的每一层代表一次磁盘IO

描述一棵B树时需要指定它的阶数，阶数表示了一个节点最多有多少的孩子节点，一般使用字母m表示阶数。

当m取2时，就是我们常见的二叉搜索树。

一棵m阶的B数定义如下：

(1)每个节点最多有m-1个关键字

(2)根节点最少可以只有一个关键字

(3)非根节点至少有Math.ceil(m/2)-1个关键字

(4)每个节点的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它

(5)所有叶子节点都位于同一层，或者说根节点到每个叶子节点的路径长度都相同

B+树

B+树是对B树的变形，最大的区别是非叶子节点不保存数据，而只用于存储索引，所有的数据都保存到叶子节点中

B树是所有节点(包含叶子节点)组成了所有的数据，而B+树是所有数据均存储到叶子节点上

同时B+树的所有叶子节点都有相邻叶子节点的指针，也就是所有叶子节点组成了一个链表

关于数据结构，这里我给大家推荐一个网站，可以学习下各种数据结构，观看数据组成原理：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B树和B+树的对比

B+树的磁盘IO更低，查询效率更高

B+树的非叶子节点并不会存储整条数据，而是存储数据的索引，这句话是针对于MySQL表来说，因此非叶子节点可以用同样的存储空间，存储更多的索引数据，也就使得B+树更加的矮胖，可以一次性读入内存中的关键字也就越多，磁盘的IO次数也就降低了，查询效率也就更高

查询效率更加稳定

非叶子节点并不是最终指向文件内容的节点，也就意味着如果需要获取更多数据，都需要通过非叶子节点索引找到叶子节点，也就是任何关键字的查找都需要走一条从根节点到叶子节点的路径，所以查询效率也更加稳定

B+树遍历效率更高

由于B+树特有的结构，只需要遍历所有叶子节点的数据便可以实现整棵树的遍历

B+树更好的支持范围查询

1. 范围查询在现在系统中是必不可少的，B+树的叶子节点都有相应的指针指向前后节点，组成链表，所以更好的支持范围查询。而B树效率则会很低
2. 上面说了这么多种树，为的就是给大家理解mysql底层索引和数据的存储结构跟上Captain的步伐，继续冲

MySQL索引

主键索引

主键索引，不允许null，这个是底层构建B+树的依据，可以提高查询效率，并提供唯一性约束，一张表中只能有一个主键，被标志为自动增长的字段一定是主键，但是主键并不一定自动增长，一般把主键定义在无意义的字段上，主键的数据类型也最好是数值

B+树的构建就是根据主键索引来构建，如果我们未指定主键，MySQL会自动创建一个列来作为主键索引

普通索引

最普通的索引，没有任何限制，该唯一索引指向的是主键索引，通过唯一索引找到主键索引，然后去主键索引构建的B+树中回表查询具体数据，如果只需要主键字段，则不需要回表即可满足条件

唯一索引

特性就是唯一，可以为null，可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。

唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。

存在唯一键冲突的时候的避免策略

insert ignore

会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

replace into

首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。使用replace into，你必须具有delete和insert权限

insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE;如果不会导致重复的问题，则插入新行，跟普通的insert into一样。使用insert into，你必须具有insert和update权限

如果有新记录被插入，则受影响行的值显示1;如果原有的记录被更新，则受影响行的值显示2;如果记录被更新前后值是一样的，则受影响行数的值显示0

全文索引

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like

MySQL可以通过建立全文索引，利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。比如实现全匹配模糊查询。

mysql的全文索引性能非常不稳定，不建议生产环境使用。需要使用全文检索的地方，建议使用ES

空间索引

MyISAM 存储引擎支持空间数据索引R树，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

组合索引

多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

Hash索引

hash结构存储数据，查询速度很快，也只能用于精确查找。缺点是范围查询效率很低，无法用于排序和分组

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。所以自适应哈希是起到一个辅助作用

索引的优缺点

大大加速数据的检索速度

减少磁盘IO，最根本也是提高了速度

创建和维护索引需要耗费时间，表中的数据进行增加、修改和删除的时候，索引也需要动态维护

索引需要占据额外的存储空间，所以这并不意味着索引越多越好，有些小表可能进行全表扫描速度更快，因为使用索引需要进行回表查询，就是先通过索引找到主键索引，再通过主键索引去构建的B+树中去查找整条数据

04 查询性能优化、索引优化、explain

查询流程 

(1)客服端发送一条查询给服务器

(2)服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。

(3)服务器端进行SQL解析、预处理，在由优化器生成对应的执行计划。

(4)MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

(5)将结果返回给客户端

优化数据访问

查看是否请求了不需要的数据，是否请求了过量的数据或者重复请求数据

查询到不需要的数据，可以用limit进行分割，我们需要对select * 敏感，只返回自己需要用到的数据，在关联表的数据的时候，尽量避免使用select * 来查询数据

重构查询方式

在传统实现中，总是强调需要数据库完成尽可能多的工作。但是对于MySQL而言，这并不适用，MySQL从设计上让连接和断开连接都非常轻量级，在返回一个小的查询结果方面很高效。

如果将一个复杂的查询拆分成多个简单查询对应用更友好，能减少更多的工作，就不要害怕这样做。

我们可以将一个大的查询“分而治之”，将大查询分成小查询，每个小查询 功能相同，只完成一小部分，每次只返回一小部分查询结果。这样可以减轻服务器负担，避免一次性锁住很多数据，占用过多事务日志等。

优化特定类型查询

Count()查询

Count(*)查询在没有where条件的时候是非常快的，如果我们想要查询某一列不为空的数据量，可以使用count(col)，而不是使用where条件过滤

使用近似值

如果某些业务场景不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低

explain优化分析

一、 id，SELECT识别符，这是SELECT的查询序列号，我的理解是SQL执行的顺序的标识，SQL从大到小的执行

id相同时，执行顺序由上至下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

id如果相同，可以认为是一组，从上往下顺序执行;在所有组中，id值越大，优先级越高，越先执行

二、select_type，查询中每个select子句的类型

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

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

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

三、table，显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的)，有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

四、type，对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右，性能从差到好)

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

五、possible_keys，指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询使用(该查询可以利用的索引，如果没有任何索引显示 null)

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key，key列显示MySQL实际决定使用的键(索引)，必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len，表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的)

不损失精确性的情况下，长度越短越好

八、ref，列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows，估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra，该列包含MySQL解决查询的详细信息。有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ，order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

再说索引

使用联合索引的时候，只有满足最左匹配原则，才能够使用索引

解释一下，也就是把A、B、C作为组合索引的时候，则必须按照ABC的顺序来使用索引，底层才可以使用索引

select * from table where A = 'a' and B = 'b' and C = 'c'

索引必须按照上面的顺序使用，当然可以只使用A或者使用AB，但是一定要按照这个顺序使用，这就是最左匹配原则，这里是因为底层B+树存储的时候就是按照ABC依次排序的，所以查找的时候也就只能按照这个顺序使用才可以正确的使用索引

索引下推ICP，接着上面的联合索引，我们看个语句select * froom table where A like 'java%' and B = 1，这个语句在未使用索引下推的情况下会通过索引A然后进行like的查询，查出数据来，然后根据主键回表查出数据看是否符合B=1的条件

而使用了索引下推下，则在判断A的条件的时候就会直接把B的条件比对并且把不符合条件的过滤掉，而不需要回表查出数据再比对，减少了回表的次数，增加了效率

聚簇索引，指的就是索引和全部数据存在于一颗树上的这种结构，切记，这是一种数据存储方式，并不是一种索引，只是叫法是这样而已，主键就是聚簇索引，默认一个表一个聚簇索引，也就是通过索引可以直接找到全部数据

非聚簇索引，指的就是索引和数据不在一起的这种存储结构，普通索引都是非聚簇索引，这种索引查找到的是主键，也就是这种索引的B+树的叶子节点存储的都是主键索引的数据，然后如果需要查找主键之外的数据，则需要拿到主键之后再去主键构建的B+树中进行回表查询

回表，我们上面已经多次提到回表这个词了，这个其实就是普通索引构建的数据无法满足要求，这个时候就需要去主键构建的B+树中去查询所有数据，这就叫做回表

前缀索引，对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

觉得写得还可以的，可以点赞关注走一波

05 锁类型和事务

先说一下mysql中的几种语言，SQL语言共分为四大类

数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL：数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

2 .数据操纵语言DML：数据操纵语言DML主要有三种形式，插入，更新，删除。

3. 数据定义语言DDL：数据定义语言DDL用来创建数据库中的各种对象如：表 视图 索引 同义词 簇。DDL操作是隐性提交的，不能rollback

4. 数据控制语言DCL：数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

重点来了

锁

锁是并发访问同一个共享资源时的同步机制，Synchronized、ReentrantLock和ReentrantReadWriteLock都用过的吧，都是一样的道理

MySQL的锁是在服务器层或者存储引擎层实现的，保证数据访问的一致性和有序性

• 按模式分类为：乐观锁与悲观锁。
• 按粒度分可以分为全局锁、表级锁、页级锁、行级锁。
• 按属性可以分为：共享锁、排它锁。
• 按状态分为：意向共享锁、意向排它锁。
• 按算法分为：间隙锁、临键锁、记录锁。

乐观锁

一种思想，乐观锁建设数据一般情况下不会产生冲突，在数据的操作过程中不会对数据做任何锁定，只有当数据进行提交跟新的时候，才会正式对数据的冲突与否进行检测

如果发生冲突了，则返回错误，调用者决定如何操作，是回滚还是重试

比较适用于读多写少的情况，如果写场景比较多，写冲突的可能性比较高，可能需要不断重试，这样会大大降低系统性能

这种可以通过增加一个数据版本字段Version来实现，读取数据的时候把这个字段一起读出来，数据每更新一次，对Version字段加一，当我们提交更新数据的时候，判断数据库表中的对应记录的版本信息和第一次取出来的Version是否一致，一致则可以更新，不一致则过期

悲观锁

这个也是一种思想，悲观的看法，认为每次去取数据的时候都会有别人去修改，所以在整个数据处理过程中，数据处于锁定状态

适用于并发量不大，写入操作比较频繁，数据一致性比较高的场景，MySQL中，共享锁和排他锁都是属于悲观锁的不同实现

全局锁

对整个数据库实例进行加锁，一般用于全库的逻辑备份

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。使用这个命令之后，整个库处于只读状态，其它线程的更新语句都会被阻塞

主库备份，需要考虑影响业务系统，从库备份，在备份期间不能执行主库同步过来的binlog，主从同步会有延迟

解决办法，mysqldump使用参数--single-transaction，启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

对操作的数据表加锁，MyISAM和InnoDB引擎都支持表级锁定，这里分为两种，一种是表锁，一种是元数据锁，即meat data lock，MDL锁

lock tables 表名 read #该表可以读，不能ddl 和 dml 中增删改，只能读取表数据
lock tables 表名 write # 既不能读，也不能写

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

MDL锁

防止DDL和DML并发的冲突，你想啊，一个查询正在遍历表中数据，而执行期间另一个线程对这个表结构进行了变更，删除了一列，查询线程拿到的结果跟表结构对不上，就乱套了

MDL锁不是显示的，MDL锁是在5.5版本引入的

对一个表做增删改查操作的时候，加MDL读锁，读锁之间不冲突，所以多个线程可以同时对一个表进行增删改查

当要对表结构变更操作的时候，加MDL写锁，读锁和写锁、写锁和写锁都是冲突的，用来保证变更结构操作的安全性

两个线程同时对一个表中加字段，其中一个要等另一个执行完才可以开始。一个线程A先在查询数据，另一个线程B想要加一列数据，需要等A线程执行完才可以执行线程B，就解决了上面的问题

MDL锁是系统默认加的，我们理解了上面的机制之后，一定要注意MDL写锁之后的读锁和写锁都会阻塞，所以在给一些表加字段的时候一定要注意，尽量避开业务系统比较繁忙的时候

即使小表，操作不慎，如果一个表的查询语句频繁，而且客户端有重试机制，也就是超时之后还会再起一个新session，库的线程很容易就慢了，这时系统就崩了

千万不要在长事务中对表结构进行修改，事务不提交会一直占用MDL写锁，那后面的语句就需要一直等待

页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。

行级锁

MySQL中只有InnoDB支持行级锁，行级锁分为共享锁和排他锁。

行级锁是粒度最低的锁，锁冲突概率最低。但加锁慢、开销大，容易发生死锁现象。

行锁并不是直接锁记录，而是锁索引

索引分为主键索引和非主键索引，一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引

共享锁

共享锁，也就是我们常说的读锁，一个事务对数据加上读锁之后，其它事务只能对该数据加读锁，不能做任何修改，不能加写锁

这样可以更好的支持并发中的读取数据，读取数据的时候，不允许其他事物对当前数据进行修改操作，从而避免不可重复读的问题的出现

select … lock in share mode

排它锁

排它锁，也就是写锁，当对数据加上写锁之后，其它事务不能对该数据读写，这个时候读锁和写锁都不可以加了，也就是全部阻塞了

写锁就是为了解决在数据修改的时候，不允许其它事务对当前数据进行修改和读取操作，从而可以避免脏读问题的产生

共享锁可以避免不可重复读的问题，排它锁可以避免脏读问题的产生

意向共享锁和意向排它锁

意向锁的出现就是为了协调表锁和行锁，支持多粒度的并存

事务A有行锁的时候，MySQL会自动给该表加上意向锁，事务B如果想申请整个表的写锁，就不用遍历去每一行判断是否存在行锁，只需要判断是否存在意向锁，即可决定是否可以加表的写锁

意向锁的互斥性

当然，表格中的共享锁和排他锁都是表锁，即表锁和意向锁的关系

意向锁是不会和行级的共享排他锁互斥的

给大家再解释一下，就是有行级共享锁，那就加上意向共享锁，当需要加表级的共享锁的时候，兼容，即行读表读共存;相反，表级的排他锁加不上，也就是行读表写不共存

相应的行级的排他锁，也就是写锁加上之后，表级的读锁和写锁都是不能加上的了，也就是行写表既不可读也不可写

总结

• 行读表读共存
• 行读表写不共存
• 行写既不可读也不可写

记录锁

记录锁是封锁记录，记录锁也叫行锁

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列(包括主键列)上不存在临键锁。

事务

事务指的是一组SQL语句，要么全部执行成功，要么全部执行失败，要么提交，要么回滚，这句话大家听得耳朵都长茧子了吧

事务特性ACID

原子性：事务是最小单元，不可再分，要么全部执行成功，要么全部失败回滚。

一致性：一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致的状态。不一致性包含三点：脏读，不可重复读，幻读

隔离性：隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

事务隔离级别

未提交读：即能够读取到没有被提交的数据，所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种。

已提交读：即能够读到那些已经提交的数据，自然能够防止脏读，但是无法限制不可重复读和幻读

可重复读：读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，

串行化：多个事务时，只有运行完一个事务之后，才能运行其他事务。 

隔离级别问题详解

脏读：一个事务处理过程里读取了另一个未提交的事务中的数据

不可重复读：一个事务在它运行期间，两次查找相同的表，出现了不同的数据

幻读：在一个事务中读取到了别的事务插入的数据，导致前后不一致

和不可重复读的区别，这里是新增，不可重复读是更改(或删除)。

这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必须加表级锁，防止在这个表中新增一条数据。

再议锁和事务问题

相信大家读到这里，应该也大致对锁和事务的关系有了更进一步的理解了吧，来，给大家捋一捋

共享锁，也就是读锁，对一行数据加上共享锁之后，别的事务就无法获得该行数据的排他锁了，别的事务也就暂时无法对这个数据进行修改操作了，也就避免了不可重复读这个问题

排他锁，也就是写锁，一个事务对数据进行修改的时候，就获得相应数据的写锁，这时候别的事务也就无法获得该数据的读锁和写锁了，也就避免了脏读问题

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

06 日志类型

日志，就是记录行为的文件，这个大家应该都很熟悉，无论在开发什么系统中，我们都离不了日志来排查我们的bug，如果没了日志这一关键要素，那我们排查问题都无从下手了

mysql中有多种日志类型，有几种日志属于我们开发中需要熟悉的日志，无论是服务崩溃恢复数据，还是用来支持mysql的innodb的事务的原子性和持久性，这些都需要日志的支持，还有innodb所特有的MVCC多版本并发控制机制，来提高数据的并发性能

redo log重做日志

这一日志保证的是innodb引擎的事务的持久性，数据的写入都是先写入到缓存区中，并不是直接写入到磁盘中，然后会顺序存储到redo log日志文件中，写入的是会修改数据的增删改语句，并不会写入select语句，顺序写入速度很快

redo log的落盘并不是随着事务的提交才写入到文件中的，而是在事务的执行过程中便开始写入到redo log文件中了

redo log记录的是物理级别上的页修改，比如页号1、偏移量20写入了数据520

保证的即使如果系统发生故障，可以根据redo log的记录事务的执行状态来恢复未写入磁盘的数据，保证事务的持久性，当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，redo log占用的空间就可以被覆盖了

很重要一点，redo log是什么时候写盘的?前面说了是在事物开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M)，Innodb存储引擎先将重做日志写入innodb_log_buffer中

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

• Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。
• 每个事务提交时会将重做日志刷新到重做日志文件。
• 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交(commit)的时间也是很短暂的。

undo log回滚日志

保证事务的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提高MVCC，下面会详细说这个MVCC

存储的是逻辑格式的日志，在执行undo的时候，仅仅是把数据从弄个逻辑上恢复到事务之前的状态，而不是从物理数据上实现的，这一点不同于redo log的存储方式，存储的是相反的SQL语句，比如执行一个insert，我就存储一个delete。执行一个update，我就存储一个相反的update，用于回滚数据

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程来判断是否由其它事务在使用这个版本以及之前版本的信息，决定是否可以清理undo log中的日志空间，这块版本链和MVCC机制有关

binlog二进制日志

这是属于逻辑格式上的日志，可以简单的理解为就是执行过的事务中的SQL语句，但又不完全是SQL语句那么简单，而是包括了执行的增删改的SQL语句和反向的信息，即delete对应着delete本身和反向的insert语句，update对应着执行前后的版本信息，insert对应着delete和insert的语句

因此binlog可以做到类似闪回的功能，就是依赖于binlog日志文件的

事务提交的时候，一次性将事务中的SQL语句按照一定的格式记录到binlog中，这里和redo log明显的差异就是redo log并不是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就已经逐步写入到磁盘中了

因此开启了binlog的情况下，对于比较大的事务的提交，可能会变得慢一些

可以用于数据库的基于时间点的还原，还会用在主从复制中，从库利用主库的binlog进行重播，实现主从同步

二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的(当然也可以精确到事务层面的)，虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

机制不同：两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

效率不同：恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致(当然也包括使用binlog进行基于时间点还原的情况)，是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功(刷入磁盘)，事务才算真正的完成。

errorlog错误日志

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

slow query log慢查询日志

慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。

1. 查看慢查询时间：
show variables like “long_query_time”;默认10s
2. 查看慢查询配置情况：
show status like “%slow_queries%”;
3. 查看慢查询日志路径：
show variables like “%slow%”
4. 开启慢日志
set global slow_query_log=1
查看已经开启
Show variables ‘slow_query_log%’

general log一般查询日志

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }

也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销

因此，Mysql默认是把General log关闭的

查看日志的存放方式：
show variables like 'log_output'

relay log中继日志

从服务器I/O线程将主服务器的二进制日志(也就是上面说的binlog)读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致

说到底其实就是辅助完成主从复制的一个文件，master的binlog文件复制写到slave中的中继日志文件中，然后用来执行，很好理解，具体细节我会单开一篇主从复制来说

07 MVCC多版本并发控制

什么是MVCC

全称Multi-Version Concurrency Control，多版本并发控制，属于一种并发控制的手段，一般在数据库管理系统中，实现对数据库的并发访问

数据库就必然涉及到读和写的存在，读写就必然涉及到读写冲突，MVCC在mysql中的innodb引擎实现就是为了更好的解决读写冲突，提高数据库的性能，做到即使有读写冲突的时候，也可以不用加锁的方式，非阻塞方式来实现并发读

最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁

MVCC属于一种悲观锁的实现

当前读和快照读

当前读：像select lock in share mode这是共享锁，select for update , update , insert , delete都是属于排他锁，上面说的采用共享锁和排他锁的这种方式，都是属于当前读，当前读就是读取的记录的最新版本，读取的时候还会保证其他并发事务不会修改当前的记录，会对当前的记录进行加锁，防止修改

快照读：不加锁的正常的select查询都是属于快照读，也就是不加锁的非阻塞读。

当然，快照读的前提是隔离级别不是串行级别，此时便会退化成当前读，之所以出现快照读的情况，是mysql中的innodb引擎基于提高并发性能的考虑，快照读也就是基本多版本的并发控制，来更高效的解决读和写之间的冲突问题

根据业务场景来考虑可以接受的问题，避免了加锁的操作，降低了开销，既然是多版本并发控制，那么就要接受读取到的并不一定是最新版本的历史数据这一场景

实现

MVCC只是一个抽象概念，innodb实现这个靠的是三个隐式字段、undo log日志、Read View来实现的

三个隐式字段

数据库在每行记录中除了记录我们自定义的那些字段之外，还有数据库的隐藏的定义字段，DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

DB_TRX_ID：最近修改事务ID，也会记录创建这条记录和最后一次修改这个记录的事务ID

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，存储在undo log日志中的Rollback segment回滚段中

DB_ROW_ID：这个不是一定有，如果表没有创建主键，innodb会自动以这列为主键，以这一列来创建B+树，产生一个聚簇索引，也就是创建的其余索引的B+树的叶子节点存储的是这个主键

实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

再说undo log日志

Undo log日志分为两种insert undo log和update undo log

Insert undo log：这种是事务在insert新数据的时候产生的日志，只有在事务回滚的时候需要，所以在事务commit之后可以立即丢弃该日志

Update undo log：这个是在进行update或者delete而产生的日志，这个不仅是事务回滚的时候需要，在快照读的时候也是需要的，也就是innodb的MVCC机制会用到历史的数据，所以不能随便删除，需要等快照读和事务回滚都不涉及到该日志的时候，这个日志才会被相应的线程统一清楚

Read View

这哥们的作用可以理解为生成的一个镜像数据，记录当时的情况

事务快照是用来存储数据库的事务运行情况。一个事务快照ReadView的创建过程可以概括为：

m_ids：一个数值列表，用于维护 Read View 生成时刻系统正活跃的事务ID列表

up_limit_id：是m_ids活跃事务ID中的最小的事务ID

low_limit_id：ReadView 生成时刻系统尚未分配的下一个事务ID ，也就是目前已出现过的事务ID 的最大值 + 1

可见性比较算法

当事务执行快照读的时候，对该记录创建一个Read View读视图，用于记录此时的情景，把它比做条件用来判断当前事务可以看到哪个版本的数据，到底是看到最新版本，还是看到指向undo log日志中的历史版本呢

我们来一起看可见性算法，来决定该版本是否可见

算法的流程

1. 当行记录的事务ID小于当前系统的最小活动id，就是可见的。

if (trx_id < view->up_limit_id) {
return(TRUE);
}

2. 当行记录的事务ID大于当前系统的最大活动id，就是不可见的。

if (trx_id >= view->low_limit_id) {
return(FALSE);
}

3. 当行记录的事务ID在活动范围之中时，判断是否在活动链表中，如果在就不可见，如果不在就是可见的。

这里我也别用那些官方语言给大家解释了，我就举个简单的例子给大家解释

滴滴滴，跟上思路，加油，就快结束了

M_ids：一个数值列表，用于维护 Read View 生成时刻系统正活跃的事务ID列表

up_limit_id：是m_ids活跃事务ID中的最小的事务ID

low_limit_id：ReadView 生成时刻系统尚未分配的下一个事务ID ，也就是目前已出现过的事务ID 的最大值 + 1

插入一个记录，事务ID是10，此时版本链是10

执行一个update操作，事务ID是20，此时版本链是20-10，commit

执行一个update操作，事务ID是30，此时版本连是30-20-10，未Commit

执行select，事务ID是40，生成一个ReadView，这是一个镜像，此时可能已经有更多事务操作这条数据了，活跃列表是m_ids是[30]，最小事务up_limit_id也是30，最大事务low_limit_id是41

比较过程

按照这个ReadView的事务链30-20-10进行上述算法的比较，30不合适，因为在活跃事务中，20满足条件，所以此时事务ID为40的读取的就是ID为20更新的数据

事务ID30Commit，事务ID50执行update，链变成了50-30-20-10，未提交

关键

此时事务ID为40的再次执行了select操作，查询了该记录

如果事务隔离级别是已提交读隔离级别，这时候会重新生成一个新的ReadView，那此时ReadView已经变了，活跃列表m_ids是[50]，最小事务up_limit_id也是50，最大事务low_limit_id是51，于是按照上述比较，30便符合条件了，所以此时读出来的版本就是事务ID30的update数据了

如果事务隔离级别是可重复读，此时不会生成新的ReadView，用的还是开始时候生成的，所以还是20符合条件

两种隔离级别

我们上面说了MVCC只在READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，已提交读和可重复读的区别在于他们生成ReadView的策略不同

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

我们根据名字也可以推断，可重复读，如果每次读取的时候生成新的ReadView了，那符合条件的版本很可能就不一样了，所以查出来的也就不一样了，就不符合条件了，于是用的就是同一个ReadView

08 分库分表策略

当业务体量很大的时候，一个库无法满足数据量的需求，此时就需要多个库来协同工作，此时如何将数据库拆分便是一个值得思考的问题

垂直拆分

垂直拆分值得是把一张表的数据按照列分成多个表，通常是按照列的关系紧密程度来进行切分，也可以按照列的变动频率来进行切分

比如把更新频率低的列放到同一张表，把更新频率高的列放到同一张表中，这样也可以根据不同的更新频率来采用不同的规则

水平切分

水平切分，这个其实也很好理解，就是等同于是把同一个表的数据的记录拆分到多个结构相同的表中，其实就是把数据进行平均分散

这个大家应该很熟悉，在开发中经常遇到这类问题，基本就是属于平均分配的原理，比如负载均衡，再比如哈希，就那意思吧

当一个数据不断增加时，水平切分是必然的选择，这样可以将数据分布到不同的节点上，可以减轻单个数据库的压力

哈希取模是最基本的策略，理解起来也是比较简单的，熟悉HashMap的这些应该都很好理解，可以使用一个单独的数据库来存储映射关系，然后对这个数据库做好备份策略

作为一个程序员，跑不了hash，还不懂哈希算法的小伙伴，赶紧去复习一波哈希

切分的问题

系统中的事务问题一定要用分布式事务的思维来解决，而不能用普通的事务的思维来解决了，因为一旦事务操作了多张表，这多张表大概率不在同一个库中，我们必须做好万全之策，分布式事务便可以解决

ID的唯一性问题，这种就是即使分库分表，每个表中也会存在一个全局唯一的ID，可以为每一个分片指定一个ID范围，如果后续ID范围不够了，可以通过扩容来解决。也可以使用分布式的ID生成器来生成全局唯一的ID

09 JSON

json是一种常用的数据类型，这个大家应该都很熟悉

在MySQL数据库中，5.7版本之前是没有JSON这一数据类型的，需要依靠varchar或者text之类的数据类型来存储，而在5.7版本之后，MySQL则直接支持了JSON这一数据类型的存储结构

JSON列存储的要么是NULL，要么必须是JSON格式数据，否则会报错。JSON数据类型是没有默认值的。

JSON格式优点

保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。

MySQL同时提供了一组操作JSON类型数据的内置函数。where条件过滤可以直接基本json中的部分字段

更优化的存储格式，存储在JSON列中的JSON数据会被转成内部特定的存储格式，允许快速读取。

这是JSON内置函数连接：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

10 读写分离、主从复制

主从复制

MySQL主从复制涉及到三个线程，一个运行在主节点(log dump thread)，其余两个(I/O thread， SQL thread)运行在从节点

主节点 binary log dump 线程

当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

从节点I/O线程

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中

从节点SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性

读写分离

一般应用对数据库而言都是读多写少，也就说对数据库读取数据的压力比较大，有一个思路就是说采用数据库集群的方案，基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询

优点

1.主从复制，读写分离，增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务。

2.对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。

3.主从只负责各自的写和读，极大程度的缓解X锁和S锁争用，从库可配置myisam引擎，提升查询性能以及节约系统开销

4.从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的binlog恢复数据，但是，最重要区别在于主库向从库发送binlog是异步的，从库恢复数据也是异步的

5.读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。对于写和读比例相近的应用，应该部署双主相互复制。