Mysql面试.md

icon	title	date	order	categories	tags	permalink
logos:mysql	Mysql 面试	2020-09-12 03:43:53 -0700	99	数据库 关系型数据库 Mysql	数据库 关系型数据库 Mysql 面试	/pages/cf957091/

Mysql 面试

基础

EXISTS 和 IN 有什么区别？

• EXISTS - 先对外表进行循环查询，再将查询结果放入 EXISTS 的子查询中进行条件比较，确定外层查询数据是否保留；
• IN - 先查询内表，将内表的查询结果作为条件，提供给外表查询语句进行比较；

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。

比如下面这样：

 SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
 SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN；如果两个表大小相当，则使用 EXISTS 和 IN 的区别不大。

UNION 和 UNION ALL 有什么区别？

UNION 和 UNION ALL 都是将两个结果集合并为一个，两个要联合的 SQL 语句字段个数必须一样，而且字段类型要“相容”（一致）。

• UNION 需要进行去重扫描，因此消息较低；而 UNION ALL 不会进行去重。
• UNION 会按照字段的顺序进行排序；而 UNION ALL 只是简单的将两个结果合并就返回。

JOIN 有哪些类型？

在 SELECT, UPDATE 和 DELETE 语句中，“连接”可以用于联合多表查询。连接使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且一般比子查询的效率更快。

JOIN 有以下类型：

• 内连接 - 内连接又称等值连接，用于获取两个表中字段匹配关系的记录，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
  • 笛卡尔积 - “笛卡尔积”也称为交叉连接（CROSS JOIN），它的作用就是可以把任意表进行连接，即使这两张表不相关。
  • 自连接（=） - “自连接（=）”可以看成内连接的一种，只是连接的表是自身而已。
  • 自然连接（NATURAL JOIN） - “自然连接”会自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。
• 外连接
  • 左连接（LEFT JOIN） - “左外连接”会获取左表所有记录，即使右表没有对应匹配的记录。左外连接使用 LEFT JOIN 关键字。
  • 右连接（RIGHT JOIN） - “右外连接”会获取右表所有记录，即使左表没有对应匹配的记录。右外连接使用 RIGHT JOIN 关键字。

CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

• CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
• CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。
• CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

金钱相关的数据用什么类型存储？

MySQL 中有 3 种类型可以表示浮点数，分别是 float、double 和 decimal。

float 和 double 为什么会丢失精度？

采用 float 和 double 类型会丢失精度。数据的精确度取决于分配给每种数据类型的存储长度。由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。

• 单精度类型 float 存储空间为 4 字节，即 32 位。
• 双精度类型 double 存储空间为 8 字节，即 64 位。

如果存储的数据转为二进制后，超过存储的位数，数据就被截断，因此存在丢失精度的可能。

【示例】丢失精度案例

-- 创建表
CREATE TABLE `test` (
  `value` float(10,2) DEFAULT NULL
);

mysql> insert into test value (131072.32);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----------+
| value     |
+-----------+
| 131072.31 |
+-----------+
1 row in set (0.02 sec)

说明：示例中，使用 float 类型，明明保留了两位小数。但是写入的数据却从 131072.32 变成了 131072.31 。

选择什么类型可以不丢失精度？

decimal 类型是 MySQL 官方唯一指定能精确存储的类型。因此，对于不允许丢失精度的场景（如金钱相关的业务），请务必使用 decimal 类型。

扩展阅读：MySQL 如何选择 float, double, decimal

如何存储 emoji 😃？

Mysql 中的默认字符集为 utf8，无法存储 emoji，如果要存储 emoji，必须使用 utf8mb4 字符集。

设置 utf8mb4 字符集方法如下：

ALTER TABLE test
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

什么是范式？什么是反范式？

什么是范式？

数据库规范化，又称“范式”，是数据库设计的指导理论。范式的目标是：使数据库结构更合理，消除存储异常，使数据冗余尽量小，增进数据的一致性。

根据约束程度从低到高有：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）等等。现代数据库设计，一般最多满足 3NF——范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库 IO 更繁忙。因此，在实际应用中，本来可以交由数据库处理的关系约束，很多都是在数据库使用程序中完成的。

什么是三大范式？

三大范式，从低到高，依次为：

• 1NF 要求属性具有原子性，不可再分解。
• 2NF 要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。
• 3NF 是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

现代数据库设计，一般最多满足 3NF——范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库 IO 更繁忙。因此，在实际应用中，本来可以交由数据库处理的关系约束，很多都是在数据库使用程序中完成的。

什么是反范式？

范式和反范式：

• 范式 - 消除冗余
• 反范式 - 适当冗余数据，以提高查询效率——空间换时间

架构

一条 SQL 查询语句是如何执行的？

1. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
3. 分析器
   • 词法分析：解析 SQL 关键字
   • 语法分析：生成一颗对应的语法解析树
4. 优化器
   • 根据语法树生成多种执行计划
   • 索引选择：根据策略选择最优方式
5. 执行器
   • 校验读写权限
   • 根据执行计划，调用存储引擎的 API 来执行查询
6. 存储引擎：存储数据，提供读写接口

一条 SQL 更新语句是如何执行的？

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

• redo log（重做日志）
  • InnoDB 存储引擎独有的日志（物理日志）
  • 采用循环写入
• bin log（归档日志）
  • Mysql Server 层通用日志（逻辑日志）
  • 采用追加写入

为了保证 redo log 和 bin log 的数据一致性，所以采用两阶段提交方式更新日志。

一条 SQL 查询语句的执行顺序是怎样的？

一条完整的 SELECT 语句内部的执行顺序是这样的：

1. FROM - 对 FROM 子句中的左表 <left_table> 和右表 <right_table> 执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1
2. ON - 对虚拟表 VT1 应用 ON 筛选，只有那些符合 <join_condition> 的行才被插入虚拟表 VT2 中
3. JOIN - 如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止
4. WHERE - 对虚拟表 VT3 应用 WHERE 过滤条件，只有符合 <where_condition> 的记录才被插入虚拟表 VT4 中
5. GROUP BY - 根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
6. CUBE|ROLLUP - 对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6
7. HAVING - 对虚拟表 VT6 应用 HAVING 过滤器，只有符合 <having_condition> 的记录才被插入虚拟表 VT7 中。
8. SELECT - 第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中
9. DISTINCT - 去除重复数据，产生虚拟表 VT9
10. ORDER BY - 将虚拟表 VT9 中的记录按照 <order_by_list> 进行排序操作，产生虚拟表 VT10。11）
11. LIMIT - 取出指定行的记录，产生虚拟表 VT11，并返回给查询用户

存储引擎

Mysql 有哪些常见存储引擎？

• InnoDB - Mysql 的默认存储引擎。支持事务、外键、表级锁和行级锁、自动崩溃恢复。索引采用 B+ 树聚簇索引。
• MyISAM - Mysql 5.1 版本前的默认存储引擎。特性丰富，但不支持事务、外键、行级锁、自动崩溃恢复。索引采用 B+ 树非聚簇索引。
• Memory - 适合快速访问数据，且数据不会被修改，重启丢失也没有关系。
• Archive - 适合存储归档数据。
• NDB - 用于 Mysql 集群场景。
• CSV - 可以将 CSV 文件作为 Mysql 的表来处理，但这种表不支持索引。

Mysql 中同一个数据库中的不同表可以设置不同的存储引擎。

InnoDB 和 MyISAM 有哪些差异？

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持四种事务隔离级别
锁	支持表级锁	支持表级锁、行级锁
索引	采用 B+ 树索引（非聚簇索引）	采用 B+ 树索引（聚簇索引）
表空间	小	大
关注点	性能	事务
计数器	维护了计数器，SELECT COUNT(*) 效率为 O(1)	没有维护计数器，需要全表扫描
故障恢复	不支持	支持（依赖于 redo log）

如何选择存储引擎？

• 大多数情况下，使用默认的 InnoDB 就够了。如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 就是比较靠前的选择了。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果存储归档数据，可以使用 ARCHIVE 引擎。

使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

日志

MySQL 有哪些类型的日志？

MySQL 日志文件有很多，包括 ：

• 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。
• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
• 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。
• 二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的 DDL 和 DML 语句（除了数据查询语句 select、show 等），以事件形式记录并保存在二进制文件中。

还有两个 InnoDB 存储引擎特有的日志文件：

• 重做日志（redo log）：重做日志至关重要，因为它们记录了对于 InnoDB 存储引擎的事务日志。
• 回滚日志（undo log）：回滚日志同样也是 InnoDB 引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB 引擎不仅会记录 redo log，还会生成对应的 undo log 日志；如果事务执行失败或调用了 rollback，导致事务需要回滚，就可以利用 undo log 中的信息将数据回滚到修改之前的样子。

bin log 和 redo log 有什么区别？

• bin log 会记录所有与数据库有关的日志记录，包括 InnoDB、MyISAM 等存储引擎的日志；而 redo log 只记 InnoDB 存储引擎的日志。
• 记录的内容不同，bin log 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo log 记录的是关于每个页（Page）的更改的物理情况。
• 写入的时间不同，bin log 仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有 redo ertry 被写入 redo log 中。
• 写入的方式也不相同，redo log 是循环写入和擦除，bin log 是追加写入，不会覆盖已经写的文件。

redo log 如何刷盘？

redo log 的写入不是直接落到磁盘，而是在内存中设置了一片称之为 redo log buffer 的连续内存空间，也就是 redo 日志缓冲区。

在如下的一些情况中，log buffer 的数据会刷入磁盘：

• log buffer 空间不足时

log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

• 事务提交时

在事务提交时，为了保证持久性，会把 log buffer 中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。

• 后台线程输入

有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。

• 正常关闭服务器时
• 触发 checkpoint 规则

重做日志缓存、重做日志文件都是以块（block）**的方式进行保存的，称之为**重做日志块（redo log block）,块的大小是固定的 512 字节。我们的 redo log 它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上checkpoint时，表示 redo log 日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的checkpoint 规则，就是 checkpoint 触发后，将 buffer 中日志页都刷到磁盘。

日志为什么要两阶段提交？

索引

什么是索引？为什么要使用索引？

“索引”是数据库为了提高查找效率的一种数据结构。

日常生活中，我们可以通过检索目录，来快速定位书本中的内容。索引和数据表，就好比目录和书，想要高效查询数据表，索引至关重要。在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，设置合理的索引是数据库查询性能优化的最有效手段。

索引的优点和缺点是什么？

✔️️️️️️️ 索引的优点：

• 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O。
• 支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
• 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

• 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
• 索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。
• 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

基于以上，可以归纳出索引的基本使用规则：

• 索引不是越多越好，不要为所有列都创建索引
• 要尽量避免冗余和重复索引
• 要考虑删除未使用的索引
• 尽量的扩展索引，不要新建索引
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引

何时适用索引？何时不适用索引？

✔️️️️ 什么情况适用索引？

• 字段的数值有唯一性的限制，如用户名。
• 频繁作为 WHERE 条件或 JOIN 条件的字段，尤其在数据表大的情况下。
• 频繁用于 GROUP BY 或 ORDER BY 的字段。将该字段作为索引，查询时就无需再排序了，因为 B+ 树本身就是按序存储的。
• DISTINCT 字段需要创建索引。

❌ 什么情况不适用索引？

• 频繁写操作（ INSERT/UPDATE/DELETE ），也就意味着需要更新索引。
• 很少作为 WHERE 条件或 JOIN 条件的字段，也就意味着索引会经常无法命中，没有意义，还增加空间开销。
• 非常小的表，对于非常小的表，大部分情况下简单的全表扫描更高效。
• 特大型的表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

索引如何分类？

索引可以从不同维度来分类：

• 数据结构
  • B+tree 索引
  • Hash 索引
  • Full-text 索引
• 物理存储
  • 聚簇索引（主键索引）
  • 二级索引（辅助索引）
• 字段特性
  • 主键索引（PRIMARY）
  • 唯一索引（UNIQUE）
  • 普通索引（INDEX）
  • 前缀索引
• 字段个数
  • 单列索引
  • 联合索引

索引有哪些常见数据结构？

在 Mysql 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的数据结构也不相同。下面是 Mysql 常用存储引擎对一些主要索引数据结构的支持：

索引数据结构/存储引擎	InnoDB 引擎	MyISAM 引擎	Memory 引擎
B+ 树索引	✔️️️️️️️	✔️️️️️️️	✔️️️️️️️
Hash 索引	❌	❌	✔️️️️️️️
Full Text 索引	✔️️️️️️️	✔️️️️️️️	❌

Mysql 索引的常见数据结构：

• 哈希索引
  • 因为索引数据结构紧凑，所以查询速度非常快。
  • 只支持等值比较查询 - 包括 =、IN()、<=>；不支持任何范围查询，如 WHERE price > 100。
  • 无法用于排序 - 因为哈希索引数据不是按照索引值顺序存储的。
  • 不支持部分索引匹配查找 - 因为哈希索引时使用索引列的全部内容来进行哈希计算的。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
  • 不能用索引中的值来避免读取行 - 因为哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响不大。
  • 哈希索引有可能出现哈希冲突
    • 出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。
    • 如果哈希冲突多的话，维护索引的代价会很高。
• B 树索引
  • 适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。
  • 所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
  • 所有的叶子节点由指针连接。

为什么 InnoDB 索引采用 B+ 树？

B+ 树 vs B 树

• B+ 树只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
• 另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+ 树 vs 二叉树

• 对于有 N 个叶子节点的 B+ 树，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
• 在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+ 树的高度依然维持在 13 层左右，也就是说一次数据查询操作只需要做 13 次的磁盘 I/O 操作就能查询到目标数据。
• 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+ 树高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

一言以蔽之，使用 B+ 树，而不是二叉树，是为了减少树的高度，也就是为了减少磁盘 I/O 次数。

B+ 树索引和 Hash 索引的差异

• B+ 树索引支持范围查询；Hash 索引不支持。
• B+ 树索引支持联合索引的最左匹配原则；Hash 索引不支持。
• B+ 树索引支持排序；Hash 索引不支持。
• B+ 树索引支持模糊查询；Hash 索引不支持。
• Hash 索引的等值查询比 B+ 树索引效率高。

综上，Hash 索引的应用场景很苛刻，不适用于绝大多数场景。

聚簇索引和非聚簇索引有什么区别？

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引又被称为**“聚簇索引（clustered index）”，其叶子节点存的是整行数据**。

• 聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。
• 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
• InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

非主键索引又被称为**“二级索引（secondary index）”，其叶子节点存的是主键的值**。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

• 如果语句是 select * from T where ID=500，即聚簇索引查询方式，则只需要搜索主键索引树；
• 如果语句是 select * from T where k=5，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

索引有哪些优化策略？

索引基本原则

• 索引不是越多越好，不要为所有列都创建索引。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
• 要尽量避免冗余和重复索引。
• 要考虑删除未使用的索引。
• 尽量的扩展索引，不要新建索引。
• 频繁作为 WHERE 过滤条件的列应该考虑添加索引。

覆盖索引

覆盖索引是指：索引上的信息足够满足查询请求，不需要回表查询数据。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左匹配原则

这里的最左前缀，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。“索引的选择性”是指不重复的索引值和记录总数的比值，选择性越高，查询效率越高。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。

前缀索引

“前缀索引”是指索引开始的部分字符。对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

前缀索引的优点是可以大大节约索引空间，从而提高索引效率。

前缀索引的缺点是会降低索引的区分度。此外，order by 无法使用前缀索引，无法把前缀索引用作覆盖索引。

使用索引来排序

Mysql 有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。

索引最好既满足排序，又用于查找行。这样，就可以通过命中覆盖索引直接将结果查出来，也就不再需要排序了。

这样整个查询语句的执行流程就变成了：

1. 从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

= 和 in 可以乱序

不需要考虑 =、IN 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

哪些情况下，索引会失效？

导致索引失效的情况有：

• 对索引使用左模糊匹配
• 对索引使用函数或表达式
• 对索引隐式类型转换
• 联合索引不遵循最左匹配原则
• 索引列判空 - 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效
• WHERE 子句中的 OR

普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引的查询性能相差微乎其微。

事务

什么是事务，什么是 ACID？

“事务”指的是满足 ACID 特性的一组操作。事务内的 SQL 语句，要么全执行成功，要么全执行失败。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID 是数据库事务正确执行的四个基本要素。

• 原子性（Atomicity）
  • 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
  • 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
• 一致性（Consistency）
  • 数据库在事务执行前后都保持一致性状态。
  • 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
• 隔离性（Isolation）
  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（Durability）
  • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  • 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。

事务存在哪些并发一致性问题？

事务中存在的并发一致性问题有：

• 丢失修改
• 脏读
• 不可重复读
• 幻读

“丢失修改”是指一个事务的更新操作被另外一个事务的更新操作替换。

如下图所示，T₁ 和 T₂ 两个事务对同一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。

如下图所示，T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 撤销了这次修改，那么 T₂ 读取的数据是脏数据。

“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。

如下图所示，T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

“幻读（phantom read）”是指一个事务内多次读取同一范围的数据，过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。

事务 T₁ 读取某个范围内的记录时，事务 T₂ 在该范围内插入了新的记录，T₁ 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

有哪些事务隔离级别，分别解决了什么问题？

为了解决以上提到的并发一致性问题，SQL 标准提出了四种“事务隔离级别”来应对这些问题。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

事务隔离级别从低到高分别是：

• “读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
• **“读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 读已提交解决了脏读的问题。
  • 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
• “可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可重复读解决了不可重复读问题。
  • 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
• “串行化（serializable ）” - 是指，强制事务串行执行，对于同一行记录，加读写锁，一旦出现锁冲突，必须等前面的事务释放锁。
  • 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
  • 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

事务隔离级别对并发一致性问题的解决情况：

隔离级别	丢失修改	脏读	不可重复读	幻读
读未提交	✔️️️	❌	❌	❌
读已提交	✔️️️	✔️️️	❌	❌
可重复读	✔️️️	✔️️️	✔️️️	❌
可串行化	✔️️️	✔️️️	✔️️️	✔️️️

各事务隔离级别是如何实现的？

Mysql 中的事务功能是在存储引擎层实现的，并非所有存储引擎都支持事务功能。InnoDB 是 Mysql 的首先事务存储引擎。

四种隔离级别具体是如何实现的呢？

以 InnoDB 的事务实现来说明：

• 对于“读未提交”隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于“串行化”隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于“读提交”和“可重复读”隔离级别的事务来说，它们都是通过 ReadView 来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。
  • “读提交”隔离级别是在“每个语句执行前”都会重新生成一个 ReadView
  • “可重复读”隔离级别是在“启动事务时”生成一个 ReadView，然后整个事务期间都在用这个 ReadView。

什么是 MVCC？

MVCC 是 Multi Version Concurrency Control 的缩写，即“多版本并发控制”。MVCC 的设计目标是提高数据库的并发性，采用非阻塞的方式去处理读/写并发冲突，可以将其看成一种乐观锁。

不仅是 Mysql，包括 Oracle、PostgreSQL 等其他关系型数据库都实现了各自的 MVCC，实现机制没有统一标准。MVCC 是 InnoDB 存储引擎实现事务隔离级别的一种具体方式。其主要用于实现读已提交和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC 的实现原理是什么？

MVCC 的实现原理，主要基于隐式字段、UndoLog、ReadView 来实现。

隐式字段

InnoDB 存储引擎中，数据表的每行记录，除了用户显示定义的字段以外，还有几个数据库隐式定义的字段：

• row_id - 隐藏的自增 ID。如果数据表没有指定主键，InnoDB 会自动基于 row_id 产生一个聚簇索引。
• trx_id - 最近修改的事务 ID。事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer - 回滚指针，指向这条记录的上一个版本。

UndoLog

MVCC 的多版本指的是多个版本的快照，快照存储在 UndoLog 中。该日志通过回滚指针 roll_pointer 把一个数据行的所有快照链接起来，构成一个版本链。

ReadView

ReadView 就是事务进行快照读时产生的读视图（快照）。

ReadView 有四个重要的字段：

• m_ids - 指的是在创建 ReadView 时，当前数据库中“活跃事务”的事务 ID 列表。注意：这是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id - 指的是在创建 ReadView 时，当前数据库中“活跃事务”中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id - 这个并不是 m_ids 的最大值，而是指创建 ReadView 时当前数据库中应该给下一个事务分配的 ID 值，也就是全局事务中最大的事务 ID 值 + 1；
• creator_trx_id - 指的是创建该 ReadView 的事务的事务 ID。

在创建 ReadView 后，我们可以将记录中的 trx_id 划分为三种情况：

• 已提交事务
• 已启动但未提交的事务
• 未启动的事务

ReadView 如何判断版本链中哪个版本可见？

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• trx_id == creator_trx_id - 表示 trx_id 版本记录由 ReadView 所代表的当前事务产生，当然可以访问。
• trx_id < min_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之前已提交的事务生成的，当前事务可以访问。
• trx_id >= max_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之后才启动的事务生成的，当前事务不可以访问。
• min_trx_id <= trx_id < max_trx_id - 需要判断 trx_id 是否在 m_ids 列表中
  • 如果 trx_id 在 m_ids 列表中，表示生成 trx_id 版本记录的事务依然活跃（未提交事务），当前事务不可以访问。
  • 如果 trx_id 不在 m_ids 列表中，表示生成 trx_id 版本记录的事务已提交，当前事务可以访问。

这种通过“版本链”来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

MVCC 实现了哪些隔离级别，如何实现的？

对于“读已提交”和“可重复读”隔离级别的事务来说，它们都是通过 MVCC 的 ReadView 机制来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。

MVCC 如何实现可重复读隔离级别

可重复读隔离级别只有在启动事务时才会创建 ReadView，然后整个事务期间都使用这个 ReadView。这样就保证了在事务期间读到的数据都是事务启动前的记录。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据，value 为 100；
• 事务 1 将 value 设为 200；
• 事务 2 读取数据，value 为 100；
• 事务 1 提交事务；
• 事务 2 读取数据，value 依旧为 100；

以上操作，如下图所示。T2 事务在事务过程中，是否可以看到 T1 事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出 min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见。

综上所述，在 T2 事务中，自始至终只能看到 trx_id = 100 的版本记录。

MVCC 如何实现读已提交隔离级别

读已提交隔离级别每次读取数据时都会创建一个 ReadView。这意味着，事务期间的多次读取同一条数据，前后读取的数据可能会出现不一致——因为，这期间可能有另外一个事务修改了该记录，并提交了事务。

举个例子，假设有两个事务依次执行以下操作：

• 初始，表中 id = 1 的 value 列值为 100。
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 将 value 设为 100；
• 事务 2 读取数据（创建 ReadView），value 为 0；
• 事务 1 提交事务；
• 事务 2 读取数据（创建 ReadView），value 为 100；

以上操作，如下图所示，T2 事务在事务过程中，是否可以看到其他事务的修改，可以根据 ReadView 中描述的规则去判断。

从图中不难看出：

• 对于 trx_id = 100 的版本记录，比对 T2 事务 ReadView ，trx_id < min_trx_id，因此在 T2 事务中的任意时刻都可见；
• 对于 trx_id = 101 的版本记录，比对 T2 事务 ReadView ，可以看出第二次查询时（T1 更新未提交），min_trx_id <= trx_id < max_trx_id ，且 trx_id 在 m_ids 中，因此 T2 事务中不可见；而第三次查询时（T1 更新已提交），trx_id < min_trx_id，因此在 T2 事务中可见；

综上所述，在 T2 事务中，当 T1 事务提交前，可读取到的是 trx_id = 100 的版本记录；当 T1 事务提交后，可读取到的是 trx_id = 101 的版本记录。

MVCC + Next-Key Lock 如何解决幻读

MySQL InnoDB 引擎的默认隔离级别虽然是“可重复读”，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

• 针对快照读（普通 SELECT 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（SELECT ... FOR UPDATE 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 SELECT ... FOR UPDATE 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

锁

Mysql 中有哪些锁？

为了解决并发一致性问题，Mysql 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

独享锁和共享锁

InnoDB 实现标准行级锁定，根据是否独享资源，可以把锁分为两类：

• 独享锁（Exclusive），简写为 X 锁，又称为“写锁”、“排它锁”。
  • 独享锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
  • 使用方式：SELECT ... FOR UPDATE;
• 共享锁（Shared），简写为 S 锁，又称为“读锁”。
  • 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
  • 使用方式：SELECT ... LOCK IN SHARE MODE;

为什么要引入读写锁机制？

实际上，读写锁是一种通用的锁机制，并非 Mysql 的专利。在很多软件领域，都存在读写锁机制。

因为读操作本身是线程安全的，而一般业务往往又是读多写少的情况。因此，如果对读操作进行互斥，是不必要的，并且会大大降低并发访问效率。正式为了应对这种问题，产生了读写锁机制。

读写锁的特点是：读读不互斥、读写互斥、写写互斥。简言之：只要存在写锁，其他事务就不能做任何操作。

注：InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

悲观锁和乐观锁

基于加锁方式分类，Mysql 可以分为悲观锁和乐观锁。

• 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
  • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
  • 实现方式：使用数据库中的锁机制。
• 乐观锁 - 假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。只在更新的时候，判断一下在此期间是否有其他线程更新该数据。
  • 实现方式：更新数据时，先使用版本号机制或 CAS 算法检查数据是否被修改。

为什么要引入乐观锁？

乐观锁也是一种通用的锁机制，在很多软件领域，都存在乐观锁机制。

锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。那么，为了提高并发度，能不能尽量不加锁呢？

乐观锁，顾名思义，就是假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。虽然不加锁，但不意味着什么都不做，而是在更新的时候，判断一下在此期间是否有其他线程更新该数据。乐观锁最常见的实现方式，是使用版本号机制或 CAS 算法（Compare And Swap）去实现。

乐观锁的优点是：减少锁竞争，提高并发度。

乐观锁的缺点是：

• 存在 ABA 问题。所谓的 ABA 问题是指在并发编程中，如果一个变量初次读取的时候是 A 值，它的值被改成了 B，然后又其他线程把 B 值改成了 A，而另一个早期线程在对比值时会误以为此值没有发生改变，但其实已经发生变化了
• 如果乐观锁所检查的数据存在大量锁竞争，会由于不断循环重试，产生大量的 CPU 开销。

全局锁、表级锁、行级锁

前文提到了，锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。在不得不加锁的情况下，显然，加锁的范围越小，锁竞争的发生频率就越小，系统的并发程度就越高。但是，加锁也需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销，锁粒度越小，系统的锁操作开销就越大。因此，在选择锁粒度时，也需要在锁开销和并发程度之间做一个权衡。

根据加锁的范围，MySQL 的锁大致可以分为：

• 全局锁 - “全局锁”会锁定整个数据库。
• 表级锁（table lock） - “表级锁”锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。表级锁有：
  • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。
  • 元数据锁（MDL） - MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 增删改查，加读锁
    • 结构变更，加写锁
  • 意向锁（Intention Lock）
  • 自增锁（AUTO-INC）
• 行级锁（row lock） - “行级锁”锁定指定的行记录。这样其它线程还是可以对同一个表中的其它行记录进行操作。行级锁有：
  • 记录锁（Record Lock）
  • 间隙锁（Gap Lock）
  • 临键锁（Next-Key Lock）
  • 插入意向锁

以上各种加锁粒度，在不同存储引擎中的支持情况并不相同。如：InnoDB 支持全局锁、表级锁、行级锁；而 MyISAM 只支持全局锁、表级锁。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

死锁是如何产生的？

“死锁”是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生死锁的场景：

• 当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
• 多个事务同时锁定同一个资源时，也会产生死锁。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。由此可知，要想避免死锁，就要从这几个必要条件上去着手：

• 更新表时，尽量使用主键更新，减少冲突；
• 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
• 设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
• 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
• 在允许幻读和不可重复读的情况下，尽量使用读已提交事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
• 还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

如何解决死锁？

当出现死锁以后，有两种策略：

• 设置事务等待锁的超时时间。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我直接把这个时间设置成一个很小的值，比如 1s，也是不可取的。当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。因此，死锁检测可能会耗费大量的 CPU。

优化

如何发现慢 SQL？

慢 SQL 的监控主要通过两个途径：

• 慢查询日志：开启 MySQL 的慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
• 服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢 SQL 进行监控和告警。

什么是执行计划？

如何分析执行计划？

如何优化 SQL

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL 查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

• 延迟关联

先通过 where 条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

例如：

select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
 where a.id = b.id

• 书签方式

书签方式就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit

例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢 SQL 的利器。

利用覆盖索引

InnoDB 使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

低版本避免使用 or 查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 <> 操作符

SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成 or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做 order by 和 group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN 优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。

比如 left join，左表就是驱动表，A 表小于 B 表，建立连接的次数就少，查询速度就被加快了。

 select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用 JOIN 关联太多的表

《阿里巴巴 Java 开发手册》规定不要 join 超过三张表，第一 join 太多降低查询的速度，第二 join 的 buffer 会占用更多的内存。

如果不可避免要 join 多张表，可以考虑使用数据异构的方式异构到 ES 中查询。

排序优化

利用索引扫描做排序

MySQL 有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机 IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION 优化

条件下推

MySQL 处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在 union 查询中都会失效，因为它无法利用索引

最好手工将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用 union all，如果不加 all 关键字，MySQL 会给临时表加上 distinct 选项，这会导致对整个临时表做唯一性检查，代价很高。

哪种 COUNT 性能最好？

先说结论：按照效率排序的话，COUNT(字段) < COUNT(主键 id) < COUNT(1) ≈ COUNT(*)。推荐采用 COUNT(*) 。

• 对于 COUNT(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

• 对于 COUNT(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

• 单看这两个用法的差别的话，你能对比出来，COUNT(1) 执行得要比 COUNT(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

• 对于 COUNT(字段) 来说：

  • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
  • 也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是 COUNT(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。COUNT(*) 肯定不是 null，按行累加。

不同的 MySQL 引擎中，COUNT(*) 有不同的实现方式：

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高；
• 而 InnoDB 引擎就麻烦了，它执行 COUNT(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也维护一个计数器？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 COUNT(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

• MyISAM 表虽然 COUNT(*) 很快，但是不支持事务；
• show table status 命令虽然返回很快，但是不准确；
• InnoDB 表直接 COUNT(*) 会遍历全表，虽然结果准确，但会导致性能问题。

如何优化查询计数？

可以使用 Redis 保存计数，但存在丢失更新一集数据不一致问题。

可以使用数据库其他表保存计数，但要用事务进行控制，增/删数据时，同步改变计数。

参考资料

• 《高性能 MySQL》
• MySQL 实战 45 讲
• 图解 MySQL 介绍