拉勾教育学习-笔记分享のMySQL"渡劫" II

【文章内容输出来源：拉勾教育Java高薪训练营】
--- 所有脑图均本人制作，未经允许请勿滥用 ---
MySQL作为当下核心的关系型数据库管理工具，除了多思考多实战，别无他法

---

傲不可长，欲不可纵，乐不可极，志不可满

---

一、MySQL 索引原理

• 从索引存储结构划分：
  • B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：
  • 普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：
  • 主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：
  • 聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

part 1 - 索引类型

「普通索引」

CREATE INDEX <索引名> ON table_name;
ALTER TABLE table_name ADD INDEX [索引名] (字段名);
CREATE TABLE table_name ([...], INDEX [索引名] (字段名));

「唯一索引」

和普通索引的区别是：索引字段必须唯一，但允许空值。（创建或修改表时追加唯一约束，就会自动创建对应的唯一索引）

CREATE UNIQUE INDEX <索引的名字> ON table_name (字段名);
ALTER TABLE table_name ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE table_name ( [...], UNIQUE [索引的名字] (字段名) ;

「主键索引」

特殊的唯一索引，不允许空值。（在创建或修改表时追加主键约束即可，每个表只能有一个主键）

CREATE TABLE table_name ( [...], PRIMARY KEY (字段名) );
ALTER TABLE table_name ADD PRIMARY KEY (字段名);

「复合索引」

和单一索引（只实施在一列的索引）区别，可以实施在多列上。

• 窄索引：1~2列的索引
• 宽索引：2+列的索引

CREATE INDEX <索引的名字> ON table_name (字段名1，字段名2...);
ALTER TABLE table_name ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE table_name ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

「全文索引」

LIKE 模糊查询在数据量较多时，效率很低，需要使用全文索引。

CREATE FULLTEXT INDEX <索引的名字> ON table_name (字段名);
ALTER TABLE table_name ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE table_name ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

全文索引使用方式: (使用 match 和 against)

SELECT * FROM user WHERE match(name) against('aaa');

【注意事项】：

1. 全文索引必须在 字符串、文本字段 上建立。
2. 全文索引字段值必须在最小字符和最大字符之间的才会有效。(InnoDB:3-84 / MyISAM:4-84)
3. 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
4. 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索 a*
   SELECT * FROM user WHERE match(name) against('a*' in boolean mode);

part 2 - 索引原理

索引定义：存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

「二分查找」

(略)

「Hash结构」

MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

--------InnoDB 自适应哈希索引---------

InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页.

InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉:

show variables like '%innodb_adaptive%';

「B+ Tree」

MySQL数据库索引采用的是B+Tree结构，在B Tree结构上做了优化改造

--------B Tree---------

• 索引值和data数据分布在整棵树结构中
• 每个节点可以存放多个索引值及对应的data数据
• 树节点中的多个索引值 从左到右升序排列

从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。
没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束

--------B+ Tree---------

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
• 叶子节点包含了所有的 索引值 和 data数据
• 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。
而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高

「聚簇索引 & 辅助索引」

聚簇索引：B+Tree的叶子节点 集中存放 主键索引值和行记录
非聚簇索引：B+Tree的叶子节点 分开存放 主键索引值和行记录

主键索引：B+Tree的叶子节点存放 主键字段值
辅助索引：B+Tree的叶子节点存放 非主键字段值

--------聚簇索引（聚集索引）---------

是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。
这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。
通常说的主键索引就是聚集索引。

InnoDB的表必须由聚簇索引！

1. 有主键，则 主键索引 即 聚簇索引；
2. 无主键，则 首个非空unique列 为 聚簇索引；
3. 均不满足时，InnoDB自己创建一个隐藏的 row-id 作为 聚簇索引。

--------辅助索引---------

一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引

part 3 - 索引分析及优化

「EXPLAIN」

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信 息，供开发人员有针对性的优化。

EXPLAIN SELECT * FROM user WHERE id = 2;

• select_type
  • SIMPLE : 查询语句不包含子查询或UNION
  • PRIMARY : 最外层查询
  • UNION : 是UNION的第二个或后续查询
  • DEPENDENT UNION : 使用外部查询结果的UNINO查询
  • UNION RESULT : UNION的结果
  • SUBQUERY : SELECT子查询结果
  • DEPENDENT SUBQUERY : SELECT子查询语句依赖外层查询的结果
• type: 表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描
  • ALL : 全盘扫描（性能最差）
  • index : 基于索引的全盘扫描，先扫描索引 再扫描全盘
  • range : 使用索引范围查询，使用了> \ >= \ < \ <= \ in ...
  • ref : 使用非唯一索引进行单值查询
  • eq_ref : 多表join查询，前面表的每一个记录，都只能匹配后面表的一行结果
  • const : 使用主键或唯一索引做等值查询，常量查询
  • NULL : 不用访问表（速度最快）
• possible_keys：查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
• key：查询时真正使用到的索引，显示的是索引名称。
• rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。
  原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
• key_len: 查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
  计算规则如下：
  • 字符串类型 : 字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
    • char(n)：n * 字符集长度
    • varchar(n)：n * 字符集长度 + 2字节
  • 数值类型 :
    • TINYINT：1个字节
    • SMALLINT：2个字节
    • MEDIUMINT：3个字节
    • INT、FLOAT：4个字节
    • BIGINT、DOUBLE：8个字节
  • 时间类型 :
    • DATE：3个字节
    • TIMESTAMP：4个字节
    • DATETIME：8个字节
  • 字段属性 : NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。
• Extra：很多额外的信息，各种操作会在Extra提示相关信息
  • Using where：查询需要通过索引回表查询数据
  • Using index：查询需要通过索引，索引就可以满足所需数据
  • Using filesort：查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有 Using filesort 时建议优化
  • Using temproray：使用到了临时表，一般出现于去重、分组等操作

「回表查询」

InnoDB索引有聚簇索引和辅助索引。
聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。
辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录。
通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

「覆盖索引」

explain的输出结果 Extra字段 为 Using index 时，能够触发索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

「最左前缀原则」

针对复合索引
查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效

「LIKE 查询」

面试题：MySQL在使用like模糊查询时，索引能不能起作用？
回答：MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name like '%o%'; // 不起作用
select * from user where name like 'o%'; // 起作用
select * from user where name like '%o'; // 不起作用

「NULL 查询」

面试题：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？
回答：有效，但不建议！

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。

• 不能使用 =，<，>这样的运算符
• 对NULL做算术运算的结果都是NULL
• count时不会包括NULL行
• NULL比空字符串需要更多的存储空间

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。
最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。

「索引与排序」

查询中的两种排序

1. filesort：先把结果查出，然后在缓存或磁盘进行排序操作，效率较低
   1. 双路排序：两次磁盘扫描读取，最终得到用户数据。
      第一次将排序字段读取出来，然后排序；
      第二次去读取其他字段数据。
   2. 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。
      如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。
      所以==> 少使用select *增加sort_buffer_size容量和max_length_for_sort_data容量 以下是实现 filesort 的情况：
   • 对索引列同时使用了ASC和DESC：

   explain select id from user order by age asc,name desc; //对应 (age,name)索引
   

   • WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）：

   explain select id from user where age>10 order by name; //对应 (age,name)索引
   

   • ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列：

   explain select id from user order by name; //对应(age,name)索引
   

   • 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引：

   explain select id from user order by name,age; //对应(name)、(age)两个索 引
   

   • WHERE子句与ORDER BY子句，使用了不同的索引：

   explain select id from user where name='tom' order by age; //对应 (name)、(age)索引
   

   • WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式：

   explain select id from user order by abs(age); //对应(age)索引
   

2. index：利用索引自动实现排序，不需另做排序操作，效率较高
   以下是实现index的情况：
   • ORDER BY 子句索引列组合满足索引最左前列：

   explain select id from user order by id; //对应(id)、(id,name)索引有效
   

   • WHERE子句+ORDER BY子句索引列组合满足索引最左前列：

   explain select id from user where age=18 order by name; //对应 (age,name)索引
   

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。
如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，建议尽可能采用覆盖索引。

part 4 - 查询优化

「慢查询定位」

----开启慢查询日志----

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令

SHOW VARIABLES LIKE 'slow_query_log%'

开启慢查询日志

SET global slow_query_log = ON; 
SET global slow_query_log_file = 'OAK-slow.log'; 
SET global log_queries_not_using_indexes = ON; // 是否记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。
SET long_query_time = 10; // 指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中

----查看慢查询日志----

1. 直接使用编辑器打开 slow.log 日志：（日志路径可使用show variables like '%slow_query_log_file%';查询）
   • time：日志记录的时间
   • User@Host：执行的用户及主机
   • Query_time：执行的时间
   • Lock_time：锁表时间
   • Rows_sent：发送给请求方的记录数，结果数量
   • Rows_examined：语句扫描的记录条数
   • SET timestamp：语句执行的时间点
   • select....：执行的具体的SQL语句
2. 使用 mysqldumpslow 查看：
   在 MySQL bin目录下执行perl mysqldumpslow --help可以查看使用格式
   • 查看日志技巧：

   #得到返回记录集最多的10个SQL。 
   mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log 
   #得到访问次数最多的10个SQL 
   mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log 
   #得到按照时间排序的前10条里面含有左连接的查询语句。 
   mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log 
   #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 
   mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more 
   

「慢查询优化」

如何判断是否慢查询：
运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10(s)。
如何判断是否应用索引：
根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL

• 是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，和是慢查询两者之间没有必然的联系。
• 我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快

慢查询原因总结

1. 全表扫描：explain分析type属性all
2. 全索引扫描：explain分析type属性index
3. 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
4. 频繁的回表查询开销：尽量少用select *，使用覆盖索引

「分页查询优化」

问题点一：

select * from user limit 10000,1; 
select * from user limit 10000,10; 
select * from user limit 10000,100; 
select * from user limit 10000,1000; 
select * from user limit 10000,10000;
......

返回记录量低于100条，查询时间基本没有变化。随着查询记录量越大，所花费的时间也会越来越多！

问题点二：

select * from user limit 1,100; 
select * from user limit 10,100; 
select * from user limit 100,100; 
select * from user limit 1000,100; 
select * from user limit 10000,100;
......

偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

优化方案：

1. 利用覆盖索引优化：
   • select * from user limit 10000,100; ×
   • select id from user limit 10000,100; √
2. 利用子查询优化：
   • select * from user limit 10000,100; ×
   • select * from user where id>= (select id from user limit 10000,1) limit 100; √√

二、MySQL 事务和锁

part 1 - ACID 特性

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）

4个特性中有3个与 WAL 有关系，都需要通过 Redo、Undo 日志来保证

*WAL的全称为 Write-Ahead Logging，先写日志，再写磁盘。

「A 原子性」

对数据的修改，要么全都执行，要么全都不执行

• 事务提交了，如果此时Buffer Pool的脏页没有刷盘，如何保证修改的数据生效？ Redo
• 如果事务没提交，但是Buffer Pool的脏页刷盘了，如何保证不该存在的数据撤销？Undo

每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中
==> 如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。
==> 如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了。

「C 一致性」

事务开始之前和事务结束之后，数据库的完整性限制未被破坏。

1. 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持 Check 。
2. 数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。

「I 隔离性」

一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的

隔离性从低到高:
读未提交 -> 读已提交 -> 可重复读 -> 可串行化
锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面详解）

「D 持久性」

是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失

如下图所示，一个“提交”动作触发的操作有：
binlog落地、发送binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。
这些都是数据库保证其数据完整性、持久性的手段。

part 2 - 事务控制的演进

「事务并发所带来的问题」

• 更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。
  • 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
  • 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。
• 脏读： 一个事务读取到了另一个事务修改但未提交的数据。
• 不可重复读： 一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。
• 幻读： 一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。

「排队」

完全顺序执行所有事务的数据库操作，不需要加锁，数据库某个时刻只处理一个事务操作

「排它锁（互斥锁）」

先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁

「读写锁」

细化读写操作。
读读并行
而读写、写读、写写 这几种之间还是要加排他锁

「MVCC」

多版本控制MVCC（Multi Version Concurrency Control），也就是Copy on Write的思想。
支持 读读、读写、写读 并行；（为了保证一致性，写写无法并行）

事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行

----MVCC定义----

在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本
多版本控制很巧妙地将 稀缺资源的独占互斥 转换为 并发，大大提高了数据库的吞吐量及读写性能

每次事务修改操作之前，都会在 Undo日志 中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚

如果想进一步解决写写冲突,可以采取 乐观锁/悲观锁 。

part 3 - 事务隔离级别

• 读未提交 Read Uncommitted ：解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。
• 已提交读(Oracle、SQLServer默认) Read Committed ：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。
• 可重复度(MySQL默认) Repeatable Read ：解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。
• 可串行化 Serializable ：所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

• 数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）
• 对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁

查看MySQL当前数据库的事务隔离级别:

show variables like 'tx_isolation';
# 或
select @tx_isolation;

设置事务隔离级别：

set tx_isolation='READ-UNCOMMITTED'; 
set tx_isolation='READ-COMMITTED'; 
set tx_isolation='REPEATABLE-READ'; 
set tx_isolation='SERIALIZABLE';

part 4 - 锁机制和实战

「锁分类」

• 从操作粒度：
  • 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
  • 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
  • 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。
• 从操作类型：
  • 读锁(S锁)：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响
    事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放
  • 写锁(X锁)：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁
    事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作
  • 意向读锁(IS锁)、意向写锁(IX锁)：属于表级锁，在对应的S\X锁前，会对表添加 意向锁。
• 从操作性能：
  • 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息
  • 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前 先锁定，再修改 的控制方式。
    (共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴)

「行锁原理」

• Record Lock 锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
• Gap Lock 锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

1. select ... from 语句：InnoDB引擎采用 MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
2. select ... from lock in share mode 语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
3. select ... from for update 语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
4. update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
5. delete ... from 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁
6. insert 语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁

「悲观锁」

定义：在数据处理过程，将数据处于锁定状态
广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴

----表级(悲观)锁----

手动增加表锁

lock table 表名称1 read|write,表名称2 read|write, ...;

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞

----共享行级(悲观)锁----

共享锁又称为读锁，简称S锁。
共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
使用共享锁的方法是 select ... lock in share mode，只适用查询语句。

事务若使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞

----排他行级(悲观)锁----

排他锁又称为写锁，简称X锁。
不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁
使用排他锁的方法是在SQL末尾加上 for update，innodb引擎默认会在update，delete语句加上for update。
行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。

事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select... for update）。如果查询没有使用到索引，将会锁住整个表记录。

「乐观锁」

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。
在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。

乐观锁实现的关键点：冲突的检测

• 乐观锁通用实现方式：
  • 使用版本字段（version）
    先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改
  • 使用时间戳（Timestamp）
    与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

↓ 通过SQL手动实现乐观锁 ↓：

1. 查询商品信息：

select (quantity,version) from products where id=1;

2. 根据产品信息生成订单：

insert into orders ... 
insert into items ...

3. 修改商品库存：

update products set quantity=quantity-1,version=version+1 where id=1 and version=#{version};

除了自己手动实现乐观锁之外，许多数据库访问框架也封装了乐观锁的实现
比如MyBatis框架大家可以使用 OptimisticLocker插件 来扩展。

「死锁与解决办法」

--------表级死锁--------

• 产生原因：
  用户X--》A表（表锁）--》B表（表锁）
  用户Y--》B表（表锁）--》A表（表锁）
• 解决方案：这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法

--------行级死锁--------

• 产生原因1：事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁
• 解决方案1：SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化
• 产生原因2：两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁
• 解决方案2：
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源
  • 按照id对资源排序，然后按顺序进行处理

--------共享锁转换为排他锁--------

• 产生原因：事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A: select * from dept where deptno=1 lock in share mode; //共享锁,1
事务A: update dept set dname='java' where deptno=1;//排他锁,3
-------------------------------------------------------------
事务B: update dept set dname='Java' where deptno=1;//由于1有共享锁，没法获取排他锁，需等待，2

• 解决方案：
  • 对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作
  • 使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。

--------死锁排查--------

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

1. 查看死锁日志：
   • show engine innodb status 命令查看近期死锁日志信息
2. 查看锁状态变量:
   • show status like'innodb_row_lock% 命令检查状态变量，分析系统中的行锁的争夺情况
     • Innodb_row_lock_current_waits：当前正在等待锁的数量
     • Innodb_row_lock_time：从系统启动到现在锁定总时间长度
     • Innodb_row_lock_time_avg： 每次等待锁的平均时间
     • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
     • Innodb_row_lock_waits：系统启动后到现在总共等待的次数

三、MySQL 集群架构

part 1 - 主从模式

MySQL主从模式是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，从节点可以复制主数据库中的所有数据库，或者特定的数据库，或者特定的表。

• 主从复制用途：
  • 实时灾备，用于故障切换（高可用）
  • 读写分离，提供查询服务（读扩展）
  • 数据备份，避免影响业务（高可用）
• 主从部署必备条件：
  • 从库服务器能连通主库
  • 主库开启binlog日志（设置log-bin参数）
  • 主从server-id不同

「实现原理」

1. 主库将数据库的变更操作记录到Binlog日志文件中
   • BinlogDump Thread 接到写入请求后，读取 Binlog信息 推送给 Slave的 I/O Thread。
2. 从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
   • Slave 的 I/O Thread 将读取到的 Binlog信息写入到本地 Relay Log 中
3. 从库读取中继日志信息在从库中进行Replay,更新从库数据信息
   • Slave 的 SQL Thread 检测到 Relay Log 的变更请求，解析 Relay log 中内容在从库上执行

【主从复制问题】

• 主库宕机后，数据可能丢失
• 从库只有一个SQL Thread，主库写压力大，复制很可能延时 【解决方法】：
• 半同步复制---解决数据丢失的问题
• 并行复制----解决从库复制延迟的问题

「半同步复制」

为了提升数据安全，MySQL让Master在某一个时间点等待 Slave节点的 ACK（Acknowledge character）消息，接收到ACK消息后才进行事务提交，这也是半同步复制的基础
MySQL从5.5版本开始引入了半同步复制机制来降低数据丢失的概率

「并行复制」

5.6+ 引入并行复制 MTS （enhanced multi-threaded slave）
在从库中有两个线程IO Thread和SQL Thread，都是单线程模式工作，因此有了延迟问题，我们可以采用多线程机制来加强，减少从库复制延迟。（IO Thread多线程意义不大，主要指的是SQL Thread多线程）

------MySQL 5.6并行复制------

只是基于库的并行复制。（伪并行复制）

------MySQL 5.7并行复制------

基于组提交的并行复制。（开始真正的并行复制）

通过对事务进行分组，当事务提交时，它们将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行，所以通过在主库上的二进制日志中添加组提交信息。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：DATABASE（默认值，基于库的并行复制方式）、LOGICAL_CLOCK（基于组提交的并行复制方式）。

------MySQL 8.0并行复制------

基于 write-set 的并行复制。

MySQL会有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。
这样的粒度，就到了 row 级别了，此时并行的粒度更加精细，并行的速度会更快。

------并行复制配置与调优------

• binlog_transaction_dependency_history_size 用于控制集合变量的大小。
• binlog_transaction_depandency_tracking 用于控制binlog文件中事务之间的依赖关系，即last_committed值。
  • COMMIT_ORDERE: 基于组提交机制
  • WRITESET: 基于写集合机制
  • WRITESET_SESSION: 基于写集合，比writeset多了一个约束，同一个session中的事务 last_committed 按先后顺序递增
• transaction_write_set_extraction 用于控制事务的检测算法，参数值为：OFF、 XXHASH64、MURMUR32
• master_info_repository 开启MTS功能后，务必将参数 master_info_repostitory 设置为 TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元 master.info 这个文件的更新将会大幅提升，资源的竞争也会变大。
• slave_parallel_workers
  • 若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制
  • 若将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程
  • 但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此 slave_parallel_workers=1 的性能反而比 0 还要差。
• slave_preserve_commit_order MySQL 5.7后的MTS可以实现更小粒度的并行复制，但需要将 slave_parallel_type 设置为LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。

开启 enhanced multi-threaded slave:

slave-parallel-type=LOGICAL_CLOCK 
slave-parallel-workers=16 
slave_pending_jobs_size_max = 2147483648 
slave_preserve_commit_order=1 
master_info_repository=TABLE 
relay_log_info_repository=TABLE 
relay_log_recovery=ON

------并行复制监控------

mysql> show tables like 'replication%'; 

通过 replication_applier_status_by_worker 可以看到worker进程的工作情况：
select * from replication_applier_status_by_worker;

「读写分离」

将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过主从复制机制进行数据的同步

• 在应用中可以在从库追加多个索引来优化查询，主库这些索引可以不加，用于提升写效率。
• 读写分离架构也能够消除读写锁冲突从而提升数据库的读写性能。使用读写分离架构需要注意：主从同步延迟和读写分配机制问题

part 2 - 双主模式

因此随着业务的发展，数据库架构可以由主从模式演变为双主模式。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中

随着业务发展，架构会从主从模式演变为双主模式，建议用双主单写，再引入高可用组件，例如 Keepalived和MMM等工具，实现主库故障自动切换。

「MMM 架构」

MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构。

• MMM故障处理机制 MMM 包含writer和reader两类角色，分别对应写节点和读节点。
  除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常。
  • 当 writer节点出现故障，程序会自动移除该节点上的VIP
  • 写操作切换到 Master2，并将Master2设置为writer
  • 将所有Slave节点会指向Master2
• MMM监控机制 MMM 包含monitor和agent两类程序，功能如下：
  • monitor：监控集群内数据库的状态，在出现异常时发布切换命令，一般和数据库分开部署。
  • agent：运行在每个 MySQL 服务器上的代理进程，monitor 命令的执行者，完成监控的探针工作和具体服务设置，例如设置 VIP（虚拟IP）、指向新同步节点。

「MHA 架构」

MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。
在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA 还支持在线快速将 Master 切换到其他主机，通常只需0.5－2秒。

! 要搭建MHA，要求一个复制集群中必须最少有三台数据库服务器

• MHA 组成：MHA Manager（管理节点）和MHA Node（数据节点）。
  1. MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
  2. MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。
  • MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。
• MHA故障处理机制：
  • 把宕机master的binlog保存下来
  • 根据binlog位置点找到最新的slave
  • 用最新slave的relay log修复其它slave
  • 将保存下来的binlog在最新的slave上恢复
  • 将最新的slave提升为master
  • 将其它slave重新指向新提升的master，并开启主从复制
• MHA优点：
  • 自动故障转移快
  • 主库崩溃不存在数据一致性问题
  • 性能优秀，支持半同步复制和异步复制
  • 一个Manager监控节点可以监控多个集群