MySQL 优化

发表于 分类于
系统梳理 MySQL 优化路径，从慢查询与执行计划、索引与表结构设计，到日志机制、分库分表与参数配置，并涵盖事务控制与 MyISAM/InnoDB 的锁机制。

📌 素材来源：本文部分图示源自 imooc（慕课网）《扛得住的 MySQL 数据库架构》课程及简书旧文，图片版权归原课程方/作者所有，仅作个人学习笔记整理之用。

MySQL 优化很容易变成一堆零散的技巧，这篇笔记按“投入产出比”把它们串起来：先判断瓶颈在哪、优先动哪一层，再依次展开 SQL 与执行计划、索引、表结构、体系结构与日志、分库分表，最后才是参数配置。

事务控制语句与锁的完整分类另有专文，本文只涉及与性能直接相关的那部分。

一、从哪里开始优化

优化的优先级

按收益从高到低排列，顺序几乎不会变：

  1. 数据库结构设计和 SQL 语句
  2. 数据库存储引擎的选择和参数配置
  3. 系统选择及优化
  4. 硬件升级

也就是说，先改 SQL 和表结构，再动参数，最后才考虑加机器。反过来做，钱花了问题还在。

影响查询速度的四个因素

  1. SQL 查询本身的效率
  2. 磁盘 IO
  3. 网卡流量
  4. 服务器硬件

影响性能的几个方面

  1. 服务器硬件
  2. 服务器系统（系统参数优化）
  3. 存储引擎：MyISAM 不支持事务、表级锁；InnoDB 支持事务与行级锁，完整支持 ACID
  4. 数据库参数配置
  5. 数据库结构设计和 SQL 语句（重点优化对象）

常见风险点

先约定两个衡量指标：

QPS：Queries Per Second，每秒查询率，衡量一台服务器每秒能响应的查询次数。

TPS：Transactions Per Second，事务数/秒。客户机发送请求时开始计时，收到响应后结束计时，据此计算耗时与完成的事务数。

需要盯住的四类风险：

  1. 效率低下的 SQL 撑起了超高的 QPS 与 TPS；
  2. 大量并发导致数据库连接数被占满（max_connections 的默认值是 151 而不是常被引用的 100——100 是 4.x 时代的老值，从 5.1 一直到 8.0 默认都是 151，一般要设置得大一些）；
  3. 超高的 CPU 使用率，CPU 资源耗尽会出现宕机；
  4. 磁盘 IO 性能突然下降，或有大量消耗磁盘性能的计划任务。解决方向是换更快的磁盘设备、调整计划任务的时间、做好磁盘维护。

Tips：不要在主库上做数据库备份，大型活动前记得取消这类计划任务。

网卡流量：如何避免无法连接数据库

  1. 减少从服务器的数量（每个从库都会从主库复制日志）；
  2. 进行分级缓存，避免前端大量缓存同时失效；
  3. 避免使用 SELECT * 查询；
  4. 分离业务网络和服务器网络。

大表带来的问题（重要）

大表的特点

  1. 记录行数巨大，单表超千万；
  2. 表数据文件巨大，超过 10 个 G。

大表的危害

1. 慢查询：很难在短时间内过滤出需要的数据。

查询字段区分度低 → 要在大数据量的表中筛出一部分数据会产生大量磁盘 IO → 拖低磁盘效率。

2. 对 DDL 的影响。

建立索引需要很长时间：

  • MySQL < 5.5：建立索引会锁表；
  • MySQL >= 5.5：建立索引会造成主从延迟（先在主上执行，再在从库上执行）。

修改表结构需要长时间锁表，同样会造成长时间的主从延迟（几百秒的延迟很常见）。

如何处理大表

思路就是分库分表，把一张大表拆成多个小表。

难点有两个：分表主键如何选择、分表后跨分区的数据查询和统计怎么做。具体做法见后文“分库分表”一章。

大事务带来的问题（重要）

事务是数据库系统区别于其他一切文件系统的重要特性之一，它是一组具有原子性的 SQL 语句，或者说一个独立的工作单元，必须满足 ACID：原子性（atomicity，全部成功或全部回滚）、一致性（consistency，如银行转账前后总金额不变）、隔离性（isolation）、持久性（durability，注意这是数据库视角的持久，磁盘损坏就不作数了）。

其中隔离性直接影响并发表现，它由隔离级别控制：

  • 未提交读（READ UNCOMMITTED）：两个事务之间互相可见，存在脏读、不可重复读、幻读的问题；
  • 已提交读（READ COMMITTED）：符合隔离性的基本概念，一个事务进行时其它已提交事务对它是可见的，解决脏读，仍存在不可重复读、幻读；
  • 可重复读（REPEATABLE READ）：InnoDB 的默认隔离级别。事务进行时其它所有事务对其不可见，多次读得到的结果一致，解决脏读与不可重复读。它靠 MVCC（多版本并发控制，不加锁地提高并发）实现。MySQL 在这一级别其实已经借助间隙锁解决了幻读，数据插不进去；
  • 可串行化（SERIALIZABLE）：在读取的每一行数据上都加锁，三种问题全部解决，但会造成大量锁超时和锁争用，性能最低，只适合要求严格数据一致且几乎没有并发的场景。

三个概念容易混，单独记一下：

1
2
3
4
5
6
7
8
9
脏读：事务 A 读到了事务 B 未提交的数据。

不可重复读：事务 A 先读取了一条数据，执行逻辑期间事务 B 改变了这条数据，
事务 A 再次读取时发现数据不匹配了。也就是两次读到的数据不一致。

幻读：事务 A 首先按条件索引得到 N 条数据，然后事务 B 增加了 M 条符合事务 A
搜索条件的数据，事务 A 再次搜索发现有 N+M 条了。也就是第一次读到的数据比后来的少。

不可重复读针对的是 update 或 delete，幻读针对的是 insert。

相关命令：

1
2
3
4
SHOW VARIABLES LIKE '%iso%';                       -- 查看系统的事务隔离级别
BEGIN;                                             -- 开启一个新事务
COMMIT;                                            -- 提交一个事务
SET SESSION tx_isolation = 'read-committed';       -- 修改事务的隔离级别

大事务指运行时间长、操作数据比较多的事务，风险集中在锁定数据太多、回滚时间长、执行时间长：

  1. 锁定太多数据，造成大量阻塞和锁超时；
  2. 回滚所需时间长，且回滚期间数据仍处于锁定状态；
  3. 执行时间长会造成主从延迟，因为只有主库全部执行完并写入日志，从库才会开始同步。

解决思路也很直接：避免一次处理太多数据，分批次处理；把不必要的 SELECT 移出事务，保证事务里只有必要的写操作。

二、SQL 查询优化（重要）

获取有性能问题的 SQL

三种途径：

  1. 通过用户反馈获取；
  2. 通过慢查日志获取；
  3. 实时获取正在执行的 SQL。

慢查日志与分析工具

慢查询日志默认没有开启，相关配置参数：

1
2
3
4
slow_query_log                # 启动/停止记录慢查日志，需要在配置文件中开启（on）
slow_query_log_file           # 指定慢查日志的存储路径及文件名，日志和数据应分开存储
long_query_time               # 记录慢查询的执行时间阈值，默认 10 秒；繁忙系统上改为 0.001 秒（1 毫秒）比较合适
log_queries_not_using_indexes # 是否记录未使用索引的 SQL

常用分析工具是 mysqldumpslow 和 pt-query-digest：

1
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log

实时抓取正在执行的 SQL（推荐）

image.png

1
2
3
SELECT id,user,host,DB,command,time,state,info
FROM information_schema.processlist
WHERE TIME >= 60

这条语句查出当前服务器上执行超过 60 秒的 SQL。写个脚本周期性执行它，就能持续捞出有问题的语句。

一条查询的执行过程（重点）

image.png

上图原文连接

从图上可以看清 MySQL 查询执行的大致过程：

  1. 客户端发送 SQL 语句；
  2. 查询缓存：如果命中缓存就直接把结果返回给客户端，不再往下走。MySQL 8.0 之后已经抛弃了这个功能；
  3. 解析器：对 SQL 语句做语法分析、语义分析，完成预处理；
  4. 优化器：确定 SQL 语句的执行路径，比如走全表扫描还是走某个索引，生成执行计划；
  5. 执行器：执行前判断用户是否具备权限，具备则调用存储引擎 API 获取数据；在 8.0 以下版本如果开了查询缓存，此时会把结果缓存起来；
  6. 返回结果。

查询缓存对性能的影响（建议关闭）

相关配置参数：

1
2
3
4
5
query_cache_type             # 设置查询缓存是否可用
query_cache_size             # 设置查询缓存的内存大小
query_cache_limit            # 设置查询缓存可用的存储最大值（加上 sql_no_cache 可以提高效率）
query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit     # 设置查询缓存分配的内存块的最小单位

缓存查找是利用对大小写敏感的哈希查找来实现的，Hash 查找只能进行全值查找（SQL 必须完全一致）。如果缓存命中，检查用户权限，权限允许就直接返回，查询既不被解析也不会生成查询计划。

在一个读写比较频繁的系统中，建议关闭缓存，因为缓存更新会加锁。把 query_cache_type 设置为 off、query_cache_size 设置为 0。

执行计划与存储引擎的交互

第二阶段是 MySQL 依照执行计划和存储引擎交互，这个阶段又包含多个子过程：


一条查询可以有多种查询方式，查询优化器会比较每种方式的（存储引擎）统计信息，找到成本最低的那一种 —— 这也正是索引不能太多的原因，候选越多，优化器挑选的开销越大。

执行计划为什么会出错

  1. 统计信息不准确；
  2. 成本估算与实际的执行成本不同；

  1. 给出的最优执行计划与估计的不同；

  1. MySQL 不考虑并发查询；
  2. 会基于固定规则生成执行计划；
  3. MySQL 不考虑不受其控制的成本，如存储过程、用户自定义函数。

优化器会自动改写哪些 SQL

查询优化器的职责是对查询进行优化，并选出 MySQL 认为成本最低的执行计划。为了生成最优执行计划，它会主动改写一部分查询。

可以被优化的 SQL 类型：

  1. 重新定义表的关联顺序；

  1. 将外连接转换为内连接；
  2. 使用等价变换规则；

  1. 优化 count()、min()、max()；

  1. 将一个表达式转换为常数；
  2. 子查询优化；

  1. 提前终止查询，比如发现一个不成立的条件（如 where id = -1）就立即返回空结果；
  2. 对 in() 条件进行优化。

定位各阶段的耗时

使用 profile（目前已不推荐）

1
2
3
4
5
SET profiling = 1;         -- 启动 profile，这是一个 session 级的配置

SHOW PROFILES;             -- 查看每一个查询消耗的总时间

SHOW PROFILE FOR QUERY N;  -- 查看某个查询各阶段消耗的时间

使用 performance_schema

performance_schema 是 5.5 引入的性能分析引擎（5.5 时期开销较大）。启动监控和历史记录表：

1
2
3
4
5
USE performance_schema;

UPDATE setup_instruments SET enabled='YES', TIMED='YES' WHERE NAME LIKE 'stage%';

UPDATE setup_consumers SET enabled='YES' WHERE NAME LIKE 'event%';

特定 SQL 的查询优化

大表的数据修改

大表的结构修改

两条路子：

  1. 利用主从复制，先在从服务器上做修改，然后主从切换；
  2. （推荐）新表 + 触发器 + 改名的方式在线切换：

添加一个新表（改好后的结构）→ 老表数据导入新表 → 老表建立触发器，把修改同步到新表 → 老表加排它锁并重命名 → 新表重命名 → 删除老表。

修改语句本身是这个样子：

1
alter table sbtest4 modify c varchar(150) not null default ''

也可以直接用成熟工具来做在线 DDL：

优化 not in 和 <> 查询

思路是把子查询改写为关联查询：

三、索引优化（非常重要）

索引的本质与选择性

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。在 RDBMS 中，数据的索引都是硬盘级索引，只有一小部分驻留在内存里。

离散性越好，列的选择性就越好，越适合作为索引。离散性差的列（例如性别）建索引往往适得其反，数据量太少的表也不适合建索引。正确地创建合适的索引，是数据库优化的基础。

索引的分类

按逻辑功能分：

  1. 普通索引：没有任何约束，只用于提高查询效率；
  2. 唯一索引：普通索引 + 数据唯一性约束，允许空值，一个表可以有多个；
  3. 主键索引：唯一索引 + NOT NULL，一个表只能有一个；
  4. 全文索引：针对较大的文本数据，生成很耗时也耗空间，实际项目里更多交给 Elasticsearch；
  5. 组合索引：为提高效率把多列建在一个索引里，遵循最左前缀原则。

对应的建立语法：

1
2
3
4
5
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);                            -- 主键索引
ALTER TABLE `table_name` ADD UNIQUE (`column`);                                 -- 唯一索引
ALTER TABLE `table_name` ADD INDEX index_name (`column`);                       -- 普通索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`);                               -- 全文索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`);  -- 组合索引

按物理实现方式分：

  1. 聚集索引：找到索引的位置，紧随其后的就是要找的数据行，叶子节点存储的就是数据记录本身；
  2. 非聚集索引：索引项按顺序存储，指向的内容是随机存储的，叶子节点存的是数据位置。

由此带来几个结论：非聚集索引不会影响数据表的物理存储顺序；一个表只能有一个聚集索引（只能有一种排序存储方式），但可以有多个非聚集索引；用聚集索引查询效率高，但插入、删除、更新的效率会比非聚集索引低。

按字段个数分，则是单列索引和联合索引，单列索引可以看作联合索引的一种特例：

1
2
CREATE INDEX idx_name_phoneNum ON user(name, phoneNum, age);  -- 联合索引 (a,b,c)
CREATE INDEX idx_name ON user(name);                          -- 单列索引

两种主要数据结构：B-tree 与 Hash

B-tree 结构

B-tree 索引的限制：

要理解 MySQL 为什么选 B+ 树，先看普通二叉树性结构的两个问题：树的高度太高导致 IO 次数太多；磁盘以页为单位读取，操作系统默认 4K，MySQL 默认 16K，二叉结构一次加载的关键字太少，浪费了这一次 IO。

B 树（多路平衡树）：关键字的个数 = 路数 - 1，每个关键字都带数据区，因此遍历时会读到多余的数据。

B+ 树（加强版多路平衡树）：非叶子节点只保留关键字和子节点的引用，只有最后一层有数据区，因此一次 IO 能载入更多关键字；叶子节点串成链表，天然具备区间扫描能力。MySQL 的匹配过程采用左闭合的比较规则。综合下来 B+ 树在 IO 能力、排序能力、扫表能力上都更优，而且查询效率稳定可靠。

对索引中关键字的对比是从左往右依次进行的（abc < acd），所以 like '%abc' 这种把 % 放在左边的写法很糟糕，它会导致整棵 B+ 树被扫一遍。

Hash 结构

Hash 索引等值匹配非常快，但限制很多：

  • Hash 索引必须进行二次查找；
  • Hash 索引无法用于排序；
  • 不支持部分索引查找，也不支持范围查找；
  • Hash 码的计算可能存在冲突，不适合重复值很高的列（如性别），身份证这类列才合适。

正因如此，Hash 索引更常见于 key-value 型数据库。

为什么要用索引，以及为什么不能滥用

用索引的三个理由：

  1. 索引大大减少了存储引擎需要扫描的数据量；
  2. 索引可以帮助排序，从而避免使用临时表；
  3. 索引可以把随机 I/O 变为顺序 I/O。

不能滥用的两个理由：

  1. 索引会增加写操作的成本；
  2. 太多的索引会增加查询优化器的选择时间。

索引就好比一本书的目录，它让你更快找到内容，但目录显然不是越多越好。假如这本书 1000 页而有 500 页是目录，效率当然低 —— 目录要占纸张，索引要占磁盘空间。

索引优化策略

索引列上不能使用表达式和函数

前缀索引和索引列的选择性

InnoDB 索引列最大宽度为 767 个字节（utf-8 下差不多 255 个字符），MyISAM 索引列宽度最大为 1000 个字节，于是就有了前缀索引和索引选择性的话题。

对于列值较长的类型，比如 BLOB、TEXT、VARCHAR，必须建立前缀索引，即把值的前一部分作为索引，这样既节约空间又能提高查询效率。代价是无法用前缀索引做 ORDER BY 和 GROUP BY，也无法做覆盖扫描。

语法：

1
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

如何选择索引列的顺序：

  1. 经常被使用到的列优先（选择性差的列不适合，如性别，查询优化器可能认为全表扫描更快）；
  2. 选择性高的列优先；
  3. 宽度小的列优先（一页中能存的索引越多，I/O 越低，查找越快）。

组合索引与最左前缀

如果索引了多列，要遵守最左前缀法则：查询从索引的最左前列开始，并且不跳过索引中的列。联合索引中，范围匹配之后的索引列就会失效。

深入理解请移步：最左前缀原理与相关优化

覆盖索引

跟组合索引有点类似，如果索引已包含所有满足查询需要的数据，就称为覆盖索引（Covering Index），也就是平时说的不需要回表 —— 索引的叶子节点上已经带着所需数据（Hash 索引做不到）。

判断标准：用 EXPLAIN 看 extra 列，覆盖索引查询会显示 Using index。MySQL 查询优化器在执行查询前会决定是否走覆盖索引。

优点：

  1. 可以优化缓存，减少磁盘 IO 操作；
  2. 可以把随机 IO 变为顺序 IO；
  3. 可以避免对 InnoDB 主键索引的二次查询；
  4. 可以避免 MyISAM 表进行系统调用。

无法使用覆盖索引的情况：

  1. 存储引擎不支持覆盖索引；
  2. 查询中使用了太多的列（如 SELECT *）；
  3. 使用了双 % 的 like 查询（底层 API 所限）。

mysql 高效索引之覆盖索引

用索引优化排序与分组

  1. GROUP BY 实质是先排序后分组，同样遵照索引的最左前缀；
  2. 索引中所有列的方向（升序、降序）要和 ORDER BY 子句完全一致；
  3. 确实无法使用索引排序时，增大 max_length_for_sort_data 和 sort_buffer_size 参数；
  4. 如果最左列使用了范围条件，排序会失效；
  5. WHERE 高于 HAVING，能写在 WHERE 里的条件就不要放到 HAVING。

索引的维护

删除重复索引

注意：主键约束相当于唯一约束 + 非空约束。一张表最多有一个主键约束，如果设置多个主键会报错 Multiple primary key defined!!!。

删除冗余索引

检查工具：pt-duplicate-key-checker。

扩展阅读：MySQL 索引背后的数据结构及算法原理

配合 EXPLAIN 看执行计划时，几个 Extra 值最常用：

  • Using where：表示存储引擎把记录返回给 server 层之后，server 层还要按 WHERE 条件再过滤一遍。它和「回表」没有因果关系——网上把它解释成「需要回表」是个流传很广的误解，覆盖索引下 Using index; Using where 同时出现才是常态；
  • Using index：表示直接访问索引就足够获取所需数据，不需要回表，即覆盖索引。所以判断有没有回表，看的是有没有 Using index，而不是有没有 Using where；
  • Using index condition：索引条件下推（ICP），把能用索引判断的条件推到存储引擎层先过滤，减少回表次数。

索引优化口诀（套路重点）

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上不计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写 *；
不等空值还有 or，索引失效要少用；
字符单引不可丢，SQL 高级也不难。

MySQL 高级-索引优化

四、表结构优化

结构优化的目的

1、减少数据冗余：数据冗余指数据库中存在相同的数据，或某些数据可以由其他数据计算得到。注意是尽量减少，不代表完全避免。

2、尽量避免数据维护中出现更新、插入和删除异常：

要避免这些异常，就需要对数据库结构进行范式化设计。

3、节约数据存储空间。

4、提高查询效率。

数据库结构设计步骤

  1. 需求分析：全面了解产品设计的存储需求、数据处理需求、数据安全性与完整性要求；
  2. 逻辑设计（重要）：设计数据的逻辑存储结构，理清数据实体之间的逻辑关系，解决数据冗余和数据维护异常，数据范式是这一步的主要工具；
  3. 物理设计：表结构设计，落到存储引擎与列的数据类型上；
  4. 维护优化：索引优化、存储结构优化。

范式化与反范式化

传送门：数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩

物理设计

相关传送门：MySQL 中字段类型与合理的选择字段类型；int(11) 最大长度是多少？varchar 最大长度是多少

另外一条经验：不要使用外键约束来保证数据的完整性，把这件事交给应用层，可以省下大量并发场景下的约束检查开销。

五、体系结构、存储引擎与日志

应用架构的演变过程

  1. 单机单库：只需要一个 MySQL Instance 就能满足数据读写需求；
  2. 主从架构：主库抗写压力，通过从库分担读压力；
  3. 分库分表：垂直拆分是按业务、按表、按列切开，切完每个实例只放一部分表（或一张表的一部分列），行的主体还是完整的；水平拆分是同一张表按行切分，切完任何实例都只有全量数据的约 1/n。注意「每个实例都有全量数据」说的是主从复制，不是垂直拆分；
  4. 云数据库：可配置性、可扩展性、多用户存储结构设计这些难题交给服务提供商。

MySQL 的分层体系结构

粗看是三层（客户端 → 服务层 → 存储引擎），细分则是四层：

  1. 网络连接层：处理客户端的连接请求，与客户端创建连接；
  2. 服务层：Connection Pool、Service & utilities、SQL interface、Parser 解析器、Optimizer 查询优化器、Caches 缓存等模块；
  3. 存储引擎层：MyISAM、InnoDB，以及支持归档的 Archive、纯内存的 Memory 等。只要正确实现与 MySQL Server 交互的接口，任何引擎都可以接入；
  4. 系统文件层：文件的物理存储层，包括二进制日志、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志等。

MySQL结构

三个容易被忽略的结论：

  1. MySQL 是插件式的存储引擎，只要符合接口规范，你甚至可以开发自己的存储引擎；
  2. 所有跨存储引擎的功能都是在服务层实现的；
  3. MySQL 的存储引擎是针对表的，不是针对库的 —— 同一个库里可以混用不同引擎，但不建议这样做。

InnoDB 的表空间

MySQL 5.5 及之后版本默认的存储引擎是 InnoDB，它使用表空间来存储数据：

1
SHOW VARIABLES LIKE 'innodb_file_per_table';
  • 为 ON 时建立独立表空间，每张表对应一个 tablename.ibd 文件；
  • 为 OFF 时数据存储到系统的共享表空间，文件是 ibdataX（X 为从 1 开始的整数）。

另外 .frm 是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

系统表空间与独立表空间

MySQL 5.5 默认系统表空间，5.6 及以后默认独立表空间，差别体现在两处：

  1. 系统表空间无法简单地收缩文件大小，造成空间浪费并产生大量磁盘碎片；独立表空间可以通过 OPTIMIZE TABLE 收缩文件，不需要重启服务器，也不会影响对表的正常访问；
  2. 对多个表进行刷新时，系统表空间实际是顺序进行的，会产生 IO 瓶颈；独立表空间可以同时向多个文件刷新数据。

强烈建议对 InnoDB 使用独立表空间，优化起来更方便、更可控。

把系统表空间里的表转移到独立表空间的步骤：

  1. 用 mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起导出），可以在从服务器上操作；
  2. 停止 MySQL 服务器，修改参数（my.cnf 加入 innodb_file_per_table），并删除 InnoDB 相关文件（可以重建 data 目录）；
  3. 重启 MySQL，并重建 InnoDB 系统表空间；
  4. 重新导入数据。

也可以用 ALTER TABLE 做转移，但那样无法回收系统表空间中已占用的空间。

InnoDB 的两个关键特性

特性一：事务性存储引擎，带两种特殊日志。 InnoDB 完全支持事务的 ACID 特性，为此需要 Redo Log 和 Undo Log：

  • Redo Log：实现已提交事务的持久性；
  • Undo Log：服务未提交的事务，用于回滚和 MVCC 读旧版本。它存在回滚段里（系统表空间 ibdata1，或拆出来的独立 undo 表空间），访问偏随机，所以可以用 innodb_undo_directory 把独立 undo 表空间放到高性能 IO 设备上。

MySQL读

redo log 机制保证了事务更新的一致性和持久性。

特性二：支持行级锁。 行级锁能最大程度地支持并发，并且它是由存储引擎层实现的。

redo log、undo log 与 binlog

三者对应的物理文件：

1
2
3
undo log : ibdata1（系统表空间中的回滚段），或独立 undo 表空间（undo_001/undo_002，*.ibu）
redo log : ib_logfile0、ib_logfile1
bin log  : 由 log_bin / log_bin_basename 指定

这里要留意：undo 从来不会写进用户表的 .ibd 里。回滚段默认在系统表空间 ibdata1，5.6 起可以用 innodb_undo_tablespaces 拆出独立的 undo 表空间，8.0 默认就是 undo_001/undo_002 这两个 .ibu 文件；临时表产生的 undo 单独放在 ibtmp1。innodb_file_per_table 只决定用户表的数据和索引是各自一个 .ibd 还是挤在共享表空间里，管不到 undo。另外 innodb_undo_directory 可以把 undo 表空间单独指到一块高速盘上。

Undo 日志记录某数据被修改前的值，用于事务失败时 rollback；Redo 日志记录某数据块被修改后的值，用于恢复尚未写入 data file 的、已成功提交的事务数据。

Redo Log 和 Binlog 经常被混为一谈，实际差别很大：

  1. Redo Log 属于 InnoDB 引擎的功能，Binlog 是 MySQL Server 自带的功能，以二进制文件记录；
  2. Redo Log 是物理日志，记录数据页更新后的状态内容；Binlog 是逻辑日志，记录更新的过程；
  3. Redo Log 是循环写，日志空间大小固定；Binlog 是追加写，写完一个写下一个，不会覆盖；
  4. Redo Log 用于服务器异常宕机后的事务数据自动恢复；Binlog 用于主从复制和数据恢复，本身没有 crash-safe 能力。

常用日志与相关文件

1
2
3
4
5
6
SHOW VARIABLES LIKE '%log_error%';        -- 错误日志
SHOW VARIABLES LIKE '%general%';          -- 通用查询日志
SHOW VARIABLES LIKE '%slow_query%';       -- 慢查询日志
SHOW VARIABLES LIKE '%long_query_time%';  -- 慢查询时长
SET long_query_time = 5;                  -- 设置慢查询阈值
SHOW VARIABLES LIKE '%datadir%';          -- 数据目录

数据目录里还有这些文件：

  • db.opt：记录这个库默认使用的字符集和校验规则；
  • *.frm：存储与表相关的元数据（meta）信息，包括表结构定义，每张表一个；
  • 配置文件（my.cnf、my.ini 等）；
  • pid 文件：存放进程 id；
  • socket 文件：可以不通过 TCP/IP 网络，直接用 unix socket 连接 MySQL。

锁与并发

锁最主要的作用是管理共享资源的并发访问，同时它也是实现事务隔离性的手段。

锁类型

1
2
3
4
-- S 锁（读锁）
SELECT ... LOCK IN SHARE MODE;
-- X 锁（写锁），update、delete 也会加
SELECT ... FOR UPDATE;

锁的粒度分表级锁和行级锁。注意 MySQL 的事务支持不是绑定在 MySQL 服务器本身，而是与存储引擎相关。手动给表加表级锁：

1
2
LOCK TABLE table_name WRITE;   -- 写锁会阻塞其它用户对该表的读写操作
UNLOCK TABLES;                 -- 释放

由此得到三条经验：

  1. 锁的粒度越小，开销越大，并发度越高；
  2. 表级锁通常在服务器层实现；
  3. 行级锁在存储引擎层实现，InnoDB 的锁机制服务器层是不知道的。

按思想还可以分成两类：

  • 悲观锁：总是假设最坏情况，每次拿数据都认为别人会修改，所以先加锁，别人只能等待，直到锁被释放。数据库的行锁、表锁、读锁、写锁都属于这种方式，Java 中的 synchronized 和 ReentrantLock 也是悲观锁思想；
  • 乐观锁：总是假设最好情况，拿数据时不加锁，更新时才判断这期间有没有人改过，一般基于版本号机制实现。

选择依据是写冲突的概率：乐观锁适用于读多写少、冲突很少发生的场景；如果写很多，应用会不断重试，反而降低系统性能，这时用悲观锁更好，因为等到锁释放就能立即获得锁并操作。

拓展阅读：图解悲观锁和乐观锁、什么是乐观锁与悲观锁？

最后区分两个常被混用的词：阻塞是由于资源不足引起的排队等待现象；死锁是两个对象各自持有一份资源，又去申请对方正持有的资源，导致双方都无法完成操作，所持资源也无法释放。

如何选择存储引擎

参考条件：

  1. 是否需要事务；
  2. 备份能力（InnoDB 支持免费在线备份）；
  3. 崩溃恢复能力；
  4. 存储引擎的特有特性。

结论很简单：InnoDB 大法好。另外尽量别在同一个库里混用存储引擎，回滚会出问题，在线热备也会出问题。

六、事务与锁

事务

事务是必须满足4个条件（ACID）： Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）

  1. 原子性：一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
  2. 一致性： 一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
  3. 隔离性：数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
  4. 持久性：事务在处理结束后对数据做出的修改是永久的，无法丢失

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理 insert , update , delete 语句。

事务控制语句

  1. 显式的开始一个事务：
1
2
3
start transaction
 或
begin
  1. 做保存点，一个事务中可以有多个保存点：
1
savepoint 保存点名称
  1. 提交事务，并使数据库中进行的所有修改成为永久性的：
1
2
3
4
commit
 或

commit work
  1. 回滚结束用户的事务，并撤销正在进行的所有未提交的修改：
1
2
3
4
rollback
或

rollback work
  1. 删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。
1
release savepoint 保存点名称
  1. 将事务滚回标记点：
1
rollback to 标记点
  1. 设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
1
set transaction

事务处理方法

  1. 用 begin ， rollback ， commit 来实现事务处理。

  2. 用 set 来改变 MySQL 的自动提交模式。

set autocommit = 0 （禁止自动提交）。
set autocommit = 1 （开启自动提交）。

锁

锁种类

  1. 锁的粒度划分： 行锁(INNODB)，表锁(MyISAM)，页锁
  2. 锁的使用方式划分： 共享锁 （s 锁），排它锁 （x 锁）
  3. 思想上划分： 乐观锁（假设不会发生冲突，只在提交时检查是否违反数据完整性），悲观锁（假设会发生并发冲突，屏蔽一切违反数据完整性的操作）
  4. 死锁，INNODB 中才会出现，两个或多个事务在同一资源上相互占用，并请求锁定对方的资源。

MyISAM 表锁

  1. 表锁的实现
  2. 锁竞争
  3. 并发插入
  4. 锁调度

InnoDB 锁

  1. 事务隔离级别
  2. 锁竞争
  3. 锁实现

七、分库分表与读写分离

高可用架构与读写分离

读写分离是承接读负载最直接的手段：

MaxScale：实现 MySQL 读写分离与负载均衡的中间件利器

分库分表的几种方式

前提：单纯分担读负载，通过一主多从、升级硬件就能解决，不必上分库分表。

把一个实例中的多个数据库拆分到不同实例（集群）

拆分简单，但不允许跨库，而且并不能减少写负载。

把一个库中的表分离到不同的数据库中

该方式只能在一定时间内减少写压力。

以上两种方式都只是暂时缓解读写性能问题。

数据库分片

对一个库中的相关表进行水平拆分，分到不同实例的数据库中。

如何选择分区键，是分片方案成败的关键：

  1. 分区键要尽可能避免跨分区查询的发生；
  2. 分区键要尽可能使各个分区中的数据平均。

分片后还要解决全局唯一 ID 的生成问题：

扩展：表的垂直拆分和水平拆分

八、参数配置

内存配置相关参数

第一步是确定可以使用的内存上限：

1
2
内存的使用上限不能超过物理内存，否则容易造成内存溢出；
（对于 32 位操作系统，MySQL 只能使用 3G 以下的内存。）

第二步是确定 MySQL 每个连接单独使用的内存：

1
2
3
4
sort_buffer_size     # 每个线程排序缓冲区的大小，有查询需要排序时才分配（直接分配该参数的全部内存）
join_buffer_size     # 每个线程所使用的连接缓冲区大小，若一个查询关联多张表，MySQL 会为每张表各分配一个
read_buffer_size     # 对一张 MyISAM 表做全表扫描时分配的读缓冲池大小，必须是 4K 的倍数
read_rnd_buffer_size # 索引缓冲区大小，只会分配实际需要的大小

注意：以上四个参数是为一个线程分配的，如果有 100 个连接就要乘以 100。

顺便理清 MySQL 数据库实例的概念：

① MySQL 是单进程多线程（Oracle 是多进程），也就是说 MySQL 实例在系统上表现为一个服务进程；

② MySQL 实例由线程和内存组成，实例才是真正用于操作数据库文件的东西。

一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存

innodb_buffer_pool_size 定义了 InnoDB 所使用缓存池的大小，对性能十分重要，必须足够大；但过大时，InnoDB 关闭时需要更多时间把脏页从缓冲池刷新到磁盘。可用的计算方式：

1
总内存 - （每个线程所需要的内存 × 连接数） - 系统保留内存

key_buffer_size 定义了 MyISAM 所使用缓存池的大小。由于 MyISAM 的数据依赖操作系统缓存，所以要给操作系统预留更大的内存空间。当前占用可以这样估算：

1
SELECT SUM(index_length) FROM information_schema.tables WHERE engine='myisam';

注意：即使业务表全部是 InnoDB，也要为 MyISAM 预留内存，因为 MySQL 系统自身使用的表仍然是 MyISAM 表。

其他常用参数

max_connections 控制允许的最大连接数，一般设到 2000 甚至更大，避免高并发时连接被占满。