如何写好SQL：企业内训文档

俊才

发布于 2026-03-31 12:38:35

790

培训目标：帮助公司开发、数据分析、运维等相关岗位人员，掌握SQL编写的核心技巧，理解不同数据库（关系型、大数据、分布式）的SQL特性，通过规范写法、合理设计及辅助优化，提升SQL执行效率、降低系统开销，避免常见问题，保障业务系统稳定高效运行。

适用范围：本培训文档适用于公司所有涉及SQL编写、维护、优化的岗位人员，涵盖MySQL、Oracle、SQL SERVER等关系型数据库，OceanBase分布式数据库，以及HIVE、Doris等大数据处理引擎，同时包含Redis缓存等辅助优化手段，内容兼顾理论基础与实战案例，确保可落地、可复用。

(全文较长，建议收藏）

一、基础认知：写好SQL的核心原则

写好SQL的核心不在于“能实现功能”，而在于“高效、规范、可维护、可扩展”。无论针对哪种数据库，均需遵循以下4个核心原则：

高效性：避免全表扫描、冗余计算，减少IO开销，确保SQL执行速度满足业务需求（尤其是高频查询、大数据量场景）；
规范性：统一命名、缩进、注释，便于团队协作维护，降低后续迭代成本；
安全性：避免SQL注入、权限泄露，限制高危操作（如无条件delete、update）；
兼容性：结合公司现有数据库环境，兼顾不同数据库的语法差异，减少跨库迁移的修改成本。

注：本文档中所有案例均结合公司常见业务场景（如用户管理、订单统计、日志分析），确保贴合实际工作，可直接参考复用。新增OceanBase相关内容，重点突出其分布式特性，适配公司分布式业务场景需求。

二、底层基石：表设计优化（SQL高效的前提）

表设计是SQL优化的“源头”，不合理的表设计会导致后续SQL优化事倍功半。核心原则：贴合业务、遵循范式、减少冗余、适配存储引擎/引擎特性。以下分关系型数据库、分布式数据库（OceanBase）、大数据引擎分别说明，结合案例对比优化前后的差异。

2.1 关系型数据库表设计（MySQL、Oracle、SQL SERVER）

2.1.1 核心理论

1）遵循三大范式（核心是减少数据冗余）

第一范式（1NF）：列不可再分，确保每一列都是原子值（如“联系方式”拆分为“手机号”“邮箱”，而非存储为“138xxxx1234;xxx@xxx.com”）
第二范式（2NF）：在1NF基础上，消除部分函数依赖，确保每一列都完全依赖于主键（而非依赖主键的一部分）
第三范式（3NF）：在2NF基础上，消除传递依赖，确保非主键列不依赖于其他非主键列（如“订单表”不存储“用户姓名”，而是通过“用户ID”关联“用户表”查询）

2）特殊场景可适当反范式（以空间换时间）：高频查询场景中，可允许少量冗余，减少多表关联，提升查询效率（如订单列表页需展示用户姓名，可在订单表冗余“用户姓名”字段，避免每次关联用户表）。

3）数据类型选择原则：优先选择“最小且合适”的数据类型，避免占用过多存储空间，同时提升查询效率（如存储手机号用CHAR(11)而非VARCHAR(20)，存储日期用DATE/DATETIME而非VARCHAR）。

4）主键与外键设计：主键需唯一、非空，优先使用自增ID（MySQL）、序列（Oracle）、IDENTITY（SQL SERVER）；外键用于关联表，确保数据一致性，但高频写入场景可适当省略（通过业务逻辑保证一致性），避免外键约束带来的性能开销。

2.1.2 分数据库案例

案例1：MySQL表设计（用户表+订单表）

优化前（反范式，冗余严重）：

-- 订单表（冗余用户姓名、手机号，违反3NF）
CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键）',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `user_name` varchar(50) NOT NULL COMMENT '用户姓名（冗余）',
  `user_phone` char(11) NOT NULL COMMENT '用户手机号（冗余）',
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

优化后（遵循3NF，减少冗余，适配InnoDB引擎）：

-- 1. 用户表（存储用户核心信息）
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID（主键）',
  `user_name` varchar(50) NOT NULL COMMENT '用户姓名',
  `user_phone` char(11) NOT NULL COMMENT '用户手机号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_user_phone` (`user_phone`) -- 手机号唯一索引，避免重复注册
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 2. 订单表（通过user_id关联用户表，无冗余）
CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键）',
  `user_id` int(11) NOT NULL COMMENT '用户ID（外键，关联user表）',
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`) -- 关联查询索引，提升效率
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
-- 关联查询用户订单信息（通过JOIN实现，无冗余）
SELECT o.order_id, o.order_amount, o.order_time, u.user_name, u.user_phone
FROM `order` o
LEFT JOIN `user` u ON o.user_id = u.user_id
WHERE o.order_time > '2024-01-01';

案例2：Oracle表设计（产品表，适配Oracle特性）

-- Oracle中使用序列生成主键，数据类型适配Oracle特性
-- 1. 产品序列（用于生成产品ID）
CREATE SEQUENCE seq_product_id
START WITH 1 -- 起始值
INCREMENT BY 1 -- 步长
NOCACHE -- 不缓存序列（避免缓存丢失导致主键断层）
NOCYCLE; -- 不循环
-- 2. 产品表（遵循范式，适配Oracle数据类型）
CREATE TABLE product (
  product_id NUMBER(11) NOT NULL COMMENT '产品ID（主键）',
  product_name VARCHAR2(100) NOT NULL COMMENT '产品名称',
  product_price NUMBER(10,2) NOT NULL COMMENT '产品价格（Oracle用NUMBER替代MySQL的decimal）',
  product_status CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态（1-正常，0-下架）',
  create_time DATE NOT NULL DEFAULT SYSDATE COMMENT '创建时间（Oracle用DATE存储日期）',
  PRIMARY KEY (product_id)
) COMMENT '产品表';
-- 插入数据（使用序列生成主键）
INSERT INTO product (product_id, product_name, product_price)
VALUES (seq_product_id.NEXTVAL, '手机', 3999.00);

案例3：SQL SERVER表设计（订单详情表，含分区表优化）

场景：订单详情表数据量巨大（千万级），按日期分区存储，提升查询效率（SQL SERVER分区表特性）。

-- 1. 创建分区函数（按订单日期分区，每月一个分区）
CREATE PARTITION FUNCTION pf_order_detail_date (DATETIME)
AS RANGE RIGHT FOR VALUES (
  '2024-02-01', '2024-03-01', '2024-04-01',
  -- 后续月份可依次添加
  '2025-01-01'
);
-- 2. 创建分区方案（指定分区存储位置）
CREATE PARTITION SCHEME ps_order_detail_date
AS PARTITION pf_order_detail_date
ALL TO ([PRIMARY]); -- 所有分区存储在PRIMARY文件组（可按需指定不同文件组）
-- 3. 创建订单详情表（分区表，适配SQL SERVER特性）
CREATE TABLE order_detail (
  detail_id INT IDENTITY(1,1) NOT NULL COMMENT '详情ID（主键）',
  order_id INT NOT NULL COMMENT '订单ID',
  product_id INT NOT NULL COMMENT '产品ID',
  quantity INT NOT NULL COMMENT '购买数量',
  detail_price DECIMAL(10,2) NOT NULL COMMENT '详情单价',
  create_time DATETIME NOT NULL COMMENT '创建时间（分区列）',
  PRIMARY KEY (detail_id, create_time) -- 主键包含分区列，提升分区查询效率
) ON ps_order_detail_date(create_time) -- 按create_time分区
COMMENT '订单详情表';
-- 说明：查询2024年3月的订单详情时，仅扫描对应分区，避免全表扫描
SELECT * FROM order_detail WHERE create_time BETWEEN '2024-03-01' AND '2024-03-31';

2.2 分布式数据库表设计（OceanBase）

2.2.1 核心理论

OceanBase是分布式关系型数据库，核心特性是“分布式架构、高可用、高并发、海量存储”，表设计的核心是适配分布式分区、减少跨分区操作、兼顾数据均衡与查询效率，其表设计既遵循关系型数据库的范式原则，又需结合分布式特性做特殊优化。

分区设计（核心）：OceanBase支持水平分区（范围分区、哈希分区、列表分区）和垂直分区，优先使用水平分区，将数据分散到不同节点，实现负载均衡；分区键选择是关键，需选择高频查询的字段（如订单日期、用户ID），避免跨分区查询（会增加网络开销）。
数据类型：与MySQL高度兼容，优先选择最小且合适的数据类型，同时支持OceanBase特有类型（如OB_JSON、OB_TIMESTAMP），适配JSON数据存储、高精度时间场景。
主键设计：支持自增主键（AUTO_INCREMENT）、序列主键（SEQUENCE）、复合主键，分布式场景下优先使用自增主键或全局序列，确保主键唯一且分布均匀，避免分区倾斜；不建议使用UUID作为主键（会导致分区不均、查询效率低）。
表类型：支持普通表、分区表、临时表、外部表，业务场景中优先使用分区表；支持本地索引和全局索引，本地索引与分区一一对应，查询效率高，优先使用。

2.2.2 OceanBase实战案例

案例1：OceanBase分区表设计（订单表，范围分区+本地索引）

场景：订单表数据量巨大（亿级），高频按日期查询，采用范围分区，适配分布式架构，减少跨分区操作。

-- 1. 创建订单表（范围分区，按订单日期分区，每月一个分区）
CREATE TABLE `order` (
  `order_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键，自增）',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  `order_time` DATETIME NOT NULL COMMENT '下单时间（分区键）',
  `order_status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态（0-待支付，1-已支付）',
  PRIMARY KEY (`order_id`) LOCAL, -- 本地主键索引（与分区一一对应）
  KEY `idx_user_id_order_time` (`user_id`, `order_time`) LOCAL -- 本地联合索引
) 
PARTITION BY RANGE (TO_DAYS(order_time)) ( -- 按日期范围分区，TO_DAYS转换为天数便于分区
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
  PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
  PARTITION p_max VALUES LESS THAN MAXVALUE -- 兜底分区，避免数据无法插入
) 
COMMENT '订单表（OceanBase分区表）';
-- 2. 插入数据（自增主键，自动分配到对应分区）
INSERT INTO `order` (user_id, order_amount, order_time, order_status)
VALUES 
(10001, 199.00, '2024-01-15 10:30:00', 1),
(10002, 3999.00, '2024-02-20 14:45:00', 0);
-- 3. 查询优化：仅扫描对应分区，避免跨分区查询
-- 查询2024年1月的订单，仅扫描p202401分区
SELECT order_id, order_amount, order_status
FROM `order`
WHERE order_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
-- 避坑：避免跨分区查询（如下查询会扫描所有分区，效率低）
SELECT order_id, order_amount
FROM `order`
WHERE user_id = 10001; -- 未指定分区键，跨分区扫描
-- 优化：添加分区键条件，或调整索引（将user_id作为分区键，根据业务调整）

案例2：OceanBase哈希分区+全局索引（用户表）

场景：用户表数据量千万级，高频按用户ID查询，采用哈希分区（均匀分布数据），搭配全局索引（适配跨分区查询场景）。

-- 1. 创建用户表（哈希分区，按user_id分区，分散数据到不同节点）
CREATE TABLE `user` (
  `user_id` BIGINT NOT NULL COMMENT '用户ID（主键）',
  `user_name` VARCHAR(50) NOT NULL COMMENT '用户姓名',
  `user_phone` CHAR(11) NOT NULL COMMENT '用户手机号',
  `region` VARCHAR(20) NOT NULL COMMENT '用户地区',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`) LOCAL, -- 本地主键索引
  UNIQUE KEY `uk_user_phone` (`user_phone`) GLOBAL, -- 全局唯一索引（跨分区查询手机号）
  KEY `idx_region` (`region`) LOCAL -- 本地索引（地区查询，无需跨分区）
)
PARTITION BY HASH(user_id) PARTITIONS 16; -- 哈希分区，16个分区，均匀分布数据
-- 2. 插入数据（按user_id哈希分配到不同分区）
INSERT INTO `user` (user_id, user_name, user_phone, region)
VALUES 
(10001, '张三', '138xxxx1234', '北京'),
(10002, '李四', '139xxxx5678', '上海'),
(10003, '王五', '137xxxx9012', '广州');
-- 3. 索引查询适配
-- 命中本地主键索引，高效查询（无需跨分区）
SELECT user_name, region FROM `user` WHERE user_id = 10001;
-- 命中全局唯一索引，跨分区查询手机号（效率略低于本地索引，但满足业务需求）
SELECT user_id, user_name FROM `user` WHERE user_phone = '138xxxx1234';
-- 命中本地索引，查询某地区用户（无需跨分区）
SELECT user_id, user_name FROM `user` WHERE region = '北京';

案例3：OceanBase JSON类型与外部表（适配多格式数据）

场景：存储用户扩展信息（JSON格式），同时需要关联HDFS上的用户行为日志（外部表），适配多源数据查询场景。

-- 1. 创建用户扩展表（使用OceanBase特有OB_JSON类型）
CREATE TABLE user_extend (
  user_id BIGINT NOT NULL COMMENT '用户ID（主键）',
  extend_info OB_JSON NOT NULL COMMENT '用户扩展信息（JSON格式）',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id) LOCAL
)
PARTITION BY HASH(user_id) PARTITIONS 8;
-- 插入JSON数据
INSERT INTO user_extend (user_id, extend_info)
VALUES (10001, '{"age": 25, "gender": "male", "hobby": ["reading", "sports"]}');
-- JSON查询（适配OceanBase JSON函数）
SELECT 
  user_id,
  extend_info->'$.age' AS age, -- 提取JSON中的age字段
  extend_info->'$.gender' AS gender
FROM user_extend
WHERE user_id = 10001;
-- 2. 创建外部表（关联HDFS上的用户行为日志，适配多源数据）
CREATE EXTERNAL TABLE user_behavior_external (
  user_id BIGINT,
  behavior_type VARCHAR(20),
  behavior_time DATETIME,
  product_id BIGINT
)
ENGINE=HDFS -- 外部表引擎（HDFS）
LOCATION 'hdfs://xxx:9000/user/oceanbase/user_behavior' -- HDFS路径
FORMAT 'TEXT' -- 数据格式
FIELDS TERMINATED BY '\t' -- 字段分隔符
COMMENT 'OceanBase外部表（关联HDFS用户行为日志）';
-- 关联查询（本地表+外部表）
SELECT u.user_id, u.user_name, b.behavior_type
FROM `user` u
LEFT JOIN user_behavior_external b ON u.user_id = b.user_id
WHERE b.behavior_time > '2024-01-01';

2.3 大数据引擎表设计（HIVE、Doris）

2.3.1 核心理论

大数据场景的核心需求是“处理海量数据（TB/PB级）”，表设计的核心的是减少数据扫描范围、提升并行处理效率，与关系型数据库的范式原则有差异，更注重“分而治之”。

HIVE：基于HDFS存储，表设计核心是“分区+分桶”，分区用于粗粒度筛选（如按日期、地区），分桶用于细粒度定位（如按用户ID），减少全表扫描；支持外部表（数据存储在HDFS指定路径，不被HIVE管理）和管理表（数据由HIVE管理），优先使用外部表，避免误删数据。
Doris：MPP架构，表设计核心是“分区+分桶+数据模型”，支持DUPLICATE KEY、UNIQUE KEY、AGGREGATE KEY三种数据模型，适配不同查询场景；内置前缀索引、ZoneMap索引，无需手动创建过多索引，重点通过表结构优化提升效率，单个分桶大小建议控制在1-10G。

2.3.2 分引擎案例

案例1：HIVE表设计（用户行为日志表，分区+分桶）

场景：用户行为日志（每日千万条），需按日期查询，同时需关联用户ID统计，采用分区+分桶设计。

-- 1. 创建外部表（分区+分桶，适配HIVE特性）
CREATE EXTERNAL TABLE user_behavior (
  user_id STRING COMMENT '用户ID',
  behavior_type STRING COMMENT '行为类型（click/collect/buy）',
  product_id STRING COMMENT '产品ID',
  behavior_time STRING COMMENT '行为时间（yyyy-MM-dd HH:mm:ss）',
  region STRING COMMENT '用户地区'
)
PARTITIONED BY (dt STRING COMMENT '日期分区（yyyy-MM-dd）') -- 按日期分区，粗粒度筛选
CLUSTERED BY (user_id) INTO 32 BUCKETS -- 按user_id分桶，细粒度定位，分桶数按需调整
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- 字段分隔符
LOCATION '/user/hive/warehouse/user_behavior' -- HDFS存储路径
COMMENT '用户行为日志表';
-- 2. 加载数据（动态分区，自动按dt分区）
SET hive.exec.dynamic.partition=true; -- 开启动态分区
SET hive.exec.dynamic.partition.mode=nonstrict; -- 允许全动态分区
INSERT OVERWRITE TABLE user_behavior PARTITION(dt)
SELECT user_id, behavior_type, product_id, behavior_time, region, 
       substr(behavior_time, 1, 10) AS dt -- 提取日期作为分区列
FROM user_behavior_raw; -- 原始日志表
-- 3. 查询优化：仅扫描2024-01-01分区，且按user_id分桶定位，避免全表扫描
SELECT behavior_type, COUNT(*) AS count
FROM user_behavior
WHERE dt = '2024-01-01' AND user_id = '10001'
GROUP BY behavior_type;

补充：HIVE动态分区易产生小文件，可通过参数设置避免，如开启自动合并小文件：

SET hive.merge.mapfiles = true; -- 合并Map任务输出
SET hive.merge.size.per.task = 268435456; -- 合并后文件大小设为256MB
SET hive.merge.smallfiles.avgsize = 134217728; -- 平均文件<128MB触发合并

案例2：Doris表设计（销售统计表，AGGREGATE KEY模型）

场景：销售数据统计（按日期、地区、产品聚合），采用AGGREGATE KEY模型，自动聚合重复数据，提升查询效率，结合前缀索引优化。

-- 创建AGGREGATE KEY表（自动聚合，减少数据量）
CREATE TABLE sales_stat (
  dt STRING COMMENT '日期（yyyy-MM-dd）',
  region STRING COMMENT '地区',
  product_id STRING COMMENT '产品ID',
  sales_amount DECIMAL(12,2) SUM COMMENT '销售金额（聚合方式：求和）',
  sales_count INT SUM COMMENT '销售数量（聚合方式：求和）',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)
ENGINE=OLAP
AGGREGATE KEY(dt, region, product_id) -- 聚合键，相同组合自动聚合
PARTITION BY RANGE(dt) ( -- 按日期分区
  PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
  PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
  PARTITION p202403 VALUES LESS THAN ('2024-04-01')
)
DISTRIBUTED BY HASH(product_id) BUCKETS 16 -- 按product_id分桶，16个桶
COMMENT '销售统计表';
-- 插入数据（重复数据自动聚合）
INSERT INTO sales_stat (dt, region, product_id, sales_amount, sales_count)
VALUES 
('2024-01-01', '北京', 'p001', 1000.00, 10),
('2024-01-01', '北京', 'p001', 500.00, 5); -- 与上一条聚合，最终sales_amount=1500.00，sales_count=15
-- 查询优化：利用前缀索引（dt、region、product_id为聚合键，即前缀索引），无需额外创建索引
SELECT dt, region, SUM(sales_amount) AS total_amount
FROM sales_stat
WHERE dt BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY dt, region;

补充：Doris前缀索引默认取前36个字节，建表时需将常用查询字段放在前面，若常用字段无法放入前缀索引，可创建物化视图调整列顺序，或创建二级索引（如Bitmap索引）优化，例如为region字段创建Bitmap索引：

CREATE INDEX idx_region ON sales_stat (region) USING BITMAP COMMENT 'region字段Bitmap索引';

三、性能核心：索引优化（SQL提速的关键）

索引是“数据的目录”，核心作用是减少数据扫描范围，提升查询效率，但索引并非越多越好——过多索引会增加写入、更新、删除的开销（每次操作需维护索引）。核心原则：按需创建、覆盖常用查询、避免无效索引。

以下分关系型数据库、分布式数据库（OceanBase）、大数据引擎，结合案例说明索引的创建、使用及避坑点。

3.1 关系型数据库索引（MySQL、Oracle、SQL SERVER）

3.1.1 核心理论

索引类型：主键索引（唯一、非空，默认创建）、唯一索引（唯一，可空）、普通索引（无约束）、联合索引（多列组合，遵循“最左前缀原则”）、聚簇索引（MySQL InnoDB主键默认聚簇索引，数据与索引存储在一起；Oracle、SQL SERVER聚簇索引需手动创建）。
最左前缀原则：联合索引（a,b,c），仅能匹配“a”“a,b”“a,b,c”的查询条件，无法匹配“b”“b,c”的查询条件，创建时需将查询频率最高的列放在前面。
索引失效场景：函数操作（如WHERE SUBSTR(name,1,1)='张'）、隐式转换（如字符串字段与数字比较）、模糊查询（如WHERE name LIKE '%张'）、OR条件（未全部命中索引）、NOT IN/!=（部分数据库失效）。

3.1.2 分数据库案例

案例1：MySQL索引优化（订单表，联合索引+覆盖索引）

场景：高频查询“某用户某时间段的订单”，优化索引设计，避免全表扫描。

-- 原有索引（仅主键索引，查询全表扫描）
CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  PRIMARY KEY (`order_id`)
);
-- 高频查询SQL（全表扫描，效率低）
SELECT order_id, order_amount, order_time FROM `order` 
WHERE user_id = 1001 AND order_time > '2024-01-01';

优化方案：创建联合索引（user_id, order_time），遵循最左前缀原则，同时实现覆盖索引

覆盖索引：索引包含查询所需所有字段，无需回表查询数据

CREATE INDEX idx_user_order_time ON `order` (user_id, order_time, order_amount);

优化后查询：命中联合索引，无需全表扫描，效率提升10倍以上

SELECT order_id, order_amount, order_time FROM `order` 
WHERE user_id = 1001 AND order_time > '2024-01-01';

索引失效案例（避坑）

1）函数操作：order_time上的函数导致索引失效

SELECT * FROM `order` WHERE DATE(order_time) = '2024-01-01'; -- 失效

优化：改写SQL，避免函数操作

SELECT * FROM `order` WHERE order_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'; -- 命中索引

2）隐式转换：user_id是int类型，传入字符串导致索引失效

SELECT * FROM `order` WHERE user_id = '1001'; -- 失效（隐式转换：字符串→int）
-- 优化：传入正确数据类型
SELECT * FROM `order` WHERE user_id = 1001; -- 命中索引

案例2：Oracle索引优化（产品表，唯一索引+函数索引）

场景：产品表需查询“产品名称模糊匹配”“产品状态为正常”的记录，优化索引避免全表扫描，结合Oracle函数索引特性。

-- 产品表（原有索引：主键索引）
CREATE TABLE product (
  product_id NUMBER(11) NOT NULL PRIMARY KEY,
  product_name VARCHAR2(100) NOT NULL,
  product_status CHAR(1) NOT NULL DEFAULT '1',
  product_price NUMBER(10,2) NOT NULL
);
-- 高频查询SQL（模糊查询，全表扫描）
SELECT product_id, product_name FROM product 
WHERE product_status = '1' AND product_name LIKE '手机%';
-- 优化方案1：创建联合索引（product_status, product_name），适配模糊查询（前缀匹配）
CREATE INDEX idx_prod_status_name ON product (product_status, product_name);
-- 优化后查询：命中索引，效率提升
SELECT product_id, product_name FROM product 
WHERE product_status = '1' AND product_name LIKE '手机%';

优化方案2：若需后缀匹配（如LIKE '%手机'），创建函数索引（Oracle支持）

CREATE INDEX idx_prod_name_reverse ON product (REVERSE(product_name));
-- 改写SQL，适配函数索引
SELECT product_id, product_name FROM product 
WHERE product_status = '1' AND REVERSE(product_name) LIKE REVERSE('%手机');
-- 补充：Oracle统计信息过时会导致索引失效，需定期更新
UPDATE STATISTICS product;

案例3：SQL SERVER索引优化（订单详情表，聚簇索引+非聚簇索引）

场景：订单详情表数据量巨大，高频查询“某订单的所有详情”，优化聚簇索引与非聚簇索引的搭配。

-- 订单详情表（原有索引：非聚簇主键索引，效率低）
CREATE TABLE order_detail (
  detail_id INT IDENTITY(1,1) NOT NULL,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY NONCLUSTERED (detail_id) -- 非聚簇主键索引
);
-- 高频查询SQL（全表扫描，效率低）
SELECT * FROM order_detail WHERE order_id = 10001;
-- 优化方案：1. 创建聚簇索引（order_id），数据与索引存储在一起，提升查询效率
CREATE CLUSTERED INDEX idx_clu_order_id ON order_detail (order_id);
-- 2. 创建非聚簇索引（覆盖索引），包含查询所需字段，避免回表
CREATE NONCLUSTERED INDEX idx_prod_quantity ON order_detail (product_id)
INCLUDE (quantity); -- 包含quantity字段，实现覆盖索引
-- 优化后查询：命中聚簇索引，无需全表扫描
SELECT * FROM order_detail WHERE order_id = 10001;
-- 索引失效避坑：LIKE通配符开头导致索引失效
SELECT * FROM order_detail WHERE product_id LIKE '%100'; -- 失效
-- 优化：使用全文索引（SQL SERVER支持）
EXEC sp_fulltext_database 'enable'; -- 启用全文索引
CREATE FULLTEXT CATALOG prod_catalog; -- 创建全文索引目录
CREATE FULLTEXT INDEX ON order_detail(product_id) KEY INDEX idx_prod_quantity ON prod_catalog;
-- 改写查询
SELECT * FROM order_detail WHERE CONTAINS(product_id, '100');

补充：SQL Server索引碎片化会导致性能下降，需定期重建或重组索引：

-- 重建索引（碎片化程度高时）
ALTER INDEX idx_clu_order_id ON order_detail REBUILD;
-- 重组索引（碎片化程度低时）
ALTER INDEX idx_clu_order_id ON order_detail REORGANIZE;

3.2 分布式数据库索引（OceanBase）

3.2.1 核心理论

OceanBase的索引设计基于分布式架构，核心是“本地索引优先、全局索引按需使用”，避免跨分区索引操作，减少网络开销。与关系型数据库索引相比，有以下核心特性：

索引类型：支持本地索引（LOCAL）和全局索引（GLOBAL），本地索引与分区一一对应，查询时仅扫描对应分区的索引，效率高，优先使用；全局索引跨所有分区，适用于跨分区查询场景（如按手机号查询用户），但维护成本高，写入开销大。
联合索引：遵循“最左前缀原则”，与MySQL一致，但需注意分区键的位置——若联合索引包含分区键，查询时可快速定位分区，提升效率；若不包含分区键，会触发跨分区扫描。
索引失效场景：与MySQL基本一致，包括函数操作、隐式转换、模糊查询（%开头）、OR条件未全部命中索引等；额外注意：跨分区查询时，全局索引若未命中，会导致全表扫描，效率极低。
特殊索引：支持Bitmap索引（适用于低基数列，如订单状态、地区）、全文索引（适用于模糊查询场景），适配不同业务需求。

3.2.2 OceanBase索引优化案例

案例1：OceanBase本地联合索引优化（订单表，避免跨分区查询）

场景：订单表按order_time范围分区，高频查询“某用户某时间段的订单”，优化本地联合索引，避免跨分区扫描。

-- 订单表（已按order_time范围分区）
CREATE TABLE `order` (
  `order_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  `order_time` DATETIME NOT NULL COMMENT '下单时间（分区键）',
  `order_status` TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`order_id`) LOCAL
);
-- 高频查询SQL（未创建合适索引，跨分区扫描，效率低）
SELECT order_id, order_amount, order_status
FROM `order`
WHERE user_id = 10001 AND order_time BETWEEN '2024-01-01' AND '2024-01-31';
-- 优化方案：创建本地联合索引（user_id, order_time），包含分区键，避免跨分区
CREATE INDEX idx_user_order_time ON `order` (user_id, order_time) LOCAL;
-- 优化后查询：命中本地联合索引，仅扫描p202401分区，效率提升显著
SELECT order_id, order_amount, order_status
FROM `order`
WHERE user_id = 10001 AND order_time BETWEEN '2024-01-01' AND '2024-01-31';

避坑：联合索引未包含分区键，导致跨分区扫描

-- 错误索引（未包含order_time，分区键）
CREATE INDEX idx_user_id ON `order` (user_id) LOCAL;
-- 查询会扫描所有分区，效率低
SELECT * FROM `order` WHERE user_id = 10001;

案例2：OceanBase全局索引与Bitmap索引优化（用户表+订单表）

场景1：用户表高频按手机号查询（跨分区），创建全局唯一索引；场景2：订单表高频按订单状态查询（低基数列），创建Bitmap索引。

-- 1. 用户表（哈希分区，按user_id分区）
CREATE TABLE `user` (
  `user_id` BIGINT NOT NULL PRIMARY KEY,
  `user_name` VARCHAR(50) NOT NULL,
  `user_phone` CHAR(11) NOT NULL,
  `region` VARCHAR(20) NOT NULL
)
PARTITION BY HASH(user_id) PARTITIONS 16;
-- 优化：创建全局唯一索引，适配跨分区手机号查询
CREATE UNIQUE INDEX uk_user_phone_global ON `user` (user_phone) GLOBAL;
-- 查询：命中全局唯一索引，跨分区查询高效
SELECT user_id, user_name FROM `user` WHERE user_phone = '138xxxx1234';
-- 2. 订单表（范围分区，按order_time分区）
CREATE TABLE `order` (
  `order_id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT NOT NULL,
  `order_amount` DECIMAL(10,2) NOT NULL,
  `order_time` DATETIME NOT NULL,
  `order_status` TINYINT NOT NULL DEFAULT 0 -- 低基数列（0-待支付，1-已支付，2-已取消）
)
PARTITION BY RANGE (TO_DAYS(order_time)) PARTITIONS 12;
-- 优化：创建Bitmap索引，适配低基数列查询
CREATE INDEX idx_order_status_bitmap ON `order` (order_status) USING BITMAP LOCAL;
-- 查询：命中Bitmap索引，快速筛选订单状态为1的记录
SELECT order_id, user_id, order_amount
FROM `order`
WHERE order_status = 1 AND order_time > '2024-01-01';

案例3：OceanBase索引失效避坑（函数操作、隐式转换）

-- 订单表（已创建本地联合索引idx_user_order_time (user_id, order_time)）
-- 1. 函数操作导致索引失效
SELECT * FROM `order` WHERE DATE(order_time) = '2024-01-01'; -- 失效，跨分区扫描
-- 优化：改写SQL，避免函数操作
SELECT * FROM `order` WHERE order_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'; -- 命中索引
-- 2. 隐式转换导致索引失效（user_id是BIGINT，传入字符串）
SELECT * FROM `order` WHERE user_id = '10001'; -- 失效，隐式转换：字符串→BIGINT
-- 优化：传入正确数据类型
SELECT * FROM `order` WHERE user_id = 10001; -- 命中索引
-- 3. 模糊查询（%开头）导致索引失效
SELECT * FROM `order` WHERE user_name LIKE '%张三'; -- 失效（假设user_name有索引）
-- 优化：使用全文索引
CREATE FULLTEXT INDEX ft_idx_user_name ON `user` (user_name) LOCAL;
SELECT * FROM `user` WHERE MATCH(user_name) AGAINST('张三');

3.3 大数据引擎索引（HIVE、Doris）

3.3.1 核心理论

HIVE：不推荐创建传统索引（Hive 3.0后已废弃），核心依赖“分区+分桶”实现优化，替代传统索引的作用；若需精准定位，可使用Bucketing Join（分桶连接）减少数据shuffle。
Doris：内置索引（前缀索引、ZoneMap索引），无需手动创建；支持二级索引（Bitmap索引、BloomFilter索引、倒排索引），按需创建——Bitmap索引适用于低基数列（如地区、状态），BloomFilter索引适用于高基数列（如身份证号），倒排索引适用于无法命中前缀索引的场景。

3.3.2 分引擎案例

案例1：HIVE索引替代方案（分桶连接优化）

场景：用户表（小表）与用户行为表（大表）关联查询，使用分桶连接替代索引，提升效率。

-- 1. 用户表（小表，按user_id分桶）
CREATE TABLE user_info (
  user_id STRING,
  user_name STRING,
  region STRING
)
CLUSTERED BY (user_id) INTO 8 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 2. 用户行为表（大表，按user_id分桶，分桶数与小表一致或为其整数倍）
CREATE TABLE user_behavior (
  user_id STRING,
  behavior_type STRING,
  behavior_time STRING
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 8 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 3. 分桶连接查询（替代索引，减少shuffle，提升效率）
SET hive.auto.convert.join=true; -- 自动开启分桶连接
SELECT u.user_id, u.user_name, b.behavior_type
FROM user_info u
JOIN user_behavior b ON u.user_id = b.user_id
WHERE b.dt = '2024-01-01';

说明：分桶连接时，相同user_id的数据在同一个桶中，无需全量shuffle，效率远超普通JOIN

案例2：Doris二级索引优化（Bitmap索引+ BloomFilter索引）

场景：销售统计表，高频查询“某地区的销售数据”（region为低基数列）和“某产品的销售数据”（product_id为高基数列），创建对应二级索引。

-- 销售统计表（原有前缀索引：dt, region, product_id）
CREATE TABLE sales_stat (
  dt STRING,
  region STRING,
  product_id STRING,
  sales_amount DECIMAL(12,2) SUM,
  sales_count INT SUM
)
ENGINE=OLAP
AGGREGATE KEY(dt, region, product_id)
PARTITION BY RANGE(dt) (
  PARTITION p202401 VALUES LESS THAN ('2024-02-01')
)
DISTRIBUTED BY HASH(product_id) BUCKETS 16;

优化方案1：为region（低基数列）创建Bitmap索引，提升等值/范围查询效率

CREATE INDEX idx_region_bitmap ON sales_stat (region) USING BITMAP COMMENT 'region Bitmap索引';
-- 查询：命中Bitmap索引，快速筛选地区数据
SELECT dt, SUM(sales_amount) FROM sales_stat WHERE region = '北京' GROUP BY dt;

优化方案2：为product_id（高基数列）创建BloomFilter索引，提升等值查询效率

CREATE INDEX idx_prod_bloom ON sales_stat (product_id) USING BLOOMFILTER COMMENT 'product_id BloomFilter索引';
-- 查询：命中BloomFilter索引，快速筛选产品数据
SELECT dt, region, sales_count FROM sales_stat WHERE product_id = 'p001';

注意：Doris Bitmap索引仅支持单列，适用于基数在100-100000之间的列，基数过高或过低均不适合；BloomFilter索引适用于高基数列的等值查询，不支持范围查询。

四、规范与高效：SQL写法优化（避免常见坑）

相同功能的SQL，不同写法的执行效率可能相差10倍甚至100倍。核心原则：简化查询、减少冗余、避免无效计算、适配数据库特性。以下分“通用规范”“分数据库写法差异”“常见坑与优化案例”三部分说明。

4.1 SQL通用规范（所有数据库适用）

命名规范：表名、字段名、索引名统一使用小写，用下划线分隔（如user_info、idx_user_id），避免使用关键字（如order、table，若必须使用需加反引号/双引号）；
缩进与注释：SQL语句按逻辑缩进，关键操作（如CREATE、SELECT、WHERE）换行，添加注释（-- 单行注释、/* 多行注释 */），说明业务含义，便于维护；
避免SELECT *：仅查询所需字段，减少数据传输开销，同时便于使用覆盖索引；
合理使用JOIN：优先使用INNER JOIN（内连接），避免不必要的LEFT JOIN（左连接）；多表JOIN时，将小表放在前面（部分数据库优化器会自动调整，但规范写法更稳妥）；
避免嵌套过深：子查询嵌套不超过3层，复杂查询可拆分为临时表或CTE（公共表表达式），提升可读性和执行效率；
批量操作：批量插入、更新、删除数据，避免单条操作（如MySQL用INSERT INTO ... VALUES (...),(...)；Oracle用INSERT ALL）；
避免NULL值判断：尽量用IS NULL/IS NOT NULL，避免用=、!=判断NULL（NULL与任何值比较都为NULL）。

4.2 分数据库SQL写法差异（核心案例）

4.2.1 分页查询（高频场景）

-- 1. MySQL：使用LIMIT
SELECT order_id, order_amount FROM `order` 
WHERE user_id = 1001 
ORDER BY order_time DESC 
LIMIT 10 OFFSET 20; -- 跳过20条，取10条（分页：第3页，每页10条）
-- 2. Oracle：使用ROWNUM（12c以下）或OFFSET FETCH（12c+）
-- 12c以下
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT order_id, order_amount FROM `order` 
    WHERE user_id = 1001 
    ORDER BY order_time DESC
  ) t WHERE ROWNUM <= 30 -- 取前30条
) WHERE rn > 20; -- 跳过20条，取10条
-- 12c+（与MySQL类似）
SELECT order_id, order_amount FROM `order` 
WHERE user_id = 1001 
ORDER BY order_time DESC 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 3. SQL SERVER：使用TOP+ROW_NUMBER()或OFFSET FETCH（2012+）
-- 2012+（推荐）
SELECT order_id, order_amount FROM `order` 
WHERE user_id = 1001 
ORDER BY order_time DESC 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 批量复制数据（SQL SERVER）
DECLARE @PageSize INT = 1000; -- 每页大小
DECLARE @PageNumber INT = 1; -- 起始页码
DECLARE @TotalRows INT;
SELECT @TotalRows = COUNT(*) FROM source_table;
WHILE @PageNumber <= CEILING(CAST(@TotalRows AS DECIMAL)/@PageSize)
BEGIN
  INSERT INTO target_table (col1, col2)
  SELECT col1, col2 FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum, col1, col2
    FROM source_table
  ) AS SubQuery
  WHERE RowNum BETWEEN (@PageNumber-1)*@PageSize +1 AND @PageNumber*@PageSize;
  SET @PageNumber = @PageNumber +1;
END
-- 4. HIVE：使用LIMIT（与MySQL类似），但需注意分区筛选
SELECT user_id, behavior_type FROM user_behavior 
WHERE dt = '2024-01-01' 
ORDER BY behavior_time DESC 
LIMIT 10 OFFSET 20;
-- 5. Doris：使用LIMIT（与MySQL完全兼容）
SELECT dt, region, sales_amount FROM sales_stat 
WHERE dt = '2024-01-01' 
ORDER BY sales_amount DESC 
LIMIT 10 OFFSET 20;

4.2.2 批量插入

-- 1. MySQL：多值插入
INSERT INTO user (user_name, user_phone)
VALUES ('张三', '138xxxx1234'), ('李四', '139xxxx5678'), ('王五', '137xxxx9012');
-- 2. Oracle：使用INSERT ALL
INSERT ALL
  INTO user (user_name, user_phone) VALUES ('张三', '138xxxx1234')
  INTO user (user_name, user_phone) VALUES ('李四', '139xxxx5678')
  INTO user (user_name, user_phone) VALUES ('王五', '137xxxx9012')
SELECT 1 FROM DUAL;
-- 3. SQL SERVER：多值插入（与MySQL类似）
INSERT INTO user (user_name, user_phone)
VALUES ('张三', '138xxxx1234'), ('李四', '139xxxx5678'), ('王五', '137xxxx9012');
-- 4. HIVE：批量插入（动态分区）
SET hive.exec.dynamic.partition=true;
INSERT OVERWRITE TABLE user_behavior PARTITION(dt)
SELECT user_id, behavior_type, product_id, substr(behavior_time,1,10) AS dt
FROM user_behavior_raw;
-- 5. Doris：多值插入（与MySQL类似）
INSERT INTO sales_stat (dt, region, product_id, sales_amount, sales_count)
VALUES 
('2024-01-01', '北京', 'p001', 1000.00, 10),
('2024-01-01', '上海', 'p002', 2000.00, 20);

4.2.3 空值处理

-- 1. MySQL：IFNULL（替代NULL值）
SELECT user_id, IFNULL(user_name, '未知用户') AS user_name FROM user;
-- 2. Oracle：NVL（替代NULL值）
SELECT user_id, NVL(user_name, '未知用户') AS user_name FROM user;
-- 3. SQL SERVER：ISNULL（替代NULL值）
SELECT user_id, ISNULL(user_name, '未知用户') AS user_name FROM user;
-- 4. HIVE：COALESCE（替代NULL值，支持多参数）
SELECT user_id, COALESCE(user_name, '未知用户', '无姓名') AS user_name FROM user;
-- 5. Doris：IFNULL（与MySQL兼容）
SELECT user_id, IFNULL(user_name, '未知用户') AS user_name FROM user;

4.3 常见坑与优化案例（重点）

案例1：避免子查询嵌套过深，用CTE替代

优化前（嵌套3层，可读性差，执行效率低）：

SELECT order_id, order_amount FROM `order`
WHERE user_id IN (
  SELECT user_id FROM user
  WHERE region IN (
    SELECT region FROM region_info
    WHERE province = '北京'
  )
);

优化后（用CTE拆分，可读性提升，执行效率优化）：

-- CTE（公共表表达式），拆分逻辑
WITH beijing_region AS (
  SELECT region FROM region_info WHERE province = '北京'
), beijing_user AS (
  SELECT user_id FROM user WHERE region IN (SELECT region FROM beijing_region)
)
SELECT order_id, order_amount FROM `order`
WHERE user_id IN (SELECT user_id FROM beijing_user);

案例2：用EXISTS替代IN（大数据量场景）

场景：查询“有订单的用户”，用户表和订单表均为百万级数据，IN效率低，EXISTS更高效（EXISTS只要找到匹配记录就停止查询，IN需遍历所有匹配值）。

-- 优化前（IN，效率低）
SELECT user_id, user_name FROM user
WHERE user_id IN (SELECT DISTINCT user_id FROM `order`);
-- 优化后（EXISTS，效率高）
SELECT u.user_id, u.user_name FROM user u
WHERE EXISTS (
  SELECT 1 FROM `order` o WHERE o.user_id = u.user_id
);

案例3：避免不必要的DISTINCT和GROUP BY

优化前（冗余DISTINCT，无需去重）：

-- 订单表中user_id对应唯一用户，无需DISTINCT
SELECT DISTINCT user_id, order_time FROM `order` WHERE order_time > '2024-01-01';
-- 优化后（删除冗余DISTINCT）
SELECT user_id, order_time FROM `order` WHERE order_time > '2024-01-01';

案例4：HIVE避免数据倾斜（GROUP BY优化）

场景：HIVE查询“各地区用户行为数”，部分地区数据量过大，导致数据倾斜，查询卡顿。

-- 优化前（数据倾斜，部分Reduce任务卡死）
SELECT region, COUNT(*) AS behavior_count
FROM user_behavior
WHERE dt = '2024-01-01'
GROUP BY region;

优化方案1：开启Map端聚合，减少数据传输

SET hive.map.aggr = true; -- 开启Map端聚合
SET hive.groupby.mapaggr.checkinterval = 100000; -- Map端聚合阈值

优化方案2：添加随机前缀，打散倾斜数据（适用于严重倾斜场景）

SELECT region, SUM(behavior_count) AS behavior_count
FROM (
  SELECT 
    region, 
    COUNT(*) AS behavior_count,
    CAST(RAND() * 10 AS INT) AS random_num -- 添加随机前缀（0-9）
  FROM user_behavior
  WHERE dt = '2024-01-01'
  GROUP BY region, random_num -- 按region+随机数分组，打散数据
) t
GROUP BY region;

案例5：Doris聚合查询优化（利用物化视图）

场景：高频查询“每日各地区销售总额”，直接查询基表效率低，创建物化视图优化。

-- 1. 创建物化视图（按dt、region聚合，自动同步基表数据）
CREATE MATERIALIZED VIEW sales_daily_region
AS
SELECT dt, region, SUM(sales_amount) AS total_amount
FROM sales_stat
GROUP BY dt, region;

-- 2. 查询物化视图（效率比查询基表提升10倍以上）
SELECT dt, region, total_amount
FROM sales_daily_region
WHERE dt BETWEEN '2024-01-01' AND '2024-01-31';

五、辅助优化：缓存与其他提升方式

除了表设计、索引、SQL写法优化，还可通过缓存、分库分表、执行计划分析等辅助方式，进一步提升SQL执行效率，减轻数据库压力。重点介绍Redis缓存（公司常用），补充其他辅助手段。

5.1 Redis缓存优化（高频查询场景）

5.1.1 核心理论

Redis是高性能的内存数据库，核心作用是“缓存高频查询数据”，减少数据库访问次数，提升响应速度（内存访问速度是磁盘的1000倍以上）。适用场景：高频查询、变化频率低的数据（如用户信息、产品信息、字典数据）。

核心原则：缓存高频数据、设置合理过期时间、避免缓存穿透/击穿/雪崩。

缓存穿透：查询不存在的数据，缓存和数据库都未命中，导致请求直接打向数据库（如恶意查询无效用户ID），解决方案：缓存空值、布隆过滤器拦截无效请求；
缓存击穿：热点数据缓存过期，大量并发请求同时打向数据库，解决方案：互斥锁、热点数据永不过期、逻辑过期；
缓存雪崩：大量缓存同时过期，导致所有请求打向数据库，解决方案：设置随机过期时间、缓存预热、多级缓存。

5.1.2 实战案例（结合MySQL+Redis）

场景：高频查询“用户信息”（用户ID→用户姓名、手机号、地区），用Redis缓存，减少MySQL访问。

// 伪代码（Java+Redis+MySQL）
public UserInfo getUserInfo(Integer userId) {
    String key = "user:info:" + userId;
    // 1. 先查Redis缓存
    String userJson = redisTemplate.opsForValue().get(key);
    if (userJson != null) {
        // 缓存命中，直接返回
        return JSON.parseObject(userJson, UserInfo.class);
    }
    // 2. 缓存未命中，查MySQL数据库
    UserInfo userInfo = userMapper.selectById(userId);
    if (userInfo != null) {
        // 3. 将查询结果存入Redis，设置过期时间（30分钟，加随机值避免雪崩）
        int expireTime = 1800 + new Random().nextInt(600); // 30-40分钟随机过期
        redisTemplate.opsForValue().set(key, JSON.toJSONString(userInfo), expireTime, TimeUnit.SECONDS);
    } else {
        // 4. 缓存空值，避免缓存穿透（过期时间设为5分钟）
        redisTemplate.opsForValue().set(key, "null", 300, TimeUnit.SECONDS);
    }
    return userInfo;
}
// 补充：缓存更新策略（用户信息更新时，同步更新缓存）
public void updateUserInfo(UserInfo userInfo) {
    // 1. 更新MySQL数据库
    userMapper.updateById(userInfo);
    // 2. 更新Redis缓存（覆盖或删除）
    String key = "user:info:" + userInfo.getUserId();
    redisTemplate.opsForValue().set(key, JSON.toJSONString(userInfo), 1800, TimeUnit.SECONDS);
    // 或删除缓存（下次查询自动重建）
    // redisTemplate.delete(key);
}
// 缓存击穿解决方案（互斥锁）
public UserInfo getUserInfoWithLock(Integer userId) {
    String key = "user:info:" + userId;
    String lockKey = "lock:user:" + userId;
    String userJson = redisTemplate.opsForValue().get(key);
    if (userJson != null) {
        return JSON.parseObject(userJson, UserInfo.class);
    }
    // 获取互斥锁，避免并发请求打向数据库
    Boolean lock = redisTemplate.opsForValue().setIfAbsent(lockKey, "1", 10, TimeUnit.SECONDS);
    if (Boolean.TRUE.equals(lock)) {
        try {
            // 再次查询缓存（防止其他线程已重建缓存）
            userJson = redisTemplate.opsForValue().get(key);
            if (userJson != null) {
                return JSON.parseObject(userJson, UserInfo.class);
            }
            // 查数据库，重建缓存
            UserInfo userInfo = userMapper.selectById(userId);
            if (userInfo != null) {
                redisTemplate.opsForValue().set(key, JSON.toJSONString(userInfo), 1800, TimeUnit.SECONDS);
            } else {
                redisTemplate.opsForValue().set(key, "null", 300, TimeUnit.SECONDS);
            }
            return userInfo;
        } finally {
            // 释放锁
            redisTemplate.delete(lockKey);
        }
    } else {
        // 未获取到锁，重试（或返回默认值）
        try {
            Thread.sleep(50);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        return getUserInfoWithLock(userId);
    }
}

SQL层面配合：缓存命中时，无需执行SQL；缓存未命中时，执行简单的单表查询（避免复杂SQL），进一步提升效率。

5.2 其他辅助优化方式

5.2.1 执行计划分析（定位SQL瓶颈）

所有数据库都支持“执行计划”，通过执行计划可查看SQL的执行过程（如是否全表扫描、是否命中索引、JOIN方式等），定位性能瓶颈。

-- 1. MySQL：EXPLAIN（查看执行计划）
EXPLAIN SELECT order_id, order_amount FROM `order` 
WHERE user_id = 1001 AND order_time > '2024-01-01';
-- 2. Oracle：EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY
EXPLAIN PLAN FOR
SELECT order_id, order_amount FROM `order` 
WHERE user_id = 1001 AND order_time > TO_DATE('2024-01-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

就节选到此了，如需全部，可联系我获取。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-03-25，如有侵权请联系 cloudcommunity@tencent.com 删除

优化