数据库性能瓶颈常常是系统整体响应速度的拖累因素。面对慢查询导致的页面加载缓慢，开发者的第一反应往往是“再加一个索引”。然而，盲目的索引堆叠不仅无法根治问题，反而会增加数据库的维护开销并可能降低写性能。
真正的SQL优化是一门综合性的技术，它贯穿于SQL编写、索引设计、数据库配置乃至整体架构的每一个环节。本文整理了83个覆盖不同维度的优化场景，从基础的查询语句规范，到复杂的分库分表架构设计，旨在提供一套系统性的性能调优方法论。
**1、避免 SELECT ***
场景：查询用户详情时需要排除敏感字段。
-- ❌ 返回全部字段（包含如`password`等不需要的敏感字段）
SELECT * FROM users WHERE id = 1001;
-- ✅ 明确指定所需字段（提升性能与安全性）
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' -- 可利用`(id, status)`复合索引
);
典型应用：电商平台筛选有效订单，避免对子查询结果集进行全表扫描。
3、延迟关联优化千万级数据分页
场景：分页展示用户行为日志。
-- ❌ 深分页时，OFFSET需要先扫描并跳过大量记录，效率低下
SELECT * FROM user_logs ORDER BY log_time DESC LIMIT 10 OFFSET 500000; -- 需扫描500010行
-- ✅ 先通过索引定位目标记录的ID，再通过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行目标数据回表
典型应用：后台管理系统查看历史操作日志。这涉及到对MySQL等数据库分页机制的深度理解。
4、批量更新用户积分
场景：活动期间批量增加用户积分。
-- ❌ 循环执行单条更新语句，网络和事务开销成倍增加
UPDATE users SET points = points + 10 WHERE id = 1001;
UPDATE users SET points = points + 10 WHERE id = 1002;
...
-- ✅ 单条语句批量操作，极大减少数据库交互次数
UPDATE users SET points = points + 10 WHERE id IN (1001, 1002, ..., 10000); -- 一次更新1万用户
典型应用：运营活动批量发放奖励。
5、使用 UNION ALL 拆分 OR 条件
场景：多条件混合查询系统日志。
-- ❌ 在多个列上使用OR，可能导致所有相关索引失效
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年出生的用户。
-- ❌ 对索引列`birthday`使用YEAR()函数，导致索引失效
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;
-- ✅ 使用提示（Hint）强制指定小表驱动大表
-- MySQL 使用 STRAIGHT_JOIN
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、覆盖索引加速订单查询
场景：仅查询用户订单的概要信息（用户ID、日期、金额）。
-- 创建覆盖索引（索引包含查询所需的所有字段）
CREATE INDEX idx_user_orders ON orders (user_id, order_date, amount);
-- ✅ 查询字段均包含在索引中，无需回表，直接从索引树获取数据
SELECT user_id, order_date, amount
FROM orders WHERE user_id = 2003; -- 仅进行索引覆盖扫描 (Using index)
典型应用：订单列表页快速加载基础信息。
9、将常量条件提前过滤
场景：按区域和产品品类筛选销售数据。
-- ❌ 先进行JOIN操作，再对JOIN后的宽表应用过滤条件
SELECT * FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.region = 'Asia' AND p.category = 'Electronics';
-- ✅ 先使用强过滤条件（如`s.region = 'Asia'`）缩小驱动表结果集，再进行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进行查询。
-- ❌ 数字12345与字符串类型的`device_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(); -- 仅扫描最近1-2个分区
典型应用：时间序列数据（如日志、监控指标）的高效查询。
13、函数索引优化JSON字段查询
场景：查询商品规格（JSON格式）中重量大于10的商品。
-- ❌ 直接对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...
-- 循环内逐条UPDATE
-- ✅ 使用单条基于CASE的UPDATE语句完成批量更新
UPDATE users SET discount =
CASE
WHEN level = 'VIP' THEN 0.8
WHEN level = 'Premium' THEN 0.9
ELSE 1.0
END; -- 单语句完成全表更新
典型应用：会员系统批量更新权益。
15、物化视图加速复杂聚合查询
场景：实时展示每日商品销售排行榜。
-- ❌ 每次查询都对亿级订单表进行实时聚合，响应慢
SELECT product_id, SUM(amount) FROM orders WHERE order_date = CURDATE()
GROUP BY product_id ORDER BY SUM(amount) DESC LIMIT 10; -- 可能耗时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;
-- 查询物化视图，响应极快
SELECT * FROM daily_top_sales ORDER BY total_sales DESC LIMIT 10; -- 0.1秒内响应
典型应用：实时数据大屏的快速展示。
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、位图索引优化低基数字段查询
场景：对性别、状态等枚举值字段进行查询（适用于Oracle等支持数据库）。
-- ❌ 对低基数（不同值很少）的字段使用B-Tree索引，效率不高
CREATE INDEX idx_gender ON users(gender); -- 性别只有‘M’，‘F’两种值
SELECT * FROM users WHERE gender = 'F';
-- ✅ 使用位图索引，对低基数字段的等值查询和复合查询有奇效
CREATE BITMAP INDEX idx_bm_gender ON users(gender);
SELECT * FROM users WHERE gender = 'F'; -- 利用位图快速定位
典型应用：数据仓库中维度字段的快速筛选。
19、避免在高并发场景滥用触发器
场景：订单创建时实时更新用户统计值。
-- ❌ 为每行INSERT创建触发器，高并发下引发严重的行锁竞争
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;
-- ✅ 采用异步批量更新策略，例如通过消息队列或定时任务
-- 1. 插入事件记录表（无触发器）
INSERT INTO order_created_events (user_id) VALUES (123);
-- 2. 定时（如每小时）批量聚合更新
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、列式存储引擎优化分析型查询
场景：对十亿级销售数据进行聚合分析。
-- ❌ 行式存储引擎（如InnoDB）进行全表扫描和聚合，速度慢
SELECT product_type, AVG(price), MAX(quantity)
FROM sales GROUP BY product_type; -- 可能耗时5分钟以上
-- ✅ 使用列式存储引擎（如ClickHouse或MariaDB ColumnStore）
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包含多层嵌套子查询，可读性差且难以优化
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可能先通过索引(age, city)定位行，再将所有行取回server层过滤`name`
SELECT * FROM users WHERE age > 20 AND city = 'Beijing' AND name LIKE '张%';
-- ✅ 支持索引下推的数据库（MySQL 5.6+）会自动优化
-- 优化器会将`name LIKE '张%'`条件下推至存储引擎层，在索引扫描时就进行过滤
-- 无需改写SQL，但需确保数据库版本支持并启用该特性
典型应用：电商平台多条件组合筛选商品。
24、异步分批次删除大数据
场景：安全删除千万级历史日志数据。
-- ❌ 直接执行大范围DELETE，产生长事务，阻塞其他操作
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); -- 短暂间隔，减少锁竞争和IO压力
END WHILE;
END$$
DELIMITER ;
CALL batch_delete();
典型应用：日志系统、业务系统定期清理过期数据。
25、全文索引替代 LIKE ‘%...%’ 模糊查询
场景：对商品标题进行关键字搜索。
-- ❌ 前导通配符`%`导致无法使用B-Tree索引
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，消耗CPU
SELECT
id,
JSON_EXTRACT(profile, '$.contact.phone') AS phone
FROM users;
-- ✅ 创建STORED类型的生成列，提前计算并存储，并可对其建立索引
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、合理使用WHERE与HAVING
场景：筛选出订单数量大于5的有效用户。
-- ❌ 在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、空间索引加速地理位置查询
场景：查找当前位置5公里范围内的加油站。
-- ❌ 使用传统距离计算函数，无法利用索引
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
场景：记录或更新用户最后登录时间。
-- ❌ 应用层先查询判断是否存在，再决定INSERT或UPDATE，需要两次数据库交互
SELECT id FROM user_login WHERE user_id = 123;
-- 根据查询结果，执行 INSERT 或 UPDATE
-- ✅ 使用原子操作，一次交互完成“有则更新，无则插入”
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、使用批量插入与组提交优化高并发写入
场景：物联网设备高频上报传感器数据。
-- ❌ 逐条插入，每次插入都对应一次事务提交
INSERT INTO device_data (device_id, value) VALUES (1001, 23.5);
INSERT INTO device_data (device_id, value) VALUES (1002, 18.7);
...
-- ✅ 1. 应用层批量插入
INSERT INTO device_data (device_id, value) VALUES
(1001, 23.5), (1002, 18.7), ... (10000, 19.2); -- 每批1000条数据
-- ✅ 2. 数据库层组提交优化（以MySQL InnoDB为例，需权衡数据持久性）
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- 每次事务提交只写日志到OS缓存，每秒刷盘
SET GLOBAL sync_binlog = 0; -- 关闭binlog同步写，依赖OS刷盘
典型应用：物联网数据采集、日志实时写入等高频写入场景。
32、使用 SKIP LOCKED 处理任务队列
场景：构建一个分布式任务调度系统，多个Worker并发获取任务。
-- ❌ 使用悲观锁 FOR UPDATE，同一时刻只有一个Worker能获取到待处理任务
BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY priority DESC
FOR UPDATE; -- 锁定所有符合条件的行
-- 然后取其中一条更新状态
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;
-- ✅ 使用 SKIP LOCKED 子句，跳过已被其他事务锁定的行，提升并发度
BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY priority DESC
FOR UPDATE SKIP LOCKED -- 关键：跳过已被锁定的行
LIMIT 1; -- 每个Worker只获取一个任务
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;
典型应用：高并发任务队列、消息队列的消费者实现。
33、使用部分索引优化高频查询
场景：只查询状态为‘active’的活跃用户订单。
-- ❌ 在全表上创建索引，索引中包含大量不需要的‘canceled’等状态的记录
CREATE INDEX idx_user_orders ON orders(user_id);
SELECT * FROM orders WHERE user_id = 100 AND status = 'active'; -- 仍需在索引中过滤status
-- ✅ 创建条件索引（部分索引），只索引活跃订单，体积更小，效率更高
CREATE INDEX idx_active_user_orders ON orders(user_id)
WHERE status = 'active'; -- PostgreSQL/SQLite等支持
SELECT * FROM orders WHERE user_id = 100 AND status = 'active'; -- 直接使用更小的部分索引
典型应用：查询条件固定的高频场景，如电商平台的有效订单查询。
34、利用哈希索引加速内存表等值查询
场景：存储用户会话数据，主键查询极为频繁。
-- ❌ 使用B-Tree索引，在纯内存操作中并非最快
CREATE TABLE session_data (
session_id CHAR(32) PRIMARY KEY, -- InnoDB默认使用B-Tree
data BLOB
) ENGINE=InnoDB;
-- ✅ 使用内存表并利用其默认的哈希索引（或显式指定）
CREATE TABLE session_data (
session_id CHAR(32) PRIMARY KEY, -- MEMORY引擎默认使用HASH索引
data BLOB
) ENGINE=MEMORY;
SELECT data FROM session_data WHERE session_id = 'e4d909c290d0fb1ca068ffaddf22cbd0'; -- 接近O(1)的哈希查找
注意：MEMORY表不支持TEXT/BLOB，示例需调整，且数据重启丢失。适用于临时缓存。
35、使用生成列优化数据类型查询
场景：日志表日期存储为字符串，需按日期范围查询。
-- ❌ 字符串类型的日期字段无法高效利用范围索引
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'; -- 全表扫描
-- ✅ 添加一个STORED生成列，将其转换为真正的DATE类型并建立索引
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或条件聚合进行行列转换
场景：将用户的多条属性记录（多行）转换为宽表格式（一行多列）。
-- ❌ 通过多次LEFT JOIN实现，性能差且SQL冗长
SELECT
u.id,
a1.value AS email,
a2.value AS phone
FROM users u
LEFT JOIN attributes a1 ON u.id = a1.user_id AND a1.type='email'
LEFT JOIN attributes a2 ON u.id = a2.user_id AND a2.type='phone';
-- ✅ 方案一：使用数据库PIVOT语法（如SQL Server）
SELECT *
FROM (
SELECT user_id, type, value FROM attributes
) AS src
PIVOT (
MAX(value) FOR type IN ([email], [phone])
) AS pvt;
-- ✅ 方案二：使用条件聚合（通用技术，也叫模拟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;
典型应用：用户画像宽表生成、报表中的行列转换。
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（MySQL 8.0+， PostgreSQL等支持）
SELECT u.id, u.name, o.order_id
FROM users u
LEFT JOIN LATERAL ( -- LATERAL允许子查询引用左侧表的列
SELECT order_id
FROM orders
WHERE user_id = u.id
ORDER BY order_date DESC
LIMIT 1
) o ON TRUE;
典型应用：查询分组内的Top N记录，替代效率低下的关联子查询。
38、使用布隆过滤器索引加速大表JOIN
场景：在十亿级用户表与百亿级行为表之间进行JOIN。
-- ❌ 直接JOIN会产生巨大的Shuffle开销和计算量
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;
-- ✅ 在大数据生态（如Hive/Spark）中使用Bloom Filter索引进行预过滤
-- 1. 在行为表上创建布隆过滤器索引
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;
-- 2. 执行查询，优化器会自动尝试使用BF进行过滤
-- 在执行JOIN前，先用BF快速判断user_id是否可能在behavior表中，过滤掉大量肯定不匹配的数据
典型应用：大数据平台中两张大表之间的JOIN操作优化。这是构建云原生大数据平台时常用的高级优化技术。
39、启用向量化执行引擎加速分析查询
场景：对十亿级销售表进行分组聚合分析。
-- ❌ 传统的行式执行引擎，逐行处理数据
SELECT product_id, AVG(price), SUM(quantity)
FROM sales GROUP BY product_id; -- 执行缓慢
-- ✅ 启用向量化执行引擎（如PostgreSQL的JIT，或ClickHouse等列存数据库的固有特性）
-- PostgreSQL示例：
SET max_worker_processes = 8;
SET enable_vectorized_engine = on; -- 假设存在该参数（实际参数可能不同）
-- 相同的查询，在支持向量化的数据库中，速度可提升数倍至数十倍
典型应用：数据仓库、OLAP场景下的复杂分析查询。
40、表达式索引优化计算字段查询
场景：根据员工的全名（姓+名）进行查询。
-- ❌ 在WHERE子句中对字段进行计算，索引失效
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索引优化时序数据范围查询
场景：按时间范围查询海量传感器数据。
-- ❌ 在时间戳上创建普通B-Tree索引，索引体积可能接近数据表本身
CREATE INDEX idx_sensor_time ON sensor_data(record_time);
SELECT * FROM sensor_data WHERE record_time BETWEEN '2023-01-01' AND '2023-01-02';
-- ✅ 使用BRIN索引（块范围索引），它记录数据块的范围而非具体值，索引极小
CREATE INDEX idx_brin_time ON sensor_data USING BRIN(record_time); -- PostgreSQL
SELECT * FROM sensor_data WHERE record_time BETWEEN '2023-01-01' AND '2023-01-02'; -- 先通过BRIN索引快速定位可能的数据块，再精确扫描这些块
典型应用：按时间顺序插入且按时间范围查询的物联网、日志数据。
42、利用多版本并发控制减少锁竞争
场景：高并发下的账户余额更新。
-- ❌ 使用悲观锁（SELECT FOR UPDATE），阻塞严重
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; -- 利用条件进行原子更新，依赖行锁或MVCC的写时冲突检测
-- 应用层检查 affected_rows，如果为0则表示余额不足或发生冲突
典型应用：读写比例高的金融、账户系统。
43、启用列压缩减少IO开销
场景：存储大量文章内容等大文本字段。
-- ❌ 未压缩文本，占用大量磁盘空间和内存缓冲
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT -- 平均每篇文章100KB
);
-- ✅ 启用表或列压缩（取决于数据库支持）
CREATE TABLE articles (
id INT PRIMARY KEY,
content TEXT COMPRESSED -- MySQL COMPRESSED属性（如InnoDB页压缩）或使用列存引擎的压缩
);
SELECT content FROM articles WHERE id = 1001; -- 读取时解压，减少磁盘IO流量
典型应用：文档管理系统、新闻网站、日志存储。
44、为外键创建索引优化级联操作
场景：删除用户时，级联删除其所有订单。
-- ❌ 定义外键但未在子表（orders）的`user_id`上建索引
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 CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 删除用户时，通过索引快速定位到子表相关行，效率高。
典型应用：任何有关联关系的表，且存在级联更新/删除操作。
45、使用临时表缓存复杂子查询结果
场景：复杂报表中多次引用同一个昂贵的子查询。
-- ❌ 相同的子查询在SELECT列表中被重复执行多次
SELECT
u.*,
(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;
-- ✅ 将子查询结果存入临时表，后续通过JOIN引用
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;
典型应用：数据仓库中ETL过程的中间步骤，或需要重复使用复杂计算结果的报表。
46、使用异步提交提升日志写入吞吐量
场景：高频写入访问日志。
-- ❌ 同步提交，每次INSERT都要等待事务日志持久化到磁盘
INSERT INTO access_log (timestamp, url, ip) VALUES (NOW(), '/home', '192.168.1.1');
-- ✅ 配置异步提交（以PostgreSQL为例，牺牲部分耐久性换取性能）
SET synchronous_commit = off; -- 事务提交后立即返回，日志写入由后台进程完成
-- 后续的INSERT操作延迟更低，吞吐量更高
INSERT INTO access_log (...) VALUES (...), (...), ...;
注意：此配置会带来在数据库崩溃时丢失少量最新提交数据的风险。适用于可容忍少量数据丢失的日志类场景。
47、使用数据库连接池
场景：Web应用处理大量并发请求，每个请求都需要访问数据库。
// ❌ 每个HTTP请求都创建新的数据库连接，连接建立和销毁开销巨大
app.get('/data', () => {
const conn = new Connection(config); // 建立TCP连接、认证等
const result = conn.query('SELECT ...');
conn.close(); // 关闭连接
return result;
});
// ✅ 使用连接池，复用已建立的连接
const pool = new ConnectionPool({ ...config, max: 20 }); // 初始化连接池
app.get('/data', () => {
const conn = pool.acquire(); // 从池中获取一个空闲连接（或等待）
const result = conn.query('SELECT ...');
pool.release(conn); // 将连接放回池中，而非关闭
return result;
});
典型应用：任何需要与数据库交互的多线程/多进程应用。
48、使用预编译语句
场景：动态生成带参数的查询。
// ❌ 拼接SQL字符串，有SQL注入风险，且数据库每次都要解析、优化SQL
String name = request.getParameter("name");
String sql = "SELECT * FROM users WHERE name = '" + name + "'"; // 危险！
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// ✅ 使用预编译语句（PreparedStatement），安全且高效
String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement ps = conn.prepareStatement(sql); // SQL被发送到数据库进行预编译
ps.setString(1, name); // 安全地设置参数
ResultSet rs = ps.executeQuery(); // 数据库重用预编译的执行计划
典型应用：所有包含用户输入或动态条件的数据库查询。
49、通过代理实现读写分离
场景：读请求远多于写请求的电商网站。
// ❌ 所有查询，无论读写，都指向主数据库
$conn = connect_to_master_db();
$conn->query("SELECT * FROM products WHERE ..."); // 读操作也给主库压力
// ✅ 引入数据库中间件（代理），自动路由
$conn = connect_to_proxy(); // 连接到代理地址
$conn->query("UPDATE products SET stock = ..."); // 写操作，代理转发到主库
$conn->query("SELECT * FROM products"); // 读操作，代理转发到某个从库
典型应用：读多写少的Web应用，缓解单库压力。这是应对高并发读场景的经典架构。
50、使用SQL提示干预执行计划
场景：优化器因统计信息偏差选择了次优索引。
-- ❌ 优化器可能错误地选择了全表扫描或一个低效的索引
SELECT * FROM orders WHERE status = 'shipped' AND amount > 1000;
-- ✅ 使用FORCE INDEX提示（MySQL语法）强制使用更合适的索引
SELECT * FROM orders FORCE INDEX (idx_status_amount)
WHERE status = 'shipped' AND amount > 1000;
-- 其他数据库提示示例：
-- PostgreSQL: /*+ IndexScan(orders idx_status_amount) */
-- Oracle: /*+ INDEX(orders idx_status_amount) */
典型应用：在紧急性能调优或优化器无法做出最佳选择时的最后手段。需谨慎使用。
51、使用物化视图日志实现快速刷新
场景：需要近乎实时地更新销售聚合数据。
-- ❌ 全量刷新物化视图，在数据量大时耗时耗资源
REFRESH MATERIALIZED VIEW daily_sales; -- 全量刷新，可能锁表
-- ✅ 创建物化视图日志，支持快速刷新（FAST REFRESH）
-- 1. 在主表上创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE (sale_id, sale_date, amount);
-- 2. 创建支持快速刷新的物化视图
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字段中的嵌套数值属性。
-- ❌ 直接查询JSON路径，无法利用普通索引
SELECT * FROM products WHERE (specs->'dimensions'->>'width')::INTEGER > 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; -- 使用索引
典型应用：灵活模式（Schema-less）存储，但需对特定字段进行高效查询。
53、使用分区交换实现数据归档
场景：将历史订单数据移出业务主表，且不希望影响在线业务。
-- ❌ 直接DELETE历史数据，产生长事务和大量UNDO日志
DELETE FROM orders WHERE order_date < '2022-01-01'; -- 可能锁表很长时间
-- ✅ 使用分区交换（Partition Exchange），瞬间完成
-- 1. 假设orders表已按order_date范围分区
-- 2. 创建一个与目标分区结构一致的归档表
CREATE TABLE orders_archive_2021 (...) WITH (compression=true);
-- 3. 执行分区交换
ALTER TABLE orders
EXCHANGE PARTITION p2021 WITH TABLE orders_archive_2021 WITHOUT VALIDATION;
-- 此操作是元数据更改，几乎瞬间完成。原p2021分区的数据瞬间变成了orders_archive_2021表的数据。
-- 4. 业务表orders中2021年分区已空，可以安全删除或归档orders_archive_2021。
典型应用：大型数据表的历史数据迁移和归档。
54、启用并行索引扫描
场景：在拥有多核CPU的服务器上查询十亿级用户表。
-- ❌ 单线程按索引扫描大量数据
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';
-- ✅ 启用并行查询（以PostgreSQL为例）
SET max_parallel_workers_per_gather = 4; -- 允许使用最多4个并行工作进程
-- 确保索引存在
CREATE INDEX idx_users_created ON users(created_at);
-- 执行查询，优化器可能决定启动多个进程并行扫描索引的不同部分
典型应用：数据仓库环境或高端OLTP系统中对大型索引的范围扫描。
55、使用连接池级别的预处理语句缓存
场景：微服务中高频执行的参数化短查询。
// ❌ 每次请求都创建新的PreparedStatement对象（虽然比Statement好，但仍有开销）
for (request : requests) {
PreparedStatement ps = conn.prepareStatement("SELECT name FROM users WHERE id = ?");
ps.setInt(1, request.userId);
ps.executeQuery();
}
// ✅ 使用连接池自带的预处理语句缓存（如HikariCP、PgBouncer in transaction mode）
// 配置连接池启用PSCache
pool.setPrepStmtCacheSize(250);
pool.setPrepStmtCacheSqlLimit(2048);
// 应用代码不变，但连接池会在背后缓存PreparedStatement，key是SQL字符串。
// 同一连接上执行相同SQL时，会复用缓存的PreparedStatement，省去数据库端的重复解析优化。
典型应用：微服务架构中，处理大量相同模式查询的接口。
56、使用GIN索引加速数组或多值类型查询
场景：查询包含特定标签的文章。
-- ❌ 对数组字段进行包含查询，无法使用B-Tree索引
SELECT * FROM articles WHERE tags @> ARRAY['technology']; -- PostgreSQL 数组操作符
-- ✅ 为数组字段创建GIN索引（PostgreSQL）
CREATE INDEX idx_article_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['technology']; -- 利用GIN索引快速查找
-- MySQL方案：如果标签以逗号分隔的字符串存储，可使用全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_tags (tags);
SELECT * FROM articles WHERE MATCH(tags) AGAINST('+technology' IN BOOLEAN MODE);
典型应用：内容管理系统的标签过滤、商品的多属性筛选。
57、使用哈希聚合替代排序聚合
场景：对大数据集进行分组统计，且不关心分组结果的顺序。
-- ❌ 数据库默认可能使用Sort Aggregate，需要对所有数据进行排序
SELECT department, COUNT(*) FROM employees GROUP BY department; -- 可能使用Sort GroupAgg
-- ✅ 在支持哈希聚合的数据库中，可尝试强制使用哈希聚合（如PostgreSQL）
SET enable_sort = off; -- 禁用排序聚合，迫使优化器考虑哈希聚合
-- 哈希聚合通过在内存中构建哈希表来分组，对于大数据且分组键重复度不高时可能更快，且更省内存
典型应用：数据仓库中的大规模分组查询，且无需排序输出。
58、使用TTL自动清理过期数据
场景：存储短信验证码，10分钟后自动失效。
-- ❌ 应用层通过定时任务清理
DELETE FROM sms_codes WHERE created_at < NOW() - INTERVAL '10 minutes'; -- 每分钟执行一次
-- ✅ 使用数据库内置的TTL（生存时间）特性
-- 示例1: MySQL 使用事件调度器（Event Scheduler）
SET GLOBAL event_scheduler = ON;
CREATE EVENT clean_expired_sms
ON SCHEDULE EVERY 1 MINUTE
DO
DELETE FROM sms_codes WHERE created_at < NOW() - INTERVAL 10 MINUTE;
-- 示例2: 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'$$);
-- 示例3: 某些云数据库或NoSQL（如Redis）直接支持TTL
典型应用：会话（Session）数据、临时验证码、缓存数据等有生命周期的数据。
59、使用列式存储引擎与索引
场景：对十亿级销售数据进行即席分析查询。
-- ❌ 传统行式数据库（如MySQL InnoDB）进行全表聚合，速度慢
SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id;
-- ✅ 使用专为OLAP设计的列式数据库（如ClickHouse）
CREATE TABLE sales (
product_id UInt32,
quantity Float32
) ENGINE = AggregatingMergeTree() -- ClickHouse引擎
ORDER BY product_id;
-- 相同的聚合查询，在列存数据库上可能快两个数量级，因为只需读取`product_id`和`quantity`两列，且列存压缩率高。
典型应用：实时分析、大数据BI报表。
60、使用异步物化视图整合多数据源
场景：需要跨多个数据库或数据源聚合数据。
-- ❌ 应用层分别查询多个库，在内存中合并，延迟高且复杂
result1 = db1.query("SELECT * FROM sales_asia");
result2 = db2.query("SELECT * FROM sales_europe");
merged = merge(result1, result2);
-- ✅ 创建异步物化视图，定期从各数据源拉取数据并聚合
CREATE MATERIALIZED VIEW global_sales AS
SELECT * FROM sales_asia@dblink_asia
UNION ALL
SELECT * FROM sales_europe@dblink_europe
REFRESH EVERY 5 MINUTE; -- 每5分钟全量或增量刷新一次
-- 应用只需查询本地的物化视图
SELECT * FROM global_sales WHERE ...;
典型应用：数据中台、跨地域或跨系统的数据整合报表。
61、位图索引加速多值属性查询
场景：用户拥有多个标签，查询具有某些标签组合的用户。
-- ❌ 在多个布尔列上使用OR条件，索引利用率低
SELECT * FROM users WHERE tag1 = true OR tag2 = true OR tag3 = true;
-- ✅ 使用位图索引（适用于数据仓库，如Oracle、Greenplum等）
-- 假设有一个`tags`字段存储标签集合，或为每个标签建立位图索引
CREATE BITMAP INDEX idx_bitmap_tag1 ON users(tag1);
CREATE BITMAP INDEX idx_bitmap_tag2 ON users(tag2);
CREATE BITMAP INDEX idx_bitmap_tag3 ON users(tag3);
-- 查询时，数据库可以对位图进行快速的AND/OR位运算
SELECT * FROM users WHERE tag1 = 1 AND tag2 = 1; -- 位图快速交集运算
典型应用：用户画像系统中，根据多标签组合圈选人群。
62、向量索引加速相似性搜索
场景：基于图片的特征向量进行相似图片搜索。
-- ❌ 计算每张图片特征向量与目标向量的距离（如余弦相似度），全表扫描
SELECT * FROM images ORDER BY feature_vector <=> '[0.12, 0.34, ...]' LIMIT 10; -- 耗时极长
-- ✅ 创建专门针对向量相似性搜索的索引（如PgVector的IVFFlat、HNSW索引）
CREATE INDEX idx_image_vector ON images USING ivfflat (feature_vector vector_cosine_ops);
-- 使用索引进行近似最近邻搜索，毫秒级返回
SELECT * FROM images ORDER BY feature_vector <=> '[0.12, 0.34, ...]' LIMIT 10;
典型应用：AI内容检索、推荐系统、以图搜图。
63、使用游标键进行分页替代 OFFSET
场景：移动端App无限滚动加载动态列表。
-- ❌ 使用OFFSET进行深分页，性能随着页码增加线性下降
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 100000;
-- ✅ 使用“游标”分页，记录上一页最后一条记录的位置
-- 客户端传递上一页最后一条记录的`created_at`和`id`
SELECT * FROM posts
WHERE (created_at, id) < ('2023-06-01 12:00:00', 12345) -- 上一页最后一条的排序字段值
ORDER BY created_at DESC, id DESC
LIMIT 10; -- 只扫描并返回接下来的10行，效率恒定
典型应用：社交媒体动态流、新闻Feed等需要深分页的场景。
64、使用全局二级索引加速分库分表查询
场景：在按order_id分片的订单表中，按user_id查询其所有订单。
-- ❌ 没有全局二级索引，需要向所有分片广播查询（Scatter）
SELECT * FROM orders WHERE user_id = 123; -- 查询发送到所有分片，然后合并结果
-- ✅ 创建全局二级索引（GSI），将`user_id`到`order_id`的映射单独维护
CREATE GLOBAL INDEX idx_global_user_order ON orders(user_id, order_id);
-- 查询时，先通过GSI找到目标`user_id`对应的所有`order_id`及所在分片，再定向查询。
典型应用：在分布式架构（分库分表）中，对非分片键进行高效查询。需数据库中间件或分布式数据库原生支持。
65、列压缩存储优化
场景：存储海量文本日志内容。
-- ❌ 未压缩存储，占用大量空间，I/O压力大
CREATE TABLE logs (
id BIGINT PRIMARY KEY,
log_content TEXT
);
-- ✅ 使用列式存储并启用压缩，或行存表的页压缩/列压缩
-- 示例：使用列存引擎并指定压缩算法
CREATE TABLE logs (
id BIGINT,
log_content TEXT
) WITH (compression = 'zstd'); -- 使用Zstandard算法压缩
-- 读取时自动解压，显著减少磁盘I/O流量。
典型应用：日志分析系统、文档数据库。
66、使用内存临时表加速中间计算
场景：复杂报表计算中产生大量中间数据。
-- ❌ 默认创建的临时表可能在磁盘上，速度慢
CREATE TEMPORARY TABLE temp_calc (...) ON COMMIT DROP;
-- ✅ 显式指定使用内存引擎创建临时表（如果数据量可控）
CREATE TEMPORARY TABLE temp_calc (...) ENGINE=MEMORY;
-- 后续的中间计算都在内存中进行，速度极快。
-- 注意：MEMORY表不支持TEXT/BLOB，且数据量不能超过内存限制。
典型应用：存储过程或复杂查询中的中间结果暂存。
67、为计算列创建表达式索引
场景：频繁根据年龄范围查询用户，但表中只有出生日期。
-- ❌ 在WHERE子句中计算年龄，索引失效
SELECT * FROM users WHERE EXTRACT(YEAR FROM age(birth_date)) BETWEEN 20 AND 30;
-- ✅ 创建一个存储的生成列“年龄”，并为其建立索引
ALTER TABLE users
ADD COLUMN age_years INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM age(birth_date))) STORED;
CREATE INDEX idx_users_age ON users(age_years);
SELECT * FROM users WHERE age_years BETWEEN 20 AND 30; -- 使用索引
典型应用：基于日期、数值计算结果的频繁查询。
68、分区剪裁优化查询
场景：查询特定月份的设备传感器数据。
-- ❌ 非分区表，即使有条件也需要扫描全年数据
SELECT * FROM sensor_data WHERE log_time >= '2023-06-01' AND log_time < '2023-07-01';
-- ✅ 按月分区的表，查询时会自动“剪裁”掉不相关的分区
CREATE TABLE sensor_data (...) PARTITION BY RANGE (log_time);
-- 假设有分区 p202305, p202306, p202307...
-- 执行上述查询时，优化器知道只扫描 p202306 分区即可。
典型应用：按时间、地域等维度分区的大型事实表查询。
69、连接池配置优化
场景：云原生微服务频繁调用数据库。
# ❌ 默认的小连接池配置，在高并发下成为瓶颈
# application.yml
datasource:
hikari:
maximum-pool-size: 10 # 连接池最大只有10个连接
# ✅ 根据实际负载和数据库连接数限制调整连接池参数
# application.yml
datasource:
hikari:
maximum-pool-size: 50 # 适当调大
minimum-idle: 10 # 维持最小空闲连接，避免新建开销
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
典型应用：任何使用连接池的应用，需要根据压力测试结果进行调优。
70、使用数据库代理简化分片逻辑
场景：应用层直接面对分库分表的复杂性。
// ❌ 应用代码需要解析SQL，计算分片键，选择对应的数据源
int shard = userId % 4; // 分片逻辑硬编码在业务代码中
DataSource ds = getShardDataSource(shard);
Connection conn = ds.getConnection();
// 执行SQL...
// ✅ 使用数据库代理（如ShardingSphere-Proxy, MyCat）
// 应用像连接单一数据库一样连接代理
Connection conn = DriverManager.getConnection("jdbc:mysql://proxy-host:3307/db", ...);
// 执行SQL，代理根据配置的分片规则自动路由
// INSERT INTO orders (user_id, ...) VALUES (123, ...) -> 路由到正确的分片
// SELECT * FROM orders WHERE user_id = 123 -> 可能需要查询多个分片并聚合
典型应用：分库分表架构下，对应用透明化数据分片细节。
71、冷热数据分层存储
场景：近期订单查询频繁，历史订单很少查询。
-- ❌ 所有订单数据都存放在昂贵的SSD上
SELECT * FROM orders WHERE order_date > '2023-01-01'; -- 热数据，快
SELECT * FROM orders WHERE order_date < '2020-01-01'; -- 冷数据，慢且占SSD空间
-- ✅ 配置表的存储策略，自动将冷数据迁移到廉价存储（如HDD或对象存储）
-- 示例：云数据库（如AWS Aurora, PolarDB）或支持表空间的数据库
ALTER TABLE orders SET (
storage_policy = 'HOT:30d COLD:1y ARCHIVE:5y'
);
-- 数据库自动后台迁移数据，查询时自动路由。
典型应用：成本敏感型业务，需要平衡性能和存储成本。
72、Saga模式优化分布式事务
场景：创建订单涉及扣减库存、生成订单记录等多个服务。
-- ❌ 使用传统的两阶段提交（2PC）分布式事务，性能差，锁资源时间长
BEGIN; -- 分布式事务协调器介入
-- 服务A: 在订单库插入订单 (PREPARE)
-- 服务B: 在库存库扣减库存 (PREPARE)
-- 等待所有参与者PREPARE成功
COMMIT; -- 提交所有参与者
-- 任何一个失败则整体回滚
-- ✅ 使用Saga长事务模式，最终一致性
1. 订单服务：本地事务插入订单（状态为“待确认”），发布“OrderCreated”事件。
2. 库存服务：监听事件，本地事务锁定库存，发布“InventoryLocked”事件。
3. 订单服务：监听事件，本地事务更新订单状态为“已确认”。
-- 如果任何一步失败，则触发已执行步骤的补偿操作（如取消订单、释放库存）。
典型应用：微服务架构下的业务流程，避免使用跨服务强一致性事务。
73、多租户数据索引优化
场景：SaaS系统，每个租户数据独立，但存于同一张表。
-- ❌ 在`tenant_id`和业务字段上创建复合索引
CREATE INDEX idx_tenant_product ON all_products (tenant_id, product_name);
-- ✅ 为每个大租户或热门租户创建局部索引（部分索引）
CREATE INDEX idx_tenant1_product ON all_products (product_name) WHERE tenant_id = 1;
CREATE INDEX idx_tenant2_product ON all_products (product_name) WHERE tenant_id = 2;
-- 查询特定租户时，使用更小、更精准的索引。
典型应用：SaaS应用，且不同租户的数据量和查询模式差异大。
74、HTAP混合负载隔离
场景：在线交易（OLTP）和实时分析（OLAP）查询同时进行，互相干扰。
-- ❌ 分析和交易共用同一实例，分析查询占用大量资源，阻塞交易
-- 会话A (OLAP): SELECT COUNT(*), SUM(amount) FROM huge_transaction_table; -- 全表扫描，慢
-- 会话B (OLTP): INSERT INTO orders ...; -- 被阻塞或变慢
-- ✅ 使用专用副本或行列混合存储进行隔离
-- 方案1：读写分离，将OLAP查询路由到专用的只读列存副本。
-- 方案2：使用支持HTAP的数据库（如TiDB），行存处理OLTP，后台自动同步数据到列存引擎供OLAP使用。
SET replica_query = 'columnar'; -- 暗示将查询路由到列存副本
典型应用：需要同时支持高并发交易和实时数据分析的系统，如实时风控、运营大盘。
75、使用资源组进行负载管理
场景：确保核心交易业务的查询性能不受后台报表任务影响。
-- ❌ 所有查询公平竞争资源，一个复杂的报表查询可能耗尽CPU/IO
用户`report_user`执行: SELECT /*+ 复杂聚合 */ ... FROM ...
-- ✅ 创建资源组，限制不同用户或会话的资源使用上限
CREATE RESOURCE GROUP critical WITH (
CPU_RATE_LIMIT = 70, -- 最多使用70%的CPU
MEMORY_LIMIT = 50 -- 最多使用50%的内存
);
CREATE RESOURCE GROUP batch WITH (
CPU_RATE_LIMIT = 30,
MEMORY_LIMIT = 30
);
ALTER USER trade_app SET RESOURCE GROUP critical;
ALTER USER report_app SET RESOURCE GROUP batch;
-- 现在，report_app用户的查询再复杂，也不会挤占trade_app的必需资源。
典型应用：金融、电商等核心业务需要资源保障的系统。
76、使用联邦查询引擎整合数据
场景：需要联合查询位于不同数据库（如MySQL和PostgreSQL）中的数据。
-- ❌ 应用层分别查询，代码复杂，性能依赖网络
result_mysql = mysql_conn.query("SELECT * FROM local_products");
result_pg = pg_conn.query("SELECT stock FROM remote_inventory");
// 在应用内存中JOIN
-- ✅ 使用数据库的联邦查询功能（如MySQL的FEDERATED引擎、PostgreSQL的postgres_fdw）
-- 1. 在本地数据库创建“外表”，映射到远程表
CREATE SERVER remote_inventory FOREIGN DATA WRAPPER mysql ...;
CREATE FOREIGN TABLE remote_inventory (
product_id INT,
stock INT
) SERVER remote_inventory;
-- 2. 直接执行JOIN查询，数据库引擎负责去远程拉取数据
SELECT lp.*, ri.stock
FROM local_products lp
JOIN remote_inventory ri ON lp.id = ri.product_id;
典型应用：数据中台、跨系统数据查询、缓慢变化的维度表同步。
77、AI驱动的自动索引管理
场景：业务查询模式随时间变化，固定索引无法始终保持最优。
-- ❌ DBA手动分析慢查询，创建/删除索引，响应慢
CREATE INDEX idx_fixed ON table (col1, col2);
-- ✅ 使用具备AI能力的数据库或外部工具，自动推荐和管理索引
-- 云数据库（如Azure SQL Database, Alibaba Cloud自治数据库）提供的自动索引优化功能。
ALTER DATABASE mydb SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON);
-- 数据库持续监控工作负载，自动创建有用的索引，并删除未使用的索引。
典型应用：上云业务，或希望降低运维复杂度的场景。
78、向量化用户定义函数
场景：在数据库内执行复杂的风险模型计算。
-- ❌ 标量UDF，对结果集的每一行都调用一次，效率低
CREATE FUNCTION calculate_risk(scalar_data JSON) RETURNS FLOAT ...
SELECT id, calculate_risk(data) AS risk_score FROM loans; -- 逐行调用
-- ✅ 向量化UDF，一次处理一批数据，充分利用现代CPU的SIMD指令
CREATE FUNCTION calculate_risk_vectorized(vector_data JSON[]) RETURNS FLOAT[] ...
-- 数据库将多行数据打包成数组传入UDF，UDF内部使用向量化操作计算，结果以数组返回。
典型应用：在数据库内进行密集计算的机器学习推理或复杂业务逻辑。
79、零拷贝数据导入
场景：每日需要将数TB的Parquet格式数据加载到数据仓库。
-- ❌ 使用INSERT INTO ... SELECT 或 COPY命令，需要解析文件并逐行/批插入
COPY target_table FROM '/path/to/data.parquet' WITH (FORMAT parquet);
-- ✅ 使用外部表或直接挂载数据文件，元数据级别操作
-- 示例1：创建外部表，数据仍在原地
CREATE EXTERNAL TABLE ext_sales (...) LOCATION 's3://bucket/data/'
FILE_FORMAT = (TYPE = PARQUET);
-- 查询时直接读取外部文件
-- 示例2：分区交换或ATTACH（如ClickHouse）
ALTER TABLE target ATTACH PARTITION '2023-01-01' FROM 's3://bucket/data_20230101.parquet';
-- 瞬间完成，因为只是增加了该数据文件的元数据引用。
典型应用：数据湖到数据仓库的ETL，大规模批量数据加载。
80、时态表管理数据历史
场景：需要查询数据在任意历史时间点的状态，用于审计或追溯。
-- ❌ 手动设计历史表，通过触发器或应用双写来维护，复杂且易错
CREATE TABLE orders_history LIKE orders;
-- 通过触发器在orders更新/删除时向history表插入旧版本
-- ✅ 使用数据库原生支持的时态表（系统版本化表）
CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2),
SYS_START TIMESTAMP GENERATED ALWAYS AS ROW START,
SYS_END TIMESTAMP GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SYS_START, SYS_END)
) WITH SYSTEM VERSIONING;
-- 插入、更新、删除操作会自动维护历史。
-- 查询历史：
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2023-01-01 10:00:00';
SELECT * FROM orders FOR SYSTEM_TIME BETWEEN ... AND ...;
典型应用：金融交易审计、法规合规、需求追溯。
81、动态数据脱敏
场景：数据分析师需要查询生产数据以生成报表，但必须隐藏敏感信息（PII）。
-- ❌ 应用层查询完整数据后再脱敏，传输了不必要的数据
SELECT user_id, name, phone_number, email FROM users WHERE region = 'North';
-- 结果集传输到应用，再由应用代码脱敏`phone_number`和`email`
-- ✅ 在数据库层使用动态数据脱敏（DDM），查询时即返回脱敏后的结果
-- 1. 定义脱敏策略（例如，仅DBA角色能看到完整数据）
CREATE MASKING POLICY phone_mask AS (val VARCHAR) RETURNS VARCHAR ->
CASE WHEN current_role() = 'ANALYST' THEN CONCAT('***-***-', SUBSTR(val, -4))
ELSE val
END;
-- 2. 将策略应用到列上
ALTER TABLE users ALTER COLUMN phone_number SET MASKING POLICY phone_mask;
-- 3. 分析师查询时，自动看到脱敏数据
SET ROLE ANALYST;
SELECT phone_number FROM users WHERE id = 1; -- 返回 ‘***-***-5678’
典型应用：在保证数据安全的前提下，支持数据分析、测试数据共享等场景。
82、使用基于成本的优化器提示
场景：优化器因统计信息轻微不准或代价模型偏差，为复杂连接选择了错误的执行计划。
-- ❌ 优化器选择了一个导致严重性能问题的计划（如错误的连接顺序导致循环嵌套连接）
SELECT * FROM large_fact_table f
JOIN large_dimension_table d1 ON f.dim1_id = d1.id
JOIN small_dimension_table d2 ON f.dim2_id = d2.id;
-- ✅ 使用优化器提示（Hints）进行微调。这是最后手段，需充分测试。
-- Oracle示例：指定连接顺序和连接方法
SELECT /*+ LEADING(d2 f d1) USE_NL(f d1) */ *
FROM large_fact_table f
JOIN large_dimension_table d1 ON f.dim1_id = d1.id
JOIN small_dimension_table d2 ON f.dim2_id = d2.id;
-- 提示含义：先用d2表（小）作为驱动表，与f表进行嵌套循环连接(NL)，结果再与d1进行嵌套循环连接。
典型应用：针对特定复杂查询的紧急性能调优，或在版本升级后临时稳定执行计划。
83、使用不可变表或追加优化表
场景：存储事件日志、操作审计流水等只追加、永不更新删除的数据。
-- ❌ 使用常规的堆表或索引组织表，即使没有更新，也需要维护行版本、事务ID等元数据，有开销。
CREATE TABLE event_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMP,
user_id INT,
event_data JSONB
); -- 存在MVCC开销
-- ✅ 使用为追加场景优化的表结构（如AOF——Append-Only File思想在表中的体现）。
-- PostgreSQL示例：使用UNLOGGED表（不写WAL）或调整fillfactor，但更佳是使用列存。
-- Greenplum/DeepGreen示例：使用追加优化(AO)表。
CREATE TABLE event_log (
id BIGINT,
event_time TIMESTAMP,
user_id INT,
event_data JSONB
) WITH (
appendonly = true, -- 只追加
orientation = column, -- 列式存储
compresstype = zstd, -- 压缩
compresslevel = 5
);
-- 写入速度极快，压缩比高，空间占用小。仅支持INSERT和TRUNCATE，不支持UPDATE/DELETE。
典型应用：时序数据、日志存储、事件溯源（Event Sourcing）架构中的事件存储。
总结
SQL性能优化是一个系统性的工程，涵盖了从编写习惯到架构设计的多个层面。面对慢查询，不应盲目地添加索引，而应遵循合理的排查与优化路径：
- 审视需求与查询：首先确认查询是否必要，能否减少数据量或计算复杂度。
- 优化查询语句：检查SQL写法，避免
SELECT *、隐式转换、函数操作索引列等常见陷阱，利用覆盖索引、延迟关联、批处理等技术。
- 设计高效索引：根据查询模式创建合适的索引（B-Tree、哈希、位图、全文、空间索引等），并考虑使用部分索引、表达式索引等高级特性。
- 调整数据库配置与架构：对于更深层次的问题，需要考虑读写分离、分库分表、冷热数据分离、使用列式存储或HTAP数据库等架构级方案。
掌握这83个场景背后的原理，并将其灵活应用于实际系统中，方能从根本上提升数据库性能，构建稳定高效的应用。
发表于 7 天前
|
查看: 20|
回复: 0
