MySQL 篇

原子性(Atomic)：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。通过undo log日志来实现。
一致性(Consistency)：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。由其它3个特性和业务代码的正确性来保证的。
隔离性(Isolation)：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰。MVCC机制和各种锁机制来保证的。
持久性(Durabiliy)：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。WAL（write-ahead logging）机制来保证的。

3、MySQL数据库引擎

show engines;

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎。
Innodb：行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些。
Memory：全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表。
MERGE：是一组MYISAM表的组合。

4、说说 InnoDB 与 MyISAM 的区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败。
InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。
在 MySQL 5.1 及之前的版本中， MyISAM 是默认的存储引擎，而在 MySQL 5.5 版本以后，默认使用 InnoDB 存储引擎。
MyISAM 索引文件和数据文件是分离的（非聚集），一张表生成三个文件：
xxx.frm：存储表结构
xxx.MYD：存储表数据
xxx.MYI：存储表索引
索引和数据（索引行对应的所有列数据）不是分离的（聚集），一张表生成两个文件：
xxx.frm：存储表结构
xxx.ibd：存储索引和数据
MySQL8.0之后，将.frm文件整合到了.ibd文件

5、索引是什么？

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中 的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特 别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

6、索引数据结构

二叉树

红黑树（二叉平衡树）
Hash表
对索引的key进行一次hash计算就可以定位出数据存储的位置。
等值查询效率高（=，IN），不能排序，不能进行范围查询，hash冲突问题。

B-Tree
节点具有相同的深度，页节点的指针为空。
所有索引元素不重复。
节点中的数据所有从左到右递增排列。
数据有序，范围查询。

B+Tree（B-Tree变种）
非页子节点不存储data，只存储索引（冗余），可以放更多的索引。
页子节点包含所有的索引字段。
页子节点用指针（双向指针）连接，提高区间访问的性能。
高版本MySQL（MySQL8）加载的时候，一二层叶子数据（冗余索引）加载在内存中，底层叶子数据存储在磁盘。

7、MySQL 索引类型有哪些？

主键索引：索引列中的值必须是唯一的，不允许有空值。
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
全文索引：只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
前缀索引：在文本类型如CHAR、VARCHAR、TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
其他（按照索引列数量分类）：
1. 单列索引。
2. 组合索引：组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

8、索引有什么优缺点？

优点：

提高数据的检索速度，降低数据库IO成本。使用索引的意义就是通过缩小表中需要查询的记录数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗。索引之所以查询快，是原因事先已经将数据排好序。若该字段正好需要排序，则正好降低了排序的成本。

缺点：

占用存储空间。索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
降低更新表速度。表的数据发生了变化，对应的索引也需要一起变更，从而降低了更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

9、索引设计原则

代码先行，索引后上：建议等主业务功能开发完毕，把涉及到该表相关sql拿出来分析之后再建立索引。
选择合适的列：选择经常用于查询、连接、过滤和排序的列作为索引列，这些列能够帮助加速查询操作。
联合索引的选择：创建联合索引应尽可能覆盖业务上的多个列，单个表联合索引不建议超过3个。
覆盖索引：覆盖索引可以减少回表次数，根据业务情况，尽可能的使用覆盖索引。
避免过长索引：长字符串我们建议采用前缀索引，前缀索引长度不建议超过20。
选择适当的索引类型：根据查询类型选择适当的类型，如普通索引、唯一索引、全文索引等。
考虑查询的顺序：如果查询中涉及排序操作，考虑在排序的列上创建索引，以避免文件排序操作。
避免冗余索引：不要创建重复的索引，冗余索引会增加维护开销。
考虑分区度：尽量不要挑选区分度不高的字段作为索引。
定期维护索引：定期检查和优化索引，包括删除不再使用的索引、重建碎片化的索引等。

9、使用索引应该注意些什么？

10、什么是视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是
有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，
相比多表查询。

11、什么是内联接、左外联接、右外联接？

内联接（Inner Join）：匹配2张表中相关联的记录。
左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。
右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join 的左右位置关系。

12、说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别。

delete 和 truncate只删除表的数据不删除表的结构。速度，一般来说：drop > truncate > delete。delete 语句是dml，这个操作会放到rollback segement中，事务提交之后才生效;。如果有相应的trigger，执行的时候将被触发。
truncate，drop是ddl, 操作立即生效，原数据不放到rollback segment中，不能回滚。操作不触发trigger.。

13、varchar 与 char 的区别

CHAR 和 VARCHAR 类型在存储和检索方面有所不同
char 是一种固定长度的类型，长度值范围是1 到255当 CHAR 值被存储时，多余部分用空格填充到特定长度，检索CHAR 值时需删除尾随空格。varchar 则是一种可变长度的类型。
对效率要求高用 char，对空间使用要求高用 varchar。

14、varchar(30) 中的 30 代表的涵义？

varchar(30) 中 30 的涵义最多存放 30 个字符。varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fifixed_length 计算 col 长度（memory 引擎也一样）。

15、int(11) 中的 11 代表什么涵义？

int(11) 中的 11，不影响字段存储的范围，只影响展示效果。

16、主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

17、主键与唯一索引有什么区别？

主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键。
主键不允许为空值，唯一索引列允许空值。
一个表只能有一个主键，但是可以有多个唯一索引。
主键可以被其他表引用为外键，唯一索引列不可以。
主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质区别。

18、为什么 **SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM** 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；MyISAM内部维持了一个计数器，预存了结果，所以直接返回即可。

19、说说count(pk/*/1/字段)的区别

count(pk)/count(*)/count(1)统计的是符合条件的数据库表的行数，count(字段)统计的是符合条件的且列值不为null的数据库表的行数。

执行时间和执行计划

执行计划完全一样：

我们可以看到，这4个SQL的执行时间几乎一样，执行计划也一样，说明这4个SQL执行效率差不多。所以，我们可以简单认为他们执行效率和执行时间没有区别。但如果从细节上考虑，还是有细微区别：

字段有索引：count(*)≈count(1)>count(字段)>count(pk)
说明：字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，索引count(字段)>count(pk)。

字段无索引：count(*)≈count(1)>count(pk)>count(字段)
说明：字段无索引，count(字段)走不了索引，count(pk)可以走主键索引，所以count(pk)>count(字段)。

count(1)和count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量做统计，count(字段)还需要取出字段，并且count(字段)还需要过滤掉null值。所以理论上count(1)比count(字段)会快一点。

count(*)是SQL92定义的标准统计行的语法，内部做过很多优化！MySQL并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以一般场景下推荐使用count(*)来统计行数。

为什么对于count(pk)，MySQL最终选择辅助索引而不是主键索引？因为二级索引相对于主键索引存储数据更少，检索性能更高，MySQL5.7内部做了优化。

20、MySQL 中一条查询 SQL 是如何执行的？

select name from t_user where id=1;

取得链接：使用使用到 MySQL 中的连接器。
查询缓存：key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。
分析器：分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
优化器：是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
执行器：通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

21、查询需要使用事务吗？

如果仅有一个查询方法，使用事务也没有意义。所以不建议使用事务。
RC隔离级别，加不加事务没有什么影响。因为最终查询出来的都是最新提交的数据。
RR隔离级别，永远都是读取第一次查询时的数据库快照，除非本事务内有更新操作。这种快照读最大的好处就是保证时间维度是一致的。如：对并发要求不高的财务报表/OA报表统计查询。

总结：对并发要求不高且对数据有时间维度一致性要求下的场景，可以考虑RR事务隔离级别下对于多个查询方法开启事务。其它场景，查询方法一般不建议使用事务。

22、MySQL 中一条更新语句是怎么执行的？

update t_user set name = 'XXX' where id = 1;

加载id=1的记录所在的整页数据到缓存池。
旧值写入undolog便于回滚。
更新内存数据。
写redo log到RedoBuff。
redo log顺序写入磁盘，准备提交事务（prepare阶段）。
准备提交事务，binlog写入磁盘。
写入commit标记到redo log文件里，提交事务完成，该标记为了保证事务提交后redo log和binlog数据一致。
系统空闲时，随机写入磁盘，以page为单位写入。

为什么MySQL不直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差，所以直接更新磁盘文件不能让数据库抗住高并发。

MySQL这套机制看起来复杂，但他可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是远高于随机读写磁盘文件的。

正是通过这套机制，才能让MySQL在较高配置的机器上每秒可以抗下几千甚至上万的读写请求。

什么是redo log和binlog的2阶段提交？

prepare 阶段：binlog写入磁盘持久化。

这个阶段SQL已经执行并更新BufferPool内存数据。
生成redo log到redolog buff并开始顺序写入磁盘。

commit 阶段：写入commit标记到redo log文件里，提交事务完成。

什么是WAL机制？

先写redo log后刷新数据库文件的机制叫做WAL（Write-Ahead Logging）机制，效率更高。

23、并发事务带来哪些问题?

脏读（Dirty read）：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

例如：事务T1修改字段A为21还未提交，事务T2读取到事务T1修改但为提交的值21。

丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。第二个事务比第一个事务先提交，第一个事务修改的结果会覆盖第二事务的结果，因此称为丢失修改。

例如：事务t1读取某表中的数据A=20，事务2也读取A=20，事务T1修改A=19，事务T2也修改A=21，最终结果A=19，事务T2的修改被丢失。

事实上，这种情况在InnoDB存储引擎中并不会发生。因为我们对InnoDB存储引擎的数据库表进行更新操作的时候需要对行加锁，因此当事务T1修改A还没有提交的时候，事务T2对A的更新是会被阻塞的，直到事务T1提交事务释放锁。

不可重复读（Unrepeatableread）:指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务T1第一次读取字段A为20，事务T2修改了字段A为21，事务T2第二次读取字段A为21。

幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例如：事务T1查询表t，查询结果有1条记录，事务T2新增了一条记录，事务T1再次查询的时候，查询结果有2条记录。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增
或者删除比如多次读取一条记录发现记录增多或减少了。

24、事务隔离级别

隔离级别	脏写	脏读	不可重复读	幻读
读未提交(Read Uncommitted)	×	√	√	√
读已提交(Read Committed：RC)	×	×	√	√
可重复读(Repeatable Read：RR)	×	×	×	√
串行化(Serializable)	×	×	×	×

MySQL默认隔离级别：可重复读，Oracle默认隔离级别：读已提交。

25、说说事务隔离级别的实现原理

MVCC机制+锁实现。

MyISAM在执行select前，会自动给涉及到的所有表加读锁，在执行update、insert、delete前会给所有涉及到的表加写锁。

InnoDB在执行select时（非串行化隔离级别），不会加锁。但是update、insert、delete会加写锁。另外，读锁会阻塞写，但不会阻塞读。而写锁会把读和写都阻塞。

读未提交(Read Uncommitted)：读取undo log日志版本链最新数据+排他锁。

读已提交(Read Committed：RC)：MVCC机制+排他锁。

可重复读(Repeatable Read：RR)：MVCC机制+排他锁。

串行化(Serializable)：共享锁+排他锁。

26、说说什么是 MVCC？

多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无
锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳
关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操
作，写操作不用阻塞读操作，提高了数据库并发读写的性能，避免了脏读和不可重复读，但不能解决更新丢失问题。

MySQL在RR和RC隔离级别下都实现了MVCC机制。

27、说说 MVCC 的实现原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖行记录的 3 个隐式字段、undo 日志版本链和 Read View 根据可见性算法来实现的。

行记录的隐式字段

在MySQL数据库表的每行记录中，除了我们自己定义的字段外，还有几个隐式字段：
db_trx_id：6byte，最近插入/修改的事务ID，记录创建这条记录/最后一次修改该记录的事务ID。
db_roll_ptr：7byte，回滚指针，指向这条记录的上一个版本。
db_row_id：6byte，隐含的自增ID（隐藏主键）。

一行数据完整字段：

undo 日志版本链

undo log 分两种：
insert undo log：代表事务在insert是产生的undo log，只在事务回滚时需要，并且在事务提交后被立即丢弃。
update undo log：事务在进行update时产生的undo log，不仅在事务回滚时需要，在快照读时也需要，所以不能删除，只有在快照读或者事务回滚不涉及该日志时，对应的日志才会被purge线程同一清除。
例如：现在要对t_account表id=1的记录做如下修改

update t_account set balance = blance + 100 where id = 1;

在事务A修改改行记录时，数据库会先对该行记录加排他锁。
把该行数据拷贝到undo log中。
拷贝完成后，修改该行记录balance=100，我们假设现有事务的事务id=1，此次修改的事务id=2。
提交事务，释放锁。

undo log日志版本链：

Read View

事务进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID。
主要由三部分组成：
trx_ids：一个数值列表，用户维护Read View生成时刻系统正活跃的事务ID列表。
up_limit_id：是trx_ids列表中事务ID最小的ID。
low_limit_id：Read View生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1。

可见性算法

1、有5个事务（事务A、事务B、事务C、事务D、事务E）正在执行。
2、A1和B2执行的sql与本测试无关，无需关注，目的仅是生成对应的事务ID。
3、C3更新balance=100并提交事务，记录undo log。
4、D4在RR事务隔离级别下执行了查询sql，现阶段活跃的事务ID：100、200、300（最大事务ID），所以Read View的构成：trx_ids=[100,200]、up_limit_id=100、low_limit_id=301。我们姑且写成 Read View：[100,200]+301。
5、同理，E4在RC事务隔离级别下的Read View：[100,200]+301。
6、A5更新balance=200，记录undo log。
7、A6更新balance=300并提交事务，记录undo log。
8、D7在RR事务隔离级别下，Read View为第一次查询时的Read View：[100,200]+301保持不变。
9、E7在RC事务隔离级别下，每次查询都更新Read View，此时Read View：[200]+301。
10、B8更新balance=400，记录undo log。
11、B9更新balance=500并提交事务，记录undo log。
12、D10在RR事务隔离级别下，Read View为第一次查询时的Read View：[100,200]+301。
13、E11在RC事务隔离级别下，每次查询都要更新Read View，此时Read View：[]+301。

总结：

RR事务隔离级别下，同一个事务中的第一个快照读才会创Read VIew，之后的快照读获取的都是同一个Read View。

RC事务隔离级别下，每个快照读都会获取最新的Read View。

MVCC机制的实现就是通过Read View与undo log日志链版本对比机制，使得不同的事务隔离级别根据日志链对比规则读取同一条数据在日志版本链上的不同版本数据。

28、说说快照读与当前读

快照读：读取的是undo log日志版本链的可见版本，该可见版本可能是历史版本，不用加锁。

所谓undo log日志版本链是指一行数据被多个事务依次修改后，MySQL会保留修改前的数据undo loghuigun日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo log日志串联起来形成一个历史记录版本链。

快照读是基于MVCC机制实现的，使用普通的不加锁的select语句读取：

select * from t where .....

当前读：读取的是undo log日志版本链的最新版本，并且当前读返回的记录都会加上锁，保证其它事务不会再并发修改这条记录。

当前读的五种方式：

-- 共享锁
select ... lock in share mode;
-- 排他锁
select ... for update;
insert into ...;
delete from ...;
update ...;

在MySQL中，只有RC和RR这两种事务隔离级别才会使用快照读。

在RC中，每次select都会重新生成一个快照，读取的是最新的事务提交版本数据。

在RR中，则会在事务开启后第一次select查询时生成快照，之后的select都是使用第一创生成的快照，只有在本事务中对数据更改才会更新快照。

29、说说 MySQL 数据库的锁？

按照锁的粒度划分：表级锁、页级锁和行级锁。

按照对数据库操作类型划分：共享锁（读锁）和排他锁（写锁）。

按照锁的对象划分：间隙锁、临键锁和记录锁。

按照加锁的方式划分：乐观锁和悲观锁。

共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行锁了）。

30、说说间隙锁、临键锁和记录锁

记录锁（Record Lock）：对表中的记录加锁，简称行锁。
记录锁加锁5种方式：

-- 共享锁
select ... lock in share mode;
-- 排他锁
select ... for update;
insert into ...;
delete from ...;
update ...;

间隙锁（Gap Lock）：锁的是两个值之间的间隙，间隙锁在可重复读隔离级别下才会生效。
例如表记录：

那么间隙锁就有id为(3,10)、(10,20)、(20,正无穷)这三个区间，在Session1下面执行sql：

select * from account where id = 18 for update;

则其它Session无法在(10,20)这个间隙范围内插入任何数据。如果执行如下sql：

select * from account where id = 25 for update;

则其它Session无法在(20,正无穷)这个范围内插入任何数据。也就是说，只要在间隙范围内锁了一条存在的记录会锁住整个间隙范围，不会锁边界，这样能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

临键锁（Next-key Locks）：是记录锁和间隙锁的组合，指的是在加某条记录及这个记录前面间隙上的锁，它会同时锁索引记录和间隙。范围是左开右闭。

31、说说悲观锁和乐观锁

悲观锁：说的是数据库被外界（包括本系统当前的其他事物以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
乐观锁：相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

32、说说什么是锁升级？

MySQL 中有共享锁和排它锁，也就是读锁和写锁。MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B+ 树的页节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。
当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

33、怎样尽量避免死锁的出现？

设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁。
设置按照同一顺序访问资源，类似于串行执行。
避免事务中的用户交叉。
保持事务简短并在一个批处理中。
使用低隔离级别。
使用绑定链接。

34、说说RR与RC的区别和应用场景

并发度
RC事务隔离级别对数据的加锁仅支持锁粒度最低的记录锁，不像RR还支持间隙锁和临键锁，所以对并发的支持更好。

死锁
RR事务隔离级别为了解决幻读，引入了间隙锁和临键锁，但是也大大曾大了锁粒度，增加了死锁的可能性。

快照读
也叫一致性读，是基于MVCC机制的非阻塞读实现的，读取的是undo log日志版本链的可见版本。在MySQL中，只有RC和RR这两种隔离级别才会使用快照读。在RC中，每次select都会重新生成一个快照，读取的是最新的事务提交的版本数据。而RR则会在事务开启后第一次select查询的时候生成快照，只有在本事务中对数据更改才会更新快照。

锁机制
在RC中只支持记录锁，而RR除了支持记录锁，还支持间隙锁和临键锁。

主从同步
MySQL的binlog支持三种日志格式：STATEMENT、ROW和MIXED。
RR事务隔离级别三种都支持，而RC仅支持ROW。

应用场景
RC事务隔离级别适用于并发性能要求较高的场景，例如大部分在线业务系统。
RR事务隔离级别适用于并发性能要求不高的场景，同时对数据的时间维度一致性要求较高的场景，例如财务报表统计。

总结：MySQL默认隔离级别是RR，而大厂确改为RC的主要原因就是考虑到并发、性能、死锁等因素。

35、说说 undolog、redolog 和 binlog

undo log

也叫回滚日志。
用于事务回滚。
如果事务提交失败，要回滚数据，可以使用undo log日志里面的数据恢复到Buff Pool里面的缓存数据。

redo log

也叫重做日志。
日志记录方式：物理日志。
主要用于恢复磁盘ibd文件里面的数据。
如果事务提交成功，Buff Pool里面的数据还没来及写入磁盘，此时系统宕机，可以使用redo log日志恢复磁盘ibd文件里面的数据。

binlog

也叫归档日志。
日志记录方式：逻辑日志。
主要用于恢复数据库磁盘里面的数据。
多用于数据恢复、主从同步等场景。

物理日志与逻辑日志

逻辑日志：可以简单理解为记录的就是sql语句。

物理日志：MySQL数据最终是摆存在数据页中的，物理日志记录的就是数据页变更。简单来说就是，在哪个数据页做了什么修改

binlog和undo log日志都属于逻辑日志，redo log属于物理日志。

36、大表如何优化？

限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。
比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
读/写分离：经典的数据库拆分方案，主库负责写，从库负责读。
垂直分区：根据数据库里面数据表的相关性进行拆分。
例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。
水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。
例如：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

数据库分片的两种常见方案：

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

详细内容可以参考：MySQL大表优化方案。

37、分库分表之后，id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id来支持。生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
Twitter的snowflake算法 https://github.com/twitter-archive/snowflake。
美团的Leaf分布式ID生成系统：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。https://tech.meituan.com/2017/04/21/mt-leaf.html

38、MySQL 优化手段有哪些？

表结构&索引：

选择合适的数据库引擎。
选择正确的索引。
适当的反范式设计表结构。

SQL语句优化：

避免select * 查询，尽可能使用索引扫描等等。
只要一行数据时使用 limit 1。查询时如果已知会得到一条数据，这种情况下加上limit 1会增加性能。因为mysql数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
not exists代替not in。not exists 用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。not in是最慢的方式要同每条记录比较，在数据量比较大的操作中不建议使用这种方式。
操作符的优化，尽量不采用不利于索引的操作符。如：in、not in、is null、is not null、<> 等，某个字段总要拿来搜索，为其建立索引。
减少使用or，使用 union all 或者 union 来替代。

Mysql参数优化：

设置Buffer_pool大小，官方推荐总内存50%-70%左右。
设置刷盘策略，平衡好数据安全性和性能关系。

sync-binlog：控制binlog刷入磁盘的频率，default vaule：1。
0：禁止MySQL服务器将二进制日志同步到磁盘。相反，MySQL服务器依赖于操作系统不时地将二进制日志刷新到磁盘，就像处理其他文件一样。此设置提供了最佳性能，但是在出现电源故障或操作系统崩溃时，服务器可能提交了未同步到二进制日志的事务。
1：允许在事务提交之前将二进制日志同步到磁盘。这是最安全的设置，但是由于磁盘写操作的增加，可能会对性能产生负面影响。在出现电源故障或操作系统崩溃时，二进制日志中缺少的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证二进制日志中没有丢失任何事务。
N：其中N是0或1之外的值:在收集了N个二进制日志提交组之后，将二进制日志同步到磁盘。在出现电源故障或操作系统崩溃时，服务器可能提交了未刷新到二进制日志的事务。由于磁盘写操作数量的增加，该设置可能会对性能产生负面影响。值越大，性能越好，但是数据丢失的风险越大。

innodb-flush-log-at-trx-commit：控制redo log刷新到磁盘，default vaule：1。
1：默认设置为1是完全符合ACID要求的。日志在每次事务提交时被写入并刷新到磁盘。
0：设置为0时，每秒将日志写入并刷新到磁盘一次。没有刷新日志的事务可能在崩溃中丢失。
2：如果设置为2，则在每个事务提交之后写入日志，并每秒刷新一次磁盘。没有刷新日志的事务可能在崩溃中丢失。

硬件&系统设置

CPU核心数、磁盘的读写性能、网卡、内存大小等等。

39、说说深度分页优化

场景描述：分页查询是我们业务场景中最常见的。但是如果数据量很大时，页数也就随之很大，这是，如果查询最后几页，就会出现深度分页问题。

select * from employee limit 99990,10;

表示从employee表中取出99990行开始的10行记录，看似值查询了10行记录，实际上这条sql是先读取100000条记录，然后抛弃前99990条记录，然后读取到后面10条想要的记录。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

优化方案：

根据自增且连续的主键排序分页

select * from employee limit 99990,10;
select * from employee where id > 99990 limit 10;

执行计划：

很遗憾，我们大多数场景下都不适合，因为我们的业务中基本上都会存在删除业务。

根据非主键字段排序的分页

select * from employee order by name limit 99990,10;

根据MySQL的cost成本计算，这条语句是不会走索引的。那么我们如何优化呢？其关键就是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查询对应的记录，SQL改写如下：

select * from employee e innor join(select id from employee order by name limit 99990,10) ed on e.id = ed.id;

原SQL使用的是filesort，优化后SQL使用的是索引排序。

执行时间对比，时间成本减少了一半以上。

ww_kobe

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL 篇

MySQL 知识点
复制链接

扫一扫

专栏目录