mysql知识点总结

存储引擎

MySQL中的存储引擎

• 查看存储引擎：show engines;
  
• 查看mysql文件存储位置
  show variables like ‘%dir%’;
  
  datadir下存储的是数据文件。

Innodb和MyISAM比较

1. 建两张表：student和teacher
   student使用的引擎为Innodb，teacher使用MyISAM。

create table student
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=INNODB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT; 

create table teacher
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT; 

1234567891011121314151617181920212223

1. 查看数据文件
   

• .frm文件是表结构等定义文件。Innodb和MyISAM都使用该类型文件。
• MyISAM将索引和数据分开存储了，.MYD是数据存储文件，.MYI是索引文件。所以在MyISAM中查找数据时，会先在索引文件中找到对应的数据存储位置，然后再到数据文件中取出对应数据。
• Innodb将索引和数据存储在一个文件中了，就是.ibd文件。

为什么Innodb和MyISAM存储方式不同？这其实和其索引的使用有关，下面就有答案。

索引

主要讲Innodb和MyISAM

为什么选择B- tree/B+ tree作为索引的数据结构？

我们先看看能用于索引的数据结构：

1. 二叉搜索树：代码实现
   二叉搜索树可以使用在数据查询，其时间平均复杂度为O(logn)。但是当数据递增插入的时候二叉树会变为线性链表，时间复杂度会变为O(n)。即使使用平衡二叉搜索树或者红黑树，因为每个节点只会有两个子节点，所以在数据量很大的时候树的深度会很深，这样查询一条数据就会要执行很多次IO操作，IO操作是很耗时的。所以二叉搜索树并不适合作为索引。

2. 哈希表

   ​

   哈希表查询的时间复杂度理想情况下是O(1)。但是在实际使用的时候难免会存在hash冲突，在冲突的时候，数据存储就需要转化为链表或者树的形式。而且使用哈希表作为索引，需要在内存中进行查询，这样当数据量很大时会很耗内存。

   ​

   弊端：

   • 会存在hash冲突。
   • 数据全部在内存中才能搜索，浪费内存。
   • 不支持范围查询

选择B+ tree的前提：

1. 局部性原理：总体意思就是你访问的磁盘块的周围的内容最有可能被下次访问。
   a. 时间局部性（Temporal Locality）：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。
   b. 空间局部性（Spatial Locality）：在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。
   c. 顺序局部性（Order Locality）：在典型程序中，除转移类指令外，大部分指令是顺序进行的。顺序执行和非顺序执行的比例大致是5:1。此外，对大型数组访问也是顺序的。

因为局部性原理，所以CPU会预读一部分数据：

1. 磁盘预读：磁盘的顺序读写会很快，可以媲美内存。所以磁盘预读一部分数据耗时很小。操作系统中，预读数据最小单位为页，系统每次预读数据都为页的整数倍，预读数据=N*PageSize。一般操作系统的页大小为4k。

B-TREE和B+TREE结构

degree：阶，就是每个节点最大能存储数据的个数。

B-TREE

B+TREE

B+TREE会将数据都存储在叶子节点上，并组成链表结构。

根据局部性原理和磁盘预读设计索引

1. 将索引块的大小设计为16K，结构如下:
   
   为什么索引块大小16K?
   假设使用int作为索引，一个索引占4byte，地址占用量忽略不计，则16K可以存储161024/4=4096个索引。则层数为2的树就能存储40964096=16777216条索引数据，这已经完全满足单表数据量了。所以选择16K为预读数据大小，即满足了要求，又减少了一次IO数据量。

B-TREE索引

下图是B-tree的结构，b-tree的每个节点存储数据为：索引+数据地址+下个索引块地址。对b-tree进行改良，可以将数据地址存储在树的叶子节点，这样索引块就能存储更多的索引。

B+TREE索引

B+TREE会将所有的索引都在叶子节点上存储，数据会存储在data上。注意：Innodb索引树的叶子节点是双向链表

• MyISAM
  MyISAM将索引和数据进行了分开存储。在叶子节点上，data存储的是该条数据对应的地址。在MyISAM中，通过索引找到数据存储地址，然后再到数据文件中读取数据返回。
  • 主键索引和普通索引结构相同
  • 索引的叶子节点储存数据记录的地址/指针
• Innodb
  Innodb中主键索引叶子节点data中存储的是整行数据，所以Innodb只是用一个文件存储索引和数据。非主键索引中的data存储的是主键值。
  • 主键索引的叶子节点存储整行数据。
  • 普通索引叶子节点存储id，所以通过普通索引检索，要经过主键索引再次查询到数据，即回表。

怎么建索引最好？

1. 数据的离散度
数据离散程度高的列建立索引才能更好的发挥B+tree的优势。
查询数据的离散度：

select count(distinct column_name)/count(*) from table_name;

1

离散度越高，说明数据重复的越少，越适合建索引。
以uname和age为例：

uname列的离散度为0.9987，age列的数据离散度为0.0000。uname的数据离散度很高，很适合建立索引，而age几乎全是重复的数据，索引极不适合建立索引。

2. 前缀索引
因为每个节点空间为16K，所以索引列的数据要越小越好，如果使用长字符串，每个节点能存储的索引越少，这样树的深度就越深，检索数据所需要的IO操作就越多。如果需要长字符串作为索引，我们可以建立前缀索引。建立前缀索引也要满足数据离散度的要求，尽量建立数据离散度高的前缀索引。
以表student为例，使用address建立索引：

create table student
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=INNODB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1;

12345678910

• 方式一：查询前缀冲突的数量前10条：

select distinct left(address,5) pref,count(*) count from student group by pref order by count desc limit 10;

1

增加前缀长度，当count数据不在急剧下降的时候，前缀长度就可以定为最小的那个值。

• 方式二：查看比重

select 
	count(distinct left(address,5))/count(*) as sel5,
	count(distinct left(address,6))/count(*) as sel6,
	count(distinct left(address,7))/count(*) as sel7 
from student;

12345

由数据可以看到，随着前缀长度的增加，比值却未大幅增加，则说明前缀长度6已经能满足高区分度了。所以可以建前缀长度为6的索引。

alter table student add index index_address (address(6));

1

索引分类

Innodb为例：
1. 主键索引
Innodb每个表都要有一个主键索引，如果不指定，Innodb会默认创建一个6byte的自增主键索引。主键索引的叶子节点上存储着整行数据。使用主键索引查询的效率最高。mysql建议使用自增序列作为主键。
2. 普通索引
普通索引是除了主键之外的列建的索引。普通索引的叶子节点存储的是id，通过普通索引查询到id，然后再根据id去主键索引查询数据。
3. 组合索引
组合索引是由多个列组成的索引。组合索引的使用要注意以下几点：
a. 数据离散度高的列排在前面。
b. 查询时索引中前面的列不能范围匹配，这样会导致后面的列失效。
4. 索引覆盖
索引覆盖是指：查询的数据就是索引数据，会直接返回，不会再进行回表查询。例如建一个idx_name_address的索引，我们只查name和address，即

select name,address from student where name = 'zhangsan' and address = 'beijing';

1

会直接返回name、address，不需要再根据id回表查询了。

其它

count(*)、count(1)和count(列名)谁更好？

使用count(*)更好。count()是SQL92定义的标准统计行数的语法，与数据库无关，跟NULL和非NULL无关。count()会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

如果根据URL进行检索，怎么提高查询效率？

url一般是很长的字符串，如果使用url建索引的话肯定是不合适的。如果使用前缀索引的话，url一般以域名开头，所以前缀也要建很长。这两种方法都不合适。
我们可以采用下面的方法：
新增一个列：url_crc。这个列存储的就是url进行crc32（循环冗余校验）的值，对url_crc建立索引，每次根据url查询时，就可以先计算url的crc32值，然后查询数据。但是crc32也会存在冲突的，所以单靠crc32值可能会查询到多条。则我们可以加上url匹配，最终sql：

select * from table_name where url = 'url' and url_crc = 'crc32';

1

Explain工具

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈。
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。
注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。

使用示例：

explain select * from actor;

1

Explain扩展用法

1. explain extended：会在explain的基础上提供一些查询优化信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows *filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

explain extended select * from film where id = 1;

1

show warnings;

1

1. explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

Explain中的列

1. id：指的是select的序列号，有几个select就有几个id。id值大的select先执行，相同的由上往下执行，为null的最后执行。
2. select_type：表示对应select的查询类型，是复杂查询还是简单查询。
   1）simple：简单查询，查询不包括子查询和union。
   2）primary：复杂查询的最外层查询。
   3）subquery：包含在select中的子查询（不在from子句中）。
   4）derived：derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含
   义）。
   用这个例子来了解 primary、subquery 和 derived 类型

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合
并优化
explain select (select 1 from actor where id = 1) from (select * from film where
id = 1) der;

1234

set session optimizer_switch='derived_merge=on'; #还原默认配置

1

5）union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

1

1. table列
   这一列表示 explain 的一行正在访问哪个表。
   当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
   当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
2. type列
   这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
   依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
   一般来说，得保证查询达到range级别，最好达到ref。

值解释：
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表，如下：

explain select min(id) from film;

1

system,const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system。

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在
const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会
找到多个符合条件的行。

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接
对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这
种通常比ALL快一些。

ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

1. possible_keys列
   这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
2. key列
   这一列显示mysql实际采用哪个索引来优化对该表的访问。
   如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
3. key_len列
   这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
   举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

explain select * from film_actor where film_id = 2;

1

1. ref列
   这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
2. rows列
   这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
3. Extra列
   这一列展示的是额外信息。常见的重要值如下：
   1）Using index：使用覆盖索引
   2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。
   3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

explain select * from film_actor where film_id > 1;

1

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索
引来优化。
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一
般也是要考虑使用索引来优化的。
6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段。

索引最佳实践

1. 全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

1

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

1

1. 最左前缀法则
   如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
2. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
3. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。
4. mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
5. is null,is not null 一般情况下也无法使用索引。
6. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作。
   问题：解决like’%字符串%'索引不被使用的方法？
   a）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

1

b）如果不能使用覆盖索引则可能需要借助搜索引擎。

1. 字符串不加单引号索引失效，涉及类型转换。
2. 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评
   估是否使用索引。
3. limit翻页过慢，可以采用id翻页。

索引使用总结：

like KK%相当于=常量，%KK和%KK% 相当于范围。

索引下推
对于联合索引（name,age,address）,正常情况下按照最左前缀原则，select * from employees where name like ‘tom%’ and age=22 and address=‘henan’； 这种情况索引只会走name字段，因为根据name字段过滤完，得到索引行里面的age和address是无序的，无法利用索引了。在mysql5.6之前，根据name字段过滤完后，会拿着id回表查询数据，然后按照age和address进行过滤。在mysql5.6之后引用了索引下推，就是在name匹配的索引中继续按照age和address过滤，然后再拿着过滤后的id回表查询。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？
估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like
KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

排序优化

1. innodb支持filesort和index排序，Using index是指使用索引本身完成排序。index效率高，filesort效率低。
2. order by满足两种情况会使用Using index：
   1）order by字段使用索引最左列
   2）where字段和order by字段组合满足索引最左匹配。
3. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4. 能用覆盖索引尽量用覆盖索引
5. group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
   by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
   的限定条件就不要去having限定了。

Using filesort文件排序原理详解
filesort文件排序方式：

• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
• 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

分页查询优化

常用分页查询

select * from employees limit 10000,10;

1

对于此操作，mysql是先读取前10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

常见分页场景优化技巧：

1. 使用主键分页：EXPLAIN select * from employees where id > 90000 limit 5;
2. 使用非主键范围分页。

JOIN关联查询优化

mysql的表关联常见有两种算法

1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法
   一次一行循环的从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

1

从执行计划中可以看到这些信息：

• 驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
• 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表， 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
• 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

1. 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。
如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

1. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
   把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

1

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下：

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

对于关联sql的优化

• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间。

in和exsits优化

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

IN
当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
#等价于：
for(select id from B){
  select * from A where A.id = B.id
}

12345

EXSITS
当A表的数据集小于B表的数据集时，exists优于in将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留。

select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
 select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引

123456

mysql内部组件结构

mysql事务及其锁

我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

事务及其ACID属性

1. 原子性（Atomicity）
   事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
2. 一致性（Consistent）：
   在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
3. 隔离性（Isolation）：
   数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
4. 持久性（Durable）：
   事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

事务得隔离级别：

脏读
读取了其它事务未提交的修改数据。
不可重复读
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性。
幻读
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数
据，这种现象就称为“幻读”。
一句话：事务A读取到了事务B提交的新增数据。

Innodb是如何解决脏读、不可重复读和幻读的？

1. 脏读和不可重复读：使用**MVCC（Multi Version Concurrency Control）**进行控制的。
2. 幻读：间隙锁（Gap Lock）和临键锁（Next-key Lock）

MVCC

Innodb中的锁

意向锁

意向锁是表锁。Innodb支持多粒度锁定，允许行锁与表锁共存。
意向锁主要用来解决，事务对表加表锁需要检测表内行数据是否加行锁的性能损耗。
例如：事务A对表中的一些数据加了行锁，事务B要对表加表锁做一些操作，这时，事务B在加锁前要检查表内数据是否被其它事务加锁了，可以一行一行检查，但是这样的效率太低了。这时意向锁就很好解决了这个问题。事务A可以对表加IX锁，表明自己正在对表内某些数据加锁，当事务B来申请表锁的时候，只需要检查该表是否有IX锁即可，就不用一行行检查了。
表级锁的兼容性：

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

记录锁

记录锁是对索引上锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;可以防止修改该条记录。

SHOW ENGINE INNODB STATUS

1

查看锁情况。

间隙锁和临键锁
间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。
假设account表里数据如下：

那么间隙的id有(3,10)，(10,20)，(20,正无穷)
在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18;则会对(3,20]区间上锁，其它session是不能在这个区间进行修改数据的。间隙锁是在可重复读隔离级别下才会生效。
临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

插入意向锁

插入意向锁是一种由insert操作之前设置的间隙锁。此锁表示插入意图，如果多个事务插入的数据不在同一个区间内，则不需要相互等待。因为行是不冲突的。

• 以下演示了插入意向锁：
  Client A 创建一个包含两条索引记录（90 和 102）的表，然后启动一个事务，该事务对 ID 大于 100 的索引记录放置排他锁。 排他锁包括记录 102 之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

12345678910

客户端 B 开始一个事务以在间隙中插入一条记录。事务在等待获得排他锁时使用插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

12

自增锁（AUTO-INC 锁）

自增锁使用表级锁AUTO_INCREMENT列。主要用于插入事务能获取连续的主键值。

Innodb中的死锁

死锁演示

事务A：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 50 for update;
+----+------------+---------+------+
| id | uname      | address | age  |
+----+------------+---------+------+
| 50 | ooBQzZxssN | LsFXWO  |  101 |
+----+------------+---------+------+
1 row in set (0.00 sec)

mysql> delete from student where id = 40;
Query OK, 1 row affected (11.99 sec)

mysql> 

123456789101112131415

事务 B：

mysql>  START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 40 for update;
+----+------------+---------+------+
| id | uname      | address | age  |
+----+------------+---------+------+
| 40 | FRriPBnLRY | tWCWdy  |  101 |
+----+------------+---------+------+
1 row in set (0.01 sec)

mysql> delete from student where id = 50;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

12345678910111213

死锁检测

• 当启用死锁检测（默认）时，Innodb会自动检测事务死锁并回滚一个或多个事务以打破死锁。Innodb尝试选择回滚小事务，其中事务的大小由插入、修改或更新的行数决定。
• 如果禁用死锁检测，mysql会根据设置的innodb_lock_wait_timeout来回滚事务。
  禁用死锁检测场景：
  在开启死锁检测的时候，事务阻塞的时候会循环检测其它事务是否等待本事务持有的锁来检测是否发生死锁，所以比较耗cpu性能。所以在高并发系统上，当大量线程等待同一个锁时，死锁检测会导致速度减慢。有时，禁用死锁检测，采用锁等待超时来回滚事务可能更高效。可以使用innodb_deadlock_detect 变量禁用死锁检测 。

如何避免死锁和处理死锁

死锁是数据库事务中经典的问题，但死锁并不危险，除非死锁非常频繁以至于无法运行某些事务。可以使用以下方式来处理死锁并降低死锁发生的可能性：

1. 在发生死锁后，使用SHOW ENGINE INNODB STATUS命令来查看最近死锁的原因，并调整应用程序来避免死锁。
2. 如果频繁的发生死锁，通过启用innodb_print_all_deadlocks变量来收集死锁信息。所有的死锁信息都记录在mysql的错误日志中，完成调试后禁用此选项。
3. 如果是由于死锁而失败，可以再次重新发出事务。死锁并不危险，再试一次。
4. **保持事务小而短，**以减少它们冲突的可能性。
5. 在进行一组更改后立即提交事务，避免mysql长时间打开未提交的事务。
6. 如果使用锁定读取（select… for update或者select… for share），可以使用RC隔离级别。
7. 不同的事务操作同一张表时，操作顺序应保持一致。
8. 使用合适的索引，以减少加锁的数量。

锁优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围。
3. 尽可能减少检索条件范围，避免间隙锁。
4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行。
5. 尽可能低级别事务隔离。

事务调度

Innodb采用CATS算法来调度事务。

CATS算法

Contention-Aware Transaction Scheduling，争抢感知事务调度。
可以看下这篇文章：
CATS简介

简单理解就是说，FIFO算法是先来等待锁的事务先获取锁，依次排队，这样算法有很大的优化空间，因为后面的事务获取锁后释放的数据可能比先来的事务释放更多的数据。一个事务操作数据上的锁可能被后面的多个事务等待，所以CATS根据评估事务加锁等待事务数来设置事务权重，这样可以将锁分配给权重大的事务以提高MySQL的吞吐量。如果权重相等，则分配给等待时间更长的事务。

有一个比喻的例子：如果有一个出租车司机和一个公交车司机都在等咖啡，那么先给公交车司机做咖啡（即使公交车司机比出租车司机迟来）可能会让更多的人尽早到达他们的目的地。因为公交车上的乘客比出租车上的乘客多。这看起来似乎对出租车司机不公平，但是这种策略可以使得整个系统运行的更快，这对于系统内的每个人都是有利的。

Mysql bufferPool

数据变更执行流程及数据丢失分析