一、数据类型选择原则
1.1 精确数值类型
整数类型按存储空间和数值范围选择：
TINYINT -- 1字节（-128~127）
SMALLINT -- 2字节（±3.2万）
MEDIUMINT -- 3字节（±838万）
INT -- 4字节（±21亿）✅最常用
BIGINT -- 8字节（±922亿亿）
浮点型陷阱示例：
-- 错误用法
CREATE TABLE payments (
amount FLOAT(7,2)
);
-- 正确做法
CREATE TABLE financial_records (
transaction DECIMAL(15,2) UNSIGNED,
rate DECIMAL(5,4)
);
1.2 字符串类型优化
不同场景类型对比：
VARCHAR(255) -- UTF8实际存储765字节
CHAR(32) -- 固定长度验证码存储
TEXT -- 最大65535字符
BLOB -- 二进制文件存储
ENUM('Y','N') -- 有限选项时性能提升40%
JSON类型实战：
CREATE TABLE product_specs (
id INT PRIMARY KEY,
attributes JSON,
INDEX idx_attrs ((CAST(attributes->"$.weight" AS UNSIGNED)))
);
UPDATE product_specs
SET attributes = JSON_SET(attributes, '$.color', 'red')
WHERE id = 1001;
二、范式与反范式设计
2.1 三大范式实践
订单系统示例：
-- 违反第二范式设计
CREATE TABLE bad_orders (
order_id INT,
product_name VARCHAR(100), -- 依赖product_id更合适
price DECIMAL(10,2),
PRIMARY KEY(order_id)
);
-- 符合第三范式设计
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
created_at DATETIME
);
CREATE TABLE order_items (
item_id INT AUTO_INCREMENT,
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY(item_id),
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);
2.2 反范式设计场景
实时统计场景：
-- 原始设计（查询慢）
SELECT COUNT(*) FROM comments WHERE post_id=123;
-- 反范式优化
CREATE TABLE posts (
post_id INT PRIMARY KEY,
comment_count INT DEFAULT 0 -- 新增计数字段
);
-- 通过触发器维护
DELIMITER $$
CREATE TRIGGER update_comment_count
AFTER INSERT ON comments
FOR EACH ROW
BEGIN
UPDATE posts
SET comment_count = comment_count + 1
WHERE post_id = NEW.post_id;
END$$
DELIMITER ;
三、索引设计黄金法则
3.1 B+树索引原理
索引失效案例：
-- 创建组合索引
ALTER TABLE employees ADD INDEX idx_name_age (last_name, age);
-- 有效查询
SELECT * FROM employees
WHERE last_name = 'Smith' AND age > 30;
-- 索引失效查询（缺少最左前缀）
SELECT * FROM employees WHERE age = 35;
-- 使用函数导致失效
SELECT * FROM employees
WHERE YEAR(hire_date) = 2020;
3.2 全文索引实战
中文分词解决方案：
-- 启用ngram分词
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;
-- 搜索示例
SELECT * FROM articles
WHERE MATCH(content) AGAINST('数据库优化' IN BOOLEAN MODE);
四、表结构优化策略
4.1 垂直拆分案例
用户表拆分示例：
-- 原始大表
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
password CHAR(60),
profile_text TEXT,
last_login DATETIME,
login_count INT
);
-- 拆分后
CREATE TABLE user_basic (
user_id INT PRIMARY KEY,
username VARCHAR(50),
password CHAR(60),
last_login DATETIME,
login_count INT
);
CREATE TABLE user_profile (
user_id INT PRIMARY KEY,
profile_text TEXT,
FOREIGN KEY(user_id) REFERENCES user_basic(user_id)
);
4.2 水平分表方案
时间范围分表示例：
-- 2023年日志表
CREATE TABLE access_log_2023 (
log_id BIGINT AUTO_INCREMENT,
user_id INT,
access_time DATETIME,
PRIMARY KEY(log_id)
) PARTITION BY RANGE (YEAR(access_time)) (
PARTITION p2023 VALUES LESS THAN (2024)
);
-- 2024年自动扩展
ALTER TABLE access_log
ADD PARTITION p2024 VALUES LESS THAN (2025);
五、高性能设计技巧
5.1 预计算字段
物化视图实现：
-- 创建汇总表
CREATE TABLE sales_summary (
product_id INT PRIMARY KEY,
total_sales DECIMAL(15,2),
last_updated TIMESTAMP
);
-- 定时更新任务
CREATE EVENT update_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
REPLACE INTO sales_summary
SELECT product_id, SUM(amount), NOW()
FROM sales
GROUP BY product_id;
END
5.2 冷热数据分离
归档策略示例：
-- 创建历史表
CREATE TABLE orders_archive LIKE orders;
-- 迁移数据
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < '2020-01-01';
-- 原表删除旧数据
DELETE FROM orders
WHERE order_date < '2020-01-01'
LIMIT 1000; -- 分批删除
六、安全设计要点
6.1 敏感字段处理
加密存储示例：
-- 使用AES加密
CREATE TABLE users_secure (
user_id INT PRIMARY KEY,
id_card VARBINARY(200)
);
-- 插入加密数据
INSERT INTO users_secure
VALUES (1, AES_ENCRYPT('123456789', 'secret_key'));
-- 查询解密
SELECT AES_DECRYPT(id_card, 'secret_key')
FROM users_secure WHERE user_id=1;
6.2 权限控制
最小权限原则实现：
-- 创建只读用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'secure_pwd';
GRANT SELECT ON analytics.* TO 'report_user'@'%';
-- 应用用户权限控制
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'app_pwd123';
GRANT INSERT, UPDATE, DELETE ON orders.* TO 'app_user'@'192.168.1.%';
REVOKE DROP ON *.* FROM 'app_user'@'192.168.1.%';