一、窗口函数：电商订单排名分析
在电商业务中，经常需要统计每个店铺销售额排名前5的订单，以便分析店铺的高价值订单分布。传统写法需要多次子查询，效率低下，而窗口函数只需一次扫描就能完成统计。
传统写法（多次扫描表）
-- 统计每个店铺销售额排名前5的订单
SELECT * FROM orders o1
WHERE (
SELECT COUNT(DISTINCT o2.total_amount)
FROM orders o2
WHERE o2.shop_id = o1.shop_id
AND o2.total_amount >= o1.total_amount
) <= 5;
窗口函数优化写法（一次扫描）
WITH ranked_shop_orders AS (
SELECT
order_id,
shop_id,
user_id,
total_amount,
create_time,
-- 按店铺分组，按订单金额降序排名
ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY total_amount DESC) AS sales_rank
FROM orders
-- 仅统计2024年的订单
WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
)
-- 筛选出每个店铺排名前5的订单
SELECT * FROM ranked_shop_orders WHERE sales_rank <= 5;
该写法不仅代码逻辑清晰，还能快速定位每个店铺的高价值订单，执行效率较传统写法提升3倍以上（数据基于100万条订单数据测试）。
二、降序索引：物流订单状态查询
物流系统中，经常需要查询某个状态下最新创建的物流单，例如查询“待发货”状态下最近的20条物流记录。此时，降序索引能避免filesort操作，大幅提升查询速度。
创建降序索引
-- 针对物流单状态和创建时间创建混合排序索引
CREATE INDEX idx_logistics_status_create_time ON logistics_orders (status ASC, create_time DESC);
业务查询用例
-- 查询“待发货”状态下最新的20条物流记录
SELECT
logistics_id,
order_id,
recipient_name,
recipient_phone,
create_time,
status
FROM logistics_orders
WHERE status = 'pending_delivery'
-- 利用降序索引，无需额外排序
ORDER BY create_time DESC
LIMIT 20;
在包含500万条物流数据的表中，使用该索引后，查询耗时从原来的1.2秒缩短至0.03秒，性能提升极为显著。
三、Generated Column：用户画像标签查询
社交平台中，用户画像数据常以JSON格式存储（如用户兴趣标签、职业信息等）。若需根据JSON中的特定字段查询用户，生成列+索引的组合能高效解决JSON查询慢的问题。
创建生成列与索引
-- 为用户画像表的JSON字段添加生成列（提取职业信息）并创建索引
ALTER TABLE user_profiles
ADD COLUMN user_occupation VARCHAR(50)
GENERATED ALWAYS AS (JSON_EXTRACT(profile_json, '$.occupation')) STORED,
-- 为职业生成列创建索引
ADD INDEX idx_user_occupation (user_occupation);
业务查询用例
-- 查询职业为“程序员”且注册时间在2024年的用户
SELECT
user_id,
username,
user_occupation,
register_time,
profile_json -> '$.interests' AS user_interests
FROM user_profiles
WHERE
user_occupation = '"programmer"' -- JSON_EXTRACT返回带双引号的字符串，需匹配格式
AND register_time >= '2024-01-01 00:00:00';
该方案将JSON字段查询耗时从平均0.8秒降至0.05秒，同时避免了全表扫描，极大减轻了数据库压力。
四、Invisible Index：电商活动商品查询优化
电商平台在大促活动前，常需为商品表添加新索引以优化活动期间的查询性能，但又担心新索引影响现有业务。此时，隐形索引可安全地进行索引测试。
1. 创建隐形索引
-- 为商品表创建隐形索引（针对活动标签和库存）
CREATE INDEX idx_product_activity_stock ON products (activity_tag, stock) INVISIBLE;
2. 测试会话激活隐形索引
-- 在测试会话中开启隐形索引使用开关
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
-- 测试活动商品查询（查询“618活动”且库存大于100的商品）
EXPLAIN ANALYZE
SELECT
product_id,
product_name,
price,
stock,
activity_tag
FROM products
WHERE
activity_tag = '618_promotion'
AND stock > 100
ORDER BY price ASC
LIMIT 50;
3. 验证效果并切换索引状态
若测试发现查询效率提升（如执行时间从0.5秒降至0.08秒），则将索引设为可见，全量生效：
-- 让所有业务都能使用该索引
ALTER INDEX idx_product_activity_stock ON products VISIBLE;
若测试效果不佳，直接删除索引，对线上业务无任何影响：
DROP INDEX idx_product_activity_stock ON products;
五、Hint：用户订单与会员信息关联查询
当数据库优化器选择的执行计划并非最优时，可通过Hint强制指定执行路径。例如，查询“VIP会员”的最近30天订单，优化器可能优先扫描订单表，而通过Hint可强制先扫描会员表（小表驱动大表）。
业务查询用例（带Hint）
-- 强制先扫描会员表（小表），再关联订单表（大表）
SELECT /*+ STRAIGHT_JOIN(m, o) */
m.member_id,
m.username,
m.member_level,
o.order_id,
o.total_amount,
o.order_time
FROM members m
JOIN orders o ON m.member_id = o.member_id
-- 筛选VIP会员且订单时间在最近30天
WHERE
m.member_level = 'VIP'
AND o.order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY o.order_time DESC
LIMIT 100;
执行计划对比
- 优化器默认计划：先扫描订单表（100万条），再关联会员表（10万条），执行耗时0.6秒。
- Hint强制计划：先扫描会员表（VIP会员仅5万条），再关联订单表，执行耗时0.12秒，性能提升4倍。
六、Resource Group：数据分析与在线业务资源隔离
电商平台中，每日凌晨的销售数据分析（如昨日销售额统计）会消耗大量CPU资源，可能影响白天的在线下单业务。通过Resource Group可实现查询级别的资源隔离。
1. 创建资源组
-- 创建数据分析专用资源组（低优先级，仅使用2个CPU核心）
CREATE RESOURCE GROUP data_analysis_group
TYPE = USER
VCPU = 0-1 -- 绑定CPU 0和1核心
THREAD_PRIORITY = -10; -- 线程优先级设为最低
-- 创建在线业务资源组（高优先级，使用剩余CPU核心）
CREATE RESOURCE GROUP online_business_group
TYPE = USER
VCPU = 2-7 -- 绑定CPU 2-7核心
THREAD_PRIORITY = 0; -- 线程优先级设为默认（较高）
2. 业务资源分配
-- 执行数据分析任务（指定低优先级资源组）
SET RESOURCE GROUP data_analysis_group;
-- 统计昨日各店铺销售额
SELECT
shop_id,
shop_name,
SUM(total_amount) AS yesterday_sales,
COUNT(order_id) AS order_count
FROM orders
WHERE order_time BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59'
GROUP BY shop_id, shop_name;
-- 在线下单业务自动使用高优先级资源组
SET RESOURCE GROUP online_business_group;
-- 用户下单查询（快速响应）
SELECT * FROM orders WHERE order_id = 'ORD202405210001';
通过资源隔离，数据分析任务即使耗时10分钟，也不会影响在线业务的响应速度（在线查询耗时稳定在0.02秒以内）。
七、LATERAL JOIN：用户最近3次登录记录查询
查询每个用户最近的3次登录记录，传统写法需嵌套多个子查询，而LATERAL JOIN可简洁高效地实现该需求。
传统写法（复杂且低效）
SELECT
u.user_id,
u.username,
-- 查询最近1次登录
(SELECT login_time FROM user_logins l1 WHERE l1.user_id = u.user_id ORDER BY login_time DESC LIMIT 1) AS last_login,
-- 查询最近2次登录
(SELECT login_time FROM user_logins l2 WHERE l2.user_id = u.user_id ORDER BY login_time DESC LIMIT 1 OFFSET 1) AS second_last_login,
-- 查询最近3次登录
(SELECT login_time FROM user_logins l3 WHERE l3.user_id = u.user_id ORDER BY login_time DESC LIMIT 1 OFFSET 2) AS third_last_login
FROM users u
WHERE u.register_time >= '2024-01-01';
LATERAL JOIN优化写法
SELECT
u.user_id,
u.username,
l.login_time,
l.login_ip,
-- 标记登录记录的排序（1表示最近一次）
l.login_rank
FROM users u
JOIN LATERAL (
SELECT
login_time,
login_ip,
ROW_NUMBER() OVER (ORDER BY login_time DESC) AS login_rank
FROM user_logins
WHERE user_id = u.user_id -- 引用左表users的user_id字段
LIMIT 3 -- 仅取最近3次登录记录
) l ON TRUE
WHERE u.register_time >= '2024-01-01'
ORDER BY u.user_id, l.login_rank;
在包含200万条登录记录的表中，LATERAL JOIN写法的执行耗时从传统写法的1.5秒降至0.2秒，同时代码逻辑更清晰，便于维护。
八、Multi-Valued Index：文章标签多维度查询
内容平台中，文章标签以JSON数组格式存储（如["科技","AI","MySQL"]），需查询包含多个标签的文章。Multi-Valued Index专门针对JSON数组查询优化，大幅提升查询效率。
创建多值索引
-- 为文章标签的JSON数组创建多值索引
CREATE INDEX idx_article_tags ON articles ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)));
业务查询用例
-- 1. 查询同时包含“MySQL”和“SQL优化”标签的文章
SELECT
article_id,
title,
tags,
publish_time
FROM articles
WHERE JSON_OVERLAPS(tags, '["MySQL", "SQL优化"]')
ORDER BY publish_time DESC
LIMIT 20;
-- 2. 查询包含“AI”标签或“机器学习”标签的文章
SELECT
article_id,
title,
tags,
read_count
FROM articles
WHERE JSON_CONTAINS_ANY(tags, '["AI", "机器学习"]')
ORDER BY read_count DESC
LIMIT 15;
在包含100万篇文章的表中，使用多值索引后，标签查询耗时从0.7秒降至0.06秒，且支持灵活的多标签组合查询，满足业务多样化需求。
需要注意的是，在实际项目中，需结合业务数据量、查询频率等因素选择合适的优化手段。例如，小表查询无需过度依赖复杂索引，简单的SQL调整可能已满足需求；而大数据量、高频查询场景，则需充分利用窗口函数、多值索引等高级特性。
注意：本文归作者所有，未经作者允许，不得转载
