MySQL常用知识点、索引、事务、存储引擎、锁机制_mysql 索引、事务、存储引擎、锁机制-CSDN博客

本文链接：https://blog.csdn.net/yin_fei_lnmp/article/details/106318645

一、索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

1.1、索引概念、索引模型

常见的MySQL主要有两种数据结构，hash索引和B+Tree索引。InnoDB引擎默认是B+Tree。

1.1.1、B+Tree：

多路搜索树，通常用于数据库索引和操作系统的文件系统中，综合效率较高。

m阶B+Tree的特性：
1、有m个子树的节点包含有m个元素（B-Tree中是m-1）；
2、根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中；
3、所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或最小的元素。
4、叶子节点会包含所有关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接。

1.1.2、B+Tree于Hash索引的优缺点

Hash索引的底层是哈希表，是以一种 key-value 存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的。对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以哈希索引只适用于等值查询的场景。

B+Tree是一种多路平衡查询树，所以它的节点是天然有序的（左子节点小于父节点，父节点小于右子节点），对于范围查询的时候不需要做全表扫描。

①哈希索引适合等值查询，但无法进行范围查询；
②哈希索引没办法利用索引完成排序；
③哈希索引不支持多列联合索引的最左匹配规则；
④如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

哈希碰撞：
所谓哈希，就是将不同的输入映射成独一无二的、固定长度的值（又称‘哈希值’），如果不同的输入得到了同一个哈希值，就发生了 ‘哈希碰撞’；
黑客攻击的一种方法，就是设法制造‘哈希碰撞’，然后入侵系统，窃取信息，也叫生日攻击（利用哈希空间不够大，而制造哈希碰撞）。
处理方法：开放寻址法和链接法。

1.2、聚簇索引、覆盖索引

1.2.1、聚簇索引

B+Tree的子节点可能存储的是整行数据，也可能是主键的值。

在InnoDB里，索引B+Tree的叶子节点存储了整行数据的是主键索引，也叫聚簇索引（聚集索引）；
而叶子节点存储了主键的值的是非聚簇索引；

InnoDB是必须要有聚集索引的：
1、如果表定义了主键，那么主键就是聚集索引；
2、如果表没有定义主键，则第一个不为空、唯一的（ not NULL unique）列是聚集索引；
3、否则，InnoDB会创建一个隐藏的 row-id作为聚集索引。

查询数据的时候，聚簇索引会更快；
因为主键索引树的叶子节点直接就是我们要查的整行数据。而非主键索引的叶子节点是主键的值，查到主键的值后，还需要通过主键的值再进行一次查询（回表）；

1.2.2、覆盖索引

而通过覆盖索引则就可以不用回表；

覆盖索引：指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，也可称为实现了覆盖索引。
常见方法：将别查询的字段，建立到联合索引里去。

explain查询执行计划时，输出结果Extra字段为Using index时，能够触发覆盖索引。

当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后在返回表操作，减少 I/O，提高效率。

1.3、联合索引、最左前缀匹配

1.3.1、多列联合索引

联合索引：对多个字段同事建立索引（有顺序区分，ABC，ACB是完全不同的两种联合索引）。
一般对于查询概率比较高，经常作为where条件的字段设置索引。

在创建联合索引时，多个字段之间的顺序是把识别度最高的放到最前面，这样命中率会高一点。

在创建多列联合索引时，根据业务需求，where子句种使用最频繁的一列放到最左边（最前面），因为MySQL索引查询遵循最左前缀匹配的原则，即最左优先，在检索数据时，从联合索引的最左边开始匹配。

1.3.2、最左匹配原则

所以当我们创建一个多列联合索引时，如（A,B,C），相当于创建了（A），（A,B）和（A,B,C）三个索引。
查询数据时搜索 ‘A’，‘A,B’，‘A,B,C’ 索引都是有效的，但是 ‘B,C’ 或者’A,C’、‘B’、'C’索引都是无效的，这就是多列联合索引的最左匹配规则。----组合索引具有有序性。

1.4、索引下推，查询优化

1.4.1、索引下推

MySQL 5.6 引入了索引下推优化；默认开启，使用 set optimizer_switch = 'index_condition_pushdown=off' 可以将其关闭，官方文档给出的例子：
name 表中（a,b,c）构成一个索引

select * from name where a='1234' and b like '%bb%' and c like '%cc%';

如果没有索引下推技术，则MySQL 会通过 a=1234 从存储引擎中查询对应的数据，返回mysql服务器，然后 MySQL 服务器基于 b like '%bb%' 和 c like '%cc%' 来判断数据是否符合条件。

如果使用了索引下推技术，则mysql 首先会返回符合 a=1234 的索引后，根据 b like '%bb%' 筛选出符合条件的索引后再返回到MySQL服务端，然后MySQL服务端基于 c like '%cc%' 来判断数据是否符合条件，这样返回给 MySQL服务端的索引数又会减少，有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

1.4.2、查询优化

可以通过explain查看sql语句的执行计划，通过执行计划来分析索引的使用情况。

查询优化器：一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器来选择。选择执行成本部最低的方案；
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划，优化过程大致如下：
①根据搜索条件找出所有可能使用的索引；
②计算全表扫描的代价；
③计算使用不同索引执行查询的代价；
④对比各种执行方案的代价，然后执行成本最低的那一个。

二、事务

事务具有ACID 特性。（原子性、一致性、隔离性、持久性）

1、原子性

Atomicity : 一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作。

2、一致性

Consistency ：事务的执行不能破坏数据库数据的完整性和一致性。一个事务在执行前和执行后，数据库都必须处于一致性状态。
如果数据库系统在运行时发生故障有些事务尚未完成就被迫中断，这些未完成的事务对数据库的修改有一部分已经写入物理数据库，这时数据库就处于一种不正确的状态，也就是不一致的状态。

3、隔离性

Isolation : 在开发环境中，并发的事务都是相互隔离的，不会相互干扰

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所作的修改，哪些在事务内和事物间是可见的，哪些是不可见的
较低级别的隔离通常可以执行更高的并发，系统的开销也更低，但是事务的一致性就不会高。

3.1、未提交读

READ UNCOMMITTED：未提交读（会发生脏读）—一个事物可以读取到另一个事务尚未提交的变更

在这个级别，事务中的修改，即使没有提交，对其他事务也是可见的；
事务可以读取未提交的数据，这也被称为脏读；
这个级别会导致很多问题，从性能上来说，未提交读不会比其他级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用；

3.2、已提交读

READ COMMITTED：已提交读（也叫不可重复读）—一个事务提交后，其变更才会被另一个事务读取到

- 大多数数据库系统的默认隔离级别都是 提交读（MySQL不是）；

提交读满足前面提到的隔离性的简单定义，一个事物开始时，只能‘看见’已经提交的事务所作的修改。换句话说：一个事务从开始到提交之前，所作的任何修改对其他事务都是不可见的；
这个级别有时候也叫不可重复读，因为两次执行同样的查询，可能会得到不一样的结果

3.3、可重复读

REPEATABLE READ：可重复读（会发生幻读）—在一个事务执行的过程中所读取到的数据，和事务启动时，所看到的一致

可重复读解决了脏读的问题。改级别保证了在同一个事务中多次读取同样记录的结果是一致的；
但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。
可重复读是MySQL的默认事务隔离级别。
MySQL通过多版本并发控制（MVCC）解决了幻读问题。

3.4、串行化

SERIALIZABLE：可串行化（可序列化）—当操作一行数据时，读写分别都会加锁。当出现读写互斥时，会排队串行执行。

是最高的隔离级别
它通过强制事务串行执行，避免了前面说的幻读问题。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。
实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受每一并发的情况下，才考虑用该级别。

4、持久性

Durability ：一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久性的保存到数据库中。即使发生系统崩溃或机器宕机等故障，只有数据库能够重新启动，那么一定能将其恢复到事务成功结束的状态。

三、存储引擎

1、MyISAM存储引擎

基于ISAM存储引擎，并对其进行扩展。它是在web数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM具有较高的插入速度，查询速度，但不支持事务。

不支持事务，也不支持外键。优势是访问速度快，对事物完整性没有要求、或者以 select、insert为主的应用基本上可以用这个引擎来创建表。
支持三种不同的存储格式：静态表、动态表、压缩表

静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的；
- 优点是存储非常迅速，容易缓存，出现故障容易恢复；
- 缺点是占用空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）。

需要注意的：在取数据时，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

动态表：记录不是固定长度的，这样存储的优点是占用空间相对较少；缺点是频繁的更新、删除数据容易产生碎片，需要定期执行 OPTIMIZE TABLE或者 myisamchk -r命令来改善性能；
压缩表：因为每个记录都是被单独压缩的，所以只有非常小的访问开支；

特点：

大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
当把删除和更新及插入操作混合使用时，动态尺寸的行产生更少的碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大列数是16.
Null被允许在索引的列中，这个值占每个键的0-1个字节。
可以把数据文件和索引文件放在不同目录。

myisam使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址。

2、InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全（ACID），支持行锁定和外键，是MySQL的默认引擎。

2.1、特性

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复的事务安全（ACID兼容）存储引擎；InnoDB锁定在行级并且也在select语句中听一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将innodb类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合。
2、InnoDB是为处理巨大数据量的最大性能设计；它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。 InnoDB将它的表和索引存储在一个逻辑表空间中， 表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM不同，比如在MyISAM表中每个表和索引放在分离的文件中。 InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
4、支持外键完整性约束。 存储表中数据时，灭表的数据都按主键顺序存放，如果没有显式在表定义时指定主键，InnoDB会为每一行生产一个6字节的 ROWID，并以此作为主键。

2.2、区别

与MyISAM都是使用B+Tree作为索引结构，但是实现方式上有所不同：

第一个重大区别就是InnoDB的数据文件本身就是索引文件，这棵树的叶子节点data域保存了完整的数据记录（聚集索引）；MyISAM索引文件和数据文件是分离的，InnoDB要求表必须有主键。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL系统会自己选择一个可以唯一标识数据的列作为主键，若不存在这种列，则MySQL自动为InnoDB生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形
第二个区别是与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录的主键的值而不是地址。

聚集索引这种实现方式使得主键的搜索十分高效，但辅助索引搜索要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

因此，不建议用过长字段作为主键，因为所有辅助索引都引用主索引。

再例如。使用非单调子弹作为主键在InnoDB中不是个好主意。因为InnoDB数据文件本身是一个B+tree，非单调的主键会造成在插入记录时塑胶文件为了维持B+tree的特性而频繁的分裂调整，十分低效。

3、MEMORY存储引擎

将表中数据存储到内存中，为查询和引用其他表数据提供快速访问。

3.1、特性

1、每个表介意有多达32个索引，每个索引16列，以及500字节的最大键长度；
2、执行Hash和 BTree缩影；
3、可以再一个MEMORY表中有非固定唯一值；
4、表使用一个固定的记录长度格式；
5、不支持 BLOB 或 TEXT列；
6、支持 AUTO_INCREMENT（自增）列和对包含NULL值的列的索引；
7、表在所有客户端之间共享；
8、表被存储在内存中，内存是MEMORY表和服务器在查询处理时的空间中，创建的内部表共享；
9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存。

4、Archive存储引擎

基本用于数据归档，它的压缩比非常的高，只允许从插入和查询操作。

5、存储引擎的选择

InnoDB：如果要提供提交、回滚、奔溃恢复能力的事务安全能力，并要求实现并发控制，它是最好的选择。

MyISAM：如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率。

MEMORY：如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选将数据存在内存中的MEMORY引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果，数据的处理速度很快，但是安全性不高。

Archive：如果只有插入和查询操作，可以选择Archive，它支持高并发的插入材质牡丹石本身不是事务安全的，非常适合存储归档数据，如存储日志信息可以使用Archive。

使用哪一种存储引擎需要灵活选择，一个数据库中多个表可以使用不同的引擎，以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

6、InnoDB和MyISAM的区别

①InnoDB支持事务，MyISAM不支持；
②InnoDB支持外键，MyISAM不支持；
③InnoDB是聚集索引，MyISAM是非聚集索引；
④InnoDB不保存表的具体行数，执行select count(*) from table时需要全部扫描，而MyISAM用了一个变量保存表的行数。
⑤InnoDB不支持全文索引，MyISAM支持全文索引，查询效率上 myisam要高；但是5.7之后 InnoDB支持全文索引了；
⑥MyISAM表格可以被压缩后进行查询操作；
⑦InnoDB支持表、行（默认）级锁，MyISAM支持表锁；
⑧InnoDB表必须有主键（没有自己会建一个），MyISAM可以没有
⑨InnoDB存储文件有frm、ibd，而MyISAM是frm，MYD，MYI
InnoDB：frm是表定义文件，ibd是数据文件（数据文件本身就是索引文件）
MyISAM:frm是表定义文件，myd是数据文件，myi是索引文件。

InnoDB引擎的四大特性：
插入缓存、二次写、自适应哈希索引、预读。

四、锁的机制

要保持数据的一致性，就会用到MySQL的锁。
InnoDB引擎共有七种类型的锁：

共享/排它锁
意向锁
记录锁
间隙锁
临键锁
插入意向锁
自增锁

1、共享/排它锁

共享锁（Share Locks ，记为 S 锁），读取时加S锁；

select * from admin where id=1 lock in share mode;

排他锁（eXclusive Locks），修改数据时加X锁（互斥锁）；

select * from admin where id=1 for update;

使用的语义为：
共享锁之间不互斥，简记为：读读可并行；
排它锁与任何锁互斥，简记为：写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。对应到数据库，可以理解为，写事务没有提交，读相关数据的select 也会被阻塞，这里的select 是指加了锁的，普通的select 仍然可以读到数据（快照读）。

快照读：读取的是快照版本，也就是历史版本的数据。普通的select就是快照读。

当前读：读取的是最新版本。update、delete、insert、select ... lock in share mode、select ... for update 是当前读。

2、意向锁

InnoDB为了支持多粒度锁机制，即允许行级锁和表级锁共存，而引入了意向锁。意向锁是指，未来的某个时刻，事务可能要加共享/排他锁了，先提前声明一个意向。

意向锁是一个表级别的锁；
意向锁又分为：
意向共享锁（IS），它预示着，事务有意向对表中的某些行加共享S锁；
意向排它锁（IX），它预示着，事务有意向对表中的某些行加排它X锁；

3、记录锁

记录锁，它封锁索引记录，而不是真正的数据记录（也叫行锁）。

锁是非主键索引，会在索引记录上加锁后，再去主键索引上加锁；
表上没有索引，会在隐藏的主键索引上加锁；
如果要锁的列没有索引，进行全表记录加锁。

select * from admin where id=1 for update;

它会在 id=1的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

4、间隙锁

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

select * from amdin where id between 8 and 15 for update;

这个SQL语句会封锁区间（8,15），以阻止其他事务插入id位于该区间的记录。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致‘不可重复读’。如果把事务的隔离级别降级为已提交读，间隙锁则会自动失效。

产生间隙锁的条件（不可重复读隔离级别下）：
使用普通索引锁定；
使用多列唯一索引；
使用唯一索引锁定多行记录。

打开间隙锁的设置，首先查看 innodb_locks_unsafe_for_binlog 是否禁用

show variables like 'innodb_locks_unsafe_for_binlog';

innodb_locks_unsafe_for_binlog 默认值为OFF；即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改my.cnf (windows是 my.ini)重启启动才行。

#在 my.cnf 里面的 [mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1

注：
1、对于指定查询某一条记录（唯一索引）的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁；
2、对于查找某一范围内的查询语句，会产生间隙锁。
3、在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样。
4、在普通索引和唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

5、临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁记录，既包含索引记录，又包含索引区间。
默认情况下，InnoDB使用 next-key locks 来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

临键锁的主要目的，就是为了避免幻读。

6、插入意向锁

对已有数据行的修改与删除，必须加强互斥锁（X锁），那么对于数据的插入，是否换需要加这么强的锁，来实施互斥呢？所以就产生了插入意向锁。
插入意向锁，是间隙锁的一种，（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间内插入记录时，如果插入的位置不冲突，不会阻塞彼此。

7、自增锁

自增锁是一种特殊的表级别锁，专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入数据，所有其他事务的插入必须等待，以便第一个插入的行，是连续的主键值。

8、总结

以上七种锁，可以按两种方式区分：
1、按锁的互斥程度来划分，可分为共享锁，排他锁

共享锁（S锁、IS锁），可以提高读读并发；
为保证数据强一致，InnoDB使用强互斥锁（X锁、IX锁），保证同一行记录修改与删除的串行性。

2、按锁的颗粒度来划分，可分为：

表锁：意向锁（IS锁，IX锁）、自增锁
行锁：记录锁、间隙锁、临键锁、插入意向锁

其中：
①InnoDB的细粒度锁（即行锁），是实现在索引记录上的
②记录锁锁定索引记录；间隙锁锁定间隔，防止间隔中被其他事务插入；临键锁锁定索引记录+间隔，防止幻读
③InnoDB使用插入意向锁，可以提高插入并发
④间隙锁、与临键锁只在 RR（不可重复读）以上的级别生效。

五、为什么MongoDB索引使用B-数，而MySQL使用B+树

1、B-树

在这里插入图片描述
特点：

树内的每个节点都存储数据；
叶子节点之间无指针相邻

2、B+树

在这里插入图片描述
特点：

数据值出现在叶子节点上；
所有叶子节点增加了一个指针。

3、结合B-树与B+树特点，做一总结

B-树的树内存储数据，因此查询单条数据时，B-树查询效率不固定，最好的情况是O(1)。我们可以认为在做单一数据查询的时候，使用B-树平均性能最好，但由于各节点五指针相邻，故不适合数据遍历操作。
B+树的数据值出现在叶子节点上，一次在查询单条数据时，查询速度非常稳定。因此，在做单一数据的查询上，其平均性能不如B-树，但是B+树的叶子节点上有指针相连，因此在做数据遍历时，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。