MySQL核心知识【索引，事务，锁，日志，集群】

MySQL介绍

数据库种类：

• 关系型数据库
• 非关系型数据库
• 大数据分析列式数据库

MySQL属于关系型数据库。其区别与其他关系型数据库最大的特点是，它是一个支持插件式的存储引擎，支持如InnoDB，MyISAM，Memory等。
MySQL是C/S模型，即有mysql client，mysql server
MySQL的服务器模型采用的是IO复用和可伸缩的线程池。其中IO复用采用的是select，而不是epoll。主要是因为数据库的磁盘IO比较慢，所以网络IO要比磁盘IO快得多，因此不需要太快的网络IO，只需要两个IO速度相适配即可。

MySQL存储引擎

MyISAM和InnoDB存储引擎有什么区别？

MyISAM不支持事务，行级锁，不支持外键。索引采用的是非聚簇索引。其优势就是访问速度比较快。如果对事务完整性没有要求，并且查询数据和存储数据比较频繁的场景可以使用该存储引擎进行数据持久化。MyISAM在磁盘上会存储成三个文件：.frm（存储表的定义，即表结构）， .MYD（数据），.MYI（索引）。
InnoDB存储引擎提供了具有提交，回滚，崩溃恢复能力的事务安全，支持行级锁，支持外键，支持自动增长列等功能。索引采用的是聚簇索引，索引和数据存储都是存储在同一个文件中的（InnnoDB的数据不能脱离索引）。InnoDB在磁盘上的存储会生成两个文件：.frm（表结构），.idb（数据+索引）

补充：Memory存储引擎是一种在内存级别存储数据的引擎。每一个memory表都对应一个磁盘文件。其优势是速度访问极快，因为它的数据都是存储在内存中的。其使用的索引结构是HASH索引（不适合做范围查询）。缺点是一旦服务关闭，之前的数据就会全部消失。

为什么使用MyISAM的时候，只有在主动加主键的时候才会加，而InooDB存储下，默认就会添加主键？

因为MyISAM中有.MYI文件专门存储索引，所以如果没有主动加索引的话，该文件就是空的。而InnoDB的数据和索引是在一起的，所以数据不能脱离索引而存在，因此如果没有主动加主键索引的话，默认就是加一个整型的id作为表的索引。

MySQL索引

索引的核心就是加速查找数据，提高查询的数据的速度。

由于索引也是要被存储成索引文件的，因此对索引的使用也会涉及到磁盘IO。如果创建的索引过多，返回会因为进行一些无用的磁盘IO，从而降低了SQL的查询效率。

索引的类型

索引在物理上被分成了

• 聚簇索引：索引和数据放在一起存储
• 非聚簇索引：索引和数据分开存储

索引在逻辑上分成了

• 普通索引（二级索引/辅助索引）：没有任何限制，可以给任何类型的字段创建普通索引
• 唯一索引：使用unique修饰的字段，自动会添加唯一索引。该字段的值不能重复，主键索引也属于唯一索引
• 主键索引：使用primary key修饰的字段会自动创建主键索引。
• 单列索引：在一个字段上加索引
• 多列索引：多个字段作为一个整体创建的索引。使用多列索引的时候，一定要使用到第一个索引，否则不能使用该索引。
• 全文索引：使用fulltext自动创建全文索引，只支持char,varchar,text上创建。用于提高查询字符串速度。

索引的创建和删除

创建表的时候添加索引

create table indexTable(
	id int,
	name varchar(20),
	index(id) # 使用index()创建索引
);

在已创建的表上添加索引

create index 索引名称 on 表名(属性名(长度));
# 例如
create index nameIdx on indexTable(name(10));

删除索引

drop index 索引名 on 表名;
# 例如
drop index nameIdx on indexTable;

查看索引

# 查看当前表中所有的索引
show indexes from 表名;

索引的底层实现原理

数据是存储在磁盘上的，而用户如果想要读取数据，就一定要将数据读取到内存中。其中最大的成本就在于磁盘I/O。磁盘是以块为单位进行数据管理的，一个数据块一般是16K。而内存是以页为单位进行内存管理的，一个页是4k。所以进行一个磁盘I/O对应需要4个内存页进行管理。

正因为查询数据的性能瓶颈在磁盘的I/O上，因此索引的底层数据结构的目的就是要尽量地减少磁盘I/O。

一般来说查找数据比较快的数据结构就是AVL树，红黑树等二叉平衡树。假设有2000w的数据使用AVL树进行存储，那么每一次读取数据至少要使用2000w的对数次，大约是25次，也就是进行一次数据读取就需要进行25次磁盘IO。
一般数据库中都是使用B-树（注意不是B减树，B-是B树，B+树，B-树的统称），也就是一个多叉平衡树，B-树的阶数通常取300~500之间，具体情况按照具体的业务去设置。假设阶数为400，那么最多只需要3次磁盘IO就可以找到数据，这样就大大减少了磁盘I/O的次数（核心）。并且因为B树节点中的数据都是有序的，所以也可以通过二分搜索查询数据，因此查询时间复杂度还是在O(logN)。

B树的特点：多叉，平衡，层数少。所以在索数据的时间复杂度不变的同时大大减少了磁盘IO的次数，这就是选用B树作为索引底层实现的最大的优势。

具体过程：判断查询的字段是否有索引（假设有索引） -> 通知存储引擎 -> 通知操作系统 -> 磁盘I/O -> 读取到内存中 -> 在内存中构建索引树进行加速查询

注意：

• 如果一个字段没有索引的话，那么就要根据该字段进行全表查询。
• 使用B树构建的索引树上是数据是否为实际数据，要根据存储引擎来判断。如果是MyIASM的话，数据部分存储的是实际数据的地址。如果InnoDB的话，数据部分存储的就是实际的数据。
• 一次查询只会使用一个索引

实际上B树并不是MySQL采用的索引底层实现，MySQL中的MyIASM和InnoDB使用的采用B+树进行构建索引树。

为什么MySQL采用B+树而不是B树作为索引底层实现呢？

主要基于一下三点：

1. 在B树中索引和数据是分散在节点中存储的，所以离根节点近的节点就可以更快地被搜索到。反之，搜索时间就更长。所以磁盘I/O次数很随机，搜索次数也很随机。
2. 每一个非叶子节点中同时要存储索引和数据，这样比只存储索引的节点能存放的索引值少的多，所以B树相对B+树层数会更高一点，搜索效率也会更低一点。
3. B树不方便进行范围查询。并且进行全表查询的时候，搜索B树节点也比较不方便。

B+树特点

• 所有非叶子节点上只存储索引值，不存放数据。因此非叶子节点中存储的索引值更多了，因此B+树的层数更少，搜索效率更高。
• 所有的叶子节点上存储了索引值和其对应的数据。所以在搜索数据的时候，都需要查询到叶子节点才能找到数据。所以搜索的效率是很平均的。
• 叶子节点被串联成了一个双向有序链表。可以更快速进行范围查询/区间查询。并且全表扫描的效率也变高了（可以直接扫描叶子节点串起来的链表即可）。

简单说，B+树比B树多出的特点就是所有非叶子节点只存储索引值，所有叶子结点存储索引值和数据的映射。并使用双向链表串联成一个有序链表。

总结

当使用select * from user where id = 123;的时候，首先判断id是否有索引，如果没有就进行全表搜索。如果有就让存储引擎进行磁盘IO将索引文件读取到内存中，并在内存中构建出一个B+树。B+树的非叶子节点中只存储索引值，叶子节点存储索引值和其对应的数据，并用双向链表将其串联形成一个有序链表。相对于B树或者其他的平衡树，它的层数最少，也就是磁盘IO次数最少。而且支持区间的范围查询。

主键索引和辅助索引

假设有这样一张表（存储引擎是InnoDB）

create table user(
	id int primary key,
	name varchar(20),
	age int,
	index(name)
);

其中id是主键索引，name是二级索引/辅助索引。从上文可知，主键索引的B+树的叶子节点存储的是数据。而辅助索引的叶子节点存储的索引所在行的主键，而不是实际数据。所以当要通过辅助索引找非索引字段就需要进行回表查询。
回表查询的意思就是在辅助索引树中获得记录的主键，然后拿着这个主键再去主键索引树中找到对应的数据。
但是也有一些例外：

# 不用回表查询
select name from user where name = 'zhangsan';
# 不用回表查询
select id, name from user where name = 'zhangsan';
# 需要回表查询
select age from user where name = 'zhangsan';

前两个SQL执行的时候，不用回表查询。因为id和name在辅助索引上就有，所以直接返回就可以了。但是age在辅助所以上没有，因此就需要进行拿着主键在主键索引的叶子节点中找。

如果在某个业务常见下，需要通过name索引快速返回其对应的age。这时就不能让辅助索引进行回表查询了，而可以考虑建立一个name和age的联合索引，这样age就可以辅助索引上，而不用回表查询了。

tyle=none&taskId=uefb1c1ae-4901-4f68-a372-fbe74d2dca2&title=&width=1055)

还有一种使用联合索引的常见，就是如果要执行`select * from user where name =‘zhangsan’ order age’的时候，如果只对name建索引，那么就会name筛选出来的值进行外部排序。

如果建立了index(name,age)的联合索引，那么**name在前，age在后，所以就会先按name排序。name相同，再按age排序。**当搜索出name='zhangsan’的值之后，这些值就是按照age进行排序的，此时就不会出现外部排序。

总结

• 辅助索引的叶子节点中存储的是数据记录对应的主键。需要进行一次回表查询才能找到辅助索引树上没有的数据。
• 如果想要通过辅助索引快速找到一个字段的数据，可以建立联合索引。并且联合索引中的顺序，决定着对哪一个字段先进行排序。在某些常见下可以避免外部排序。

聚簇索引和非聚簇索引

辅助索引中叶子节点存储的是记录的主键，所以需要进行回表查询辅助辅助树中没有的数据，其底层的存储引擎是InnoDB。这种将索引和数据放在一起存储的索引也被成为聚簇索引。
如果存储引擎是MyIASM的话，那么主键索引树的叶子节点中存储是完整数据记录的地址。而辅助索引的叶子节点中存储的也是完整数据记录的地址。这种将索引和数据分开存储在两个文件的索引也成为非聚簇索引。可以看出来非聚簇索引的辅助索引是可以不用回表查询的。

哈希索引

MySQL中的memory存储引擎使用的就是哈希索引，其底层是一个链式哈希表。即发生哈希冲突的时候，采用的是拉链法处理哈希冲突。
哈希表的查询效率是O(1)，看起来要比B树效率还要高。但是实际上哈希索引的缺点也很明显。哈希索引底层的哈希表是无序的，所以不能使用order by进行查询。并且哈希表不能支持范围搜索和模糊匹配。除非哈希索引将所有数据进行搜索，那样的话，磁盘IO的次数就太多了，效率就会大大降低。
一般基于kv（哈希表）存储的数据库都用于内存中的数据搜索，例如Redis。

总结

• 哈希索引查询数据时，需要进行的磁盘IO次数很多，效率很低。
• 哈希索引只支持等值匹配，不支持范围搜索和排序等操作。

InnoDB自适应哈希

前面说了memory存储引擎是在内存中使用的哈希索引进行数据构建，但是由于磁盘IO的次数太多，并且查询方式的有限，所以一般不使用。

而InnoDB中也有哈希索引，但是开启这个哈希索引有一定的条件。当InnoDB检测到某一个二级索引树上相同的二级索引值被重复的查询，此时InnoDB会根据这个二级索引值在内存中构建一个哈希索引一次来加速查询数据。
如图：

原本需要二级索引查询+回表查询两次查询操作，现在只需要一次哈希定位就可以了。
但是自适应哈希索引本身也是需要被维护在内存中的，也是需要消耗一定的性能。并不是任何场景下都是适用。

# 查看是否开启自适应哈希：默认开启
show variables like 'innodb_adaptive_hash_index';
# 查看自适应哈希分区数量：每一个自适应哈希索引分区都有一把单独的锁
show variables like 'innodb_adaptive_hash_index_parts';
# 查看InnoDB存储引擎使用情况
show engine innodb status;

自适应哈希默认启动开启，一般使用show engine innodb status;查看InnoDB的使用情况，然后判断是否要关闭自适应哈希。

1. 分区的锁竞争情况。如果一个分区上等待的的线程数量过多，那么锁竞争会导致效率降低。
2. 自适应哈希索引搜索频率和二级索引树搜索频率。如果自适应哈希索引的使用频率比较低，那么就没有必要在内存中维护自适应哈希了。

优化索引的常见方式

1. 经常要作为where中的过滤字段可以考虑添加索引。并且选择的字段的区分度要高。当区分度不高的情况下，索引会失效而被优化成为全表扫描或者其他的方式。

2. 查询的字段会影响到是否要回表查询，所以要精确定位需要的字段。

3. 一张表在一次查询时只会使用一个索引，在一张表有多个索引的情况下，优先使用过滤数据量最少的索引。如果一定要使用某一个索引进行数据查询的话，可以使用select * from 表名 force index(索引名)的方式。

   1. 

4. 字符串字段创建索引时，尽量规定索引的长度，不至于让索引的长度过长。

   1. 
      

5. 同时出现where和order by的时候，可以考虑建立联合索引。

6. 需要注意索引字段在涉及类型强转（字段是字符串类型，但是查询的时候使用的是整数，MySQL就会自动进行类型强转），函数调用，表达式字段时不能使用索引。

   1. 

7. 使用like进行前缀的模糊查询（例如like ‘%zhang%’），某些场景下使用or，not in都会被优化成全表查询。

   1. not in会被优化成为范围搜索。or会被优化成union all;

8. 进行联合查询时，先比较每一张表执行查询条件的开销，哪一个执行查询条件的开销小，哪一张表就是小表。然后用小表筛选出的数据在大表中使用索引加速查找。所以小表决定循环次数，大表决定每一次循环的时间。

慢查询日志-slow_query_log

对于索引优化的方式，一般都是通过explain进行分析，是否使用到索引？是不是索引建立的有问题？是否因为表数据量太大导致花费的时间很长？
在实际项目中会设计到很多的sql，此时就需要找到最消耗时间的sql，从这个性能瓶颈出发使用explain做进一步的分析和优化。但是如何找到耗时的sql呢？这就需要使用到慢查询日志，即slow_query_log。

slow_query_log是MySQL中四大日志之一，顾名思义就知道它记录的就是查询比较慢的sql等详细信息。

慢查询信息查看命令

show variables like 'slow_query%';

• slow_query_log：是否开启了慢查询（默认是关闭）
• slow_query_log_file：slow_query_log存储的位置

# 开启慢查询
# slow_query_log是全局变量，所以需要使用global
set global slow_query_log = ON;

long_query_time

系统中通过long_query_time变量来判断哪一个SQL执行的时间比较慢，消耗时间比该变量大的SQL就被称为慢查询。
在不同的场景下可以进行设置慢查询的标准。

# 查看long_query_time
show variables like 'long_query_time';
# 设置long_query_time
set long_query_time = 1;

• 慢查询日志的信息

MySQL事务

什么是事务

事务就是将一条或者多条SQL打包成一组，形成一个最小的执行单元。这个执行单元在执行的时候具有原子性，要么全部执行成功，要么全部执行失败。
一般事务设计到三个操作：开启事务，提交事务（结束事务），回滚事务

事务处理的命令

# 查看是否为自动提交事务（默认是自动提交，即一句SQL就是一个事务）
select @@autocommit;

# 设置是否手动提交事务
set autocommit=0;

# 开启事务
begin; 

# 随时可以回滚，相当于事务中的SQL全部执行失败，恢复当执行事务前的状态
rollback

# 提交事务
commit; 

# 设置一个名字叫point的保存点
savepoint point;

# 事务回滚到名字家偶偶point的保存点
rollback to savepoint;

# 设置事务的隔离级别
set tx_isolation='';

# 查看事务的隔离级别
select @@tx_isolation；

为什么会有事务

事务的本质就是为了服务上层应用。当上层的应用程序在访问数据的时候，事务简化了编程模型。因为不用在考虑网络异常，服务器宕机，并发访问等问题，因为事务要么回滚，要么提交。其中的潜在错误和并发问题已经被绕开了。
所以事务并不是数据库系统天生自带的，而是因为更好地服务上层，从而产生的。

ACID特性

MySQL中的每一个事务都有这样四种特性：

• 原子性
  • 事务是一个不可分割的整体。当执行事务时，要么全部成功，要么全部失败，不可以只完成部分的事务。
• 一致性
  • 一致性就是从一个正确的状态迁移到另一个正确的状态。其中正确的状态就是满足真实世界中预定的约束。所以数据库的一致性是需要由用户来负责的。
• 隔离性
  • 当两个或者多个事务在并发执行时，为了保证数据的安全性。将一个事务内部的操作和其他事务内部的操作进行隔离，来让事务之间在并发时不会相互影响。隔离级别越高，两个事务之间影响越小，安全性越高，但是并发性越差。
• 持久性
  • 事务在提交之后，可以保证数据库对数据的修改是永久的。即使数据库出现了异常情况，也应该可以恢复数据。

补充：

• 一致性是目的，而AID是手段。AID是数据库本身的特质或者是能力，这些特质使得用户在使用的时候可以不用考虑存储能力方面的问题，但是用户要通过逻辑使得数据的状态是正确的，是受到预定的约束的。
• 数据的写入是先缓存起来，然后一起写入磁盘，因此在异常情况下可能会出现缓存中数据丢失的情况。
• MySQL最重要的是日志，而不是数据。MySQL中通过redo log，undo log来进行异常情况发生下的数据恢复和事务回滚，做到了数据的持久性和事务的原子性，从而来保证了数据的一致性。

事务并发存在的问题

如果事务不经过隔离，在并发执行事务的时候通常会出现一下的问题：

• 脏读
  • 一个事务读到另一个事务未提交的数据。
  • 例如：事务A和事务B并发执行，在A中进行一些操作之后，B读取到A尚未提交的数据，同时A进行了回滚，此时B读取到的就是无效的脏数据。
• 不可重复读
  • 一个事务的操作导致另一个事务前后两次读取的数据不同。即一个事务读取到另一个事务修改的数据。
  • 例如：事务A和事务B并发执行，B对数据进行查询，之后A对B查询的数据进行了更新，并提交了事务，最后B进行相同的查询时，看到了与之前查询不同的数据。
• 幻读
  • 一个事务的操作导致另一个事务前后两次查询操作得到的结果数不同。即一个事务读取到另一个事务新增的数据或者读取不到删除的数据。
  • 例如：事务A和事务B并发执行，B对数据进行了查询，之后A对插入或者删除了一条满足B查询条件的记录，最后B进行相同查询时得到的数据记录条数和之前不同。

其中脏读大多数情况下都是有问题的，因为它的不确定因素太大了。
但是不可重复读和幻读根据业务场景的不同，可能是问题也可能不是。

事务的隔离级别

在并发的过程中，为了保证数据的安全，所以就引入了事务时间的隔离问题。而在不同的业务场景下，事务之间的隔离程度是不同的。所以在MySQL中就划分成了4中不同的隔离级别。

1. READ-UNCOMMITTED，读未提交：在提交前，另一个事务就可以看到修改的数据。
2. READ-COMMITTED，读提交：只能读到其他事务提交过的数据。
3. REPEATABLE-READ，可重复读：保证事务前后两次的查询操作数据是相同的。
4. SERIALIZABEL，串行化：线程是串行执行的。

下图展示，设置对应的隔离级别能够解决和还没解决的问题：

补充：

• 事务的隔离界别越高，安全性越好，并发性越差，花费的性能越高。
• 在可重复的隔离级别下已经解决了一部分的幻读问题。即在一个事务插入一条记录并提交之后，另一个事务中是看不到的。但是如果事务进行了update，那么幻读问题还是存在的。要想完全解决幻读问题，就需要将隔离级别设置成串行化（serializable）
  • 在repeatable-read隔离级别下，使用update出现的幻读问题。

MySQL中的锁

事务 -> 事务的ACID -> 事务的隔离性 -> 事务的隔离级别

事务通常在并发情况下执行，为了兼顾数据的安全性，一致性和并发的效率，所以事务被划分成为四种隔离级别。隔离级别底层是通过锁+MVCC来实现的。
其中“读未提交”隔离级别没有加锁来限制并发控制，所以数据安全性极低。而串行化通过锁将所有的事务进行排序，然后串行化来执行，所以数据安全性高，但是并发效率太低了。因此这两种隔离级别在实际过程中使用的都很少。
读提交和可重复读两种隔离级别使用了不同的锁来同时兼顾了数据安全性和并发效率，所以平时使用的比较多。

MySQL中的InnoDB支持行级锁和表级锁，MyIASM只支持表级锁。接来下就介绍一下各种不同的行级锁和表级锁。

表级锁和行级锁

• 表级锁：对整张表加锁。
  • 优点：开销小，加锁快，不会出现死锁。
  • 缺点：加锁粒度大，发生锁冲突的概率高，并发度低。
• 行级锁：对某一行记录加锁
  • 优点：加锁粒度小，发生锁冲突的概率低，并发度高。
  • 缺点：开销大，加锁慢，可能会出现死锁。

排它锁和共享锁

排它锁（Exclusive），也叫作X锁或者写锁。
共享锁（Shared），也叫作S锁或者读锁。

X锁和S锁之间的关系

两个S锁之间是可以兼容的，而一个S锁一个X锁或者两个X锁之间都是不可兼容的。

• 如果一个事务对一行数据加了S锁，那么允许其他事务一对同一行加S锁，但是不能加X锁，否则就会被阻塞。
• 如果一个事务对一行数据加了X锁，那么就不允许其他事务对同一行数据加任何锁。

手动显示加锁的命令

# 强制获得X锁
select ... for update;

# 强制获得S锁
select ... lock in share mode;

总结

表级锁和行级锁指的是锁的粒度。而排它锁和共享锁指的是锁的种类。

InnoDB行级锁

行级锁

InnoDB的一大特色就是支持行级锁，加锁的力度更小，并发能力更好。

注意点：

1. InnoDB的行锁是通过给索引上的索引项加锁来实现的，而不是给某一行记录加锁。所以只有使用到了对应的索引项InnoDB才会加行锁，否则InnoDB就会加表锁。

   1. 发生行锁冲突：

   2. 发生表级锁冲突：

2. 行锁是加在索引上的，如果查询数据的时候，使用到了被上锁的索引字段作为过滤条件，即使最终访问到的是不同的记录，但是依然会发生锁冲突。注意：如果给辅助索引上加锁，相当于给辅助索引对应的记录的主键索引加锁。

   1. 

3. 有时SQL中可能使用了索引，但是经过MySQL的优化器之后，如果MySQL认为全表扫描比使用索引查询数据更快，那么会放弃索引，此时如果加锁，加的是表级锁，而不是行级锁。

总结：

• 行锁是将锁加在索引项上的，而不是加在某一行记录上的。
• 在查询的过程中不能使用被加上锁的索引项作为过滤字段，否则会锁冲突。
• 最终是否将锁加在索引上还是要看MySQL优化之后的结果。

补充：
上面的示例都是在MySQL默认隔离级别“可重复读”下操作的，所以需要手动加锁。如果使用“串行化”隔离级别，默认在读的时候加共享锁，在修改或者插入的时候加排它锁。

间隙锁

在InnoDB中间隙锁的目的就是为了防止幻读。
幻读产生的必要条件是这四个条件的组合：主键索引之间加锁，辅助索引之间加锁，范围查询时加锁，等值查询时加锁

进行范围查询时，在主键索引之间加锁

当进行范围查询的时候，InnnoDB会给符合条件的数据记录的索引项加锁，同时给符合条件的记录之间不存在的地方进行加锁，这把锁就是间隙锁（gap lock），就像在记录的间隙中加锁一样。
特殊地，当最后一行加了间隙锁之后，那么最后一行数据的后面也都被加上了锁。

注意：

• 间隙锁的范围是左开右闭的。例如：查询出的记录的主键是2,5,10（10是最后一行数据），那么在(2,5]，(5,10]，(10,+无穷]中都加上了间隙锁。
• 有些书上会有next-key lock，这个就是不仅在间隙上加锁，在记录本身上也加上了行锁。所以next-key lock = gap lock + record lock

看几个例子来帮助理解：

1. 成功插入

2. 被间隙锁被阻塞

进行范围查询时，在辅助索引之间加锁

• 当辅助索引值相同时，主键索引是按升序排序的。例如：age是辅助索引，当age > 18都加上锁之后，此时要插入age=18，并且主键值比第一个age=18的记录的主键大的时候，此时一样会被阻塞住。

• 有时MySQL会将索引优化掉，而直接使用全表查询，此时加的就是表锁了。

进行等值查询时，在主键索引之间加锁

因为主键是不能重复的，所以就不存在幻读问题。因为如果主键重复，MySQL的语法分析器就报错的。因此当索引是主键索引或则唯一键索引的时候，只会给查询的记录加上行锁。

进行等值查询时，在辅助索引之间加锁

因为辅助索引是有可能会重复的，为了避免出现重复的索引值，所以会给查询的记录的加上行锁，并且给最靠近该记录的左右两条记录中间加上间隙锁。这就形成了next-key lock，即间隙锁+行锁。

InnoDB的表级锁

InnoDB中存在行级锁，所以一般情况下不会使用加锁粒度更大的表级锁。但是也有特使情况：

1. 如果一张表需要更新的数据很多甚至是整张表，同时这张表由比较大，此时一行行加锁效率就比较低，而直接加一个表级锁是一个更好的选择。
2. 当事务中涉及到多张表，此时逻辑关闭比较复杂，容易出现死锁，从而造成事务的大量回滚。此时使用行锁的效率也比较低。

原则是能有行锁就用行锁，如果行锁的开销比表锁大时，就选择使用直接加表锁。

加表锁命令

# 给表加一个读锁
lock table 表名 read;

# 给表加一个写锁
lock table 表名 write;

# 释放所有表的锁
unlock tables;

意向排它锁和意向共享锁

意向共享锁（IS锁）：在给一行记录加共享锁（S锁）之前，要给该记录的表加一个IS锁。
意向排它锁（IX锁）：在给一行记录加排它锁（X锁）之前，要给该记录的表加一个IX锁。

意向锁的作用就是更高效地获取表锁

如果现在要获取一个有100万记录的表的写锁，就需要先判断表中是否有记录获得了行锁，如果已经获得了行锁，那么就不能给表加写锁了。但是100万行的记录一行一行扫描效率就太低了。
如果在加行锁之前，给表加一个意向锁，表明了表中加行锁的情况，那么在加表锁的时候就很容易进行判断是否可以加锁了。

注意：

1. 意向锁是InnoDB在获取行锁之前自己获取的
2. 意向锁之间是兼容的，不会冲突。
3. 意向锁的意义就是为了更高效地获取表锁。

死锁

在MyIASM中是不会出现死锁的，因为总是一次性需要的全部的锁，所以要么就可以满足，要么就一直等待。
但是InnoDB中有更细粒度的锁——行锁，而行锁在不同的时间点都可以去获得，所以常常会导致出现死锁的现象。

最常见的，InnoDB中的死锁会发生在两个事务在前后以不同顺序去获得锁，最后导致两个事务自个获得的锁不释放，但是都想要获得对方获得的锁，从而导致了死锁现象。
所以死锁问题往往都是由于用户在使用的时候造成的，在多线程竞争多个资源的时候，往往会出现死锁现象。

锁的优化建议

1. 设计合理的索引，并使用索引去访问数据，使得锁可以加的更精准，从而使得并发的能力更强。
2. 在能力范围之内尽量将事务减小，事务越小发生锁冲突的概率越小。
3. 多线程在访问资源的时候，尽量约定按照相同的顺序访问资源。
4. 除非必要，否则不要显示进行加锁查询数据。
5. 等等

MVCC多版本并发控制

前面说过四种隔离级别中，read-uncommitted没有做任何的并发控制，所以会出现脏读，不可重复读和幻读的问题（基本上就是所有并发会出现的问题它都有）。而serializable利用了行锁，间隙锁和共享锁，排它锁的不兼容性做到多事务之间的读写操作是串行执行的。
而read-committed和repeatable-read是分别解决了脏读和幻读及部分的幻读问题。这是通过MVCC做到的。
MVCC中有两种读操作：

1. 快照读：读的是当前可见版本数据。例如：select读取的是已经生成的快照。
2. 当前读：读的是当前最新版本的数据。例如：insert，delete，update，select … lock in share mode/for update读取的是当前最新被提交过的数据。

每一行记录中除了自定义的数据之外还有很多隐藏字段，其中有两个字段是修改当前记录的事务的ID（DB_TRX_ID）和指向上一个版本的快照的回滚指针（DB_ROLL_PTR）。
通过记录中的指针就可以将之前生成的快照版本串联起来，方便之后读取历史版本数据。

注意：事务中修改的数据有两种状态，一种是准备状态，一种是被正确提交状态。当事务被commit的时候，其修改的时候就会由准备状态更改为提交状态。生成的快照是基于已经本提交的数据的，处于准备状态的数据是不会形成快照的。

大致了解了MVCC就是将多个版本的快照串联起来方便回滚进行读取之后，我们就要知道快照是什么时候生成的。这就是实现read-committed和repeatable-read的底层原理：

• read-committed：在同一个事务中，每一次select查询数据的时候，都会生成一次快照（Read View）
• repeatable-read：在一个事务中，只有第一次select查询数据的时候会生成一次快照。

read-committed原理解释

1. 为什么read-committed解决了脏读问题？

因为在事务中select是基于最新一次的快照读，而脏读的数据处于准备状态，所以不会被写入到快照读中。因此读不到脏数据。

2. 为什么read-committed解决不了可重复读和幻读问题？

因为在事务中select会读取最新一次的快照，所以在其他事务中被更新的数据被提交之后，在下一次select时就会生成这些被更新数据的快照，所以就可以读取到其他事务修改的数据了。幻读问题也是类似的。

repeatable-read原理解释

1. 为什么解决了不可重复读问题？

因为repeatable-read级别下只会在第一次select是生成快照，如果在其他事务更新数据之前已经select过了，那么即使数据被其他事务更新过了，但是下一次select也只会读取第一次生成的快照，因此读取不到其他事务中被提交过的修改过的数据。

2. 为什么只解决了部分的幻读问题？

解决了部分的幻读问题的原理和解决了不可重复读问题的原理类似，也是因为只会读取第一次生成的快照，所以之前插入或者删除的记录都是不可知的。
但是在同一个事务中是可以看到自己修改的数据的，如果在本事务中将其他事务中插入的数据进行修改，那么该记录的DB_TRX_ID就是当前事务的ID，那么在当前事务中就可以读取到了。

MySQL日志

MySQL有很多的日志，并且分布在不同的地方，有的是MySQL Server层就自带的，有的是某一个存储引擎独有的。
在MySQL Server层有四大日志：

• 错误日志，error log：mysql服务在运行过程中出现的coredump，error，exception和一些普通的日志信息都存储在错误日志中。当数据库出现错误无法使用的时候，实现查看错误日志进行查看错误和进行错误分析。
• 查询日志，query log：存放着所有的sql
• 二进制日志，bin log：记录了所有DDL和DML语句，除了select，描述了数据更改的过程。主要用于主从复制和数据恢复。
• 慢查询日志，slow query log：记录超过long query time的sql，用于性能分析

slow query log和undo log之前说过了，下面主要介绍bin log和redo log。

bin log

bin log中存储所有DDL和DML语句（除了select查询），当出现异常之后，可以直接使用bin log中的语句，放在数据库重新执行一遍，就可以恢复数据库中的数据了。

# 在mysql中执行./data.sql
mysql> source ./data.sql

# 在shell中执行./data.sql
shell> cat data.sql | mysql -uroot -p

由于磁盘空间是有限的，所以bin log中的日志信息也需要设置一个过期天数，数据库服务器中只保存过期天数之内的bin log信息。在过期天使之前的数据应该被备份在其他的地方，方便之后恢复更早的数据。
通过数据备份+bin log的方式就可以最大化保证数据可以被恢复。

redo log

redo log，重做日志：用于记录事务操作的变化，确保数据的持久性。
redo log在事务开始之后就可以记录，不管事务是否提交都会记录下来，当数据commit成功之后，redo log就会被刷新到磁盘中。如果异常情况发生，InnoDB可以使用redo log将数据恢复到异常情况下的样子，保证数据的完整性。
redo log会先写入redo log缓冲区，即log buffer中，为了避免事务在执行过程中花费性能在磁盘IO上，所以设置大一点的redo log缓冲区可以节省磁盘IO。默认innodb_log_buffer_size为16M。
最终redo log缓冲区中的日志信息会放在在ib_logfile0，ib_logfile1中，该文件叫做重做日志文件。

InnnoDB操作数据不是去操作磁盘数据，而是操作内存中的数据。磁盘数据会被读取到内存中，该位置叫做Buffer Pool。InnoDB会将Buffer Pool中的脏数据的变化情况记录在redo log中，用于数据恢复。然后有专门的线程会定时将BufferPool中的数据刷新到磁盘中。

MySQL优化

MySQL的优化有哪些？这个问题不是很开放性，下到SQL语句，上到源码剖析。但是有几个常见的切入点。

索引方面的优化

可以看起慢查询日志的记录，通过设置long_query_time来筛选出不能接收的慢查询操作。然后针对慢查询日志中的SQL，使用explain做进一步的分析。有没有使用索引？是否正确而准确地使用索引？是不是可以建立更高效的索引进行查询？

MySQL client端的优化

client端首先得连接数据库，如果是在高并发的情况下，如果实现快速的网络连接。此时可以引入连接池作为中间件，并通过设置初始化连接数量和最大超时时间等参数进行优化。

连接之后，client端要使用数据库中的数据，对于热点数据不能每次都从数据库中读取，否则效率就没有达到最大化，因此可以引入缓存层，如Redis作为中间件。用户先从缓存层获取数据，如果获取不到再到数据库中拿数据，拿到数据之后就缓存在缓存层中，之后再接到相同的请求就可以直接返回数据了。

MySQL server端的优化

MySQL中有很多的默认配置，我们可以根据不同的业务常见进行不同的配置修改来适应当前的业务。
如：

• 如果遇到查询多，而更新少的业务场景，就可以开启查询缓存。当然也可以使用Redis作为缓存进行更细粒度的控制。
• 自适应哈希是否要开启，如果等待在某一个哈希索引的分区上的线程太多或者自适应哈希索引的使用率不高的话，就可以关闭自适应哈希索引。如果二级索引树上某一个索引使用频率高，那么就可以开启自适应哈希索引。
• 可以通过增大buffer pool的大小来减少磁盘的IO。
• 在高并发情况下，可以增大线程的最大连接上限和增大连接的最大超时时间，还可以进行线程缓存。
• 等等…

MySQL集群

主从复制

主从复制的流程：

1. 主库的更新操作写入bin log中
2. 当从库连接主库的时候，主库为其创建一个log dump线程，用于发送bin log给从库
3. 在从库中执行了start slave命令之后，从库创建一个I/O线程用来连接主库，并请求主库中的bin log。在从库的I/O线程接收到log dump线程发送的bin log之后，就会将其复制到本地的relay log中。
4. 从库的SQL线程读取relay log中的内容，并解析成具体的SQL操作，重放这些操作来更新从库的数据，使其和主库的数据一致。

简单来说：从库从主库中获得主库各种操作，并在本地重新运行了一遍，来保证自己的数据和主库完全一致。

注意：

• 只要IO线程和SQL线程保持同步，relay log一般都在缓存中存放，所以开销比较小。

读写分离

读写分离就是有多台MySQL服务器，其中只能在主库中进行写操作，只能在从库中进行读操作。主库负责写，多个从库负责读，主从库之间通过主从复制进行数据同步更新，保持数据的一致。
这样当读请求越来越多的时候，只需要将从库进行水平扩展就可以减轻服务器的压力了。

通常会使用MySQL的中间件作为中间的代理服务器，将client端的数据传输给主从库中。

常见的主从库架构模式有：

• 一主一从
• 一主多从
• 多主多从

分库分表

当读请求越来越多的时候，通过主从库之间的读写分离和主从复制就可以做到主从库数据同步的同时，通过水平扩展使得服务器能够接收更多的读请求。
但是当用户增多，写请求越来越多，如何保证数据库的负载能够足够呢？
这时就需要使用分库分表，对写操作进行切分。原本在一台服务器上进行写操作，现在要在多台服务器上进行写操作。

库表问题

1. 单库太大：单库处理能力有限，服务器上的磁盘空间不足，就需要将单库切分成更小的库。
2. 单表太大：当数据量很大的时候，CRUD的效率都很低，此时就需要将单表切分成更小的表。

拆分策略

当单个库太大的时候，先考虑是表太多，但是数据太多。

1. 如果是表太多造成数据量大，则考虑使用垂直拆分，即根据业务拆分成多个库。
2. 如果是单个表数据量太大，则考虑使用水平切分，即根据表的规则经表拆分成多张表。

分库分表时，优先考虑垂直拆分（分库），再考虑水平拆分（分表）。