Mysql索引与事务

一、索引的数据结构

索引的定义：索引是帮助Mysql高效获取数据的数据结构。

索引的本质：索引是数据结构，满足特定的查找算法，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效查找算法。

索引在存储引擎中实现的，因此每种存储引擎的索引不完全一样，存储引擎可以定义每个表最大的索引数和最大索引长度。所有的引擎支持至少16个索引，总索引长度至少为256个字节。

索引的优点

  1. 提高数据检索的效率，降低数据库的IO成本。

  2. 通过创建唯一索引，可以保证数据库中每一行数据的唯一性。

  3. 使用分组和排序的子句进行查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。

索引的缺点

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

  2. 索引需要占用磁盘空间，每个索引需要像数据一样，存储在磁盘上，如果有大量索引，索引文件就可能比数据文件更快达到最大文件尺寸。

  3. 索引大大提高了查询速度，同时会降低更新表的速度，当对数据进行增删改时，索引也需要动态维护。

二、Mysql的创建与设计原则

2.1 索引的分类和操作

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照作用字段个数进行划分，分成单列索引和联合索引。

唯一索引，通过UNIQUE INDEX 创建，或者创建或修改表结构时使用UNIQUE关键字创建，无论哪种都会添加上唯一索引

CREATE UNIQUE INDEX uk_username ON sys_user(user_name);
ALTER TABLE sys_user ADD UNIQUE (user_age);

全文索引，类似slor的功能，mysql有类似的实现，但是功能远不及slor。

其他操作

查看表索引

SHOW INDEX FROM table_name;

删除索引

ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

2.2 哪些情况适合创建索引

  1. 字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

  1. 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

  1. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者 使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多 个，那么可以在这些列上建立 组合索引 。

  1. DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多

  1. 多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。如果类型不一致，正确查询的情况下会有隐式转换，隐式转换会涉及到函数，使用函数后就会索引失效。

  1. 使用字符串前缀创建索引

前缀索引(Prefix Index)是指对列值的前面一部分字符建立索引，而不是整个列值。这种索引特别适用于长字符串列（如VARCHAR、TEXT等），可以节省索引空间同时保持查询效率。

CREATE INDEX 索引名 ON 表名(列名(前缀长度));

需要注意的是，选取的前缀长度，通过计算区分度确定，越接近100%且长度越短越好。

SELECT 
    COUNT(DISTINCT LEFT(列名, 3))/COUNT(*) AS sel3,
    COUNT(DISTINCT LEFT(列名, 5))/COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(列名, 7))/COUNT(*) AS sel7,
    COUNT(DISTINCT LEFT(列名, 10))/COUNT(*) AS sel10,
    COUNT(DISTINCT 列名)/COUNT(*) AS actual_selectivity
FROM 表名;

  1. 使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

  1. 在多个字段都要创建索引的情况下，联合索引优于单值索引

2.3 哪些情况不适合建索引

  1. 在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY、ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

  1. 数据量小的表最好不要使用索引

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

  1. 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的"性别"字段上只有“男”与“·女"两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

举例 1 ：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。

  1. 避免对经常更新的表创建过多的索引

  2. 不建议用无序的值作为索引

  3. 删除不再使用或者很少使用的索引

  4. 不要定义冗余或重复的索引

2.4 总结

索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。

选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。

三、性能分析与查询优化

3.1 查看系统性能参数

查询语法如下

show [GLOBAL|SESSION] status like '参数'

以下是常用的性能参数

  • connections： 连接Mysql服务器的次数

  • uptime：Mysql服务器上线时间

  • slow_queries：慢查询次数

3.2 统计sql的查询成本

  1. 统计SQL的查询成本：last_query_cost

SHOW STATUS LIKE 'last_query_cost';

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

  1. 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。

  2. 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

  1. 查看sql执行成本：SHOW PROFILE

show variables like 'profiling';
#开启
set profiling = 'ON';
# 查看所有历史sql执行情况
show profiles;
# 更具sql查询ID, 查询cpu等情况
show profile cpu,block io for query 2;

3.3 定位执行慢的sql慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

  1. 开启慢查询日志

set global slow_query_log='ON';

  1. 查看下慢查询日志是否开启，以及慢查询日志文件的位置：

show variables like `%slow_query_log%`;

  1. 修改long_query_time

show variables like '%long_query_time%';
​
#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql > set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 
​
mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

  1. 查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

  1. 慢查询日志分析工具：mysqldumpslow

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

  1. 关闭慢查询日志

方式1：永久性方式

[mysqld] 
slow_query_log=OFF

方式2：临时性方式

SET GLOBAL slow_query_log=off;

3.4 重点 分析查询语句EXPLAIN

不论我们的查询语句有多复杂，包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

详细介绍查看链接，面试官：不会看 Explain执行计划，简历敢写 SQL 优化？

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

四、调优策略

五、数据库事务基础

5.1 事务基础知识

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

事务的四个特性

  • 原子性（atomicity）：原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

  • 一致性（consistency）：一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。

  • 隔离型（isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性（durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

一致性理解，所谓一致性是数据库在事务提交前后的状态应与业务逻辑所抽象的客观世界中真实状况保持一致。这种一致性是一种需要人为去定义的规则。如何指定规则，数据库就严格按照这种规则去处理数据。

这里拿经典的例子来说明： A有200R，B有200R，A账户向B账户转账100R。结果A、B各有100R、300R，

转账前后两个人的余额之和并没有改变还是400R，这被叫做事务一致性。即值的修改，符合人为设定的规则。

5.2 事务隔离级别

数据并发问题

  1. 脏写（Dirty Write）

对于两个事务 Session A、Session B，如果事务Session A修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写

  1. 脏读（Dirty Read）

对于两个事务 Session A、Session B，Session A读取了已经被 Session B更新但还没有被提交的字段。之后若 Session B回滚，Session A读取的内容就是临时且无效的。

  1. 不可重复读（Non-Repeatable Read）

对于两个事务Session A、Session B，Session A读取了一个字段，然后 Session B更新了该字段。 之后Session A再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

  1. 幻读（Phantom）

对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。 之后, 如果 Session A再次读取同一个表, 就会多出几行。那就意味着发生了幻读。

注意1：

有的同学会有疑问，那如果Session B中剔除了一些符合studentno > 0的记录而不是插入新记录，那么Session A之后再根据studentno > 0的条件读取的记录变少了，这种现象算不算幻读呢？这种现象不属于幻读，幻读强调的是一个事物按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

注意2：

那对于先前已经读到的记录，之后又读取不到这种情况，算啥呢？这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到之前读取没有获取到的记录。

SQL中的四种隔离级别

  • READ UNCOMMITTED：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

  • READ COMMITTED：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。

  • REPEATABLE READ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

  • SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

  • 事务的隔离性由锁机制实现。

  • 而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。 REDO LOG 称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。 UNDO LOG 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

5.3 数据库日志类型

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

MySQL InnoDB引擎使用redo log日志保证事务的持久性，使用undo log日志保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从离不开binlog，需要依赖binlog来同步数据，保证数据的一致性。

redo log是物理日志，记录的是“在某个数据页做了什么修改”，属于Innodb存储引擎。

而binlog日志是逻辑日志，记录内容是语句的原始逻辑，属于MySQL Server层。所有的存储引擎只要发生了数据更新，都会产生binlog日志。

binlog日志与redo log日志关系如下图：

redo log

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL有了崩溃恢复的能力。

首先明确，MySQL中数据是以页为单数存储，当你查询一条记录时，硬盘会把一整页的数据加载出来，加载出来的数据叫做数据页，会放到Buffer Pool中。后续的查询都是先从Buffer Pool中找，没有找到再去硬盘加载其他的数据页直到命中，这样子可以减少磁盘IO的次数，提高性能。更新数据的时候也是一样，优先去Buffer Pool中找，如果存在需要更新的数据就直接更新。然后会把“在某个数据页做了什么修改”记录到重做日志缓存（redo log buffer）里，在刷盘的时候会写入redo log日志文件里。

什么时候redo log buffer中的数据刷新到磁盘？

理想情况下，事务一提交就会进行刷盘操作，但是实际上是刷盘的时机是根据策略来决定的。

  • 0：设置为0的时候，每次提交事务时不刷盘。

  • 1：设置为1的时候，每次提交事务时刷盘。

  • 2：设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。

innodb_flush_log_at_trx_commit参数默认为1，以这个为例

其他具体流程参考，https://segmentfault.com/a/1190000041758784

binlog

MySQL数据库的数据备份、主备、主主、住从都离不开binlog，需要依赖binlog来同步数据，保证数据一致性。

binlog日志有三种格式，可以通过binlog_format参数设置，有以下三种：

  • statement

  • row

  • mixed

statement，设置statement记录的内容是SQL语句原文，比如update T set update_time = now() where id = 1，

同步数据时，会执行记录的SQL语句，但是有个问题update_time = now()这里会获取到当前系统问题，直接执行会导致与原库数据不一致。

为了解决这种问题，我们需要将binlog_format设置成row，记录的不仅仅是简单的SQL语句了，还包含了操作的具体数据

row格式记录的内容看不到详细信息，通过mysqlbinlog工具解析出来。

设置成row带来的好处就是同步数据的一致性，通常情况都设置成row，这样可以为数据库的恢复与同步带来更好的可靠性。但是这种格式需要大量的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

所以又有了一种折中方案，设置为mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否会引起数据不一致，如果是就用row格式，否则就用statement格式。

binlog的写入时机为事务执行过程中，先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中（实际先会写入page cache，然后再由fsync写入binlog文件）

write和fsync的时机可以由参数sync_binlog控制，可以配置成0、1、N(N>1)。

  • 设置成0时：表示每次提交事务都只会write，由系统自行判断什么时候执行fsync。

  • 设置成1时：表示每次提交事务都会执行fsync，就和redo log日志刷盘流程一样。

  • 设置成N时：表示每次提交事务都会write，但是积累N个事务后才fsync。

已设置N=2为例：

undo log

想要保证事务的原子性，就需要在发生异常时，对已经执行的操作进行回滚，在MySQL中恢复机制是通过undo log（回滚日志）实现的，所有事务进行的修改都会先被记录到这个回滚日志，然后再执行其他相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子。并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

另外，MVCC的实现依赖：隐藏字段、Read View、undo log。在底层实现中，InnoDB通过数据行的DB_TRX_ID和Read View来判断数据的可见性，如不可见，则通过数据行DB_ROLL_PTR找到undo log中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事物里，用户只能看到该事务创建Read View之前已经提交的修改和该事务本身做的修改。

六、数据库锁机制与MVCC

6.1 mysql锁

https://blog.csdn.net/yzj5208/article/details/81288633

https://blog.csdn.net/m0_65152767/article/details/140099556

乐观锁用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即 为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实 现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我 们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比 对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期 数据。

悲观锁 :在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中synchronized很 相似，共享锁（读锁）和排它锁（写锁）是悲观锁的不同的实现

共享锁（读锁） 共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前 其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。使用关键字lock in share mode

SELECT * from TABLE where id = "1"  lock in share mode;  #结果集的数据都会加共享锁

排它锁（写锁） 若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务 不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。使用关键字for update

select status from TABLE where id=1 for update;

6.2 Mysql锁颗粒度详解

参考链接：https://blog.csdn.net/m0_74295055/article/details/138522739

https://blog.csdn.net/m0_65152767/article/details/140099556

https://www.jb51.net/database/32323126c.htm

首先mysql从锁的颗粒度来说，分为三类

库锁

一般用于数据全库备份时使用，防止备份时出现数据不一致的情况。

# 读锁锁库，允许读，但是不允许写
flush tables with read lock;
unlock tables;

表锁

分为两类

  • 表共享读锁，允许其他事务读，但是不能往表里写数据

    lock tables 表名 read;
unlock tables;

  • 表独占写锁，不允许其他事务读或写表中的数据

    lock tables 表名 write;
unlock tables;

行锁

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）；

对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁 或者 X锁，需要我们手动加锁。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类

首先明确InnoDB的B+树索引，叶子节点是有序的双向链表。

记录锁（Record Locks）

如图，锁的就是34这条记录

对表中的记录加锁，叫做记录锁，简称行锁。

SELECT * FROM `test` WHERE `id`=34 FOR UPDATE;

它会在 id=34 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=34 这一行。需要注意的是：

  • id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。

  • 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

间隙锁（Gap Locks）

如图间隙锁就是一些区间，注意左右全开的区间，区间头尾都是记录锁，而临键锁就是间隙锁+记录锁，是个左开右闭的区间

(-无穷，6)
(6，12)
(12，16)
……

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

临键锁（Next-Key Locks）

如图临键锁就是一些区间，临键锁是间隙锁+记录锁，是个左开右闭的区间

(-无穷，6]
(6，12]
(12，16]
……
(98，正无穷]

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

总结

  • InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。

  • 索引上的等值查询(当其索引为唯一索引时)，给不存在的记录加锁时, 优化为间隙锁 。

  • 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

  • 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止

6.3 多版本并发控制MVCC

  1. 什么是MVCC MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

  2. 快照读与当前读 MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读, 而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

  • 快照读 快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读。之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

  • 当前读 当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

  1. 隔离级别 我们知道事务有 4 个隔离级别，可能存在三种并发问题：

另图：

  1. 隐藏字段、Undo Log版本链 回顾一下undo日志的版本链，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

  • trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id 隐藏列。

  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

  1. MVCC实现原理之ReadView MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

具体如何实现的，可以参考B站宋红康的视频

MVCC在可重复读下解决幻读的流程

简单描述一下MVCC，mvcc是mysql通过数据行的多个版本管理来实现数据库的并发控制。实现依赖数据行的隐藏字段、Undo Log、Read View。在READ COMMITTED和REPEATABLE READ隔离级别的事务，用于保证读到已经提交了的事务修改过的记录。先通过隐藏字段确定自己的事务ID，然后再获取ReadView，查询得到的数据，然后与 ReadView 中的事务版本号进行比较，如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照，重复比较，最终返回符合规则的数据。

当查询一条记录的时候，系统如何通过MVCC找到它：

  1. 首先获取事务自己的版本号，也就是事务 ID；

  2. 获取 ReadView；

  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

  5. 最后返回符合规则的数据。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

如表所示：

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

七、高频面试问题

7.1 mysql索引的数据结构

对于不同的存储引擎，存储结构和方式有所不同，例如默认的InnoDB，索引的底层是B+树的结构，其中最底层的每个叶子节点都是一个数据页，里面存放的数据是有序的集合，通过单向链表连接，而不同的数据页通过双向链表连接。

索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种

聚簇索引（Clustered Index）并不是一种单独的索引类型，而是一种数据存储方式。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择表的第一个唯一的的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

非聚簇索引（NoClustered Index），非聚集索引也称为二级索引或者辅助索引，对于非聚集索引，数据库会有单独的存储空间来存放。非聚集索引在查找的时候要经过两个步骤，需要先搜索非聚集索引的B+Tree，这个B+Tree的叶子结点存储的不是完整的数据行，而是主键值，当搜索完成后得到主键的值，然后拿着主键值再去搜索主键索引的B+Tree，就可以获取到一行完整的数据。也称之为回表。

7.2 mysql回表

回表查询： 先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

发生回表的几种情况，以二级索引为条件查询数据，二级索引没有存储完整的数据行，所以会先在二级索引的结构查找到结果ID，再去聚簇索引中根据id，找到完整的数据。

7.3 mysql 覆盖索引和索引下推

对联合索引 (a, b, c)，如果有如下的 SQL：select a, b, c from test where a=1 and b=2。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，从而减少了很多的随机 IO 操作。而减少 IO 操作，而减少随机 IO 是 DBA 主要的优化策略，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。 原文链接：https://blog.csdn.net/qq_37080455/article/details/139710370

7.4 mysql 最左匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。

1、全字段全值匹配 索引的全部字段都在查找条件当中，并且都是使用 = 进行全值匹配的情况下，索引是命中生效的：

select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'

虽然 where 子句几个搜索条件顺序调换了，但不影响查询结果，这是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。

2、从左到右按顺序匹配

select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'

只要是按照联合索引创建的字段从左到右的顺序依次使用，不管使用其中多少个字段，都会命中索引。

3、缺失最左边的字段

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

这种缺失了最左边 a 字段的情况就是违背最左匹配原则的典型例子，结果就是没有用到索引（索引失效）。

因为缺失了最左边的字段，导致索引数据结构 B+ 树不知道第一步该查哪个节点，从而需要去全表扫描了。在建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询 b 和 c。

4、缺失中间的字段 假如去掉中间的字段，保留最左边和右边的字段（就是我们说的索引字段不连续）：

select * from table_name where a = '1' and c = '3'

结果就是只用到了 a 列的索引，而 b 列和 c 列都没有用到。

因为在这种情况下进行数据检索时，B+ 树可以用 a 来指定第一步的搜索方向，但由于下一个字段 b 的缺失，所以只能先把 a = 1 的数据主键 ID 都找出来，然后通过查到的主键 ID 回表查询相关行，再去匹配 c 值的数据了。当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了

5、匹配范围值 出现匹配范围值的情况可能比较复杂或难以理解，但我们只需要牢记最左匹配原则的规则：遇到范围查询 (>、<、between、like) 时就会停止匹配。

比如下面这种情况：

select * from table_name where  a = 1 and b > 3 and c = 'mm';

这种情况下，由于 a 是等值匹配，所以 B+ 树走完 a 索引之后 b 还是有序的，但走完 b 索引之后，由于 b 是范围匹配，所以此时 c 已经是无序的了，最终只使用了 (a, b) 两个索引（由于此时 c 就没法走索引，所以优化器只能根据 a, b 得到数据的主键 ID 回表查询，最终影响了执行效率）。

再比如下面的情况：

select * from table_name where  a > 1 and b > 1
select * from table_name where  a > 1 and a < 3 and b > 1;

当多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到 B+ 树索引，也就是只有 a 用到索引，在 a > 1 和 1 < a < 3 的范围内 b 是无序的，所以 b 不能用索引，找到 a 的记录后，只能根据条件 b > 1 继续逐条过滤。

6、like 语句匹配问题 当索引列是字符型，并且使用了 like 语句进行模糊查询时，如果通配符 % 不出现在开头，则可以用到索引，否则将会违背了最左匹配原则，而不会使用索引，走的是全表扫描：

select * from table_name where a like 'As%';   //走索引查询
select * from table_name where a like '%As';   //全表查询
select * from table_name where a like '%As%';  //全表查询

我们先了解一下字符型字段的比较规则：当列是字符型的话，它的比较规则是先比较字符串的第一个字符，第一个字符小的那个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个字符，依次类推。

所以，如果通配符 % 出现在开头，B+ 树则无法进行比较匹配，进而导致索引失效。

7.5 mysql 索引失效的场景

  1. 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

结论: MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。 如果查询条件中没有使用这些字段中第1个字段时，多列(或联合）索引不会被使用。

  1. 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序。

所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了。

当插入数据的主键顺序应该是在之前的页面中时，我们需要把那个 页面分裂 成两个页面，把这个页中的一些记录移动到新创建的这个页中。

页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。

所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 。

  1. 计算、函数、类型转换(自动或手动)导致索引失效

    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
​
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

  2. 类型转换导致索引失效

    # 未使用到索引，name=123发生类型转换，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
​
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
​

  3. 范围条件右边的索引失效

    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

    应用开发中范围查询，例如： 金额范围查询，日期查询往往都是范围查询。创建联合索引时考虑放在后面。

  4. 查询条件中包含不等于

    索引只能查知道的东西，!=和<>会导致索引失效

  5. is null可以使用索引，is not null无法使用索引 结论：最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串。

    拓展: 同理，在查询中使用not like 也无法使用索引，导致全表扫描。

  1. like以通配符%开头索引失效 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有“%"不在第一个位置，索引才会起作用。

    拓展：Alibaba《Java开发手册》 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

  1. OR 前后存在非索引的列，索引失效 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

    因为OR的含义就是两个只要满足一个即可，因此只有一个条伴列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

  2. 数据库和表的字符集统一使用utf8mb4

    统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

7.6 适合和不适合创建索引的情况

参考第二节Mysql的创建与设计原则

7.7 mysql 执行计划

Explain作用

当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。也就是说，MySQL解释了它将如何处理该语句，包括如何连接表以及什么顺序连接表等。

  • 表的加载顺序

  • sql 的查询类型

  • 可能用到哪些索引，哪些索引又被实际使用

  • 表与表之间的引用关系

  • 一个表中有多少行被优化器查询

Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。重点关注type

type`：查询使用了何种类型，它在 `SQL`优化中是一个非常重要的指标，以下性能从好到坏依次是：`system` > `const` > `eq_ref` > `ref` > `ref_or_null` > `index_merge` > `unique_subquery` > `index_subquery` > `range` > `index` > `ALL

const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。

range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

ALL：将遍历全表以找到匹配的行，性能最差。

7.8 mysql 慢查询优化

不能只谈，创建联合索引，尽量不连表查，减少函数查询等

最好结合实际项目来说

  1. 业务层面

    大数据上ES，比如高级权限可以看所有数据，查看用户想查看一辆车一整年的GPS轨迹，和产品沟通，看能不能降到一个月，分月查看

  2. 代码层面

    比如多张表联查情况，可以分别查出多部分数据，然后再进行拼装

  3. sql层面

    加上联合索引，覆盖索引，联表查询时用小表驱动大表，不使用select *等等

  4. 硬件层面

    通过分表和分库的方式，比如之前公车项目，需要做GPS定位，设备传回来的GPS数据，就需要分表进行入库，每辆车的ID来选择表，每20辆车为一张表

如果单纯是sql查询慢

一直慢的原因：索引没有设计好、SQL 语句没写好、MySQL 选错了索引 mysql慢查询优化 第一步：开启mysql慢查询日志，通过慢查询日志定位到执行较慢的SQL语句。 第二步：利用explain关键字可以模拟优化器执行SQL查询语句，来分析SQL查询语句。 第三步：通过查询的结果进行优化。

小表驱动大表

比如查询员工的部门信息，可以通过部门表驱动员工表，可以减少内存的消耗

将小表放在驱动大表的位置可以减少内存消耗。在进行关联查询时，‌MySQL会将驱动表的数据加载到内存中，然后逐行与被驱动表进行匹配。如果驱动表很大，需要占用较多的内存来存储数据。而将小表放在驱动表的位置，可以减少内存消耗，提高查询性能。

7.9 mysql 事务隔离级别

SQL中的四种隔离级别

  • READ UNCOMMITTED：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

  • READ COMMITTED：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。

  • REPEATABLE READ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

  • SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

7.10 Mysql锁

大致说一下库锁，表锁，行锁，

然后介绍行锁里乐观锁，悲观锁，共享锁，排它锁，

如果继续深入，可以说一下间隙锁和临建锁

7.11 mysql 日志

redo log，binlog, undo log，错误日志等等

7.12 MVCC多版本并发控制

简单描述一下MVCC，mvcc是mysql通过数据行的多个版本管理来实现数据库的并发控制。实现依赖数据行的隐藏字段、Undo Log、Read View。在READ COMMITTED和REPEATABLE READ隔离级别的事务，用于保证读到已经提交了的事务修改过的记录。先通过隐藏字段确定自己的事务ID，然后再获取ReadView，查询得到的数据，然后与 ReadView 中的事务版本号进行比较，如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照，重复比较，最终返回符合规则的数据。

当查询一条记录的时候，系统如何通过MVCC找到它：

  1. 首先获取事务自己的版本号，也就是事务 ID；

  2. 获取 ReadView；

  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

  5. 最后返回符合规则的数据。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

如表所示：

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：