MySQL

todo

索引

• 从数据页的角度看 B+ 树
• 为什么 mysql 使用 B+ 树
• mysql 单表不要超过 2w 行?
• 索引失效
• count(*) 和 count(1) 的区别?
• mysql 分页性能优化

事务

• mysql 可重复读 (repeatable read) 事务隔离级别, 完全解决幻读 (phantom read) 了吗

锁

• update 语句没有使用索引, 会锁全表吗
• mysql 间隙锁 + 记录锁可以避免删除操作导致的幻读吗
• mysql 死锁

日志

• *

alter user 'root'@'localhost' identified with mysql_native_password BY 'pass';
flush privileges;
sudo systemctl restart mysql;

mysql [-h <host>] [-P <port>] -u <username> -p<password> [-D <database_name>]

# 查看连接列表
show processlist;
# 手动断开连接
kill connection +<id>
# 查看空闲连接的最大空闲时间
show variables like 'wait_timeout';
# 查看最大连接数, 如果超过最大连接数, 则 mysql 会拒绝后续的连接请求
show variables like 'max_connections';

执行 1 条 select 语句, 发生了什么

mysql 的架构分为两层: server 层和存储引擎层

• server 层负责建立连接、解析和执行 sql: 包括连接器、查询缓存、sql 解析器、sql 优化器、sql 执行器
• 存储引擎层负责数据的存储和检索: 支持 InnoDB 等存储引擎

1. 连接器

• mysql 连接需要 TCP 三次握手, 断开连接需要 TCP 四次挥手
• mysql 空闲连接的最大空闲时间 (wait_timeout) 默认是 8h
• 使用长连接可以减少建立连接和断开连接的时间
• 使用长连接, 可能导致内存增长, 解决方法:
  • mysql server 定期断开长连接
  • 客户端使用 mysql_reset_connection 函数重置连接

2. 查询缓存

查询缓存命中率很低, mysql@8.0 移除 server 层的查询缓存 (不是 InnoDB 的 buffer pool)

3. 解析 sql

词法分析 tokenization、语法分析 parsing, 检查 sql 是否有语法错误, 构建 AST 抽象语法树

4. 执行 sql

• prepare 预处理阶段
  • 检查 sql 查询语句中的表或者字段是否存在
  • 将 select * 中的 *, 替换为表中的所有字段名
• optimize 优化阶段: 制定执行计划, explain <sql> 查看执行计划
• execute 执行阶段
  • 主键索引查询
  • 全表扫描
  • 索引下推: 减少二级索引查询时的回表次数

MySQL 是行式数据库

例如有一个 db0 的数据库, db0 中有一张 users 表

show variables like 'datadir';

ls /var/lib/mysql/db0
# db.opt    -- 数据库的默认字符集
# users.frm -- users 表结构文件
# users.ibd -- users 表空间文件, 存储表数据

表空间、段、区、页、行

表空间 (table space) 由段 (segment), 区 (extent), 页 (page), 行 (row) 组成

• 表空间 (table space): 段的集合
• 段 (segment): 区的集合, 段分为
  • 索引段: 存储 B+ 树的非叶节点 (索引节点) 的区的集合
  • 数据段: 存储 B+ 树的叶节点 (数据节点) 的区的集合
  • 回滚段: 存储事务的 undo log 的区的集合, mysql 使用 read view + undo log 实现 MVCC 多版本并发控制
• 区 (extent): 页的集合
  • 区是 InnoDB 分配存储空间的基本单位
  • InnoDB 使用 B+ 树, B+ 树的每一层使用双向链表
  • 如果以页为单位分配存储空间, 则双向链表中相邻的两个页的物理地址不连续, 导致查询磁盘时有大量随机 I/O
  • 以区为单位分配存储空间, 则双向链表中相邻的两个页的物理地址连续, 查询磁盘时是顺序 I/O, 范围查询 (扫描叶节点) 时性能很高
  • 默认区大小是 1MB, 对于 16KB 的页, 1 个区分为 64 个页
• 页 (page): 行的集合
  • 页是 InnoDB 读写数据的基本单位
  • 如果以行为单位读写数据, 1 次 I/O 操作只能处理 1 行数据
  • 以页为单位读写数据, 默认页大小是 16KB 的连续存储空间, 1 次 I/O 操作可以处理至少 16KB 的数据
  • 页分为: 数据页、undo 日志页、redo 日志页、溢出页; 表中的记录 (record) 存储到数据页

行格式: redundant, compact, dynamic, compressed

• 不紧凑的行格式: redundant
• 紧凑的行格式: compact、dynamic (默认)、compressed
• 1 行数据的最大字节数是 65535 (除了 TEXT、BLOBs 等)
• compact 行格式发生行溢出时, 只存储该列的部分数据, 剩余数据存储到溢出页中
• dynamic 和 compressed 行格式发生行溢出时, 不存储该列的部分数据, 数据完全存储到溢出页中

compact 行格式

一条记录, 分为「记录的额外信息」和「记录的真实数据」

记录的额外信息: 变长字段的长度列表、null 值列表、记录头信息

• 变长字段的长度列表
  • 只有表中存在变长字段时, 才有变长字段的长度列表 (例如 varchar)
  • 变长字段的长度列表按照列的顺序逆序存储, 位置靠前的字段的真实数据和对应的字段长度可以在一个 cache line 中, 可以提高 cpu 缓存命中率
• null 值列表
  • 只有表中存在 nullable 字段时, 才有 null 值列表; 字段都是 not null 时, 没有 null 值列表
  • compact 行格式使用 null 值列表标识为 null 的列, null 值不会存储到记录的真实数据
    • 二进制值为 1 时, 表示该列的值为 null
    • 二进制值为 0 时, 表示该列的值不为 null
  • null 值列表按照列的顺序逆序存储
• 记录头信息
  • delete_mask 标识该条记录是否被删除; 执行 delete 删除时, 不会立刻删除记录, 而是将该条记录的 delete_mask 设置为 1
  • next_record 下一条记录的物理地址, 指向下一条记录的「记录头信息」和「真实数据」之间的位置
  • record_type 记录类型
    • 0 表示普通记录
    • 1 表示 B+ 树非叶子节点
    • 2 表示最小记录
    • 3 表示最大记录

记录的真实数据

• row_id: 非必需; 如果有主键, 或者有唯一约束, 则没有 row_id 隐藏字段; 如果没有主键, 也没有唯一约束, 则 InnoDB 会为该条记录自动生成自增的 row_id 隐藏字段, 长度 6 个字节
• trx_id 必需; 创建/更新该条记录的事务 id, 长度 6 个字节
• roll_pointer 必需; 指向该条记录上个版本 (写入到 undo log) 的指针, 长度 7 个字节
• 列 1、列 2、…

计算 varchar(n) 中 n 的最大值时, 需要减去「变长字段的长度列表」和「null 值列表」占用的字节数

即: 所有字段的总长度 + 变长字段的长度列表占用的字节数 + null 值列表占用的字节数 <= 65535

索引

索引分类

• 按数据结构分类: B+ 树索引、hash 哈希索引、full-text 全文索引
• 按存储分类: 聚簇索引、二级索引
• 按字段分类: 主键索引、唯一索引、普通索引、前缀索引
• 按字段数量分类: 单列索引、联合索引

按数据结构分类

InnoDB 支持 B+ 树索引、全文索引, 不支持哈希索引

按存储分类

创建表时

• 如果有主键, 则默认选择主键作为聚簇索引的索引键; 默认主键索引是聚簇索引
• 如果没有主键, 则选择第一个 not null 的唯一字段作为聚簇索引的索引键
• 以上两个都没有, 则 InnoDB 自动生成自增的 row_id 隐藏字段作为聚簇索引的索引键

B+ 树是多叉树, 非叶节点存储索引, 叶子节点存储数据

聚簇索引和二级索引

• 聚簇索引的 B+ 树的叶子节点存储的是数据
• 二级索引的 B+ 树的叶子节点存储的是主键值

回表查询和覆盖索引

• 回表查询: 先查询二级索引的 B+ 树, 找到叶子节点上的主键值; 再根据主键值查询主键索引的 B+ 树, 找到行数据
• 覆盖索引: 联合索引的列包含查询的全部列, 查询的数据可以在二级索引的 B+ 树的叶子节点中找到时, 不需要回表查询

B+ 树对比 B 树

• B+ 树的叶子节点存储数据, 非叶节点存储索引; B 树叶子节点、非叶节点都存储数据
• B+ 树的叶子节点使用双向链表, 适合范围查询

按字段分类

• 主键索引: 建立在主键字段上的索引, 索引列的值必须 not null; 一张表最多只有一个主键索引, 默认主键索引是聚簇索引
• 唯一索引: 建立在 unique 字段上的索引, 索引列的值必须唯一, 可以为 null; 一张表可以有多个唯一索引
• 普通索引: 建立在普通字段上的索引, 索引列的值可以重复, 可以为 null
• 前缀索引: 建立在字符类型字段 (char、 varchar、binary、varbinary) 的前缀上的索引, 索引列的值可以重复, 可以为 null; 使用前缀索引的目的是减少索引占用的存储空间, 增大一个索引页中存储的索引数量, 提高查询效率

create table <table_name> (
  <column_name> <data_type>,
  <column_name2> <data_type2>,

  -- 创建主键索引
  primary key (<column_name>) using btree,
  -- 创建唯一索引
  unique key [<index_name>] (<column_name>, <column_name2>, ...),
  -- 创建普通索引
  index [<index_name>] (<column_name>, <column_name2>, ...),
  -- 创建前缀索引
  index [<index_name>] (<column_name>(length))
);

-- 创建唯一索引
create unique index <index_name> on <table_name> (<column_name>, <column_name2>, ...);
-- 创建普通索引
create index <index_name> on <table_name> (<column_name>, <column_name2>, ...);
-- 创建前缀索引
create index <index_name> on <table_name> (<column_name>(length));

按字段数量分类

单列索引、联合索引

创建联合索引, 联合索引的 B+ 树先按 column_name1 排序, column_name1 相同时再按 column_name2 排序

create index <index_name> on <table_name> (<column_name1>, <column_name2>, ...);

最左匹配原则

使用联合索引时, 必须包含联合索引的最左列, 并且不能跳过联合索引中间的某一列; 否则联合索引全部/部分失效

使用联合索引时, 存在最左匹配原则, 例如创建一个 (a, b, c) 的联合索引, 则联合索引的 B+ 树先按 a 排序, a 相同时再按 b 排序, b 相同时再按 c 排序

以下的条件查询, 联合索引有效; 因为有查询优化器, 所以 a 字段在 where 子句中的顺序不重要

• where a = 1
• where a = 1 and b = 2
• where a = 1 and b = 2 and c = 3

以下的条件查询, 联合索引失效; 因为 a 字段是全局有序的, 而 b、c 字段是全局无序、局部有序的, 索引有效的前提是索引的 key 是有序的

• where b = 2
• where c = 3
• where b = 2 and c = 3

Caution

• 联合索引的最左匹配原则, 遇到范围查询 (例如 >, <) 时, 会停止匹配, 即范围查询的字段会使用联合索引, 范围查询字段后面的字段不会使用联合索引
• 对于 >=, <=, between, like 前缀匹配的范围查询, 不会停止匹配

索引下推

• 查看 sql 的执行计划时, extra = Using index condition 时, 说明使用了索引下推优化
• 对于联合索引 (a, b), 执行 select * from table_name where a > 1 and b = 2, 只有 a 字段会使用联合索引, b 字段不会使用联合索引; 在联合索引的 B+ 树中找到第一个满足条件的主键值, 例如 ID = 2
• mysql@5.6 前, 只能使用满足条件的主键值 (ID = 2) 一个个回表查询
• mysql@5.6 引入索引下推, 可以在联合索引遍历时, 对联合索引中包含的字段先判断, 直接过滤不满足条件的记录 (这里是 b != 2), 减少二级索引查询时的回表次数

索引区分度

区分度是某个字段 column 不同值的数量 / 总行数

创建联合索引时, 将区分度大的字段排在前面, 区分度小的字段排在后面; 这样区分度大的字段可能被更多的 sql 使用到; 例如 gender 的区分度很小, 创建联合索引时适合排在后面; uuid 的区分度很大, 创建联合索引时适合排在前面

区分度 = distinct(column) / count(*)

索引的缺点

• 索引会占用磁盘空间
• 创建和更新索引需要消耗时间, 消耗的时间随数据量的增大而增加
• 会降低表的增删改效率, 因为每次增删改, 都需要更新 B+ 树以保持索引有序性

什么时候需要索引

• 有 unique 唯一约束的字段
• 频繁用于 where 条件查询的字段
• 频繁用于 group by 分组 和 order by 排序的字段

什么时候不需要索引

• 表的数据量很小
• 频繁更新的字段, 不需要索引 (会导致频繁更新 B+ 树以保持索引有序性)
• 索引区分度很小的字段, 例如 gender
• where 条件查询、group by 分组 和 order by 排序不使用的字段

索引优化

• 前缀索引优化
  • 使用字符类型字段的前几个字符建立索引
  • 减少索引占用的存储空间, 增大一个索引页中存储的索引数量, 提高查询效率
  • 前缀索引的局限性
    • order by 无法使用前缀索引
    • 覆盖索引中, 无法使用前缀索引
• 覆盖索引优化
  • 联合索引的列包含查询的全部列, 查询的数据可以在二级索引的 B+ 树的叶子节点中找到时, 不需要回表查询
  • 可以避免回表查询, 减少 I/O 操作
• 主键索引最好自增
  • InnoDB 默认主键索引是聚簇索引
  • 如果使用自增主键, 则插入数据时是追加, 不需要移动数据; 页写满时自动分配新页
  • 如果使用非自增主键, 则插入数据时是随机插入, 可能需要移动数据, 甚至可能导致「页分裂」即复制一个页的数据到另一个页, 页分裂会导致内存碎片, 索引不紧凑, 影响查询效率
• 主键的长度最好不要太长
  • 主键的长度越小, 二级索引的叶子节点越小 (二级索引的 B+ 树的叶子节点存储的是主键值), 二级索引占用的存储空间越小
• 索引列最好 not null
• 防止索引失效

索引失效

• 对索引列使用「左模糊匹配」like '%xxx' 或「左右模糊匹配」like '%xxx%', 会导致索引失效
• 对索引列使用函数、表达式计算、隐式类型转换 (mysql 比较字符串和数字时, 会自动将字符串转换为数字进行比较), 会导致索引失效
• where 子句中的 or: 在 where 子句中, 如果 or 前面的是索引列, or 后面的不是索引列, 会导致索引失效

查看执行计划

• id 查询的序号; id 相同则执行顺序从上到下; id 不同则 id 越大越先执行
• select_type 查询的类型
  • simple 简单查询, 即不包含连接查询, 子查询
  • primary 主查询
  • union 联合查询 union 后的查询
  • subquery 子查询
• type 扫描类型, 效率从高到低 null, system, const, eq_ref, ref, range, index, all
  • null
  • system
  • const 查询结果只有一条的主键索引或唯一索引扫描
  • eq_ref 主键索引或唯一索引扫描, 通常出现在联表查询
  • ref 非唯一索引扫描; 使用了非唯一索引, 或者唯一索引的非唯一前缀
  • range 索引范围扫描, 通常出现在范围查询
  • index 全索引扫描
  • all 全表扫描
• possible_keys 可能使用的索引, 一个或多个
• key 实际使用的索引, null 表示未使用索引
• key_len 索引的长度 (占用的字节数)
• rows: 扫描的行数
• filtered: 返回行数占读取行数的百分比, filtered 的值越大越好
• extra
  • Using filesort: 查询语句中包含 order by 排序或 group by 分组, 并且无法使用索引进行排序时, 可能会使用文件排序, 效率低
  • Using temporary: 查询语句中包含 order by 排序或 group by 分组时, 可能会使用临时表保存中间结果, 效率低
  • Using index: 使用了覆盖索引, 联合索引的列包含查询的全部列, 查询的数据可以在二级索引的 B+ 树的叶子节点中找到时, 不需要回表查询, 效率高

事务

事务的 4 个特性: ACID

• 原子性 atomicity: 一个事务中的所有操作, 要么全部完成, 要么全部不完成; 如果事务在执行过程中发生错误, 则会回滚 (undo) 到事务开始前的状态
• 一致性 consistency: 事务操作前和操作后, 保持数据的一致性
• 隔离性 isolation: 数据库允许多个并发事务同时读写数据, 隔离性可以防止多个事务并发执行导致数据不一致
• 持久性 durability: 事务提交或回滚后, 对数据的改变是持久的

InnoDB 如何保证事务的 4 个特性

• 原子性 atomicity: 通过 undo log 回滚日志保证原子性
• 一致性 consistency: 通过持久性 + 原子性 + 隔离性保证一致性
• 隔离性 isolation: 通过 MVCC 多版本并发控制、锁机制保证隔离性
• 持久性 durability: 通过 redo log 重做日志保证持久性

并发事务会引发的问题

并发事务会引发的问题: 脏读 (dirty read)、不可重复读 (non-repeatable read)、幻读 (phantom read)

严重程度: 脏读 > 不可重复读 > 幻读

• 脏读: 一个事务读到另一个未提交事务更新的数据
• 不可重复读: 一个事务内多次读取同一条数据, 前后两次读出的数据不同
• 幻读: 一个事务内多次查询满足某个查询条件的「记录数量」, 前后两次查询的记录数量不同

事务隔离级别

• 读未提交 (read uncommitted): 一个事务还没有提交, 他的更新就能被其他事务看到
• 读已提交 (read committed): 一个事务提交后, 他的更新才能被其他事务看到;「读已提交」隔离级别在每次读数据时，都会生成一个新的 read view
  • 读已提交隔离级别, 意味着一个事务内多次读取同一条数据, 前后两次读出的数据可能不同, 因为另一个事务可能更新了该记录并提交了事务
• 可重复读 (repeatable read): 一个事务执行过程中看到的数据, 和该事务执行开始时看到的数据是相同的, 可重复读是 mysql 的默认事务隔离级别;「可重复读」隔离级别在启动事务时, 会生成一个 read view, 整个事务期间都会使用这个 read view, 可以在 undo log 版本链中找到事务启动时的数据快照
  • 可重复读隔离级别, 意味着整个事务期间每次查询的数据, 都是事务启动时的数据快照
• 串行化 (serializable): 会对记录加读写锁, 多个事务串行执行, 性能很差

隔离级别: 串行化 > 可重复读 > 读已提交 > 读未提交; 隔离级别越高, 性能越差

隔离级别, √ 代表有, X 代表无	读未提交	读已提交	可重复读	串行化
脏读	√	X	X	X
不可重复读	√	√	X	X
幻读	√	√	√	X

InnoDB 默认事务隔离级别是可重复读

InnoDB 默认事务隔离级别是可重复读, 但是很大程度上避免了「幻读」

• 对于快照读 (普通 select 语句), 通过 MVCC 多版本并发控制避免了幻读
  • 开启事务 (begin/start transaction), 并执行了第一条 select 语句后, 会创建一个 read view
  • 整个事务期间都会使用这个 read view, 可以在 undo log 版本链中找到事务启动时的数据快照
  • 整个事务期间每次查询的数据, 都是事务启动时的数据快照, 避免了幻读
• 对于当前读 (select … for update 等语句), 通过临键锁 (记录锁 + 间隙锁) 避免了幻读
  • mysql 中除了普通 select 语句, 其他都是当前读, 例如 update, insert, delete 等
  • 如何避免特殊场景下的幻读: 开启事务后, 立刻执行 select ... for update 加临键锁 (记录锁 + 间隙锁), 避免其他事务插入记录

开启事务 != 启动事务

	开启事务的命令	启动事务的时机
开启事务	sql1 = begin/start transaction	执行 sql1 后, 执行了第一条 select 语句后
启动事务	sql2 = start transaction with consistent snapshot	执行 sql2 后, 立刻启动事务

MVCC 和 read view

MVCC: 使用版本链, 控制并发事务读写同一条记录时的行为, 称为多版本并发控制

read view 中的 4 个字段

• 活跃事务: 已启动、未提交的事务
• m_ids: 创建 read view 时, 数据库中活跃事务的事务 id 列表
• min_trx_id: 创建 read view 时, 数据库中活跃事务中, 最小的事务 id
• max_trx_id: 创建 read view 时, 下一个事务 id, 即全局事务中, 最大的事务 id + 1
• creator_trx_id: 创建该 read view 的事务的事务 id

记录中的 2 个隐藏列

• trx_id: 创建/更新该条记录的事务 id, 长度 6 个字节
• roll_pointer: 指向该条记录上个版本 (写入到 undo log 重做日志) 的指针, 长度 7 个字节

if (trx_id < min_trx_id) {
  // 已提交的事务
} else if (trx_id >= min_trx_id && trx_id < max_trx_id) {
  // 已启动、未提交的事务
} else {
  // trx_id >= max_trx_id
  // 未启动的事务
}

• 如果记录的 trx_id < read view 中的 min_trx_id, 说明这个版本的记录是在创建 read view 前已提交的事务创建/更新的, 所以该版本的记录对当前事务可见
• 如果记录的 trx_id >= read view 中的 max_trx_id, 说明这个版本的记录是在创建 read view 后才启动的事务创建/更新的, 所以该版本的记录对当前事务不可见
• 如果记录的 trx_id [min_trx_id, max_trx_id)
  • 如果该记录的 trx_id 在 m_ids 列表中, 说明创建/更新该版本的记录的事务是活跃事务 (已启动、未提交), 所以该版本的记录对当前事务不可见
  • 如果该记录的 trx_id 不在 m_ids 列表中, 说明创建/更新该版本的记录的事务是已提交事务, 所以该版本的记录对当前事务可见

锁

i18n

• [库] 全局锁
• [表] 表共享锁: table S (Shared Lock)
• [表] 表排他锁: table X (Exclusive Lock)
• [表] 意向共享锁 table IS (Intention Shared Lock)
• [表] 意向排他锁 table IX (Intention Exclusive Lock)
• [行] 记录共享锁 record S
• [行] 记录排他锁 record X
• [行] 间隙共享锁 gap S
• [行] 间隙排他锁 gap X
• [行] next-key 共享锁 next-key S
• [行] next-key 排他锁 next-key X

共享锁 S、排他锁 X 满足: 读读共享、读写互斥、写写互斥

按锁的范围分类

• 全局锁: 用于数据库逻辑备份
• 表级锁: 表锁、元数据锁、意向锁、auto-inc 锁
• 行级锁: 记录锁、间隙锁、临键锁

全局锁

-- 对数据库 (的所有表) 加全局锁, 整个数据库只读
flush tables with read lock;
-- 释放全局锁 (会话结束后, 自动释放全局锁)
unlock tables;

# 逻辑备份数据库, 需要加全局锁
mysqldump -u <username> -p<password> <database_name> > ./backup.sql
# 逻辑备份数据库 (InnoDB), 开启事务, 不需要加全局锁
mysqldump -u <username> -p<password> --single-transaction <database_name> > backup.sql
# 重放逻辑备份
mysql -u <username> -p<password> [<database_name>] < ./backup.sql

表级锁: 表锁, 元数据锁, 意向锁, auto-inc 锁

表锁 (table S, table X)

• 表共享锁 (Sharded Lock, table S): 允许共享读本表, 允许本线程写本表, 不允许其他线程写本表
• 表排他锁 (Exclusive Lock, table X): 允许本线程读写本表, 不允许其他线程读写本表

-- 表共享锁
lock tables <table_name> read;
-- 表排他锁
lock tables <table_name> write;

元数据锁 (MDL)

• 元数据: 表结构, 无需显式的使用元数据锁, 元数据锁是在事务提交后释放

• 对一张表进行 crud 时, 加 MDL 共享读锁

• 对一张表进行修改表结构时, 加 MDL 排他写锁

• 有线程 crud 表时, 加 MDL 共享读锁; 如果有其他线程修改表结构 (申请 MDL 排他写锁), 则会被阻塞, 直到 crud 结束释放 MDL 共享读锁

• 有线程修改表结构时, 加 MDL 排他写锁; 如果有其他线程 curd 表 (申请 MDL 共享读锁), 则会被阻塞, 直到修改表结构结束释放 MDL 排他写锁

• 申请 MDL 锁的请求会形成一个 FIFO 队列, 队列中 MDL 排他写锁的获取优先级高于 MDL 共享读锁

• 线程 A 开启了长事务, 一直没有提交, 执行一条 select 语句, 对该表加 MDL 共享读锁

• 线程 B 也执行一条 select 语句, 不会被阻塞

• 线程 C 想修改表结构, 因为线程 A 的长事务一直没有提交, 即一直占用 MDL 共享读锁, 所以线程 C 获取不到 MDL 排他写锁, 会被阻塞

• 线程 C 阻塞后, 后续的 crud 线程, 都会被阻塞: 申请 MDL 锁的请求会形成一个 FIFO 队列, 队列中 MDL 排他写锁的获取优先级高于 MDL 共享读锁

所以修改表结构前, 可以先 kill 长事务 (如果有)

意向锁 (table IS, table IX)

意向锁分为:

• 意向共享锁 (Intention Shared Lock, table IS)
• 意向排他锁 (Intention Exclusive Lock, table IX)
• 对表中的某些记录加「记录共享锁 (record S)」前, 需要对该表加「意向共享锁 (table IS)」
• 对表中的某些记录加「记录排他锁 (record X)」前, 需要对该表加「意向排他锁 (table IX)」
• 普通 select 语句不加记录锁

-- 先对表加意向共享锁 (table IS), 再对记录加记录共享锁 (record S)
select ... lock in share mode;

-- 先对表加意向排他锁 (table IX), 再对记录加记录排他锁 (record X)
select ... for update;

为什么需要意向锁: 快速判断表中是否有记录被加记录锁

• 如果没有意向锁, 则加「表排他锁 table X」时, 需要遍历表中的所有记录, 检查是否有记录加了「记录排他锁 record X」这样效率很低
• 引入意向锁, 可以快速判断表中是否有记录被加记录锁

auto-inc 锁 (mysql@5.1)

• 主键声明 auto_increment; 插入数据时可以不指定主键值, 数据库会加表级别的 auto-inc 锁, 生成自增主键
• auto-inc 锁不是在事务提交后才释放, 而是插入数据完成后立刻释放
• mysql@5.1.22 开始, InnoDB 提供 lightweight memory mutex, 用于生成自增主键
  • innodb_autoinc_lock_mode = 0: 使用 auto-inc 锁
  • innodb_autoinc_lock_mode = 2: 使用 lightweight memory mutex

行级锁: 记录锁, 间隙锁, 临键锁, 插入意向锁

-- 先对表加意向共享锁 (table IS), 再对记录加记录共享锁 (record S)
select ... lock in share mode;

-- 先对表加意向排他锁 (table IX), 再对记录加记录排他锁 (record X)
select ... for update;
update table ... where id = 1;
delete from table ... where id = 1;

行级锁分为

• 记录锁 (record lock): 一个记录锁可以锁定一条记录
  • 记录排他锁 (record X)
  • 记录共享锁 (record S)
• 间隙锁 (gap lock): 一个间隙锁可以锁定一个间隙
• 临键锁 (next-key lock): 临键锁 = 记录锁 + 间隙锁, InnoDB 使用临键锁避免幻读

sql	加记录锁类型
普通 select 语句	不加记录锁
insert、delete、update	加记录排他锁 record X, 不允许其他线程读写该行
select ... lock in share mode	锁定读, 加记录共享锁 record S, 允许其他线程读该行, 不允许其他线程写该行
select ... for update	锁定读, 加记录排他锁 record X, 不允许其他线程读写该行

行级锁加锁规则

唯一索引 (聚簇索引) 等值查询

• 查询的记录存在时, 在索引 B+ 树上找到该记录后, 该记录的索引中的临键锁会退化为记录锁
• 查询的记录不存在时, 在索引 B+ 树上找到第一条大于该记录的记录后, 该记录的索引中的临键锁会退化为间隙锁

唯一索引 (聚簇索引) 范围查询

• 对于「大于等于」条件的范围查询, 因为存在等值查询条件, 如果查询的记录存在, 则该记录的索引中的临键锁会退化为记录锁
• 对于「小于、小于等于」条件的范围查询
  • 如果查询的记录不存在
    • 扫描到终止范围查询条件的记录时, 该记录的索引的临键锁「会」退化为间隙锁
    • 其他扫描到的记录, 对这些记录的索引加临键锁
  • 如果查询的记录存在
    • 如果是「小于」条件的范围查询
      • 扫描到终止范围查询条件的记录时, 该记录的索引的临键锁「会」退化为间隙锁
      • 其他扫描到的记录, 对这些记录的索引加临键锁
    • 如果是「小于等于」条件的范围查询
      • 扫描到终止范围查询条件的记录时, 该记录的索引的临键锁「不会」退化为间隙锁
      • 其他扫描到的记录, 对这些记录的索引加临键锁

非唯一索引 (二级索引) 等值查询

• 3.1 查询的记录存在时

  • 3.1.1 直到扫描到第一个不满足条件的二级索引时, 才停止扫描
  • 3.1.2 对于扫描到的二级索引, 加临键锁
  • 3.1.3 对于第一个不满足条件的二级索引, 该二级索引的临键锁会退化为间隙锁
  • 3.1.4 对于满足条件的记录的聚簇索引, 加记录锁

• 3.2 查询的记录不存在时

  • 3.2.1 对于第一个不满足条件的二级索引, 该二级索引的临键锁会退化为间隙锁
  • 3.2.2 因为满足条件的记录不存在, 所以不会对聚簇索引加记录锁

• 非唯一索引 (二级索引) 范围查询: 对二级索引都是加临键锁, 「不会」退化为间隙锁或记录锁

• 如果没有使用索引, 则是全表扫描, 会对每个聚簇索引加临键锁, 等价于加表锁, 效率很低

插入意向锁

• 插入意向锁是特殊的间隙锁
• 一个事务插入新记录时, 判断插入位置是否被其他事务加了间隙锁或临键锁, 如果插入位置被其他事务加了间隙锁或临键锁, 则插入操作会被阻塞
• 阻塞期间, 该事务会生成一个插入意向锁, 锁的状态设置为 waiting, 直到其他事务提交, 释放间隙锁或临键锁

Caution

• granted 状态的锁, 表示事务获取到锁
• waiting 状态的锁, 表示事务没有获取到锁

undo log, redo log

• undo log 回滚日志: InnoDB 存储引擎层生成的日志, 保证事务的原子性, 实现事务回滚和 MVCC 多版本并发控制
• redo log 重做日志: InnoDB 存储引擎层生成的日志, 保证事务的持久性, 用于故障恢复 (crash-safe)
• binlog 二进制日志: server 层生成的日志, 主要用于数据备份、主从复制

为什么需要 undo log

• 保证事务的原子性, 实现事务回滚
• mysql 使用 read view + undo log 实现 MVCC 多版本并发控制

执行「增删改」语句时, 虽然没有 begin/start transaction 开启事务和 commit 提交事务, 但是 mysql 会隐式开启事务以执行「增删改」语句, 执行完成后自动提交事务

• insert 插入一条记录时, 向 undo log 中写入该条记录的主键值, 这样回滚时可以根据主键值删除该条记录
• delete 删除一条记录时, 向 undo log 中写入该条记录, 这样回滚时可以重新插入该条记录
  • 执行 delete 删除时, 不会立刻删除记录, 而是将该条记录的 delete_mask 设置为 1
  • purge 线程执行最终的删除
• update 更新一条记录时
  • 如果更新的是主键列, update 分为 2 步: 先删除该行, 再插入新行
  • 如果更新的不是主键列, 向 undo log 中写入更新的列的旧值 (反向 update 操作)

每次增删改生成的 undo log 都有一个 trx_id 事务 ID 和一个 roll_pointer 指针

• trx_id 事务 ID: 增删改该行的事务 ID
• roll_pointer: 指向该行上个版本的指针, roll_pointer 指针将 undo log 串联为一个链表, 这个链表称为版本链

为什么需要 buffer pool

mysql 启动时, 为 buffer pool 申请一块连续的内存空间, 按默认的 16KB 划分缓存页

• 查询数据时
  • 如果 buffer pool 中有该数据的缓存页, 则直接读 buffer pool 中的数据
  • 否则从磁盘中读该数据页, 并将数据页缓存到 buffer pool 中, 再返回数据 (即使查询 1 行, 也会缓存 1 页)
• 更新数据时:
  • 如果 buffer pool 中有该数据的缓存页, 则直接写 buffer pool 中的数据, 并将该缓存页标记为脏页, 同时向 redo log 中写入本次更新; 为了减少磁盘 I/O 次数, 不会立刻将脏页刷新到磁盘, 而是由后台线程每隔 1s 将脏页刷新到磁盘 (刷盘)
  • 否则从磁盘中读该数据页, 并将数据页缓存到 buffer pool 中, 再更新数据 (即使更新 1 行, 也会缓存 1 页)

为什么需要 redo log

WAL (Write-Ahead Logging): 更新数据时, 先写 buffer pool 中的数据, 并将该缓存页标记为脏页, 同时向 redo log 中写入本次更新; 为了减少磁盘 I/O 次数, 不会立刻将脏页刷新到磁盘, 而是由后台线程每隔 1s 将脏页刷新到磁盘 (刷盘)

故障时, 虽然脏页没有写入磁盘, 但是有 redo log, 可以使用 redo log 恢复数据

redo log 需要写磁盘, 数据也需要写磁盘, 为什么需要 redo log

• redo log 的写磁盘是追加写
• 数据的写磁盘需要先找到写入位置, 是随机 I/O

对比 undo log 回滚日志和 redo log 重做日志

• undo log 回滚日志: 保存事务「更新前」的数据 (旧值), 保证事务的原子性, 实现事务回滚和 MVCC 多版本并发控制
• redo log 重做日志: 保存事务「更新后」的数据 (新值), 保证事务的持久性, 用于故障恢复 (crash-safe)

redo log 刷盘时机

• 后台线程每隔 1s 将脏页刷新到磁盘
• 每次事务提交时
• mysql 正常关闭时
• redo log buffer 使用量大于 1/2 时

redo log 写满了怎么办

redo log 写满时, mysql 更新操作会被「阻塞」先将 buffer pool 中的脏页刷新到磁盘, 擦除不必要的 redo log, 再继续执行更新操作

为什么需要 binlog

binlog 记录「所有的」表结构修改和表数据更新, 不记录查询操作

redo log	binlog
InnoDB 存储引擎层	server 层
redo log 存储空间大小固定	全量日志

被删库只能使用 binlog 恢复数据

• redo log 边写边擦除, 只记录未被刷入磁盘的数据, 不记录已刷入磁盘的数据
• binlog 是全量日志, 记录「所有的」表结构修改和表数据更新

为什么需要两阶段提交

syntax

创建表, 修改表

-- 查询所有数据库
show databases;

-- 创建数据库
create database [if not exists] <database_name> [default charset <charsetName>] [collate <collateName>];
-- e.g.
create database if not exists db0 default charset utf8mb4 collate utf8mb4_general_ci;

-- 使用数据库
use <database_name>;

-- 查询当前数据库
select database();

-- 删除数据库
drop database [if exists] <database_name>;
drop database [if exists] <database_name1>, <database_name2>, ...;

-- 查询当前数据库的所有表
show tables;
show tables from <database_name>;

-- 创建表
create table [if not exists] <table_name> (
  <primary_key>  int unsigned auto_increment primary key,   -- 无符号整型自增主键
  <column_name2> varchar(160) not null unique,              -- 非空唯一变长字符串
  <column_name3> boolean default true,                      -- 默认 true
  <column_name4> int check (column_name4 between 0 and 100) -- 检查约束
)
  collate utf8mb4_general_ci -- 使用 utf8mb4_general_ci 排序规则
  default charset = utf8mb4  -- 使用 utf8mb4 字符集
  engine = InnoDB;           -- 使用 InnoDB

-- 描述表结构
desc <table_name>;
-- 等价于
describe <table_name>;
explain <table_name>;
show columns from <table_name>;
show fields from <table_name>;

-- 查询创建表的 sql
show create table <table_name>;
-- 格式化输出
show create table <table_name> \G;

-- 删除表
drop table [if exists] <table_name>;
drop table [if exists] <table_name>, <table_name2>, ...;

-- 清空表
truncate table <table_name>;

-- 增加字段
alter table <table_name> add <newColumnName> <data_type>;

-- 在指定字段的后面增加字段
alter table <table_name> add <newColumnName> <data_type> after <column_name>;

-- 删除字段
alter table <table_name> drop <column_name>;

-- 修改字段的数据类型 (modify)
alter table <table_name> modify <column_name> <data_type>;

-- 修改字段的字段名和数据类型 (change)
alter table <table_name> change <oldColumnName> <newColumnName> <data_type>;

-- 修改表名
alter table <oldTableName> rename <newTableName>;

数据类型

数据类型	大小	描述
tinyint	1 byte	极小整数
smallint	2 bytes	小整数
mediumint	3 bytes	中整数
int 或 integer	4 bytes	大整数
bigint	8 bytes	极大整数
float	4 bytes	单精度浮点数
double	8 bytes	双精度浮点数
decimal	依赖 (M,D)	高精度小数
char	0 ~ 255 chars	定长字符串
varchar	0 ~ 65535 bytes	变长字符串
tinyblob	0 ~ 255 bytes	极短二进制数据
tinytext	0 ~ 255 bytes	极短文本数据
blob	0 ~ 65535 bytes	短二进制数据
text	0 ~ 65535 bytes	短文本数据
mediumblob	0 ~ 16MB	二进制数据
mediumtext	0 ~ 16MB	文本数据
longblob	0 ~ 4GB	长二进制数据
longtext	0 ~ 4GB	长文本数据
date	3 bytes	yyyy-mm-dd
time	3 bytes	hh:mm:ss
year	1 bytes	yyyy
datetime	5 ~ 8 bytes	yyyy-mm-dd hh:mm:ss
timestamp	4 ~ 7 bytes	yyyy-mm-dd hh:mm:ss

插入, 更新, 删除

-- 插入
insert into <table_name> (<column_name1>, <column_name2>, ...)
values (<row1value1>, <row2value2>, ...), (<row2value1>, <row2value2>, ...), ...;
-- All columns
insert into <table_name>
values (<row1value1>, <row2value2>, ...), (<row2value1>, <row2value2>, ...), ...;

-- 更新
update <table_name> set <column_name1> = <value1>, <column_name2> = <value2>, ... [where <condition_expr>];

-- 删除
delete from <table_name> [where <condition_expr>];

查询

• and &&
• or ||
• not !
• between l and r 左闭右闭 [l, r]
• in Array.prototype.includes
• like 通配符 (_ 匹配单个字符, % 匹配任意个字符)
• is [not] null

select [distinct] <column_name1> [as <alias1>], <column_name2> [as <alias2>], ... -- distinct 去重

from <table_name>

where <condition_expr>                                            -- where 分组前过滤

group by <column_name1>, <column_name2>, ...                       -- group by 分组字段列表

having <condition_expr>                                           -- having 分组后过滤

order by <column_name1> [asc]|desc, <column_name2> [asc]|desc, ... -- order by 排序查询

limit <start_index>, <page_size>;                                  -- limit 分页查询
limit <page_size> offset <start_index>;                            -- limit 分页查询

聚合函数

• count, max, min, avg, sum
• null 值不参与聚合函数的计算
• where 条件中不能有聚合函数, having 条件中可以有聚合函数

权限

权限	说明
all, all privileges	所有权限
select	查询权限
insert	插入权限
update	修改权限
delete	删除权限
alter	修改表的权限
drop	删除数据库, 表, 视图的权限
create	创建数据库, 表的权限

-- 查询用户
use mysql;
select * from user;

-- 创建用户
create user '<username>'@'<hostname>' identified by '<password>';

-- 修改用户密码
alter user '<username>'@'<hostname>' identified with mysql_native_password by '<newPassword>';

-- 删除用户
drop user '<username>'@'<hostname>';

-- 查询权限
show grants for '<username>'@'<hostname>';

-- 授予权限
grant <privilegeName1>, <privilegeName2>, ... on <database_name>.<table_name> to '<username>'@'hostname';

-- 撤销权限
revoke <privilegeName1>, <privilegeName2>, ... on <database_name>.<table_name> from '<username>'@'<hostname>';

函数

字符串函数

• concat(s1, s2, …)
• lower(str)
• upper(str)
• lpad(str, n, padStr)
• rpad(str, n, padStr)
• trim(str)
• substring(str, start, len)

数值函数

• ceil(x)
• floor(x)
• mod(x, y)
• rand()
• round(x, y)

日期函数

• curdate()
• curtime()
• now()
• year(date)
• month(date)
• day(date)
• date_add(date, interval)
• datediff(date1, date2)

流程函数

if(<cond>, <ret1>, <ret2>)

等价于 return cond ? ret1 : ret2;

ifnull(<val1>, <val2>)

等价于 return val1 != null ? val1 : val2;

case when <cond1> then <ret1> when <cond2> then <ret2> ... else <default> end

等价于 if (cond1) return ret1; if (cond2) return ret2; ... return default;

case <expr> when <val1> then <ret1> when <val2> then <ret2> ... else <default> end

等价于 if (expr == val1) return ret1; if (expr == val2) return ret2; ... return default

约束

约束	关键字
非空约束	not null
唯一约束	unique
主键约束	primary key
默认约束	default
检查约束	check
外键约束	foreign key

create table <table_name> (
  <primary_key>  int unsigned auto_increment primary key,     -- 主键约束: 无符号整型自增主键
  <column_name2> varchar(16) not null unique,                 -- 非空约束, 唯一约束: 非空唯一变长字符串
  <column_name3> boolean default true,                        -- 默认约束: 默认 true
  <column_name4> int check (<column_name4> between 0 and 100) -- 检查约束
);

外键约束

外键: 关联两表的数据, 确保数据的一致性, 完整性

-- 创建子表时, 添加从子表某列指向父表某列的外键
create table <table_name> (
  [constraint] [<foreignKeyName>] foreign key (<column_name>) references <foreignTableName> (<foreignColumnName>);
)

-- 修改子表时, 添加从子表某列指向父表某列的外键
alter table <table_name> add constraint <foreignKeyName> foreign key (<column_name>) references <foreignTableName> <foreignColumnName>;
-- e.g. 添加从 t_emp 员工表 (子表) dep_id 字段指向 t_dep 部门表 (父表) id 字段的外键
alter table t_emp add constraint fk_emp_dep_id foreign key (dep_id) references t_dep id;

-- 删除外键
alter table <table_name> drop foreign key <foreignKeyName>;

场景: 从 t_emp 员工表 (子表) dep_id 字段指向 t_dep 部门表 (父表) id 字段的外键

no action/restrict

t_dep 部门表 (父表) 中删除某行, 或更新某行的 id 时; 如果 t_emp 员工表 (子表) 中存在 dep_id == 该 id 的记录, 则不允许删除/更新

cascade

t_dep 部门表 (父表) 中删除某行, 或更新某行的 id 时; 时, 如果 t_emp 员工表 (子表) 中存在 dep_id == 该 id 的记录, 则同时删除/更新子表中的记录

set null

t_dep 部门表 (父表) 中删除某行, 或更新某行的 id 时; 如果 t_emp 员工表 (子表) 中存在 dep_id == 该 id 的记录, 则将子表中, 记录的 dep_id 字段值设置为 null

set default

t_dep 部门表 (父表) 中删除某行, 或更新某行的 id 时; 如果 t_emp 员工表 (子表) 中存在 dep_id == 该 id 的记录, 则将子表中, 记录的 dep_id 字段值设置为默认值 (InnoDB 不支持)

联表查询

1. 一对多: 部门表 -> 员工表; 通常为 “多” (员工表, 子表) 创建外键 (foreign key), 指向 “一” 的主键 (部门表, 父表)
2. 多对多: 学生表 -> 课程表; 通常创建中间表, 中间表有 2 个外键, 分别指向两个表的主键, 即转换为「学生表 -> 中间表」,「课程表 -> 中间表」两个一对多问题
3. 一对一: 常用于单表拆分, 基本字段放在一张表中, 详情字段放在另一张表中; 通常详情表的外键 (user_id), 指向基础表的主键 (id), 并且外键所在的列使用唯一约束

联表查询分类

• 连接查询
  • 内连接: 查询 left 表, right 表交集的数据
  • 外连接
    • 左外连接: 查询 left 表, 和 left 表, right 表交集的数据
    • 右外连接: 查询 right 表, 和 left 表, right 表交集的数据
  • 自连接: left 表 == right 表 == 自身, 自连接必须使用表别名
• 联合查询
• 子查询

连接查询

-------------
-- 隐式内连接
-------------
select <column_name1>, <column_name2>, ... from <table_name1>, <table_name2> where <condition_expr>;
-- e.g. 查询每个员工的姓名, 和关联的部门名
select t_emp.name, t_dep.name from t_emp, t_dep where t_emp.dep_id = t_dep.id;

-------------
-- 显式内连接
-------------
select <column_name1>, <column_name2>, ... from <table_name1> [inner] join <table_name2> on <condition_expr>;
select e.name, d.name from t_emp as e inner join t_dep as d on e.dep_id = d.id; -- as 可省略

-------------
-- 左外连接
-------------
select <column_name1>, <column_name2>, ... from <table_name1> left [outer] join <table_name2> on <condition_expr>;
-- e.g. 查询 t_emp 表的所有数据, 和关联的部门数据 (即使某些员工没有部门, 查询结果中也会保留这些员工)
select e.*, d.* from t_emp as e left outer join t_dep as d on e.dep_id = d.id;
-- 等价于
select e.*, d.* from t_dep as d right outer join t_emp as e on e.dep_id = d.id;

-------------
-- 右外连接
-------------
select <column_name1>, <column_name2>, ... from <table_name1> right [outer] join <table_name2> on <condition_expr>;
-- e.g. 查询 t_dep 表的所有数据, 和关联的员工数据 (即使某些部门没有员工, 查询结果中也会保留这些部门)
select d.*, e.* from t_emp as e right outer join t_dep as d on e.dep_id = d.id;
-- 等价于
select d.*, e.* from t_dep as d left outer join t_emp as e on d.id = e.dep_id;

-------------
-- 自连接
-------------
select <column_name1>, <column_name2>, ... from <table_name> <alias1> join <table_name> <alias2> on <condition_expr>;
-- e.g. 自连接 + 内连接: 在 t_emp 表中, 查询每个员工的姓名, 和关联的领导的姓名
select e.name, l.name from t_emp as e, t_emp as l where e.leader_id = l.id

-- e.g. 在 t_emp 表中, 查询每个员工的姓名, 和关联的领导的姓名 (即使某些员工没有领导, 查询结果中也会保留这些员工)

-- e.g. 自连接 + 左外连接
select e.name as 'employeeName', l.name as 'leaderName' from t_emp as e left outer join t_emp as l where e.leader_id = l.id;
-- e.g. 自连接 + 右外连接
select e.name 'employeeName', l.name 'leaderName' from t_emp l right join t_emp e where e.leader_id = l.id;

联合查询

联合多个查询结果, 合并为新的结果集; 联合查询的列数, 列的类型必须相同

select <column_name1>, <column_name2>, ... from <table_name1> ...
union [all]
select <column_name1>, <column_name2>, ... from <table_name2> ...;

-- e.g.
select * from t_emp where salary < 5000
union [all]
select * from t_emp where age > 50;

-- 使用 union 时, 和 or 条件查询等价, 会去重
-- 使用 union all 时, 和 or 条件查询不等价, 不会去重
select * from t_emp where salary < 5000 or age > 50;
-- 当某个员工月薪 < 5000, 年龄也 > 50 时, 则该员工在联合查询的结果集中会出现两次

子查询

根据子查询的结果, 可以分为

• 标量子查询: 子查询的结果为 1 个值
• 列子查询: 子查询的结果为 1 列
• 行子查询: 子查询的结果为 1 行
• 表子查询: 子查询的结果为多行多列 (一张表)

根据子查询的位置, 可以分为

• where 后的子查询
• from 后的子查询
• select 后的子查询

-- 标量子查询: 子查询的结果为 1 个值
select * from t_emp
where dep_id = (
  select id from t_dep where dep_name = "Web Infra"
)

-- 列子查询: 子查询的结果为 1 列
select * from t_emp
where salary > [all | some] (
  select salary from t_emp where dep_id = (
    select id from t_dep where dep_name = "Web Infra"
  )
)

-- 行子查询: 子查询的结果为 1 行
select * from t_emp
where (salary, leader_id) = (
  select salary, leader_id from t_emp where name = "lark"
)

-- 表子查询: 子查询的结果为多行多列 (一张表)
select * from t_emp
where (job, salary) in (
  select job, salary from t_emp where name = "lark" or name = "lark2"
)

select e.*, d.* from (
  select * from t_emp where birthday >= "2002-02-28" as e left outer join t_dep as d on e.dep_id = d.id
)

事务

mysql 的事务默认自动提交

-- 查询事务隔离级别
select @@transaction_isolation;

-- 设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

-- 查询事务提交方式
select @@autocommit;
-- 设置自动提交
set @@autocommit = 1;
-- 设置手动提交
set @@autocommit = 0;

-- 开启事务
start transaction;
-- 等价于
begin;

-- 提交事务
commit;

-- 回滚事务
rollback;

索引

-- 建立索引
create [unique | fulltext] index <index_name> on <table_name> (<column>, <column2>, ...);

-- 查询索引
show index from <table_name> [\G];

-- 删除索引
drop index <index_name>, <index_name2>, ... on <table_name>;

-- id 是主键, 也是聚簇索引
-- 聚簇索引树的叶子节点存储 [id, name, age]
create table users (
  id int primary key,
  name varchar(50),
  age int
)

-- 为 name 字段建立索引 (二级索引)
-- 二级索引树的叶子节点存储 [name, id]
create index idx_name on users (name);
insert into users values (1, 'Alice', 22), (2, 'Bob', 23);

-- 先在二级索引树中找到 name = 'Bob' 的叶子节点, 叶子节点存储 [name: 'Bob', id: 2]
-- 再在聚簇索引树中找到 id = 2 的叶子节点, 叶子节点存储 [id: 2, name: 'Bob', age: 23]
select * from users where name = 'Bob'

前缀索引

计算前缀索引的区分度

create index <index_name> on <table_name> (<column_name>(n));

-- 计算前缀索引的区分度 (越接近 1 越好)
select count(distinct substring(name, 1, 5)) / count(*) from users;
create index idx_name_5 on users (name(5));

覆盖索引

联合索引的列包含查询的全部列, 查询的数据可以在二级索引的 B+ 树的叶子节点中找到时, 不需要回表查询

-- idx_name_age
-- 二级索引的叶子节点存储 [name, age, id]
-- 主键索引的叶子节点存储行数据
show index from users;

-- using where; using index: 查询时使用了索引, 并且无需回表查询
explain select name, age from users where name = 'lark' and age = 22;
-- using index condition: 查询时使用了索引, 但是需要回表查询
explain select * from users where name = 'lark' and age = 22;

create index idx_name on users (name);
select * from users where id = 2; -- 1 次查询
select id, name from users where name = 'Alice'; -- 1 次查询
select id, name, age from users where name = 'Alice'; -- 2 次查询, 需要回表查询 age

最左匹配原则

使用联合索引时, 必须包含联合索引的最左列, 并且不能跳过联合索引中间的某一列; 否则联合索引全部/部分失效

-- idx_name_age_gender
-- 二级索引的叶子节点存储 [name, age, gender, id]
-- 主键索引的叶子节点存储行数据
show index from users;

-- 联合索引有效
select * from users where name = 'lark' and age = 22 and gender = 1;
select * from users where name = 'lark' and age = 22;
select * from users where name = 'lark';

-- 未包含联合索引的最左列, 联合索引全部失效
select * from users where age = 22 and gender = 1;
select * from users where gender = 1;

-- 跳过联合索引中的 age 列, 联合索引的 gender 列部分失效 (多一次回表查询)
select * from users where name = 'lark' and gender = 1;

索引失效

• 对索引列使用「左模糊匹配」like '%xxx' 或「左右模糊匹配」like '%xxx%', 会导致索引失效
• 对索引列使用函数、表达式计算、隐式类型转换 (mysql 比较字符串和数字时, 会自动将字符串转换为数字进行比较), 会导致索引失效
• where 子句中的 or: 在 where 子句中, 如果 or 前面的是索引列, or 后面的不是索引列, 会导致索引失效

-- name 字段是二级索引

-- 对索引列使用「左」或「左右」模糊匹配
select * from users where name like 'htc%'; -- 索引有效
select * from users where name like '%ccc'; -- 索引失效
select * from users where name like '%tc%'; -- 索引失效

-- 对索引列使用函数
select * from users where length(name) = 5; -- 索引失效
-- 解决方法: 创建 length(name) 虚拟列并建立索引
alter table users add key idx_name_length ((length(name)));

-- 对索引列进行表达式计算
select * from users where id = 7 - 1; -- 索引有效
select * from users where id + 1 = 7; -- 索引失效

-- 对索引列进行隐式类转换 (phone: varchar)
-- mysql 比较字符串和数字时, 会自动将字符串转换为数字进行比较
select "10" > 9; -- 1

select * from users where phone = 15395377789; -- 索引失效
-- 原理: 等价于
select * from users where cast(phone as signed int) = 15395377789; -- 索引失效

select * from users where id = "1" -- 索引有效
-- 原理: 等价于
select * from users where id = cast("1" as signed int) -- 索引有效

where 子句中的 or: 在 where 子句中, 如果 or 前面的是索引列, or 后面的不是索引列, 则索引失效

select * from users where id = 1 or age = 7; -- 索引失效
-- 解决方法: 为 age 列建立索引
create index idx_age on users (age);

sql 提示

• use index(<index_name>) 提示 mysql 使用索引
• ignore index(<index_name>) 提示 mysql 忽略索引
• force index(<index_name>) 强制 mysql 使用索引

-- 提示 mysql 使用索引
explain select * from <table_name> use index(<index_name>) <condition_expr>;
-- 提示 mysql 忽略索引
explain select * from <table_name> ignore index(<index_name>) <condition_expr>;
-- 强制 mysql 使用索引
explain select * from <table_name> force index(<index_name>) <condition_expr>;

sql 性能分析

-- 查询 CRUD 的频率
-- Com_insert, Com_delete, Com_update, Com_select
show [session | global] status like 'Com_______';

-- 是否开启慢查询日志, 默认关闭
-- show variables like 'slow_query_log';

-- 是否开启 profiling, 默认开启
select @@have_profiling;

-- 在 session/global 级别开启 profiling
set [session | global] profiling = 1;

-- 查询每条 sql 的 queryID, 耗时, 查询语句
show profiles;

-- 查询指定 queryID 的 sql 各个阶段的耗时
show profile for query <queryID>;

-- 查询指定 queryID 的 sql 各个阶段的耗时和 cpu 占用
show profile cpu for query <queryID>;

-- 查看 select 语句的执行计划
[explain | desc] select * from <table_name>;

慢查询日志

# /etc/my.cnf 开启慢查询日志
show_query_log=1
# sql 查询时间超过 2s 时, 记录慢查询日志
long_query_time=2

# 慢日志
/var/lib/mysql/localhost-slow.log

锁

-- object_schema 数据库名
-- object_name 表名
-- index_name 索引名
-- lock_type 锁的级别: 表级锁 table, 行级锁 record
-- lock_mode 锁的类型
-- * 意向共享锁 table IS
-- * 意向排他锁 table IX
-- * 记录共享锁 record S,rec_not_gap
-- * 记录排他锁 record X,rec_not_gap
-- * 间隙共享锁 record S,gap
-- * 间隙排他锁 record X,gap
-- * 记录共享锁 record S,next-key
-- * 记录排他锁 record X,next-key
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

MVCC

Multi-Version Concurrency Control

begin; -- transaction A
select name from users where id = 2; -- transaction A; john_doe
begin; -- transaction B
update users set name = 'jane_doe' where id = 2; -- transaction B
select name from users where id = 2; -- transaction A; john_doe
commit; -- transaction B
select name from users where id = 2; -- transaction A; john_doe
-- 原理: InnoDB 默认事务隔离级别是可重复读

-- 当前读
select name from users where id = 2 lock in share mode; -- transaction A; jane_doe
select name from users where id = 2 for update; -- transaction A; jane_doe