目录
- 索引基础概念
- 聚集索引详解
- 非聚集索引详解
- 核心区别对比
- 性能影响分析
- 实际应用场景
- 最佳实践建议
索引基础概念
什么是索引
索引是数据库管理系统中一种重要的数据结构，它为表中的数据创建有序的访问路径，类似于书籍的目录。通过索引，数据库可以快速定位到所需的数据行，避免全表扫描，从而大幅提升查询性能。
索引的存储结构
MySQL中的索引主要基于B+树数据结构实现：
- B+树特点：多路平衡查找树，所有叶子节点在同一层
- 查询效率：时间复杂度为O(log n)
- 范围查询：叶子节点通过指针连接，便于范围扫描
-- 查看表的索引信息
SHOW INDEX FROM table_name;
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE id = 1;
聚集索引详解
定义与特点
聚集索引（Clustered Index）是一种特殊的索引类型，其特点是索引顺序与表中数据的物理存储顺序完全一致。
核心特征
1. 物理存储顺序
聚集索引B+树结构：
[Root Node]
/ \
[Internal] [Internal]
/ | \ / | \
[Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
| | | | |
实际数据 实际数据 实际数据 实际数据 实际数据
2. 唯一性
- 每个表最多只能有一个聚集索引
- 聚集索引的叶子节点直接存储完整的数据行
3. InnoDB中的聚集索引
在InnoDB存储引擎中：
-- 主键自动成为聚集索引
CREATE TABLE users (
id INT PRIMARY KEY, -- 聚集索引
name VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP
);
-- 如果没有主键，InnoDB会选择第一个唯一非空索引
CREATE TABLE products (
sku VARCHAR(20) UNIQUE NOT NULL, -- 成为聚集索引
name VARCHAR(100),
price DECIMAL(10,2)
);
-- 如果没有合适的索引，InnoDB会创建隐藏的6字节ROWID
CREATE TABLE logs (
message TEXT,
level VARCHAR(10)
-- InnoDB内部创建隐藏的ROWID作为聚集索引
);
聚集索引的工作原理
数据查询过程
-- 基于聚集索引的查询
SELECT * FROM users WHERE id = 100;
查询步骤：
- 从B+树根节点开始
- 根据id=100逐层向下查找
- 直接在叶子节点获取完整数据行
- 只需一次磁盘I/O操作
范围查询优势
-- 范围查询效率高
SELECT * FROM users WHERE id BETWEEN 100 AND 200;
由于数据物理有序，范围查询只需顺序扫描相邻的叶子节点。
聚集索引的优缺点
✅ 优点
- 查询速度快：直接获取数据，无需额外查找
- 范围查询高效：物理顺序与逻辑顺序一致
- 减少磁盘I/O：数据和索引存储在一起
❌ 缺点
- 插入性能影响：可能导致页分裂
- 更新主键代价高：需要重新组织数据
- 唯一性限制：每表只能有一个
非聚集索引详解
定义与特点
非聚集索引（Non-Clustered Index）是独立于数据存储的索引结构，索引顺序与数据物理存储顺序无关。
核心特征
1. 逻辑结构分离
非聚集索引B+树结构：
[Root Node]
/ \
[Internal] [Internal]
/ | \ / | \
[Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
| | | | |
指向数据 指向数据 指向数据 指向数据 指向数据
↓ ↓ ↓ ↓ ↓
[数据页] [数据页] [数据页] [数据页] [数据页]
2. 多样性
- 一个表可以有多个非聚集索引
- 叶子节点存储索引键值和指向数据的指针
3. 在InnoDB中的实现
-- 创建非聚集索引
CREATE TABLE users (
id INT PRIMARY KEY, -- 聚集索引
name VARCHAR(50),
email VARCHAR(100) UNIQUE, -- 非聚集索引
age INT,
city VARCHAR(50),
created_at TIMESTAMP,
INDEX idx_name (name), -- 非聚集索引
INDEX idx_age_city (age, city), -- 复合非聚集索引
INDEX idx_created (created_at) -- 非聚集索引
);
非聚集索引的工作原理
InnoDB中的二次查找
在InnoDB中，非聚集索引的叶子节点存储的是主键值，需要进行"回表"操作：
-- 基于非聚集索引的查询
SELECT * FROM users WHERE name = 'John';
查询步骤：
- 在name索引的B+树中查找’John’
- 获取对应的主键值（如id=100）
- 使用主键值在聚集索引中查找
- 获取完整的数据行
- 需要两次磁盘I/O操作
覆盖索引优化
-- 创建覆盖索引，避免回表
CREATE INDEX idx_name_email ON users(name, email);
-- 这个查询可以直接从索引获取所需数据
SELECT name, email FROM users WHERE name = 'John';
非聚集索引的优缺点
✅ 优点
- 数量不限：可创建多个索引满足不同查询需求
- 插入友好：不影响数据的物理存储顺序
- 灵活性高：可针对不同列组合创建索引
❌ 缺点
- 查询性能：需要回表操作，增加I/O开销
- 存储开销：额外的存储空间
- 维护成本：DML操作需要同时维护多个索引
核心区别对比
1. 存储方式对比
| 特性 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 数据存储 | 叶子节点直接存储数据行 | 叶子节点存储指针或主键值 |
| 物理顺序 | 与索引顺序一致 | 与索引顺序无关 |
| 数据查找 | 直接获取 | 需要额外查找（回表） |
2. 性能对比
-- 性能测试示例
CREATE TABLE performance_test (
id INT PRIMARY KEY, -- 聚集索引
name VARCHAR(50),
email VARCHAR(100),
score INT,
INDEX idx_name (name), -- 非聚集索引
INDEX idx_score (score) -- 非聚集索引
);
-- 插入测试数据
INSERT INTO performance_test (id, name, email, score)
VALUES (1, 'Alice', 'alice@email.com', 95);
-- 聚集索引查询（速度快）
SELECT * FROM performance_test WHERE id = 1;
-- 执行计划：type=const, rows=1
-- 非聚集索引查询（需要回表）
SELECT * FROM performance_test WHERE name = 'Alice';
-- 执行计划：type=ref, extra=null（表示回表）
-- 覆盖索引查询（速度快，无需回表）
SELECT name FROM performance_test WHERE name = 'Alice';
-- 执行计划：type=ref, extra=Using index
3. 数量限制对比
-- 聚集索引：每表最多1个
ALTER TABLE users ADD PRIMARY KEY (id); -- 只能有一个主键
-- 非聚集索引：可以有多个
CREATE INDEX idx_1 ON users(name);
CREATE INDEX idx_2 ON users(email);
CREATE INDEX idx_3 ON users(age, city);
CREATE INDEX idx_4 ON users(created_at);
-- ... 可以继续创建更多
4. 维护成本对比
插入操作影响
-- 聚集索引的插入可能导致页分裂
INSERT INTO users (id, name) VALUES (150, 'Bob'); -- 如果150插入到已满的页中
-- 非聚集索引的插入相对简单
-- 只需在索引树中插入新的索引项
更新操作影响
-- 更新聚集索引键值（代价高）
UPDATE users SET id = 999 WHERE id = 1; -- 可能需要移动整行数据
-- 更新非聚集索引键值（代价相对较低）
UPDATE users SET name = 'NewName' WHERE id = 1; -- 只需更新索引项
性能影响分析
1. 查询性能
单行查询
-- 聚集索引查询（最优）
SELECT * FROM users WHERE id = 1;
-- I/O次数：1次，直接命中
-- 非聚集索引查询
SELECT * FROM users WHERE email = 'test@email.com';
-- I/O次数：2次，索引查找 + 回表
-- 覆盖索引查询（优化）
SELECT id, email FROM users WHERE email = 'test@email.com';
-- I/O次数：1次，无需回表
范围查询
-- 聚集索引范围查询（高效）
SELECT * FROM users WHERE id BETWEEN 100 AND 200;
-- 连续的磁盘读取，效率高
-- 非聚集索引范围查询
SELECT * FROM users WHERE age BETWEEN 25 AND 35;
-- 可能需要多次随机I/O，效率较低
2. 插入性能
顺序插入 vs 随机插入
-- 聚集索引顺序插入（高效）
INSERT INTO users (id, name) VALUES
(1001, 'User1'),
(1002, 'User2'),
(1003, 'User3');
-- 聚集索引随机插入（可能导致页分裂）
INSERT INTO users (id, name) VALUES
(505, 'User505'), -- 插入到中间位置
(203, 'User203'),
(799, 'User799');
页分裂现象
-- 监控页分裂
SHOW GLOBAL STATUS LIKE 'innodb_page_splits';
-- 优化：使用AUTO_INCREMENT避免随机插入
CREATE TABLE optimized_users (
id INT AUTO_INCREMENT PRIMARY KEY, -- 顺序增长
name VARCHAR(50),
email VARCHAR(100)
);
3. 存储空间
-- 查看表和索引的存储空间
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Total Size (MB)',
ROUND((data_length / 1024 / 1024), 2) AS 'Data Size (MB)',
ROUND((index_length / 1024 / 1024), 2) AS 'Index Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_name = 'your_table';
实际应用场景
1. 电商平台场景
用户系统
-- 用户表设计（千万级用户）
CREATE TABLE users (
user_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，用于主键查询
mobile VARCHAR(20) UNIQUE, -- 非聚集索引，手机号登录
email VARCHAR(100) UNIQUE, -- 非聚集索引，邮箱登录
username VARCHAR(50),
password_hash VARCHAR(255),
status TINYINT DEFAULT 1, -- 1:正常 0:禁用
register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login_time TIMESTAMP,
-- 业务查询索引
INDEX idx_mobile (mobile), -- 手机号登录：SELECT * FROM users WHERE mobile = '13812345678'
INDEX idx_email (email), -- 邮箱登录：SELECT * FROM users WHERE email = 'user@example.com'
INDEX idx_status_register (status, register_time), -- 用户统计：按状态和注册时间
INDEX idx_last_login (last_login_time) -- 活跃用户分析
);
-- 实际生产查询场景
-- 1. 用户登录（每日千万次）
SELECT user_id, username, status FROM users WHERE mobile = '13812345678' AND password_hash = 'xxx';
-- 2. 用户详情页（高频查询）
SELECT * FROM users WHERE user_id = 123456; -- 聚集索引，最快
-- 3. 运营分析查询
SELECT COUNT(*) FROM users WHERE status = 1 AND register_time >= '2024-01-01'; -- 使用复合索引
-- 4. 找回密码功能
SELECT user_id, username FROM users WHERE email = 'user@example.com';
订单系统
-- 订单表（亿级订单量）
CREATE TABLE orders (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
order_no VARCHAR(32) UNIQUE, -- 业务订单号，给用户看的
user_id BIGINT, -- 买家ID
seller_id BIGINT, -- 卖家ID
total_amount DECIMAL(15,2), -- 订单总金额
order_status TINYINT, -- 订单状态：1待付款 2已付款 3已发货 4已完成 5已取消
payment_status TINYINT, -- 支付状态
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 核心业务索引
INDEX idx_order_no (order_no), -- 订单号查询：用户查看订单详情
INDEX idx_user_status_time (user_id, order_status, created_at), -- 用户订单列表
INDEX idx_seller_status_time (seller_id, order_status, created_at), -- 商家订单管理
INDEX idx_status_amount (order_status, total_amount), -- 财务对账
INDEX idx_created_time (created_at), -- 按时间范围统计订单
INDEX idx_payment_status (payment_status, created_at) -- 支付相关查询
);
-- 生产环境查询实例
-- 1. 用户查看自己的订单（用户APP中最频繁的查询）
SELECT order_id, order_no, total_amount, order_status, created_at
FROM orders
WHERE user_id = 123456 AND order_status IN (1,2,3,4)
ORDER BY created_at DESC LIMIT 20; -- 使用 idx_user_status_time
-- 2. 订单详情查询（通过订单号）
SELECT * FROM orders WHERE order_no = 'ORD20241201123456'; -- 使用 idx_order_no
-- 3. 商家订单管理
SELECT order_id, order_no, user_id, total_amount, order_status, created_at
FROM orders
WHERE seller_id = 789 AND order_status = 2
AND created_at >= '2024-12-01'
ORDER BY created_at DESC; -- 使用 idx_seller_status_time
-- 4. 财务日报（每日定时任务）
SELECT
DATE(created_at) as order_date,
COUNT(*) as order_count,
SUM(total_amount) as total_revenue
FROM orders
WHERE order_status = 4 -- 已完成订单
AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at); -- 使用 idx_status_amount 和 idx_created_time
商品系统
-- 商品表（百万级商品）
CREATE TABLE products (
product_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
sku VARCHAR(50) UNIQUE, -- 商品SKU，唯一标识
spu_id BIGINT, -- 标准产品单元ID
category_id INT, -- 分类ID
brand_id INT, -- 品牌ID
title VARCHAR(500), -- 商品标题
price DECIMAL(10,2), -- 价格
stock_quantity INT, -- 库存数量
sales_count INT DEFAULT 0, -- 销量
status TINYINT DEFAULT 1, -- 1上架 0下架
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 搜索和筛选索引
INDEX idx_sku (sku), -- SKU查询
INDEX idx_category_status_sales (category_id, status, sales_count), -- 分类页面排序
INDEX idx_brand_status (brand_id, status), -- 品牌筛选
INDEX idx_price_status (price, status), -- 价格筛选
INDEX idx_status_created (status, created_at), -- 新品上架
INDEX idx_sales_status (sales_count, status), -- 热销排序
FULLTEXT idx_title_search (title) -- 全文搜索
);
-- 商品搜索页面（用户最常用功能）
-- 1. 分类浏览 + 销量排序
SELECT product_id, title, price, sales_count
FROM products
WHERE category_id = 123 AND status = 1
ORDER BY sales_count DESC LIMIT 20; -- 使用 idx_category_status_sales
-- 2. 价格区间筛选
SELECT product_id, title, price
FROM products
WHERE price BETWEEN 100.00 AND 500.00 AND status = 1
ORDER BY sales_count DESC; -- 使用 idx_price_status
-- 3. 品牌筛选
SELECT product_id, title, price, sales_count
FROM products
WHERE brand_id = 456 AND status = 1
ORDER BY created_at DESC; -- 使用 idx_brand_status
-- 4. 商品详情（通过SKU，仓库管理常用）
SELECT * FROM products WHERE sku = 'PHONE-IPHONE15-256GB-BLACK';
2. 金融支付系统
支付流水表
-- 支付流水（十亿级记录）
CREATE TABLE payment_records (
record_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，自增ID
payment_no VARCHAR(32) UNIQUE, -- 支付单号，业务主键
order_no VARCHAR(32), -- 关联订单号
user_id BIGINT, -- 用户ID
merchant_id BIGINT, -- 商户ID
amount DECIMAL(15,2), -- 支付金额
currency VARCHAR(3) DEFAULT 'CNY', -- 货币类型
payment_method TINYINT, -- 支付方式：1微信 2支付宝 3银联 4余额
payment_status TINYINT, -- 支付状态：1待支付 2支付中 3支付成功 4支付失败 5已退款
transaction_id VARCHAR(64), -- 第三方交易ID
created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), -- 毫秒级时间戳
completed_at TIMESTAMP(3), -- 支付完成时间
-- 业务查询索引
INDEX idx_payment_no (payment_no), -- 支付单号查询
INDEX idx_order_no (order_no), -- 通过订单号查支付记录
INDEX idx_user_status_time (user_id, payment_status, created_at), -- 用户支付历史
INDEX idx_merchant_time (merchant_id, created_at), -- 商户流水查询
INDEX idx_status_method_time (payment_status, payment_method, created_at), -- 支付分析
INDEX idx_transaction_id (transaction_id), -- 第三方交易ID查询
INDEX idx_amount_time (amount, created_at), -- 大额交易监控
INDEX idx_completed_time (completed_at) -- 按完成时间统计
);
-- 生产环境关键查询
-- 1. 支付状态查询（用户支付后轮询）
SELECT payment_status, amount, completed_at
FROM payment_records
WHERE payment_no = 'PAY20241201123456'; -- 使用聚集索引或唯一索引
-- 2. 用户支付历史（用户中心）
SELECT payment_no, order_no, amount, payment_method, payment_status, created_at
FROM payment_records
WHERE user_id = 123456 AND payment_status = 3
ORDER BY created_at DESC LIMIT 50; -- 使用 idx_user_status_time
-- 3. 商户对账（每日定时）
SELECT
DATE(completed_at) as pay_date,
COUNT(*) as transaction_count,
SUM(amount) as total_amount
FROM payment_records
WHERE merchant_id = 789
AND payment_status = 3
AND completed_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(completed_at); -- 使用 idx_merchant_time
-- 4. 风控监控（大额交易实时监控）
SELECT record_id, payment_no, user_id, amount, created_at
FROM payment_records
WHERE amount >= 10000.00
AND created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND payment_status IN (2, 3); -- 使用 idx_amount_time
账户余额表
-- 用户账户表
CREATE TABLE user_accounts (
account_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
user_id BIGINT UNIQUE, -- 用户ID，一对一关系
balance DECIMAL(15,2) DEFAULT 0.00, -- 账户余额
frozen_amount DECIMAL(15,2) DEFAULT 0.00, -- 冻结金额
total_recharge DECIMAL(15,2) DEFAULT 0.00, -- 累计充值
total_withdraw DECIMAL(15,2) DEFAULT 0.00, -- 累计提现
version INT DEFAULT 0, -- 乐观锁版本号
status TINYINT DEFAULT 1, -- 账户状态
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id), -- 通过用户ID查账户
INDEX idx_balance (balance), -- 余额查询（风控）
INDEX idx_status (status) -- 账户状态筛选
);
-- 余额变动记录表（用于对账和审计）
CREATE TABLE balance_changes (
change_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
user_id BIGINT, -- 用户ID
account_id BIGINT, -- 账户ID
change_type TINYINT, -- 变动类型：1充值 2消费 3提现 4退款 5奖励
amount DECIMAL(15,2), -- 变动金额（正负数）
balance_before DECIMAL(15,2), -- 变动前余额
balance_after DECIMAL(15,2), -- 变动后余额
business_no VARCHAR(32), -- 业务单号
description VARCHAR(200), -- 变动描述
created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
INDEX idx_user_time (user_id, created_at), -- 用户余额变动历史
INDEX idx_account_time (account_id, created_at), -- 账户变动记录
INDEX idx_business_no (business_no), -- 业务单号查询
INDEX idx_type_time (change_type, created_at), -- 按类型统计
INDEX idx_amount_time (amount, created_at) -- 大额变动监控
);
3. 社交媒体/内容系统
用户动态表
-- 用户动态/朋友圈（亿级数据）
CREATE TABLE user_posts (
post_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
user_id BIGINT, -- 发布用户ID
content TEXT, -- 动态内容
image_urls JSON, -- 图片URL数组
post_type TINYINT DEFAULT 1, -- 动态类型：1普通 2转发 3视频
visibility TINYINT DEFAULT 1, -- 可见性：1公开 2好友 3私密
like_count INT DEFAULT 0, -- 点赞数
comment_count INT DEFAULT 0, -- 评论数
share_count INT DEFAULT 0, -- 分享数
status TINYINT DEFAULT 1, -- 状态：1正常 0删除
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 核心查询索引
INDEX idx_user_status_time (user_id, status, created_at), -- 个人主页时间线
INDEX idx_visibility_time (visibility, created_at), -- 公开动态流
INDEX idx_like_count_time (like_count, created_at), -- 热门内容排序
INDEX idx_type_status_time (post_type, status, created_at), -- 按类型筛选
FULLTEXT idx_content_search (content) -- 内容全文搜索
);
-- 生产环境查询场景
-- 1. 个人主页时间线（用户查看自己或他人的动态）
SELECT post_id, content, image_urls, like_count, comment_count, created_at
FROM user_posts
WHERE user_id = 123456 AND status = 1
ORDER BY created_at DESC LIMIT 20; -- 使用 idx_user_status_time
-- 2. 公共动态流（首页推荐）
SELECT post_id, user_id, content, like_count, comment_count, created_at
FROM user_posts
WHERE visibility = 1 AND status = 1
AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY like_count DESC, created_at DESC LIMIT 50; -- 使用 idx_visibility_time
-- 3. 热门内容排行
SELECT post_id, user_id, content, like_count, comment_count
FROM user_posts
WHERE status = 1 AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY like_count DESC LIMIT 100; -- 使用 idx_like_count_time
关注关系表
-- 用户关注关系（千万级关系）
CREATE TABLE user_follows (
follow_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
follower_id BIGINT, -- 关注者ID
following_id BIGINT, -- 被关注者ID
status TINYINT DEFAULT 1, -- 关注状态：1关注 0取消关注
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 关键业务索引
UNIQUE KEY uk_follower_following (follower_id, following_id), -- 防重复关注
INDEX idx_follower_status (follower_id, status), -- 我关注的人
INDEX idx_following_status (following_id, status), -- 关注我的人
INDEX idx_following_time (following_id, created_at) -- 粉丝时间排序
);
-- 关注相关查询
-- 1. 我关注的人列表
SELECT following_id FROM user_follows
WHERE follower_id = 123456 AND status = 1;
-- 2. 关注我的人列表（粉丝列表）
SELECT follower_id FROM user_follows
WHERE following_id = 123456 AND status = 1
ORDER BY created_at DESC;
-- 3. 检查关注关系（判断是否已关注）
SELECT status FROM user_follows
WHERE follower_id = 123456 AND following_id = 789012;
4. 日志监控系统
应用日志表
-- 应用日志（每日千万级别）
CREATE TABLE application_logs (
log_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，时间顺序自增
trace_id VARCHAR(32), -- 链路追踪ID
app_name VARCHAR(50), -- 应用名称
log_level TINYINT, -- 日志级别：1DEBUG 2INFO 3WARN 4ERROR 5FATAL
logger_name VARCHAR(200), -- 日志器名称
message TEXT, -- 日志消息
exception_stack TEXT, -- 异常堆栈（如果有）
user_id BIGINT, -- 用户ID（业务日志）
ip_address VARCHAR(45), -- IP地址
user_agent VARCHAR(500), -- 用户代理
created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), -- 毫秒级时间戳
-- 查询和监控索引
INDEX idx_trace_id (trace_id), -- 链路追踪查询
INDEX idx_app_level_time (app_name, log_level, created_at), -- 应用错误监控
INDEX idx_level_time (log_level, created_at), -- 按级别查询日志
INDEX idx_user_time (user_id, created_at), -- 用户行为日志
INDEX idx_ip_time (ip_address, created_at), -- IP地址分析
INDEX idx_created_time (created_at), -- 时间范围查询
FULLTEXT idx_message_search (message) -- 日志内容搜索
);
-- 生产环境日志查询
-- 1. 链路追踪（问题排查最常用）
SELECT log_id, app_name, log_level, logger_name, message, created_at
FROM application_logs
WHERE trace_id = 'trace-123456789'
ORDER BY created_at ASC; -- 使用 idx_trace_id
-- 2. 错误日志监控（实时告警）
SELECT COUNT(*) as error_count
FROM application_logs
WHERE log_level >= 4 -- ERROR及以上级别
AND created_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE); -- 最近5分钟
-- 3. 应用健康检查
SELECT
app_name,
log_level,
COUNT(*) as log_count
FROM application_logs
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY app_name, log_level
ORDER BY app_name, log_level; -- 使用 idx_app_level_time
-- 4. 用户行为分析
SELECT message, created_at
FROM application_logs
WHERE user_id = 123456
AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY created_at DESC; -- 使用 idx_user_time
系统性能监控表
-- 系统性能指标（高频写入）
CREATE TABLE system_metrics (
metric_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，按时间自增
server_name VARCHAR(100), -- 服务器名称
metric_type VARCHAR(50), -- 指标类型：cpu, memory, disk, network
metric_name VARCHAR(100), -- 具体指标名：cpu_usage, memory_used等
metric_value DECIMAL(15,4), -- 指标值
unit VARCHAR(20), -- 单位：%, MB, KB/s等
tags JSON, -- 标签（主机、环境等）
collected_at TIMESTAMP(3), -- 采集时间
created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
-- 监控查询索引
INDEX idx_server_type_time (server_name, metric_type, collected_at), -- 服务器指标查询
INDEX idx_type_name_time (metric_type, metric_name, collected_at), -- 指标类型查询
INDEX idx_collected_time (collected_at), -- 时间范围查询
INDEX idx_server_collected (server_name, collected_at) -- 服务器时间序列
);
-- 性能监控查询
-- 1. 服务器CPU使用率趋势（最常用的监控图表）
SELECT
collected_at,
metric_value as cpu_usage
FROM system_metrics
WHERE server_name = 'web-server-01'
AND metric_type = 'cpu'
AND metric_name = 'cpu_usage'
AND collected_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY collected_at ASC; -- 使用 idx_server_type_time
-- 2. 内存使用率告警检查
SELECT server_name, metric_value, collected_at
FROM system_metrics
WHERE metric_type = 'memory'
AND metric_name = 'memory_usage_percent'
AND metric_value >= 90.0 -- 内存使用率超过90%
AND collected_at >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
ORDER BY metric_value DESC; -- 使用 idx_type_name_time
-- 3. 磁盘空间监控
SELECT
server_name,
MAX(metric_value) as max_disk_usage
FROM system_metrics
WHERE metric_type = 'disk'
AND metric_name = 'disk_usage_percent'
AND collected_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY server_name
HAVING max_disk_usage >= 85.0; -- 磁盘使用率超过85%
5. 物联网 (IoT) 数据处理系统
IoT传感器数据表
-- IoT传感器数据（每秒千万级数据写入）
CREATE TABLE sensor_data (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，时间序列自增
device_id VARCHAR(50), -- 设备ID
sensor_type TINYINT, -- 传感器类型：1温度 2湿度 3压力 4光照
sensor_value DECIMAL(10,4), -- 传感器数值
location_id INT, -- 地理位置ID
factory_id INT, -- 工厂ID
workshop_id INT, -- 车间ID
is_abnormal TINYINT DEFAULT 0, -- 是否异常：0正常 1异常
collected_at TIMESTAMP(3), -- 采集时间（毫秒精度）
created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
-- 核心查询索引
INDEX idx_device_type_time (device_id, sensor_type, collected_at), -- 设备历史数据查询
INDEX idx_location_time (location_id, collected_at), -- 区域环境监控
INDEX idx_factory_abnormal (factory_id, is_abnormal, collected_at), -- 工厂异常监控
INDEX idx_type_value_time (sensor_type, sensor_value, collected_at), -- 阈值告警查询
INDEX idx_workshop_time (workshop_id, collected_at) -- 车间生产监控
);
-- 生产环境IoT查询场景
-- 1. 实时设备监控（监控大屏每5秒刷新）
SELECT
device_id,
sensor_type,
AVG(sensor_value) as avg_value,
MAX(sensor_value) as max_value,
COUNT(CASE WHEN is_abnormal = 1 THEN 1 END) as abnormal_count
FROM sensor_data
WHERE factory_id = 101
AND collected_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
GROUP BY device_id, sensor_type; -- 使用 idx_factory_abnormal
-- 2. 温度异常告警（每分钟检查）
SELECT device_id, sensor_value, collected_at
FROM sensor_data
WHERE sensor_type = 1 -- 温度传感器
AND sensor_value > 80.0 -- 超过80度
AND collected_at >= DATE_SUB(NOW(), INTERVAL 1 MINUTE)
AND is_abnormal = 0; -- 避免重复告警
-- 3. 设备历史趋势分析（用户查看设备曲线图）
SELECT
DATE_FORMAT(collected_at, '%H:%i') as time_point,
AVG(sensor_value) as avg_temp
FROM sensor_data
WHERE device_id = 'TEMP_SENSOR_001'
AND sensor_type = 1
AND collected_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY DATE_FORMAT(collected_at, '%H:%i')
ORDER BY time_point; -- 使用 idx_device_type_time
6. 物流配送系统
快递运单表
-- 快递运单表（日处理量千万级）
CREATE TABLE express_orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，时间序列
tracking_number VARCHAR(30) UNIQUE, -- 运单号，对外展示
order_no VARCHAR(32), -- 关联业务订单号
sender_phone VARCHAR(20), -- 寄件人手机号
receiver_phone VARCHAR(20), -- 收件人手机号
sender_address VARCHAR(500), -- 寄件地址
receiver_address VARCHAR(500), -- 收件地址
sender_city_code VARCHAR(10), -- 寄件城市编码
receiver_city_code VARCHAR(10), -- 收件城市编码
package_weight DECIMAL(8,3), -- 包裹重量（kg）
freight_amount DECIMAL(10,2), -- 运费
express_type TINYINT, -- 快递类型：1标准 2加急 3当日达
current_status TINYINT, -- 当前状态：1已揽收 2运输中 3派送中 4已签收 5异常
current_location VARCHAR(200), -- 当前位置
courier_id BIGINT, -- 当前负责快递员ID
estimated_delivery TIMESTAMP, -- 预计送达时间
actual_delivery TIMESTAMP, -- 实际送达时间
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 业务核心索引
INDEX idx_tracking_number (tracking_number), -- 运单号查询（用户最常用）
INDEX idx_receiver_phone (receiver_phone), -- 收件人手机号查询
INDEX idx_sender_phone (sender_phone), -- 寄件人手机号查询
INDEX idx_courier_status (courier_id, current_status), -- 快递员工作台
INDEX idx_status_city_time (current_status, receiver_city_code, created_at), -- 城市配送统计
INDEX idx_type_estimated (express_type, estimated_delivery), -- 时效统计
INDEX idx_receiver_city_status (receiver_city_code, current_status) -- 区域配送管理
);
-- 生产环境物流查询场景
-- 1. 用户查询物流信息（最高频查询）
SELECT
tracking_number,
current_status,
current_location,
estimated_delivery,
updated_at
FROM express_orders
WHERE tracking_number = 'SF1234567890123'; -- 使用唯一索引
-- 2. 收件人查询包裹（手机号查询）
SELECT
tracking_number,
sender_phone,
current_status,
estimated_delivery
FROM express_orders
WHERE receiver_phone = '13812345678'
AND current_status IN (1, 2, 3) -- 未签收的包裹
ORDER BY created_at DESC; -- 使用 idx_receiver_phone
-- 3. 快递员工作台（派送任务管理）
SELECT
tracking_number,
receiver_phone,
receiver_address,
package_weight,
estimated_delivery
FROM express_orders
WHERE courier_id = 1001
AND current_status = 3 -- 派送中状态
AND receiver_city_code = 'BJ001' -- 北京区域
ORDER BY estimated_delivery ASC; -- 使用 idx_courier_status
7. 游戏行业数据系统
游戏玩家数据表
-- 游戏玩家数据表（千万级玩家）
CREATE TABLE game_players (
player_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，玩家唯一ID
player_uuid VARCHAR(36) UNIQUE, -- 玩家UUID，跨服标识
username VARCHAR(50) UNIQUE, -- 玩家用户名
email VARCHAR(100), -- 邮箱
phone VARCHAR(20), -- 手机号
server_id INT, -- 所在服务器ID
level INT DEFAULT 1, -- 玩家等级
experience BIGINT DEFAULT 0, -- 经验值
gold BIGINT DEFAULT 0, -- 游戏金币
diamond INT DEFAULT 0, -- 钻石数量
vip_level TINYINT DEFAULT 0, -- VIP等级
guild_id BIGINT, -- 公会ID
last_login_time TIMESTAMP, -- 最后登录时间
total_playtime INT DEFAULT 0, -- 总游戏时长（分钟）
player_status TINYINT DEFAULT 1, -- 玩家状态：1正常 2封号 3注销
register_ip VARCHAR(45), -- 注册IP
register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 游戏业务索引
INDEX idx_username (username), -- 用户名登录
INDEX idx_server_level (server_id, level), -- 服务器排行榜
INDEX idx_guild_level (guild_id, level), -- 公会成员排行
INDEX idx_vip_level (vip_level, level), -- VIP玩家分析
INDEX idx_login_time (last_login_time), -- 活跃玩家分析
INDEX idx_status_register (player_status, register_time), -- 玩家注册分析
INDEX idx_level_exp (level, experience) -- 等级经验分析
);
-- 生产环境游戏查询场景
-- 1. 玩家登录验证（每秒数万次）
SELECT player_id, player_uuid, level, server_id, player_status
FROM game_players
WHERE username = 'player123' AND player_status = 1; -- 使用 idx_username
-- 2. 服务器等级排行榜（游戏内排行榜系统）
SELECT player_id, username, level, experience
FROM game_players
WHERE server_id = 1001 AND player_status = 1
ORDER BY level DESC, experience DESC LIMIT 100; -- 使用 idx_server_level
-- 3. 公会成员管理（公会系统）
SELECT player_id, username, level, last_login_time, total_playtime
FROM game_players
WHERE guild_id = 12345 AND player_status = 1
ORDER BY level DESC; -- 使用 idx_guild_level
-- 4. VIP用户分析（运营分析）
SELECT
vip_level,
COUNT(*) as player_count,
AVG(level) as avg_level,
SUM(diamond) as total_diamond
FROM game_players
WHERE player_status = 1
GROUP BY vip_level
ORDER BY vip_level DESC; -- 使用 idx_vip_level
-- 5. 玩家流失分析（运营重点关注）
SELECT
DATE(last_login_time) as last_login_date,
COUNT(*) as player_count
FROM game_players
WHERE last_login_time < DATE_SUB(NOW(), INTERVAL 7 DAY)
AND register_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) -- 30天内注册但7天未登录
GROUP BY DATE(last_login_time)
ORDER BY last_login_date DESC; -- 使用 idx_login_time
游戏充值记录表
-- 游戏充值记录表（亿级交易数据）
CREATE TABLE game_payments (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，时间序列
payment_id VARCHAR(32) UNIQUE, -- 支付订单号
player_id BIGINT, -- 玩家ID
server_id INT, -- 服务器ID
product_id VARCHAR(20), -- 商品ID
product_name VARCHAR(100), -- 商品名称
payment_amount DECIMAL(10,2), -- 支付金额（人民币）
diamond_amount INT, -- 获得钻石数量
payment_channel TINYINT, -- 支付渠道：1微信 2支付宝 3苹果 4谷歌
payment_status TINYINT, -- 支付状态：1待支付 2已支付 3已发货 4失败 5退款
payment_time TIMESTAMP, -- 支付完成时间
device_type TINYINT, -- 设备类型：1iOS 2Android 3PC
client_version VARCHAR(20), -- 客户端版本
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 充值分析索引
INDEX idx_payment_id (payment_id), -- 支付订单查询
INDEX idx_player_status (player_id, payment_status), -- 玩家充值记录
INDEX idx_server_time (server_id, payment_time), -- 服务器收入统计
INDEX idx_product_time (product_id, payment_time), -- 商品销售分析
INDEX idx_channel_amount (payment_channel, payment_amount), -- 渠道收入分析
INDEX idx_status_time (payment_status, payment_time), -- 支付状态监控
INDEX idx_device_time (device_type, payment_time) -- 设备收入统计
);
-- 游戏充值查询场景
-- 1. 玩家充值历史（客服查询）
SELECT
payment_id,
product_name,
payment_amount,
diamond_amount,
payment_status,
payment_time
FROM game_payments
WHERE player_id = 123456
ORDER BY payment_time DESC LIMIT 50; -- 使用 idx_player_status
-- 2. 服务器日收入统计（运营日报）
SELECT
DATE(payment_time) as pay_date,
COUNT(*) as order_count,
SUM(payment_amount) as total_revenue,
AVG(payment_amount) as avg_amount
FROM game_payments
WHERE server_id = 1001
AND payment_status = 2 -- 已支付
AND payment_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(payment_time)
ORDER BY pay_date DESC; -- 使用 idx_server_time
-- 3. 商品销售排行（商品运营分析）
SELECT
product_id,
product_name,
COUNT(*) as sale_count,
SUM(payment_amount) as total_revenue
FROM game_payments
WHERE payment_status = 2
AND payment_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY product_id, product_name
ORDER BY total_revenue DESC LIMIT 20; -- 使用 idx_product_time
-- 4. 支付渠道分析（财务分析）
SELECT
payment_channel,
device_type,
COUNT(*) as transaction_count,
SUM(payment_amount) as channel_revenue,
AVG(payment_amount) as avg_transaction
FROM game_payments
WHERE payment_status = 2
AND payment_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY payment_channel, device_type; -- 使用 idx_channel_amount
8. 直播平台系统
直播间数据表
-- 直播间数据表（百万级直播间）
CREATE TABLE live_rooms (
room_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，直播间ID
room_number VARCHAR(20) UNIQUE, -- 直播间号码，对外展示
streamer_id BIGINT, -- 主播ID
room_title VARCHAR(200), -- 直播间标题
category_id INT, -- 直播分类ID
room_status TINYINT, -- 直播状态：1直播中 2暂停 3关闭
viewer_count INT DEFAULT 0, -- 当前观看人数
max_viewer_count INT DEFAULT 0, -- 历史最高人数
total_duration INT DEFAULT 0, -- 累计直播时长（分钟）
gift_total_value DECIMAL(15,2) DEFAULT 0, -- 礼物总价值
follower_count INT DEFAULT 0, -- 关注数
room_quality TINYINT DEFAULT 1, -- 画质：1流畅 2高清 3超清 4蓝光
is_recommended TINYINT DEFAULT 0, -- 是否推荐：0否 1是
start_time TIMESTAMP, -- 开播时间
end_time TIMESTAMP, -- 关播时间
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 直播业务索引
INDEX idx_room_number (room_number), -- 直播间号码查询
INDEX idx_streamer_status (streamer_id, room_status), -- 主播直播管理
INDEX idx_category_status (category_id, room_status), -- 分类直播列表
INDEX idx_status_viewer (room_status, viewer_count), -- 热门直播排序
INDEX idx_recommended_viewer (is_recommended, viewer_count), -- 推荐直播
INDEX idx_gift_value (gift_total_value), -- 收入排行
INDEX idx_start_time (start_time) -- 开播时间分析
);
-- 生产环境直播查询场景
-- 1. 直播间首页（用户进入直播间）
SELECT
room_id,
room_title,
streamer_id,
viewer_count,
room_status,
start_time
FROM live_rooms
WHERE room_number = '123456' AND room_status = 1; -- 使用 idx_room_number
-- 2. 热门直播列表（直播首页推荐）
SELECT
room_id,
room_number,
room_title,
streamer_id,
viewer_count,
category_id
FROM live_rooms
WHERE room_status = 1 -- 正在直播
ORDER BY viewer_count DESC, is_recommended DESC LIMIT 50; -- 使用 idx_status_viewer
-- 3. 分类直播列表（用户按分类浏览）
SELECT
room_id,
room_number,
room_title,
viewer_count,
gift_total_value
FROM live_rooms
WHERE category_id = 101 AND room_status = 1
ORDER BY viewer_count DESC LIMIT 30; -- 使用 idx_category_status
-- 4. 主播收入排行榜（周榜月榜）
SELECT
room_id,
room_number,
streamer_id,
gift_total_value,
viewer_count
FROM live_rooms
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) -- 本周数据
ORDER BY gift_total_value DESC LIMIT 100; -- 使用 idx_gift_value
-- 5. 推荐直播算法（个性化推荐）
SELECT
room_id,
room_number,
room_title,
viewer_count,
category_id
FROM live_rooms
WHERE is_recommended = 1
AND room_status = 1
AND viewer_count >= 100 -- 有一定人气的直播间
ORDER BY viewer_count DESC, RAND() LIMIT 20; -- 使用 idx_recommended_viewer
直播礼物记录表
-- 直播礼物记录表（十亿级数据）
CREATE TABLE live_gifts (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，时间序列
gift_id VARCHAR(32) UNIQUE, -- 礼物记录ID
room_id BIGINT, -- 直播间ID
streamer_id BIGINT, -- 主播ID
user_id BIGINT, -- 送礼用户ID
gift_type_id INT, -- 礼物类型ID
gift_name VARCHAR(50), -- 礼物名称
gift_count INT, -- 礼物数量
unit_price DECIMAL(8,2), -- 单价（人民币）
total_value DECIMAL(10,2), -- 总价值
gift_level TINYINT, -- 礼物等级：1普通 2精品 3豪华 4传说
is_combo TINYINT DEFAULT 0, -- 是否连击：0否 1是
combo_count INT DEFAULT 1, -- 连击次数
platform_commission DECIMAL(8,2), -- 平台抽成
streamer_income DECIMAL(8,2), -- 主播收入
gift_time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), -- 送礼时间
-- 礼物分析索引
INDEX idx_gift_id (gift_id), -- 礼物记录查询
INDEX idx_room_time (room_id, gift_time), -- 直播间礼物统计
INDEX idx_streamer_time (streamer_id, gift_time), -- 主播收入统计
INDEX idx_user_time (user_id, gift_time), -- 用户消费记录
INDEX idx_type_time (gift_type_id, gift_time), -- 礼物类型分析
INDEX idx_value_time (total_value, gift_time), -- 大额礼物监控
INDEX idx_level_time (gift_level, gift_time) -- 礼物等级统计
);
-- 直播礼物查询场景
-- 1. 直播间实时礼物流水（直播间礼物动画）
SELECT
user_id,
gift_name,
gift_count,
total_value,
is_combo,
gift_time
FROM live_gifts
WHERE room_id = 123456
AND gift_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
ORDER BY gift_time DESC LIMIT 100; -- 使用 idx_room_time
-- 2. 主播收入统计（主播后台）
SELECT
DATE(gift_time) as gift_date,
COUNT(*) as gift_count,
SUM(total_value) as total_revenue,
SUM(streamer_income) as streamer_revenue,
AVG(total_value) as avg_gift_value
FROM live_gifts
WHERE streamer_id = 789012
AND gift_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(gift_time)
ORDER BY gift_date DESC; -- 使用 idx_streamer_time
-- 3. 用户消费记录（用户个人中心）
SELECT
room_id,
streamer_id,
gift_name,
gift_count,
total_value,
gift_time
FROM live_gifts
WHERE user_id = 456789
ORDER BY gift_time DESC LIMIT 50; -- 使用 idx_user_time
-- 4. 土豪用户识别（运营分析）
SELECT
user_id,
COUNT(*) as gift_times,
SUM(total_value) as total_spent,
AVG(total_value) as avg_gift_value,
MAX(total_value) as max_single_gift
FROM live_gifts
WHERE gift_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY user_id
HAVING total_spent >= 1000.00 -- 一周消费超过1000元
ORDER BY total_spent DESC LIMIT 100; -- 使用 idx_user_time
-- 5. 平台礼物收入分析（财务分析）
SELECT
gift_type_id,
gift_name,
SUM(gift_count) as total_sent,
SUM(total_value) as total_revenue,
SUM(platform_commission) as platform_income
FROM live_gifts
WHERE gift_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY gift_type_id, gift_name
ORDER BY total_revenue DESC; -- 使用 idx_type_time
9. 政府公共服务系统
政务服务办件表
-- 政务服务办件表（千万级办件数据）
CREATE TABLE government_applications (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，按时间自增
application_no VARCHAR(30) UNIQUE, -- 办件编号，对外展示
service_code VARCHAR(20), -- 服务事项编码
service_name VARCHAR(200), -- 服务事项名称
applicant_id_card VARCHAR(18), -- 申请人身份证号
applicant_name VARCHAR(50), -- 申请人姓名
applicant_phone VARCHAR(20), -- 申请人手机号
enterprise_code VARCHAR(20), -- 企业统一社会信用代码（企业办件）
department_code VARCHAR(10), -- 受理部门编码
window_code VARCHAR(10), -- 受理窗口编码
staff_id VARCHAR(20), -- 受理工作人员ID
application_type TINYINT, -- 申请类型：1个人 2企业
urgency_level TINYINT DEFAULT 2, -- 紧急程度：1加急 2普通 3延期
current_status TINYINT, -- 当前状态：1已受理 2审核中 3补正 4办结 5终止
legal_time_limit INT, -- 法定办结时限（工作日）
promised_time_limit INT, -- 承诺办结时限（工作日）
actual_time_limit INT, -- 实际办结时限（工作日）
is_overdue TINYINT DEFAULT 0, -- 是否超期：0未超期 1超期
application_date DATE, -- 申请日期
accept_date DATE, -- 受理日期
finish_date DATE, -- 办结日期
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 政务查询索引
INDEX idx_application_no (application_no), -- 办件编号查询（用户查询进度）
INDEX idx_applicant_id (applicant_id_card), -- 身份证号查询（个人办件）
INDEX idx_enterprise_code (enterprise_code), -- 企业代码查询（企业办件）
INDEX idx_department_status (department_code, current_status), -- 部门办件管理
INDEX idx_service_date (service_code, application_date), -- 服务事项统计
INDEX idx_staff_status (staff_id, current_status), -- 工作人员工作量
INDEX idx_status_overdue (current_status, is_overdue), -- 超期办件监控
INDEX idx_urgency_date (urgency_level, application_date) -- 加急办件处理
);
-- 生产环境政务查询场景
-- 1. 用户查询办件进度（政务APP/网站最常用功能）
SELECT
application_no,
service_name,
current_status,
promised_time_limit,
accept_date,
finish_date,
CASE current_status
WHEN 1 THEN '已受理'
WHEN 2 THEN '审核中'
WHEN 3 THEN '需补正材料'
WHEN 4 THEN '已办结'
WHEN 5 THEN '已终止'
END as status_name
FROM government_applications
WHERE applicant_id_card = '110101199001011234' -- 用户身份证号
AND application_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY) -- 一年内的办件
ORDER BY created_at DESC; -- 使用 idx_applicant_id
-- 2. 部门办件管理（政务工作人员工作台）
SELECT
current_status,
COUNT(*) as count,
COUNT(CASE WHEN is_overdue = 1 THEN 1 END) as overdue_count,
AVG(actual_time_limit) as avg_time_limit
FROM government_applications
WHERE department_code = 'DEPT001'
AND application_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY current_status; -- 使用 idx_department_status
-- 3. 超期办件预警（每日定时检查）
SELECT
application_no,
service_name,
applicant_name,
staff_id,
promised_time_limit,
DATEDIFF(CURDATE(), accept_date) as elapsed_days
FROM government_applications
WHERE current_status IN (1, 2, 3) -- 未办结
AND is_overdue = 0 -- 尚未标记超期
AND DATEDIFF(CURDATE(), accept_date) >= promised_time_limit
ORDER BY elapsed_days DESC; -- 使用 idx_status_overdue
网约车出行平台
-- 网约车订单表（日订单量千万级）
CREATE TABLE ride_orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引，订单时间序列
order_no VARCHAR(32) UNIQUE, -- 订单号，对外展示
passenger_id BIGINT, -- 乘客ID
driver_id BIGINT, -- 司机ID（接单后分配）
vehicle_id BIGINT, -- 车辆ID
service_type TINYINT, -- 服务类型：1快车 2专车 3豪华车 4拼车
order_status TINYINT, -- 订单状态：1待接单 2已接单 3服务中 4已完成 5已取消
start_longitude DECIMAL(10,6), -- 起点经度
start_latitude DECIMAL(10,6), -- 起点纬度
start_address VARCHAR(500), -- 起点地址
end_longitude DECIMAL(10,6), -- 终点经度
end_latitude DECIMAL(10,6), -- 终点纬度
end_address VARCHAR(500), -- 终点地址
city_code VARCHAR(10), -- 城市编码
estimated_distance DECIMAL(8,2), -- 预估距离（公里）
actual_distance DECIMAL(8,2), -- 实际距离（公里）
estimated_duration INT, -- 预估时长（分钟）
actual_duration INT, -- 实际时长（分钟）
estimated_amount DECIMAL(10,2), -- 预估费用
actual_amount DECIMAL(10,2), -- 实际费用
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 下单时间
accepted_at TIMESTAMP, -- 接单时间
started_at TIMESTAMP, -- 开始服务时间
completed_at TIMESTAMP, -- 完成时间
-- 核心业务索引
INDEX idx_order_no (order_no), -- 订单号查询
INDEX idx_passenger_status (passenger_id, order_status), -- 乘客订单历史
INDEX idx_driver_status (driver_id, order_status), -- 司机订单管理
INDEX idx_city_status_time (city_code, order_status, created_at), -- 城市运营分析
INDEX idx_status_created (order_status, created_at), -- 订单状态分析
INDEX idx_service_type_time (service_type, created_at), -- 服务类型统计
INDEX idx_location_time (city_code, start_longitude, start_latitude, created_at) -- 热点区域分析
);
-- 生产环境网约车查询场景
-- 1. 乘客查询历史订单（乘客APP）
SELECT
order_no,
service_type,
start_address,
end_address,
actual_amount,
order_status,
created_at,
completed_at
FROM ride_orders
WHERE passenger_id = 123456
AND order_status IN (4, 5) -- 已完成或已取消
ORDER BY created_at DESC LIMIT 20; -- 使用 idx_passenger_status
-- 2. 城市运营实时监控（运营中心大屏）
SELECT
city_code,
service_type,
COUNT(*) as total_orders,
COUNT(CASE WHEN order_status = 1 THEN 1 END) as waiting_orders,
COUNT(CASE WHEN order_status = 4 THEN 1 END) as completed_orders,
AVG(CASE WHEN actual_amount > 0 THEN actual_amount END) as avg_amount,
AVG(CASE WHEN actual_duration > 0 THEN actual_duration END) as avg_duration
FROM ride_orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR) -- 最近1小时
GROUP BY city_code, service_type; -- 使用 idx_city_status_time
-- 3. 热点区域分析（智能调度算法）
SELECT
ROUND(start_longitude, 3) as lng_area,
ROUND(start_latitude, 3) as lat_area,
COUNT(*) as order_count,
AVG(estimated_amount) as avg_amount
FROM ride_orders
WHERE city_code = 'BJ'
AND created_at >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR) -- 前一小时的数据
GROUP BY ROUND(start_longitude, 3), ROUND(start_latitude, 3)
HAVING order_count >= 5 -- 订单密度较高的区域
ORDER BY order_count DESC; -- 使用 idx_location_time
10. 生产环境性能优化实战
电商订单表优化案例
-- 原始设计（性能问题）
CREATE TABLE orders_old (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32), -- 没有索引，订单号查询慢
user_id BIGINT, -- 没有复合索引，用户订单列表查询慢
status TINYINT,
created_at TIMESTAMP,
INDEX idx_user_id (user_id) -- 单列索引，不够优化
);
-- 优化后设计
CREATE TABLE orders_optimized (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引保持不变
order_no VARCHAR(32),
user_id BIGINT,
status TINYINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 优化索引设计
UNIQUE KEY uk_order_no (order_no), -- 订单号唯一索引
INDEX idx_user_status_time (user_id, status, created_at), -- 复合索引优化用户订单查询
INDEX idx_status_time (status, created_at), -- 管理后台按状态查询
INDEX idx_created_time (created_at) -- 时间范围统计
);
-- 性能对比
-- 查询用户订单列表（优化前后对比）
-- 优化前：需要回表，性能差
SELECT * FROM orders_old WHERE user_id = 123456 ORDER BY created_at DESC LIMIT 20;
-- 优化后：使用覆盖索引，性能大幅提升
SELECT id, order_no, status, created_at
FROM orders_optimized
WHERE user_id = 123456 AND status IN (1,2,3,4)
ORDER BY created_at DESC LIMIT 20;
-- 执行计划：Using index（覆盖索引，无需回表）
分表分库策略
-- 大表拆分策略（日订单量百万级）
-- 按时间分表
CREATE TABLE orders_202412 (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE,
user_id BIGINT,
status TINYINT,
created_at TIMESTAMP,
INDEX idx_user_status (user_id, status),
INDEX idx_status_time (status, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-02')),
PARTITION p20241202 VALUES LESS THAN (TO_DAYS('2024-12-03')),
-- ... 按日分区
);
-- 按用户ID分库分表（水平拆分）
-- 订单表_0, 订单表_1, 订单表_2, 订单表_3 (根据 user_id % 4)
CREATE TABLE orders_0 (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE,
user_id BIGINT, -- user_id % 4 = 0 的数据
status TINYINT,
created_at TIMESTAMP,
INDEX idx_user_status_time (user_id, status, created_at)
);
-- 应用层路由逻辑
-- 写入：根据 user_id % 4 决定写入哪个分表
-- 查询：根据 user_id % 4 决定查询哪个分表
实际生产问题解决案例
案例1：电商订单查询优化（某电商平台实战）
-- 【问题背景】
-- 电商平台用户订单查询页面响应时间超过5秒，用户体验极差
-- 订单表：orders，数据量2亿条，每日新增100万订单
-- 主要查询：用户查看自己的订单列表
-- 【原始设计】（存在严重性能问题）
CREATE TABLE orders_before (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
order_no VARCHAR(32), -- 订单号（无索引！）
user_id BIGINT, -- 用户ID
status TINYINT, -- 订单状态
total_amount DECIMAL(15,2), -- 订单金额
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id) -- 单列索引，不够优化
);
-- 【问题SQL】（执行时间：3-8秒）
SELECT * FROM orders_before
WHERE user_id = 123456
ORDER BY created_at DESC LIMIT 20;
-- 执行计划：Using filesort, 需要排序大量数据
-- 【优化后设计】
CREATE TABLE orders_after (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引保持不变
order_no VARCHAR(32),
user_id BIGINT,
status TINYINT,
total_amount DECIMAL(15,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 关键优化：复合索引
UNIQUE KEY uk_order_no (order_no), -- 订单号唯一索引
INDEX idx_user_time (user_id, created_at), -- 用户+时间复合索引
INDEX idx_user_status_time (user_id, status, created_at) -- 覆盖更多查询场景
);
-- 【优化后SQL】（执行时间：50ms）
SELECT * FROM orders_after
WHERE user_id = 123456
ORDER BY created_at DESC LIMIT 20;
-- 执行计划：Using index for order-by, 直接利用索引顺序
-- 【优化效果】
-- 响应时间：从5秒降低到50ms，提升100倍
-- CPU使用率：从80%降低到5%
-- 用户满意度：显著提升，投诉减少90%
案例2：社交应用朋友圈优化（某社交APP实战）
-- 【问题背景】
-- 朋友圈时间线加载缓慢，用户刷新朋友圈需要等待10秒以上
-- 用户动态表：user_posts，数据量10亿条
-- 主要查询：获取用户关注的人的最新动态
-- 【原始设计】（查询超时）
CREATE TABLE user_posts_before (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT, -- 发布者ID
content TEXT, -- 动态内容
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id), -- 单列索引
INDEX idx_created_at (created_at) -- 时间索引
);
-- 【问题SQL】（执行时间：超时）
-- 查询关注用户的动态（朋友圈feed流）
SELECT p.* FROM user_posts_before p
WHERE p.user_id IN (
SELECT following_id FROM user_follows
WHERE follower_id = 123456 AND status = 1
)
ORDER BY p.created_at DESC LIMIT 50;
-- 问题：IN查询 + 大表排序，性能极差
-- 【优化方案1：索引优化】
CREATE TABLE user_posts_v1 (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_deleted TINYINT DEFAULT 0, -- 软删除标记
-- 优化索引设计
INDEX idx_user_time (user_id, created_at), -- 复合索引，支持按用户时间排序
INDEX idx_time_user (created_at, user_id), -- 反向索引，支持全局时间排序
INDEX idx_user_status (user_id, is_deleted) -- 过滤删除的动态
);
-- 优化后SQL v1（执行时间：2秒）
SELECT p.* FROM user_posts_v1 p
INNER JOIN user_follows f ON p.user_id = f.following_id
WHERE f.follower_id = 123456 AND f.status = 1 AND p.is_deleted = 0
ORDER BY p.created_at DESC LIMIT 50;
-- 使用JOIN替代IN查询，性能有所改善
-- 【优化方案2：推送模式（最终方案）】
-- 创建用户时间线表，预先计算好每个用户的朋友圈内容
CREATE TABLE user_timeline (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
user_id BIGINT, -- 接收动态的用户ID
post_id BIGINT, -- 动态ID
author_id BIGINT, -- 动态作者ID
post_time TIMESTAMP, -- 动态发布时间
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 推送时间
-- 专门为时间线查询优化的索引
INDEX idx_user_posttime (user_id, post_time), -- 用户时间线主索引
INDEX idx_user_created (user_id, created_at) -- 备用排序索引
);
-- 【终极优化SQL】（执行时间：20ms）
SELECT t.post_id, t.author_id, t.post_time
FROM user_timeline t
WHERE t.user_id = 123456
ORDER BY t.post_time DESC LIMIT 50;
-- 直接查询预计算的时间线，性能极佳
-- 【优化效果】
-- 响应时间：从10秒以上降低到20ms，提升500倍
-- 架构改进：从拉取模式改为推送模式
-- 存储成本：增加30%，但用户体验大幅提升
-- 系统稳定性：显著提升，高峰期不再卡顿
案例3：金融风控系统优化（某支付公司实战）
-- 【问题背景】
-- 实时风控系统响应慢，大额交易预警延迟严重
-- 交易表：payment_records，数据量50亿条，QPS峰值10万
-- 关键需求：100ms内完成风控检查
-- 【原始设计】（风控查询超时）
CREATE TABLE payment_records_before (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT, -- 用户ID
amount DECIMAL(15,2), -- 交易金额
payment_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
merchant_id BIGINT, -- 商户ID
status TINYINT, -- 支付状态
INDEX idx_user_id (user_id), -- 基础索引
INDEX idx_amount (amount), -- 金额索引
INDEX idx_time (payment_time) -- 时间索引
);
-- 【问题SQL】（执行时间：3-10秒）
-- 风控检查：用户最近1小时大额交易次数
SELECT COUNT(*) FROM payment_records_before
WHERE user_id = 123456
AND amount >= 10000.00
AND payment_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND status = 2; -- 成功交易
-- 问题：需要扫描大量数据，索引效率低
-- 【优化方案：针对风控查询设计索引】
CREATE TABLE payment_records_optimized (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(15,2),
payment_time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), -- 毫秒精度
merchant_id BIGINT,
status TINYINT,
risk_level TINYINT DEFAULT 0, -- 风险等级：0正常 1可疑 2高危
-- 针对风控优化的复合索引
INDEX idx_user_status_time (user_id, status, payment_time), -- 用户交易时间线
INDEX idx_user_amount_time (user_id, amount, payment_time), -- 用户金额时间索引
INDEX idx_amount_time_status (amount, payment_time, status), -- 大额交易监控
INDEX idx_risk_time (risk_level, payment_time), -- 风险交易分析
INDEX idx_merchant_amount (merchant_id, amount, payment_time) -- 商户大额交易
);
-- 【优化后SQL】（执行时间：5ms）
-- 风控检查优化版本
SELECT COUNT(*) FROM payment_records_optimized
WHERE user_id = 123456
AND status = 2
AND payment_time >= DATE_SUB(NOW(3), INTERVAL 1 HOUR)
AND amount >= 10000.00;
-- 使用 idx_user_status_time + 覆盖查询，性能大幅提升
-- 【进一步优化：实时风控缓存表】
CREATE TABLE user_risk_profile (
user_id BIGINT PRIMARY KEY, -- 聚集索引
last_hour_count INT DEFAULT 0, -- 最近1小时交易次数
last_hour_amount DECIMAL(15,2) DEFAULT 0, -- 最近1小时交易总额
last_day_count INT DEFAULT 0, -- 最近24小时交易次数
last_day_amount DECIMAL(15,2) DEFAULT 0, -- 最近24小时交易总额
risk_score INT DEFAULT 0, -- 风险评分
last_update TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), -- 最后更新时间
INDEX idx_risk_score (risk_score), -- 风险评分索引
INDEX idx_last_update (last_update) -- 更新时间索引
);
-- 【实时风控查询】（执行时间：<1ms）
SELECT risk_score, last_hour_count, last_hour_amount
FROM user_risk_profile
WHERE user_id = 123456;
-- 直接查询预计算的风控数据，响应极快
-- 【优化效果】
-- 响应时间：从3-10秒降低到1ms，提升3000-10000倍
-- 风控准确性：实时更新，误报率降低60%
-- 系统吞吐量：支持QPS从1000提升到10万
-- 业务影响：大幅减少用户交易被误拦截的情况
案例4：视频网站播放记录优化（某视频平台实战）
-- 【问题背景】
-- 用户观看历史查询慢，推荐算法计算超时
-- 播放记录表：video_plays，数据量100亿条，日增量5000万
-- 核心需求：快速获取用户观看偏好，支持推荐算法
-- 【原始设计】（查询缓慢）
CREATE TABLE video_plays_before (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT, -- 用户ID
video_id BIGINT, -- 视频ID
play_duration INT, -- 播放时长（秒）
video_duration INT, -- 视频总时长
completion_rate DECIMAL(5,2), -- 完播率
play_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
device_type TINYINT, -- 设备类型
INDEX idx_user_id (user_id), -- 单列索引
INDEX idx_video_id (video_id), -- 单列索引
INDEX idx_play_time (play_time) -- 时间索引
);
-- 【问题SQL】（执行时间：30秒+）
-- 推荐算法：获取用户最近观看的视频类型偏好
SELECT v.category_id, COUNT(*) as watch_count, AVG(p.completion_rate) as avg_completion
FROM video_plays_before p
JOIN videos v ON p.video_id = v.video_id
WHERE p.user_id = 123456
AND p.play_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND p.completion_rate >= 0.6 -- 有效观看
GROUP BY v.category_id
ORDER BY watch_count DESC;
-- 问题：大表JOIN + 复杂聚合，性能极差
-- 【优化方案：数据预聚合 + 专用索引】
-- 用户观看偏好汇总表
CREATE TABLE user_watch_preferences (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT, -- 用户ID
category_id INT, -- 视频分类ID
watch_count INT DEFAULT 0, -- 观看次数
total_duration INT DEFAULT 0, -- 总观看时长
avg_completion_rate DECIMAL(5,2) DEFAULT 0, -- 平均完播率
last_watch_time TIMESTAMP, -- 最后观看时间
preference_score DECIMAL(8,4) DEFAULT 0, -- 偏好评分
stats_date DATE, -- 统计日期
-- 推荐算法专用索引
UNIQUE KEY uk_user_category_date (user_id, category_id, stats_date),
INDEX idx_user_score (user_id, preference_score), -- 用户偏好排序
INDEX idx_user_lastwatch (user_id, last_watch_time), -- 最近观看
INDEX idx_category_score (category_id, preference_score) -- 分类热度
);
-- 优化后的播放记录表
CREATE TABLE video_plays_optimized (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
video_id BIGINT,
category_id INT, -- 冗余字段，避免JOIN
play_duration INT,
video_duration INT,
completion_rate DECIMAL(5,2),
play_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
device_type TINYINT,
-- 针对不同查询场景的索引
INDEX idx_user_time (user_id, play_time), -- 用户观看历史
INDEX idx_user_category_time (user_id, category_id, play_time), -- 用户分类偏好
INDEX idx_video_time (video_id, play_time), -- 视频播放统计
INDEX idx_completion_user (completion_rate, user_id), -- 完播率分析
INDEX idx_category_time (category_id, play_time) -- 分类热度统计
);
-- 【优化后SQL】（执行时间：10ms）
-- 推荐算法优化版本
SELECT category_id, preference_score, watch_count, avg_completion_rate
FROM user_watch_preferences
WHERE user_id = 123456
AND stats_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY preference_score DESC LIMIT 10;
-- 直接查询预聚合数据，性能极佳
-- 【实时观看行为分析】（执行时间：50ms）
SELECT
category_id,
COUNT(*) as recent_watches,
AVG(completion_rate) as avg_completion
FROM video_plays_optimized
WHERE user_id = 123456
AND play_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY category_id;
-- 使用优化索引，快速获取实时数据
-- 【优化效果】
-- 推荐算法响应时间：从30秒降低到10ms，提升3000倍
-- 用户观看历史加载：从5秒降低到100ms，提升50倍
-- 推荐准确率：提升25%，用户停留时间增加40%
-- 服务器资源：CPU使用率降低70%，支持更多并发用户
案例5：电商库存系统优化（某大型电商实战）
-- 【问题背景】
-- 商品库存查询和更新性能问题，高并发下出现死锁
-- 库存表：product_inventory，数据量5000万条
-- 关键需求：高并发库存扣减，实时库存查询
-- 【原始设计】（高并发死锁）
CREATE TABLE product_inventory_before (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT, -- 商品ID
warehouse_id INT, -- 仓库ID
available_stock INT, -- 可用库存
reserved_stock INT DEFAULT 0, -- 预占库存
total_stock INT, -- 总库存
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_product_warehouse (product_id, warehouse_id),
INDEX idx_product_id (product_id) -- 基础索引
);
-- 【问题SQL】（高并发死锁）
-- 库存扣减操作
UPDATE product_inventory_before
SET available_stock = available_stock - 1,
reserved_stock = reserved_stock + 1
WHERE product_id = 12345 AND warehouse_id = 1 AND available_stock >= 1;
-- 问题：表级锁竞争，高并发下频繁死锁
-- 【优化方案1：分库分表 + 索引优化】
-- 按商品ID哈希分表，减少锁竞争
CREATE TABLE product_inventory_0 ( -- product_id % 10 = 0
id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT,
warehouse_id INT,
available_stock INT,
reserved_stock INT DEFAULT 0,
total_stock INT,
version INT DEFAULT 0, -- 乐观锁版本号
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 优化索引设计
UNIQUE KEY uk_product_warehouse (product_id, warehouse_id),
INDEX idx_warehouse_product (warehouse_id, product_id), -- 仓库维度查询
INDEX idx_stock_product (available_stock, product_id) -- 库存预警
);
-- 【优化方案2：乐观锁 + 批量操作】
-- 使用乐观锁避免死锁
UPDATE product_inventory_0
SET available_stock = available_stock - 1,
reserved_stock = reserved_stock + 1,
version = version + 1
WHERE product_id = 12345
AND warehouse_id = 1
AND available_stock >= 1
AND version = @old_version; -- 乐观锁检查
-- 【优化方案3：库存缓存表】
-- 高频查询商品的库存缓存
CREATE TABLE hot_product_stock (
product_id BIGINT PRIMARY KEY, -- 聚集索引
total_available INT DEFAULT 0, -- 全部仓库可用总库存
update_frequency INT DEFAULT 0, -- 更新频率统计
last_update TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
INDEX idx_available (total_available), -- 库存查询索引
INDEX idx_frequency (update_frequency) -- 热度排序
);
-- 【优化后查询】（执行时间：1ms）
-- 快速库存检查
SELECT total_available FROM hot_product_stock WHERE product_id = 12345;
-- 【库存分布查询】（执行时间：5ms）
-- 查看商品在各仓库的库存分布
SELECT warehouse_id, available_stock, reserved_stock
FROM product_inventory_0 -- 根据product_id路由到正确分表
WHERE product_id = 12345
ORDER BY available_stock DESC;
-- 【优化效果】
-- 并发性能：支持QPS从1000提升到50000
-- 死锁率：从每小时100次降低到每天不到5次
-- 查询响应时间：从100ms降低到1-5ms
-- 库存准确率：99.99%，误差大幅降低
-- 系统稳定性：高峰期不再出现库存系统崩溃
最佳实践建议
1. 聚集索引设计原则
选择合适的聚集索引键
-- ✅ 好的选择：自增主键
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 单调递增，避免页分裂
user_id INT,
product_id INT,
order_time TIMESTAMP
);
-- ❌ 避免：UUID作为聚集索引
CREATE TABLE bad_orders (
uuid CHAR(36) PRIMARY KEY, -- 随机值，会导致频繁页分裂
user_id INT,
product_id INT
);
-- ✅ 如果必须使用UUID，考虑有序UUID
-- 或者使用自增ID + UUID的组合方案
CREATE TABLE better_orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
uuid CHAR(36) UNIQUE,
user_id INT,
product_id INT
);
避免更新聚集索引键
-- ❌ 避免更新主键
UPDATE users SET id = 999 WHERE id = 1;
-- ✅ 使用业务无关的自增ID
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 永不更新
user_uuid CHAR(36) UNIQUE, -- 业务相关的唯一标识
username VARCHAR(50),
email VARCHAR(100)
);
2. 非聚集索引优化策略
创建复合索引
-- 根据查询模式创建复合索引
-- 遵循"等值查询在前，范围查询在后"的原则
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);
-- 支持的查询模式
SELECT * FROM orders WHERE user_id = 1; -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 2; -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 2 AND created_at > '2024-01-01'; -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01'; -- 部分使用索引
-- 不支持的查询模式
SELECT * FROM orders WHERE status = 2; -- 不使用索引
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- 不使用索引
利用覆盖索引
-- 设计覆盖索引减少回表
CREATE INDEX idx_user_summary ON orders(user_id, status, order_amount, created_at);
-- 这个查询可以完全由索引覆盖
SELECT user_id, COUNT(*) as order_count, SUM(order_amount) as total_amount
FROM orders
WHERE user_id = 1 AND status IN (1, 2)
GROUP BY user_id;
索引维护策略
-- 定期分析索引使用情况
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write,
count_fetch,
count_insert,
count_update,
count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read DESC;
-- 删除未使用的索引
-- ALTER TABLE table_name DROP INDEX unused_index_name;
3. 性能监控与调优
监控索引效率
-- 检查慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- 分析执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE email = 'test@example.com';
-- 监控索引统计信息
SHOW INDEX FROM users;
ANALYZE TABLE users;
索引碎片整理
-- 检查表碎片
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
ROUND((data_free / 1024 / 1024), 2) AS 'Free Space (MB)',
ROUND((data_free / (data_length + index_length) * 100), 2) AS 'Fragmentation %'
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND data_free > 0;
-- 重建表减少碎片
OPTIMIZE TABLE table_name;
-- 或者
ALTER TABLE table_name ENGINE=InnoDB;
4. 常见问题与解决方案
问题1：频繁的页分裂
-- 监控页分裂
SHOW GLOBAL STATUS LIKE 'innodb_page_splits';
-- 解决方案：使用自增主键
ALTER TABLE problematic_table
ADD COLUMN new_id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST;
问题2：回表操作过多
-- 问题查询
SELECT * FROM users WHERE age = 25; -- 需要回表
-- 解决方案1：覆盖索引
CREATE INDEX idx_age_cover ON users(age, name, email);
SELECT name, email FROM users WHERE age = 25; -- 无需回表
-- 解决方案2：包含列索引（MySQL 8.0+）
CREATE INDEX idx_age_include ON users(age) INVISIBLE; -- 隐形索引测试
问题3：索引选择性差
-- 检查索引选择性
SELECT
COUNT(DISTINCT column_name) / COUNT(*) AS selectivity,
COUNT(DISTINCT column_name) AS unique_values,
COUNT(*) AS total_rows
FROM table_name;
-- 选择性低于0.1的索引考虑删除或重新设计
总结
核心要点回顾
- 聚集索引：数据即索引，索引即数据，查询效率高但每表只能有一个
- 非聚集索引：索引与数据分离，灵活性高但可能需要回表操作
- 选择原则：根据查询模式和性能需求选择合适的索引策略
- 优化策略：合理设计复合索引，利用覆盖索引减少回表
设计建议
- 主键设计：优先使用自增整型主键作为聚集索引
- 查询优化：基于实际查询模式设计非聚集索引
- 性能监控：定期分析索引使用情况，及时优化
- 维护策略：建立索引生命周期管理机制
通过深入理解聚集索引和非聚集索引的原理与应用，可以更好地设计数据库结构，优化查询性能，为应用系统提供高效稳定的数据访问支撑。