Mysql 2020年整理一文全-用于自己看

MySQL逻辑架构

MySQL的一些文件

1. 二进制日志log-bin：用于主从复制。
2. 错误日志log-error：默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息等。
3. 查询日志show-log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的。
4. frm文件：存放表结构。
5. myd文件：存放表数据。
6. myi文件：存放表索引。
7. mysql配置文件：Windows下名为my.ini，Linux下为/etc/my.cnf。

整体架构图

Connectors连接层

1. 每一个Mysql客户端都对应服务器上的一个线程。服务器上维护了一个线程池。
2. 连接层复制对连接进行认证：用户密码认证或SSL证书认证。
3. 判断客户端角色是否有某个查询的权限。

服务层

系统管理和控制工具	说明
SQL Interface	接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户
Parser	分析SQL命令语法的合理性，并尝试将SQL语句进行分解成数据结构
Optimizer	对SQL命令按照标准流程进行优化分析，
Caches & Buffers	由一系列小缓存组成：表缓存，记录缓存，key缓存，权限缓存等

优化器：
优化器可以从数据字典中获取许多统计信息，例如表中的行数、表中的每个列的分布情况等。优化器可以根据这些信息选择有效的执行计划，而用户程序则难以获得这些信息。

Mysql缓存

1. MysqlServer 会对整条查询语句进行Hash计算，把得到的hash与Query查询的结果集放入QueryCache里。如果SQL语句只要相差哪怕一个字符,那么这两个SQL将使用不同的Cache地址。
2. 缓存失效：在表的结构或数据发生改变时，查询缓存中的数据不再有效。查询中包含一个不确定的函数(例如NOW())，或者查询结果太大时则无法使用缓存。
3. MySqlServer 自己管理内存的释放和分配，不通过操作系统。当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效。如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间。

引擎层

存储引擎真正的负责了 MySQL 中数据的存储和提取

存储层

数据存储层，实际存储MySQL 数据库文件和一些日志文件等的系统，如Linux，Unix,Windows等。

SQL流程

SQL的手写顺序

SELECT DISTINCT <Select_List>
FROM <left_table><join type>
JOIN <right_table> on <join_condition>
WHERE <Where_condition>
GROUP BY <Group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

SQL的执行顺序

FROM <left_table><join type>
JOIN <right_table> on <join_condition>
WHERE <Where_condition>
GROUP BY <Group_by_list>
HAVING <having_condition>
SELECT DISTINCT <Select_List>
ORDER BY <order_by_condition>
LIMIT <limit_number>

7种join语句

其他

1. MySQL临时表有两类：外部临时表(create temporary table,有表定义文件frm)和内部临时表(用于存储中间结果集)。临时表只在本次会话中有效，会话断开后数据会自动清理。
2. SQL分类：DDL：数据定义语言，DML：数据操纵语言 DCL：数据访问控制语言
3. MySQl支持跨存储引擎进行查询：SQL引擎在存储引擎之上，先用SQL引擎分别向不同存储引擎拉取数据，再在SQL层join操作。
4. 分区表：分区表由多个相关的底层表来实现,存储引擎管理分区的各个低层表和管理普通表一样,分区表的索引只是在各个底层表上各自加上一个完全相同的索引。对于SELECT/INSERT/DELETE/UPDATE：当查询一个分区表的时候,分区层先打开并锁定所有的底层表。优化器判断是否可以先过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据(所有的分区必须使用相同的存储引擎)。

创建一个HASH分区表的示例：

CREATE TABLE Demo (
 `ID` int(10) unsigned NOT NULL 
 `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `Desc` tinyint(4) NOT NULL COMMENT '描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='创建一个具有4张底层表且根据ID主键进行Hash分区的分区表'
PARTITION BY HASH(ID) PARTITIONS 4;

1. 视图：视图本身是一个虚拟表不存放任何数据。其中的数据是从其他表中生成的。视图的两种处理方法：合并算法(将视图SQL和查询SQL进行合并)和临时表算法(基于视图sql创建临时表),
2. 外键的成本：在修改数据时候都要在另外一张表中多执行一次查询
3. 内部存储代码：MySQL支持通过触发器,存储过程,函数等形式来存储代码
   优点：在服务器内部执行；节省网络；可以复用；可以提升安全。
   缺点：调试开发缺少相应的工具，库函数有限不适合开发复杂逻辑。
4. 触发器：在执行修改的时候,执行一些特定的操作,可用于反范式数据更新,每一个表的每一个事件最多只能定义一个触发器,仅支持"基于行的触发",如果数据集非常大的时候,效率非常低。

5. 三范式

   • 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。(例如地址里包含省份,城市,需单独拆开存储省份,城市)
   • 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。(保证了一张表只描述一件事情:例如一个表里既有学生信息,又有学生上课修的学分信息,数据冗余,更新数据的时候,需要重复更新)
   • 第三范式：设R是一个满足第二范式条件的关系模式，非主属性之间不存在依赖,简记为3NF.(表中的字段和主键直接对应不依靠其他中间字段，说白了就是，决定某字段值的必须是主键)。

数据库索引

索引树

1. 二叉搜索树：二叉树，每个结点只存储一个关键字，等于则命中。小于走左结点，大于走右结点。
2. B（B-）树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现且只出现一次，非叶子结点可以命中；所有叶子结点位于同一层。

1. B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

1. B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3。

索引的好处

1. 索引减少了服务器需要扫描的数据量
2. 索引帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

MySql为什么使用B+树

1. 读取代价低：B+树内部没有指向关键字的指针，因此内部节点相对于b树更小。那么一次性读入内存的关键字就越多，相对于IO读写次数降低了。
2. 查询效率更稳定：所有关键字查询的路径长度相同,每一个数据查询效率相当。
3. 方便扫库：b+树更适合在区间查询的情况,b树则需要一次中序遍历。

空间树索引(R树)

1. 在地图中，先将基本的数据用最小的方形覆盖，得到最最基本的最小矩形，将这些矩形作为R树的叶子节点。
2. 高一层级的处理：将相邻的多个最小矩形用一个矩形覆盖，生成的第二个矩形作为R树种叶子节点的父节点。
3. 依次类推，将最后一个最大的矩形作为R树的根节点。

自适应Hash索引

Innodb存储引擎会监控表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，InndoDB将会建立哈希索引,经常访问的二级索引数据会自动被生成到hash索引里面。

1. 哈希索引只能包含哈希值和行指针,不存储字段值
2. 哈希索引不能用于排序
3. 哈希索引不支持部分索引进行匹配查询
4. 哈希索引只支持部分索引匹配查找
5. 当哈希冲突的时候,维护代价就很高

聚集索引

InnoDB使用聚集索引

聚集索引并不是一种索引类型，而是一种数据存储方式，指实际数据行和相关的键值保存在一块。一个表只能有一个聚集索引。
InnoDB一定会创建聚集索引：InnoDB通常根据主键进行聚集。如果没有主键就通过唯一且不为空的索引列做主键进行聚集，否则就使用自己生成的虚拟的聚集索引。
聚集数据优点:

1. 把相关数据保存一起,减少磁盘IO
2. 数据访问更快

聚集数据缺点:

1. 插入速度严重依赖顺序插入，否则将面临严重的"页分裂"问题，页分裂会导致表占用更多的磁盘空间。
2. 二级索引里都默认包含了对应行的主健列,所以二级索引查询是两次查询：先查到主键，再通过主建去查对应的数据。

MyISM使用非聚集索引

1. 主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。
2. 表数据存储在独立的地方，主键索引和辅助索引的叶子节点都使用一个地址指向真正的表数据。
3. MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

覆盖索引

对于一次查询，建立索引的字段正好是覆盖该查询语句与查询条件中所涉及的字段，那么就称这一次的查询使用了覆盖索引。在聚集索引和非聚集索引结构中都可以使用覆盖索引。

索引类别:

1. 普通索引：仅加速查询
2. 唯一索引：加速查询 + 列值唯一（可以有null）
3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
5. 全文索引：对文本的内容进行分词，进行搜索。相关度是基于匹配的关键词个数,以及关键词在文档中出现的次数.出现次数越少的词语,匹配时的相关度就越高。

索引合并：使用多个单列索引组合搜索

B+树如何存储组合索引：
b+树在存储联合索引（A，B，C）时，非叶子节点仅仅是对第一个关键字段A的索引，叶子节点存储着kv的结构，key存储的是三个关键字A、B、C的数据，且按照A-B-C的顺序进行排序。value存储的是主键ID的值。

索引页操作

索引文件结构

1. 一个索引文件结构由多个段组成，每一个段和一个索引文件相关。
2. 一个段有多个区组成，每个区的默认大小为1MB
3. 一个区有多个页组成，一个页的默认大小为16KB
4. 一个也可以容纳N个行，InnoDB要求每一个页至少有2行记录。页内行记录按照主键ID来排序。
5. InnoDB管理的最小粒度是页，页加载进内存以后才会通过扫描页来获取行。
6. 故B+树的每一个叶子节点并非是行记录而且页记录。

页合并

1. 当删除一行记录时，实际上记录没有被物理删除，记录只是被标记为删除且它的空间可以被其他记录使用。
2. 当页内删除的记录达到一定值以后，InnoDB寻找临近的页，看是否可以将两个页合并。合并后调整上层的索引。
3. 一般update和delete操作会产生页合并。

页分裂

当页填充满了以后，需创建新的页进行调整：

1. 先创建新页
2. 判断当前页可以从哪里进行分裂，（记录行层面）
3. 移动记录行，然后重新定义页之间的顺序，然后调整索引。

页分裂发生在update和insert操作中，会造成页的在物理层面的存储是乱序的。可以通过OPTIMIZE命令去重新整理表，但这个过程会很耗费时间。同时也页分裂和页合并的过程会在索引树上加锁。

查询优化

Explain

Explain与SQL语句一起使用用来显示来自优化器关于SQL执行的信息。包括以下信息

1. 表的加载顺序
2. sql的查询类型
3. 可能用到的索引，哪些索引被实际使用。
4. 表与表之间的关系。

Explain 执行计划包含以下字段信息：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

id

id：表示查询中执行select子句或者操作表的顺序：id的值越大，代表优先级越高，越先执行，id值相同的执行顺序由上而下。

select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。由以下几个常用的值。

select_type	说明	示例SQL
SIMPLE	简单的 select 查询,查询中不包含子查询或者 UNION	SELECT * FROM t1
PRIMARY	当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY	SELECT * FROM (SELECT t1.content from t1) a
DERIVED	表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为DERIVED	SELECT * FROM (SELECT t1.content from t1) a
SUBQUERY	当 select 或 where 列表中包含了子查询，该子查询被标记为SUBQUERY	SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.id =2 )
DEPEDENT SUBQUERY	当 select 或 where 列表中包含了子查询且该赖外层是，该子查询被标记为DEPEDENT SUBQUERY	SELECT t2.id FROM t2 WHERE t2.id in (SELECT t3.id FROM t3 WHERE t3.content ="demo" )
UNCACHEABLE SUBQUERY	无法使用缓存的子查询,例如使用了@@来引用系统变量
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED	SELECT FROM t1 UNION SELECT from t2
UNION RESULT	从UNION表获取结果的SELECT	SELECT from ( SELECT FROM t1 UNION SELECT * from t2 )

table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type

type是查询的索引访问类型，结果值从最好到最坏依次是：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

结果值	说明	示例SQL
system	当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。
const	表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。	SELECT * FROM t1 WHERE id=1
eq_ref	唯一性索引扫描	SELECT * FROM t1,t2 where t1.id=t2.id
ref	非唯一性索引扫描	SELECT * FROM t1,t2 where t1.content=t2.content,content为普通索引
ref_or_null	非唯一性索引扫描，也需要null值扫描	SELECT * FROM t1,t2 where t1.content=t2.content or t2.content is null
index_merge	在查询过程中需要多个索引组合使用，通常出现在有OR的关键字的SQL中	SELECT * FROM t2 WHERE t2.name="demo" or t2.id =15
unique_subquery	利用唯一索引来关联子查询	SELECT * FROM t1 where t1.id in (SELECT t2.id from t2)
index_subquery	利用索引来关联子查询	SELECT * FROM t1 where t1.name in (SELECT t2.name from t2)
range	根据索引选择行，一般where 语句中出现 了 between、<、>、in 等的查询	SELECT * FROM t1 where id between （2，6）
index	在覆盖索引的情况下读了全表	SELECT Name from t1，Name为索引
ALL	在非覆盖索引的情况下读了全表	SELECT Name from t1,Name不是索引

possible_keys

表示在查询语句中使用到的索引，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。

key

在实际查询中使用到的索引。

key_len

key_len：表示查询用到的索引字节数，原则上长度越短越好 。

1. 单列索引，那么需要将整个索引长度算进去；
2. 多列索引，不是所有列都能用到，需要计算查询中实际用到的列。
3. key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。

ref

1. 当使用常量等值查询，显示const，
2. 当关联查询时，会显示相应关联表的关联字段
3. 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
4. 其他情况null

rows

rows 显示 MySQL 认为它执行查询时必须检查的行数，越少越好。

filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。

Extra

其他的额外重要的信息

值	说明
Using index	我们在相应的 select 操作中使用了覆盖索引
Using Where	出现using where，表明索引被用来执行索引键值的查找，没有出现using where 表明索引只是 用来读取数据而非利用索引执行查找
Using temporary	常见于查询后结果需要使用临时表来存储排序 order by 和分组查询 group by
Using filesort	MySQL 中无法利用索引 完成的排序操作称为“文件排序”
Using join buffer	在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
impossible where	where 子句的值总是 false

单表查询索引优化

全职匹配，索引最佳

查询的字段按照顺序在索引中都可以匹配到。若查询未按照索引顺序，但所有的字段都是索引，优化器会自动调整顺序。
例：索引为(A,B,C)查询顺序为(B,C,A),索引依旧有效。

最左前缀

查询从索引的最左前列开始并且不跳过索引中的列，一旦跳过某个字段，索引后面的的字段都无法被使用。

不在索引列上做计算

不要在索引列上做任何操作(计算、函数、隐式或显式类型转换)，会导致索引失效而转向全表扫描。

1. 查询语句中的等号左边不要做计算。
2. 查询语句中的等号右边不要做转换。

3. varchar类型查询中若不加引号，mysql或做一次隐式的类型转换，例如：name为varchar类型 。

   • where name=”1000“正常使用索引。
   • where name=1000 mysql会隐式的做一次类型转换。

索引列上不能有范围查询

例如 有一个组合索引（A,B,C）。查询 where A=1 and B >2 and C ="1" 仅能使用索引（AB）。

尽量使用覆盖索引

只访问索引查询（索引列和查询列一致），减少select*。

少使用不等于(!= ,<>, is not null,not in,not exist)

mysql 在使用不等于(!= ，<>， not in，not in，not exist)时，有时会无法使用索引会导致全表扫描。is not null会使索引失效，但is null 不会使索引失效。

减少使用or

建议使用 union all 或者 union 来替代。

非覆盖索引下，like不要以通配符开头('%abc...')

1. like 以通配符开头，在非覆盖索引下，会导致索引失效。
2. like 以非通配符开头，该索引以及后续索引都能生效。例如索引（A,B,C）。查询 where A=”b“ AND B like ”AB%“ AND c=”1“。可以使用索引（A，B，C）。
3. 覆盖索引的情况下like %XXX% 仍能使用索引。例如索引（A,B,C）。查询 SELECT A,B,C from Table where A like ”%b%“ 仍能使用索引A。原因是该查询仅在（A，B，C）是查询就能获得结果，无需二次回表。

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

多表查询优化

原则：小表驱动大表：即小的数据集驱动大的数据集

1. left join 时，左侧的为驱动表，右侧为被驱动表。被驱动表建立索引才有效果。
2. inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。

3. in 和exist的小表驱动大表原则。有以下两个等价查询。

   1. SELECT * FROM A where id in（select id from B）

      • 先执行B表的查询
      • 再执行A表的查询

   2. SELECT * FROM A where id exist（select 1 from B where A.id = B.id）

      • 先执行A表的查询
      • 通过A表的结果集，然后执行B表的查询进行筛选。
   3. 当A表的数据集远大于B表时，用in优于用exist。
   4. 当A表的数据集远小于A表时，用exist由于用in。

1. SELECT * FROM A where id exist（select 1 from B where A.id = B.id）语法可以理解为：将A表的查询结果，放到子查询中做验证，根据验证结果来决定主查询的数据结果是否要保留。
2. mysql在执行exist的子查询时会忽略SELECT清单，因此子SELECT清单对最后的筛选没有影响。

排序分组分页优化。

一个原则：mysql支持两种方式的排序：文件排序（FileSort）和索引排序（Index），Index排序效率更高。

尽量使用索引排序

1. 如果查询条件没有where limit这些过滤条件，那么order by无法使用索引排序。
2. 如果order by使用的排序字段不符合索引的最左前缀原则，那么order by无法使用索引排序。

3. 如果order by使用的排序字符符合最左前缀原则，但每一个字段的升降序不统一，则无法使用索引排序。

   • 例：有索引(A,B)。查询select * from Table where <Condtion> order by A asc,B desc 无法使用索引排序。

4. 如果Where子句和Order BY 子句条件列组合满足索引最左前列，则可以使用索引排序。

   • 例：有索引(A,B)。查询 explain select * from Table where A = "aa" order by B可以使用索引排序。

文件排序的两个实现算法

1. 多路排序，Mysql4.1之前。两次扫描磁盘，两次IO非常耗时。

   1. 第一次扫描磁盘读取主键和Orderby列，在buffer中对他们进行排序。若buffer不够会使用文件进行排序。
   2. 根据排序好的主键再从磁盘上获取select列并返回结果。

2. 单路排序，直接从磁盘中取出select列和orderby列。在buffer中直接对他们进行排序然后返回结果，一次IO操作。

   • 但它会耗费更多的内存空间。若内存空间不够，则需要多次取出数据在buffer中排序，排序完以后存入文件，然后对多个结果集合进行归并排序。
   • 可以减少select后的字段或增大buffer的空间，以减少buffer空间不够的情况。

分组优化

分组之前一般都要进行一次排序。分组优化索引原则基本同排序优化原则。唯一区别：groupby即使没有过滤条件也可以用到索引排序。

分页优化

limit原理：limit n，m：先读取前N+m条数据，然后抛弃前n条，读取后面的m条。
优化原理：利用表的覆盖索引来加速分页查询。
select * from DEMO where Condition limit 10000,20
优化方案：

1. select * from DEMO where id >= （select ID from DEMO where Condition limit 10000,1 ）limit 20。
2. select * from DEMO d right join (select ID from DEMO where Condition limit 10000,20) t on d.id = b.id 。
3. select * from DEMO where id > order by id limit 10000，20（尽量只使用主键ID）。

mysql服务状态命令

show profile

1. 利用show profile可以查看sql的执行周期。
2. 在使用profile之前需开启profile功能（set profiling =1）。
3. show proflie命令可以列出最近N（默认15，通过profile_history_size控制）条SQL的执行细节。
4. 诊断SQL：show profile cpu,block io for query id

5. 需关注的show profile结论

   1. converting heap to myisam.查询结果太大，内存不够，使用磁盘。
   2. creating tmp table 创建了临时表
   3. copying to tmp table on disk。把内存中的临时表的数据复制到磁盘。
   4. locked 锁

show processlist

显示当前连接到mysql的连接或者线程的状态。root用户可以看到所有的连接，其他用户只能看见自己的线程。

show status:

1. 用于查询mysql状态变量相关统计信息。
2. 通过 show status like 查看部分变量
3. show status 只显示会话级别的，可以通过 show global status 查看全局状态。

show engine innodb status

输出了关于 InnoDB 一些平均值的统计信息。

1. BACKGROUND THREAD 后台线程状态信息。
2. LATEST DETECTED DEADLOCK 最近一次死锁信息。
3. TRANSACTIONS事务统计相关信息。
4. FILE I/O 文件io信息。
5. INSERT BUFFER AND ADAPTIVE HASH INDEX 插入缓存（用于非聚集索引）和自适应哈希索引
6. LOG：innodb的重做日志统计。

数据库锁

表锁（偏读）

加表锁[读锁或写锁]

LOCK TABLES tableName WRITE|READE

解锁

UNLOCK TABLES

行锁（偏写）

1. 索引失效会导致行锁升级到有表锁。

2. 间隙锁是Innodb在可重复读提交下为了解决泛读问题而引入的锁机制。间隙锁锁定间隔，防止间隔中被其他事务插入。间隙锁是行锁的一种，只适用于索引有效的情况。

   1. 对于范围查询，会锁定整个范围。
   2. 对于等值查询，若查询的数据不在数据库内，则会寻找一个小于当前值的最大数据left，和大于当前值的最小数据right;然后锁定范围(left,right]

3. 锁一行记录：

   • SELECT....LOCK IN SHARE MODE(不属于SQL规范，加读锁)
   • SELECT....FOR UPDATE (不属于SQL规范，加写锁)
4. InnoDB处理死锁的方式：将持有最少行级排他锁的事务进行回滚。

数据库日志

mysql 一致性日志

innodb引擎中，根一致性相关的日志有重做日志（redolog），回滚日志（undolog），二进制日志（binlog）。

• redo log：当有数据写请求时，在数据真正更改前，innodb会先把相关操作写入redo日志。这样如果数据库宕机恢复后仍能继续完成相关修改。
• undo log：记录事务开始前的数据状态，当事务进行了一半时数据库宕机恢复后能够正常回滚事务。
• binlog：MySQL Server层维护的一种二进制日志，记录了所有的DDL和DML语句，binlog主要是用于数据库的复制和恢复。
• checkpoint：当遇到内存不足、db buffer已满等情况时，需要将db buffer中的内容/部分内容转储到data file中。在转储时，会记录checkpoint发生的”时刻“。在故障恢复时候，只需要redo/undo最近的一次checkpoint之后的操作。

binlog格式:

1. statement：基于语句的复制:备库把sql再执行一遍
   好处：二进制日志里的事件更加紧凑,节省宽带
   坏处：sql包含了动态函数会导致数据不一致(CURRENT_USER()函数),存储过程和触发器也会存在问题。
2. RAW:基于行的复制:把实际数据记录到二进制日志中

好处：可以正确的复制每一行,
坏处：对于更新范围大的事件,日志事件会很庞大,若修改表结构,则会出现问题,同时会产生大量日志

1. 混合模式:一般的复制使用语句模式,对于语句无法复制的操作使用ROW模式。

数据库事务

ACID:

1. 原子性：事务是一个不开分割的单元。
2. 一致性：数据库总是从一个一致性转换到另一个一致性。
3. 隔离性：各个事务的操作是互相隔离的。
4. 持久性:一旦事务提交,其所做的修改就会永远保存到数据库

ACID实现：

1. 如何保证原子性：通过Undo日志实现为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
2. 如何保证持久性：（Innodb Doublewrite）Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。InnoDB的日志是环形方式写的，InnoDB会使用一个后台线程把日志的内容写入到数据文件。
3. 如何保证隔离性：通过MVCC和数据库锁机制保证隔离性。
4. 如何保证一致性：通过原子性和持久性以及隔离性来保证了一致性。

四种隔离级别

1. 读未提交内容：事物的修改即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，也被称为脏读。
2. 读提交内容：一个事务从开始到提交前，所做的修改对其他事务都是不可见的，也叫不可重复读。因为两次执行查询的结果,有可能是不一样的。
3. 可重复读：一个事务中多次读取同样的记录是一致的。无法解决幻读：指某个事务在读取某个范围记录的时候另外一个事务又在该范围插入了新的记录。当之前的事务再次读取该范围的记录，会产生幻读.
4. 可串行化：通过强制事务串型执行,避免幻读问题,通过表锁和间隙锁来解决幻读。

默认的级别为重复读。

MVCC实现方式

Innodb事务实现方式:MVCC有关的字段：提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败
DATA_TRX_ID:
DATA_ROLL_PTR
实现方式:
SELECT:只查找版本早于当前事务版本的数据行,可确保事务读取的行,要么是在事务开始前已经存在,要么是事务自身插入或修改过的.
,行的删除要么未定义要么大于当前事务版本号.
INSERT:为新插入的每一行保存当前系统版本号作为插入标识
DELETE:为删除的每一行保存当前系统版本号作为行删除标识
UPDATE:新插入一行记录,保存当前的系统版本好,同时保存当前的系统版本号作为原来行的删除标记
MVCC只在可重复读,提交读.
串行化会对所有读取的行都加锁.

事务日志实现

数据库字段类型

1. Null类型：可为Null的列会使用更多的存储空间,当可为NULL的列被索引时,每一个索引记录都需要一个额外的字节。
2. DATETIME(9999年)和TIMESTAMP(2038年)都可以存储时间类型精确到秒。但TIMESTAMP只使用DATETIME的一半存储空间，并且会根据时区变化，具有特殊的自动更新能力。但TIMESTAMP允许的时间范围要小的得多。

3. 整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT分别使用8,16,24,32,64位存储空间,

   1. UNSIGNED属性表示不允许负值,int(M) 中的M指示最大显示宽度，最大有效显示宽度是 255，且显示宽度与存储大小或类型包含的值的范围无关。

4. VARCHAR：存储可变长字符串，仅使用必要的空间。

   1. VARCHAR需要一个或者两个来记录字符串的长度。
   2. varchar可能导致UPDATE时使行变的更长，存储引擎需要做一些额外的工作.
5. CHAR类型是定长的：存储时,mysql会删除所有的末尾空格。char适合存储很短的字符串或者所有的字符串都接近同一个长度(例如MD5值)
6. DECIMAL(18.9):一共(18/9)*4+1=9个字节(小数位置本身占一个字节)

7. BLOB/TEXT类型：

   1. 为存储很大的数据而设计的，分别采用二进制和字符串方式。
   2. MySQL把每个BLOB和TEXT值当一个独立的对象处理，当BLOB或TEXT太大时MySQL在行内存储一个1~4字节的指针，然后在外部存储实际的值。
   3. MySQL不能对全部长度进行索引,MySQL提供INET_ATON()和INET_NTOA()表示IP地址数字和字符串之间的转换

8. mysql中的数据类型enum

   1. 单选字符串数据类型，适合存储表单界面中的“单选值”。

　　 2. 设定enum的时候，需要给定“固定的几个选项”；存储的时候就只存储其中的一个值.

1. mysql中的数据类型set。

   1. 多选字符串数据类型，适合存储表单界面的“多选值”
   2. 设定set的时候，同样需要给定“固定的几个选项”；存储的时候，可以存储其中的若干个值。

主从复制

主从复制的作用

1. 做数据的热备,主库故障后可以切换到备库继续工作。
2. 架构的扩展,做多库的存储,降低IO访问的频率。
3. 读写分离,使数据库能支撑更大的并发。

复制模式

1. 异步模式：MySQL 主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果，不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中。
2. 全同步复制：主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会受到严重的影响。
3. 半同步复制：主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端.若等待时间超过某一个配置则半同步自动关闭，转为异步复制。
4. 增强半同步复制，MySql5.7对半同步复制的增强:主库在收到一个从库的ack以后再把事务commit.在commit之前等待Slave ACK。同时MySQL5.7的半同步复制有单独的应答接收线程，变成了双工模式，发送和接收互不影响，可以堆积事务利于group commit,有利于提升性能。

主从延时

1. master对DDL和DML产生binlog,
2. slave的Slave_IO_Running线程读取主库日志.
3. slave的Slave_SQL_Running是单线程(只有一个SQL线程来重放中继日志中的事件)的.

当主库的产生的DDL数量超过了slave的一个线程所能承受的范围时,会产生延时.

• 主库:binlog dump线程:用于生成binlog日志
• 从库:I/O线程获取主库的binlog日志

SQL线程,重放日志

1. 慢SQL语句过多,导致主从延时
2. 从库硬件比主库差
3. 从库太多导致复制延时(网络竞争),从库太多也会对主库的性能带来压力,因为各个从库不共享binlog dump资源。
4. 写sql数据量比较大,从库单线程执行复制
5. 网络延时

1. 并行复制一定程度降低主从延时:从库库级别并行应用binlog，同一个库数据更改还是串行的。

测量备库延迟时间:

1.seconds_behind_master:通过服务当前的时间戳和二进制日志事件的时间戳做对比得到(若没有事件,则无法测量出延时,主库大事务提交,导致延时计算不正确)
2.直接用备库的时间戳减去心跳记录的值

复制过程:

1. 主库把数据更改记录到二进制日志文件，MYSQL会按照事务提交的顺序来记录二进制日志中。记录完成后主库通知存储引擎提交事务。
2. 备库将主库上的日志复制到自己的中继日志中

备库启动一个IO线程和主库建立一个连接(使用普通的用户名账号)。主库启动一个特殊的二进制转储线程，如果该线程追赶上了主库，它将进入睡眠状态直到有其他的新事件产生。

1. 备库读取中继日志中的事件，将其重放

备库的SQL线程从中继日志中读取事件并在备库执行。

InnoDB和MyISAM的区别

1. InnoDB支持事务，MyISAM不支持。
2. InnoDB(Mysql中唯一支持的内置存储引擎)支持外键，而MyISAM不支持。
3. InnoDB支持表锁和行级锁。MyISAM支持表级锁。尽管myisam是表级锁,它依然可以一边读取一边并发追加新的行。
4. InnoDB是聚集索引，B+tree作为结构。MyISAM是非聚集索引,使用B+tree作为数据结构。InnoDB必须有主键,MyISAM可以没有。
5. InnoDB不保存表的具体行数，而MyISAM用变量保存了行数。
6. InnoDB不支持全文索引，MyISAM支持全文索引。
7. MyISAM表可以被压缩以后进行查询。
8. InnoDB使用日志减少提交事务时的开销。