1、基本概念

1.1 MySQL架构的简介？

从上到下分为四层：客户端、Server层、存储引擎层和文件系统层。

客户端：各种开发语言都提供了对应的数据库连接框架，比如Java提供了JDBC，阿里的Druid数据库连接池；
Server层：包含了连接器（管理连接、用户鉴权）、查询缓存、分析器（解析sql语句，提取sql语句中表的信息及查询条件）、优化器（用哪个索引、子查询转连接查询等）、执行器以及MySQL管理系统；
存储引擎层：负责与文件系统交互，从磁盘中读取数据，将数据写入到磁盘中，存储引擎被设计成可拔插的插件形式；
文件系统层：MySQL中的表以及记录会保存在磁盘中，最终会映射在文件系统上，会在datadir目录下，每个数据库对应datadir目录下的一个子目录，这个子目录里会保存表结构的定义文件（表名.frm）、系统表空间（ibdata1，ibdata2…）以及独立表空间（表名.ibd）。
1.2 一条sql语句的执行过程？

客户端向MySQL服务端发送一个查询语句的请求；
MySQL服务端的Service层先进行处理，首先是连接器，建立客户端与服务端之间的连接，并鉴权；
MySQL服务端会先查询缓存，如果缓存命中，则直接返回缓存中数据页中的数据结果；否则进入下一阶段；
分析器对sql文本进行解析，生成解析树；
优化器对解析树进行优化，比如调整where查询条件的顺序与索引列一致，生成一个执行计划；
执行器基于优化器生成的执行计划，调用存储引擎底层的api来执行，并将结果返回给客户端。
1.3 候选键、主键、UNIQUE键、外键的概念分别是什么？

候选键：对于一张表，可以通过某个列或者某一组列来唯一确定表中的一条记录，这个列或者这组列被称为表的候选键。一个表中可以有多个候选键；
主键：从候选键中选出一个作为主键，一个表中只能有一个主键；
UNIQUE键：表中不是主键的列，也想赋予它独一无二的含义，即该列或者这一组列中的值是不可重复的，一个表中可以有多个UNIQUE键；
外键：如果A表中的某个列或者某些列依赖于B表中的某个列或者某些列，那么就称A表为子表，B表为父表。子表和父表使用外键来关联起来，一般在创建表的时候声明外键，关联子表和父表。当向子表中插入数据时，MySQL都会检查插入数据的外键列对应的值是否能在父表中对应的外键列找到，如果找不到会报错。

1.4 外连接、内连接的概念和区别是什么？
内连接：连接查询中，当驱动表中的记录在被驱动表中找不到匹配记录时，该记录不添加到结果集；
外连接：连接查询中，当驱动表中的记录在被驱动表中找不到匹配记录时，仍将驱动表中的记录添加到结果集，此时驱动表在被驱动表中匹配不到的字段用NULL填充。

1.5 存储过程和存储函数的区别？
存储过程和存储函数都可以批量执行多个sql语句，不同点在于：
存储函数需要些RETURNS返回值类型，而且函数体中要有RETURNS语句，而存储过程没有；
存储函数可以直接在表达式中调用，存储过程需要显式地通过CALL语句调用；
存储函数侧重经过sql语句后返回一个值，存储过程侧重批量执行多个sql语句；
存储函数执行过程中产生的结果集并不会被显示到客户端，存储过程执行过程中产生的结果集会被显示到客户端。
1.6 MySQL中in和exists的区别？
MySQL中in语句和exists语句都可以用在子查询里，如下： ```sql select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)

select * from TableIn where ANAME in(select BNAME from TableEx)


- EXISTS：先对外表进行全表遍历，对外表的每一行数据去关联子表进行查询，如果子表不为空则外表查询时会使用到索引进行查询；
- IN：先执行子查询，将子查询的结果作为主查询的条件，再执行主查询。
二者查询效率比较：
- 当外表大，内表小时，用IN；
- 当外表小，内表大时，用EXISTS。
<a name="oyvQk"></a>
## 1.7 主键使用自增id还是uuid，为什么？
建议使用自增id作为主键。因为在插入记录时，如果插入的数据页满了，在遵循一个数据页内记录按主键值从小到大的顺序排序，多个数据页之前也是按照主键值的顺序组成的双向链表，会新生成一个数据页，之前数据页的记录会按前面介绍的规则分布在老数据页和新数据页内，这个过程伴随着页分裂和数据从老数据页移动到新数据页的过程，是很消耗性能的。主键使用uuid上述情况就会发生，但是主键使用自增id就不会发生，因为自增id的记录插入，永远是一个数据页写满了再插入新的数据页，不会有记录在数据页之间移动的性能损耗。
<a name="xwO55"></a>
## 1.8 MySQL自增主键用完了怎么办？
1. 首先这个问题没有遇到过，因为自增主键一般用int类型，MySQL中int类型占4个字节，共32个比特位，以无符号整数来说，最大值可以到43亿，单表一般数据超过百万级别就应该考虑分库分表，因此正常情况下不会出现自增主键用完的场景。
1. 如果一定要基于这个几乎不可能出现的场景分析，一个解决方案就是将自增主键的id类型由int改为Bigint，BigInt类型占8个字节，64的比特位，数量肯定是够用的；
1. 如果修改自增主键类型，就涉及到一个如何在线修改数据库表结构的问题，这里有两个方法：
   1. 使用MySQL提供的ALTER语句，该语句在线修改表结构，不会阻塞原表的读取和写入：
```sql
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

采用第三方工具，比如pt-osc和gh-ost。
1.9 InnoDB和MyISAM的区别？
MySQL在5.0版本前的默认存储引擎使MyISAM，5.0版本后的默认存储引擎使InnoDB。

InnoDB支持事务，MyISAM不支持事务，这也是MySQL将默认存储引擎设置为InnoDB的原因之一；
InnoDB支持外键，MyISAM不支持外键；
InnoDB支持行级锁和表级锁，MyISAM仅支持表级锁；
InnoDB的聚簇索引，索引本身就是数据，而MyISAM的索引和数据是分开存放的。

如何选择？

如果要支持事务，只能选择InnoDB；
InnoDB适合频繁修改以及涉及到安全性较高的应用，MyISAM适合查询以及插入为主的应用；

1.10 binlog和redo log的区别？
从三个维度：适用的对象、写入的内容、写入的方式比较：
使用的对象不同：
- binlog是MySQL的Server层实现的，所有的存储引擎都可以使用；
- redo log是InnoDB存储引擎特有的。
写入的内容不同：
- binlog是逻辑日志，记录的是这个语句的原始逻辑，比如 “给 id = 1 这一行的 age 字段加 1”；
- redo log是物理日志，记录的是数据页中数据的变化，比如一条sql语句在哪几个数据页中做了修改。
写入的方式不同：
- binlog是追加写，写满一个binlog后再切换到下一个binlog里写；
- redo log是循环写，redo log只会记录未刷入磁盘中的日志，已经刷入磁盘中的数据都会从redo log这个有限的日志文件中移除。
  1.11 讲一下redo log的两阶段提交原理？/如何解决bin log和redo log的一致性问题？/MySQL恢复数据库为什么不能仅靠bin log恢复？
  所谓两阶段提交，其实就是把 redo log 的写入拆分成了两个步骤：prepare 和 commit。首先，存储引擎将执行更新好的新数据存到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。然后执行器生成这个操作的 bin log，并把 bin log 写入磁盘。最后执行器调用存储引擎的提交事务接口，存储引擎把刚刚写入的 redo log 状态改成提交（commit）状态，更新完成。
  如果数据库在写入 redo log(prepare) 阶段之后、写入 binlog 之前，发生了崩溃，此时 redo log 里面的事务处于 prepare 状态，binlog 还没写，之后从库进行同步的时候，无法执行这个操作，但是实际上主库已经完成了这个操作，所以为了主备一致，MySQL 崩溃时会在主库上回滚这个事务；而如果数据库在写入 binlog 之后，redo log 状态修改为 commit 前发生崩溃，此时 redo log 里面的事务仍然是 prepare 状态，binlog 存在并完整，这样之后就会被从库同步过去，但是实际上主库并没有完成这个操作，所以为了主备一致，即使在这个时刻数据库崩溃了，主库上事务仍然会被正常提交。
  1.12 介绍一下查询缓存？
  见我这篇文章的3.2节：常见的MySQL语句优化
  1.13 查询缓存和Buffer Pool的区别？
  两者共同点都是为了加速查询速度，都有缓存的功能。区别在于查询缓存是MySQL Server层做的优化，且在MySQL 8.0之后查询缓存这个模块已经作废了，查询缓存是用一个类似哈希表的数据结构，key是查询语句，value是查询结果；Buffer Pool是存储引擎层做的优化，为了减少磁盘与内存之间io，而将数据页缓存在Buffer Pool中，下次sql操作的数据页如果就在Buffer Pool中，直接从Buffer Pool中获取数据页即可，不需要再走磁盘IO读取数据页。
  2、事务
  2.1 事务的ACID四大特性有哪些？是如何保证的？
原子性（Atomicity）：针对一组操作，要么都发生，要么都不发生。原子性在MySQL中是由undo日志保证，undo日志保存了记录的操作历史，事务回滚时根据undo日志里记录的操作回滚。
一致性（Consistency）：事务在运行前后，对数据库的一致性约束不变。举例：现有完整性约束a+b=10，事务的发生改变了a的值，就一定要改变b的值，使事务结束后依然满足a+b=10的约束。一致性是由事务的原子性和隔离性来保证的，二者是手段，一致性是结果。
隔离性（Isolation）：多个事务同时执行时互不影响。MySQL中保证事务隔离性可以采用非锁机制的MVCC版本链机制，也可以加锁；
持久性（Durability）：事务一旦提交，它所做的操作会永久地保存在数据库上，即使数据库宕机也不会丢失。MySQL中通过redo日志保证事务的持久性，增删改操作同时在内存和redo日志中记录，事务提交的时候会将redo日志刷新到磁盘中，数据库宕机时可以通过redo日志恢复。

2.2 脏写、脏读、不可重复读和幻读是指什么？InnoDB中是如何避免它们的？
脏写：两个写事务，事务A先更新记录，事务B后更新记录，事务A再提交，事务B再提交，此时事务A发现UPDATE后的的数据不是事务A想更新的记录，而是事务B更新的记录。由于脏写的性质最为恶劣，所有事务隔离级别都不允许脏写情况的发生，InnoDB中通过加锁（独占锁）的方式避免脏写；
脏读：一个事务读到了另一个未提交事务修改过的记录叫脏读，InnoDB中通过MVCC机制避免脏读；
不可重复读：事务A根据某个搜索条件不断地查询，事务B不断地更新满足事务A中搜索条件的记录，导致事务A每次根据相同搜索条件查询出来的记录值不一样。InnoDB中通过MVCC机制避免不可重复读；
幻读：事务A根据某个搜索条件不断地查询，事务B不断地插入满足事务A中搜索条件的记录，导致事务A每次根据相同搜索条件都会多查询出来记录，多出来的记录就叫做幻影记录。InnoDB针对快照读通过MVCC机制避免幻读，针对当前读通过对next-key locks加锁避免幻读。

2.3 InnoDB支持哪几种事务隔离级别？
READ UNCOMMITTED：未提交读，脏读、不可重复读和幻读都会发生；
READ COMMITTED：可提交读，脏读不会发生，不可重复读和幻读还会发生；
REPEATABLE READ：可重复读，SQL标准规定脏读、不可重复读不会发生，幻读还会发生，InnoDB保证了脏读、不可重复读和幻读都不会发生；
SERIALIZABLE：可串行化，通过加独占锁的方式保证事务的串行执行，脏读、不可重复读和幻读都不会发生。

2.4 InnoDB是如何解决幻读的？
在RR事务隔离级别下，InnoDB解决幻读分为快照读和当前读两个场景，快照读是指采用MVCC机制不加锁的读，当前读是指加锁（共享锁或者独占锁）的查询语句，以及UPDATE和DELETE语句。
快照读：快照读时，会从版本链的头结点（最新记录）依次遍历版本链，将满足以下条件之一的记录返回：
- 版本链中的undo记录的trx_id小于或者等于当前快照读事务的id；
- 版本链中的undo记录的删除版本号为空或者删除版本号大于当前事务id。
当前读：在RR事务隔离级别下，InnoDB会给当前读的查询范围包括记录加上行锁中的next-key locks锁，避免在当前读时，其他事务在查询范围内插入新的幻影记录。

在SERIALIZABLE事务隔离级别下，InnoDB解决幻读是通过加表锁。事务在读操作时，先加表级别的共享锁，直到事务结束时才释放；事务在写操作时，先加表级别的排他锁，直到事务结束才释放。也就是说串行化这个事务隔离级别锁定了整张表来避免幻影记录插入。

2.5 InnoDB是如何解决脏读和不可重复读的？

首先介绍一下MVCC版本链的形成

InnoDB为了解决脏读、不可重复读和幻读的产生，提出了一种无锁的方案：MVCC机制。同一条记录由多个undo日志组成一个链表，这个链表就叫做MVCC版本链。链表的头节点是记录最新的版本，由roll_pointer连接不同的undo日志，查询时从头节点开始遍历MVCC版本链，如果版本链中的某个undo日志对应的版本对当前查询事务是可见的，就返回这个记录的这个版本。

然后介绍一下ReadView结构

ReadView结构是在查询时会生成的，目的就是判断MVCC版本链中的记录对当前查询事务是否可见。ReadView由以下4部分组成：

m_ids：表示生成ReadView时当前系统中活跃（未提交的）的事务id组成的列表；
min_trx_id：m_ids列表中的最小值；
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id（事务id是递增的）；
creator_trx_id：表示生成ReadView的当前事务的id。

如何根据某个读事务生成的ReadView快照，判断版本链上的某个版本对该查询事务是否可见呢？遵循以下步骤：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前读事务在访问它自己修改过的记录，所以该版本对当前事务可见；
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本对当前事务可见；
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，该版本对当前事务不可见；
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本对当前事务不可见；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本对当前事务可见。

最后介绍一下READ COMMITTED和REPEATABLE READ事务隔离级别下是如何解决脏读和不可重复读的

这两种事务隔离级别解决脏读和不可重复读的区别就在于生成ReadView结构的时机是不一样的。

READ COMMITTED：每次查询时都会生成一个ReadView，按照上面介绍的ReadView判断版本链中记录对当前事务是否可见的原则，可以避免脏读；
REPEATABE READ：只在事务第一次查询时生成一个ReadView，后面这个事务再查询的话就用第一次生成的这个ReadView，正因为这个ReadView相对于后面查询时已经”老了”，因此有些最新UPDATE并且提交了的事务对记录做的修改会对查询事务不可见，避免了不可重复读。

3、索引

3.1 什么是索引？为什么用索引？
索引是为了更加高效快速查询数据库而设计的针对数据库表的一种数据结构，通常在建表的时候通过index/key来指定表中的索引，一个索引可以包含表中的一列，也可以是一组列。
当对查询条件的列，或者ORDER BY、GROUP BY指定的列建立索引时，在正确的sql语句书写的前提（保证索引没失效）下，可以提高查询速率。

3.2 使用索引的优缺点？
优点：
当对查询条件的列，或者ORDER BY、GROUP BY指定的列建立索引时，在正确的sql语句书写的前提（保证索引没失效）下，可以提高查询速率。

缺点：

索引会占据额外的存储空间（相对于数据记录）；
对表进行增删改操作时，可能会伴随索引中记录的移动和页分裂，需要额外的时间进行动态维护。

3.3 索引的分类？
按照逻辑分类，索引可以分为：
主键索引：即对主键列创建的聚簇索引，一张表有且只能有一个主键索引；
唯一索引：唯一索引中只能包含一列，且列中的值不允许重复，声明唯一索引时要加上UNIQUE，一张表可以有多个唯一索引，比如身份证号、卡号都可以作为唯一索引；
普通索引：普通索引中可以包含多个列，且允许列中的值重复，允许NULL值插入，一张表可以有多个普通索引；
全文索引：声明全文索引是要加上FULLTEXT，全文索引可以让搜索关键词更加高效，平时用的少。

按照物理逻辑分类，索引可以分为：

聚簇索引：一般是表中的主键索引，如果表中没有指定主键，则会选择表中第一个不允许为NULL的唯一索引，如果还是没有，则采用InnoDB存储引擎为行格式的隐藏列row_id作为聚簇索引。一张表中只能有一个聚簇索引，聚簇索引的非叶子节点存储主键列，叶子节点存储完整的列信息，回表也是指查询完非聚簇索引后，再来查询一遍聚簇索引；
非聚簇索引：索引列是非主键列的其他列或者一组列，一张表中可以有多个非聚簇索引，非聚簇索引的非叶子节点存储索引列，叶子节点存储索引列及主键列。

3.4 普通索引和唯一索引的区别？
索引又可以根据索引列的值是否可以重复分为普通索引和唯一索引：
普通索引：建表时创建索引用INDEX或者KEY关键字指定，普通索引允许索引列中的数据包含重复值，普通索引的唯一任务就是加快查询速度，因此只为经常出现在查询条件（WHERE）和排序分组条件（ORDER BY GROUP BY）中的列创建普通索引。
唯一索引：建表时创建唯一索引用UNIQUE INDEX关键字指定，唯一索引不允许索引列中的数据重复，如果插入数据的唯一索引在表中已经存在，插入动作会报错。在实际业务中唯一索引的创建不是为了提高查询速度，而是为了避免数据出现重复。

多说一句：列的UNIQUE属性底层也是通过UNIQUE索引实现的。

3.5 InnoDB存储引擎为什么采用B+树而不是B树作为索引的数据结构？

先说一下B树和B+树的结构不同：
B树：

B树的非叶子节点和叶子节点都存储data值（data值可以理解为用户记录）；
B树的相邻叶子结点之间没有指针连接。

B+树：

非叶子节点只存页目录记录；
叶子节点只存放具体数据记录；
所有叶子节点组成一个双向链表，便于遍历数据。

再答为什么B树和B+树数据结构的差异导致索引选B+树？

B+树的非叶子节点仅存放页目录记录，不存储具体的数据，具体的数据在叶子节点中存储，就会使非叶子节点可以存储更多的页目录记录，使整个B+树呈矮胖形，降低B+树的高度，减少磁盘IO次数，提高查询效率；
B+树所有叶子节点之间形成一个双向链表的结构，遍历查询时效率要高；
B+树的所有记录数据都存储在叶子节点上，B+树的查询都要从根节点走到叶子节点，使每一条数据的查询效率相当。

3.6 为什么MongoDB的索引是B树而不是B+树？
B+树和B数的一个区别在于：B+树的叶子节点之间形成了双向链表，便于遍历查询，而B树没有。MySQL是关系型数据库，关系型数据库中连接查询使用比较多，连接查询中遍历场景很普遍，因此MySQL适用于B+树索引；MongoDB是非关系数据库，非关系型数据库中单一查询比较常见，B树由于非叶子节点也会存放数据，B树的单一查询整体上效率要更高，因此MongoDB的索引使用B树。

3.7 InnoDB存储引擎为什么采用B+树索引而不是哈希索引？
哈希索引底层就是一张哈希表，哈希表的key是主键值基于哈希函数计算得到的哈希码，哈希表中的排序顺序是根据哈希码而不是主键值排序的，因此哈希索引不能用于排序；
哈希索引仅支持等值比较查询，不支持范围查询；
如果哈希函数选取的不好，或者哈希索引中的k-v过多时，会导致哈希冲突严重（哈希索引中采用拉链法解决哈希索引）。

哈希索引也有使用场景，当等值查询的场景较多时，且哈希冲突不严重的情况下，哈希索引的查询效率还是比较高的。

3.8 MySQL中的聚簇索引和非聚簇索引是什么？

首先介绍一下InnoDB中B+树索引是如何形成的

B+树的叶子节点存放用户数据，然后会生成一个目录项记录这么个数据结构（就是后面的索引记录），目录项记录主要有2部分组成：主键值（或者索引列的值）以及页号，每一个目录项记录都指向下层的一个数据页，目录项记录中的key（主键值或者索引列的值）值是指向的数据页中的记录的最小key，目录项记录中的页号与其对应的数据页一致。多个目录项记录也存放在一个数据页里，存放目录项记录的数据页多了，也会由树的上一层形成的目录项记录指向这些存放目录项记录的数据页，一层一层往上套娃，最终形成了一棵B+树，我们说的目录项记录就是索引项，整个B+树是由索引项记录和用户记录组成的。

然后介绍一下聚簇索引

我们把具有以下特点的B+树索引称为聚簇索引：

使用记录主键值的大小进行记录和页的排序，这包括以下三个方面的含义：
- 页内的记录是按照主键从小到大的顺序排成一个单向链表；
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表；
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录，所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。另外，聚簇索引由于所有的用户记录都存储在了叶子节点（即B+树索引同时存储了索引和所有的用户记录），因此对聚簇索引来说，索引即数据，数据即索引。

最后介绍一下非聚簇索引

非聚簇索引根据索引列的数目还可以细分为二级索引和联合索引，二级索引的索引列只有一个，联合索引的索引列有多个。当我们查询条件不是主键，且又想用到索引时，非聚簇索引就起到了作用。以二级索引为例说明：

使用索引列的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照索引列的大小顺序排成一个单向链表；
- 各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表；
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引列大小顺序排成一个双向链表。
B+树的叶子节点存储的并不是完整的用户记录（即不是用户记录的所有列），而只是索引列+主键这两个列的值；
目录项记录中不再是主键 + 页号的搭配，而变成了索引列 + +主键列 + 页号的搭配。

3.9 聚簇索引和非聚簇索引的区别是什么？
一个表中只能有一个聚簇索引，但可以有多个非聚簇索引；
聚簇索引中键值的顺序与表中记录的顺序是一致的（因为表中记录按主键值顺序排列），非聚簇索引中索引列的顺序与表中记录的顺序不一致；
聚簇索引的叶子节点存放完整的用户数据的列，非聚簇索引中叶子节点仅存放索引列以及主键列，要查询完整的用户记录还需要根据主键值查询聚簇索引进行回表。

3.10 什么是回表？
回表是指在查询语句中，查询的字段没有被索引列完全覆盖，有些查询字段不在索引列中，此时会先到索引列对应的二级索引或者联合索引的B+树中查询，然后根据主键值再到聚簇索引的B+树中查询，将聚簇索引叶子节点保持的所用用户记录返回。到聚簇索引中再次查询的过程被称为回表。

3.11 什么是索引覆盖？
索引覆盖是指查询语句中，查询的字段都在索引列中被覆盖完全了，查询时只需要查询二级索引或者联合索引的B+树而不需要回表查询聚簇索引的B+树。

3.12 查询索引时的顺序IO和随机IO是什么？
顺序IO：由于二级索引或者联合索引对应的B+树中的记录首先会按照索引列顺序进行升序排序，所以二级索引查出来的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O；
随机IO：根据从联合索引对应的B+树获取到的记录的主键值可能并不相连，而在聚簇索引中记录是根据主键的顺序升序排列的，所以根据这些并不连续的主键值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也称为随机I/O。

总结：第一步查询联合索引使用的是顺序IO，第二部查询聚簇索引使用的是随机IO，显然顺序IO比随机IO的性能高很多，顺序IO仅需要遍历几个连续的数据页，而不像随机IO一样东找一个西找一个。

3.13 什么是索引下推？

索引下推全称Index Condition Pushdown，简称ICP，是MySQL 5.6版本后引入的新特性，是针对查询条件是二级索引的场景做的查询优化。具体点，在根据查询条件对二级索引进行查询时，不忙着将查询得到的二级索引记录进行回表查询，而是根据二级索引列的其他查询条件（查询条件是二级索引列但没用上二级索引的）进一步过滤检查，仅将符合后者查询条件的二级索引记录进行回表，这样做减少了回表的次数，减少了随机IO，提升了查询效率。使用到了索引下推的查询语句的执行计划中，Extra列是Using index condition。
“下推”也就是说把查询中与被使用索引有关的查询条件下推到存储引擎中判断，而不是返回到server层再判断，最终目的还是减少回表次数。
讲解的时候可以举下面的这个例子：

-- key1是二级索引
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

使用索引下推的两个条件：

select语句；
二级索引。
3.14 什么情况下不宜建立索引？

表中数据量比较小的时候不要建立索引（百量级的就不需要建立索引全表扫描性价比更高，十万量级的就需要考虑建立索引了）；
频繁更新的字段不适合建立索引（因为每次更新字段都要维护索引记录的排序，还会伴随而来的页分裂的数据移动都会带来性能损耗）；
不在搜索条件、分组条件和排序条件中的字段不需要建立索引；
基数较小的列不适合建立索引（全表扫描性价比更高，因为要考虑回表带来的性能损耗）；

3.15 什么时候建立的索引会失效？
对多个列建立非聚簇索引，搜索条件中第一个等值或者范围搜索条件中的列不是索引列中的最左边的列；
列值可能为NULL的列建立索引可能失效；
当查询条件为“非”的语义时索引不会生效，此时优化器更倾向于全量查询，“非”的语义是指查询条件里有例如：<>、NOT、in、not exists；
模糊查询时，模糊匹配条件通配的是字符串前缀，比如”%ob”，这样索引无法比较模糊匹配中的字符串大小，如果模糊匹配条件是”jo%”通配后面的字符是可以的；
对索引列使用函数或者其他表达式，比如upper(name)=’SUNYANG’, number / 2 > 4；
当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式：
```
select * from sunyang where id='123';
```
当优化器发现需要回表的数据列过多（超过总数据了的30%），会放弃使用二级索引 + 回表的方式，而选择全表扫描的方式。

3.16 创建索引有哪些原则？
只为用于搜索、排序或者分组的列创建索引；
为列的基数大的列创建索引；
索引列的类型尽量小；
视情况只对字符串值的前缀建立索引；
让索引在表达式中单独出现，不要放在函数、复杂的表达式中；
为了尽可能少地让聚簇索引发生页分裂和记录移位的情况，建议让主键拥有AUTO_INCREAMENT属性；
避免冗余和重复索引；
尽量使用覆盖索引进行查询，避免回表带来的性能消耗。

3.17 什么是最左前缀原则（最左匹配原则）？
这个是创建索引时，以及使用索引进行查询时要遵循的规则，具体故规则如下：
如果是多个列等值查询，查询条件用到的列必须依次是索引列的顺序，比如创建联合索引（a, b, c），想要索引生效，查询条件只能使用以下组合：a，ab，ac，abc；
如果是范围查询时，索引只能用到索引列中最左边的列的范围查询。

3.18 为什么对索引使用表达式或者函数计算，会使索引失效？
因为索引这棵B+树里存放的是索引字段的原始值，并不是经过表达式或者函数计算后的值，因此查询条件里只能使用最原始的索引列，不要搞其他花里胡哨的。

3.19 列值为null时，查询是否会用到索引？
可以。但是不建议列值为NULL，因为列值为NULL使优化器难以对查询语句进行优化，可以设置一个DEFAULT值。

3.20 为什么select * 不允许？
使用 * 会返回所有列的字段，有些字段并不需要，这样会造成磁盘I/O和网络带宽的浪费；
如果select 后面的列都在联合索引，或者是联合索引的一个子集时，此时就是索引覆盖的情况，查询时只会使用到联合索引，无需在回表；如果是 * 的话还需要回表查询聚簇索引，带来性能开销。

3.20 对于如下sql语句：where a=1 and b=1；where b=1；where b=1 order by time desc，表的索引该如何建立？
基于索引的最左匹配原则，想让上述的3条sql语句的查询都能使用到索引，需要为表建立如下2个索引:
```
index idx_a_b(a,b),
idnex idx_b_time(b,time)
```
4、锁

4.1 InnoDB中锁的类型有哪些？
首先可以分为共享锁和排他锁。
共享锁：也叫读锁，S锁，手动加S锁可以使用LOCK IN SHARE MODE的sql语句，共享锁页也分为表的S锁和记录行的S锁，以记录行的S锁为例，如果一个事务获取了某条记录的S锁，其他事务也可以获取该记录的S锁，打不能获取该记录的X锁；
独占锁：也叫写锁，X锁，手动加X锁可以使用FOR UPDATE的sql语句，独占锁也分为表的X锁和记录行的X锁，以记录行的X锁为例，如果一个事务获取了某条记录的X锁，其他事务不可以获取该记录的X锁，也不可以获取该记录的S锁。

按照锁的粒度的不同，又可以分为表锁和行锁。

表锁：是给整张表加锁的，表锁又分为表级别的S锁、X锁和表级别的IX锁和IS锁，IX和IS锁也叫意向锁，主要为了确保在加表锁之前表中没有行锁，避免通过遍历的方式去查询表中是否有上行锁：
- 表级别的S锁：如果一个事务获取了某张表的S锁，其他事务可以继续获取该表的S锁以及表内记录行的S锁，不可以获取该表的X锁以及表内记录的X锁；
- 表级别的X锁：如果一个事务获取了某张表的X锁，其他事务不可以获取该表的S锁和X锁，不可以获取表内记录的S锁和X锁；
- 表级别的IS锁：当事务准备为某条记录加上S锁（行锁）时，需要先在表级别上加一个IS锁；
- 表级别的IX锁：当事务准备为某条记录加上X锁（行锁）时，需要先在表级别上加一个IX锁。
行锁：行锁的粒度自然是每个记录行，InnoDB中行锁是给索引上的索引项记录加锁，行锁又分为以下几种：
- Record Locks：给每一条具体的索引加锁，也分为共享锁和独占锁；
- Gap Locks：又叫间隙锁，间隙锁的作用范围是某条索引项记录到它上一条索引项记录之间的范围，不包含当前索引项记录。
- Next-key Locks：可以看做是Record Locks和Gap Locks的二合一，锁的作用范围是当前索引项记录，以及当前索引项记录到它上一条索引项记录之间的范围，InnoDB正式通过Next-key Locks锁防止当前读中的幻读发生的；
- Insert Intention Locks：插入意向锁，事务在插入数据时，即使由于插入范围被其他事务锁住暂时阻塞住，也会在内存空间中生成一个插入意向锁，表明该事务想在某个间隙插入数据，锁的trx_id是该事务的id，is_waiting属性为true，当该事务获取到锁时，会将is_waiting属性改为false；
- 隐式锁：一个事务首先插入了一条记录时，会将该记录的trx_id值设置为当前事务的id，此时如果其他事务向获取该记录的X锁或者S锁时，会检查一下该记录的trx_id值对应的事务是否是当前活跃的事务（未提交的事务），如果是就会帮当前事务创建一个X锁，is_waiting属性为false，自己进入等待状态（就是为自己也创建一个锁结构，is_waiting属性是true）。
  5、SQL优化
  5.1 你知道有哪些sql优化方法？
  见我这篇文章：常见的MySQL语句优化
  5.2 如何优化单表的分页查询？
  见我这篇文章：单表分页查询优化
  6、高级特性（主从复制、读写分离、分库分表）
  （1）如果某个表有近千万行数据，增删改查比较慢，可以从哪些维度去优化？
  （2）百万级别的数据，如何删除？
  （3）MySQL主从复制原理以及流程？
  （4）分表后的ID怎么保证唯一性？
  参考
  100道MySQL数据库经典面试题解析（收藏版）
  mysql 常见面试题
   最全MySQL面试60题和答案
   最完整MySQL数据库面试题（2020最新版）
  索引失效的情况有哪些？索引何时会失效？（全面总结）
  mysql 如何建索引索引（面试题）？
  【原创】为什么Mongodb索引用B树，而Mysql用B+树?
  面试必问的 MySQL，你懂了吗？
  面试官：聊聊索引失效？失效的原因是什么？
  面试题：你有没有搞混查询缓存和Buffer Pool？谈谈看！

MySQL面试题

1、基本概念

1.1 MySQL架构的简介？

1.2 一条sql语句的执行过程？

1.3 候选键、主键、UNIQUE键、外键的概念分别是什么？

1.4 外连接、内连接的概念和区别是什么？

1.5 存储过程和存储函数的区别？

1.6 MySQL中in和exists的区别？

1.9 InnoDB和MyISAM的区别？

1.10 binlog和redo log的区别？

1.11 讲一下redo log的两阶段提交原理？/如何解决bin log和redo log的一致性问题？/MySQL恢复数据库为什么不能仅靠bin log恢复？

1.12 介绍一下查询缓存？

1.13 查询缓存和Buffer Pool的区别？

2、事务

2.1 事务的ACID四大特性有哪些？是如何保证的？

2.2 脏写、脏读、不可重复读和幻读是指什么？InnoDB中是如何避免它们的？

2.3 InnoDB支持哪几种事务隔离级别？

2.4 InnoDB是如何解决幻读的？

2.5 InnoDB是如何解决脏读和不可重复读的？

3、索引

3.1 什么是索引？为什么用索引？

3.2 使用索引的优缺点？

3.3 索引的分类？

3.4 普通索引和唯一索引的区别？

3.5 InnoDB存储引擎为什么采用B+树而不是B树作为索引的数据结构？

3.6 为什么MongoDB的索引是B树而不是B+树？

3.7 InnoDB存储引擎为什么采用B+树索引而不是哈希索引？

3.8 MySQL中的聚簇索引和非聚簇索引是什么？

3.9 聚簇索引和非聚簇索引的区别是什么？

3.10 什么是回表？

3.11 什么是索引覆盖？

3.12 查询索引时的顺序IO和随机IO是什么？

3.13 什么是索引下推？

3.14 什么情况下不宜建立索引？

3.15 什么时候建立的索引会失效？

3.16 创建索引有哪些原则？

3.17 什么是最左前缀原则（最左匹配原则）？

3.18 为什么对索引使用表达式或者函数计算，会使索引失效？

3.19 列值为null时，查询是否会用到索引？

3.20 为什么select * 不允许？

3.20 对于如下sql语句：where a=1 and b=1；where b=1；where b=1 order by time desc，表的索引该如何建立？

4、锁

4.1 InnoDB中锁的类型有哪些？

5、SQL优化

5.1 你知道有哪些sql优化方法？

5.2 如何优化单表的分页查询？

6、高级特性（主从复制、读写分离、分库分表）

参考