MySQL面试篇（详细版）

优化

1. 如何定位慢查询？

在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能（默认是关闭），并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

2. SQL执行计划(分析SQL语句的执行快慢)

如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain或者describe来去查看这条sql的执行情况，首先在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况（参考索引的聚集索引和非聚集索引），如果出现了，可以尝试添加索引或修改返回字段来修复。

3. 索引（3.1 了解过索引吗？）

索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

3.2 存储索引及数据结构 (索引底层数据结构了解过吗？)

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：第一阶数更多，路径更短，第二个磁盘读写代价 B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表。

3.3 B树与B+树的区别

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。
第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

3.4 聚簇和非聚簇索引

聚簇索引

聚集索引就是聚簇索引，主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引，如果没有主键则是唯一键（unique），没有唯一键，InnoDB自动生成一个隐式rowid作为聚集索引。

非聚簇索引（二级索引）

二级索引也是非聚簇索引，主要是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇引。

回表查询

其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

注意：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引。

3.5 覆盖索引和超大分页优化问题

覆盖索引

覆盖索引是指select查询语句使用了索引，返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。（这个想想B+树查询的原理更容易理解）。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段。

利用覆盖索引解决超大分页查询问题

原因

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

思路

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了。因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

3.6 索引创建原则

• 数据量大，且查询比较频繁的表（基本）。
  比如十万条数据，经常查询。
• 常常作为查询条件，排序，分组的字段。
• 尽量使用联合索引。
  我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
• 要控制索引数量，字段内容长则使用前缀。
  如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

3.7 索引失效场景

• 违反最左前缀法则。
  假设说有字段ID name status address ID为聚集索引，后三者为联合索引，在查询的时候（都是select *），条件必须是 name 开头，以及不允许跳过name或者status，直接用address，可以只是name或者name status。
• 范围查询右边的列，不能使用索引。
  前提同上，如果status是范围查询那么SQL语句：
  select * from table_name where name='小米科技' and status>'1' and address='北京'
  address索引字段是无效的。
• 不要在索引列上进行运算操作，索引将失败。
  前提同上，如果name使用函数substring：
  select * from table_name where substring（name,3,2）='科技'
  索引字段name也失效。
• 字符串不加单引号，造成索引失效。（类型转换）
  如SQL语句（status是String类型）
  select * from table_name where name='小米科技' and status='1' 和
  select * from table_name where name='小米科技' and status=1
  前者status的1加了单引号，后者没加，造成类型转换，导致后者status索引失效。
• 以%开头的Like模糊查询，索引失效。
  select * from table_name where name like '%小米%' 与
  select * from table_name where name like '%小米' 与
  select * from table_name where name like '小米%'
  其中前两个都是%在前，造成name字段索引无效，只有第三个有效。

4. SQL优化经验（参考java开发手册（黄山版））

4.1 表的设计优化，数据类型的选择

1. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。
2. 如果存储的字符串长度几乎相等，使用 char 定长字符串类型（学号，手机号）。
3. 在数据库中不能使用物理删除操作，要使用逻辑删除。

4.2 索引优化

索引优化，参考索引创建原则（3.6）。

4.3 SQL语句优化

1. 避免索引失效（参考索引失效场景3.7）
2. 避免使用select *
3. 不要使用 count(列名) 或 count(常量) 来替代 count( * )，count( * ) 是SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
4. 使用 ISNULL() 来判断是否为 NULL 值。
   说明：NULL 与任何值的直接比较都为 NULL。
5. 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
6. 不得使用外键与级联，一切外键概念必须在应用层解决。
7. join的优化，能用innerjoin就不用left join和right join，本质都是要以小表作为驱动，内连接会对两个表进行优化，优先把小表放外面，大表放里面。

4.4主从复制，读写分离

数据库的使用场景读的操作比较多，为了避免写的操作影响性能，可以使用读写分离的架构。

4.5分库分表

单表数据量达到1000w或者内存超过20G的时候可以考虑(详细可见第7点)。

其他面试题

5. 事务相关（什么是事务？）

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功要么同时失败。

5.1 事务特性（介绍一下事务的特性。）

ACID，分别指的是：原子性、一致性、隔离性、持久性；
我举个例子（一一对应）：
A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要
么都成功，要么都失败。
在转账的过程中，数据要一致，A扣除了500，B必须增加500。
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰。
在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落
盘操作）。

5.2 隔离级别（并发事务带来哪些问题以及如何处理？）

事务带来的问题：

第一是脏读:

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

第二是不可重复读：

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

第三是幻读（Phantom read）：

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

怎么解决这些问题呢？MySQL的默认隔离级别是？

MySQL支持四种隔离级别，分别有：
第一个是未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。
第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。
第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。
第四个是串行化（serializable）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。
所以，我们一般使用的都是mysql默认的隔离级别:可重复读

5.3 undo log与redo log的区别？

首先二者都是日志文件，接下来我们先引入两个概念：缓冲池和数据页

缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在crud时，先操作缓冲池中的数据（缓冲池没有数据，则从磁盘中加载并且缓存），以一定的频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页默认大小为16KB。存储的是行数据。

redo log（重做日志）：记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
由两部分组成：重做日志缓存（redo log buffer）以及重做日志文件（redo log file），前者在内存中，后者在磁盘中。
使用场景：假设数据库提交update和delete操作到缓冲池时，缓冲池同步数据到磁盘时，数据库宕机，导致缓冲池丢失数据（update已同步，delete没来得及同步）。

重做日志如何工作呢？
当事务提交后会把所有修改信息存到redo log buffer中，并且同步到redo log file，当发生故障时，根据文件进行数据恢复。

undo log
回滚日志：用于记录数据被修改前的信息，作用有两个：提供回滚和MVCC（多版本并发控制）。与redo log 不一样的是它是逻辑日志。主要用来实现事务的原子性和一致性。
比如：当delete操作一条记录后，undo log 会记录一条对应的insert记录，反之亦然。用于回滚。
MVCC参考下一个面试题。

5.4 MVCC（1.事务的隔离性如何保证呢？2.什么是MVCC？）

事务的隔离性是由锁和mvcc实现的。

锁（排他锁）：当一个事务获取了一个数据行的排他锁，其他事务就无法获取该数据行的其他所锁。
MVCC（Multi-Version Concurrency Control）：是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图。

隐藏字段是指：

在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。

undo log：

主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。

readView：

解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc(read committed)隔离级别，每一次执行快照读时生成ReadView，如果是rr(repeatable read)隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用。

6. 主从同步原理

MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：
第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志Relay Log 。
第三：从库根据中继日志中的事件，同步自己的数据。

7. 分库分表（根据自己的具体项目来答）

• 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发问题。
• 水平分表，解决单表存储和性能问题。
• 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数。（用户，订单，商品）
• 垂直分表，冷热数据分离，多表互不影响。（比如商品基本信息与详细描述分离）
  注意：水平分表分库需要使用数据库中间件，比如MyCat 来解决跨界点等相关问题。