TDSQL 表结构设计最佳实践

表结构设计的基本原则

1. 范式原则

第一范式（1NF）：确保每列都是原子的，不可再分
第二范式（2NF）：确保非主键列完全依赖于主键，消除部分依赖
第三范式（3NF）：确保非主键列之间不存在传递依赖
BC范式（BCNF）：确保每个决定因素都包含主键

2. 性能原则

减少数据冗余，避免不必要的JOIN操作
合理设计索引，提高查询效率
考虑数据访问模式，优化表结构
避免过度设计，保持表结构简单清晰

3. 可维护性原则

使用清晰、一致的命名规范
添加适当的注释
考虑未来的扩展需求
遵循模块化设计原则

4. 数据完整性原则

使用主键约束确保数据唯一性
使用外键约束维护数据关联性
使用NOT NULL约束避免空值问题
使用DEFAULT约束设置合理的默认值
使用CHECK约束验证数据有效性

数据类型选择最佳实践

1. 数值类型选择

数据类型	存储大小	范围	适用场景
TINYINT	1字节	-128 ~ 127	布尔值、状态码（0-255）
SMALLINT	2字节	-32768 ~ 32767	小范围数值
MEDIUMINT	3字节	-8388608 ~ 8388607	中等范围数值
INT	4字节	-2147483648 ~ 2147483647	大部分数值场景
BIGINT	8字节	-9223372036854775808 ~ 9223372036854775807	大整数、主键ID
DECIMAL	可变	取决于精度	金额、高精度数值
FLOAT	4字节	单精度浮点数	科学计算、不精确数值
DOUBLE	8字节	双精度浮点数	科学计算、高精度浮点数

最佳实践：

优先使用整数类型，其次是DECIMAL，最后是FLOAT/DOUBLE
选择最小的合适数据类型，减少存储占用
金额等精确数值使用DECIMAL类型
避免使用FLOAT/DOUBLE存储精确数值

2. 字符串类型选择

数据类型	存储大小	适用场景
CHAR	固定长度	长度固定的字符串（如身份证号、手机号）
VARCHAR	可变长度	长度可变的字符串（如用户名、地址）
TINYTEXT	0-255字节	短文本
TEXT	0-65535字节	较长文本
MEDIUMTEXT	0-16777215字节	中等长度文本
LONGTEXT	0-4294967295字节	极长文本
BLOB	二进制数据	图片、文件等二进制数据

最佳实践：

长度固定的字符串使用CHAR类型
长度可变的字符串使用VARCHAR类型
避免使用TEXT/BLOB类型作为查询条件
大文本数据考虑存储在外部系统

3. 日期时间类型选择

数据类型	存储大小	格式	适用场景
DATE	3字节	YYYY-MM-DD	日期（无时间）
TIME	3字节	HH:MM:SS	时间（无日期）
DATETIME	8字节	YYYY-MM-DD HH:MM:SS	日期和时间（范围广）
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	时间戳（自动更新）
YEAR	1字节	YYYY	年份

最佳实践：

只需要日期时使用DATE类型
只需要时间时使用TIME类型
需要同时存储日期和时间时，优先使用DATETIME
需要自动更新的时间戳使用TIMESTAMP
注意TIMESTAMP的范围限制（1970-2038）

主键设计最佳实践

1. 主键类型选择

最佳实践：

优先使用自增整数（AUTO_INCREMENT）作为主键
避免使用字符串作为主键，特别是UUID
复合主键应谨慎使用，尽量不超过3列
主键列应尽量短，减少索引存储占用

2. 主键设计示例

sql

-- 推荐：自增整数主键
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 不推荐：字符串主键
CREATE TABLE orders (
    order_id VARCHAR(36) PRIMARY KEY, -- UUID作为主键，性能较差
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL
);

-- 谨慎使用：复合主键
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id) -- 复合主键
);

索引设计最佳实践

1. 索引类型选择

索引类型	适用场景
B-tree索引	大部分查询场景，包括等值查询、范围查询、排序
Hash索引	只支持等值查询，不支持范围查询和排序
Full-text索引	全文搜索
Spatial索引	空间数据查询

2. 索引设计原则

最佳实践：

为经常用于查询条件的字段建立索引
为JOIN条件中的字段建立索引
为ORDER BY和GROUP BY字段建立索引
避免为低选择性字段建立索引（如性别、状态码）
控制索引数量，每个表索引数不宜超过5个
定期优化和重建索引

3. 联合索引设计

最佳实践：

遵循最左前缀原则
将选择性高的字段放在前面
考虑查询覆盖，包含常用的查询字段
避免冗余索引

示例：

sql

-- 联合索引设计
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    order_status TINYINT NOT NULL,
    -- 联合索引：user_id（高选择性）+ order_date（范围查询）
    INDEX idx_user_date (user_id, order_date)
);

-- 有效查询：使用最左前缀
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND order_date >= '2025-01-01';

-- 无效查询：不使用最左前缀
SELECT * FROM orders WHERE order_date >= '2025-01-01';

4. 索引优化示例

问题：某电商网站的商品查询SQL执行缓慢：

sql

SELECT * FROM products WHERE category_id = 123 AND price BETWEEN 100 AND 200 ORDER BY sales_count DESC;

解决方案：

sql

-- 为查询条件和排序字段建立联合索引
CREATE INDEX idx_category_price_sales ON products(category_id, price, sales_count DESC);

分区表设计最佳实践

1. 分区类型选择

分区类型	适用场景
RANGE分区	按范围划分（如日期、数值）
LIST分区	按离散值划分（如地区、状态）
HASH分区	均匀分布数据
KEY分区	基于主键或唯一键的哈希分区

2. 分区设计原则

最佳实践：

对大数据量表使用分区表
选择合适的分区键，考虑数据访问模式
控制每个分区的大小，建议在10GB-50GB之间
避免过度分区，分区数不宜超过100个
考虑分区裁剪，提高查询效率

3. 分区表示例

sql

-- 按日期范围分区
CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 按状态LIST分区
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_status TINYINT NOT NULL,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL
) PARTITION BY LIST (order_status) (
    PARTITION pactive VALUES IN (1, 2, 3), -- 活跃订单
    PARTITION pcompleted VALUES IN (4), -- 已完成订单
    PARTITION pcanceled VALUES IN (5, 6) -- 已取消订单
);

表结构设计示例

1. 电商系统用户表设计

sql

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    phone VARCHAR(20) COMMENT '手机号',
    gender TINYINT DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女',
    birthday DATE COMMENT '生日',
    register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    last_login_time DATETIME COMMENT '最后登录时间',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
    -- 索引设计
    INDEX idx_register_time (register_time),
    INDEX idx_status (status)
) COMMENT='用户表';

2. 电商系统订单表设计

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    user_id INT NOT NULL COMMENT '用户ID',
    order_sn VARCHAR(30) NOT NULL UNIQUE COMMENT '订单编号',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
    actual_amount DECIMAL(10,2) NOT NULL COMMENT '实际支付金额',
    payment_method TINYINT NOT NULL COMMENT '支付方式：1-支付宝，2-微信，3-银行卡',
    order_status TINYINT NOT NULL DEFAULT 1 COMMENT '订单状态：1-待支付，2-待发货，3-待收货，4-已完成，5-已取消，6-已退款',
    shipping_address_id INT NOT NULL COMMENT '收货地址ID',
    order_remark VARCHAR(255) COMMENT '订单备注',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    -- 索引设计
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    INDEX idx_create_time (create_time),
    INDEX idx_order_sn (order_sn)
) COMMENT='订单表';

3. 电商系统商品表设计

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    product_name VARCHAR(100) NOT NULL COMMENT '商品名称',
    category_id INT NOT NULL COMMENT '分类ID',
    brand_id INT NOT NULL COMMENT '品牌ID',
    price DECIMAL(10,2) NOT NULL COMMENT '商品价格',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存数量',
    sales_count INT NOT NULL DEFAULT 0 COMMENT '销量',
    is_on_sale TINYINT NOT NULL DEFAULT 1 COMMENT '是否上架：0-下架，1-上架',
    description TEXT COMMENT '商品描述',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    -- 索引设计
    INDEX idx_category_id (category_id),
    INDEX idx_brand_id (brand_id),
    INDEX idx_is_on_sale (is_on_sale),
    INDEX idx_sales_count (sales_count),
    FULLTEXT INDEX ft_product_name (product_name) -- 全文索引用于商品搜索
) COMMENT='商品表';

表结构设计的常见问题及解决方案

1. 过度设计问题

问题现象：表结构过于复杂，包含过多的字段和关联关系

解决方案：

遵循KISS原则（Keep It Simple, Stupid）
避免过度范式化，适当冗余数据提高性能
拆分大表为多个小表，提高查询效率
考虑使用NoSQL数据库存储非结构化数据

2. 数据类型不当问题

问题现象：使用了不合适的数据类型，导致存储浪费或性能问题

解决方案：

选择最小的合适数据类型
金额等精确数值使用DECIMAL类型
避免使用TEXT/BLOB类型作为查询条件
日期时间类型选择合适的存储格式

3. 索引设计不合理问题

问题现象：索引过多或索引设计不当，导致插入更新性能下降

解决方案：

控制索引数量，每个表索引数不宜超过5个
为常用查询条件字段建立索引
遵循最左前缀原则设计联合索引
定期优化和重建索引

4. 主键设计不当问题

问题现象：使用了不合适的主键类型，导致性能问题

解决方案：

优先使用自增整数作为主键
避免使用字符串作为主键
复合主键应谨慎使用
主键列应尽量短

表结构设计的性能优化

1. 垂直拆分

适用场景：

表字段过多
部分字段访问频率高
部分字段数据量大

示例：

sql

-- 拆分前：大表包含所有字段
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    gender TINYINT,
    birthday DATE,
    register_time DATETIME,
    last_login_time DATETIME,
    status TINYINT,
    -- 大量不常用字段
    bio TEXT,
    avatar_url VARCHAR(255),
    address TEXT,
    preferences JSON
);

-- 拆分后：核心表 + 扩展表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    gender TINYINT,
    birthday DATE,
    register_time DATETIME,
    last_login_time DATETIME,
    status TINYINT
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    address TEXT,
    preferences JSON,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

2. 水平拆分

适用场景：

表数据量过大（超过1000万行）
单表查询性能下降
写入压力大

拆分策略：

按范围拆分（如按日期、ID范围）
按哈希拆分（如按用户ID哈希）
按列表拆分（如按地区、业务线）

示例：

sql

-- 按用户ID哈希拆分订单表
-- 拆分规则：order_id % 4，分为4个表
CREATE TABLE orders_0 (
    -- 表结构与原orders表相同
) PARTITION BY HASH (order_id) PARTITIONS 4;

-- 或者使用应用层分表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
CREATE TABLE orders_2 LIKE orders;
CREATE TABLE orders_3 LIKE orders;

表结构设计的维护

1. 定期审查和优化

定期分析表的使用情况
审查索引使用情况，删除无用索引
优化数据类型和表结构
重建碎片化的索引和表

2. 使用工具辅助设计

ER图设计工具（如MySQL Workbench、Navicat）
数据库设计规范检查工具
性能分析工具（如EXPLAIN、SHOW PROFILE）

3. 版本控制

使用版本控制系统管理数据库 schema 变更
制定数据库变更流程
进行充分的测试和验证

常见问题（FAQ）

Q1: 如何选择合适的数据类型？

A1: 选择数据类型时应考虑以下因素：

数据的实际范围和精度要求
存储效率
查询性能
数据完整性
未来的扩展需求

一般原则是：选择最小的合适数据类型，优先使用整数类型，其次是DECIMAL，最后是字符串类型。

Q2: 主键应该使用自增ID还是UUID？

A2: 建议优先使用自增ID作为主键，原因如下：

自增ID存储效率高，索引体积小
插入性能好，避免页分裂
查询效率高，特别是范围查询
便于调试和维护

UUID适合于分布式系统中需要全局唯一ID的场景，但会带来性能问题，应谨慎使用。

Q3: 如何设计联合索引？

A3: 设计联合索引时应遵循以下原则：

遵循最左前缀原则
将选择性高的字段放在前面
考虑查询覆盖，包含常用的查询字段
避免冗余索引
控制联合索引的长度

Q4: 什么时候需要使用分区表？

A4: 以下情况适合使用分区表：

表数据量过大（超过1000万行）
需要按时间范围快速查询历史数据
需要快速删除大量历史数据
数据具有明显的分区键特征

Q5: 如何优化大表的查询性能？

A5: 优化大表查询性能的方法包括：

合理设计索引
垂直拆分表，分离热点字段
水平拆分表，分散数据量
使用分区表
优化查询语句，避免全表扫描
增加从库，实现读写分离

Q6: 如何处理表结构变更？

A6: 处理表结构变更时应遵循以下流程：

在测试环境进行充分测试
选择业务低峰期进行变更
备份相关数据
使用合适的变更工具（如pt-online-schema-change）
监控变更过程和系统性能
进行验证测试

Q7: 如何设计高可用的表结构？

A7: 设计高可用表结构时应考虑：

使用合适的主键和索引设计
避免单点故障
考虑数据冗余和备份策略
设计合理的分片策略
考虑容灾和恢复方案

Q8: 如何设计支持大数据量的表结构？

A8: 设计支持大数据量的表结构时应考虑：

合理的分片策略
高效的索引设计
适当的数据冗余
考虑数据压缩
设计合理的数据生命周期管理策略

TDSQL 表结构设计最佳实践 ​

表结构设计的基本原则 ​

1. 范式原则 ​

2. 性能原则 ​

3. 可维护性原则 ​

4. 数据完整性原则 ​

数据类型选择最佳实践 ​

1. 数值类型选择 ​

2. 字符串类型选择 ​

3. 日期时间类型选择 ​

主键设计最佳实践 ​

1. 主键类型选择 ​

2. 主键设计示例 ​

索引设计最佳实践 ​

1. 索引类型选择 ​

2. 索引设计原则 ​

3. 联合索引设计 ​

4. 索引优化示例 ​

分区表设计最佳实践 ​

1. 分区类型选择 ​

2. 分区设计原则 ​

3. 分区表示例 ​

表结构设计示例 ​

1. 电商系统用户表设计 ​

2. 电商系统订单表设计 ​

3. 电商系统商品表设计 ​

表结构设计的常见问题及解决方案 ​

1. 过度设计问题 ​

2. 数据类型不当问题 ​

3. 索引设计不合理问题 ​

4. 主键设计不当问题 ​

表结构设计的性能优化 ​

1. 垂直拆分 ​

2. 水平拆分 ​

表结构设计的维护 ​

1. 定期审查和优化 ​

2. 使用工具辅助设计 ​

3. 版本控制 ​

常见问题（FAQ） ​

Q1: 如何选择合适的数据类型？ ​

Q2: 主键应该使用自增ID还是UUID？ ​

Q3: 如何设计联合索引？ ​

Q4: 什么时候需要使用分区表？ ​

Q5: 如何优化大表的查询性能？ ​

Q6: 如何处理表结构变更？ ​

Q7: 如何设计高可用的表结构？ ​

Q8: 如何设计支持大数据量的表结构？ ​

TDSQL 表结构设计最佳实践

表结构设计的基本原则

1. 范式原则

2. 性能原则

3. 可维护性原则

4. 数据完整性原则

数据类型选择最佳实践

1. 数值类型选择

2. 字符串类型选择

3. 日期时间类型选择

主键设计最佳实践

1. 主键类型选择

2. 主键设计示例

索引设计最佳实践

1. 索引类型选择

2. 索引设计原则

3. 联合索引设计

4. 索引优化示例

分区表设计最佳实践

1. 分区类型选择

2. 分区设计原则

3. 分区表示例

表结构设计示例

1. 电商系统用户表设计

2. 电商系统订单表设计

3. 电商系统商品表设计

表结构设计的常见问题及解决方案

1. 过度设计问题

2. 数据类型不当问题

3. 索引设计不合理问题

4. 主键设计不当问题

表结构设计的性能优化

1. 垂直拆分

2. 水平拆分

表结构设计的维护

1. 定期审查和优化

2. 使用工具辅助设计

3. 版本控制

常见问题（FAQ）

Q1: 如何选择合适的数据类型？

Q2: 主键应该使用自增ID还是UUID？

Q3: 如何设计联合索引？

Q4: 什么时候需要使用分区表？

Q5: 如何优化大表的查询性能？

Q6: 如何处理表结构变更？

Q7: 如何设计高可用的表结构？

Q8: 如何设计支持大数据量的表结构？