MySQL面试（三）-CSDN博客

本文链接：https://blog.csdn.net/java_beautiful/article/details/147992742

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 ）定义：⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的。

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

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

在 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 的行锁是怎么实现的？

I nnoDB ⾏锁是通过对索引数据⻚上的记录加锁实现的 ，主要实现算法有 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 和 check point 搭配； 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 语句执⾏则直接返回结果。

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

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