是不是又被慢查询搞崩溃了？
页面转圈圈，老板在催催，急得我们满头包，最后只好双手一摊：“再加个索引试试？”
打住！别再无脑加索引了，数据库快被我们加成刺猬了！
真正的SQL优化，是一门手艺活。从我们写的每一句SQL代码，到数据库怎么存数据、怎么搭架子，都有讲究。
下面整理了83个SQL优化场景示例，从“千万别用SELECT *”这种小细节，到“分库分表”这种大型架构，让我们一起告别野路子，掌握让数据库起飞的正确姿势！
1、避免 SELECT *
场景：我们查询用户详情时排除敏感字段
-- ❌ 返回全部字段（含不需要的敏感字段）
SELECT * FROM users WHERE id = 1001; -- 包含password等无用字段
-- ✅ 明确指定字段（提升性能与安全性）
SELECT user_id, name, email, created_at
FROM users
WHERE id = 1001; -- 仅返回必要字段典型应用：Web接口返回用户公开信息时减少数据传输量。
2、EXISTS 替代 IN 检查活跃订单
场景：我们查询活跃客户的订单
-- ❌ IN子查询可能返回NULL导致漏数据
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE status = 'active' -- 子查询效率低
);
-- ✅ EXISTS在匹配后立即终止扫描
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id
AND c.status = 'active' -- 利用索引快速定位
);典型应用：电商平台筛选有效订单时避免全表扫描。
3、延迟关联优化千万级数据分页
场景：分页展示用户行为日志
-- ❌ 深分页时OFFSET效率骤降
SELECT * FROM user_logs
ORDER BY log_time DESC
LIMIT 10 OFFSET 500000; -- 需扫描50万+10行
-- ✅ 先通过索引定位ID再取数据
SELECT log.*
FROM user_logs log
JOIN (
SELECT id FROM user_logs -- 仅扫描索引
ORDER BY log_time DESC
LIMIT 10 OFFSET 500000
) tmp ON log.id = tmp.id; -- 回表取10行数据典型应用：后台管理系统查看历史操作日志。
4、批量更新用户积分
场景：活动期间批量增加用户积分
-- ❌ 循环单条更新（网络I/O翻倍）
UPDATE users SET points = points + 10 WHERE id = 1001;
UPDATE users SET points = points + 10 WHERE id = 1002;
...
-- ✅ 单次批量操作（减少99%开销）
UPDATE users SET points = points + 10
WHERE id IN (1001, 1002, ..., 10000); -- 一次更新1万用户典型应用：运营活动批量发放奖励。
5、UNION ALL 拆分OR条件
场景：多条件混合查询日志
-- ❌ OR导致索引失效（type='error'与source='api'无复合索引）
SELECT * FROM system_log
WHERE log_type = 'error' OR source_module = 'api';
-- ✅ 拆分查询并去重（各自走索引）
SELECT * FROM system_log WHERE log_type = 'error' -- 索引: log_type
UNION ALL
SELECT * FROM system_log
WHERE source_module = 'api'
AND log_type != 'error'; -- 索引: source_module典型应用：监控系统多维度检索告警日志。
6、避免函数转换出生日期
场景：我们统计1990年出生用户
-- ❌ 函数计算导致索引失效
SELECT * FROM users
WHERE YEAR(birthday) = 1990; -- 无法使用birthday索引
-- ✅ 直接比较日期范围
SELECT * FROM users
WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31'; -- 走日期索引典型应用：用户画像按年龄段分析。
7、小表驱动大表JOIN顺序
场景：部门表（小）关联销售表（大）
-- ❌ 大表作驱动表（可能全表扫描）
SELECT s.* FROM sales s
JOIN departments d ON s.dept_id = d.id;
-- ✅ 小表驱动大表（强制优化器顺序）：STRAIGHT_JOIN是MySQL专属语法
SELECT /*+ STRAIGHT_JOIN */ s.*
FROM departments d -- 100行的小表
JOIN sales s ON d.id = s.dept_id; -- 驱动1亿行大表
-- ✅ PostgreSQL方案：用LEADING提示
SELECT /*+ LEADING(d s) */ s.*
FROM departments d
JOIN sales s ON d.id = s.dept_id;
-- ✅ Oracle方案：用ORDERED提示
SELECT /*+ ORDERED */ s.*
FROM departments d
JOIN sales s ON d.id = s.dept_id;典型应用：数据仓库中维度表关联实际表。
8、覆盖索引加速订单查询
场景：我们查询用户订单概要
-- 创建覆盖索引（包含所有查询字段）
CREATE INDEX idx_user_orders ON orders (user_id, order_date, amount);
-- ✅ 直接从索引取数据（避免回表）
SELECT user_id, order_date, amount -- 所有字段均在索引中
FROM orders
WHERE user_id = 2003; -- 索引覆盖扫描典型应用：订单列表页快速加载基础信息。
9、常量条件提前过滤
场景：我们按区域和品类筛选销售数据
-- ❌ JOIN后过滤（可能处理大量无效数据）
SELECT *
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.region = 'Asia' AND p.category = 'Electronics';
-- ✅ 先过滤区域再JOIN（减少参与JOIN的数据量）
SELECT *
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.region = 'Asia' -- 先过滤90%非亚洲数据
AND p.category = 'Electronics'; -- 再关联产品表典型应用：BI报表中多层过滤条件优化。
10、避免隐式类型转换
场景：按字符串类型ID查询
-- ❌ 数字与字符串比较（索引失效）
SELECT * FROM devices
WHERE device_id = 12345; -- device_id为VARCHAR类型
-- ✅ 保持类型一致（利用索引）
SELECT * FROM devices
WHERE device_id = '12345'; -- 字符串匹配索引典型应用：物联网设备管理按设备ID准确查询。
11、用 CASE WHEN 替代多轮扫描
场景：我们根据不同状态统计订单数量
-- ❌ 多次全表扫描（性能低下）
SELECT COUNT(*) AS total_orders FROM orders;
SELECT COUNT(*) AS pending_orders FROM orders WHERE status = 'pending';
SELECT COUNT(*) AS shipped_orders FROM orders WHERE status = 'shipped';
-- ✅ 单次扫描完成多维度统计
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_orders
FROM orders; -- 一次扫描完成三类统计典型应用：管理后台的订单状态统计面板。
12、分区表优化时间范围查询
场景：我们查询最近30天日志
-- ❌ 非分区表全量扫描
SELECT * FROM server_logs
WHERE log_time BETWEEN NOW() - INTERVAL 30 DAY AND NOW(); -- 扫描全年数据
-- ✅ 按月分区表仅扫描相关分区
CREATE TABLE server_logs (
id INT,
log_content TEXT,
log_time DATETIME
) PARTITION BY RANGE (TO_DAYS(log_time)) (
PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01'))
);
SELECT * FROM server_logs
WHERE log_time BETWEEN NOW() - INTERVAL 30 DAY AND NOW(); -- 仅扫描最近分区典型应用：时间序列数据（日志、监控数据）的高效查询。
13、函数索引优化JSON查询
场景：我们查询JSON字段中的特定属性
-- ❌ 无法使用常规索引
SELECT * FROM products
WHERE JSON_EXTRACT(specs, '$.weight') > 10; -- 全表扫描
-- ✅ 创建函数索引加速查询
CREATE INDEX idx_product_weight ON products( (JSON_EXTRACT(specs, '$.weight')) );
SELECT * FROM products
WHERE JSON_EXTRACT(specs, '$.weight') > 10; -- 使用函数索引典型应用：电商平台查询商品规格参数。
14、游标替代方案处理逐行逻辑
场景：根据用户等级批量更新折扣
-- ❌ 游标逐行处理效率极低
DECLARE user_cursor CURSOR FOR
SELECT id, level FROM users;
OPEN user_cursor;
FETCH NEXT FROM user_cursor...
-- ✅ 批量CASE WHEN更新
UPDATE users SET discount =
CASE
WHEN level = 'VIP' THEN 0.8
WHEN level = 'Premium' THEN 0.9
ELSE 1.0
END; -- 单语句完成全量更新典型应用：会员系统批量更新权益。
15、物化视图加速复杂聚合
场景：实时显示每日销售排行榜
-- ❌ 每次查询实时聚合（10秒+）
SELECT product_id, SUM(amount)
FROM orders
WHERE order_date = CURDATE()
GROUP BY product_id ORDER BY SUM(amount) DESC LIMIT 10;
-- ✅ 创建物化视图定时刷新
CREATE MATERIALIZED VIEW daily_top_sales
REFRESH EVERY 5 MINUTE
AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
WHERE order_date = CURDATE()
GROUP BY product_id;
-- 查询物化视图（0.1秒）
SELECT * FROM daily_top_sales ORDER BY total_sales DESC LIMIT 10;典型应用：实时数据大屏的快速展示。
16、压缩归档历史数据
场景：处理千万级历史订单
-- ❌ 所有数据存在业务表（查询缓慢）
SELECT * FROM orders WHERE order_date < '2020-01-01'; -- 扫描全表
-- ✅ 分区归档历史数据
CREATE TABLE orders_archive (
LIKE orders INCLUDING INDEXES
) COMPRESSION='ZLIB'; -- 启用压缩
INSERT INTO orders_archive
SELECT * FROM orders WHERE order_date < '2020-01-01';
DELETE FROM orders WHERE order_date < '2020-01-01';典型应用：金融系统历史数据存储优化。
17、避免视图嵌套导致的性能黑洞
场景：多层视图关联查询
-- ❌ 嵌套视图导致执行计划混乱
CREATE VIEW v_orders AS SELECT * FROM orders WHERE status = 'completed';
CREATE VIEW v_user_orders AS
SELECT u.name, v.*
FROM users u JOIN v_orders v ON u.id = v.user_id;
-- 查询性能极差
SELECT * FROM v_user_orders WHERE amount > 1000;
-- ✅ 扁平化查询结构
SELECT u.name, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
AND o.amount > 1000; -- 直接访问基表典型应用：报表系统避免过度抽象。
18、位图索引优化低基数字段
场景：性别、状态等枚举值查询
-- ❌ B-Tree索引效率低
CREATE INDEX idx_gender ON users(gender); -- 性别只有2个值
SELECT * FROM users WHERE gender = 'F';
-- ✅ 位图索引大幅提升性能
CREATE BITMAP INDEX idx_bm_gender ON users(gender);
SELECT * FROM users WHERE gender = 'F'; -- 位图快速定位典型应用：数据仓库中维度字段查询。
19、避免触发器导致的隐式性能损耗
场景：订单创建时更新统计值
-- ❌ 触发器逐行更新（高并发时锁竞争）
CREATE TRIGGER update_order_count
AFTER INSERT ON orders
FOR EACH ROW
UPDATE user_stats SET order_count = order_count + 1
WHERE user_id = NEW.user_id;
-- ✅ 异步批量更新
-- 使用消息队列或定时任务
INSERT INTO order_created_events (user_id) VALUES (123);
-- 每小时批量更新
UPDATE user_stats us
JOIN (
SELECT user_id, COUNT(*) AS cnt
FROM order_created_events
GROUP BY user_id
) tmp ON us.user_id = tmp.user_id
SET us.order_count = us.order_count + tmp.cnt;典型应用：高并发系统的计数器场景。
20、列式存储优化分析查询
场景：十亿级数据聚合分析
-- ❌ 行式存储全表扫描（5分钟+）
SELECT product_type, AVG(price), MAX(quantity)
FROM sales GROUP BY product_type;
-- ✅ 列式存储加速聚合
CREATE TABLE sales_columnar (
sale_id INT,
product_type VARCHAR(20),
price DECIMAL(10,2),
quantity INT
) ENGINE=ColumnStore; -- 使用列式引擎
SELECT product_type, AVG(price), MAX(quantity)
FROM sales_columnar GROUP BY product_type; -- 秒级响应典型应用：大数据分析平台OLAP场景。
21、使用临时表分解复杂查询
场景：多层级关联的复杂报表生成
-- ❌ 单条复杂SQL难以优化（嵌套5层子查询）
SELECT
d.name AS dept_name,
(SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id) AS emp_count,
(SELECT AVG(salary) FROM salaries s WHERE s.emp_id IN
(SELECT id FROM employees WHERE dept_id = d.id)
) AS avg_salary
FROM departments d;
-- ✅ 分步使用临时表存储中间结果
CREATE TEMPORARY TABLE temp_dept_stats AS
SELECT
d.id AS dept_id,
d.name AS dept_name,
COUNT(e.id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name;
CREATE TEMPORARY TABLE temp_salary_stats AS
SELECT
e.dept_id,
AVG(s.salary) AS avg_salary
FROM employees e
JOIN salaries s ON e.id = s.emp_id
GROUP BY e.dept_id;
-- 最终合并结果
SELECT
t1.dept_name,
t1.emp_count,
t2.avg_salary
FROM temp_dept_stats t1
JOIN temp_salary_stats t2 ON t1.dept_id = t2.dept_id;典型应用：数据仓库中ETL过程分阶段处理。
22、利用窗口函数避免自连接
场景：我们计算每个部门内员工的工资排名
-- ❌ 自连接导致O(n²)复杂度
SELECT
e1.name,
e1.department,
COUNT(e2.id) + 1 AS rank
FROM employees e1
LEFT JOIN employees e2
ON e1.department = e2.department
AND e1.salary < e2.salary
GROUP BY e1.id; -- 性能随数据量指数级下降
-- ✅ 窗口函数线性扫描
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees; -- 一次扫描完成计算典型应用：绩效排名、Top N分析等场景。
23、索引下推优化复合条件查询
场景：联合索引条件下非索引列的过滤
-- 表结构：INDEX (age, city)
-- ❌ 旧版本MySQL可能先取行再过滤
SELECT * FROM users
WHERE age > 20 AND city = 'Beijing' AND name LIKE '张%';
-- ✅ 支持索引下推的数据库自动优化
-- 无需改写SQL，数据库自动将`name LIKE`条件下推至存储引擎
-- 确保使用最新数据库版本（MySQL 5.6+）典型应用：电商平台多条件筛选商品。
24、异步删除大数据量数据
场景：我们删除千万级历史数据
-- ❌ 直接删除导致长事务阻塞
DELETE FROM user_logs WHERE created_at < '2020-01-01'; -- 执行10分钟+
-- ✅ 分批次异步删除
DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
DECLARE done INT DEFAULT FALSE;
WHILE NOT done DO
DELETE FROM user_logs
WHERE created_at < '2020-01-01'
LIMIT 10000; -- 每次删1万行
SET done = ROW_COUNT() = 0;
COMMIT;
DO SLEEP(1); -- 间隔1秒减少锁竞争
END WHILE;
END$$
DELIMITER ;
CALL batch_delete();典型应用：日志系统定期清理过期数据。
25、全文索引替代LIKE模糊查询
场景：商品标题关键字搜索
-- ❌ 通配符LIKE无法使用索引
SELECT * FROM products
WHERE title LIKE '%智能手机%'; -- 全表扫描
-- ✅ 创建全文索引加速搜索
ALTER TABLE products ADD FULLTEXT INDEX idx_title (title);
SELECT * FROM products
WHERE MATCH(title) AGAINST('智能手机' IN NATURAL LANGUAGE MODE);典型应用：内容检索系统、电商搜索。
26、使用Generated Column物化计算列
场景：频繁查询JSON字段的解析值
-- ❌ 每次查询实时解析JSON
SELECT
id,
JSON_EXTRACT(profile, '$.contact.phone') AS phone
FROM users;
-- ✅ 物化列避免重复计算
ALTER TABLE users
ADD COLUMN phone VARCHAR(20)
GENERATED ALWAYS AS (JSON_EXTRACT(profile, '$.contact.phone')) STORED;
CREATE INDEX idx_phone ON users(phone); -- 可索引
SELECT id, phone FROM users; -- 直接读取物化列典型应用：频繁访问的JSON/XML字段提取。
27、CTE递归查询替代循环处理
场景：我们查询部门树形结构
-- ❌ 应用层循环查询（N+1问题）
SELECT * FROM departments WHERE parent_id IS NULL; -- 查顶级部门
-- 然后循环查询每个部门的子部门...
-- ✅ 递归CTE一次获取整棵树
WITH RECURSIVE dept_tree AS (
SELECT id, name, parent_id, 1 AS level
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id, dt.level + 1
FROM departments d
JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY level, id;典型应用：组织架构、分类目录等树形数据查询。
28、避免HAVING子句滥用
场景：分组后筛选订单数量
-- ❌ HAVING执行在分组后，效率低
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5; -- 先分组再过滤
-- ✅ WHERE在分组前过滤
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE EXISTS ( -- 先过滤有效订单
SELECT 1 FROM order_details od
WHERE od.order_id = orders.id AND od.status = 'valid'
)
GROUP BY user_id
HAVING COUNT(*) > 5; 典型应用：用户行为分析中的条件过滤。
29、空间数据使用R-Tree索引
场景：我们查找附近的加油站
-- ❌ 传统索引无法加速距离计算
SELECT * FROM gas_stations
WHERE ST_Distance(location, POINT(116.4074, 39.9042)) < 5000; -- 全表扫描
-- ✅ 空间索引加速范围查询
ALTER TABLE gas_stations ADD SPATIAL INDEX(location);
SELECT * FROM gas_stations
WHERE MBRContains(
ST_Buffer(POINT(116.4074, 39.9042), 5000),
location
); -- 使用索引快速筛选候选集典型应用：LBS应用、地理围栏。
30、使用INSERT … ON DUPLICATE KEY UPDATE避免先查后改
场景：记录用户最后登录时间
-- ❌ 先查询后更新（2次交互）
SELECT id FROM user_login WHERE user_id = 123;
-- 如果存在则UPDATE，否则INSERT
-- ✅ 原子化操作
INSERT INTO user_login (user_id, last_login, login_count)
VALUES (123, NOW(), 1)
ON DUPLICATE KEY UPDATE
last_login = NOW(),
login_count = login_count + 1;典型应用：计数器、状态更新等高并发场景。
31、使用Group Commit优化高并发写入
场景：物联网设备批量上报数据
-- ❌ 单条插入（每秒1000次提交）
INSERT INTO device_data (device_id, value) VALUES (1001, 23.5);
INSERT INTO device_data (device_id, value) VALUES (1002, 18.7);
...
-- ✅ 批量提交+组提交优化
INSERT INTO device_data (device_id, value)
VALUES (1001, 23.5), (1002, 18.7), ... (10000, 19.2); -- 每批1000条
-- 数据库配置（以InnoDB为例）：
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- 牺牲部分持久性换性能
SET GLOBAL sync_binlog = 0;典型应用：物联网、日志采集等高频写入场景。
32、使用Skip Locked处理排队系统
场景：分布式任务调度
-- ❌ 悲观锁导致并发阻塞
BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY priority DESC
FOR UPDATE; -- 锁定整个结果集
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;
-- ✅ 跳过已锁定记录
BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY priority DESC
FOR UPDATE SKIP LOCKED -- 跳过被锁定的行
LIMIT 1; -- 只取一个任务
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;典型应用：高并发任务调度系统。
33、使用Partial Indexes（部分索引）优化高频查询
场景：只查询活跃用户的订单
-- ❌ 全量索引包含无效数据
CREATE INDEX idx_user_orders ON orders(user_id);
SELECT * FROM orders WHERE user_id = 100 AND status = 'active'; -- 索引包含已撤销订单
-- ✅ 创建条件索引
CREATE INDEX idx_active_user_orders ON orders(user_id) WHERE status = 'active';
SELECT * FROM orders
WHERE user_id = 100 AND status = 'active'; -- 索引体积减少70%典型应用：电商平台查询有效订单。
34、利用哈希索引加速等值查询
场景：内存表的主键查询
-- ❌ B-Tree索引在内存中非最优
CREATE TABLE session_data (
session_id CHAR(32) PRIMARY KEY,
data BLOB
) ENGINE=InnoDB; -- B-Tree索引
-- ✅ 使用哈希索引（内存表）
CREATE TABLE session_data (
session_id CHAR(32) PRIMARY KEY,
data BLOB
) ENGINE=MEMORY; -- 默认哈希索引
SELECT data FROM session_data WHERE session_id = 'e4d909c290d0fb1ca068ffaddf22cbd0'; -- O(1)复杂度典型应用：会话存储、临时缓存表。
35、使用Generated Column进行数据类型转换优化
场景：字符串类型的日期范围查询
-- ❌ 字符串日期无法走索引
CREATE TABLE logs (
id INT PRIMARY KEY,
log_date VARCHAR(10) -- 格式'YYYY-MM-DD'
);
SELECT * FROM logs WHERE log_date BETWEEN '2023-01-01' AND '2023-01-31'; -- 全表扫描
-- ✅ 生成列转换类型
ALTER TABLE logs
ADD COLUMN log_date_real DATE
GENERATED ALWAYS AS (STR_TO_DATE(log_date, '%Y-%m-%d')) STORED;
CREATE INDEX idx_log_date ON logs(log_date_real);
SELECT * FROM logs WHERE log_date_real BETWEEN '2023-01-01' AND '2023-01-31'; -- 日期索引典型应用：遗留系统日期字段优化。
36、使用Pivot技术替代多重JOIN
场景：将多行属性转为列展示
-- ❌ 多重JOIN导致性能低下
SELECT
u.id,
MAX(CASE WHEN a.type='email' THEN a.value END) AS email,
MAX(CASE WHEN a.type='phone' THEN a.value END) AS phone
FROM users u
LEFT JOIN attributes a ON u.id = a.user_id
GROUP BY u.id; -- 需要分组计算
-- ✅ 使用PIVOT语法（SQL Server/Oracle）
SELECT *
FROM (
SELECT user_id, type, value FROM attributes
) AS src
PIVOT (
MAX(value) FOR type IN ([email], [phone])
) AS pvt; -- 直接行列转换
-- ✅ MySQL兼容方案（用CASE WHEN模拟PIVOT）
SELECT
user_id,
MAX(CASE WHEN type = 'email' THEN value END) AS email,
MAX(CASE WHEN type = 'phone' THEN value END) AS phone
FROM attributes
GROUP BY user_id;
-- 这是业界广泛采用的“模拟 PIVOT”技术，称为 Conditional Aggregation（条件聚合）典型应用：用户属性宽表生成。
37、使用Lateral Join优化依赖子查询
场景：我们查询每个用户最近一次订单
-- ❌ 关联子查询效率低
SELECT u.id, u.name,
(SELECT order_id FROM orders o
WHERE o.user_id = u.id
ORDER BY order_date DESC LIMIT 1) AS last_order_id
FROM users u;
-- ✅ Lateral Join（PostgreSQL/MySQL 8.0+）
SELECT u.id, u.name, o.order_id
FROM users u
LEFT JOIN LATERAL (
SELECT order_id
FROM orders
WHERE user_id = u.id
ORDER BY order_date DESC
LIMIT 1
) o ON true;典型应用：获取分组内Top N记录。
38、使用Bloom Filter索引加速大表JOIN
场景：十亿级用户表与百亿级行为表关联
-- ❌ 传统JOIN资源消耗巨大
SELECT u.id, COUNT(b.event_id)
FROM users u
JOIN behavior b ON u.id = b.user_id
WHERE u.country = 'CN'
GROUP BY u.id;
-- ✅ 使用Bloom Filter预过滤（Hive/Spark SQL）
SET hive.bloom.filter.enabled=true;
CREATE INDEX idx_user_id ON TABLE behavior (user_id)
AS 'BLOOMFILTER' WITH DEFERRED REBUILD;
ALTER INDEX idx_user_id ON behavior REBUILD;
-- 查询时自动应用Bloom Filter典型应用：大数据生态中的JOIN优化。
39、使用向量化执行加速分析查询
场景：十亿条记录的聚合计算
-- ❌ 传统行式处理（慢）
SELECT product_id, AVG(price), SUM(quantity)
FROM sales GROUP BY product_id;
-- ✅ 启用向量化执行（如：PostgreSQL）
SET max_worker_processes = 8;
SET enable_vectorized_engine = on; -- 开启向量化
-- 一样查询速度提升5-10倍典型应用：数据仓库复杂分析。
40、使用表达式索引优化计算字段
场景：根据完整姓名查询
-- ❌ 无法使用索引
SELECT * FROM employees
WHERE CONCAT(first_name, ' ', last_name) = '张三';
-- ✅ 创建表达式索引
CREATE INDEX idx_full_name ON employees ((CONCAT(first_name, ' ', last_name)));
SELECT * FROM employees
WHERE CONCAT(first_name, ' ', last_name) = '张三'; -- 使用索引典型应用：频繁查询的复合字段。
41、使用BRIN索引优化时序数据
场景：我们按时间范围查询传感器数据
-- ❌ 传统索引体积过大
CREATE INDEX idx_sensor_time ON sensor_data(record_time); -- 索引大小接近数据表
-- ✅ BRIN索引（Block Range Index）
CREATE INDEX idx_brin_time ON sensor_data USING BRIN(record_time); -- 索引极小
SELECT * FROM sensor_data
WHERE record_time BETWEEN '2023-01-01' AND '2023-01-02';典型应用：物联网时序数据查询。
42、使用多版本并发控制(MVCC)避免锁竞争
场景：高并发读写场景
-- ❌ 使用锁机制导致阻塞
BEGIN;
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE; -- 行锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
COMMIT;
-- ✅ 基于MVCC的乐观锁
UPDATE accounts SET balance = balance - 100
WHERE id = 1001 AND balance >= 100; -- 无锁更新，检查版本或条件典型应用：金融系统账户更新。
43、使用列压缩减少IO开销
场景：大文本字段存储
-- ❌ 未压缩文本占用空间大
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT -- 平均100KB/篇
);
-- ✅ 启用列压缩
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT COMPRESSED -- 压缩至30%
);
SELECT content FROM articles WHERE id = 1001; -- 减少70%磁盘IO典型应用：内容管理系统、日志存储。
44、使用外键索引优化级联操作
场景：删除主表记录时同步子表
-- ❌ 无索引导致级联删除全表扫描
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE; -- 删除用户时自动删订单
-- 删除用户时：先全表扫描orders找对应user_id
-- ✅ 为外键字段创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id); -- 外键索引
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 删除用户时：通过索引快速定位订单典型应用：关系紧密的级联操作。
45、使用临时表缓存中间结果集
场景：复杂报表多次引用一样子查询
-- ❌ 重复执行一样子查询
SELECT
(SELECT AVG(amount) FROM orders WHERE user_id = u.id) AS avg_order,
(SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;
-- ✅ 临时表存储聚合结果
CREATE TEMPORARY TABLE temp_user_orders AS
SELECT user_id, AVG(amount) AS avg_order, MAX(amount) AS max_order
FROM orders GROUP BY user_id;
SELECT u.*, t.avg_order, t.max_order
FROM users u
LEFT JOIN temp_user_orders t ON u.id = t.user_id;典型应用：数据仓库复杂报表。
46、使用异步提交提升写入吞吐
场景：日志类数据高吞吐写入
-- ❌ 同步提交限制写入速度
INSERT INTO access_log (...) VALUES (...); -- 每次提交需等待刷盘
-- ✅ 异步提交（PostgreSQL示例）
SET synchronous_commit = off; -- 异步提交
-- 批量写入日志
INSERT INTO access_log (...) VALUES (...), (...), ...;典型应用：日志采集、监控数据写入。
47、使用连接池减少连接开销
场景：Web应用高并发访问数据库
-- ❌ 每次请求新建连接（慢）
$conn = new mysqli($host, $user, $pass, $db);
$conn->query("SELECT ...");
-- ✅ 连接池复用连接
$conn = $connection_pool->get_connection();
$conn->query("SELECT ...");
$connection_pool->release_connection($conn);典型应用：任何Web后端服务。
48、使用预编译语句防止SQL注入+提升性能
场景：动态条件查询
-- ❌ 拼接SQL风险高且无法重用执行计划
String sql = "SELECT * FROM users WHERE name = '" + name + "'";
stmt.executeQuery(sql);
-- ✅ 预编译重用执行计划
PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM users WHERE name = ?"
);
ps.setString(1, name);
ps.executeQuery();典型应用：所有动态SQL场景。
49、使用数据库连接代理实现读写分离
场景：读多写少业务场景
-- ❌ 所有查询到主库
$conn = connect_to_master();
$conn->query("SELECT * FROM big_table"); -- 读操作也到主库
-- ✅ 通过代理路由
$conn = connect_to_proxy();
$conn->query("UPDATE ..."); -- 写操作路由到主库
$conn->query("SELECT ..."); -- 读操作路由到从库典型应用：电商、社交应用等读密集型系统。
50、使用SQL提示（Hint）干预执行计划
场景：优化器选择错误索引
-- ❌ 优化器误选全表扫描
SELECT * FROM orders WHERE status = 'shipped' AND amount > 1000;
-- ✅ 强制指定索引（MySQL示例）
SELECT * FROM orders FORCE INDEX (idx_status_amount)
WHERE status = 'shipped' AND amount > 1000;典型应用：紧急性能问题处理。
51、使用物化视图日志增量刷新
场景：实时更新销售聚合数据
-- ❌ 全量刷新物化视图导致资源高峰
REFRESH MATERIALIZED VIEW daily_sales; -- 每次全量刷新10分钟
-- ✅ 使用物化视图日志增量刷新
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (sale_id, sale_date, amount);
CREATE MATERIALIZED VIEW daily_sales
REFRESH FAST ON COMMIT
AS
SELECT sale_date, SUM(amount) AS total_sales
FROM sales GROUP BY sale_date;
-- 每次提交后自动增量刷新（秒级完成）典型应用：实时业务看板数据更新。
52、使用函数索引优化JSON路径查询
场景：高效查询JSONB字段中的嵌套属性
-- ❌ 直接查询无法使用索引
SELECT * FROM products
WHERE specs->'dimensions'->>'width' > '100'; -- 全表扫描
-- ✅ 创建函数索引
CREATE INDEX idx_product_width ON products
((CAST(specs->'dimensions'->>'width' AS INTEGER)));
SELECT * FROM products
WHERE CAST(specs->'dimensions'->>'width' AS INTEGER) > 100; -- 使用索引典型应用：产品规格参数的高效检索。
53、使用分区交换实现零停机归档
场景：归档历史数据不影响在线业务
-- ❌ 直接删除历史数据导致表锁
DELETE FROM orders WHERE order_date < '2022-01-01'; -- 锁表30分钟
-- ✅ 分区交换归档
-- 创建归档表（一样结构）
CREATE TABLE orders_archive (...) PARTITION BY RANGE (order_date);
-- 交换分区
ALTER TABLE orders
EXCHANGE PARTITION p2021
WITH TABLE orders_archive
WITHOUT VALIDATION; -- 瞬间完成
-- 在后台处理归档表数据典型应用：金融系统历史数据迁移。
54、使用并行索引扫描加速大表查询
场景：十亿级用户表范围查询
-- ❌ 单线程索引扫描慢
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';
-- ✅ 启用并行索引扫描（PostgreSQL）
SET max_parallel_workers_per_gather = 8;
CREATE INDEX idx_users_created ON users(created_at);
-- 一样查询速度提升5倍典型应用：大数据分析平台的时间范围查询。
55、使用连接池预处理语句
场景：高并发短查询
-- ❌ 每次执行都解析SQL
for each request:
db.query("SELECT * FROM products WHERE id = ?", [product_id])
-- ✅ 连接池级预处理
// 初始化时准备语句
const stmt = pool.prepare("SELECT * FROM products WHERE id = ?");
// 执行时直接使用预处理句柄
stmt.execute([product_id]); -- 跳过解析和优化阶段典型应用：微服务架构中的高频查询接口。
56、使用GIN索引加速数组查询
场景：我们查询包含特定标签的文章
-- ❌ 数组字段全扫描
SELECT * FROM articles
WHERE tags @> ARRAY['technology']; -- 全表扫描
-- ✅ PostgreSQL：数组字段用GIN索引
CREATE INDEX idx_article_tags ON articles USING GIN (tags); -- tags为ARRAY类型
SELECT * FROM articles
WHERE tags @> ARRAY['technology']; -- 索引加速
-- ✅ MySQL：全文索引用GIN（数组字段需用其他方案，如拆分表）
CREATE FULLTEXT INDEX idx_article_tags ON articles(tags) USING GIN; -- tags为VARCHAR类型（存储逗号分隔标签）典型应用：内容管理系统的标签过滤。
57、使用哈希聚合替代排序聚合
场景：大数据量分组统计
-- ❌ 排序聚合内存消耗大
SELECT department, COUNT(*)
FROM employees
GROUP BY department; -- 使用Sort Group
-- ✅ 使用哈希聚合
SET enable_sort = off; -- 强制哈希聚合
-- 一样查询内存减少70%，速度提升2倍典型应用：数据仓库中的大分组查询。
58、使用TTL自动过期数据
场景：自动清理验证码记录
-- ❌ 定时任务删除过期数据
DELETE FROM sms_codes
WHERE created_at < NOW() - INTERVAL '10 minutes'; -- 每分钟执行
-- ✅ 使用TTL（Time-To-Live）
CREATE TABLE sms_codes (
id SERIAL PRIMARY KEY,
phone VARCHAR(20),
code VARCHAR(6),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) WITH (ttl_expiration_expression = 'created_at + INTERVAL ''10 minutes''');
-- 数据库自动后台清理典型应用：临时数据存储（会话、验证码）。
说明：主流数据库的TTL实现方式（如：MySQL用事件调度器、PostgreSQL用pg_cron插件）
-- ✅ MySQL方案：用事件调度器实现TTL
CREATE TABLE sms_codes (
id INT AUTO_INCREMENT PRIMARY KEY,
phone VARCHAR(20),
code VARCHAR(6),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 开启事件调度器（全局）
SET GLOBAL event_scheduler = ON;
-- 创建定时清理事件（每1分钟执行）
CREATE EVENT IF NOT EXISTS clean_expired_sms
ON SCHEDULE EVERY 1 MINUTE
DO DELETE FROM sms_codes
WHERE created_at < NOW() - INTERVAL 10 MINUTE;
-- ✅ PostgreSQL: 使用 pg_cron
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule(
'clean-sms-codes',
'*/1 * * * *',
$$DELETE FROM sms_codes WHERE created_at < NOW() - INTERVAL '10 minutes'$$
);59、使用列式索引加速聚合
场景：实时分析十亿级数据
-- ❌ 传统索引无法加速聚合
SELECT product_id, SUM(quantity)
FROM sales
GROUP BY product_id; -- 全表扫描
-- ✅ 使用列式索引（ClickHouse）
CREATE TABLE sales (
product_id UInt32,
quantity Float32
) ENGINE = AggregatingMergeTree()
ORDER BY product_id;
-- 查询速度提升100倍典型应用：实时分析大屏数据计算。
60、使用异步物化视图
场景：跨数据中心数据聚合
-- ❌ 同步查询多个数据库
SELECT a.data FROM db1.a
UNION ALL
SELECT b.data FROM db2.b; -- 高延迟
-- ✅ 创建异步物化视图
CREATE MATERIALIZED VIEW global_view
AS
SELECT * FROM db1.a@link1
UNION ALL
SELECT * FROM db2.b@link2
REFRESH EVERY 5 MINUTE; -- 定期异步刷新
-- 查询本地物化视图
SELECT * FROM global_view;典型应用：分布式系统数据聚合。
61、使用位图索引加速多值查询
场景：用户标签组合查询
-- ❌ 多列OR查询效率低
SELECT * FROM users
WHERE tag1 = true OR tag2 = true OR tag3 = true;
-- ✅ 位图索引快速合并
CREATE BITMAP INDEX idx_tags ON users(tags);
SELECT * FROM users
WHERE tags IN ('tag1', 'tag2', 'tag3'); -- 位图索引合并典型应用：用户画像标签查询。
62、使用向量索引加速类似度搜索
场景：图片特征向量搜索
-- ❌ 全表扫描计算类似度
SELECT * FROM images
ORDER BY feature_vector <-> '[0.12, 0.34, ...]'
LIMIT 10; -- 耗时分钟级
-- ✅ 创建向量索引
CREATE INDEX idx_image_vector ON images
USING ivfflat (feature_vector vector_cosine_ops);
-- 一样查询毫秒级响应典型应用：AI内容检索系统。
63、使用分页游标替代OFFSET
场景：无限滚动列表
-- ❌ 深分页性能差
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 100000; -- 扫描100010行
-- ✅ 使用游标分页
SELECT * FROM posts
WHERE created_at < '2023-06-01' -- 上一页最后时间
ORDER BY created_at DESC
LIMIT 10; -- 扫描10行典型应用：社交媒体动态流。
64、使用全局二级索引加速分库查询
场景：分库分表后非分片键查询
-- ❌ 全分片扫描
SELECT * FROM orders
WHERE user_id = 123; -- 需查询所有分片
-- ✅ 创建全局二级索引
CREATE GLOBAL INDEX idx_user_orders ON orders(user_id);
-- 直接定位到对应分片典型应用：分布式数据库查询优化。
65、使用列压缩存储优化IO
场景：存储大文本内容
-- ❌ 未压缩文本占用空间大
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT -- 平均100KB/篇
);
-- ✅ 列压缩存储
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT COMPRESSED WITH (compression_level = 7) -- LZ4压缩
);
-- 存储空间减少70%，IO性能提升典型应用：内容管理系统、日志存储。
66、使用内存表加速临时计算
场景：复杂计算的中间结果
-- ❌ 磁盘临时表速度慢
CREATE TEMPORARY TABLE temp_results (...) ON COMMIT DROP;
-- ✅ 使用内存表
CREATE TABLE temp_results (...) ENGINE=MEMORY;
-- 中间计算速度提升10倍典型应用：复杂报表计算的中间步骤。
67、使用表达式索引优化计算字段
场景：查询年龄范围
-- ❌ 无法使用出生日期索引
SELECT * FROM users
WHERE EXTRACT(YEAR FROM age(birth_date)) BETWEEN 20 AND 30;
-- ✅ 创建表达式索引
CREATE INDEX idx_user_age ON users
((EXTRACT(YEAR FROM age(birth_date))));
-- 直接使用索引查询典型应用：用户年龄分段统计。
68、使用分区剪裁优化时间序列查询
场景：查询特定时间范围日志
-- ❌ 全分区扫描
SELECT * FROM logs
WHERE log_time BETWEEN '2023-06-01' AND '2023-06-02';
-- ✅ 按天分区
CREATE TABLE logs (...) PARTITION BY RANGE (log_time);
-- 自动只扫描相关分区典型应用：物联网设备数据查询。
69、使用连接复用减少连接开销
场景：微服务高频数据库访问
// ❌ 每次请求新建连接
app.get('/data', () => {
const conn = new Connection();
conn.query(...);
conn.close();
});
// ✅ 连接池复用
const pool = new ConnectionPool(...);
app.get('/data', () => {
const conn = pool.acquire();
conn.query(...);
pool.release(conn);
});典型应用：云原生应用数据库访问。
70、使用数据库代理实现自动分片路由
场景：分库分表架构下的查询
-- ❌ 应用层处理分片逻辑
-- 需要计算user_id % 4确定分片
shard = user_id % 4;
conn = get_shard_connection(shard);
conn.query("SELECT ...");
-- ✅ 使用数据库代理
-- 应用直接查询代理
SELECT * FROM users WHERE user_id = 123;
-- 代理自动路由到正确分片典型应用：超大规模系统分库分表架构。
71、冷热数据分层存储优化
场景：历史订单查询频率低
-- ❌ 所有数据存SSD，成本高昂
SELECT * FROM orders WHERE order_date > '2023-01-01'; -- 热数据
SELECT * FROM orders WHERE order_date < '2020-01-01'; -- 冷数据（占80%容量）
-- ✅ 自动分层存储
ALTER TABLE orders SET (
storage_policy = 'HOT:30d COLD:1y ARCHIVE:5y'
);
-- 查询时自动路由到对应存储层典型应用：电商平台订单历史查询。
72、分布式事务优化（Saga模式）
场景：跨服务订单创建
-- ❌ 分布式事务锁资源
BEGIN;
INSERT INTO orders ...; -- 服务A
UPDATE inventory ...; -- 服务B（跨数据库）
COMMIT; -- 两阶段提交性能差
-- ✅ Saga最终一致性
1. 订单服务：INSERT INTO orders (status='pending')...
2. 库存服务：UPDATE inventory SET lock_qty = lock_qty + 1...
3. 订单服务：UPDATE orders SET status='confirmed'...
-- 失败时执行补偿操作典型应用：微服务架构下单流程。
73、多租户索引优化
场景：SaaS系统分租户查询
-- ❌ 单索引包含所有租户
CREATE INDEX idx_tenant_data ON all_data (tenant_id, data);
-- ✅ 局部索引（PostgreSQL部分索引）
CREATE INDEX idx_tenant1_data ON all_data (data)
WHERE tenant_id = 1; -- 每个租户独立索引典型应用：SaaS应用多客户数据隔离。
74、HTAP混合负载隔离
场景：交易与分析同时进行
-- ❌ 分析查询阻塞交易
SELECT COUNT(*) FROM big_table; -- 全表扫描（OLAP）
INSERT INTO orders ...; -- 被阻塞（OLTP）
-- ✅ 使用读写分离+列存副本
-- OLTP主库：行式存储处理交易
-- OLAP副本：列式存储实时同步
SET replica_query = 'columnar'; -- 分析查询自动路由到列存副本典型应用：实时报表与交易并存系统。
75、资源组隔离关键业务
场景：保障核心交易性能
-- ❌ 报表查询耗尽资源
SELECT /*+ 复杂分析查询 */ ...; -- 占用90%CPU
-- ✅ 创建资源组
CREATE RESOURCE GROUP critical
CPU_RATE_LIMIT = 70; -- 核心交易组
CREATE RESOURCE GROUP report
CPU_RATE_LIMIT = 30; -- 报表组
ALTER USER trade_user SET RESOURCE GROUP critical;
ALTER USER report_user SET RESOURCE GROUP report;典型应用：金融交易系统。
76、跨库查询联邦优化
场景：整合多个数据源
-- ❌ 应用层多次查询拼接
result1 = db1.query("SELECT * FROM products...")
result2 = db2.query("SELECT * FROM inventory...")
merge_results(result1, result2)
-- ✅ 使用联邦引擎
CREATE SERVER remote_db FOREIGN DATA WRAPPER mysql;
CREATE FOREIGN TABLE remote_products (...) SERVER remote_db;
SELECT local.*, remote.stock
FROM local_products local
JOIN remote_products remote ON local.id = remote.product_id;典型应用：数据中台跨系统查询。
77、AI预测索引
场景：预测热门商品
-- ❌ 固定索引无法适应变化
CREATE INDEX idx_popular ON products (sales_count);
-- ✅ AI自动索引管理
ALTER TABLE products
ADD INDEX auto_idx
USING "adaptive"
WITH (refresh_interval = '1h'); -- 自动识别热点字段典型应用：流量波动大的电商推荐系统。
78、向量化UDF加速计算
场景：实时风险评分计算
-- ❌ 逐行计算UDF
SELECT id, complex_risk_score(data) FROM loans; -- 单行处理
-- ✅ 向量化执行
CREATE FUNCTION vectorized_risk_score(VECTOR)
RETURNS FLOAT AS ... LANGUAGE PL/python;
SELECT id, vectorized_risk_score(data_vector)
FROM loans; -- 批量处理典型应用：风控系统实时计算。
79、零拷贝数据导入
场景：每日批量数据加载
-- ❌ 传统INSERT慢
INSERT INTO target SELECT * FROM source; -- 逐行插入
-- ✅ 存储层直接挂载
ALTER TABLE target ATTACH PARTITION source
FROM 's3://bucket/data.parquet'; -- 秒级完成典型应用：数据仓库ETL过程。
80、区块链式数据版本
场景：审计历史追溯
-- ❌ 手动维护历史表
CREATE TABLE orders_history AS SELECT * FROM orders; -- 每日快照
-- ✅ 时态表自动版本
CREATE TABLE orders (
id INT PRIMARY KEY,
...
) WITH (SYSTEM_VERSIONING = ON);
-- 查询历史版本
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2023-01-01';典型应用：财务系统审计追踪。
81、动态数据屏蔽与性能
场景：生产环境数据库查询需要保护用户隐私（如：PII信息），但又不希望影响分析师查询性能。
-- ❌ 应用层处理：查询完整数据后由应用程序过滤和脱敏，网络传输和数据处理开销大
SELECT user_id, name, phone_number, email FROM users WHERE region = 'North';
-- ✅ 数据库层动态数据屏蔽：在传输前完成过滤和脱敏，减少网络传输量，计算下推
CREATE VIEW vw_users_analytics AS
SELECT
user_id,
name,
-- 使用脱敏函数，仅暴露手机号后四位
mask(phone_number, 'partial(3, "xxxx", 4)') AS phone_number,
-- 完全屏蔽邮箱
mask(email, 'email()') AS email
FROM users;
-- 分析师直接查询屏蔽后的视图，安全且高效
SELECT * FROM vw_users_analytics WHERE region = 'North';典型应用：数据分析、报表系统等需要兼顾数据安全与查询性能的场景。它属于架构调整 (D) 中的安全设计，也通过计算下推实现了**查询重构 (C)**。
82、基于成本的优化器（CBO）提示
场景：极端情况下，统计信息轻微偏差或复杂连接导致优化器选择了次优的执行计划（如：错误的连接顺序或索引）。
-- ❌ 无法干预，任由优化器选择可能很慢的计划
SELECT *
FROM large_table_a a
JOIN large_table_b b ON a.key = b.key
JOIN small_table_c c ON b.other_key = c.other_key;
-- 优化器可能错误地选择以 large_table_b 作为驱动表
-- ✅ 使用优化器提示（Hints）显式指定连接顺序或方法（语法因数据库而异）
SELECT /*+ LEADING(c b a) USE_NL(b a) */ *
FROM large_table_a a
JOIN large_table_b b ON a.key = b.key
JOIN small_table_c c ON b.other_key = c.other_key;
-- 提示优化器：从小表 c 开始，使用嵌套循环连接（NL）依次连接 b 和 a典型应用：紧急性能问题修复、数据仓库复杂查询调优。这是对索引策略 (B) 和查询重构 (C) 的终极补充手段，属于深度干预。
83、不可变表优化
场景：日志、事件流等只追加（append-only）且永不更新的数据场景，传统表的更新、删除开销成为瓶颈。
-- ❌ 使用常规堆表，即使没有更新删除，也需要维护事务可见性信息（如：MVCC版本）
CREATE TABLE event_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMP,
user_id INT,
event_data JSONB
); -- 存在行版本开销
-- ✅ 使用不可变表或追加优化表，移除不必要的更新开销
CREATE TABLE event_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMP,
user_id INT,
event_data JSONB
) WITH (
appendonly = true, -- 只追加
orientation = column, -- 列式存储
compresstype = zstd -- 压缩
); -- 无更新/删除开销，写入速度和压缩比极高典型应用：时序数据、日志存储、事件溯源架构。这是架构调整 (D) 中根据业务特征选择存储模型的典范，也影响了冷热分离 (D2) 策略。
83个SQL优化场景，我们就盘点到这！
是不是发现，SQL优化，远不止“加个索引”那么简单？
它更像是一个从编码习惯到架构思维的打怪升级。我们别蛮干，要巧干！先看看查询是不是没必要，再看看索引有没有用对，最后才思考是不是要动架构。这套组合拳打下来，大部分问题都能搞定。
目前，就从我们手头那个最折磨人的慢查询开始，对照着这些场景，动手试试吧！
作者丨SQL数据库
来源丨公众号：SQL语句（ID：SQLquery）
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...