MySQL存储引擎&索引&事务

发布时间：2024年01月15日

一.存储引擎

1.创建的存储引擎

MyISAM 不支持事务、也不支持外键，索引采用非聚集索引，其优势是访问的速度快，对事务完整性没有要求，以 SELECT 、 INSERT 为主的应用基本上都可以使用这个存储引擎来创建表。 MyISAM 的表在磁盘上存储成 3 个文件，其文件名都和表名相同，扩展名分别是：
  • .frm（存储表定义）
  • .MYD（MYData，存储数据）
  • .MYI （MYIndex，存储索引）
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，支持自动增长列，外键等功能,?索引采用聚集索引，索引和数据存储在同一个文件，所以InnoDB 的表在磁盘上有两个文件，其文件名都和表名相同，扩展名分别是：
  • .frm（存储表的定义）
  • .ibd（存储数据和索引）
MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表实际只对应一个磁盘文件，格式是.frm （表结构定义）。 MEMORY 类型的表访问非常快，因为它的数据是放在内存中的，并且 默认使 用 HASH 索引（不适合做范围查询），但是一旦服务关闭，表中的数据就会丢失掉。
在mysql中可以使用查看存储引擎
show engines;

可以进入mysql的存储目录查看存储的文件

2.区别

种类
锁机 制
B- 树索
引
哈希索
引
外键 事务索引缓存数据缓存
MyISAM表锁 支持 不支持 不支持 不支持 支持 不支持
InnoDB 行锁 不支持 支持支持支持支持支持
Memory表锁支持 支持
不支
持
不支
持
支持支持

  • 锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度。
  • B-树索引和哈希索引：主要是加速SQL的查询速度。
  • 外键：子表的字段依赖父表的主键，设置两张表的依赖关系。
  • 事务：多个SQL语句，保证它们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要回滚事务。
  • 索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O（数据库的性能提升，目的是为了减少磁盘I/O操作来提升数据库访问效率），读取上一次内存中查询的缓存就可以了。

二.索引?

1.简介

当表中的数据量到达几十万甚至上百万的时候， SQL 查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL 查询。
由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘 I/O 操作。如果索引创建过多，使用不当，会造成SQL 查询时，进行大量无用的磁盘 I/O 操作，降低了 SQL 的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！
索引的优点： 提高查询效率
索引的缺点： 索引并非越多越好，过多的索引会导致 CPU 使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O 造成 CPU 负荷太重

2.索引的分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。 索引的核心是提高查询 的速度！
物理上 可以分为 聚集索引和非聚集索引

逻辑上可以分为以下几类

  1. 普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表，数量是不限的，一张表的一次sql查询只能用一个索引 where a=1 and b='M')
  2. 唯一性索引：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
  3. 主键索引：使用Primary Key修饰的字段会自动创建索引(MyISAM, InnoDB)
  4. 单列索引：在一个字段上创建索引
  5. 多列索引：在表的多个字段上创建索引 (uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上)
  6. 全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度(线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高效的搜索 elasticsearch 简称es C++开源的搜索引擎 搜狗的workflow)

3.索引的创建和删除

创建表的时候指定索引字段：
CREATE TABLE index1(id INT ,
name VARCHAR ( 20 ),
sex ENUM ( 'male' , 'female' ),
INDEX(id,name));
在已经创建的表上添加索引：
CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（ length ） [ ASC | DESC ]);

例如

create index? nameidx on student(name(10));

删除索引 :
DROP INDEX 索引名 ON 表名 ;

结合实践讲出来?

注意点:?

  1. 经常作为where条件过滤的字段考虑添加索引
  2. 字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度key_len过长
  3. 索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了

?4.索引的执行过程

1.explain查看执行计划

使用explain查看sql的执行计划，分析索引的执行过程，查看user表的索引：

explain select * from student where uid=1;

?explain select * from student where name='zhangsan';

2.explain结果字段分析

  • select_type
    simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询为simple且只有一个。

    primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary且只有一个。

    union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是 union。

    union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null。
  • table
    显示查询的表名；

    如果不涉及对数据库操作，这里显示null；

    如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生的；

    如果是尖括号括起来<union M,N>也是一个临时表，表示这个结果来自于union查询的id为M，N的结果集；
  • type
    const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type就是 const。

    ref：常见于辅助索引的等值查找，或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找会出现；返回数据不唯一的等值查找也会出现。

    range：索引范围扫描，常见于使用<、>、is null、between、in、like等运算符的查询中。

    index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文件的查询，可以使用索引排序或者分组的查询。

    all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。
  • ref
    如果使用常数等值查询，这里显示const；
    如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；
  • Extra
    using filesort：排序时无法用到索引，常见于order by和group by语句中。
    using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

?5.索引的底层实现原理

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只
能逐一加载每一个磁盘块（对应索引树的节点），索引树越低，越 “ 矮胖 ” ，磁盘 IO 次数就
少
MySQL 支持两种索引，一种的 B树索引，一种是哈希索引，大家知道，B树和哈希表在数据查询时的效率是非常高的。
这里我们主要讨论一下 MySQL InnoDB存储引擎，基于B树（但实际上MySQL 采用的是 B+ 树结构）的索引结构。
B树是一种m 阶(一般在200-300)平衡树，叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个 B- 树的层数是非常低的，基本上不超过三层。
由于磁盘的读取也是按 block 块操作的（内存是按 page页面操作的），因此B树的节点大小一般设置为和磁盘块大小一致，这样一个B树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B- 树存储索引的时候，磁盘 I/O 的操作次数是最少的（ MySQL 的读写效率，主要集中在磁盘I/O上）。

1.B树

data存储的是数据本身还是磁盘上的地址?

不同的存储引擎对应的不同,对于MyISAM存储的是磁盘上的地址,因为存储数据的文件与存储索引的文件不同,而InnoDB存储的是数据本身,因为数据和索引存储在一个文件中

AVL树和B树的搜索时间是一样的,但是B树更加矮胖,因此它的磁盘IO的次数更少

从上图可以看到 B 树存在的缺点：
  • 每个节点中有key，也有data，但是每一个节点的存储空间是有限的，如果data数据较大时会导致每个节点能存储的key的数据很小
  • 当存储的数据量很大时同样会导致B树的高度较大，磁盘IO次数花费增大，效率降低

2.B+树

那么 MySQL 最终为什么要采用 B+ 树存储索引结构呢，那么看看 B- 树和 B+ 树在存储结构上有什么不同？
  1. B树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B树的，B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B树，使用的磁盘I/O次数少，因此查询会更快一些。
  2. B树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
  3. 在B树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。
索引的底层原理:对于一个SQL语句,mysql server先检查过滤的字段有没有索引,如果没有索引就做整表搜索,有索引的话操作系统kernel或从磁盘上的索引文件中先把索引数据加载到内存上,用B+树构建,为什么用B+树来构建呢?因为B+树是一个平衡树,搜索的效率是非常好的,B+树是每一个节点每一个节点构建,每一个节点对应的刚好是一个磁盘IO,非叶子节点存的都是key,这样相对于B树来说存储的key值较多,key和data都是存储在叶子节点的,所以用B+树来构造叶子结点的话,会以花费最少的磁盘IO的次数,以二分搜索的时间复杂度,来找见索引的数据

6.InnoDB的主键和二级索引树

主键就是primary key,二级索引就是普通的索引和unique

1.主键索引树

场景一:uid是主键

data中存储的是表中的对应主键的数据?

2.二级索引树

场景二:uid是主键,name创建普通索引(二级索引)

data中存储的是主键,所以如果查询了普通索引和主键的值,直接可以在二级索引树上查找到数据并且进行返回,如果查询到之外的数据,需要进行回表操作,也就是需要拿着查询到的主键索引,到主键索引树上查询到相应的数据进行返回

一次SQL查询只会查询一个索引树.但是如果我们SQL如果中涉及到两个字段,那么一个索引树可以索引命中呢?此时我们可以建立多列索引,比如对于以下的SQL

select * from user where age=20 order by name;

?此时建立age和name的多列索引,二级索引树上key存储的就是age,name,data存储的是主键

注意:对于多列索引,查询中必须包含第一个字段(可以不包含其他的建立多列索引的字段),否则索引不命中.

如果对于含有多个字段的过滤,每个字段有单独的索引,因为mysql只会选择一个二级索引树进行搜索,所以之后选择其中一个,那么选择哪个呢?MySQL会看看到底是按哪个字段取的数据量少,就会选择哪个二级索引树.

如果想要强行使用某个索引,可以使用

select * from user force index(age) where name='zhangsan' and age=10;

如果name和age都建立了二级索引,因为name的数据量少,所以会选用name索引树进行搜索,此时我们可以使用强制索引,强制使用age索引树搜索?

7.MyISAM的主键和二级索引树

1.主键索引树

data存放的是数据的地址

2.二级索引树

此时二级索引树data存放的不是主键,二级相应的数据地址,不涉及回表的操作了.

8.聚簇索引和非聚簇索引?

MyISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储，例如一个user表，会在磁盘上存储三个文件 user.frm（表结构文件） user.MYD（表的数据文件） user.MYI（表的索引文件）。

MyISAM 的索引方式也叫做非 聚簇 索引。
InnoDB的索引树叶节点包含了完整的数据记录，这种索引叫做 聚簇 索引。因为 InnoDB 的数据文件本身要按主键聚集，所以InnoDB 要求表必须有主键（区别于 MyISAM 可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形

9.哈希索引?

哈希表中的数据没有任何数据可言,只能做等值查询

1.没办法处理磁盘上的数据，加载到内存上构建高效的搜索数据结构，因为它没有办法减少磁盘IO的次数
2.只适合做等值搜索，其它的范围、排序、前缀搜索等不合适

10.InnoDB自适应哈希索引

lnnoDB存储引擎监测到同样的二级索引不断被使用，那么它会根据这个二级索引，在内存上根据二级索引树（B+树)上的二级索引值，在内存上构建一个哈希索引，来加速搜索


自适应哈希索引本身的数据维护也是要耗费性能的，并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能!根据参数指标，来具体分析是否打开或者关闭自适应哈希索引!! !

show engine innodb status\G

能看到两个比较重要的信息:

  1. RW-latch等待的线程数量（自适应哈希索引默认分配了8个分区)，同一个分区等待的线程数量过多
  2. 走自适应哈希索引搜索的频率和二级索引树搜索的频率
    ?

如果等待的线程数量多并且哈希索引搜索的频率低,最好关闭自适应哈希索引

三.索引总结

  1. MySQL以主键的值构造成一颗树，叶子节点存放着该主键对应的整行数据。此为聚簇索引。
  2. 其他的索引为辅助索引，叶子节点存放着索引字段的值及对应的主键值。
  3. 一般情况下，—次查询只能使用一条索引
  4. 对查询where条件中区分度高的字段加索引
  5. 联合索引，叶子节点存储的顺序以创建时指定的顺序为准，因此区分度高的放左边，能被多个查询复用到的放左边
  6. 只select需要用到的字段，尽量避免select*
  7. 如有必要，可使用FORCE INDEX强制索引
  8. 多表JOIN，先按各表的查询条件比较哪个开销小，从小表取出所有符合条件的，到大表循环查找
  9. 以下情况无法使用到索引，like通配符在最左，not in,=, <>,对列做函数运算，隐式数据类型转换，OR子句
    ?

面试题:如果优化SQL或索引

实际项目中是千万条的SQL语句,首先我们要能够查询到哪些SQL语句是慢的,然后通过explain进行具体情况进行分析

  1. 慢查询日志
  2. 压测执行各种业务!!
  3. 查看慢查询日志，找出所有执行耗时的sql
  4. 用explain分析这些耗时的sql
  5. 举例子。。

show variables like '%slow_query%';

?set global slow_query_log=on;

?show variables like 'long%';

?set long_query_time = 1;

?

show profiles命令可有查看sql具体的运行时间，全局变量的名字是：profiling?

set global profiling=on?

show profiles查看

四.MySQL事务?

1.什么是事务

一个事务是由一条或者多条对数据库操作的 SQL 语句所组成的一个不可分割的单元，只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库；如果有部分事务处理失败，那么事务就要回退到最初的状态，因此，事务要么全部执行成功，要么全部失败。
所以记住事务的几个基本概念，如下：
  1. 事务是一组SQL语句的执行，要么全部成功，要么全部失败，不能出现部分成功，部分失败的结果。保证事务执行的原子操作。
  2. 事务的所有SQL语句全部执行成功，才能提交（commit）事务，把结果写回磁盘上。
  3. 事务执行过程中，有的SQL出现错误，那么事务必须要回滚（rollback）到最初的状态。

刚开始我们拿一个常见的操作来说明我们为什么需要事务.假如现在张三给李四转账100元,我们需要先把张三账户的钱减少100,然后再把李四账户的钱增加100.但是如果张三账户的钱减少100的时候突然发生网络异常,那么张三减少了100,但是李四却没有增加100,这个时候就会出现问题了.

update accout set money=money-100 where name='张三'

update accout set money=money+100 where name='李四'

为了避免这种问题的发生,因此出现了事务(transaction)

?MyISAM是不支持事务的,InnoDB是支持事务,行锁的

select @@autocommit;

默认是自动提交的,可以自行设置为0,不自动提交

set?autocommit=0;

2.事务的ACID特性

每一个事务必须满足下面的 4 个特性：
事务的原子性（ Atomic ）：
事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。
事务的一致性（ Consistency ）：
一个事务执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态必须由用户来负责，由并发控制机制实现。就拿网上购物来说，你只有让商品出库，又让商品进入顾客的购物车才能构成一个完整的事务。
事务的隔离性（ Isolation ）：
当两个或者多个事务并发执行时，为了保证数据的安全性，将一个事物内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到，使得并发执行的各个事务之间不能互相影响。
事务的持久性（ Durability ）：
事务完成 (commit) 以后， DBMS 保证它对数据库中的数据的修改是永久性的，即使数据库因为故障出错，也应该能够恢复数据！
ACD:是由mysql的redo log和undo log机制来保证的
l:隔离性，是由my和ql事务的锁机制来实现保证的
?

3.事务并发存在的问题

事务处理不经隔离，并发执行事务时通常会发生以下的问题：
脏读 （ Dirty Read ）：一个事务读取了另一个事务未提交的数据。例如当事务 A 和事务 B 并发执行时，当事务A 更新后，事务 B 查询读取到 A 尚未提交的数据，此时事务 A 回滚，则事务 B 读到的数据就是无效的脏数据。（事务B 读取了事务 A 尚未提交的数据）
不可重复读 （ NonRepeatable Read ）：一个事务的操作导致另一个事务前后两次读取到不同的数据。 例如当事务A 和事务 B 并发执行时，当事务 B 查询读取数据后，事务 A 更新操作更改事务 B 查询到的数据，此时事务B 再次去读该数据，发现前后两次读的数据不一样。（事务 B 读取了事务 A 已提交的数据）
幻读 （ Phantom Read ）幻读：一个事务的操作导致另一个事务前后两次查询的结果数据量不同。例如 当事务A 和事务 B 并发执行时，当事务 B 查询读取数据后，事务 A 新增或者删除了一条满足事务 B 查询条件的记录，此时事务B 再去查询，发现查询到前一次不存在的记录，或者前一次查询的一些记录不见了。 （事务B 读取了事务 A 新增加的数据或者读不到事务 A 删除的数据）

4.事务的隔离级别

MySQL 支持的四种隔离级别是：
1 、 TRANSACTION_READ_UNCOMMITTED。未提交读。说明在提交前一个事务可以看到另一个事务的变化。这样读脏数据，不可重复读和幻读都是被允许的。
2 、 TRANSACTION_READ_COMMITTED。已提交读。说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和幻读产生。
3 、 TRANSACTION_REPEATABLE_READ 。可重复读。说明事务保证能够再次读取相同的数据而不会失败，但幻读仍然会出现。可以解决一部分的幻读问题
4 、 TRANSACTION_SERIALIZABLE。串行化。是最高的事务级别，它防止读脏数据，不可重复读和幻读。
具体的处理可以参考这篇博客: 数据库事务-CSDN博客

5.MySQL的事务处理命令

打开 MySQL 的 Command 命令行窗口，测试以下命令：
1 、 SELECT @@AUTOCOMMIT; 查看 MySQL 是否自动提交事务

?0表示手动提交事务，1表示自动提交事务，设置事务提交方式为手动提交方式：

set autocommit= 0 ;
BEGIN; 开启一个事务
COMMIT; 提交一个事务
ROLLBACK; 回滚一个事务到初始的位置
SAVEPOINT point1; 设置一个名字为 point1 的保存点
ROLLBACK TO point1; 事务回滚到保存点 point1 ，而不是回滚到初始状态
SET TX_ISOLATION='REPEATABLE-READ'; 设置事务的隔离级别
SELECT @@TX_ISOLATION; 查询事务的隔离级别

?五.MySQL的锁机制

1.表级锁&行级锁

表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。
行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。
InnoDB的行锁是加在索引项上面的，是给索引在加锁，并不是给单纯的行记录在加锁;索引如果过滤条件没有索引的话，使用的就是表锁，而不是行锁!!!
所以说InnoDB既有行锁,也有表锁

2.排它锁和共享锁

排它锁（ Exclusive ），又称为 X 锁，写锁。
共享锁（ Shared ），又称为 S 锁，读锁。
X 和 S 锁之间有以下的关系： SS 可以兼容的， XS 、 SX 、 XX 之间是互斥的
  • 一个事务对数据对象 O 加了 S 锁，可以对 O 进行读取操作但不能进行更新操作。加锁期间其它事务能对O 加 S 锁但不能加 X 锁。
  • 一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。
  • 显示加锁：select ... lock in share mode强制获取共享锁，select ... for update获取排它锁

3.InnoDB行级锁

recore lock:行锁

gap lock:间隙锁

next key lock:record lock+gap lock;

1.行级锁

InnoDB 存储引擎支持事务处理，表支持行级锁定，并发能力更好。
  1. InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。
  2. 由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。
  3. 即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。
  4. 通过普通索引加上的锁,实际上是根据普通索引映射到对应的主键,给对应的索引项加锁来实现的,因此通过不同的过滤条件,只要映射到了相同的索引项,也是被锁住的

2.间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时， InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“ 间隙（ GAP)” ， InnoDB 也会对这个“ 间隙 ” 加锁，这种锁机制就是所谓的间隙锁。举例来说， 假如 user 表中只有 101 条记录， 其userid 的值分别是 1,2,...,100,101 ， 下面的 SQL ：
select * from user where userid > 100 for update ;
是一个范围条件的检索， InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁，也会对 userid 大于 101 （但是这些记录并不存在）的 " 间隙 " 加锁，防止其它事务在表的末尾增加数据。
InnoDB 使用间隙锁的目的，为了防止幻读 ， 以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 userid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读。
间隙锁作用于普通索引.
使用select * from user where age>20;
此时插入age=20的用户数据是无法插入成功的,因为辅助索引是先按age进行排序存储到B+树上的,然后按照主键的大小进行排序,此时间隙锁加到的(20,21],(21,25],(25,﹢无穷]上的,而恰好age=20,自增的主键是大于age=20的主键12的,因此刚好加到间隙锁
此时进行范围查询加的是next key lock(也就是gap lock+record lock)对大于20的数据加record lock,对之间的间隙加gap lock;
主键或者唯一键进行等值查询的时候,加的都是行锁(此时没有间隙锁gap lock),插入是不会受到影响的,不用担心间隙锁
当普通索引进行等值查询的时候,会对两边的间隙加间隙锁,对符合条件的数据加行锁

4.MVCC

先来了解一下undolog

undo log ：回滚日志，保存了事务发生之前的数据的一个版本，用于事务执行时的回滚操作，同时也是实现多版本并发控制（MVCC ）下读操作的关键技术。
DB_TRX_ID:事务 ID DB_ROLL_PTR: 回滚指针

?

MVCC 是多版本并发控制（ Multi-Version Concurrency Control ，简称 MVCC ），是 MySQL 中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别的实现，也经常称为多版本数据库。MVCC 机制会生成一个数据请求时间点的一致性数据快照 （ Snapshot) ， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本（系统版本号和事务版本号）。
MVCC 多版本并发控制中，读操作可以分为两类：
1 、快照读（ snapshot read ）
读的是记录的可见版本，不用加锁。如select
2 、当前读（ current read ）
读取的是记录的最新版本，并且当前读返回的记录。如insert，delete，update，select...lock in share mode/for update
MVCC ：每一行记录实际上有多个版本，每个版本的记录除了数据本身之外，增加了其它字段
DB_TRX_ID ：记录当前事务 ID
DB_ROLL_PTR ：指向 undo log 日志上数据的指针
已提交读 ：每次执行语句的时候都重新生成一次快照（ Read View ），每次 select 查询时。
为什么无法解决”不可重复读”?
因为每一次select都会重新产生一次数据快照，其它事务更新后而且已提交的数据，可以实时反馈到当前事务的select结果当中!

为什么无法解决”幻读”?
因为每一次select都会重新产生一次数据快照，其它事务增加了和当前事务查询条件相同的新的数据并且已成功commit提交，导致当前事务再次查询时，数据多了
可重复读 ：同一个事务开始的时候生成一个当前事务全局性的快照（ Read View ），第一次 select 查询时。
为什么解决了”不可重复读”?为什么无法解决”幻读”?
第一次select产生数据快照，其它事务虽然修改了最新的数据，但是当前事务select时，依然查看的是最初的快照数据
快照内容读取原则 ：
  1. 版本未提交无法读取生成快照
  2. 版本已提交，但是在快照创建后提交的，无法读取
  3. 版本已提交，但是在快照创建前提交的，可以读取
  4. 当前事务内自己的更新，可以读到

4.意向共享锁和意向排他锁

要获取一张表的共享锁S或者排它锁X，最起码得确定，这张表没有被其它事务获取过X锁!这张表(1千万)里面的数据没有被其它事务获取过行锁X锁!

因此当我们加表锁的时候,需要一行一行的扫描是否加过行锁,这样效率是十分低的,因此出现了意向锁

意向共享锁（ IS 锁）：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须先取得该表的 IS 锁。
意向排他锁（ IX 锁）：事务计划给记录加行排他锁，事务在给一行记录加排他锁前，必须先取得该表的 IX 锁
  1. 意向锁是由InnoDB存储引擎获取行锁之前自己获取的
  2. 意向锁之间都是兼容的，不会产生冲突
  3. 意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）
  4. 意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。

5.InnoDB表级锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择 InnoDB 的理由，但个别情况下也使用表级锁；
1 ）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；
2 ）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
如：
LOCK TABLE user READ ；读锁锁表
LOCK TABLE user WRITE; 写锁锁表
事务执行 ...
COMMIT/ROLLBACK; 事务提交或者回滚
UNLOCK TABLES; 本身自带提交事务，释放线程占用的所有表锁

6.死锁?

MyISAM 表锁是 deadlock free 的， 这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。
死锁问题一般都是我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

7.锁的优化建议

  1. 尽量使用较低的隔离级别
  2. 设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力
  3. 选择合理的事务大小，小事务发生锁冲突的概率小
  4. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  5. 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
  6. 不要申请超过实际需要的锁级别
  7. 除非必须，查询时不要显示加锁
文章来源:https://blog.csdn.net/qq_64580912/article/details/135582848
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章