MySQL面试（三）

最新推荐文章于 2025-11-24 15:00:59 发布

原创最新推荐文章于 2025-11-24 15:00:59 发布 · 447 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #kafka #java #c++ #oracle #程序员

31.介绍⼀下MySQL中事务的特性？

在关系型数据库管理系统中，⼀个逻辑⼯作单元要成为事务，必须满⾜这 4个特性，即所谓的 ACID：原⼦性（Atomicity）、⼀致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1）原⼦性

原⼦性：事务作为⼀个整体被执⾏，包含在其中的对数据库的操作要么全部被执⾏，要么都不执⾏。

InnoDB存储引擎提供了两种事务⽇志：redo log(重做⽇志)和undo log(回滚⽇志)。其中redo log⽤于保证事务持久性；undo log则是事务原⼦性和隔离性实现的基础。

每写⼀个事务,都会修改Buffer Pool,从⽽产⽣相应的Redo/Undo⽇志:

1.如果要回滚事务，那么就基于undo log来回滚就可以了，把之前对缓存也做的修改都给回滚了就可以了。

2.如果事务提交之后，redo log刷⼊磁盘，结果MySQL宕机了，是可以根据redo log恢复事务修改过的缓存数据的。

实现原⼦性的关键，是当事务回滚时能够撤销所有已经成功执⾏的sql语句。

InnoDB 实现回滚，靠的是undo log ：当事务对数据库进⾏修改时，InnoDB 会⽣成对应的undo log ；如果事务执⾏失败或调⽤了rollback ，导致事务需要回滚，便可以利⽤undo log中的信息将数据回滚到修改之前的样⼦。

2）⼀致性

⼀致性：事务应确保数据库的状态从⼀个⼀致状态转变为另⼀个⼀致状态。⼀致状态的含义是数据库中的数据应满⾜完整性约束。

约束⼀致性：创建表结构时所指定的外键、唯⼀索引等约束。

数据⼀致性：是⼀个综合性的规定，因为它是由原⼦性、持久性、隔离性共同保证的结果，⽽不是单单依赖于某⼀种技术。

3）隔离性

隔离性：指的是⼀个事务的执⾏不能被其他事务⼲扰，即⼀个事务内部的操作及使⽤的数据对其他的并发事务是隔离的。

不考虑隔离性会引发的问题:

1.脏读 : ⼀个事务读取到了另⼀个事务修改但未提交的数据。

2.不可重复读: ⼀个事务中多次读取⾏记录的结果不⼀致，后⾯读取的跟前⾯读取的结果不⼀致。

3.幻读 : ⼀个事务中多次按相同条件查询，结果不⼀致。后续查询的结果和⾯前查询结果不同，多了或少了⼏⾏记录。

数据库事务的隔离级别有4个，由低到⾼依次为Read uncommitted 、Read committed、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读、不可重复读、幻读这⼏类问题。

4）持久性

持久性：指的是⼀个事务⼀旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

1.MySQL 事务的持久性保证依赖的⽇志⽂件: redo logredo log 也包括两部分：⼀是内存中的⽇志缓冲(redo log buffer)，该部分⽇志是易失性的；⼆是磁盘上的重做⽇志⽂件(redo log file)，该部分⽇志是持久的。redo log是物理⽇志，记录的是数据库中物理⻚的情况。

2.当数据发⽣修改时，InnoDB不仅会修改Buffer Pool中的数据，也会在redo log buffer记录这次操作；当事务提交时，会对redo log buffer进⾏刷盘，记录到redo log file中。如果MySQL宕机，重启时可以读取redo log file中的数据，对数据库进⾏恢复。这样就不需要每次提交事务都实时进⾏刷脏了。

5）ACID总结

1.事务的持久化是为了应对系统崩溃造成的数据丢失.

2.只有保证了事务的⼀致性，才能保证执⾏结果的正确性

3.在⾮并发状态下，事务间天然保证隔离性，因此只需要保证事务的原⼦性即可保证⼀致性.

4.在并发状态下，需要严格保证事务的原⼦性、隔离性。

32.MySQL 的可重复读怎么实现的？

可重复读（repeatable read）定义：⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的。

MVCC

1.MVCC，多版本并发控制, ⽤于实现读已提交和可重复读隔离级别。

2.MVCC的核⼼就是 Undo log多版本链 + Read view，“MV”就是通过Undo log来保存数据的历史版本，实现多版本的管理，“CC”是通过Read-view来实现管理，通过 Read-view原则来决定数据是否显示。同时针对不同的隔离级别， Read view的⽣成策略不同，也就实现了不同的隔离级别。

Undo log 多版本链

每条数据都有两个隐藏字段:

trx_id: 事务id,记录最近⼀次更新这条数据的事务id.

roll_pointer: 回滚指针,指向之前⽣成的undo log

每⼀条数据都有多个版本,版本之间通过undo log链条进⾏连接通过这样的设计⽅式,可以保证每个事务提交的时候,⼀旦需要回滚操作,可以保证同⼀个事务只能读取到⽐当前版本更早提交的值,不能看到更晚提交的值。

ReadView

Read View是 InnoDB 在实现 MVCC 时⽤到的⼀致性读视图，即consistent read view，⽤于⽀持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现.

Read View简单理解就是对数据在某个时刻的状态拍成照⽚记录下来。那么之后获取某时刻的数据时就还是原来的照⽚上的数据，是不会变的.

Read View中⽐较重要的字段有4个:

1.m_ids : ⽤来表示MySQL中哪些事务正在执⾏,但是没有提交.

2.min_trx_id : 就是m_ids⾥最⼩的值.

3.max_trx_id : 下⼀个要⽣成的事务id值,也就是最⼤事务id

4.creator_trx_id : 就是你这个事务的id

当⼀个事务第⼀次执⾏查询sql时，会⽣成⼀致性视图 read-view（快照），查询时从 undo log 中最新的⼀条记录开始跟 read-view 做对⽐，如果不符合⽐较规则，就根据回滚指针回滚到上⼀条记录继续⽐较，直到得到符合⽐较条件的查询结果。

Read View判断记录某个版本是否可⻅的规则如下

1.如果当前记录的事务id落在绿⾊部分（trx_id < min_id），表示这个版本是已提交的事务⽣成的，可读。

2.如果当前记录的事务id落在红⾊部分（trx_id > max_id），表示这个版本是由将来启动的事务⽣成的，不可读。

3. 如果当前记录的事务id落在⻩⾊部分（min_id <= trx_id <= max_id），则分为两种情况：

4. 若当前记录的事务id在未提交事务的数组中，则此条记录不可读；

5. 若当前记录的事务id不在未提交事务的数组中，则此条记录可读。

RC 和 RR 隔离级别都是由 MVCC 实现，区别在于：

RC 隔离级别时，read-view 是每次执⾏ select 语句时都⽣成⼀个；

RR 隔离级别时，read-view 是在第⼀次执⾏ select 语句时⽣成⼀个，同⼀事务中后⾯的所有 select 语句都复⽤这个 read-view 。

33.Repeatable Read 解决了幻读问题吗？

可重复读（repeatable read）定义：⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的。

不过理论上会出现幻读，简单的说幻读指的的当⽤户读取某⼀范围的数据⾏时，另⼀个事务⼜在该范围插⼊了新⾏，当⽤户在读取该范围的数据时会发现有新的幻影⾏。

注意在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插⼊的数据的。因此，幻读在“当前读”下才会出现（查询语句添加forupdate，表示当前读）；

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读（Current Read）。

快照读

快照读是指读取数据时不是读取最新版本的数据，⽽是基于历史版本读取的⼀个快照信息（mysql读取undo log历史版本) ，快照读可以使普通的SELECT 读取数据时不⽤对表数据进⾏加锁，从⽽解决了因为对数据库表的加锁⽽导致的两个如下问题

1. 解决了因加锁导致的修改数据时⽆法对数据读取问题.

2. 解决了因加锁导致读取数据时⽆法对数据进⾏修改的问题.当前读

当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据⽽且要保证事务的隔离性，所以当前读是需要对数据进⾏加锁的（插⼊/更新/删除操作，属于当前读，需要加锁 , select forupdate 为当前读）

表结构

假设 select * from where value=1 for update，只在这⼀⾏加锁（注意这只是假设），其它⾏不加锁，那么就会出现如下场景：

Session A的三次查询Q1-Q3都是select * from where value=1 forupdate，查询的value=1的所有row。

T1：Q1只返回⼀⾏(1,1,1)；

T2：session B更新id=0的value为1，此时表t中value=1的数据有两⾏

T3：Q2返回两⾏(0,0,1),(1,1,1)

T4：session C插⼊⼀⾏(6,6,1)，此时表t中value=1的数据有三⾏

T5：Q3返回三⾏(0,0,1),(1,1,1),(6,6,1)

T6：session A事物commit。

其中Q3读到value=1这⼀样的现象，就称之为幻读，幻读指的是⼀个事务在前后两次查询同⼀个范围的时候，后⼀次查询看到了前⼀次查询没有看到的行。

先对“幻读”做出如下解释：

要讨论「可重复读」隔离级别的幻读现象，是要建⽴在「当前读」的情况下，⽽不是快照读,因为在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插⼊的数据的。

Next-key Lock 锁

产⽣幻读的原因是，⾏锁只能锁住⾏，但是新插⼊记录这个动作，要更新的是记录之间的“间隙”。因此，Innodb 引擎为了解决「可重复读」隔离级别使⽤「当前读」⽽造成的幻读问题，就引出了 next-key 锁，就是记录锁和间隙锁的组合。

RecordLock锁：锁定单个⾏记录的锁。（记录锁，RC、RR隔离级别都⽀持）

GapLock锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（范围锁，RR隔离级别⽀持）

Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别⽀持）

总结

1.RR隔离级别下间隙锁才有效，RC隔离级别下没有间隙锁；

2.RR隔离级别下为了解决“幻读”问题：“快照读”依靠MVCC控制，“当前读”通过间隙锁解决；

3.间隙锁和⾏锁合称next-key lock，每个next-key lock是前开后闭区间；

4.间隙锁的引⼊，可能会导致同样语句锁住更⼤的范围，影响并发度。

34.请说⼀下数据库锁的种类？

MySQL数据库由于其⾃身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎⽀持不同的锁机制。

MyISAM和MEMORY存储引擎采⽤的表级锁，InnoDB存储引擎既⽀持⾏级锁，也⽀持表级锁，默认情况下采⽤⾏级锁。

BDB采⽤的是⻚⾯锁，也⽀持表级锁

按照数据操作的类型分

读锁（共享锁）：针对同⼀份数据，多个读操作可以同时进⾏⽽不会互相影响。

写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分

表级锁：开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发⽣锁冲突的概率最⾼，并发度最低。

⾏级锁：开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼。

⻚⾯锁：开销和加锁时间界于表锁和⾏锁之间；会出现死锁；锁定粒度界于表锁和⾏锁之间，并发度⼀般

按照操作性能可分为乐观锁和悲观锁

乐观锁：⼀般的实现⽅式是对记录数据版本进⾏⽐对，在数据更新提交的时候才会进⾏冲突检测，如果发现冲突了，则提示错误信息。

悲观锁：在对⼀条数据修改的时候，为了避免同时被其他⼈修改，在修改数据之前先锁定，再修改的控制⽅式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

35.请说一下共享锁和排他锁？

行级锁分为共享锁和排他锁两种。

⾏锁的是mysql锁中粒度最⼩的⼀种锁，因为锁的粒度很⼩，所以发⽣资源争抢的概率也最⼩，并发性能最⼤，但是也会造成死锁，每次加锁和释放锁的开销也会变⼤。

使⽤MySQL⾏级锁的两个前提

使⽤ innoDB 引擎开启事务 (隔离级别为 Repeatable Read )

InnoDB⾏锁的类型

共享锁（S）：当事务对数据加上共享锁后, 其他⽤户可以并发读取数据，但任何事务都不能据进⾏修改（获取数据上的排他锁），直到已释放所有共享锁。

排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，⼜能修改数据。

加锁的⽅式

InnoDB引擎默认更新语句，update,delete,insert 都会⾃动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果要加可以使⽤下⾯的⽅式:

加共享锁（S）：select * from table_name where ... lock in share mode;

加排他锁（x）：select * from table_name where ... for update;

锁兼容

共享锁只能兼容共享锁, 不兼容排它锁

排它锁互斥共享锁和其它排它锁

36.InnoDB 的行锁是怎么实现的？

InnoDB⾏锁是通过对索引数据⻚上的记录加锁实现的，主要实现算法有 3种：Record Lock、Gap Lock 和 Next-key Lock。

RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都⽀持）

GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别⽀持）

Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别⽀持）

注意： InnoDB这种⾏锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使⽤⾏级锁，否则，InnoDB将使⽤表锁

在RR隔离级别，InnoDB对于记录加锁⾏为都是先采⽤Next-Key Lock，但是当SQL操作含有唯⼀索引时，Innodb会对Next-Key Lock进⾏优化，降级为RecordLock，仅锁住索引本身⽽⾮范围。

各种操作加锁的特点

1）select ... from 语句：InnoDB引擎采⽤MVCC机制实现⾮阻塞读，所以对于普通的select语句，InnoDB不加锁

2）select ... from lock in share mode语句：追加了共享锁，InnoDB会使⽤Next-Key Lock锁进⾏处理，如果扫描发现唯⼀索引，可以降级为RecordLock锁。

3）select ... from for update语句：追加了排他锁，InnoDB会使⽤NextKey Lock锁进⾏处理，如果扫描发现唯⼀索引，可以降级为RecordLock锁。

4）update ... where 语句：InnoDB会使⽤Next-Key Lock锁进⾏处理，如果扫描发现唯⼀索引，可以降级为RecordLock锁。

5）delete ... where 语句：InnoDB会使⽤Next-Key Lock锁进⾏处理，如果扫描发现唯⼀索引，可以降级为RecordLock锁。

6）insert语句：InnoDB会在将要插⼊的那⼀⾏设置⼀个排他的RecordLock锁。

下⾯以“update t1 set name=‘lisi’ where id=10”操作为例，举例⼦分析下 InnoDB 对不同索引的加锁⾏为，以RR隔离级别为例。

1. 主键加锁

加锁⾏为：仅在id=10的主键索引记录上加X锁。

2. 唯⼀键加锁

加锁⾏为：现在唯⼀索引id上加X锁，然后在id=10的主键索引记录上加X锁。

3. ⾮唯⼀键加锁

加锁⾏为：对满⾜id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范围分别加Gap Lock。

4. ⽆索引加锁

加锁⾏为：表⾥所有⾏和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

37.并发事务会产生哪些问题

事务并发处理可能会带来⼀些问题，如下：

更新丢失

当两个或多个事务更新同⼀⾏记录，会产⽣更新丢失现象。可以分为回滚覆盖和提交覆盖。

回滚覆盖：⼀个事务回滚操作，把其他事务已提交的数据给覆盖了。

提交覆盖：⼀个事务提交操作，把其他事务已提交的数据给覆盖了。

脏读

⼀个事务读取到了另⼀个事务修改但未提交的数据。

不可重复读

⼀个事务中多次读取同⼀⾏记录不⼀致，后⾯读取的跟前⾯读取的不⼀致。

幻读

⼀个事务中多次按相同条件查询，结果不⼀致。后续查询的结果和⾯前查询结果不同，多了或少了⼏⾏记录。

“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库⼀致性问题，为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供⽤户选择。

读未提交

Read Uncommitted 读未提交：解决了回滚覆盖类型的更新丢失，但可能发⽣脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。

已提交读

Read Committed 读已提交：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发⽣不可重复读现象，也就是可能在⼀个事务中两次查询结果不⼀致。

可重复度

Repeatable Read 可重复读：解决了不可重复读，它确保同⼀事务的多个实例在并发读取数据时，会看到同样的数据⾏。不过理论上会出现幻读，简单的说幻读指的的当⽤户读取某⼀范围的数据⾏时，另⼀个事务⼜在该范围插⼊了新⾏，当⽤户在读取该范围的数据时会发现有新的幻影⾏。

可串⾏化

所有的增删改查串⾏执⾏。它通过强制事务排序，解决相互冲突，从⽽解决幻度的问题。这个级别可能导致⼤量的超时现象的和锁竞争，效率低下。

数据库的事务隔离级别越⾼，并发问题就越⼩，但是并发处理能⼒越差（代价）读未提交隔离级别最低，并发问题多，但是并发处理能⼒好。以后使⽤时，可以根据系统特点来选择⼀个合适的隔离级别，⽐如对不可重复读和幻读并不敏感，更多关⼼数据库并发处理能⼒，此时可以使⽤Read Commited隔离级别。

事务隔离级别，针对Innodb引擎，⽀持事务的功能。像MyISAM引擎没有关系。

事务隔离级别和锁的关系

1）事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决⽅案，本质上是对锁和MVCC使⽤的封装，隐藏了底层细节。

2）锁是数据库实现并发控制的基础，事务隔离性是采⽤锁来实现，对相应操作加不同的锁，就可以防⽌其他事务同时对数据进⾏读写操作。

3）对⽤户来讲，⾸先选择使⽤隔离级别，当选⽤的隔离级别不能解决并发问题或需求时，才有必要在开发中⼿动的设置锁。

MySQL默认隔离级别：可重复读

Oracle、SQLServer默认隔离级别：读已提交

⼀般使⽤时，建议采⽤默认隔离级别，然后存在的⼀些并发问题，可以通过悲观锁、乐观锁等实现处理。

38.说⼀下MVCC内部细节

MVCC概念

MVCC（Multi Version Concurrency Control）被称为多版本并发控制，是指在数据库中为了实现⾼并发的数据访问，对数据进⾏多版本处理，并通过事务的可⻅性来保证事务能看到⾃⼰应该看到的数据版本。

MVCC最⼤的好处是读不加锁，读写不冲突。在读多写少的系统应⽤中，读写不冲突是⾮常重要的，极⼤的提升系统的并发性能，这也是为什么现阶段⼏乎所有的关系型数据库都⽀持 MVCC 的原因，不过⽬前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下⼯作。

回答这个⾯试题时，主要介绍以下的⼏个关键内容：

1）⾏记录的三个隐藏字段

DB_ROW_ID : 如果没有为表显式的定义主键，并且表中也没有定义唯⼀索引，那么InnoDB会为表添加⼀个row_id的隐藏列作为主键。

DB_TRX_ID : 事务中对某条记录做增删改时,就会将这个事务的事务ID写⼊到trx_id中.

DB_ROLL_PTR : 回滚指针,指向undo log的指针

2）Undo log 多版本链

举例：事务 T-100 和 T-120 对表中 id = 1 的数据⾏做 update 操作，事务T-130 进⾏ select 操作，即使 T-100 已经提交修改，三次 select 语句的结果都是“lisi”。

每⼀条数据都有多个版本,版本之间通过undo log链条进⾏连接

3）ReadView

Read View是 InnoDB 在实现 MVCC 时⽤到的⼀致性读视图，即

consistent read view，⽤于⽀持 RC（Read Committed，读提交）和 RR

（Repeatable Read，可重复读）隔离级别的实现.

Read View简单理解就是对数据在每个时刻的状态拍成照⽚记录下来。那么

之后获取某时刻的数据时就还是原来的照⽚上的数据，是不会变的.

Read View中⽐较重要的字段有4个:

m_ids : ⽤来表示MySQL中哪些事务正在执⾏,但是没有提交.

min_trx_id : 就是m_ids⾥最⼩的值.

max_trx_id : 下⼀个要⽣成的事务id值,也就是最⼤事务id

creator_trx_id : 就是你这个事务的id

通过Read View判断记录的某个版本是否可⻅的⽅式总结:

trx_id = creator_trx_id

如果被访问版本的trx_id,与readview中的creator_trx_id值相同,表明当前事务在访问⾃⼰修改过的记录,该版本可以被当前事务访问.

trx_id < min_trx_id

如果被访问版本的trx_id,⼩于readview中的min_trx_id值,表明⽣成该版本的事务在当前事务⽣成readview前已经提交,该版本可以被当前事务访问.

trx_id >= max_trx_id

如果被访问版本的trx_id,⼤于或等于readview中的max_trx_id值,表明⽣成该版本的事务在当前事务⽣成readview后才开启,该版本不可以被当前事务访问.

trx_id > min_trx_id && trx_id < max_trx_id

如果被访问版本的trx_id,值在readview的min_trx_id和max_trx_id之间，就需要判断trx_id属性值是不是在m_ids列表中？在：说明创建readview时⽣成该版本的事务是活跃的,该版本不可以被访问

不在：说明创建readview时⽣成该版本的事务已经被提交,该版本可以被访问

何时⽣成ReadView快照

在 读已提交（Read Committed，简称RC）隔离级别下，每⼀次读取数据前都⽣成⼀个ReadVIew。

在 可重复读 （Repeatable Read，简称RR）隔离级别下，在⼀个事务中，只在 第⼀次读取数据前⽣成⼀个ReadVIew。

4）快照读（Snapshot Read）与当前读（Current Read）

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读（Current Read）。

快照读

1. 解决了因加锁导致的修改数据时⽆法对数据读取问题.

2. 解决了因加锁导致读取数据时⽆法对数据进⾏修改的问题.

当前读

当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据⽽且要保证事务的隔离性，所以当前读是需要对数据进⾏加锁的（Update delete insert select ....lock in share mode ,select for update 为当前读）

总结⼀下并发环境下，写-写操作有加锁解决⽅案，但为了提⾼性能，InnoDB存储引擎提供MVCC，⽬的是为了解决读-写，写-读操作下不加锁仍能安全进⾏。

MVCC的过程，本质就是访问版本链，并判断哪个版本可⻅的过程。该判断算法是通过版本上的trx_id与快照ReadView的若⼲个信息进⾏对⽐。

快照⽣成的时机因隔离级别不同，读已提交隔离级别下，每⼀次读取前都会⽣成⼀个快照ReadView；⽽可重复读则仅在⼀个事务中，第⼀次读取前⽣成⼀个快照。

39.说⼀下MySQL死锁的原因和处理方法

1) 表的死锁

产⽣原因:

⽤户A访问表A（锁住了表A），然后⼜访问表B；另⼀个⽤户B访问表B（锁住了表B），然后企图访问表A；这时⽤户A由于⽤户B已经锁住表B，它必须等待⽤户B释放表B才能继续，同样⽤户B要等⽤户A释放表A才能继续，这就死锁就产⽣了。

⽤户A--》A表（表锁）--》B表（表锁）

⽤户B--》B表（表锁）--》A表（表锁）

解决⽅案：

这种死锁⽐较常⻅，是由于程序的BUG产⽣的，除了调整的程序的逻辑没有其它的办法。

仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进⾏处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

2) ⾏级锁死锁

产⽣原因1：

如果在事务中执⾏了⼀条没有索引条件的查询，引发全表扫描，把⾏级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执⾏后，就很容易产⽣死锁和阻塞，最终应⽤系统会越来越慢，发⽣阻塞或死锁。

解决⽅案1：

SQL语句中不要使⽤太复杂的关联多表的查询；使⽤explain“执⾏计划"对SQL语句进⾏分析，对于有全表扫描和全表锁定的SQL语句，建⽴相应的索引进⾏优化。

产⽣原因2：

两个事务分别想拿到对⽅持有的锁，互相等待，于是产⽣死锁

产⽣原因3：每个事务只有⼀个SQL,但是有些情况还是会发⽣死锁.

1. 事务1,从name索引出发 , 读到的[hdc, 1], [hdc, 6]均满⾜条件, 不仅会加name索引上的记录X锁, ⽽且会加聚簇索引上的记录X锁, 加锁顺序为先[1,hdc,100], 后[6,hdc,10]

2. 事务2，从pubtime索引出发，[10,6],[100,1]均满⾜过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。

3. 但是加锁时发现跟事务1的加锁顺序正好相反，两个Session恰好都持有了第⼀把锁，请求加第⼆把锁，死锁就发⽣了。

解决⽅案: 如上⾯的原因2和原因3, 对索引加锁顺序的不⼀致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量⽅式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以⼤⼤降低出现死锁的可能；

40.介绍⼀下MySQL的体系架构？

MySQL Server架构⾃顶向下⼤致可以分⽹络连接层、服务层、存储引擎层和系统⽂件层。

⼀、⽹络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建⽴的⽀持。⽬前⼏乎⽀持所有主流的服务端编程技术，例如常⻅的 Java、C、Python、.NET等，它们通过各⾃API技术与MySQL建⽴连接。

⼆、服务层（MySQL Server）

服务层是MySQL Server的核⼼，主要包含系统管理和控制⼯具、连接池、SQL接⼝、解析器、查询优化器和缓存六个部分。

连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，⼀个线程负责管理⼀个连接。

系统管理和控制⼯具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等

SQL接⼝（SQL Interface）：⽤于接受客户端发送的各种SQL命令，并且返回⽤户需要查询的结果。⽐如DML、DDL、存储过程、视图、触发器等。

解析器（Parser）：负责将请求的SQL解析⽣成⼀个"解析树"。然后根据⼀些MySQL规则进⼀步检查解析树是否合法。

查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执⾏计划，然后与存储引擎交互。

select uid,name from user where gender=1;

选取--》投影--》联接策略

1）select先根据where语句进⾏选取，并不是查询出全部数据再过滤

2）select查询根据uid和name进⾏属性投影，并不是取出所有字段

3）将前⾯选取和投影联接起来最终⽣成查询结果

缓存（Cache&Buffer）：缓存机制是由⼀系列⼩缓存组成的。⽐如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

三、存储引擎层（Pluggable Storage Engines）存储引擎负责MySQL中数据的存储与提取，与底层系统文件进⾏交互。

MySQL存储引擎是插件式的，服务器中的查询执⾏引擎通过接⼝与存储引擎进⾏通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常⻅的是MyISAM和InnoDB。

四、系统文件层（File System）

该层负责将数据库的数据和⽇志存储在文件系统之上，并完成与存储引擎的交互，是⽂件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

⽇志⽂件

错误⽇志（Error log）

默认开启，show variables like '%log_error%'

通⽤查询⽇志（General query log）

记录⼀般查询语句，show variables like '%general%';

⼆进制⽇志（binary log）

记录了对MySQL数据库执⾏的更改操作，并且记录了语句的发⽣时间、执⾏时⻓；但是它不记录select、show等不修改数据库的SQL。主要⽤于数据库恢复和主从复制。

show variables like '%log_bin%'; //是否开启

show variables like '%binlog%'; //参数查看

show binary logs;//查看⽇志⽂件

慢查询⽇志（Slow query log）

记录所有执⾏时间超时的查询SQL，默认是10秒。

show variables like '%slow_query%'; //是否开启

show variables like '%long_query_time%'; //时⻓

配置文件

⽤于存放MySQL所有的配置信息⽂件，⽐如my.cnf、my.ini等。

数据⽂件

db.opt ⽂件：记录这个库的默认使⽤的字符集和校验规则。

frm ⽂件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每⼀张表都会有⼀个frm ⽂件。

MYD ⽂件：MyISAM 存储引擎专⽤，存放 MyISAM 表的数据（data)，每⼀张表都会有⼀个 .MYD ⽂件。

MYI ⽂件：MyISAM 存储引擎专⽤，存放 MyISAM 表的索引相关信息，每⼀张 MyISAM 表对应⼀个 .MYI ⽂件。

ibd⽂件和 IBDATA ⽂件：存放 InnoDB 的数据⽂件（包括索引）。InnoDB 存储引擎有两种表空间⽅式：独享表空间和共享表空间。独享表空间使⽤ .ibd ⽂件来存放数据，且每⼀张 InnoDB 表对应⼀个 .ibd ⽂件。共享表空间使⽤ .ibdata ⽂件，所有表共同使⽤⼀个（或多个，⾃⾏配置）.ibdata ⽂件。

ibdata1 ⽂件：系统表空间数据⽂件，存储表元数据、Undo⽇志等。

ib_logfile0、ib_logfile1 ⽂件：Redo log ⽇志⽂件。

pid ⽂件

pid ⽂件是 mysqld 应⽤程序在 Unix/Linux 环境下的⼀个进程⽂件，和许多其他 Unix/Linux 服务端程序⼀样，它存放着⾃⼰的进程 id。

socket ⽂件

socket ⽂件也是在 Unix/Linux 环境下才有的，⽤户在 Unix/Linux环境下客户端连接可以不通过 TCP/IP ⽹络⽽直接使⽤ Unix Socket 来连接 MySQL。

41.undo log、redo log、 binlog的作⽤是什么？

undo log 基本概念

undo log是⼀种⽤于撤销回退的⽇志，在数据库事务开始之前，MySQL会先记录更新前的数据到 undo log⽇志⽂件⾥⾯，当事务回滚时或者数据库崩溃时，可以利⽤ undo log来进⾏回退。

Undo Log产⽣和销毁：Undo Log在事务开始前产⽣；事务在提交时，并不会⽴刻删除undo log，innodb会将该事务对应的undo log放⼊到删除列表中，后⾯会通过后台线程purge thread进⾏回收处理。

注意: undo log也会产⽣redo log，因为undo log也要实现持久性保护。undo log的作⽤

1. 提供回滚操作【undo log实现事务的原⼦性】

在数据修改的时候，不仅记录了redo log，还记录了相对应的undolog，如果因为某些原因导致事务执⾏失败了，可以借助undo log进⾏回滚。

undo log 和 redo log 记录物理⽇志不⼀样，它是逻辑⽇志。可以认为当delete⼀条记录时，undo log中会记录⼀条对应的insert记录，反之亦然，当update⼀条记录时，它记录⼀条对应相反的update记录。

2. 提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】

MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，⽤undoLog来实现多版本并发控制(MVCC)。当读取的某⼀⾏被其他事务锁定时，它可以从undo log中分析出该⾏记录以前的数据版本是怎样的，从⽽让⽤户能够读取到当前事务操作之前的数据【快照读】。

redo log 基本概念

InnoDB引擎对数据的更新，是先将更新记录写⼊redo log⽇志，然后会在系统空闲的时候或者是按照设定的更新策略再将⽇志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以⼤⼤减少IO操作的频率，提升数据刷新的效率。

redo log：被称作重做⽇志, 包括两部分：⼀个是内存中的⽇志缓冲：

redo log buffer，另⼀个是磁盘上的⽇志⽂件： redo log file 。

redo log的作⽤

mysql 每执⾏⼀条 DML 语句，先将记录写⼊ redo log buffer 。后续某个时间点再⼀次性将多个操作记录写到 redo log file 。当故障发⽣致使内存数据丢失后，InnoDB会在重启时，经过重放 redo，将Page恢复到崩溃之前的状态通过Redo log可以实现事务的持久性。

bin log基本概念

binlog是⼀个⼆进制格式的⽂件，⽤于记录⽤户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog⾥，但是不会记录SELECT和SHOW这类操作。

binlog在MySQL的Server层实现(引擎共⽤)

binlog为逻辑⽇志,记录的是⼀条SQL语句的原始逻辑

binlog不限制⼤⼩,追加写⼊,不会覆盖以前的⽇志.

默认情况下，binlog⽇志是⼆进制格式的，不能使⽤查看⽂本⼯具的命令（⽐如，cat，vi等）查看，⽽使⽤mysqlbinlog解析查看。

bin log的作⽤

1. 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据⼀致性。

2. 数据恢复：通过mysqlbinlog⼯具来恢复数据。

42.redo log与undo log的持久化策略？

redo log持久化

缓冲区数据⼀般情况下是⽆法直接写⼊磁盘的，中间必须经过操作系统缓冲区( OS Buffer )。因此， redo log buffer 写⼊ redo logfile 实际上是先写⼊ OS Buffer，然后再通过系统调⽤ fsync() 将其刷到 redo log file.

Redo Buffer 持久化到 redo log 的策略，可通过

Innodb_flush_log_at_trx_commit 设置

⼀般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据

undo log持久化

MySQL中的Undo Log严格的讲不是Log，⽽是数据，因此他的管理和落盘都跟数据是⼀样的：

Undo的磁盘结构并不是顺序的，⽽是像数据⼀样按Page管理

Undo写⼊时，也像数据⼀样产⽣对应的Redo Log (因为undo也是对⻚⾯的修改，记录undo这个操作本身也会有对应的redo)。

Undo的Page也像数据⼀样缓存在Buffer Pool中，跟数据Page⼀起做LRU换⼊换出，以及刷脏。Undo Page的刷脏也像数据⼀样要等到对应的Redo Log 落盘之后

当事务提交的时候，innodb不会⽴即删除undo log，因为后续还可能会⽤到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交⾏版本，只要该事务不结束，该⾏版本就不能删除，即undolog不能删除。

但是在事务提交的时候，会将该事务对应的undo log放⼊到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的⻚是否可以重⽤，如果可以重⽤，则会分配给后⾯来的事务，避免为每个独⽴的事务分配独⽴的undo log⻚⽽浪费存储空间和性能。

43.bin log与undo log的区别？

1）redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使⽤。

2）redo log是物理⽇志，记录的是“在XXX数据⻚上做了XXX修改”；binlog是逻辑⽇志，记录的是原始逻辑，其记录是对应的SQL语句。

物理⽇志: 记录的是每⼀个page⻚中具体存储的值是多少，在这个数据⻚上做了什么修改. ⽐如: 某个事物将系统表空间中的第100个⻚⾯中偏移量为1000处的那个字节的值1改为2.

逻辑⽇志: 记录的是每⼀个page⻚⾯中具体数据是怎么变动的，它会记录⼀个变动的过程或SQL语句的逻辑, ⽐如: 把⼀个page⻚中的⼀个数据从1改为2，再从2改为3,逻辑⽇志就会记录1->2,2->3这个数据变化的过程.

3）redo log是循环写的，空间⼀定会⽤完，需要write pos和checkpoint搭配；binlog是追加写，写到⼀定⼤⼩会切换到下⼀个，并不会覆盖以前的⽇志

Redo Log ⽂件内容是以顺序循环的⽅式写⼊⽂件，写满时则回溯到第⼀个⽂件，进⾏覆盖写。

write pos: 表示⽇志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；

check point: 表示将⽇志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将⽇志上的相关记录擦除掉，即 write pos ->checkpoint 之间的部分是redo log空着的部分，⽤于记录新的记录，checkpoint -> write pos 之间是redo log 待落盘的数据修改记录

如果 write pos 追上 checkpoint，表示写满，这时候不能再执⾏新的更新，得停下来先擦掉⼀些记录，把 checkpoint 推进⼀下。

3）Redo Log作为服务器异常宕机后事务数据⾃动恢复使⽤，Binlog可以作为主从复制和数据恢复使⽤。Binlog没有⾃动crash-safe能⼒

CrashSafe指MySQL服务器宕机重启后，能够保证：

所有已经提交的事务的数据仍然存在。

所有没有提交的事务的数据⾃动回滚。

44.MySQL的binlog有几种日志格式？分别有什么区别？

binlog⽇志有三种模式

1）ROW（row-based replication, RBR）：⽇志中会记录每⼀⾏数据被修改的情况，然后在slave端对相同的数据进⾏修改。

优点：能清楚记录每⼀个⾏数据的修改细节，能完全实现主从数据同步和数据的恢复。⽽且不会出现某些特定情况下存储过程或function⽆法被正确复制的问题。

缺点：批量操作，会产⽣⼤量的⽇志，尤其是alter table会让⽇志量暴涨。

2）STATMENT（statement-based replication, SBR）：记录每⼀条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，⽽是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执⾏过的相同的SQL再次执⾏。简称SQL语句复制。

优点：⽇志量⼩，减少磁盘IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不⼀致，⽐如last_insert_id()、now()等函数。

3）MIXED（mixed-based replication, MBR）：以上两种模式的混合使⽤，⼀般会使⽤STATEMENT模式保存binlog，对于STATEMENT模式⽆法复制的操作使⽤ROW模式保存binlog，MySQL会根据执⾏的SQL语句选择写⼊模式。

企业场景如何选择binlog的模式

1. 如果⽣产中使⽤MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。

2. 如果⽣产中使⽤MySQL的特殊功能较多的，可以选择Mixed模式。

3. 如果⽣产中使⽤MySQL的特殊功能较多，⼜希望数据最⼤化⼀致，此时最好Row 模式；但是要注意，该模式的binlog⽇志量增⻓⾮常快.

45.mysql 线上修改⼤表结构有哪些风险？

在线修改⼤表的可能影响

在线修改⼤表的表结构执⾏时间往往不可预估，⼀般时间较⻓。

由于修改表结构是表级锁，因此在修改表结构时，影响表写⼊操作。

如果⻓时间的修改表结构，中途修改失败，由于修改表结构是⼀个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写⼊。

修改⼤表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低。

在线修改⼤表结构容易导致主从延时，从⽽影响业务读取。

修改⽅式：

1. 对表加锁(表此时只读)

2. 复制原表物理结构

3. 修改表的物理结构

4. 把原表数据导⼊中间表中，数据同步完后，**锁定中间表，并删除原表

5. rename中间表为原表

6. 刷新数据字典，并释放锁

使⽤⼯具： online-schema-change ，是percona推出的⼀个针对mysql在线ddl的⼯具。percona是⼀个mysql分⽀维护公司，专⻔提供mysql技术服务的。

46.count(列名)、count(1)和**count(*)有什么区别**?

进⾏统计操作时,count中的统计条件可以三种选择:

执⾏效果上：

count(*) 包括了所有的列,在统计时不会忽略列值为null的数据。

count(1) ⽤1表示代码⾏,在统计时,不会忽略列值为null的数据。

count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计。

执⾏效率上：

InnoDB引擎：count（字段) < count(1) = count(*)

InnoDB通过遍历最⼩的可⽤⼆级索引来处理select count(*) 语句，除⾮索引或优化器提示指示优化器使⽤不同的索引。如果⼆级索引不存在，则通过扫描聚集索引来处理。

InnoDB已同样的⽅式处理count(1)和count(*)

MyISAM引擎：count（字段) < count(1) <= count(*)

MyISAM存储了数据的准确⾏数，使⽤ count(*)会直接读取该⾏数，只有当第⼀列定义为NOT NULL时，count（1），才会执⾏该操作，所以优先选择 count(*)

count(列名) 会遍历整个表，但不同的是，它会先获取列，然后判断是否为空，然后累加，因此count(列名)性能不如前两者。

注意：count(*)，这是SQL92 定义的标准统计⾏数的语法，跟数据库⽆关，与NULL也⽆关。⽽count(列名) 是统计列值数量，不计NULL，相同列值算⼀个。

47.什么是分库分表？什么时候进行分库分表？

简单来说，就是指通过某种特定的条件，将我们存放在同⼀个数据库中的数据分散存放到多个数据库（主机）上⾯，以达到分散单台设备负载的效果。

分库分表解决的问题

分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原来单体服务的数据库进行拆分.将数据⼤表拆分成若干数据表组成，使得单⼀数据库、单⼀数据表的数据量变小，从⽽达到提升数据库性能的目的。

什么情况下需要分库分表

单机存储容量遇到瓶颈.

连接数,处理能⼒达到上限.

注意:

分库分表之前,要根据项⽬的实际情况确定我们的数据量是不是够⼤,并发量是不是够⼤,来决定是否分库分表.

数据量不够就不要分表,单表数据量超过1000万或100G的时候, 速度就会变慢(官⽅测试),

分库分表包括：垂直分库、垂直分表、⽔平分库、⽔平分表四种⽅式。

垂直分库

数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进⾏分类,分布到不同的数据库上⾯

将数据库部署在不同服务器上，从⽽达到多个服务器共同分摊压⼒的效果

垂直分表

表中字段太多且包含⼤字段的时候，在查询时对数据库的IO、内存会受到影响，同时更新数据时，产⽣的binlog⽂件会很⼤，MySQL在主从同步时也会有延迟的⻛险

将⼀个表按照字段分成多表，每个表存储其中⼀部分字段。

对职位表进⾏垂直拆分, 将职位基本信息放在⼀张表, 将职位描述信息存放在另⼀张表

垂直拆分带来的⼀些提升

解决业务层⾯的耦合，业务清晰

能对不同业务的数据进⾏分级管理、维护、监控、扩展等

⾼并发场景下，垂直分库⼀定程度的提⾼访问性能

垂直拆分没有彻底解决单表数据量过⼤的问题

⽔平分库

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。⽔平分库分表能够有效的缓解单机和单库的性能瓶颈和压⼒，突破IO、连接数、硬件资源等的瓶颈.

简单讲就是根据表中的数据的逻辑关系，将同⼀个表中的数据按照某种条件拆分到多台数据库（主机）上⾯, 例如将订单表按照id是奇数还是偶数, 分别存储在不同的库中。

⽔平分表

针对数据量巨⼤的单张表（⽐如订单表），按照规则把⼀张表的数据切分到多张表⾥⾯去。但是这些表还是在同⼀个库中，所以库级别的数据库操作还是有IO瓶颈

总结

垂直分表: 将⼀个表按照字段分成多表，每个表存储其中⼀部分字段。

垂直分库: 根据表的业务不同,分别存放在不同的库中,这些库分别部署在不同的服务器.

⽔平分库: 把⼀张表的数据按照⼀定规则,分配到不同的数据库,每⼀个库只有这张表的部分数据.

⽔平分表: 把⼀张表的数据按照⼀定规则,分配到同⼀个数据库的多张表中,每个表只有这个表的部分数据

48.说说 MySQL 的主从复制？

主从复制的⽤途

实时灾备，⽤于故障切换

读写分离，提供查询服务

备份，避免影响业务

主从部署必要条件

主库开启binlog⽇志（设置log-bin参数）

主从server-id不同

从库服务器能连通主库

主从复制的原理

Mysql 中有⼀种⽇志叫做 bin ⽇志（⼆进制⽇志）。这个⽇志会记录下所有修改了数据库的SQL 语句

（insert,update,delete,create/alter/drop table, grant 等等）。

主从复制的原理其实就是把主服务器上的 bin ⽇志复制到从服务器上执⾏⼀遍，这样从服务器上的数据就和主服务器上的数据相同了。

1. 主库db的更新事件(update、insert、delete)被写到binlog

2. 主库创建⼀个binlog dump thread，把binlog的内容发送到从库

3. 从库启动并发起连接，连接到主库

4. 从库启动之后，创建⼀个I/O线程，读取主库传过来的binlog内容并写⼊到relay log

5. 从库启动之后，创建⼀个SQL线程，从relay log⾥⾯读取内容，执⾏读

取到的更新事件，将更新内容写⼊到slave的db

49. 说⼀下 MySQL 执⾏⼀条查询语句的内部执行过程？

①建⽴连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建⽴连接。MySQL 客户端与服务端的通信⽅式是 “ 半双⼯ ”。对于每⼀个 MySQL 的连接，时刻都有⼀个线程状态来标识这个连接正在做什么。

通讯机制：

全双⼯：能同时发送和接收数据，例如平时打电话。

半双⼯：指的某⼀时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机

单⼯：只能发送数据或只能接收数据。例如单⾏道

线程状态：

show processlist; //查看⽤户正在运⾏的线程信息，root⽤户能查看所有线程，其他⽤户只能看⾃⼰的

id：线程ID，可以使⽤kill xx；

user：启动这个线程的⽤户

Host：发送请求的客户端的IP和端⼝号

db：当前命令在哪个库执⾏

Command：该线程正在执⾏的操作命令

Create DB：正在创建库操作

Drop DB：正在删除库操作

Execute：正在执⾏⼀个PreparedStatement

Close Stmt：正在关闭⼀个PreparedStatement

Query：正在执⾏⼀个语句

Sleep：正在等待客户端发送语句

Quit：正在退出

Shutdown：正在关闭服务器

Time：表示该线程处于当前状态的时间，单位是秒

State：线程状态

Updating：正在搜索匹配记录，进⾏修改

Sleeping：正在等待客户端发送新请求

Starting：正在执⾏请求处理

Checking table：正在检查数据表

Closing table : 正在将表中数据刷新到磁盘中

Locked：被其他查询锁住了记录

Sending Data：正在处理Select查询，同时将结果发送给客户端Info：⼀般记录线程执⾏的语句，默认显示前100个字符。想查看完整的使⽤show full processlist;

②查询缓存（Cache&Buffer），这是MySQL的⼀个可优化查询的地⽅，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进⾏语法语义解析，并⽣成“解析树”。

缓存Select查询的结果和SQL语句

执⾏Select查询时，先查询缓存，判断是否存在可⽤的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。

即使开启查询缓存，以下SQL也不能缓存

查询语句使⽤SQL_NO_CACHE

查询的结果⼤于query_cache_limit设置

查询中有⼀些不确定的参数，⽐如now()

show variables like '%query_cache%'; //查看查询缓存是否启⽤，空间⼤⼩，限制等

show status like 'Qcache%'; //查看更详细的缓存参数，可⽤缓存空间，缓存块，缓存多少等

③解析器（Parser）将客户端发送的SQL进⾏语法解析，⽣成"解析树"。预处理器根据⼀些MySQL规则进⼀步检查“解析树”是否合法，例如这⾥将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后⽣成新的“解析树”。

④查询优化器（Optimizer）根据“解析树”⽣成最优的执⾏计划。MySQL使⽤很多优化策略⽣成最优的执⾏计划，可以分为两类：静态优化（编译时优化）、动态优化（运⾏时优化）。

等价变换策略

5=5 and a>5 改成 a > 5

a < b and a=5 改成b>5 and a=5

基于联合索引，调整条件位置等

优化count、min、max等函数

InnoDB引擎min函数只需要找索引最左边

InnoDB引擎max函数只需要找索引最右边

MyISAM引擎count(*)，不需要计算，直接返回提前终⽌查询

使⽤了limit查询，获取limit所需的数据，就不在继续遍历后⾯数据

in的优化

MySQL对in查询，会先进⾏排序，再采⽤⼆分法查找数据。⽐如where id in (2,1,3)，变成 in (1,2,3)

⑤查询执⾏引擎负责执⾏ SQL 语句，此时查询执⾏引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接⼝与底层存储引擎缓存或者物理⽂件的交互，得到查询结果并返回给客户端。若开启⽤查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执⾏则直接返回结果。

如果开启了查询缓存，先将查询结果做缓存操作

返回结果过多，采⽤增量模式返回