表设计与索引

​

设计原则

• 合理范式：避免重复数据与更新异常；读多写少的场景可适度反范式
• 主键选择：推荐自增 BIGINT 或有序 UUID（如 ULID）；保证短且单调更友好
• 外键：InnoDB 支持外键约束，但高并发写场景可用应用层保证一致性
• 约束优先：尽量让数据库层面负责唯一性、非空、检查条件，实现“数据即文档”

​

索引类型

• B-Tree 索引：默认，支持前缀匹配
• 唯一索引：保证唯一性，可帮助优化器确定行数
• 复合索引：多个列组成，顺序极其重要
• 覆盖索引：查询列完全由索引覆盖，减少回表
• 全文索引：FULLTEXT，适用于文本搜索（8.0+ 支持 InnoDB）
• 倒排哈希索引：MySQL 原生不提供，可借助外部搜索引擎（Elasticsearch、Sphinx）

-- 唯一与普通索引
CREATE UNIQUE INDEX ux_users_email ON users(email);
CREATE INDEX idx_products_price ON products(price);

-- 复合索引顺序（高选择性列在前）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 前缀索引（长字符串）
CREATE INDEX idx_users_email_prefix ON users(email(12));

​

索引生效规则

• 左前缀原则：(a,b,c) 的复合索引可用于 a、a,b、a,b,c
• 范围条件停止匹配：a= ? AND b> ? AND c= ? 中 c 通常无法利用
• 函数/表达式包裹列、不同字符集/排序规则可能导致索引失效
• 隐式类型转换会导致索引未命中，确保比较两侧类型一致

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'PAID';
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = CURDATE(); -- 注意函数包裹

​

虚拟列与功能性索引

ALTER TABLE orders
ADD COLUMN order_date DATE AS (DATE(created_at)) STORED,
ADD INDEX idx_orders_order_date (order_date);

​

EXPLAIN 与诊断

EXPLAIN SELECT *
FROM products WHERE price BETWEEN 100 AND 500 ORDER BY price LIMIT 20;
EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'iPhone%'; -- 8.0+

• type: 访问类型（ALL 全表扫描、range、ref、const 等）
• key: 实际命中的索引；rows 预估扫描行数；filtered 过滤比例
• extra: 是否出现 Using filesort, Using temporary，提示排序或临时表

SET optimizer_trace="enabled=on";
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
SELECT trace FROM information_schema.optimizer_trace\G

ANALYZE TABLE products;
SHOW INDEX FROM products;

​

索引维护与监控

• 定期使用 ANALYZE TABLE 更新统计信息
• SHOW INDEX FROM tbl 查看索引使用情况
• performance_schema.table_io_waits_summary_by_table、sys.schema_unused_indexes 可识别未使用索引
• MySQL 8.0 支持 ALTER TABLE ... ADD INDEX ... INVISIBLE 隐藏索引做灰度验证

ALTER TABLE orders ADD INDEX idx_orders_status (status) INVISIBLE;
-- 验证后再切换
ALTER TABLE orders ALTER INDEX idx_orders_status VISIBLE;

​

常见陷阱

• 过多索引影响写入与空间；定期清理冗余索引
• 低选择性列（如性别、状态）单列索引价值有限，可与其他列组合
• 混用不同字符集/排序规则导致比较异常
• 缺乏主键会导致 InnoDB 自动维护隐藏主键，影响复制性能

​

设计检查清单

• 表是否有主键？是否为自增或业务无重复的键？
• 外键约束是否会影响写入性能？是否需要级联？
• 每个高频查询是否有合适索引支撑？EXPLAIN 是否命中预期？
• 是否存在宽表或过长字段，是否需要拆分或归档策略？

​

小结