OceanBase 常见 SQL 优化案例

SQL 优化的重要性

SQL 优化是 OceanBase 数据库性能优化的重要组成部分。高效的 SQL 语句可以显著提高数据库的性能，降低资源消耗，提高系统的响应速度。相反，低效的 SQL 语句可能导致数据库性能下降，甚至系统崩溃。

慢 SQL 的危害

资源消耗大：慢 SQL 会消耗大量的 CPU、内存和 I/O 资源
响应时间长：慢 SQL 会导致应用程序响应时间变长，影响用户体验
锁竞争激烈：慢 SQL 会持有锁的时间变长，导致锁竞争激烈，影响其他事务
系统吞吐量下降：慢 SQL 会降低系统的整体吞吐量，影响系统的并发处理能力

SQL 优化的收益

提高查询速度：优化后的 SQL 语句执行速度更快
降低资源消耗：减少 CPU、内存和 I/O 资源的消耗
提高系统吞吐量：提高系统的并发处理能力
改善用户体验：缩短应用程序的响应时间
降低硬件成本：提高资源利用率，减少硬件投资

常见 SQL 优化案例

案例一：全表扫描优化

场景：查询语句执行全表扫描，导致性能下降

原 SQL：

sql

SELECT * FROM orders WHERE order_status = 1;

问题：orders 表没有为 order_status 列创建索引，导致查询执行全表扫描

优化方案：为 order_status 列创建索引

sql

-- 创建索引
CREATE INDEX idx_order_status ON orders(order_status);

-- 优化后的查询（索引生效）
SELECT * FROM orders WHERE order_status = 1;

优化效果：

查询时间从 1000ms 降低到 10ms
扫描行数从 100 万行降低到 1 万行
CPU 使用率从 50% 降低到 5%

案例二：联合索引优化

场景：查询语句使用多个列作为查询条件，但没有创建合适的联合索引

原 SQL：

sql

SELECT * FROM users WHERE age > 18 AND gender = 'M' AND city = 'Beijing';

问题：users 表分别为 age、gender、city 列创建了单独的索引，但没有创建联合索引，导致查询无法充分利用索引

优化方案：创建合适的联合索引，遵循最左前缀原则

sql

-- 创建联合索引
CREATE INDEX idx_age_gender_city ON users(age, gender, city);

-- 优化后的查询（联合索引生效）
SELECT * FROM users WHERE age > 18 AND gender = 'M' AND city = 'Beijing';

优化效果：

查询时间从 500ms 降低到 50ms
扫描行数从 50 万行降低到 5 千行
内存使用率从 30% 降低到 3%

案例三：索引覆盖查询优化

场景：查询语句需要返回多个列，但索引只包含部分列，导致需要回表查询

原 SQL：

sql

SELECT user_id, user_name, email FROM users WHERE user_id > 1000;

问题：users 表只有主键索引，查询需要返回 user_name 和 email 列，导致需要回表查询

优化方案：创建覆盖索引，包含查询所需的所有列

sql

-- 创建覆盖索引
CREATE INDEX idx_user_id_name_email ON users(user_id, user_name, email);

-- 优化后的查询（索引覆盖，无需回表）
SELECT user_id, user_name, email FROM users WHERE user_id > 1000;

优化效果：

查询时间从 300ms 降低到 30ms
避免了回表查询，减少了磁盘 I/O
提高了查询效率，降低了资源消耗

案例四：子查询优化

场景：查询语句包含子查询，导致性能下降

原 SQL：

sql

SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE age > 18);

问题：子查询会导致性能下降，特别是当子查询结果集较大时

优化方案：使用 JOIN 代替子查询

sql

-- 优化后的查询（使用 JOIN 代替子查询）
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.user_id WHERE u.age > 18;

优化效果：

查询时间从 800ms 降低到 80ms
避免了子查询的嵌套执行
提高了查询效率，降低了资源消耗

案例五：分页查询优化

场景：分页查询语句使用 OFFSET 关键字，导致性能下降

原 SQL：

sql

SELECT * FROM products ORDER BY product_id DESC LIMIT 100000, 10;

问题：OFFSET 关键字会导致数据库扫描大量的无用数据，影响性能

优化方案：使用基于主键的分页查询

sql

-- 优化后的查询（基于主键的分页）
SELECT * FROM products WHERE product_id < (SELECT product_id FROM products ORDER BY product_id DESC LIMIT 100000, 1) ORDER BY product_id DESC LIMIT 10;

优化效果：

查询时间从 1500ms 降低到 15ms
避免了扫描大量的无用数据
提高了分页查询的效率

案例六：LIKE 查询优化

场景：LIKE 查询使用前置通配符，导致索引失效

原 SQL：

sql

SELECT * FROM products WHERE product_name LIKE '%手机%';

问题：LIKE 查询使用前置通配符（%），导致索引失效，执行全表扫描

优化方案：

避免使用前置通配符
使用全文索引
使用应用程序端的模糊查询

sql

-- 优化方案 1：避免使用前置通配符
SELECT * FROM products WHERE product_name LIKE '手机%';

-- 优化方案 2：使用全文索引
CREATE FULLTEXT INDEX idx_product_name ON products(product_name);
SELECT * FROM products WHERE MATCH(product_name) AGAINST('手机' IN NATURAL LANGUAGE MODE);

优化效果：

查询时间从 1200ms 降低到 120ms
索引生效，避免了全表扫描
提高了模糊查询的效率

案例七：COUNT 函数优化

场景：使用 COUNT(*) 统计行数，导致性能下降

原 SQL：

sql

SELECT COUNT(*) FROM orders;

问题：COUNT(*) 会扫描整个表，导致性能下降，特别是当表数据量较大时

优化方案：

使用 COUNT(primary_key) 代替 COUNT(*)
使用近似计数（如 HyperLogLog 算法）
缓存计数结果

sql

-- 优化方案 1：使用 COUNT(primary_key) 代替 COUNT(*)
SELECT COUNT(order_id) FROM orders;

-- 优化方案 2：使用近似计数
SELECT APPROX_COUNT_DISTINCT(user_id) FROM orders;

优化效果：

查询时间从 2000ms 降低到 200ms
减少了扫描的数据量
提高了计数查询的效率

案例八：JOIN 优化

场景：多表 JOIN 查询，导致性能下降

原 SQL：

sql

SELECT * FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE o.order_date > '2023-01-01';

问题：多表 JOIN 查询会导致性能下降，特别是当 JOIN 的表较多时

优化方案：

减少 JOIN 的表数量
为 JOIN 条件创建索引
使用子查询或临时表优化

sql

-- 优化方案 1：为 JOIN 条件创建索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 优化方案 2：使用子查询优化
SELECT * FROM (
    SELECT o.*, oi.* FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_date > '2023-01-01'
) t JOIN products p ON t.product_id = p.product_id;

优化效果：

查询时间从 1800ms 降低到 180ms
减少了 JOIN 的复杂度
提高了多表查询的效率

案例九：GROUP BY 优化

场景：GROUP BY 查询导致性能下降

原 SQL：

sql

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

问题：GROUP BY 查询会导致排序操作，影响性能

优化方案：

为 GROUP BY 列创建索引
使用 WITH ROLLUP 替代多个 GROUP BY 查询
使用应用程序端的分组

sql

-- 优化方案 1：为 GROUP BY 列创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 优化后的查询（索引生效，避免排序）
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

优化效果：

查询时间从 1000ms 降低到 100ms
避免了排序操作
提高了 GROUP BY 查询的效率

案例十：ORDER BY 优化

场景：ORDER BY 查询导致性能下降

原 SQL：

sql

SELECT * FROM products WHERE category_id = 1 ORDER BY price DESC;

问题：ORDER BY 查询会导致排序操作，影响性能

优化方案：为 ORDER BY 列创建索引

sql

-- 创建索引（包含查询条件和排序字段）
CREATE INDEX idx_category_id_price ON products(category_id, price DESC);

-- 优化后的查询（索引生效，避免排序）
SELECT * FROM products WHERE category_id = 1 ORDER BY price DESC;

优化效果：

查询时间从 900ms 降低到 90ms
避免了排序操作
提高了 ORDER BY 查询的效率

SQL 优化的方法和技巧

分析慢 SQL

使用 OceanBase 慢 SQL 日志：

sql

-- 查看慢 SQL 日志
SELECT * FROM oceanbase.GV$OB_SLOW_QUERY ORDER BY start_time DESC LIMIT 10;

-- 分析慢 SQL 执行计划
EXPLAIN SELECT * FROM orders WHERE order_status = 1;

-- 查看慢 SQL 的执行统计信息
SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE plan_id = 12345;

使用性能分析工具：

OCP：OceanBase 云平台提供了慢 SQL 分析功能
Prometheus + Grafana：可以通过 OceanBase 提供的 exporter 监控慢 SQL
OceanBase 内置诊断工具：如 SHOW PROFILE 命令

优化 SQL 的基本原则

避免全表扫描：为查询条件创建合适的索引
避免索引失效：
- 避免在索引列上使用函数或表达式
- 避免在索引列上使用 NOT、!= 或 <> 操作符
- 避免在索引列上使用 LIKE '%value%' 模糊查询
- 避免在索引列上使用 IS NULL 或 IS NOT NULL
使用合适的索引类型：根据查询需求选择合适的索引类型
优化 JOIN 查询：
- 小表驱动大表
- 为 JOIN 条件创建索引
- 减少 JOIN 的表数量
优化子查询：使用 JOIN 代替子查询
优化分页查询：使用基于主键的分页
优化 GROUP BY 和 ORDER BY：为 GROUP BY 和 ORDER BY 列创建索引
**避免 SELECT ***：只查询需要的列，使用索引覆盖查询

索引优化技巧

选择高选择性的列作为索引：索引的选择性越高，索引的效率越高
遵循最左前缀原则：联合索引的查询效率取决于查询条件是否匹配索引的最左前缀
避免过度索引：每个表的索引数量不宜过多，建议不超过 5 个
使用覆盖索引：查询结果可以完全从索引中获取，不需要访问表数据
定期维护索引：定期收集索引统计信息，重建碎片化的索引

SQL 优化的最佳实践

开发阶段

编写高效的 SQL 语句：遵循 SQL 优化的基本原则
使用绑定变量：避免 SQL 注入，提高缓存命中率
避免硬编码值：使用参数化查询
测试 SQL 性能：在开发阶段测试 SQL 语句的性能
使用 EXPLAIN 分析执行计划：分析 SQL 语句的执行计划，找出性能瓶颈

测试阶段

压力测试：使用压力测试工具测试 SQL 语句的性能
边界测试：测试边界条件下的 SQL 性能
大数据量测试：测试大数据量下的 SQL 性能
并发测试：测试并发场景下的 SQL 性能
分析执行计划：分析 SQL 语句的执行计划，优化性能瓶颈

生产阶段

监控慢 SQL：配置慢 SQL 监控，及时发现慢 SQL
分析慢 SQL：定期分析慢 SQL，找出性能问题
优化慢 SQL：对慢 SQL 进行优化，提高性能
定期审查 SQL：定期审查应用程序中的 SQL 语句
持续优化：持续监控和优化 SQL 性能

SQL 优化的常见问题

索引失效

症状：查询语句没有使用预期的索引，执行全表扫描

解决方案：

检查查询语句，避免在索引列上使用函数或表达式
检查索引列的数据类型，确保与查询条件的数据类型一致
避免使用 NOT、!= 或 <> 操作符
避免使用 LIKE '%value%' 模糊查询
重新创建索引，确保索引有效

执行计划不稳定

症状：相同的 SQL 语句在不同时间执行，使用不同的执行计划

解决方案：

定期收集表和索引的统计信息
使用绑定变量，避免 SQL 语句的硬解析
使用 SQL 计划绑定，固定执行计划
调整查询语句，使其执行计划更稳定

锁竞争激烈

症状：SQL 语句持有锁的时间过长，导致锁竞争激烈

解决方案：

优化 SQL 语句，减少执行时间
减少事务的范围，缩短持有锁的时间
使用合适的隔离级别
避免在事务中执行长时间的查询

资源消耗过大

症状：SQL 语句消耗大量的 CPU、内存或 I/O 资源

解决方案：

优化 SQL 语句，减少资源消耗
为查询条件创建合适的索引
调整查询语句，减少扫描的数据量
增加硬件资源，提高系统的处理能力

SQL 优化的工具和命令

执行计划分析

sql

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE order_status = 1;

-- 查看详细的执行计划
EXPLAIN EXTENDED SELECT * FROM orders WHERE order_status = 1;

-- 查看格式化的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_status = 1;

性能分析

sql

-- 启用性能分析
SET profiling = 1;

-- 执行查询
SELECT * FROM orders WHERE order_status = 1;

-- 查看性能分析结果
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 禁用性能分析
SET profiling = 0;

慢 SQL 监控

sql

-- 查看慢 SQL 日志
SELECT * FROM oceanbase.GV$OB_SLOW_QUERY ORDER BY start_time DESC LIMIT 10;

-- 查看慢 SQL 的执行计划
SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE plan_id = 12345;

-- 查看慢 SQL 的统计信息
SELECT * FROM oceanbase.GV$OB_SLOW_QUERY_STAT;

常见问题（FAQ）

Q1: 如何找出慢 SQL？

A1: 找出慢 SQL 的方法：

配置慢 SQL 日志，记录执行时间超过阈值的 SQL 语句
使用 OceanBase 内置的慢 SQL 视图：GV$OB_SLOW_QUERY
使用 OCP 平台的慢 SQL 分析功能
使用 Prometheus + Grafana 监控慢 SQL

Q2: 如何分析慢 SQL 的执行计划？

A2: 分析慢 SQL 执行计划的方法：

使用 EXPLAIN 命令查看执行计划
分析执行计划中的扫描方式、连接方式和排序方式
找出执行计划中的性能瓶颈，如全表扫描、索引失效等
根据执行计划优化 SQL 语句

Q3: 如何优化全表扫描？

A3: 优化全表扫描的方法：

为查询条件创建合适的索引
调整查询语句，减少扫描的数据量
增加硬件资源，提高系统的处理能力
考虑使用分区表，减少扫描的数据量

Q4: 如何优化 JOIN 查询？

A4: 优化 JOIN 查询的方法：

小表驱动大表
为 JOIN 条件创建索引
减少 JOIN 的表数量
使用 JOIN 代替子查询
考虑使用临时表优化复杂 JOIN

Q5: 如何优化 GROUP BY 和 ORDER BY？

A5: 优化 GROUP BY 和 ORDER BY 的方法：

为 GROUP BY 和 ORDER BY 列创建索引
避免在 GROUP BY 和 ORDER BY 列上使用函数或表达式
考虑使用应用程序端的分组和排序
调整 GROUP BY 和 ORDER BY 的顺序

Q6: 如何优化分页查询？

A6: 优化分页查询的方法：

使用基于主键的分页
避免使用 OFFSET 关键字
考虑使用滚动分页
缓存热门分页结果

Q7: 如何优化 LIKE 查询？

A7: 优化 LIKE 查询的方法：

避免使用前置通配符
使用全文索引
使用应用程序端的模糊查询
考虑使用 Elasticsearch 等搜索引擎

Q8: 如何避免索引失效？

A8: 避免索引失效的方法：

避免在索引列上使用函数或表达式
避免在索引列上使用 NOT、!= 或 <> 操作符
避免在索引列上使用 LIKE '%value%' 模糊查询
避免在索引列上使用 IS NULL 或 IS NOT NULL
确保索引列的数据类型与查询条件的数据类型一致

Q9: 如何固定执行计划？

A9: 固定执行计划的方法：

使用 SQL 计划绑定
定期收集表和索引的统计信息
使用绑定变量，避免 SQL 语句的硬解析
调整查询语句，使其执行计划更稳定

Q10: 如何持续优化 SQL 性能？

A10: 持续优化 SQL 性能的方法：

配置慢 SQL 监控，及时发现慢 SQL
定期分析慢 SQL，找出性能问题
优化慢 SQL，提高性能
定期审查应用程序中的 SQL 语句
持续监控和优化 SQL 性能

OceanBase 常见 SQL 优化案例 ​

SQL 优化的重要性 ​

慢 SQL 的危害 ​

SQL 优化的收益 ​

常见 SQL 优化案例 ​

案例一：全表扫描优化 ​

案例二：联合索引优化 ​

案例三：索引覆盖查询优化 ​

案例四：子查询优化 ​

案例五：分页查询优化 ​

案例六：LIKE 查询优化 ​

案例七：COUNT 函数优化 ​

案例八：JOIN 优化 ​

案例九：GROUP BY 优化 ​

案例十：ORDER BY 优化 ​

SQL 优化的方法和技巧 ​

分析慢 SQL ​

优化 SQL 的基本原则 ​

索引优化技巧 ​

SQL 优化的最佳实践 ​

开发阶段 ​

测试阶段 ​

生产阶段 ​

SQL 优化的常见问题 ​

索引失效 ​

执行计划不稳定 ​

锁竞争激烈 ​

资源消耗过大 ​

SQL 优化的工具和命令 ​

执行计划分析 ​

性能分析 ​

慢 SQL 监控 ​

常见问题（FAQ） ​

Q1: 如何找出慢 SQL？ ​

Q2: 如何分析慢 SQL 的执行计划？ ​

Q3: 如何优化全表扫描？ ​

Q4: 如何优化 JOIN 查询？ ​

Q5: 如何优化 GROUP BY 和 ORDER BY？ ​

Q6: 如何优化分页查询？ ​

Q7: 如何优化 LIKE 查询？ ​

Q8: 如何避免索引失效？ ​

Q9: 如何固定执行计划？ ​

Q10: 如何持续优化 SQL 性能？ ​

OceanBase 常见 SQL 优化案例

SQL 优化的重要性

慢 SQL 的危害

SQL 优化的收益

常见 SQL 优化案例

案例一：全表扫描优化

案例二：联合索引优化

案例三：索引覆盖查询优化

案例四：子查询优化

案例五：分页查询优化

案例六：LIKE 查询优化

案例七：COUNT 函数优化

案例八：JOIN 优化

案例九：GROUP BY 优化

案例十：ORDER BY 优化

SQL 优化的方法和技巧

分析慢 SQL

优化 SQL 的基本原则

索引优化技巧

SQL 优化的最佳实践

开发阶段

测试阶段

生产阶段

SQL 优化的常见问题

索引失效

执行计划不稳定

锁竞争激烈

资源消耗过大

SQL 优化的工具和命令

执行计划分析

性能分析

慢 SQL 监控

常见问题（FAQ）

Q1: 如何找出慢 SQL？

Q2: 如何分析慢 SQL 的执行计划？

Q3: 如何优化全表扫描？

Q4: 如何优化 JOIN 查询？

Q5: 如何优化 GROUP BY 和 ORDER BY？

Q6: 如何优化分页查询？

Q7: 如何优化 LIKE 查询？

Q8: 如何避免索引失效？

Q9: 如何固定执行计划？

Q10: 如何持续优化 SQL 性能？