1.6 漫画数据库设计实战 – 从零开始设计高性能数据库
🎯 学习目标
掌握数据库表结构设计原则
理解字段类型选择与优化
学会雪花算法ID生成策略
掌握索引设计与优化技巧
了解分库分表设计方案
📖 故事开始
小明: “老王，我总是不知道怎么设计数据库表，字段类型该选什么，索引怎么建？”
架构师老王: “哈哈，数据库设计就像盖房子，地基不牢，地动山摇！今天我们从头开始学习数据库设计的艺术。”
小明: “那从哪里开始呢？”
架构师老王: “先从一个电商系统的用户表开始…”
🏗️ 第一章：表结构设计原则
1.1 三大范式与反范式
架构师老王: “数据库设计有三大范式，但实际项目中我们经常需要反范式设计。”
-- 第一范式：原子性（每个字段不可再分）
-- ❌ 错误设计
CREATE TABLE user_bad (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
address TEXT -- 包含省市区，违反第一范式
);
-- ✅ 正确设计
CREATE TABLE user_good (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
detail_address VARCHAR(200)
);
-- 第二范式：完全函数依赖
-- ❌ 错误设计
CREATE TABLE order_item_bad (
order_id BIGINT,
product_id BIGINT,
product_name VARCHAR(100), -- 依赖于product_id，不依赖于组合主键
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
-- ✅ 正确设计
CREATE TABLE order_item_good (
order_id BIGINT,
product_id BIGINT,
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
-- 第三范式：消除传递依赖
-- ❌ 错误设计
CREATE TABLE employee_bad (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
department_id BIGINT,
department_name VARCHAR(100), -- 传递依赖于department_id
salary DECIMAL(10,2)
);
-- ✅ 正确设计
CREATE TABLE employee_good (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
department_id BIGINT,
salary DECIMAL(10,2)
);
CREATE TABLE department (
id BIGINT PRIMARY KEY,
name VARCHAR(100)
);
1.2 反范式设计场景
-- 电商订单表 - 为了查询性能，适当冗余
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
user_name VARCHAR(100), -- 冗余用户名，避免关联查询
user_phone VARCHAR(20), -- 冗余手机号
total_amount DECIMAL(12,2),
item_count INT, -- 冗余商品数量
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_status_created (status, created_at),
INDEX idx_created_at (created_at)
);
-- 商品表 - 冗余分类信息
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
category_id BIGINT,
category_name VARCHAR(100), -- 冗余分类名
brand_id BIGINT,
brand_name VARCHAR(100), -- 冗余品牌名
price DECIMAL(10,2),
stock INT DEFAULT 0,
sales_count INT DEFAULT 0, -- 冗余销量统计
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_brand (brand_id),
INDEX idx_price (price),
INDEX idx_sales (sales_count DESC)
);
🔢 第二章：字段类型选择与优化
2.1 数值类型选择
架构师老王: “选择合适的数据类型，既能节省存储空间，又能提高查询性能。”
-- 数值类型选择指南
CREATE TABLE type_examples (
-- 主键：使用BIGINT，支持雪花算法
id BIGINT UNSIGNED PRIMARY KEY,
-- 状态字段：使用TINYINT
status TINYINT UNSIGNED DEFAULT 0 COMMENT '0:待支付 1:已支付 2:已发货 3:已完成',
-- 年龄：使用TINYINT UNSIGNED (0-255)
age TINYINT UNSIGNED,
-- 计数器：根据预期大小选择
view_count INT UNSIGNED DEFAULT 0, -- 浏览量
like_count MEDIUMINT UNSIGNED DEFAULT 0, -- 点赞数
-- 金额：使用DECIMAL，避免浮点精度问题
price DECIMAL(10,2) NOT NULL COMMENT '价格，精确到分',
balance DECIMAL(15,2) DEFAULT 0.00 COMMENT '余额',
-- 百分比：可以存储为整数（乘以100）
discount_rate SMALLINT UNSIGNED COMMENT '折扣率，如85表示8.5折',
-- 时间戳：根据需求选择
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expired_at DATETIME COMMENT '过期时间'
);
2.2 字符串类型优化
-- 字符串类型选择
CREATE TABLE string_examples (
id BIGINT PRIMARY KEY,
-- 固定长度：使用CHAR
country_code CHAR(2) COMMENT '国家代码 CN/US',
gender CHAR(1) COMMENT '性别 M/F',
-- 变长字符串：使用VARCHAR
username VARCHAR(50) NOT NULL COMMENT '用户名',
email VARCHAR(100) COMMENT '邮箱',
phone VARCHAR(20) COMMENT '手机号',
-- 长文本：使用TEXT
description TEXT COMMENT '商品描述',
content LONGTEXT COMMENT '文章内容',
-- JSON数据：MySQL 5.7+支持JSON类型
extra_info JSON COMMENT '扩展信息',
-- 枚举类型：适合固定选项
priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_phone (phone)
);
-- 字符串长度优化示例
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY,
nickname VARCHAR(50), -- 昵称最多50字符
avatar_url VARCHAR(500), -- 头像URL
bio VARCHAR(500), -- 个人简介
location VARCHAR(100), -- 地理位置
website VARCHAR(200), -- 个人网站
-- 使用前缀索引优化长字符串
INDEX idx_avatar_prefix (avatar_url(100)),
INDEX idx_bio_prefix (bio(50))
);
❄️ 第三章：雪花算法ID生成策略
3.1 雪花算法原理
架构师老王: “雪花算法生成的ID是64位长整型，包含时间戳、机器ID和序列号。”
雪花算法ID结构（64位）：
┌─────────────────────────────────────────────────┬──────────┬──────────┬──────────────┐
│ 时间戳(41位) │机器ID(10位)│ 序列号(12位) │ 符号位(1位) │
└─────────────────────────────────────────────────┴──────────┴──────────┴──────────────┘
/**
* 雪花算法ID生成器
*/
@Component
public class SnowflakeIdGenerator {
// 起始时间戳 (2020-01-01)
private final long START_TIMESTAMP = 1577836800000L;
// 各部分位数
private final long SEQUENCE_BITS = 12;
private final long MACHINE_BITS = 10;
private final long TIMESTAMP_BITS = 41;
// 最大值
private final long MAX_SEQUENCE = ~(-1L << SEQUENCE_BITS);
private final long MAX_MACHINE_ID = ~(-1L << MACHINE_BITS);
// 位移
private final long MACHINE_SHIFT = SEQUENCE_BITS;
private final long TIMESTAMP_SHIFT = SEQUENCE_BITS + MACHINE_BITS;
private long machineId;
private long sequence = 0L;
private long lastTimestamp = -1L;
public SnowflakeIdGenerator() {
// 从配置或环境变量获取机器ID
this.machineId = getMachineId();
}
public synchronized long nextId() {
long currentTimestamp = System.currentTimeMillis();
// 时钟回拨检查
if (currentTimestamp < lastTimestamp) {
throw new RuntimeException("时钟回拨，拒绝生成ID");
}
if (currentTimestamp == lastTimestamp) {
// 同一毫秒内，序列号递增
sequence = (sequence + 1) & MAX_SEQUENCE;
if (sequence == 0) {
// 序列号溢出，等待下一毫秒
currentTimestamp = waitNextMillis(currentTimestamp);
}
} else {
// 不同毫秒，序列号重置
sequence = 0L;
}
lastTimestamp = currentTimestamp;
// 组装ID
return ((currentTimestamp - START_TIMESTAMP) << TIMESTAMP_SHIFT)
| (machineId << MACHINE_SHIFT)
| sequence;
}
private long waitNextMillis(long currentTimestamp) {
while (currentTimestamp <= lastTimestamp) {
currentTimestamp = System.currentTimeMillis();
}
return currentTimestamp;
}
private long getMachineId() {
// 可以从配置文件、环境变量或数据库获取
String machineIdStr = System.getProperty("machine.id", "1");
long id = Long.parseLong(machineIdStr);
if (id > MAX_MACHINE_ID || id < 0) {
throw new IllegalArgumentException("机器ID超出范围");
}
return id;
}
}
3.2 数据库表设计中的ID策略
-- 用户表 - 使用雪花算法ID
CREATE TABLE users (
id BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花算法生成的用户ID',
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE,
password_hash VARCHAR(255) NOT NULL,
salt VARCHAR(32) NOT NULL,
status TINYINT DEFAULT 1 COMMENT '1:正常 0:禁用',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_phone (phone),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
);
-- 订单表 - 雪花算法ID + 业务订单号
CREATE TABLE orders (
id BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花算法ID',
order_no VARCHAR(32) UNIQUE NOT NULL COMMENT '业务订单号',
user_id BIGINT UNSIGNED NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_no (order_no),
INDEX idx_user_id (user_id),
INDEX idx_status_created (status, created_at)
);
-- 订单号生成规则
-- 格式：日期(8位) + 机器ID(2位) + 序列号(6位)
-- 示例：2024010101000001
3.3 分布式ID生成服务
/**
* 分布式ID生成服务
*/
@Service
public class DistributedIdService {
@Autowired
private SnowflakeIdGenerator snowflakeGenerator;
@Autowired
private RedisTemplate<String, String> redisTemplate;
/**
* 生成用户ID
*/
public Long generateUserId() {
return snowflakeGenerator.nextId();
}
/**
* 生成订单号
*/
public String generateOrderNo() {
String date = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
String machineId = String.format("%02d", getMachineId());
// 使用Redis生成序列号，保证单机唯一
String key = "order_seq:" + date + ":" + machineId;
Long seq = redisTemplate.opsForValue().increment(key);
// 设置过期时间为2天
redisTemplate.expire(key, Duration.ofDays(2));
return date + machineId + String.format("%06d", seq);
}
/**
* 生成商品SKU编码
*/
public String generateSkuCode(Long categoryId) {
String categoryCode = String.format("%04d", categoryId);
String timestamp = String.valueOf(System.currentTimeMillis() % 100000);
String random = String.format("%03d", new Random().nextInt(1000));
return "SKU" + categoryCode + timestamp + random;
}
private int getMachineId() {
// 从配置获取机器ID
return Integer.parseInt(System.getProperty("machine.id", "1"));
}
}
📊 第四章：索引设计与优化
4.1 索引类型与选择
架构师老王: “索引就像书的目录，选对了事半功倍，选错了适得其反。”
-- 单列索引
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(200),
category_id BIGINT,
brand_id BIGINT,
price DECIMAL(10,2),
stock INT,
status TINYINT,
created_at TIMESTAMP,
-- 普通索引
INDEX idx_category (category_id),
INDEX idx_brand (brand_id),
INDEX idx_price (price),
INDEX idx_status (status),
-- 唯一索引
UNIQUE INDEX uk_name_brand (name, brand_id),
-- 前缀索引（适用于长字符串）
INDEX idx_name_prefix (name(20))
);
-- 复合索引设计
CREATE TABLE user_orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
status TINYINT,
order_type TINYINT,
total_amount DECIMAL(12,2),
created_at TIMESTAMP,
-- 复合索引：最左前缀原则
INDEX idx_user_status_created (user_id, status, created_at),
INDEX idx_status_type_amount (status, order_type, total_amount),
-- 覆盖索引：包含查询所需的所有字段
INDEX idx_user_cover (user_id, status, total_amount, created_at)
);
-- 函数索引（MySQL 8.0+）
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20),
created_at TIMESTAMP,
-- 函数索引：支持大小写不敏感查询
INDEX idx_email_lower ((LOWER(email))),
-- 表达式索引
INDEX idx_created_year ((YEAR(created_at)))
);
4.2 索引优化策略
-- 查询优化示例
-- ❌ 低效查询
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-01';
-- ✅ 高效查询（使用范围查询，能利用索引）
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
AND created_at < '2024-01-02 00:00:00';
-- ❌ 低效查询（函数导致索引失效）
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';
-- ✅ 高效查询（使用函数索引或存储计算结果）
SELECT * FROM users WHERE username = 'admin';
-- 分页查询优化
-- ❌ 深分页性能差
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 100000, 20;
-- ✅ 使用游标分页
SELECT * FROM products
WHERE created_at < '2024-01-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;
-- ✅ 使用ID分页
SELECT * FROM products
WHERE id > 1000000
ORDER BY id
LIMIT 20;
4.3 索引监控与维护
-- 查看索引使用情况
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
CARDINALITY,
SUB_PART,
NULLABLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, SEQ_IN_INDEX;
-- 查看未使用的索引
SELECT
s.TABLE_SCHEMA,
s.TABLE_NAME,
s.INDEX_NAME
FROM information_schema.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage t
ON s.TABLE_SCHEMA = t.OBJECT_SCHEMA
AND s.TABLE_NAME = t.OBJECT_NAME
AND s.INDEX_NAME = t.INDEX_NAME
WHERE t.INDEX_NAME IS NULL
AND s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
AND s.INDEX_NAME != 'PRIMARY';
-- 分析表和索引
ANALYZE TABLE products;
-- 优化表（重建索引）
OPTIMIZE TABLE products;
🔄 第五章：分库分表设计
5.1 垂直拆分
架构师老王: “当单表数据量过大时，我们需要考虑分库分表。先看垂直拆分。”
-- 原始用户表（字段过多）
CREATE TABLE users_original (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
password_hash VARCHAR(255),
salt VARCHAR(32),
nickname VARCHAR(50),
avatar_url VARCHAR(500),
gender TINYINT,
birthday DATE,
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
address VARCHAR(200),
bio TEXT,
hobby TEXT,
education VARCHAR(100),
occupation VARCHAR(100),
company VARCHAR(100),
created_at TIMESTAMP,
updated_at TIMESTAMP
);
-- 垂直拆分后
-- 用户基础信息表
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE,
password_hash VARCHAR(255) NOT NULL,
salt VARCHAR(32) NOT NULL,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_phone (phone)
);
-- 用户详细信息表
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY,
nickname VARCHAR(50),
avatar_url VARCHAR(500),
gender TINYINT,
birthday DATE,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 用户地址信息表
CREATE TABLE user_addresses (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
detail_address VARCHAR(200),
is_default TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
5.2 水平分表
-- 订单表水平分表（按月分表）
-- 2024年1月订单表
CREATE TABLE orders_202401 (
id BIGINT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE NOT NULL,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12,2),
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_no (order_no),
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
);
-- 2024年2月订单表
CREATE TABLE orders_202402 (
-- 结构相同
id BIGINT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE NOT NULL,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12,2),
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_no (order_no),
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
);
-- 分表路由逻辑
/**
* 分表路由服务
*/
@Service
public class ShardingService {
/**
* 根据时间路由到对应的订单表
*/
public String getOrderTableName(LocalDateTime createTime) {
String suffix = createTime.format(DateTimeFormatter.ofPattern("yyyyMM"));
return "orders_" + suffix;
}
/**
* 根据用户ID路由到对应的用户表
*/
public String getUserTableName(Long userId) {
// 按用户ID取模分表
int tableIndex = (int) (userId % 16);
return "users_" + String.format("%02d", tableIndex);
}
/**
* 获取查询时间范围内的所有表名
*/
public List<String> getOrderTableNames(LocalDateTime startTime, LocalDateTime endTime) {
List<String> tableNames = new ArrayList<>();
LocalDateTime current = startTime.withDayOfMonth(1);
while (!current.isAfter(endTime)) {
String suffix = current.format(DateTimeFormatter.ofPattern("yyyyMM"));
tableNames.add("orders_" + suffix);
current = current.plusMonths(1);
}
return tableNames;
}
}
5.3 分库策略
# ShardingSphere配置示例
spring:
shardingsphere:
datasource:
names: ds0,ds1,ds2,ds3
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/ecommerce_0
username: root
password: password
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/ecommerce_1
username: root
password: password
ds2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/ecommerce_2
username: root
password: password
ds3:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/ecommerce_3
username: root
password: password
rules:
sharding:
tables:
users:
actual-data-nodes: ds$->{
0..3}.users_$->{
00..15}
database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-database-inline
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-table-inline
orders:
actual-data-nodes: ds$->{
0..3}.orders_$->{
202401..202412}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: order-database-inline
table-strategy:
standard:
sharding-column: created_at
sharding-algorithm-name: order-table-inline
sharding-algorithms:
user-database-inline:
type: INLINE
props:
algorithm-expression: ds$->{
id % 4}
user-table-inline:
type: INLINE
props:
algorithm-expression: users_$->{
String.format('%02d', id % 16)}
order-database-inline:
type: INLINE
props:
algorithm-expression: ds$->{
user_id % 4}
order-table-inline:
type: INLINE
props:
algorithm-expression: orders_$->{
created_at.format('yyyyMM')}
🎯 第六章：性能优化实战
6.1 查询优化
-- 商品搜索优化
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
category_id BIGINT,
brand_id BIGINT,
price DECIMAL(10,2),
stock INT DEFAULT 0,
sales_count INT DEFAULT 0,
rating DECIMAL(3,2) DEFAULT 0,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 复合索引优化多条件查询
INDEX idx_category_price_sales (category_id, price, sales_count DESC),
INDEX idx_brand_price (brand_id, price),
INDEX idx_status_created (status, created_at DESC),
-- 全文索引支持商品名称搜索
FULLTEXT INDEX ft_name (name)
);
-- 优化后的查询
-- 分类 + 价格区间 + 排序
SELECT id, name, price, sales_count
FROM products
WHERE category_id = 1001
AND price BETWEEN 100 AND 500
AND status = 1
ORDER BY sales_count DESC
LIMIT 20;
-- 使用全文索引搜索
SELECT id, name, price
FROM products
WHERE MATCH(name) AGAINST('手机 华为' IN NATURAL LANGUAGE MODE)
AND status = 1
ORDER BY rating DESC
LIMIT 20;
6.2 统计查询优化
-- 订单统计表（预计算）
CREATE TABLE order_statistics (
id BIGINT PRIMARY KEY,
stat_date DATE NOT NULL,
stat_type TINYINT NOT NULL COMMENT '1:日统计 2:月统计',
total_orders INT DEFAULT 0,
total_amount DECIMAL(15,2) DEFAULT 0,
avg_amount DECIMAL(10,2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE INDEX uk_date_type (stat_date, stat_type),
INDEX idx_stat_date (stat_date)
);
-- 用户行为统计表
CREATE TABLE user_behavior_stats (
user_id BIGINT PRIMARY KEY,
total_orders INT DEFAULT 0,
total_amount DECIMAL(15,2) DEFAULT 0,
last_order_time TIMESTAMP NULL,
avg_order_amount DECIMAL(10,2) DEFAULT 0,
favorite_category_id BIGINT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_total_amount (total_amount DESC),
INDEX idx_last_order (last_order_time DESC)
);
-- 定时任务更新统计数据
/**
* 统计数据更新服务
*/
@Service
public class StatisticsService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private StatisticsMapper statisticsMapper;
/**
* 每日统计任务
*/
@Scheduled(cron = "0 0 1 * * ?") // 每天凌晨1点执行
public void updateDailyStatistics() {
LocalDate yesterday = LocalDate.now().minusDays(1);
// 计算昨日订单统计
OrderStatistics stats = orderMapper.getDailyStatistics(yesterday);
// 更新或插入统计数据
statisticsMapper.upsertDailyStats(stats);
log.info("更新日统计数据完成: {}", yesterday);
}
/**
* 用户行为统计更新
*/
@Async
public void updateUserBehaviorStats(Long userId) {
UserBehaviorStats stats = orderMapper.getUserBehaviorStats(userId);
statisticsMapper.updateUserBehaviorStats(stats);
}
}
📋 面试常考知识点
Q1: 如何选择合适的数据类型？
A:
数值类型：根据取值范围选择最小的类型
字符串：固定长度用CHAR，变长用VARCHAR
时间：TIMESTAMP vs DATETIME的区别
金额：使用DECIMAL避免精度问题
Q2: 雪花算法的优缺点？
A:
优点：全局唯一、趋势递增、高性能
缺点：依赖系统时钟、机器ID管理复杂
替代方案：UUID、数据库自增ID、Redis生成
Q3: 如何设计高效的索引？
A:
遵循最左前缀原则
避免在索引列上使用函数
考虑覆盖索引减少回表
定期监控和清理无用索引
Q4: 什么时候需要分库分表？
A:
单表数据量超过1000万
单库连接数不够用
读写QPS达到瓶颈
需要考虑数据一致性和跨库查询问题
🎯 最佳实践总结
架构师老王: “数据库设计的核心原则：”
合理范式化: 在性能和规范之间找平衡
选择合适类型: 够用就好，不要过度设计
索引设计: 查询驱动，定期优化
分库分表: 提前规划，平滑扩展
监控运维: 持续优化，预防问题
小明: “原来数据库设计有这么多门道！”
架构师老王: “是的，好的数据库设计是系统性能的基石。记住：设计时多思考，运行时少烦恼！”
© 版权声明
文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!
THE END
暂无评论内容