【MySQL】MySQL聚集索引与非聚集索引深度解析
目录
- 索引基础概念
- 聚集索引详解
- 非聚集索引详解
- 核心区别对比
- 性能影响分析
- 实际应用场景
- 最佳实践建议
索引基础概念
什么是索引
索引是数据库管理系统中一种重要的数据结构，它为表中的数据创建有序的访问路径，类似于书籍的目录。通过索引，数据库可以快速定位到所需的数据行，避免全表扫描，从而大幅提升查询性能。
索引的存储结构
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, -- 买家IDseller_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, -- 标准产品单元IDcategory_id INT, -- 分类IDbrand_id INT, -- 品牌IDtitle 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, -- 聚集索引，自增IDpayment_no VARCHAR(32) UNIQUE, -- 支付单号，业务主键order_no VARCHAR(32), -- 关联订单号user_id BIGINT, -- 用户IDmerchant_id BIGINT, -- 商户IDamount 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), -- 第三方交易IDcreated_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, -- 用户IDaccount_id BIGINT, -- 账户IDchange_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, -- 发布用户IDcontent 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, -- 关注者IDfollowing_id BIGINT, -- 被关注者IDstatus 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), -- 链路追踪IDapp_name VARCHAR(50), -- 应用名称log_level TINYINT, -- 日志级别：1DEBUG 2INFO 3WARN 4ERROR 5FATALlogger_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, networkmetric_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), -- 设备IDsensor_type TINYINT, -- 传感器类型：1温度 2湿度 3压力 4光照sensor_value DECIMAL(10,4), -- 传感器数值location_id INT, -- 地理位置IDfactory_id INT, -- 工厂IDworkshop_id INT, -- 车间IDis_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, -- 当前负责快递员IDestimated_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, -- 聚集索引，玩家唯一IDplayer_uuid VARCHAR(36) UNIQUE, -- 玩家UUID，跨服标识username VARCHAR(50) UNIQUE, -- 玩家用户名email VARCHAR(100), -- 邮箱phone VARCHAR(20), -- 手机号server_id INT, -- 所在服务器IDlevel INT DEFAULT 1, -- 玩家等级experience BIGINT DEFAULT 0, -- 经验值gold BIGINT DEFAULT 0, -- 游戏金币diamond INT DEFAULT 0, -- 钻石数量vip_level TINYINT DEFAULT 0, -- VIP等级guild_id BIGINT, -- 公会IDlast_login_time TIMESTAMP, -- 最后登录时间total_playtime INT DEFAULT 0, -- 总游戏时长（分钟）player_status TINYINT DEFAULT 1, -- 玩家状态：1正常 2封号 3注销register_ip VARCHAR(45), -- 注册IPregister_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, -- 玩家IDserver_id INT, -- 服务器IDproduct_id VARCHAR(20), -- 商品IDproduct_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 3PCclient_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, -- 聚集索引，直播间IDroom_number VARCHAR(20) UNIQUE, -- 直播间号码，对外展示streamer_id BIGINT, -- 主播IDroom_title VARCHAR(200), -- 直播间标题category_id INT, -- 直播分类IDroom_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, -- 礼物记录IDroom_id BIGINT, -- 直播间IDstreamer_id BIGINT, -- 主播IDuser_id BIGINT, -- 送礼用户IDgift_type_id INT, -- 礼物类型IDgift_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), -- 受理工作人员IDapplication_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_statusWHEN 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, -- 乘客IDdriver_id BIGINT, -- 司机ID（接单后分配）vehicle_id BIGINT, -- 车辆IDservice_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, -- 用户IDstatus 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, -- 发布者IDcontent 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, -- 接收动态的用户IDpost_id BIGINT, -- 动态IDauthor_id BIGINT, -- 动态作者IDpost_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, -- 用户IDamount DECIMAL(15,2), -- 交易金额payment_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,merchant_id BIGINT, -- 商户IDstatus 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, -- 用户IDvideo_id BIGINT, -- 视频IDplay_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, -- 用户IDcategory_id INT, -- 视频分类IDwatch_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, -- 冗余字段，避免JOINplay_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, -- 商品IDwarehouse_id INT, -- 仓库IDavailable_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 = 0id 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的索引考虑删除或重新设计
总结
核心要点回顾
- 聚集索引：数据即索引，索引即数据，查询效率高但每表只能有一个
- 非聚集索引：索引与数据分离，灵活性高但可能需要回表操作
- 选择原则：根据查询模式和性能需求选择合适的索引策略
- 优化策略：合理设计复合索引，利用覆盖索引减少回表
设计建议
- 主键设计：优先使用自增整型主键作为聚集索引
- 查询优化：基于实际查询模式设计非聚集索引
- 性能监控：定期分析索引使用情况，及时优化
- 维护策略：建立索引生命周期管理机制
通过深入理解聚集索引和非聚集索引的原理与应用，可以更好地设计数据库结构，优化查询性能，为应用系统提供高效稳定的数据访问支撑。