八股文之MySQL篇

前言

数据库中我们开发生产中是必需的，而MySQL是大多数公司的首选关系型数据库，这一篇开始介绍MySQL相关的面试题，并且给出相应的回答。

DQL（数据查询语言）的执行顺序

1. FROM
2. WHERE
3. GROUP BY (无法用到SELECT中的别名)
4. HAVING
5. SELECT
6. ORDER BY (可以用到SELECT中的别名)
7. LIMIT

DML（数据修改语言）

1. INSERT INTO
2. UPDATE，SET
3. DELETE FROM

DDL（数据定义语言）

1. CREATE TABLE / INDEX / DATABASE
2. DROP TABLE / INDEX / DATABASE
3. ALERT TABLE
4. RENAME TABLE / DATABASE

一条 SQL 是如何在 MySQL 中运行的 / 执行流程

1. 语法解析
2. 语义分析
3. 查询优化，根据SQL的复杂性，表索引等因素来生成一个执行计划
4. 生成执行计划
5. 执行查询
6. 返回结果

MySQL 中的函数

常见多行函数：

1. IF：相当于Java中的 IF ELSE
2. CASE：相当于Java中的 IF ELSE IF … ELSE
3. IFNULL：不为 NULL 返回第一个值，为 NULL 返回第二个值
4. CONCAT：字符串连接函数
5. IN：值得注意的是，IN 函数的参数个数是有限制的，大概在一百万左右
6. DATE_FORMAT：日期格式化函数，注意日期格式是: %Y年 %m月 %d日 %H时 %i分 %s秒
7. SUBSTRING：截取字符串

单行 / 分组 / 聚集函数：

1. MAX
2. MIN
3. COUNT
4. SUM
5. AVG

如何定位慢查询

• 慢查询顾名思义，就是一条SQL执行的时间很长，所以它慢嘛，在面试中，我们应该结合相关的场景，讲出该场景中所涉及的接口，它在测试的时候响应的就很慢，后续我们排查到的原因就是慢查询
• 还可以讲在系统中当时采用了运维工具（Skywalking），这个工具可以检测出是哪个接口，因为它呈现的方式非常直观，以图表的方式给出，一眼就可以看出哪些是耗时的接口，再去排查SQL的问题
• 最后一个就是MySQL自带慢查询功能，MySQL在设计的时候就考虑到了这一点，非常的哇塞，我们开启这个功能也很简单，在配置文件my.ini（如果安装的时候不做特殊处理，配置文件的位置是固定的，直接在Linux中打开配置文件）中开启配置选项：slow-query-log=1和long_query_time=2即可，前者是开启慢查询日志记录，后者是慢查询时间的阈值，超过这个时间的SQL，会被记录到慢查询日志文件中，后续进行排查即可，但一般在生产环境中不会开启该功能，因为会消耗一定的性能

如何优化或分析慢查询

采用MySQL自带的分析命令：explain 或 desc（两者功能相同）

• 通过查看key和key_len字段检查是否命中了索引（索引本身存在是否有失效的情况）
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引（索引树扫描） index 或全盘扫描 all
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
  

什么是索引

一种用于帮助数据库提升查询效率的有序的数据结构。

索引就是帮助MySQL高效获取数据的数据结构，这些数据结构以某种方式引用数据，在查找数据的时候不是全表扫一遍，降低数据库的IO成本，而是在这些数据结构中进行查找，能够快速的获取数据，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。（和面试官讲的时候举个例子最好了）

在 MySQL 中可以适用 hash 索引吗

我们是无法直接使用 hash 结构的索引，但是 mysql 内部执行查询优化时会自动决定是否使用 hash 索引来提升效率。

索引的底层数据结构 / 原理

hash: 就是类似于 Java 中 HashMap 数据结构的一种结构，就是 hash 表的概念。是基于索引值，进行 hash 运算，运算后计算该数据在 hash 表的存储位置。优势：直接基于索引查询数据时，在 hash 碰撞不高时查询效率较高。缺点：存在 hash 碰撞问题、无法支持范围查询、无法支持排序操作。

MySQL的InnoDB数据存储引擎采用的是B+树来存储索引

1. 页存储：16 KB
2. 非叶子节点存储索引值
3. 叶子结点存储行数据
4. 叶子结点间相互存在引用，且是从左到右从小到大有序存储的双向链表
5. 利用非叶子节点存储索引值这一特点，保证一页数据可以存储更多的索引数据，使得树的高度在3层左右就能存储非常大量级的数据

B 树和 B+ 树的区别（为什么用 B+ 树不用 B 树）

B树的所有节点都会存储key和value，但是B+树只有叶子节点会存储key和value，其他节点只会存储key，所以，在同等数据量下，B+树在一定的树高下，存储的key比B树会多不少，B树为了存储这些key就会使得树更高，导致IO操作变多，当然B树可以选择优化的方案也有，比如将热点数据放在靠近根节点的位置，能够提升热点数据的查询效率，B树主要用于文件系统和部分数据库索引（mongoDB），B+树不仅有上述优点，它的叶子节点之间形成了双向链表，能够应对区间查询和扫库、表（便于遍历数据），B+树的节点大小是一页（正好一个IO可以读完），一页的大小是16KB，能够存储的key数量：16 * 1024 / (6 + 4)，假设一行数据大小是1K，一页能够存储16行数据，只需要知道这么多信息即可推出B+树能够存储多少条索引记录

聚簇索引和非聚簇索引是什么

第一点，要明白的是，聚簇索引也叫聚集索引，非聚簇索引也叫二级索引，面试的时候别分不清，这是致命的。

聚簇索引（聚集索引）：数据和索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索引）：数据和索引分开存储，B+树的叶子节点保存了对应的主键，可以有多个

聚集索引选举规则（一定存在）：

1. 如果存在主键，那么主键索引就是聚集索引
2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
3. 如果表中没有主键，或者没有合适的唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引

什么是回表查询

我执行一条SQL，它是通过二级索引找到对应的主键值，再到聚簇索引中去查找数据项（整行数据），这个过程就是回表，很简单的。

什么是索引覆盖

覆盖索引指的是查询的时候使用了索引，需要查询的列，必须在索引中全部能够找到

• 使用id查询，直接走聚集索引，一次索引扫描，直接返回数据，性能高
• 如果返回的列中没有创建索引，有可能会触发回表查询，所以知道为什么讲要避免使用select *了吧，当然这只是原因之一哈

MySQL 超大分页怎么处理

超大分页查询：在一个数据很多的表中，进行limit分页查询，其中起始页很大，返回的数据行数很小，需要对数据进行排序，效率很低
解决方案：索引覆盖的子查询（分页查主键id）+ 关联查询
解释：其中子查询中用到覆盖查询，所以第一次就可以查出来，假设查出来的是主键的中间表，然后通过主键id和要查询的表做一个关联查询，就能够很大程度上的优化这个问题

索引创建的原则

• 主键索引和唯一索引，这个很直观，因为通过一个key对应一行数据，这是最高效的
• 根据业务创建的索引，也称为复合索引，业务规则如下：
  

什么情况下索引会失效

结合之前讲过的explain或desc命令来分析查询语句，主要查看key和key_len这两个字段就可以判定出索引有没有失效。

1. 违反最左前缀法则：要遵守最左前缀法则。指的是查询从（联合）索引的最左前列开始，不得跳过索引中的列。匹配该法则，则会走索引，否则索引失效，需要注意的是索引不是全部失效，只有匹配的最左前缀没有失效，其余均失效
2. 范围查询的列，该列右边的索引均失效（如果用了）
3. 在索引列上进行运算操作，索引失效
4. 字符串不加单引号，会造成索引失效（例子：本来是字符串类型的，但是没加单引号，MySQL的查询优化器会进行类型转换，造成索引失效）
5. 以%开头的模糊查询会导致索引失效，如果是尾部模糊匹配，索引不会失效

谈谈SQL优化的经验

从以下五个方面去展开：

1. 表的设计优化、字段的数据类型如何选择，例如：tinyint、int、bigint，char、varchar、text（参考阿里巴巴开发手册）
2. 索引优化，索引创建的原则（避免索引失效的情况和遵守索引创建的原则）
3. SQL语句优化，例如：避免索引失效，避免使用select *，优先使用union all而不是union，小表驱动大表（外连接要注意，内连接会做优化，目的都是为了减少连接次数）
4. 设置主从复制和读写分离的架构，不让数据的写入来影响读操作，缓解服务器压力
5. 分库分表（在数据量特别大的时候需要考虑，下面会提到）

什么是事务

事务是由多个 SQL 语句组成的一个操作单元，这个事务单元，要么都执行成功，要么都执行失败，其实这就说明了事务的一些特性，原子性和一致性的特性。

事务的特性是什么（面试必问）

首先复习下什么是事务，事务其实就是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为
一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。四大特性如下：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）：事务执行后，对数据产生的变动要么与事务操作之前的一致（事务失败），要么就是事务中的所有操作都执行成功，达成业务预期的一致。
• 隔离性（Isolation）：在多个事务并发执行时，事务与事务之间的隔离性，不会去影响对方
• 持久性（Durability）：事务执行后需要将数据持久化的存储

隔离级别

1. 读未提交：一个事务可以读到另一个事务中未提交的数据，什么问题都没解决
2. 读已提交：一个事务可以读到另一个事务中已提交的数据，解决了脏读问题
3. 可重复读：解决读已提交中不可重复读的问题
4. 串行化：将并行执行的事物变成串行执行，从而杜绝所有因并发执行导致的问题

并发事务带来的常见问题

1. 脏读：一个事务读到另外一个事务还没有提交的数据
2. 不可重复读：侧重在数据不同，在一个事务中读取到另一个事务提交的修改操作，导致前后读取数据的内容不一致，与幻读的主要区别是体现在 UPDATE 语句上
3. 幻读：侧重在数量不同，在一个事务中，一开始读到5条数据，之后再次读取时读到4条或者6条，此时产生幻读，与不可重复读主要的区别是，幻读主要产生的SQL语句为 INSERT / DELETE 操作
4. 第一类更新丢失（了解即可）：A 事务撤销，撤销回去的结果将 B 事务已经提交的数据覆盖，现代数据库不再存在该问题，因此可以不用考虑
5. 第二类更新丢失：两个事务同时执行，读取到相同数据后进行修改，事务都提交后，出现后提交事务覆盖前面提交事务的情况。并发量高用悲观锁解决，并发量低用乐观锁解决。

如何解决并发事务带来的问题

×号代表可以解决这类问题，√号代表解决不了这类问题

undo log 和 redo log 的区别

redo log：记录数据页的物理变化，服务宕机可用来同步数据，顺序读写能够保证磁盘性能
undo log：记录的是逻辑日志，当事务回滚时，通过逆操作（insert和delete）恢复原来的数据
redo log保证了事务的持久性，undo log 保证了事务的原子性和一致性

事务中的隔离性是如何保证的

锁：排他锁（如一个事务获取了某个数据行的排他锁，其他事务就不能获取该行的其他锁）、共享锁、记录锁（RR、RC隔离级别支持）、间隙锁（RR隔离级别支持）、next-key Lock（记录锁 + 间隙锁，RR隔离级别支持）
MVCC：Multi-Version Concurrency Control，多版本并发控制。指的是维护一个数据的多个版本，使得读写操作没有冲突，在不加锁的情况下，实现了并发事务（隔离性）。以下是MVCC的三个要点：

MySQL 中的幻读问题（RR 隔离级别）

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读（注意在当前隔离方案中是避免而不是一定不会发生）的方案：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
• 针对当前读（select … for update、update、insert、delete语句，后者会被自动加上排他锁），是通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读。
• 来源：MySQL幻读问题解析

记住，MySQL在当前读的情况下才会有幻读情况。

MySQL 的可重复读（RR 隔离级别）是怎么实现的

可重复读（repeatable read）定义：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
MVCC实现了读已提交和可重复读隔离级别，其核心就是undo log版本链 + read view，“MV”就是通过undo log 来保存数据的历史版本，实现多版本的管理，“CC”是通过read-view来实现管理，通过read-view原则来决定数据是否回显。 同时针对不同的隔离级别，read-view的生成策略不同，实现了不同的隔离级别。

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

• RC隔离级别时，read-view是每次执行select语句时都会被生成
• RR隔离级别时，read-view是在第一次执行select语句时生成一个，同一个事务中后面的所有select 语句都复用这个read-view

MySQL 主从同步原理

主从复制核心就是二进制日志（bin log），bin log 记录了所有的数据库定义语句（DDL）和数据操作语句（DML），但不包括数据查询（select、show）语句，分成三步：

1. 主库在事务提交时，会把数据变更记录在bin log中
2. 从库读取主库的bin log，写入到从库的中继日志relay log中
3. 从库重做中继日志中的事件，即同步数据中

分库分表

首先，分库分表不是一个简单活，当你必须要做的时候，才能去做，否则没有必要，那什么时候去做呢？现在你的项目数据量上来了，表的数据到了千万级别了，你想了想，现在网站访问速度这么慢，我来琢磨怎么优化，但这个时候优化已经起不到任何作用了，包括读写分离、查询索引等，还有磁盘IO、网络IO，CPU的瓶颈（联合查询、连接量巨大等），这个时候就要去做分库分表，因为它就是用来解决海量数据存储的。