MySQL高频问题：事务及慢SQL优化全解析

1.MySQL事务是什么，默认隔离级别？

事务是MySQL执行过程中的一个逻辑单位，由一系列的操作组成，这些操作要么完全执行，要么完全不执行，保证了数据库操作的一致性和完整性。

事务具有四个基本特性，也就是通常所说的ACID特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

什么是原子性？

原子性子性意味着事务中的所有操作要么全部完成，要么全部不完成，它是不可分割的单位。如果事务中的任何一个操作失败了，整个事务都会回滚到事务开始之前的状态，如同这些操作从未被执行过一样。

什么是一致性？

一致性确保事务从一个一致的状态转换到另一个一致的状态。

比如在银行转账事务中，无论发生什么，转账前后两个账户的总金额应保持不变。假如A账户（100块）给B账户（10块）转了10块钱，不管成功与否，A和B的总金额都是110块。

什么是隔离性？

隔离性意味着并发执行的事务是彼此隔离的，一个事务的执行不会被其他事务干扰。就是事务之间是井水不犯河水的。

隔离性主要是为了解决事务并发执行时可能出现的问题，如脏读、不可重复读、幻读等。

数据库系统通过事务隔离级别（如读未提交、读已提交、可重复读、串行化）来实现事务的隔离性。

什么是持久性？

持久性确保事务一旦提交，它对数据库所做的更改就是永久性的，即使发生系统崩溃，数据库也能恢复到最近一次提交的状态。通常，持久性是通过数据库的恢复和日志机制来实现的，确保提交的事务更改不会丢失。

MySQL 默认隔离级别：MySQL 中 InnoDB 存储引擎的默认隔离级别是可重复读（Repeatable Read）。

        InnoDB在可重复读的级别就已经解决了幻读的问题，这也是InnoDB使用可重复读作为默认隔离级别的原因。  InnoDB 通过next-key lock 锁(行锁和间隙锁的组合)来锁住记录之间的"间隙"和记录本身， 防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

可重复读（Repeatable Read）

定义：在同一个事务内，多次读取同一数据时，读到的数据是一致的，不受其他事务提交的影响。

示例：假设事务 T1 开始，读取账户 A 的余额为 1000 元。此时事务 T2 将账户 A 的余额修改为 1200 元并提交。但在事务 T1 中再次读取账户 A 的余额时，仍然读到的是 1000 元。这是因为在可重复读隔离级别下，事务 T1 在开始时生成了一个 Read View（读视图），后续的读取操作都基于这个 Read View，所以看不到其他事务在该事务开始后提交的修改。直到事务 T1 结束，期间无论执行多少次对账户 A 余额的读取操作，结果都是 1000 元。

解决的问题：可重复读解决了脏读和不可重复读的问题。脏读是指一个事务读取到另一个未提交事务修改的数据，可重复读通过基于 Read View 的版本控制避免了这种情况。不可重复读是指在同一个事务内，多次读取同一数据时，由于其他事务的提交导致读取结果不一致，可重复读通过固定事务内的读视图，确保了同一事务内读取数据的一致性。不过，在可重复读隔离级别下，InnoDB 通过 Next - Key 锁机制基本解决了幻读问题（ InnoDB 通过next-key lock 锁(行锁和间隙锁的组合)来锁住记录之间的"间隙"和记录本身， 防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。），但标准的可重复读隔离级别理论上可能存在幻读情况。幻读是指在同一个事务内，按照相同的查询条件多次查询，由于其他事务的插入或删除操作，导致每次查询返回的记录数不同。

2.如何找到慢SQL？

MySQL慢查询日志是用来记录MySQL在执行命令中，响应时间超过预设阈值的SQL语句。因此，通过分析慢查询日志我们就可以找出执行速度比较慢的SQL语句。

出于性能层面的考虑，慢查询日志功能默认是关闭的，你可以通过以下命令开启：

# 开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';
# 慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
# 无论是否超时，未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;
# 慢查询仅记录扫描行数大于此参数的 SQL
SET SESSION min_examined_row_limit = 100;

设置成功之后，使用 showvariableslike'slow%'; 命令进行查看:

这里对日志中的一些信息进行说明：

Time ：被日志记录的代码在服务器上的运行时间。

User@Host：谁执行的这段代码。

Query_time：这段代码运行时长。

Lock_time：执行这段代码时，锁定了多久。

Rows_sent：慢查询返回的记录。

Rows_examined：慢查询扫描过的行数。

实际项目中，慢查询日志通常会比较复杂，我们需要借助一些工具对其进行分析。像MySQL内置的 mysqldumpslow 工具就可以把相同的SQL归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。

3.如何分析SQL性能?

可以使用 EXPLAIN 命令来分析SQL的 执行计划 。执行计划是指一条SQL语句在经过MySQL查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE,和 UPDATE语句，我们一般分析 SELECT 查询较多。

我们这里简单来演示一下 EXPLAIN 的使用。

EXPLAIN 的输出格式如下：

4.项目中是怎么使用索引的？联合索引了解吗？

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方:我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

要选择选择合适的字段创建索引：

• 不为NULL的字段：索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用0,1,true,false这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段：被作为WHERE条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引

我们应该尽可能的考虑建立联合索引而不是单列索引。因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

详细的上下文解释可以参考：

InnoDB存储引擎底层拆解：从页、事务到锁，如何撑起MySQL数据库高效运转