平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL数据库建表与采用优化实践教学”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。
实际处理时，干后端这行多年，我越来越有一个体会：MySQL 的性能问题，十有八九在写下第一行 CREATE TABLE 的时候就已经注定了。剩下的那一两成，才轮到 SQL 编写和运维调优来操心。可现实是，太多团队在项目初期对表结构随手一建，字段类型全凭感觉，索引想起哪个加哪个，等到数据量上来、接口开始超时了，才急急忙忙找人"优化数据库"。
落到代码里，这篇文章是我这些年踩坑、填坑、反复验证之后攒下来的一份完整笔记。从存储引擎怎么选、每个字段类型背后藏着什么门道、主键和索引到底该怎么设计，到 SQL 怎么写才不踩雷、事务和锁怎么控制、参数怎么调、架构怎么搭，甚至一些看起来不起眼但真出了事就是 P0 的特殊场景，我都会尽量掰开了揉碎了讲。
一、存储引擎：这个选择其实已经没什么好纠结的了
理解这一步时，MySQL 是个插件式存储引擎架构，InnoDB、MyISAM、Memory、Archive、CSV……名字能列一长串。但说句实在话，到了 2024 年，这个选择题的答案已经轻松到不能再轻松：用 InnoDB，没有第二个选项。
理解这一步时，InnoDB 兼容事务、行级锁、外键约束、崩溃后自动恢复、MVCC 同时发读，是 MySQL 5.5 之后的默认引擎。你能想到的任何 OLTP 业务——电商下单、社交发帖、、SaaS 多租户——它都能胜任，而且表现得很好。
实际处理时，MyISAM 是上一代默认引擎，不兼容事务，只有表级锁，崩溃之后没有自动恢复机制，得靠 myisamchk 手动修。它唯一的优势是 COUNT(*) 快（因为存了行数），以及全文索引在很早的版本里比 InnoDB 成熟。但这些优势在今天已经完全不值一提了。如果你接手一个老项目，发现某些表还在用 MyISAM，找机会迁移过来：
ALTER TABLE old_table ENGINE = InnoDB;
理解这一步时，这条语句会重建整张表。如果表很大（比如上千万行），直接执行会锁表很久，影响线上业务。这时候要用在线 DDL 工具，后面会专门讲。
实际处理时，Memory 引擎把数据全部放在内存里，重启就没了。偶尔用来做中间临时表还行，别的场景别碰。Archive 引擎压缩率很高但只兼容 INSERT 和 SELECT，不兼容更新和删除，极个别日志归档场景可能用得上。CSV 引擎把数据存成 CSV 文件，基本没有实际业务价值。
理解这一步时，所以这一节其实很短。记住 InnoDB 就够了，把精力留给后面真正复杂的部分。
二、数据类型：差一个字节，到了亿级就是差几个 GB
在这个场景下，数据类型是建表时最基础的决定，也是最容易被"差不多就行"心态毁掉的地方。选错类型，轻则浪费磁盘和内存，重则查询性能断崖式下跌，甚至埋下数据精度丢失的定时炸弹。我按照类别一个一个说。
2.1 整数：别动不动就 INT
MySQL 给了五种整数类型，从 1 字节到 8 字节：
| 类型 | 字节数 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32,768 ~ 32,767 | 0 ~ 65,535 |
| MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 |
| INT | 4 | -21亿 ~ 21亿 | 0 ~ 42亿 |
| BIGINT | 8 | ±9.2×10¹⁸ | 0 ~ 1.8×10¹⁹ |
选择原则就一句话：能装下业务数据的最小类型，就是最好的类型。
理解这一步时，状态字段、开关标志、枚举编号，用 TINYINT UNSIGNED，0 到 255 足够了。年龄、小计数器，SMALLINT UNSIGNED。绝大多数业务 ID、普通计数，INT UNSIGNED。雪花算法生成的 ID、毫秒级时间戳、特别大的计数，上 BIGINT UNSIGNED。
从实现思路看，这里有一个被误解了无数次的点，我必须单独拎出来讲清楚：INT(11) 里面那个 11，不是长度限制落到代码里，。它不会限制你能存多大的数字，INT(1) 照样能存 21 亿。这个括号里的数字只在配合 ZEROFILL 的时候才有意义——它决定显示的时候前面补几个零。MySQL 8.0.17 已经正式废弃了这个显示宽度语法，新建表的时候请不要再写了。
另一个建议：如果字段永远不可能是负数，加上 UNSIGNED。INT SIGNED 上限 21 亿，INT UNSIGNED 上限 42 亿，白白多了一倍的空间，何乐而不为？用户 ID、订单数量、库存数量，这些字段不可能是负数，统统加 UNSIGNED。
布尔值用 TINYINT(1)。我见过有人用 ENUM('true','false') 存布尔，有人用 VARCHAR(5) 存字符串 “true”，还有人用 CHAR(1) 存 ‘Y’/‘N’。一个字节就能解决的事情，别给自己找麻烦。TINYINT(1) 在绝大多数 ORM 和驱动里都会被自动映射为布尔类型，生态兼容性最好。
2.2 浮点数：金额字段用 FLOAT 是要出事的
这条我要用加粗加大字号喊出来：结合项目来看，FLOAT 和 DOUBLE 是近似类型，存在精度损失，绝对、绝对、绝对不能用来存金额、回了、计费、余额这类对精度有严格要求的数据。
-- 千万别这么干
price FLOAT NOT NULL DEFAULT 0;
balance DOUBLE NOT NULL DEFAULT 0;
-- 正确做法
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;
DECIMAL(M, D) 是精确数值类型。M 是总位数，D 是小数位数。DECIMAL(10,2) 能存的最大值是 99999999.99。它内部以压缩的二进制格式存储，做加减乘除的时候不会出现 0.1 + 0.2 ≠ 0.3 这种浮点误差。
DECIMAL 的代价是存储空间比 FLOAT/DOUBLE 大，运算速度也稍慢。但对于金额字段来说，这点代价完全能够忽略。你总不能为了省几个字节，让用户的账算错一分钱吧？
在这个场景下，如果你的业务对精度要求到了极端程度——比如加密货币交易要精确到小数点后 8 位甚至 18 位——那能够考虑用 BIGINT 存最小单位。比如金额不存"元"，存"分"；比特币不存"BTC"，存"聪"（1 BTC = 100,000,000 聪）。这样所有运算都是纯整数运算，速度最快，也不存在任何精度问题。展示的时候在应用层做一次除法就行了。
2.3 字符串：VARCHAR(255) 不是万能答案
从实现思路看，字符串是日常开发中用得最多的类型，也是"图省事"重灾区。我见过太多表，所有字符串字段一律 VARCHAR(255)，问他为什么，答曰"够用了"。够用是够用了，但你考虑过排序的时候内存怎么分配的吗？
先说清楚几种字符串类型的区别：
CHAR(N)：定长。不管你实际存了多少个字符，它都占 N 个字符的空间，不足的用空格补齐。最大 255。适合长度完全固定的场景：MD5 哈希（32 位）、SHA256（64 位）、国家代码（2 位）、手机号（11 位）。因为定长，MySQL 处理它的时候不需额外的长度前缀，也不需处理变长偏移，读写都快那么一丢丢。
VARCHAR(N)：变长。实际占用 = 实际数据的字节数 + 1 或 2 字节的长度前缀（数据不超过 255 字节用 1 字节前缀，超过用 2 字节）。理论最大 65535 字节，但受行大小限制（InnoDB 单行最大约 65535 字节，刨去其他字段，实际远达不到）。适合绝大多数文本：用户名、邮箱、地址、标题、备注。
TEXT 家族：TINYTEXT（最大 255 字节）、TEXT（64KB）、MEDIUMTEXT（16MB）、LONGTEXT（4GB）。用来存大文本，比如文章正文、富文本内容、大段 JSON。
BLOB 家族：跟 TEXT 对应的二进制版本。存图片、文件、加密后的二进制数据。
选择的时候，有几个坑需留意：
第一，VARCHAR 的 N 不要无脑写 255。 实际处理时，为什么？因为 MySQL 在做排序（filesort）、新建内存临时表、采用 MEMORY 引擎的时候，对 VARCHAR 列是按照声明的最大长度来分配内存的。你声明 VARCHAR(255)，哪怕实际存的内容只有 3 个字符，排序的时候也会按 255 个字符来分配空间。如果 utf8mb4 编码，一个字符最多 4 字节，那就是 1020 字节。乘以排序的行数，内存消耗很可观。用户名写 VARCHAR(50) 就够了，邮箱 VARCHAR(100) 够了，订单号 VARCHAR(32) 够了。按实际业务上限来定，别偷懒。
第二，TEXT 和 BLOB 不要放在主表里。 落到代码里，这个我后面在"垂直拆分"那一节还会再讲，这里先说结论。InnoDB 默认页大小 16KB，如果一行数据里塞了个大 TEXT，很可能触发"行溢出"——实际数据被存到单独的溢出页，聚簇索引里只留一个 20 字节的指针。这意味着你扫描主表的时候，哪怕根本没 SELECT 那个 TEXT 字段，也可能因为行格式的问题产生额外的随机 IO。正确做法是把大字段拆到扩展表，主表保持精干。
第三，固定长度的字符串用 CHAR 比 VARCHAR 好。 结合项目来看，手机号固定 11 位，身分证固定 18 位，用 CHAR(11) 和 CHAR(18) 比 VARCHAR 少一个字节的前缀开销，而且不需处理变长偏移。
第四，IP 地址别用 VARCHAR(15)。 用 INT UNSIGNED（4 字节）配合 INET_ATON() 和 INET_NTOA() 两个函数：
-- 写入
INSERT INTO access_log (ip_int) VALUES (INET_ATON('192.168.1.100'));
-- 读取
SELECT INET_NTOA(ip_int) AS ip FROM access_log;
-- 范围查询（比如查某个 C 段）
SELECT * FROM access_log
WHERE ip_int BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');
比 VARCHAR 省空间、比字符串比较快、还兼容范围查询，一举三得。
2.4 日期和时间：别让 2038 年成为你的噩梦
| 类型 | 字节 | 范围 | 时区 |
|---|---|---|---|
| DATE | 3 | 1000-01-01 ~ 9999-12-31 | 无 |
| TIME | 3 | -838:59:59 ~ 838:59:59 | 无 |
| DATETIME | 5（5.6.4+）/ 8（旧版） | 1000-01-01 ~ 9999-12-31 | 无，存什么读什么 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 | 存入转 UTC，读出按会话时区转 |
| YEAR | 1 | 1901 ~ 2155 | 无 |
怎么选？
实际处理时，需跨时区的（国际化业务、用户在多个时区）→ TIMESTAMP。它存的时候转成 UTC，读的时候按当前会话的时区转回来，天然兼容多时区。
结合项目来看，纯业务时间，不涉及跨时区 → DATETIME。你存进去 2024-06-01 12:00:00，读出来就是 2024-06-01 12:00:00，不做任何转换。
只关心日期不关心时分秒 → DATE。生日、合同签署日、统计日期，用 DATE 就好，别浪费空间存个 00:00:00。
需毫秒甚至微秒精度 → DATETIME(3) 或 DATETIME(6)，MySQL 5.6.4 开始兼容小数秒。
这里有一个必须知道的坑：结合项目来看，TIMESTAMP 的上限是 2038 年 1 月 19 日 03:14:07 UTC。 结合项目来看，底层是 32 位 Unix 时间戳，到那天就溢出了。如果你的数据可能存到 2038 年以后——保鲜合同到期日、房产产权到期日、某些长期计划的截止日期——必须用 DATETIME。我见过一个做保鲜系统的团队，所有日期字段都用了 TIMESTAMP，等到有人发现 2038 年的时候数据存不进去，已经积累了好几亿条记录，迁移成本巨大。
还有一条铁律：永远不要用 VARCHAR 存时间字符串。 从实现思路看，“2024-06-01 12:00:00” 这种字符串没法做范围比较（字符串比较和日期比较是两回事）、没法做日期运算（DATE_ADD、DATEDIFF 全用不了）、索引效率也低。用对类型，让数据库帮你做时间计算。
2.5 枚举和集合：能不用就不用
MySQL 提供了 ENUM 和 SET 两种类型。ENUM 是单选，最多 65535 个值；SET 是多选，最多 64 个成员。
我的建议是：尽量别用。
落到代码里，ENUM 最大的问题是改起来麻烦。你要新增一个枚举值，得 ALTER TABLE。MySQL 8.0 允许在末尾追加新值而不重建表，这算是一个改进。但如果你要修改中间某个值的名称、删除一个值、调整顺序，对不起，重建表。对于一个几千万行的表来说，这个代价是巨大的。
在这个场景下，而且 ENUM 内部是用整数编号存储的，排序的时候按编号排而不是按字面值排，这个行为经常让人困惑。调试的时候看到数据库里存的是 1、2、3 而不是 ‘pending’、‘paid’、‘shipped’，排查问题也不便于。
在这个场景下，SET 就更别提了。位运算存储，查询和调试都很痛苦，而且最多 64 个成员，扩展性极差。
更好的做法：用 TINYINT UNSIGNED 存状态码，在应用层维护枚举映射（Java 的 enum、Go 的 const、Python 的 Enum 都行）。或者建一张字典表，用外键关联。这样新增状态只需在字典表里加一行数据，不需动表结构，运维成本几乎为零。
2.6 JSON：好用，但别滥用
从实现思路看，MySQL 5.7 引入了原生 JSON 类型，8.0 进一步增强了 JSON 函数和操作符。它适合存那些 schema 不固定、结构经常变化的半结构化数据：用户自定义设置、第三方回调的原始报文、动态表单、A/B 实验参数。
CREATE TABLE user_preferences (
user_id BIGINT UNSIGNED NOT NULL,
settings JSON DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查询的时候能够用 JSON_EXTRACT 或者 ->> 操作符：
SELECT * FROM user_preferences WHERE settings->>'$.theme' = 'dark';
SELECT settings->>'$.notifications.email' FROM user_preferences WHERE user_id = 1001;
但这里有一个很关键的性能问题：JSON 列不能直接建索引。 实际处理时，若你需频繁按 JSON 里的某个字段做查询，必须新建虚拟生成列，随后在生成列上建索引：
ALTER TABLE user_preferences
ADD COLUMN theme VARCHAR(20) GENERATED ALWAYS AS (settings->>'$.theme') VIRTUAL;
CREATE INDEX idx_theme ON user_preferences(theme);
这样 WHERE settings->>'$.theme' = 'dark' 就能走索引了。
但我说句掏心窝子的话：如果一个字段你已经需频繁拿来当查询条件了，它大概率不应该继续待在 JSON 里。直接提出来做一个独立的列，建上索引，比折腾虚拟生成列干净得多。JSON 适合存那些"偶尔需看一眼、但基本不拿来做查询条件"的数据。把它当成一个结构化的 TEXT 来用，而不是当成一个关系型列来用。
三、字段设计的几条通用原则
3.1 能 NOT NULL 就不要允许 NULL
从实现思路看，这是我建表时的第一信条。除非业务语义上确实需区分"没有值"和"值为空字符串/零"（这种场景其实很少见），否则一律 NOT NULL 加默认值。
结合项目来看，NULL 在 MySQL 里的代价是多方面的，而且很多代价是隐性的，你平时感觉不到，数据量上来之后才会慢慢显现：
在这个场景下，每行需额外的比特位来标记哪些列是 NULL。一列占 1 bit，十列占 10 bit。看着不多，但乘以几亿行就是实实在在的存储开销。
实际处理时，索引里 NULL 值也需占位。B+Tree 里，NULL 是作为一个特殊值存在的。COUNT(col) 会跳过 NULL 行，COUNT(*) 不会。这个差异在业务逻辑里经常导致统计结果跟预期对不上，随后排查半天发现是 NULL 在作祟。
结合项目来看，联合索引里，如果某列有大量 NULL，优化器对索引的选择可能出问题。IS NULL 虽然能够走索引，但在复杂条件组合里（比如 WHERE a IS NULL AND b = 1 AND c > 10），优化器的判断逻辑会变得微妙。
还有一个很现实的工程问题：NULL = NULL 的结果不是 TRUE，是 NULL。这个三值逻辑让无数新手在 WHERE 条件里翻过车。NOT IN 子查询里如果包含 NULL，整个结果集直接变空。这些都是经典的、反复出现的、本能够在建表时就避免的坑。
-- 我推荐的写法
name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
avatar_url VARCHAR(500) NOT NULL DEFAULT '' COMMENT '头像地址',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
结合项目来看，空字符串、零值、默认时间戳——这些在绝大多数业务场景下都能完美替代 NULL 的语义，而且不会带来上面说的那些副作用。
3.2 每个字段都要写 COMMENT
实际处理时，这不是可选的，是必须的。我知道，写注释很烦，赶进度的时候谁也不想多打几个字。但你想想，半年之后你自己回来看这张表，status 字段的 0、1、2、3、4 分别代表什么，你还记得清吗？团队新来的同事能猜得出来吗？
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0
COMMENT '订单状态: 0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-已退款',
一行注释，省掉无数次"这个字段啥意思"的群聊问答。这笔账怎么算都划算。
3.3 冗余字段：一个需权衡的话题
在这个场景下，教科书会告诉你遵循第三范式，消除一切冗余。但在真实的业务系统里，适度的反范式冗余往往能大幅减少 JOIN，显著提升查询性能。
常用的合理冗余：订单表里冗余商品名称和商品图片（因为商品名可能改，但订单快照不应该变）；评论表里冗余用户昵称（避免每次查评论都 JOIN 用户表）；数据仓库里的宽表（提前把维度拍平，查询时不再需多表关联）。
实际处理时，冗余的代价是数据一致性需应用层来保障。商品名改了，订单里的冗余名称要不要同步更新？这取决于业务语义。如果是"下单时的快照"，那就不更新；如果是"始终展示最新名称"，那就得在商品更新的逻辑里同步改订单表，或者用 binlog 异步同步。
实际处理时，这个取舍没有标准答案，取决于查询频率、更新频率、一致性要求。但我的经验是：对于读多写少的维度信息（用户名、商品名、分类名），冗余进来是值得的；对于频繁变更且对一致性要求高的数据（库存、余额），别冗余，老老实实 JOIN 或者走缓存。
四、主键设计：这个决定会影响表的整个生命周期
结合项目来看，InnoDB 是聚簇索引组织表。什么意思呢？就是数据本身按照主键的顺序，物理存储在 B+Tree 的叶子节点上。主键不仅仅是一个"唯一标识"，它决定了数据在磁盘上的物理排列方式。这个选择的影响是深远的、全局的、不可逆的（改主键等于重建表）。
4.1 自增 BIGINT：绝大多数场景的最优解
BIGINT UNSIGNED AUTO_INCREMENT 作为主键，几乎是单机和主从架构下的标准答案。原因很朴素：
顺序写入。 落到代码里，新数据的 ID 永远比已有数据大，所以永远追加在 B+Tree 的最右边。不需在中间找位置插入，不会触发页分 裂。写入路径是纯粹的顺序追加，对磁盘最友好，对 Buffer Pool 最友好。
索引紧凑。 从实现思路看，InnoDB 的每个二级索引的叶子节点都存储主键值。BIGINT 是 8 个字节。如果主键是 36 字节的 UUID，那每个二级索引的每条记录都多存 28 个字节。一张表有 5 个二级索引、1 亿行数据，光这个差异就多占 14GB。
范围查询高效。 数据物理有序，WHERE id BETWEEN 1000 AND 2000 只需定位到起点，随后沿着叶子节点的双向链表顺序读就行了，不需任何随机跳转。
Buffer Pool 命中率高。 热点数据集中在 B+Tree 的右侧，缓存效率高。
4.2 UUID 做主键：一个代价高昂的选择
从实现思路看，这个问题我被问过不下几十次，也亲眼见过好几个团队因为用 UUID 做主键而踩了大坑。我把问题掰开了说：
随机写入导致页分 裂。 实际处理时，UUID 是无序的（至少 v4 是），每次插入的新记录可能落在 B+Tree 的任意位置。如果一个页已经满了，新记录要插进去，就得把页里的数据搬一半到一个新页里。这就是页分 裂。页分 裂意味着额外的写操作、额外的空间碎片、额外的维护开销。在高同时发写入场景下，页分 裂会让写入性能下降 30% 甚至更多。
索引膨胀。 落到代码里，前面说了，每个二级索引都存主键。36 字节的 UUID 对比 8 字节的 BIGINT，每个二级索引每条记录多 28 字节。索引越大，能装进 Buffer Pool 的比例就越小，缓存命中率就越低，查询就越慢。这是一个恶性循环。
数据碎片化。 从实现思路看，随机分布的数据导致范围查询需大量的随机 IO。顺序读和随机读的性能差距，在机械盘上是几个数量级，在 SSD 上也有几倍的差距。
从实现思路看，如果你确实需分布式全局唯一 ID——比如微服务架构下多个服务都要生成 ID，不想依赖一个中心化的发号器——那建议用雪花算法（Snowflake）或者它的变体（美团 Leaf、百度 UidGenerator）。它们生成的是 64 位整数，时间戳在高位，所以整体是趋势递增的，兼顾了唯一性和有序性。
4.3 无主键表：一个隐蔽的坑
实际处理时，若你建表的时候忘了定义主键，InnoDB 不会报错，但它会在内部做一些你不一定想要的事情：先尝试找一个非空的唯一索引来当聚簇索引；如果连这个都没有，就自动生成一个隐藏的 6 字节 ROW_ID 作为聚簇索引。
结合项目来看，这个隐藏的 ROW_ID 有一个很要命的特点：它是全局共享一个计数器的。所有没有显式主键的 InnoDB 表，共用同一个 ROW_ID 序列。在高同时发插入的场景下，这个全局计数器会成为一个互斥锁争用的热点，拖慢所有相关表的写入性能。
所以，每张表都必须显式定义主键。 没有例外。
五、索引设计：MySQL 优化最核心的战场
落到代码里，若说数据类型是地基，主键是骨架，那索引就是 MySQL 性能优化的灵魂。一条合适的索引能够让查询从扫描一千万行变成只读三行，性能差距是六个数量级。但索引也不是免费的午餐——每个索引都要占磁盘空间，每次写入都要同步维护。设计索引，本质上是在读性能和写性能之间找平衡。
5.1 先搞懂 B+Tree
落到代码里，InnoDB 的索引用的是 B+Tree。理解它的几个关键特性，后面很多设计决策就自然明白了：
非叶子节点只存键值，不存数据。 落到代码里，一个 16KB 的页，如果键值是 8 字节的 BIGINT 加上 6 字节的指针，大约能放 1170 个键值。三层树就能索引大约 1170 × 1170 × 16 ≈ 两千万行数据。四层树能到两百亿行。所以绝大多数表的索引树只有三到四层，一次查询最多三到四次磁盘 IO。这就是索引能把查询从秒级拉到毫秒级的根本原因。
所有数据都在叶子节点。 聚簇索引的叶子存完整的行数据，二级索引的叶子存索引列的值加上主键值。
叶子节点借助双向链表串起来。 落到代码里，范围查询定位到起点之后，沿着链表顺序往后读就行了，不需回到父节点重新找路。这对 BETWEEN、>、<、ORDER BY 这类操作很友好。
5.2 联合索引：最常用也最容易设计错的索引
在这个场景下，联合索引（复合索引）是日常工作中用得最多的索引形式。一个联合索引 (a, b, c) 在 B+Tree 里的排列规则是：先按 a 排序，a 相同的按 b 排序，b 也相同的按 c 排序。
落到代码里，这就引出了最左前缀匹配原则：查询条件必须从最左边的列开始，连续匹配，才能借助这个索引。
-- 索引 (a, b, c)
WHERE a = 1 -- ✅ 用到 a
WHERE a = 1 AND b = 2 -- ✅ 用到 a, b
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 用到 a, b, c
WHERE a = 1 AND c = 3 -- ⚠️ 只用到 a（跳过了 b，c 用不上）
WHERE b = 2 -- ❌ 完全用不上
WHERE b = 2 AND c = 3 -- ❌ 完全用不上
WHERE c = 3 -- ❌ 完全用不上
设计联合索引的时候，有几条规则我反复验证过，确实管用：
等值条件的列放前面，范围条件的列放最后。 因为范围条件之后的列没法继续借助索引的有序性。
-- 查询：WHERE status = 1 AND type = 2 AND create_time > '2024-01-01'
-- ✅ INDEX(status, type, create_time) -- 三列全用上
-- ❌ INDEX(status, create_time, type) -- type 用不上，因为 create_time 是范围
在等值条件列之间，区分度高的放前面。 落到代码里，区分度就是不重复值的数量除以总行数。用户 ID 的区分度接近 1（几乎每个值都不重复），性别的区分度是 0.5（假设男女各半），状态字段的区分度可能只有 0.001。所以 (user_id, status) 比 (status, user_id) 过滤效率更高。
考虑 ORDER BY 和 GROUP BY。 若查询经常是 WHERE a = ? ORDER BY b，那 (a, b) 这个索引既能过滤又能排序，一举两得，省掉了 filesort。这种"一个索引同时服务过滤和排序"的设计，是性能优化的甜区。
不要建冗余索引。 有了 (a, b) 就不需再单独建 (a)，因为 (a, b) 已经覆盖了 (a) 的全部能力。但反过来不行，(a, b) 不能替代 (b)。我见过不少表，索引列表里 (a) 和 (a, b) 同时存在，纯粹是浪费空间和写入性能。
5.3 覆盖索引：能做到的话，一定要做到
在这个场景下，当查询需的所有列都包含在某个索引里的时候，MySQL 能够直接从索引树得到结果，完全不需回表去读聚簇索引。在 EXPLAIN 里，这表现为 Extra: Using index。
-- 假设有索引 idx_status_name_age (status, name, age)
SELECT name, age FROM users WHERE status = 1;
-- status 用来过滤，name 和 age 直接从索引里取，不需要回表
结合项目来看，覆盖索引的收益在回表代价大的时候尤其明显。如果表很宽（每行几百甚至上千字节），回表意味着从聚簇索引里按主键做一次随机读，把整行数据拉出来，随后只取其中两个字段。这个过程的 IO 量跟索引扫描完全不是一个量级。覆盖索引把"索引扫描 + 随机回表"变成了纯粹的"索引顺序扫描"，性能提升可能是十倍甚至几十倍。
从实现思路看，但也不要走极端。覆盖索引意味着索引里要包含更多的列，索引本身会变大，写入时的维护成本也会增加。通常覆盖索引适合那些查询频率极高、得到列数有限的场景。
5.4 前缀索引：长字符串的折中方案
结合项目来看，对于很长的 VARCHAR 字段——比如 URL、邮箱、文件路径——全字段索引会很大。能够只对前 N 个字符建索引：
-- 先看看不同前缀长度的区分度
SELECT
COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel_6,
COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
COUNT(DISTINCT LEFT(email, 14)) / COUNT(*) AS sel_14,
COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
如果 sel_10 已经达到了 0.97 以上（也就是前 10 个字符就能区分 97% 的行），那用前缀 10 就够了：
ALTER TABLE users ADD INDEX idx_email (email(10));
在这个场景下，前缀索引有两个限制要知道：它不能用来覆盖索引（因为索引里没有完整的值），也不能用来 ORDER BY 和 GROUP BY（因为前缀不能代表完整的排序依据）。
5.5 索引数量：克制是一种美德
从实现思路看，索引加速了读，但拖慢了写。每次 INSERT、UPDATE、DELETE，MySQL 都要同步维护所有的索引。一张表上有 10 个索引，写入性能可能只有 1 个索引时的三分之一甚至更低。
我的经验法则是：单表索引控制在 5 到 6 个以内。 若你发现一张表有十几个索引，大概率存在冗余索引或者过度优化。
结合项目来看，定期清理不用的索引。MySQL 8.0 配合 Performance Schema 能够查看哪些索引从来没被查询用过：
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';
落到代码里，删索引之前如果不放心，能够先把它设为不可见（8.0 的特性），观察一段时间：
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;
-- 跑一两周，确认没有查询受影响
DROP INDEX idx_old ON orders;
这比直接删了再急急忙忙加回来要稳妥得多。
5.6 函数索引（8.0.13+）
从实现思路看，有些查询确实需对列做函数运算，比如按年月分组。在 8.0.13 之前，这种查询没法走索引。8.0.13 之后能够建函数索引：
ALTER TABLE orders ADD INDEX idx_year_month
((YEAR(create_time) * 100 + MONTH(create_time)));
-- 这个查询就能走索引了
SELECT * FROM orders
WHERE YEAR(create_time) * 100 + MONTH(create_time) = 202406;
注意：查询里的表达式必须和索引定义里的表达式完全一致，一个字都不能差，否则优化器认不出来。
六、字符集和排序规则：不起眼，但踩了就是大坑
落到代码里，字符集问题平时不显山不露水，但一旦踩坑，表现往往是"索引莫名其妙不生效"或者"JOIN 性能突然崩了"，排查起来很折磨人。
结论先说：用 utf8mb4，不要用 utf8。
MySQL 里的 utf8 其实是 utf8mb3，最多只兼容 3 字节的 UTF-8 字符。这意味着它存不了 emoji 表情，存不了部分生僻汉字（比如"?"），也存不了一些少数民族文字。utf8mb4 才是真正的 UTF-8，最多 4 字节，能覆盖所有 Unicode 字符。MySQL 8.0 已经把默认字符集改成了 utf8mb4，如果你还在用 5.7，建表的时候请务必显式指定。
排序规则（Collation）决定字符怎么比较和排序：
utf8mb4_general_ci：比较快，但不够精确。比如它不区分某些带重音的字符。MySQL 5.7 的默认值。
utf8mb4_0900_ai_ci：基于 Unicode 9.0 标准，比较规则更准确。ai 是 accent insensitive（不区分重音），ci 是 case insensitive（不区分大小写）。MySQL 8.0 的默认值。
utf8mb4_bin：按二进制值比较，大小写敏感。需精确匹配的场景用这个。
从实现思路看，建表的时候显式写出来，别依赖默认值（不同版本默认值不一样，跨版本迁移的时候容易出问题）：
CREATE TABLE t (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
最大的坑：表与连接、表与表之间的字符集必须一致。 如果表是 utf8mb4 但连接是 utf8，或者两张表在 JOIN 的时候字符集不同，MySQL 会在比较的时候做隐式字符集转换。这个转换相当于对列加了一个 CAST 函数，索引直接失效，退化为全表扫描。
-- 检查当前连接的字符集
SHOW VARIABLES LIKE 'character_set%';
-- 确保客户端、连接、结果集都是 utf8mb4
SET NAMES utf8mb4;
结合项目来看，在应用层的数据库连接设置里，也要确保连接字符集是 utf8mb4。Java 的 JDBC URL 加 characterEncoding=utf8（JDBC 里 utf8 实际对应 utf8mb4），Go 的 DSN 加 charset=utf8mb4，Python 的 connector 指定 charset='utf8mb4'。
七、表结构设计模式：当单表扛不住的时候
7.1 垂直拆分：把表切"瘦"
在这个场景下，当一张表的列很多，或者有些列是大字段（TEXT、BLOB）、有些列访问频率很低的时候，把表纵向切开：
-- 主表：高频访问，行小
user(id, name, phone, email, status, create_time)
-- 扩展表：低频访问，可能有大字段
user_detail(user_id, bio TEXT, avatar_url VARCHAR(500), address VARCHAR(200), ...)
理解这一步时，好处很直接。主表的每一页能装更多行，Buffer Pool 同样大小能缓存更多有效数据，全表扫描和索引扫描的 IO 量都会减少。大字段被隔离到扩展表，只有在确实需的时候才去读取。大部分业务场景下，查用户列表只需主表的几个字段，根本不会碰扩展表。
7.2 水平拆分：把表切"碎"
实际处理时，当单表数据量到了千万级甚至亿级，不管索引设计得多精巧，单机的磁盘容量、IO 吞吐、内存上限都会成为硬瓶颈。这时候需水平拆分——把一张大表按某种规则拆成多张小表，分散到多个库甚至多台机器上。
常用的拆分策略：
按时间范围。 从实现思路看，订单按月分表（order_202401、order_202402、……），日志按天分表。天然适合有时效性的数据。历史数据过期了直接 DROP 整表或者 DROP PARTITION，干净利落。
按哈希取模。 user_id % 16 分到 16 张表。数据分布均匀，不会出现某张表特别大其他表特别小的情况。缺点是扩容的时候需迁移数据，16 张表扩到 32 张，理论上所有数据都要重新分配。
按一致性哈希。 理解这一步时，借助虚拟节点减少扩容时的数据迁移量。实现复杂一些，但大规模集群下是值得的。
按业务维度。 按地区分表、按租户分表。查询天然带分片键，不需跨分片。
结合项目来看，工具方面，ShardingSphere 是目前 Java 生态里最成熟的选择，兼容 JDBC 代理模式和 Sidecar 模式。MyCat 也是老牌方案。如果是云原生环境，能够看看 Vitess（YouTube 开源的）。再往上，如果团队有能力驾驭，TiDB、OceanBase 这类 NewSQL 数据库能够从应用层彻底摆脱分库分表的复杂性——后面架构那一节会展开讲。
7.3 分区表：MySQL 原生的"轻量分表"
实际处理时，分区表是介于单表和分库分表之间的一个方案。数据在逻辑上还是一张表，SQL 写法不变，但物理上按分区存储在不同的文件里。
CREATE TABLE access_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
log_date DATE NOT NULL,
content VARCHAR(1000) NOT NULL DEFAULT '',
PRIMARY KEY (id, log_date)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(log_date)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
分区表最大的两个优势：第一，查询的时候如果 WHERE 条件命中分区键，MySQL 只扫描对应的分区（这叫分区裁剪），其他分区直接跳过。第二，删除历史数据能够直接 ALTER TABLE access_log DROP PARTITION p202401，秒级完成，不产生大量碎片，不写大量 binlog，不影响线上业务。对比 DELETE FROM access_log WHERE log_date < '2024-02-01' 那种逐行删除、产生大量 undo log 和 binlog 的做法，简直是天壤之别。
在这个场景下，但分区表也有明显的限制。分区键必须包含在所有唯一索引里（包括主键），这限制了主键的设计。不能跨分区建外键。某些复杂查询下优化器对分区表的处理不如普通表。分区数上限 8192。
我的建议：时间序列类数据、需定期清理历史的场景，分区表很好用。其他复杂场景，优先考虑中间件分表或者 NewSQL。
7.4 一个能够直接拿去用的建表模板
把前面所有原则综合起来，一个生产级别的建表语句大概长这样：
CREATE TABLE `t_order` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`order_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '订单号',
`user_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3完成 4取消 5退款',
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额(元)',
`product_name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '商品名(下单快照)',
`remark` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '买家备注',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_deleted` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 0否 1是',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_id_status` (`user_id`, `status`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';
在这个场景下，每个字段都有 NOT NULL 和默认值。主键是自增 BIGINT。索引覆盖了最高频的查询路径。字符集和排序规则显式声明。每个字段都有 COMMENT。这是一个能够直接拿去用的模板，根据具体业务增减字段和索引就好。
八、SQL 查询优化：把每一条 SQL 写对
理解这一步时，建表设计是地基，SQL 编写就是日常工作中最频繁的优化动作。一条烂 SQL 能够让精心设计的索引形同虚设，一条好 SQL 能够让普通硬件跑出惊人的性能。这一节内容很多，我按场景一个一个来。
8.1 SELECT：只拿你需的
永远不要 SELECT *。 这大概是我做代码审查时写得最多的一句评论了。
SELECT * 的问题：得到了大量不需的列，浪费网络带宽和内存；彻底断绝了采用覆盖索引的可能；如果表里有 TEXT 大字段，会把大字段也拉出来，IO 暴增；表结构变更时（加列、改列顺序），应用可能莫名其妙地出错。
-- ❌
SELECT * FROM orders WHERE user_id = 1001;
-- ✅
SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id = 1001;
理解这一步时，明确写出你需的列。多打几个字，换来的是更少的网络传输、更高的缓存命中率、以及覆盖索引的可能性。
同理，SELECT DISTINCT 也要谨慎。DISTINCT 需排序或者哈希去重，代价不低。先想想能不能借助改写 JOIN 来消除重复，而不是靠 DISTINCT 来兜底。
8.2 WHERE：索引失效的完整清单
从实现思路看，这是面试高频题，但更是生产环境的真实痛点。我把它整理成一份尽可能完整的清单，每一种都配上错误写法和正确写法。
（1）对索引列采用函数或表达式。
-- ❌ 索引失效
WHERE YEAR(create_time) = 2024
WHERE DATE(create_time) = '2024-06-01'
WHERE id + 1 = 100
WHERE UPPER(name) = 'ALICE'
WHERE LENGTH(phone) = 11
-- ✅ 改写为等价的、不碰列本身的条件
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02'
WHERE id = 99
WHERE name = 'Alice' -- 如果排序规则是 ci，本身就不区分大小写
原理很轻松：B+Tree 的有序性是基于原始列值的。你对列做了函数运算，优化器没法在索引树上做二分查找了，只能全表扫描。
（2）隐式类型转换。
-- phone 是 VARCHAR 类型
-- ❌ 传入数字，MySQL 会对 phone 列做隐式 CAST，索引失效
WHERE phone = 13800000000
-- ✅ 加引号
WHERE phone = '13800000000'
理解这一步时，这个坑特别隐蔽。SQL 不会报错，查询结果也是对的，只是默默走了全表扫描。很多"索引明明建了但就是不生效"的诡异问题，根源就在这里。在代码里拼 SQL 的时候，一定要确认参数类型和列类型一致。
（3）LIKE 左模糊。
-- ❌ 前缀不确定，没法走索引
WHERE name LIKE '%张'
WHERE name LIKE '%张%'
-- ✅ 右模糊可以走索引
WHERE name LIKE '张%'
在这个场景下，如果业务确实需全模糊搜索（比如搜索框输入"数据"要匹配"数据库优化"），那就不是 B+Tree 索引能干的事了，用全文索引或者 Elasticsearch。
（4）OR 连接非索引列。
-- a 有索引，b 没有索引
-- ❌ 优化器大概率放弃 a 的索引，直接全表扫描
WHERE a = 1 OR b = 2
-- ✅ 改写为 UNION ALL
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a != 1;
理解这一步时，若 a 和 b 都有索引，MySQL 可能会走 Index Merge（合同时两个索引的结果），但 Index Merge 的效率通常不如 UNION ALL 可控。
（5）联合索引不满足最左前缀。 前面已经详细讲过，索引 (a, b, c) 跳过 a 直接查 b 是走不了的。
（6）范围条件之后的列。 索引 (a, b, c)，WHERE a = 1 AND b > 2 AND c = 3，c 用不上。因为 b 是范围条件，在 b 的范围内，c 是无序的。
（7）NOT IN、NOT EXISTS、!=。 在这个场景下，这些否定条件在数据分布不利于索引的时候，优化器可能判断全表扫描更快。不是绝对不走索引，但要做好它可能不走索引的心理准备。
（8）ORDER BY 方向与索引不一致（8.0 之前）。 索引 (a, b) 默认都是升序。ORDER BY a ASC, b DESC 在 8.0 之前没法借助索引。8.0 兼容了降序索引，能够显式新建 (a ASC, b DESC)。
8.3 JOIN：小表驱动大表，被驱动表必须有索引
JOIN 是关系型数据库的核心能力，也是性能问题的高发区。
MySQL 8.0 的 JOIN 算法主要有三种：
Index Nested Loop Join (INL)：驱动表逐行扫描，拿关联键到被驱动表走索引查找。这是最理想的情况，前提是被驱动表的关联列有索引。
Hash Join（8.0.18+）：对驱动表构建内存哈希表，随后扫描被驱动表做哈希匹配。适用来等值 JOIN 且无索引的情况，比老版本的 Block Nested Loop 快很多。
Block Nested Loop (BNL)：8.0.18 之前的无索引 JOIN 方案。把驱动表的数据分块放入 join_buffer，随后循环匹配被驱动表。最慢。
-- ✅ 确保被驱动表的关联字段有索引
-- orders.user_id 必须有索引
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';
-- ✅ 避免超过 3 张表的 JOIN
-- 复杂关联拆成多次简单查询，在应用层组装
-- ❌ 避免在 JOIN 条件中对列做函数
ON YEAR(o.create_time) = u.reg_year -- 索引失效
子查询改 JOIN 是一个常用的优化手段：
-- ❌ IN 子查询（某些版本和场景下优化器处理不好）
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE city = '北京');
-- ✅ 改写为 JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';
结合项目来看，若 JOIN 的时候没有索引可用，能够适当调大 join_buffer_size（默认 256KB），减少分块次数：
join_buffer_size = 4M
8.4 ORDER BY：能走索引就别排序
实际处理时，排序是查询中很常用的操作，也是 CPU 和内存的大户。MySQL 的排序有两条路径。
路径一：借助索引的有序性。 从实现思路看，若 ORDER BY 的列和方向恰好跟某个索引匹配，MySQL 直接按索引顺序读取，零排序开销。这是最优解。
-- 有索引 (status, create_time)
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;
-- 完美利用索引，EXPLAIN 里没有 Using filesort
路径二：Filesort。 结合项目来看，没法借助索引的时候，MySQL 在内存（或磁盘）里做排序。EXPLAIN 里表现为 Extra: Using filesort。
实际处理时，Filesort 内部有两种算法。"双路排序"是先取出排序键和行 ID，排序完再根据行 ID 回表取完整数据，两次扫描。"单路排序"是一次取出所有需的列，排序完直接输出，一次扫描但内存占用大。
优化 Filesort 的手段：
# 增大排序缓冲区（默认 256KB，太小了）
sort_buffer_size = 4M
# 增大此值让优化器倾向选择单路排序
max_length_for_sort_data = 4096
另外，ORDER BY 后面跟 LIMIT 能让优化器选择 Top-N 排序（只维护一个大小为 N 的堆），而不是全量排序。所以 ORDER BY ... LIMIT 20 比先排序再取前 20 条要高效得多。
8.5 GROUP BY：本质是排序 + 聚合
落到代码里，GROUP BY 的执行逻辑是先按分组列排序（或借助索引的有序性），随后对每一组做聚合。如果没有合适的索引，MySQL 会新建临时表来完成分组，EXPLAIN 里会出现 Using temporary。
-- 有索引 (user_id)
SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id;
-- 利用索引有序性，无临时表
-- 无索引
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 可能创建临时表
落到代码里，MySQL 8.0 默认采用哈希聚合而不是排序聚合，对无索引的 GROUP BY 性能有明显提升。
另外，5.7 开始默认开启 ONLY_FULL_GROUP_BY 模式。SELECT 里的非聚合列必须出现在 GROUP BY 中，否则报错。这是 SQL 标准的要求，不要关闭它，修正你的 SQL 就好：
-- ❌ 报错（name 不在 GROUP BY 里）
SELECT name, MAX(score) FROM students GROUP BY class_id;
-- ✅ 修正
SELECT class_id, ANY_VALUE(name), MAX(score) FROM students GROUP BY class_id;
8.6 深分页：LIMIT 1000000, 10 是性能杀手
LIMIT 1000000, 10 的执行逻辑是：扫描同时丢弃前 100 万行，然后取下面的 10 行。不管你要不要前面的行，它都得扫描。数据量越大，这个问题越致命。
方案一：延迟关联。 借助覆盖索引先拿到 ID，再回表取数据：
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) tmp ON o.id = tmp.id;
落到代码里，子查询只走主键索引（覆盖索引，只读索引树，很快），拿到 10 个 ID 后再回表取完整数据。回表只回 10 次，代价可忽略。
方案二：游标法。 这是我最建议的方案：
-- 记住上一页最后一条记录的 ID
SELECT * FROM orders WHERE id > #{lastMaxId} ORDER BY id LIMIT 10;
在这个场景下，不管翻到第几百万页，都只扫描 10 行。限制是不能跳页，适合"加载更多"、无限滚动的场景。
方案三：业务限制。 理解这一步时，告诉用户最多翻 100 页。这不是技术妥协，是合理的产品设计。Google 搜索也不会让你翻到第一万页。
方案四：缩小范围。 加时间条件或其他过滤条件，把数据集缩小到可管理的范围：
SELECT * FROM orders
WHERE create_time >= '2024-06-01' AND id > #{lastMaxId}
ORDER BY id LIMIT 10;
8.7 INSERT：批量、批量、还是批量
-- ❌ 逐条插入（1 万次网络往返 + 1 万次事务提交）
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
...
-- ✅ 合并为一条语句
INSERT INTO t VALUES (1,'a'), (2,'b'), (3,'c'), ..., (500,'z');
结合项目来看，每批建议 500 到 2000 条。太大可能超过 max_allowed_packet，也会占用过多的 binlog 缓冲。
在这个场景下，超大量数据导入（比如几百万行的初始化数据），用 LOAD DATA INFILE：
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, order_no, user_id, amount, create_time);
落到代码里，比 INSERT 快 10 到 20 倍，因为省掉了 SQL 解析的开销，而且能够用更高效的批量写入路径。
若是在维护窗口 做大规模导入，能够临时调整一些参数：
SET unique_checks = 0; -- 关闭唯一性检查
SET autocommit = 0; -- 手动控制提交
SET foreign_key_checks = 0; -- 关闭外键检查
-- 执行导入...
COMMIT;
SET unique_checks = 1;
SET autocommit = 1;
SET foreign_key_checks = 1;
8.8 UPDATE：确保 WHERE 走索引
-- ❌ 循环逐条更新
for id in id_list:
cursor.execute("UPDATE t SET status=1 WHERE id=%s", (id,))
-- ✅ 批量更新
UPDATE t SET status = 1 WHERE id IN (1, 2, 3, ..., 1000);
-- ✅ 不同行更新不同值
UPDATE t SET status = CASE id
WHEN 1 THEN 10
WHEN 2 THEN 20
WHEN 3 THEN 30
END
WHERE id IN (1, 2, 3);
这里有一个很关键的点：UPDATE 的 WHERE 条件必须走索引。 实际处理时，若 WHERE 条件没有索引，MySQL 需全表扫描找到目标行。在 RR 隔离级别下，扫描过的行都可能被锁住，后果是灾难性的。我见过一个线上事故，一条 UPDATE 的 WHERE 条件写错了没走索引，直接把一张几百万行的表锁了十几分钟，所有相关接口全部超时。
8.9 DELETE：不要在一个事务里删太多
-- ❌ 一次性删除千万行
DELETE FROM logs WHERE create_time < '2023-01-01';
-- ✅ 分批删除
DELETE FROM logs WHERE create_time < '2023-01-01' LIMIT 10000;
-- 在应用层循环执行，直到 affected_rows = 0
-- 每批之间 sleep 50~100ms，降低主从延迟
一次性删大量数据的问题：长事务、大量 undo log、大量 binlog、主从延迟飙升、锁等待阻塞业务。分批删是标准操作。
若是清空整张表，用 TRUNCATE TABLE。它是 DDL 操作，不逐行删除，不写逐行的 binlog，秒级完成。但不可回滚，执行前确认清楚。
若要删除大部分数据只保留少量，更聪明的做法是"反向操作"：
CREATE TABLE logs_new LIKE logs;
INSERT INTO logs_new SELECT * FROM logs WHERE create_time >= '2024-01-01';
RENAME TABLE logs TO logs_old, logs_new TO logs;
DROP TABLE logs_old;
8.10 UNION：永远优先用 UNION ALL
UNION 会对结果集做去重（隐含 DISTINCT），需额外的排序或哈希操作。如果你确定两个结果集没有重复（或者业务上允许重复），永远用 UNION ALL：
SELECT name FROM table_a WHERE ...
UNION ALL
SELECT name FROM table_b WHERE ...;
8.11 COUNT(*)：InnoDB 没有捷径
InnoDB 的 SELECT COUNT(*) FROM t 需全表扫描。因为 MVCC 的存在，不同事务看到的行数可能不同，所以没有预存的精确计数值。这跟 MyISAM 不一样（MyISAM 存了精确行数，但 MyISAM 已经没人用了）。
优化方案：维护一张计数表，业务写入的时候同步更新；用 SHOW TABLE STATUS LIKE 't' 拿到估算行数（Rows 字段是近似值，误差可能不小）；如果只需判断"是否存在"，用 SELECT 1 FROM t LIMIT 1 而不是 COUNT(*) > 0；大数据量的精确计数交给离线任务或者 OLAP 引擎。
8.12 子查询：能改 JOIN 就改，能用窗口函数就用窗口函数
-- ❌ 相关子查询（对外层每一行都执行一次子查询）
SELECT * FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE user_id = o.user_id);
-- ✅ 改写为 JOIN
SELECT o.* FROM orders o
INNER JOIN (
SELECT user_id, AVG(amount) AS avg_amt
FROM orders GROUP BY user_id
) t ON o.user_id = t.user_id
WHERE o.amount > t.avg_amt;
-- ✅ 或者用 MySQL 8.0 窗口函数
SELECT * FROM (
SELECT *, AVG(amount) OVER (PARTITION BY user_id) AS avg_amt
FROM orders
) t WHERE amount > avg_amt;
落到代码里，窗口函数是 8.0 带来的真正好用的东西。很多以前需自关联或者相关子查询才能实现的逻辑（排名、环比、累计、分组取第 N 条），现在一个窗口函数就搞定了，可读性和性能都好得多。
九、事务与锁：并发场景下的命脉
9.1 短事务是铁律，没有商量余地
在这个场景下，事务从 BEGIN 到 COMMIT 之间的时间越短越好。长事务的危害是全方位的：持有的行锁不释放，阻塞其他同时发事务；Undo Log 持续增长且无法被 Purge 线程清理，表空间膨胀；主从复制延迟（从库需重放整个事务）；连接被长时间占用，连接池耗尽。
绝对不要在事务中做这些事情： 在这个场景下，HTTP 或 RPC 远程调用（可能超时几秒甚至几十秒）、文件读写和图片处理、发送消息或邮件或短信、任何需等待用户交互的操作。
在这个场景下，正确的做法是把非数据库操作放在事务外面。事务里只包含数据库操作，而且尽量只包含必要的几条 DML：
# ❌ 事务中包含远程调用
conn.begin()
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
result = requests.post("http://payment-service/deduct", ...) # 可能耗时 3 秒
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
# ✅ 远程调用在事务外
result = requests.post("http://payment-service/deduct", ...)
if result.success:
conn.begin()
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
查看当前有没有长事务在跑：
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;
9.2 隔离级别：搞清楚你在用什么
| 级别 | 脏读 | 不可重复读 | 幻读 | 并发 |
|---|---|---|---|---|
| READ UNCOMMITTED | 有 | 有 | 有 | 最高 |
| READ COMMITTED | 无 | 有 | 有 | 高 |
| REPEATABLE READ | 无 | 无 | 基本解决 | 中 |
| SERIALIZABLE | 无 | 无 | 无 | 最低 |
从实现思路看，InnoDB 默认是 REPEATABLE READ（RR）。在这个级别下，InnoDB 借助 MVCC 解决了快照读的幻读，借助 Next-Key Lock 解决了当前读的幻读。大部分互联网业务用 RR 就够了。
实际处理时，若你的业务能接受"同一事务内两次读取同一行结果不同"，能够考虑 READ COMMITTED（RC）。RC 的锁粒度更小——没有间隙锁，只有行锁——同时发性能更好。很多大厂的 MySQL 集群跑的就是 RC。
从实现思路看，SERIALIZABLE 几乎不会在生产环境采用，除非是级别的对强一致性有极端要求的场景。
9.3 锁：理解它才能驾驭它
InnoDB 的锁体系：
行锁（Record Lock）：锁定某一条索引记录。
间隙锁（Gap Lock）：锁定索引记录之间的"间隙"，防止其他事务往间隙里插入新记录。只在 RR 级别存在。
临键锁（Next-Key Lock）：行锁加间隙锁的组合，锁定一个左开右闭的区间。这是 RR 级别下 InnoDB 加锁的基本单位。
表级锁：DDL 操作、LOCK TABLES 语句、或者当 WHERE 条件不走索引时，行锁退化为表锁。
减少锁冲突的实践：
-- ✅ 用主键或唯一索引精确定位，只锁一行
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
-- ❌ WHERE 条件不走索引，可能锁全表
UPDATE accounts SET balance = balance - 100 WHERE name = '张三';
-- ✅ 多行操作按固定顺序，避免死锁
UPDATE t SET ... WHERE id IN (1, 2, 3) ORDER BY id;
死锁的排查和预防：
-- 查看最近一次死锁的详细信息
SHOW ENGINE INNODB STATUS\G
-- 开启所有死锁的日志记录
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 设置锁等待超时（默认 50 秒，可以调小让应用更快重试）
SET GLOBAL innodb_lock_wait_timeout = 10;
预防死锁的核心思路：事务尽量短小；多行操作按固定顺序（比如按主键升序）；确保 WHERE 条件走索引，避免锁升级为表锁；考虑降低隔离级别到 RC（消除间隙锁）。
9.4 悲观锁的正确打开方式
BEGIN;
SELECT balance FROM accounts WHERE id = 1001 FOR UPDATE;
-- 此时其他事务对 id=1001 的 FOR UPDATE / UPDATE / DELETE 都会阻塞
-- 业务判断和计算
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
COMMIT; -- 锁释放
注意：FOR UPDATE 必须走索引，否则锁全表。事务尽快提交，不要持着锁做别的事情。高同时发场景优先考虑乐观锁（版本号机制）或者无锁方案。
9.5 MVCC：InnoDB 高并发读的秘密
在这个场景下，MVCC（多版本同时发控制）是 InnoDB 实现"读不阻塞写、写不阻塞读"的核心机制。每行数据有两个隐藏列：trx_id（最后修改该行的事务 ID）和 roll_pointer（指向 Undo Log 中的旧版本）。
在这个场景下，事务开始时（或者第一次 SELECT 时，取决于隔离级别）生成一个 ReadView，记录当时活跃的事务列表。之后每次读取，沿着 Undo Log 版本链往回找，找到第一个对当前事务"可见"的版本。
实际处理时，RC 级别下，每次 SELECT 都生成新的 ReadView，所以能看到其他事务已提交的最新数据。RR 级别下，只在事务第一次 SELECT 时生成 ReadView，之后复用，所以整个事务内看到的数据是一致的快照。
落到代码里，这就是为什么 InnoDB 的读操作不需加锁——它读的是历史版本，不会和写操作冲突。理解了这个机制，很多看起来"奇怪"的行为就说得通了。
十、服务器参数调优：锦上添花，不是雪中送炭
在这个场景下，参数调优这件事，我必须先泼一盆冷水：在表结构和 SQL 都正确的前提下，合理的参数设置能让硬件性能充分发挥；但如果 SQL 写得烂、索引建得乱，调什么参数都救不回来。参数调优是最后一步，不是第一步。
10.1 InnoDB Buffer Pool：最重要的单个参数
它缓存了数据页和索引页，避免每次都读磁盘。
# 建议设为物理内存的 60%~80%
innodb_buffer_pool_size = 12G
# 实例数，>1GB 时建议设为 8，减少内部互斥锁竞争
innodb_buffer_pool_instances = 8
# 优雅关闭时把热数据列表写入文件，启动时自动预加载
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
检查 Buffer Pool 命中率：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 应该在 99% 以上。如果低于 95%，说明内存不够或者查询模式有问题
10.2 Redo Log 和刷盘策略
# Redo Log 大小
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# 刷盘策略
# 0：每秒刷盘（最快，崩溃可能丢 1 秒数据）
# 1：每次提交都刷盘（最安全，默认）
# 2：提交写 OS 缓存，每秒刷盘（折中）
innodb_flush_log_at_trx_commit = 1
10.3 Binlog
sync_binlog = 1 # 每次提交刷盘（最安全）
binlog_format = ROW # 记录每行变更，主从一致性最好
binlog_expire_logs_seconds = 604800 # 保留 7 天
innodb_flush_log_at_trx_commit = 1 加上 sync_binlog = 1 被称为"双 1 设置"，是最安全的数据保护方案。代价是写入性能有一定损耗。如果业务能接受极端情况下丢失极少量数据（比如非核心的日志表），能够把其中一个改为 2 或 0 来提升性能。
10.4 连接和线程
max_connections = 1000
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 128
10.5 排序和临时表
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 256M
max_heap_table_size = 256M
tmp_table_size 和 max_heap_table_size 要一起设，取较小值生效。超过这个大小的临时表会转为磁盘临时表，性能骤降。
10.6 慢查询日志：必须开，没有商量余地
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000
定期用工具分析：
mysqldumpslow -s t -t 20 /var/log/mysql/slow-query.log
pt-query-digest /var/log/mysql/slow-query.log --limit 30
从实现思路看，慢日志是你发现性能问题的第一手资料来源。不开慢日志就像开车不看仪表盘，迟早出事。
10.7 表缓存
table_open_cache = 4096
table_definition_cache = 2048
如果 SHOW GLOBAL STATUS LIKE 'Opened_tables' 的值在持续增长，说明 table_open_cache 不够用，需增大。
十一、架构层面的优化：当单机到了极限
11.1 读写分离
实际处理时，最经典也最容易实施的架构优化。主库承担写，从库承担读，借助 binlog 异步复制同步数据。
实施要点：用 ProxySQL、MySQL Router 或 ShardingSphere 做路由，对应用透明。注意主从延迟——刚写入的数据立刻去从库读可能读不到，关键读操作路由到主库。半同步复制能够降低延迟窗口但不能完全消除。多从库之间做负载均衡。
11.2 缓存层
在 MySQL 前面加一层 Redis，把热点数据的读请求拦截掉。
需处理的经典问题：缓存穿透（查不存在的数据，用布隆过滤器或缓存空值解决）、缓存击穿（热点 key 过期瞬间大量请求打到数据库，用互斥锁或逻辑过期解决）、缓存雪崩（大量 key 同时过期，用随机过期时间和多级缓存解决）、数据一致性（Cache-Aside 模式、延迟双删、binlog 异步更新）。
11.3 分库分表
从实现思路看，当单表数据量到了亿级，或者单库的写入 TPS 到了瓶颈，就需水平拆分。分片键的选择至关重要：选查询最频繁的等值条件列，确定后尽量不变，尽量避免跨分片查询。全局 ID 用雪花算法或 Leaf，不要用 UUID。
理解这一步时，分片之后的难题——跨分片 JOIN、跨分片聚合、分布式事务、全局排序——没有银弹，需根据业务场景逐一设计方案。
11.4 NewSQL：彻底告别分库分表的复杂性
在这个场景下，若架构允许，TiDB、OceanBase、PolarDB 这类 NewSQL 数据库能够从根本上解决问题。它们兼容 MySQL 协议，应用几乎不需改动，但底层自动做了数据分布、水平扩展、高可用。
实际处理时，当然，引入 NewSQL 也意味着运维复杂度、成本、学习曲线的变化。需根据团队能力和业务阶段综合评估。但对于新起步的项目，如果预期数据量会更快增长，从一开始就考虑 NewSQL 可能比后面再做分库分表要省心得多。
十二、特殊场景的应对
12.1 大表 DDL：别把线上锁死了
在这个场景下，直接对千万级大表执行 ALTER TABLE，可能锁表几分钟到几小时，期间所有读写全部阻塞。
解决方案：
pt-online-schema-change（Percona）：新建新表 → 建触发器同步增量 → 拷贝数据 → 原子换名。全程不锁原表。
gh-ost（GitHub）：原理类似，但用 binlog 代替触发器，对主库压力更小。
MySQL 8.0 Instant DDL：部分操作（比如加列）能够秒级完成，不需重建表：
ALTER TABLE orders ADD COLUMN remark VARCHAR(200) DEFAULT '', ALGORITHM=INSTANT;
落到代码里，但 Instant DDL 兼容的操作有限，不是所有 DDL 都能秒级完成。
12.2 秒杀和热点行
大量并发更新同一行（比如商品库存），行锁成为严重瓶颈。
应对策略：库存拆分（把 1 个商品的 1000 件库存拆成 10 个子记录，每个 100 件，请求随机路由到不同子记录，锁竞争降低 10 倍）；队列串行化（请求先入 Redis 队列或消息队列，单线程消费扣减）；合同时扣减（在内存中攒批，定时批量落库）；乐观锁（UPDATE goods SET stock = stock - 1 WHERE id = ? AND stock > 0，借助 WHERE 条件做原子判断）。
12.3 统计和报表：别在 OLTP 主库上跑
一条 SELECT COUNT(*), SUM(amount), AVG(price) FROM orders WHERE ... 可能锁住大量行、占用大量 IO，影响在线业务。
方案：从库跑报表；预计算（定时任务把统计结果写入汇总表）；数据同步到 OLAP 引擎（ClickHouse、StarRocks、Apache Doris）；物化视图思想（应用层维护宽表，借助 binlog 增量同步）。
12.4 全文搜索
MySQL 的 FULLTEXT 索引能够做基本的文本搜索：
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram;
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE)
LIMIT 20;
ngram 分词器适合中文，ngram_token_size 默认 2。但如果业务需分面、高亮、同义词、相关性排序、拼音搜索这些复杂功能，MySQL 的 FULLTEXT 远远不够，上 Elasticsearch 或 Meilisearch。
12.5 时间序列数据
结合项目来看，日志、坚控指标、IoT 数据这类按时间持续增长的数据，天然适合按时间分区或分表。删除历史数据直接 DROP PARTITION，秒级完成。如果数据量极大且查询模式轻松（按时间范围加聚合），考虑专用时序数据库：InfluxDB、TDengine、TimescaleDB。
12.6 多租户数据隔离
落到代码里，SaaS 系统常用需求。三种方案：独立数据库（隔离性最好，连接管理复杂）、共享库独立 Schema（中等隔离）、共享表加 tenant_id 字段（成本最低）。
-- tenant_id 必须出现在所有查询中
-- 联合索引第一列放 tenant_id
INDEX idx_tenant_status (tenant_id, status, create_time)
忘加 tenant_id 条件是最严重的安全事故（数据泄漏）。在代码层面应该有拦截机制，比如 MyBatis 拦截器、全局 Filter，确保每条 SQL 都自动带上租户条件。
十三、EXPLAIN：你的第一诊断工具
理解这一步时，每次优化 SQL 之前，先跑一下 EXPLAIN。不要猜，不要凭经验，让执行计划告诉你真相。
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY create_time DESC LIMIT 20;
type（访问类型），从好到差：const（主键或唯一索引等值，最多一行）→ eq_ref（JOIN 时被驱动表走主键或唯一索引）→ ref（普通索引等值）→ range（索引范围扫描）→ index（全索引扫描）→ ALL（全表扫描）。日常查询至少应该达到 range 级别。看到 ALL，说明需加索引或改写 SQL。
key：实际采用的索引名。NULL 说明没走索引。
rows：优化器预估需扫描的行数。越小越好。
Extra 里的关键信息：Using index 是覆盖索引，很好；Using index condition 是索引下推，好；Using filesort 需额外排序，看看能不能优化；Using temporary 采用了临时表，通常需优化；Using join buffer (BNL) 说明 JOIN 没走索引，加索引。
索引下推（ICP） 结合项目来看，值得单独说一下。MySQL 5.6 引入的优化。以索引 (name, age) 为例，查询 WHERE name LIKE 'Zhang%' AND age = 25：没有 ICP 时，存储引擎得到所有 name LIKE 'Zhang%' 的行，Server 层再逐行过滤 age = 25。有 ICP 时，存储引擎在索引层就同时判断两个条件，只把都满足的行回表。减少了大量无效回表。EXPLAIN 中表现为 Extra: Using index condition。
直方图（8.0）：当数据分布严重不均匀时，优化器可能做出错误的执行计划选择。能够新建直方图帮助优化器更准确地估算：
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 256 BUCKETS;
十四、坚控和日常运维
优化不是一次性的事情，而是持续的观察、分析、调整。
-- 当前连接和执行中的查询
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;
-- 杀掉问题查询
KILL;
-- InnoDB 引擎状态
SHOW ENGINE INNODB STATUS\G
-- 全局状态指标
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Performance Schema 在 8.0 里很好用：
-- 最耗时的 SQL
SELECT digest_text, count_star,
ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
-- 当前锁等待
SELECT * FROM performance_schema.data_lock_waits;
-- 未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';
-- 冗余索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';
定期维护：
-- 更新索引统计信息
ANALYZE TABLE orders;
-- 查看碎片率
SELECT table_name,
ROUND(data_free / 1024 / 1024, 2) AS free_mb,
ROUND(data_free / (data_length + data_free) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE table_schema = 'your_db' AND data_free > 100 * 1024 * 1024
ORDER BY data_free DESC;
碎片整理：小表能够直接 OPTIMIZE TABLE，大表用 pt-online-schema-change 或 gh-ost 在线操作。OPTIMIZE TABLE 会锁表，生产环境慎用。
从实现思路看，备份策略不是性能优化，但比性能优化重要一万倍。物理备份用 xtrabackup，逻辑备份用 mysqldump 或 mydumper，binlog 备份用来时间点恢复。最重要的一条：定期做恢复演练。备份不验证等于没有备份。
十五、MySQL 8.0 带来的新武器
若版本允许，升级到 8.0 能白捡不少好东西。
窗口函数。 理解这一步时，ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM OVER、AVG OVER……很多以前需自关联或者相关子查询才能实现的逻辑，现在一个窗口函数搞定：
-- 每个用户最近一笔订单
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
FROM orders
) t WHERE rn = 1;
CTE（公共表表达式）。 在这个场景下，递归查询组织架构、树形结构，以前要写存储过程或者在应用层递归，现在一个 WITH RECURSIVE 搞定：
WITH RECURSIVE org_tree AS (
SELECT id, name, parent_id, 1 AS depth
FROM departments WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id, t.depth + 1
FROM departments d INNER JOIN org_tree t ON d.parent_id = t.id
)
SELECT * FROM org_tree;
Hash Join。 理解这一步时，等值 JOIN 在没有索引的情况下，8.0.18 之后自动采用 Hash Join 替代 Block Nested Loop，性能提升数倍。
不可见索引。 安全地测试"如果删掉这个索引会怎样"，不影响线上运行。
原子 DDL。 在这个场景下，CREATE、ALTER、DROP TABLE 要么完全成功，要么完全回滚，不会出现"表新建了一半"的中间状态。
直方图。 帮助优化器在数据倾斜时做出更好的决策。
降序索引。 兼容 (a ASC, b DESC) 这种混合方向的索引，解决以前 ORDER BY a ASC, b DESC 无法借助索引的问题。
十六、最后说几句
落到代码里，MySQL 优化是一个从宏观到微观、从设计到运维的系统工程。我把它总结为一个优先级金字塔：最顶层是架构设计（分库分表、缓存、读写分离），影响最大但改动成本也最高；第二层是 SQL 与索引优化，日常最频繁、投入产出比最高；第三层是表结构设计，建表时做对的事情会在整个生命周期持续产生收益；最底层是参数调优，锦上添花，不是雪中送炭。
几条我这些年总结的铁律，放在这里收尾：
先 EXPLAIN，后动手。不要凭感觉优化。
小表驱动大表，覆盖索引消灭回表。
联合索引：等值在前，范围在后，最左前缀不能断。
禁止 SELECT *，禁止函数包裹索引列，禁止隐式类型转换。
事务要短，锁要小，能批量就不逐条。
深分页用游标，大删除要分批。
字段 NOT NULL 加默认值，类型选最小够用的。
主键要有序，UUID 别做主键。
索引不是越多越好，单表控制在五六个以内，定期清理僵尸索引。
落到代码里，慢查询日志必须开，Performance Schema 必须看，定期 ANALYZE TABLE。
落到代码里，数据库优化没有银弹，也没有一劳永逸的方案。业务在变，数据在增长，昨天合理的索引今天可能就成了瓶颈。保持对数据的敬畏，保持对性能的敏感，定期回顾和巡检，才能让你的 MySQL 在漫长的岁月里始终稳健地跑着。
面试题：你知道哪些 MySQL 优化方案？
- 建好索引：给经常查询、排序和分组的字段加索引，就像给书加目录，找数据快几百倍；联合索引把等值列放前、范围列放后，一个索引同时搞定过滤和排序。
- 别查多余列：只 SELECT 需的字段，千万别用 SELECT *，省内存省带宽，还能触发覆盖索引免回表，性能差距几十倍。
- 避免全表扫描：写 SQL 时确保 WHERE 条件能走索引，别让数据库一行行从头翻到尾；EXPLAIN 里 type=ALL 就是警报，千万级数据全扫等于宕机。
- 优化深分页：翻页太深时用“延迟关联”或记录上次最大 ID 做游标，避免跳过几十万行废数据再取十条，无论第几页都只扫十行。
- 小表驱动大表：JOIN 时让数据少的表去匹配多的表，被驱动表关联字段必须有索引，否则嵌套循环让性能指数级崩塌。
- 字段类型要小：能用 TINYINT 就别用 INT，能用 VARCHAR(50) 就别用 VARCHAR(255)，越小越快；金额用 DECIMAL 别用 FLOAT，IP 用 INT 别用字符串，选对类型是零成本提速。
- 拆分大表：单表超千万行就按时间或用户 ID 水平分表，或把不常用大字段垂直拆到副表，主表精干了 Buffer Pool 命中率自然上去。
- 加缓存层：热点数据放 Redis，挡住 90% 重复读请求，别啥都查库；但要处理好穿透、击穿、雪崩和一致性，缓存不是加了就完事。
- 读写分离：写走主库、读走从库，一台扛不住就多搞几台分担读压力；关键业务注意主从延迟，必要时强制走主库。
- 调大缓冲池：innodb_buffer_pool_size 设为物理内存 60%~80%，让热数据全在内存处理；这是最重要的单个参数，命中率低于 99% 就该扩容。
- 批量操作：插入更新多条时用批量语句合同时提交，别一条一条循环，减少网络往返和事务开销；超大量导入用 LOAD DATA INFILE，比 INSERT 快十几倍。
- 避免索引失效：别在索引列上做函数运算、隐式类型转换或左模糊查询，索引直接作废退化为全扫；字符串查询记得加引号，数字传字符串也会失效。
- 用 EXPLAIN 分析：复杂 SQL 先跑 EXPLAIN，看 type 是否 range 以上、key 命中预期索引没、rows 扫描行数合不合理、Extra 有无 filesort 或 temporary，别靠猜优化。
- 异步解耦：发短信、记日志、推送等非核心流程扔进消息队列，别放事务里阻塞主业务；事务越长锁越久，同时发能力断崖下跌。
- 定期清理归档：半年前老数据移到历史库或 OLAP 系统，保持在线表轻量干净；大批量删除要分批执行同时控间隔，别一个事务删百万行拖垮主从。
- 合理设计冗余：高频读取场景适当冗余维度字段到事实表，用空间换时间少 JOIN；但频繁变更或强一致字段别冗余，维护成本比查询收益还大。
- 控制连接数：应用端用连接池复用连接，别频繁新建销毁；设合理 max_connections 和 wait_timeout，防连接打满拒新请求；长事务是连接耗尽头号杀手。
- 坚控慢查询：开慢查询日志同时定期用工具分析 Top SQL，把耗时长的揪出来重点优化；不开慢日志就像开车不看仪表盘，问题永远发现不了。
- 选对存储引擎：需事务和高同时发就用 InnoDB，它是唯一选择；MyISAM 不兼容事务和行锁，除极个别只读归档外不该出现在生产环境。
- 统计信息要准：数据分布大变后跑 ANALYZE TABLE，别让优化器基于过时统计选错执行计划；MySQL 8.0 可建直方图应对数据倾斜。
- 主键要有序：用自增 BIGINT 或雪花算法做主键，保证顺序写入避免页分 裂；UUID 做主键导致随机插入、索引膨胀、范围查询变慢，分布式用趋势递增整数 ID。
- NOT NULL 加默认值：字段尽量 NOT NULL 同时给合理默认值，NULL 让索引统计、比较逻辑和 COUNT 行为变复杂，很多隐蔽 Bug 源于此。
- 事务保持短小：不在事务中做远程调用、文件读写或等用户交互，BEGIN 到 COMMIT 间只做必要 DB 操作；长事务持锁不释放、Undo Log 无法回收，是线上故障常用根源。
- ORDER BY 走索引：让排序列和方向与索引匹配，避免额外 filesort 耗 CPU 和内存；MySQL 8.0 兼容降序索引，混合排序方向也能借助索引。
- 子查询改 JOIN：相关子查询对外层每行都执行一次内层查询，性能极差；改写为 JOIN 或窗口函数，让优化器自主选驱动表和连接算法，通常快几个数量级。
- UNION 用 ALL：UNION 隐含去重需额外排序或哈希，确定无重复或允许重复时永远用 UNION ALL，省去不必要计算。
- 大表 DDL 在线执行：千万级以上大表改结构用 pt-online-schema-change 或 gh-ost，别直接 ALTER TABLE 锁表几十分钟；MySQL 8.0 INSTANT DDL 兼容部分操作秒级完成。
- 字符集统一 utf8mb4：表和连接都用 utf8mb4，别用 utf8（不兼容 emoji）；字符集不一致导致 JOIN 和 WHERE 时隐式转换，索引静默失效且极难排查。
- COUNT 别指望快：InnoDB 的 COUNT(*) 必须全表扫描，没缓存行数；精确计数维护单独计数表，判断存在用 LIMIT 1，近似计数用 SHOW TABLE STATUS。
- 备份比优化重要：物理备份用 xtrabackup，逻辑备份用 mydumper，binlog 保留用来时间点恢复；最重要是定期恢复演练，备份不验证等于没备份，这条比上面所有优化都优先。
- GROUP BY 借助索引：分组列有索引就能避免临时表和排序，EXPLAIN 里看到 Using temporary 就要考虑加索引或改写查询。
- OR 条件拆开：OR 连接非索引列会让优化器放弃索引走全扫，改写为 UNION ALL 或确保每个分支都有独立索引可用。
- IN 列表别太长：IN 里元素过多会导致优化器评估失准甚至栈溢出，上千个值考虑临时表 JOIN 或分批查询。
- UPDATE DELETE 必走索引：WHERE 条件不走索引会锁全表，RR 级别下扫描过的行都被锁住，一条烂 UPDATE 就能把整张表锁死几分钟。
- 逻辑删除慎用：is_deleted 字段让所有查询都要加 WHERE is_deleted=0，联合索引要考虑是否包含它；高频查询表可考虑按删除状态分区隔离。
- 前缀索引省空间：长字符串字段只对前 N 个字符建索引，先算区分度选最小够用的长度；但前缀索引不能用来覆盖索引和 ORDER BY。
- 函数索引用对地方：MySQL 8.0 兼容对表达式建索引，但查询里的表达式必须和索引定义一字不差才能命中，适合确实无法改写的函数查询场景。
- 不可见索引安全测试：删索引前先用 ALTER INDEX INVISIBLE 观察一两周，确认无影响再 DROP，比删了再加回来稳妥得多。
- 连接字符集要对：JDBC、Go DSN、Python connector 都要显式指定 utf8mb4，客户端和服务端字符集不一致是索引失效的隐形杀手。
- 窗口函数替代自关联：排名、环比、累计、分组取第 N 条等场景，ROW_NUMBER/LAG/LEAD/SUM OVER 比自关联或相关子查询可读性和性能都好得多。
落到代码里，总的来说，MySQL建表适合结合实际项目边做边理解。先抓住核心思路，再逐步补上细节和边界处理，最后效果会更稳定，也更容易复用。
