第 52 章:数据库设计与索引

学习目标

• 学会表结构设计规范
• 掌握索引原理和最佳实践
• 学会 SQL 性能调优

一、命名规范

类型	规范	示例
表名	小写下划线,复数	users / orders
字段名	小写下划线	user_name / created_at
主键	id	BIGINT 自增
时间	created_at / updated_at	DATETIME
状态	status (TINYINT)	0=禁用,1=启用
逻辑删除	deleted (TINYINT)	0=未删,1=已删

⚠️ 坑 1:数据库字段命名不要用驼峰。MySQL Linux 默认表名/字段名大小写敏感,跨平台会炸。

二、必备字段

每张表都加:

CREATE TABLE user (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- 业务字段
    username     VARCHAR(50) NOT NULL,

    -- 审计字段
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by   BIGINT,
    updated_by   BIGINT,
    deleted      TINYINT NOT NULL DEFAULT 0
);

三、字段类型选型

原则:能用小的就别用大的。

数据	类型	说明
布尔	TINYINT	0=假,1=真
年龄	TINYINT UNSIGNED	0-255
枚举	TINYINT	不要用 VARCHAR
金额	DECIMAL(10,2)	不用 FLOAT(精度丢)
ID	BIGINT UNSIGNED	雪花 ID 也用 BIGINT
短文本	VARCHAR(N)	N 估算最大长度
长文本	TEXT	单独存,不和其他字段一起查

为什么不用 FLOAT 存金额?

FLOAT(10,2) 存 99.99 → 实际是 99.99000000000001

⚠️ 坑 2:金额永远用 DECIMAL,金融级别不要用 FLOAT / DOUBLE。计算也在数据库做,不要在 Java 用 double。

四、索引原理

CREATE INDEX idx_user_name ON user(username);

底层:B+ Tree。

                    [Cat, Dog]
                  /     |      \
        [Ant, Bat] [Cat, Cow] [Dog, Duck]
            ↓         ↓         ↓
         数据行     数据行    数据行

索引类型:

类型	说明
主键索引	叶子节点存数据,聚集
唯一索引	值不能重复
普通索引	没有限制
联合索引	多列,最左前缀
前缀索引	INDEX(name(10))

五、联合索引最左前缀

CREATE INDEX idx_user_name_age ON user(name, age);

能用上索引的查询:

WHERE name = 'Tom'                  ✅
WHERE name = 'Tom' AND age = 18     ✅
WHERE name LIKE 'Tom%'              ✅(前缀匹配)

用不上:

WHERE age = 18                      ❌(跳过 name)
WHERE name LIKE '%Tom'              ❌(前缀通配)

⚠️ 坑 3:联合索引顺序很关键,把区分度高的放前面(选择性高)。

六、什么情况建索引

场景	建索引
WHERE 条件	✅
JOIN 字段	✅
ORDER BY 字段	✅
区分度低的字段(性别)	❌
频繁更新的字段	❌(索引要维护)
小表(< 1000 行)	❌

七、SQL 优化

7.1 EXPLAIN

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

字段	关注
type	ALL 全表扫描 ❌,ref / range ✅
rows	扫描行数
Extra	Using filesort / Using temporary ❌

7.2 慢 SQL 列表

-- 开启慢日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 看慢日志
SHOW VARIABLES LIKE 'slow_query_log_file';

7.3 常见优化

-- ❌ SELECT * FROM user
SELECT id, name FROM user;            -- 只取需要的字段

-- ❌ WHERE age + 1 = 18              -- 索引失效
WHERE age = 18 - 1

-- ❌ WHERE DATE(created_at) = '2026-08-14'   -- 函数让索引失效
WHERE created_at >= '2026-08-14' AND created_at < '2026-08-15'

-- ❌ OR user_id = 1 OR user_id = 2
WHERE user_id IN (1, 2)

八、覆盖索引

CREATE INDEX idx_user_name_age ON user(name, age);

-- 这条查询,索引树上就有 id,不需要回表
SELECT id, name FROM user WHERE name = 'Tom';

回表 = 索引查到的不是数据,还要去主键索引查一次。 覆盖索引 = 索引树上已经有所有需要的字段,不用回表。

九、本章小结

要点	关键
字段	DECIMAL 存金额,TINYINT 存状态
索引	联合索引最左前缀
函数	WHERE 里用函数索引失效
优化	EXPLAIN 看 type 和 Extra

动手练习

1. 设计一张 order 表,有 5 个字段加索引
2. 用 EXPLAIN 分析你的查询

---

下一章:第 53 章:Redis 缓存实战 →