MySQL实践 | Michael Lu’s Blog

type

status

date

slug

summary

唯一索引还是普通索引

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的 SQL 语句：select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

建唯一索引还是普通索引？

从查询语句先分析

假设，执行查询的语句是 select id from T where k=5

首先通过B+树从根节点开始搜索

一直找到叶子节点（数据页）

在数据页内用二分法定位具体记录

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

性能区别：几乎没有

关键在于InnoDB的页读取机制：

数据页（Page）是读写的基本单位

默认大小是16KB

当读取一条记录时，整个数据页都会被加载到内存

所以一旦找到k=5的记录，它所在的整个数据页已经在内存中

对于普通索引多做的操作：

仅需要一次指针移动（找下一条记录）

一次比较运算（判断是否还是k=5）

这些都是内存操作，速度极快

简单类比：

这就像在一本打开的书页上找内容

唯一索引：找到一个就知道没有了

普通索引：需要看一下下一条是不是还是它

但因为书页已经打开（在内存中），多看一眼的代价几乎可以忽略

更新过程

innodb内存模型就是buffer pool

Buffer Pool ├── 数据页缓存区（Data Pages） │ ├── Young区 │ └── Old区 ├── Change Buffer │ └── 二级索引修改缓存 └── 自适应哈希索引

A. 数据页缓存：

默认16KB一页

使用LRU算法管理

分为young区和old区

缓存表数据和索引数据

B. Change Buffer：

只对普通索引有效

减少随机I/O

提升写入性能

查询请求 ↓ 检查Buffer Pool / \ 有数据无数据 ↓ ↓ 直接返回从磁盘读入 ↓ 放入Buffer Pool

写入请求 ↓ 是否是二级索引修改？ / \ 是否 ↓ ↓ 数据页在直接写入 Buffer Pool？ / \ 是否 ↓ ↓ 直接修改写入Change Buffer

INSERT INTO t(id,k) values(1,1),(2,2)…

如果k是普通索引：

修改可以先写入Change Buffer

不需要立即读取磁盘

大大减少随机I/O

Merge过程：

当数据页被读入Buffer Pool时

会将Change Buffer中的改动合并

这个过程称为Merge

后台线程也会定期Merge

普通更新：磁盘读取 -> 修改数据 -> 写回磁盘

使用Change Buffer：记录修改到Change Buffer -> 后续合并

为什么change buffer只能是普通索引用呢？

唯一索引：

必须实时检查唯一性约束
需要读取索引页判断是否有冲突
无法推迟这个检查操作

普通索引：

无需唯一性检查
可以先把修改缓存起来
允许延迟处理

唯一索引写入：

插入记录(如 id=1) ↓ 必须立即检查是否存在id=1的记录 ↓ 需要读取索引页到内存 ↓ 如果数据页不在Buffer Pool中 ↓ 必须从磁盘读入（导致随机I/O） ↓ 进行唯一性检查 ↓ 确认无重复后才能写入

所以change buffer适合：

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统；

假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

区分change buffer和redo log

假设要插这个

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

涉及了四个部分：内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1 在内存中，直接更新内存；

Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息将上述两个动作记入 redo log 中（图中 3 和 4）。

change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？：不会

change buffer 虽然最初是在内存中，但它同时也会被记录到系统表空间(ibdata1)中。这样即使数据库发生意外关闭或掉电，这些改动记录也不会丢失。

具体的工作流程是:

当发生针对二级索引的改动时,先写入内存中的 change buffer
同时这个改动也会被写入 redo log
在 checkpoint 时,内存中的 change buffer 会被持久化到系统表空间
重启时，InnoDB 会先将 change buffer 从系统表空间加载到内存
然后根据 redo log 重做未完成的改动

给个完整一点的例子：

假设我们有一个商品库存表:

现在执行一个更新库存的操作:

让我们看看完整的执行流程:

数据页加载阶段:

InnoDB 首先检查 buffer pool，发现 id=100 这行数据所在的数据页不在内存中

从磁盘读入这个数据页到 buffer pool（这是随机读，比较慢）

由于是主键更新，必须立即执行，不能使用 change buffer

二级索引更新阶段:

update_time 发生变更，需要更新 idx_update_time 索引

由于是二级索引更新，可以使用 change buffer

InnoDB 将这个更新操作缓存在内存中的 change buffer

同时将这个 change buffer 的变更写入到系统表空间(ibdata1)

记录这次 change buffer 操作到 redo log

事务提交阶段:

将整个更新操作记录到 redo log

将 redo log 写入磁盘（顺序写，很快）

事务提交完成，返回客户端更新成功

后台异步操作:

数据页：buffer pool 中的脏页会在合适的时机刷盘

change buffer：在未来某个时刻（比如有查询访问到相关的二级索引），才会将 change buffer 中的操作合并到实际的索引页中

如果这时候发生掉电:

数据库重启恢复阶段:

首先从系统表空间加载 change buffer 到内存

读取 redo log，重放未完成的变更

此时 change buffer 的内容已经完整恢复

后续正常访问:

当有查询需要访问 idx_update_time 索引时

InnoDB 会执行 merge 操作，把 change buffer 中的变更应用到实际的索引页

用户依然能看到完整且正确的数据

这个例子展示了几个重要的设计理念:

尽量减少随机 IO：使用 change buffer 缓存二级索引的更新

确保数据安全：通过 redo log 和系统表空间双重保证

提高性能：优先处理内存操作，延迟磁盘操作

WAL(Write-Ahead Logging)机制：先写日志，再写数据页

MySQL选错索引？（todo）

怎么给字符串字段加索引

比如一个支持邮箱登陆的系统

mysql> create table SUser( ID bigint unsigned primary key, email varchar(64), ... )engine=innodb;

如使用邮箱登陆肯定有这个语句，如果没有加索引就会全局扫描

mysql> select f1, f2 from SUser where email='xxx';

同时，MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

比如，这两个在 email 字段上创建索引的语句：

mysql> alter table SUser add index index1(email);

或mysql> alter table SUser add index index2(email(6));

这样的占用空间会更小，但是可能会增加额外的记录扫描次数。

select id,name,email from SUser where email='[email protected]';

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

从 index1 索引树找到满足索引值是'[email protected]'的这条记录，取得 ID2 的值；

到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；

取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='[email protected]'的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

为什么需要循环：

循环是必要的，因为：

可能存在前缀索引的情况

即使是完整索引，由于 MySQL 的 MVCC 机制，也需要回表验证可见性

InnoDB 要确保返回的是正确且完整的数据

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是'zhangs'的记录，找到的第一个是 ID1；

到主键上查到主键值是 ID1 的行，判断出 email 的值不是'[email protected]'，这行记录丢弃；

取 index2 上刚刚查到的位置的下一条记录，发现仍然是'zhangs'，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；

重复上一步，直到在 idxe2 上取到的值不是'zhangs'时，循环结束。在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

同一条语句有时候慢有时候快

redo log—>粉板

账本—》磁盘

记忆—〉内存

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）

什么情况引发flush？

1.粉板满了

把 checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域。

性能上：这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

2.内存不足了

innodb用buffer pool管理内存

想象你有一个小白板（这就是内存—》buffer pool），白板空间有限，只能写 6 个数字。你需要经常在上面写写画画（这就是数据库的读写操作）。

干净的页 就像刚擦干净的白板，上面写的内容和笔记本(磁盘)里的完全一样。

脏页当你在白板上改了一个数字，但还没有把它抄到笔记本里，这就是"脏页"。比如白板和笔记本都写着"1"，你把白板上的"1"改成了"2"，但还没抄到笔记本里，这就形成了脏页。

为什么要及时处理脏页？ 假设你的白板满了，要写新内容：

如果要擦掉的是干净内容，直接擦了写新的就行

如果要擦掉的是改过的内容（脏页），你必须先把它抄到笔记本里，才能擦掉写新的

如果你改了很多数字都没抄到笔记本：

突然要写新内容时，需要先花很多时间抄笔记本

万一这时候停电了（数据库崩溃），改过的内容就丢了

所以数据库会：

控制脏页比例，不能改太多内容都不抄笔记本

在空闲时主动把改过的内容抄到笔记本里

实在太多脏页了，就会暂停接受新的修改，先把改过的内容抄到笔记本里

这就是为什么我们说"脏页太多会影响性能"——因为总有一天要花时间把它们抄到笔记本里！

F1: 脏页比例(max-dirty-pages-Pct)

就像刚才白板的例子，这是"已经改了但还没抄到笔记本里的内容"占总内容的比例

比如白板上10页内容，其中4页是改过的还没抄到笔记本，那脏页比例就是40%

F2: 当前日志序号和检查点的差距(checkpoint)

可以理解为"改动记录"积压了多少

就像你改了很多内容，但只是记在小本子上了，还没正式抄到笔记本里

然后：

R = max{F1, F2}

意思是取这两个值中的较大者

这个 R 值决定了要以多快的速度把脏页写入磁盘

为什么要这样设计？

如果脏页比例(F1)很高，说明内存中积压了太多改动，需要加快写入速度

如果日志差距(F2)很大，说明改动记录积压太多，也需要加快写入速度

3.系统空闲的时候

4.mysql正常关闭的时候

innodb_io_capacity

让我用一个生活中的例子来解释 innodb_io_capacity 和 IOPS：

想象你是一个抄写员：

你的抄写速度（一分钟能抄多少字）就是 IOPS

innodb_io_capacity 就是告诉系统你的实际抄写速度

举个具体例子：

假设你实际抄写速度是每分钟100字

但你告诉系统你能抄200字/分钟（innodb_io_capacity设置过高）

结果：

系统以为你能快速处理，就允许积累更多脏页

但实际你抄不了那么快

最后导致大量内容积压，系统性能下降

反过来：

如果你说你只能抄50字/分钟（innodb_io_capacity设置过低）

结果：

系统过于保守，不敢让脏页积累

频繁要求你抄写

浪费了你的实际抄写能力

所以正确设置 innodb_io_capacity：

用 fio 工具测试磁盘真实 IOPS

将这个实际值设置给 innodb_io_capacity

这样系统就能根据你的真实"抄写能力"来合理安排工作

为什么表数据删掉一半，表文件大小不变

innodb_file_per_table 控制的表数据放的位置：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。（最好是ON，因为放在共享表，删了表，空间也不回收）

数据页和记录复用对比

单条记录的删除和复用：

当删除R4这条记录时，InnoDB只是把这条记录标记为"已删除"

这个位置可以被新的记录复用，但仅限于这个位置

比如要插入一个ID=450的新记录，就可以复用R4的位置

但是这种删除不会释放物理空间

整个数据页的删除和复用：

如果一个数据页上的所有记录都被删除了

这整个数据页就可以被完全释放

这个数据页可以被用来存储任何新的记录，不限制具体位置

这就像是得到了一个全新的空白页，使用更灵活

主要区别：

记录复用：只能复用特定位置，就像座位表中标记删除的一个座位

数据页复用：可以完全重新规划使用，就像得到一个全新的空白教室

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞

插入数据页会导致页的分裂，造成空洞。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。—》所以需要重建表

online ddl

解决空洞————〉直接搞个新的表重建表

DDL(Data Definition Language)是数据定义语言，用于修改数据库结构，而不是数据本身。

与DML(增删改查数据)的区别是DDL修改的是表结构，而不是表中的数据。

锁处理：

开始时获取MDL写锁

快速降级为读锁

允许数据修改，阻止其他DDL

数据处理：

建立临时文件

复制原表数据

记录变更日志

应用日志更新

最终替换文件

业务影响：

表始终可用

支持实时增删改

仅短暂加锁

对业务基本无感知

INPLACE vs COPY:

COPY：在Server层创建临时表

INPLACE：在InnoDB内部处理，看起来像"原地"操作

临时空间需求：

即使是INPLACE也需要临时文件空间

1TB表需要约2TB总空间

INPLACE和ONLINE关系：

ONLINE必定是INPLACE

INPLACE不一定是ONLINE

如添加全文索引：是INPLACE但非ONLINE

count(*)

count() 的实现方式

你首先要明确的是，在不同的 MySQL 引擎中，count() 有不同的实现方式。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

用缓存系统保存计数有丢失数据和计数不精确：

数据库里多一个表来做：

虽然会话 B 的读操作仍然是在 T3 执行的，但是因为这时候更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见。因此，会话 B 看到的结果里，查计数值和“最近 100 条记录”看到的结果，逻辑上就是一致的

按效率从高到低排序:

COUNT(*): 优化最好，不取值，直接累加

COUNT(1): 不取值，返回常数

COUNT(主键): 需要解析数据行返回id

COUNT(NOT NULL列): 需要解析返回具体字段

COUNT(可NULL列): 需要额外判断NULL

order by怎么工作

比如用这个：

select city,name,age from t where city='杭州' order by name limit 1000 ;

全排序字段

按name排序可能在内存也可能需要外部排序，取决于sort buffer size大小。

就像是在处理一叠纸牌：

内存排序就像手里的牌少，可以一次性摊开排序

外部排序就像牌太多，需要先分成几堆分别排序，最后再合并

rowid排序

区别：

全字段排序像是复印完整文件后排序

RowID排序像是只记录文件名和位置排序，需要时再去原处取文件

如果排序字段较小，回表成本高，适合全字段排序

如果排序字段较大，内存有限，适合RowID排序

如果用了联合索引：

alter table t add index city_user(city, name);

这就像是一本按城市分章节的通讯录：

每个城市（chapter）内的名字都已经按字母顺序排好

直接翻到"杭州"章节

顺序读取就自动是按名字排序的

正确显示随机消息

假设做一个：

英语学习 App 首页有一个随机显示单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。

mysql> select word from words order by rand() limit 3;

内存临时表

怎么执行？

mysql优先按rowid排序来找

R就是rand()为每个word生成的随机浮点数

位置信息是什么：

位置信息的关键作用：

作为"指针"：

指向内存临时表中的具体行

用于快速定位原始数据

避免了存储完整的word字段

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

MySQL不同引擎的行数据定位方式

磁盘临时表

优先排序算法mysql5.6之后

归并排序：把10000个人排成队，然后只要前3名

优先队列：维护一个3人的获奖名单，有更好的就替换掉

所以用这种方式来优化的：

随机排序方法：

mysql> select count(*) into @C from t; set @Y1 = floor(@C * rand()); set @Y2 = floor(@C * rand()); set @Y3 = floor(@C * rand()); select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行 select * from t limit @Y2，1； select * from t limit @Y3，1；

为什么会比order by rand更小代价？

ORDER BY RAND()：把所有号码排序后抽前3个

随机位置抽样：直接随机生成3个号码去抽奖