在 MySQL 优化中，有一句名言：“设计大过索引，索引大过配置”。如果表结构设计不合理，后续无论如何调整参数或堆硬件，都只是在泥潭里加速。在 MySQL 8.0 环境下，由于默认字符集和优化器逻辑的变化，设计规范有了新的定义。

---

1.1 字符集与排序规则：utf8mb4 的深水区

MySQL 8.0 默认使用 utf8mb4 字符集和 utf8mb4_0900_ai_ci 排序规则。

【实操要点】

• 0900：代表基于 Unicode 9.0 规范。
• ai (Accent Insensitive)：不区分重音。
• ci (Case Insensitive)：不区分大小写。

【性能陷阱：隐式转换】

这是开发中最常遇到的“索引失效”场景。如果 A 表是 utf8mb4_0900_ai_ci，B 表是 utf8mb4_general_ci，即使关联字段都有索引，JOIN 时也会全表扫描。

❌ 错误示例：

-- 因为排序规则不同，执行计划会显示 type: ALL
SELECT a.id FROM table_a a 
JOIN table_b b ON a.code = b.code; 

✅ 避坑 Checklist：

1. 统一化：新项目全库统一 utf8mb4_0900_ai_ci。
2. 迁移检查：从 5.7 迁移 8.0 时，务必使用以下 SQL 检查不一致的列：

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'your_db';

1.2 主键的选择：B+ Tree 友好性实验

在 8.0 中，主键的设计直接决定了**聚簇索引（Clustered Index）**的写入效率。

【实操对比：自增 ID vs UUID】

• 自增 ID：顺序写入，页填充率高，几乎不产生碎片。
• 随机 UUID：导致频繁的页分裂（Page Split），不仅浪费空间，还会造成大量的随机 IO。

【8.0 特性：UUID 顺序化】

如果你必须用 UUID（如分布式场景），MySQL 8.0 提供了 UUID_TO_BIN 函数，支持将时间位互换，使其具备顺序性。

代码示例：

-- 创建表时使用 BINARY(16) 存储 UUID
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    order_no VARCHAR(20)
);

-- 插入时利用 swap_flag=1 将时间高位移到前面，实现顺序存储
INSERT INTO orders (id, order_no) 
VALUES (UUID_TO_BIN(UUID(), 1), 'REQ001');

---

1.3 JSON 类型的硬核优化

MySQL 8.0 对 JSON 的支持已经非常成熟，开发中不要再把 JSON 当成长字符串（TEXT）来存。

【实操：如何给 JSON 里的字段加索引】

JSON 本身不支持直接建索引，但我们可以利用 虚拟列（Virtual Column） 或 多值索引（Multi-Valued Indexes）。

场景：ext_info 字段存有 {"city": "Shanghai"}，需要按城市查询。

-- 方案：函数索引（8.0.13+）
ALTER TABLE users ADD INDEX idx_city ((CAST(ext_info->>'$.city' AS CHAR(20))));

-- 方案：多值索引（针对 JSON 数组，8.0.17+）
-- 假设 tags 字段为 ["tech", "java"]
ALTER TABLE users ADD INDEX idx_tags ((CAST(ext_info->'$.tags' AS UNSIGNED ARRAY)));

-- 查询命中索引
SELECT * FROM users WHERE 'tech' MEMBER OF (ext_info->'$.tags');

---

1.4 字段类型的精细化准则

1. 数值型：优先使用 INT / BIGINT。尽量避免使用 DECIMAL 除非是金钱计算，因为其计算由 CPU 模拟，效率低于浮点数。
2. 时间型：

• TIMESTAMP：占用 4 字节，受时区影响，上限到 2038 年。
• DATETIME：占用 8 字节（5.6 后优化为 5 字节），不受时区影响。建议 8.0 默认使用 DATETIME。

3. 禁止 NULL：

• NULL 字段会占用额外空间，且在复合索引中会导致优化器逻辑变复杂。
• 建议：所有字段设为 NOT NULL，并给定默认值。

---

---