本文档风哥主要介绍DM数据库SQL编写规范与优化指南，包括SQL规范概述、优化原则、性能影响因素、编写规范、索引优化策略、查询优化策略、实施步骤、执行计划分析、调优工具、实际案例和最佳实践等内容，风哥教程参考DM官方文档DM8 SQL语言使用手册、DM8性能优化指南，适合数据库技术人员在学习和生产环境中使用。
Part01-基础概念与理论知识
1.1 SQL规范概述
SQL编写规范是确保SQL语句高效、可维护、可读的重要准则。良好的SQL编写规范可以提高数据库性能，减少维护成本，提高开发效率。
# SQL规范的定义
SQL编写规范是确保SQL语句高效、可维护、可读的重要准则，包括命名规范、格式规范、性能规范等。
# SQL规范的重要性
– 提高性能：通过规范编写SQL，提高查询性能
– 降低成本：减少数据库资源消耗，降低硬件成本
– 提高效率：减少SQL调试和优化时间
– 便于维护：提高SQL的可读性和可维护性
– 减少错误：减少SQL编写错误，提高代码质量
# SQL规范的内容
– 命名规范：表名、字段名、别名等的命名规范
– 格式规范：SQL语句的格式和缩进规范
– 性能规范：SQL语句的性能优化规范
– 注释规范：SQL语句的注释规范
– 安全规范：SQL语句的安全编写规范
# SQL规范的应用场景
– 开发阶段：在开发阶段遵循SQL规范，确保代码质量
– 测试阶段：在测试阶段验证SQL性能，发现性能问题
– 生产环境：在生产环境监控SQL性能，持续优化
– 代码审查：在代码审查阶段检查SQL规范
SQL编写规范是确保SQL语句高效、可维护、可读的重要准则，包括命名规范、格式规范、性能规范等。
# SQL规范的重要性
– 提高性能：通过规范编写SQL，提高查询性能
– 降低成本：减少数据库资源消耗，降低硬件成本
– 提高效率：减少SQL调试和优化时间
– 便于维护：提高SQL的可读性和可维护性
– 减少错误：减少SQL编写错误，提高代码质量
# SQL规范的内容
– 命名规范：表名、字段名、别名等的命名规范
– 格式规范：SQL语句的格式和缩进规范
– 性能规范：SQL语句的性能优化规范
– 注释规范：SQL语句的注释规范
– 安全规范：SQL语句的安全编写规范
# SQL规范的应用场景
– 开发阶段：在开发阶段遵循SQL规范，确保代码质量
– 测试阶段：在测试阶段验证SQL性能，发现性能问题
– 生产环境：在生产环境监控SQL性能，持续优化
– 代码审查：在代码审查阶段检查SQL规范
1.2 SQL优化原则
SQL优化需要遵循一定的原则，确保优化的有效性和安全性。
# 1. 了解数据分布
– 统计信息：收集和更新统计信息
– 数据量：了解表的数据量和增长趋势
– 数据分布：了解数据的分布情况
– 选择性：了解字段的选择性
# 2. 合理使用索引
– 创建索引：为经常查询的字段创建索引
– 使用索引：确保查询使用索引
– 避免全表扫描：避免全表扫描，提高查询效率
– 索引维护：定期维护索引，确保索引有效
# 3. 优化查询语句
– 避免SELECT *：只查询需要的字段
– 使用WHERE条件：使用WHERE条件过滤数据
– 避免子查询：避免使用子查询，使用连接代替
– 使用JOIN：使用JOIN代替子查询
# 4. 合理使用函数
– 避免在WHERE中使用函数：避免在WHERE条件中使用函数
– 使用索引列：使用索引列进行查询
– 避免类型转换：避免隐式类型转换
– 使用合适的函数：使用合适的函数提高效率
# 5. 控制返回结果
– 使用LIMIT：使用LIMIT限制返回结果数量
– 分页查询：使用分页查询减少数据传输
– 避免大结果集：避免返回大结果集
– 使用游标：使用游标处理大结果集
# 6. 批量操作
– 使用批量插入：使用批量插入提高效率
– 使用批量更新：使用批量更新提高效率 风哥提示：
– 使用批量删除：使用批量删除提高效率
– 避免循环操作：避免循环操作，使用批量操作代替
– 统计信息：收集和更新统计信息
– 数据量：了解表的数据量和增长趋势
– 数据分布：了解数据的分布情况
– 选择性：了解字段的选择性
# 2. 合理使用索引
– 创建索引：为经常查询的字段创建索引
– 使用索引：确保查询使用索引
– 避免全表扫描：避免全表扫描，提高查询效率
– 索引维护：定期维护索引，确保索引有效
# 3. 优化查询语句
– 避免SELECT *：只查询需要的字段
– 使用WHERE条件：使用WHERE条件过滤数据
– 避免子查询：避免使用子查询，使用连接代替
– 使用JOIN：使用JOIN代替子查询
# 4. 合理使用函数
– 避免在WHERE中使用函数：避免在WHERE条件中使用函数
– 使用索引列：使用索引列进行查询
– 避免类型转换：避免隐式类型转换
– 使用合适的函数：使用合适的函数提高效率
# 5. 控制返回结果
– 使用LIMIT：使用LIMIT限制返回结果数量
– 分页查询：使用分页查询减少数据传输
– 避免大结果集：避免返回大结果集
– 使用游标：使用游标处理大结果集
# 6. 批量操作
– 使用批量插入：使用批量插入提高效率
– 使用批量更新：使用批量更新提高效率 风哥提示：
– 使用批量删除：使用批量删除提高效率
– 避免循环操作：避免循环操作，使用批量操作代替
1.3 SQL性能影响因素
SQL性能受多个因素影响，了解这些因素有助于更好地优化SQL。
# 1. 数据库参数
– 内存参数：BUFFER、MEMORY_POOL等
– IO参数：MAX_BUFFER_SIZE、IO_THROTTLE_THRESHOLD等
– 并发参数：MAX_SESSIONS、MAX_WORKER_THREADS等
– 优化器参数：OPTIMIZER_MODE、HASH_JOIN_ENABLE等
# 2. 索引设计
– 索引类型：B树索引、位图索引等
– 索引字段：选择合适的索引字段
– 索引数量：避免创建过多索引
– 索引维护：定期维护索引
# 3. 表结构设计
– 表分区：使用表分区提高查询效率
– 字段类型：选择合适的字段类型
– 字段长度：设置合适的字段长度
– 约束设计：合理设计约束
# 4. SQL语句本身
– 查询复杂度：避免复杂的查询
– 子查询：避免使用子查询
– 连接方式：选择合适的连接方式
– 函数使用：合理使用函数
# 5. 数据量
– 数据量大小：数据量越大，查询越慢
– 数据增长：数据增长会影响查询性能
– 数据分布：数据分布不均会影响查询性能 学习交流加群风哥微信: itpux-com
– 数据倾斜：数据倾斜会导致性能问题
# 6. 系统资源
– CPU：CPU资源不足会影响性能
– 内存：内存资源不足会影响性能
– 磁盘IO：磁盘IO性能会影响性能
– 网络：网络延迟会影响性能
– 内存参数：BUFFER、MEMORY_POOL等
– IO参数：MAX_BUFFER_SIZE、IO_THROTTLE_THRESHOLD等
– 并发参数：MAX_SESSIONS、MAX_WORKER_THREADS等
– 优化器参数：OPTIMIZER_MODE、HASH_JOIN_ENABLE等
# 2. 索引设计
– 索引类型：B树索引、位图索引等
– 索引字段：选择合适的索引字段
– 索引数量：避免创建过多索引
– 索引维护：定期维护索引
# 3. 表结构设计
– 表分区：使用表分区提高查询效率
– 字段类型：选择合适的字段类型
– 字段长度：设置合适的字段长度
– 约束设计：合理设计约束
# 4. SQL语句本身
– 查询复杂度：避免复杂的查询
– 子查询：避免使用子查询
– 连接方式：选择合适的连接方式
– 函数使用：合理使用函数
# 5. 数据量
– 数据量大小：数据量越大，查询越慢
– 数据增长：数据增长会影响查询性能
– 数据分布：数据分布不均会影响查询性能 学习交流加群风哥微信: itpux-com
– 数据倾斜：数据倾斜会导致性能问题
# 6. 系统资源
– CPU：CPU资源不足会影响性能
– 内存：内存资源不足会影响性能
– 磁盘IO：磁盘IO性能会影响性能
– 网络：网络延迟会影响性能
风哥提示：SQL优化是一个综合性的工作，需要从多个方面考虑，包括数据库参数、索引设计、表结构设计、SQL语句本身等。了解SQL性能的影响因素，是进行SQL优化的基础。
Part02-生产环境规划与建议
2.1 SQL编写规范
2.1.1 命名规范
# 1. 表名命名规范
– 使用小写字母：表名使用小写字母
– 使用下划线分隔：多个单词使用下划线分隔
– 使用有意义的名称：表名要有意义，能够反映表的用途
– 避免使用保留字：避免使用数据库保留字
– 示例：fgedu_user、fgedu_order、fgedu_product
# 2. 字段名命名规范
– 使用小写字母：字段名使用小写字母
– 使用下划线分隔：多个单词使用下划线分隔
– 使用有意义的名称：字段名要有意义，能够反映字段的用途
– 避免使用保留字：避免使用数据库保留字
– 示例：user_id、user_name、create_time
# 3. 别名命名规范
– 使用简短有意义的别名：别名要简短且有意义
– 使用小写字母：别名使用小写字母
– 避免使用关键字：避免使用数据库关键字
– 示例：u、o、p
# 4. 实际示例
– 创建表
SQL> create table fgedu_user (
user_id int primary key,
user_name varchar(50) not null,
user_email varchar(100) unique,
create_time timestamp default current_timestamp
);
– 查询表 学习交流加群风哥QQ113257174
SQL> select user_id, user_name, user_email from fgedu_user where user_id = 1;
– 使用小写字母：表名使用小写字母
– 使用下划线分隔：多个单词使用下划线分隔
– 使用有意义的名称：表名要有意义，能够反映表的用途
– 避免使用保留字：避免使用数据库保留字
– 示例：fgedu_user、fgedu_order、fgedu_product
# 2. 字段名命名规范
– 使用小写字母：字段名使用小写字母
– 使用下划线分隔：多个单词使用下划线分隔
– 使用有意义的名称：字段名要有意义，能够反映字段的用途
– 避免使用保留字：避免使用数据库保留字
– 示例：user_id、user_name、create_time
# 3. 别名命名规范
– 使用简短有意义的别名：别名要简短且有意义
– 使用小写字母：别名使用小写字母
– 避免使用关键字：避免使用数据库关键字
– 示例：u、o、p
# 4. 实际示例
– 创建表
SQL> create table fgedu_user (
user_id int primary key,
user_name varchar(50) not null,
user_email varchar(100) unique,
create_time timestamp default current_timestamp
);
– 查询表 学习交流加群风哥QQ113257174
SQL> select user_id, user_name, user_email from fgedu_user where user_id = 1;
2.1.2 格式规范
# 1. SQL语句格式规范
– 关键字大写：SQL关键字使用大写
– 字段名小写：字段名使用小写
– 适当缩进：使用缩进提高可读性
– 换行规范：长语句适当换行
# 2. SELECT语句格式规范
– SELECT关键字独占一行
– 字段列表每行一个字段
– FROM关键字独占一行
– WHERE条件独占一行
# 3. 实际示例
– 标准格式
SQL> SELECT
user_id,
user_name,
user_email
FROM
fgedu_user
WHERE
user_id = 1
AND user_status = 1;
– 复杂查询格式
SQL> SELECT
u.user_id,
u.user_name,
o.order_id,
o.order_amount
FROM
fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
WHERE
u.user_status = 1
AND o.order_status = 1
AND o.create_time >= ‘2024-01-01’;
– 关键字大写：SQL关键字使用大写
– 字段名小写：字段名使用小写
– 适当缩进：使用缩进提高可读性
– 换行规范：长语句适当换行
# 2. SELECT语句格式规范
– SELECT关键字独占一行
– 字段列表每行一个字段
– FROM关键字独占一行
– WHERE条件独占一行
# 3. 实际示例
– 标准格式
SQL> SELECT
user_id,
user_name,
user_email
FROM
fgedu_user
WHERE
user_id = 1
AND user_status = 1;
– 复杂查询格式
SQL> SELECT
u.user_id,
u.user_name,
o.order_id,
o.order_amount
FROM
fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
WHERE
u.user_status = 1
AND o.order_status = 1
AND o.create_time >= ‘2024-01-01’;
2.1.3 性能规范
更多视频教程www.fgedu.net.cn
# 1. 避免SELECT *
– 只查询需要的字段
SQL> SELECT user_id, user_name FROM fgedu_user;
– 避免使用SELECT *
SQL> SELECT * FROM fgedu_user;
# 2. 使用WHERE条件
– 使用WHERE条件过滤数据
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 避免查询所有数据
SQL> SELECT user_id, user_name FROM fgedu_user;
# 3. 使用索引
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 使用索引查询
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
# 4. 避免在WHERE中使用函数
– 避免在WHERE中使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE create_time >= ‘2024-01-01’;
– 避免使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE YEAR(create_time) = 2024;
# 5. 使用LIMIT
– 使用LIMIT限制返回结果
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1 LIMIT 10;
– 避免返回大量数据
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 只查询需要的字段
SQL> SELECT user_id, user_name FROM fgedu_user;
– 避免使用SELECT *
SQL> SELECT * FROM fgedu_user;
# 2. 使用WHERE条件
– 使用WHERE条件过滤数据
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 避免查询所有数据
SQL> SELECT user_id, user_name FROM fgedu_user;
# 3. 使用索引
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 使用索引查询
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
# 4. 避免在WHERE中使用函数
– 避免在WHERE中使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE create_time >= ‘2024-01-01’;
– 避免使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE YEAR(create_time) = 2024;
# 5. 使用LIMIT
– 使用LIMIT限制返回结果
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1 LIMIT 10;
– 避免返回大量数据
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
2.2 索引优化策略
2.2.1 索引创建原则
# 1. 为经常查询的字段创建索引
– 为WHERE条件字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 为JOIN字段创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
– 为ORDER BY字段创建索引
SQL> CREATE INDEX idx_fgedu_user_create_time ON fgedu_user(create_time);
# 2. 为高选择性的字段创建索引
– 高选择性字段适合创建索引
SQL> CREATE INDEX idx_fgedu_user_email ON fgedu_user(user_email); 更多学习教程公众号风哥教程itpux_com
– 低选择性字段不适合创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
# 3. 避免创建过多索引
– 索引会占用存储空间
– 索引会影响插入、更新、删除性能
– 只为必要的字段创建索引
# 4. 定期维护索引
– 重建索引
SQL> ALTER INDEX idx_fgedu_user_status REBUILD;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 为WHERE条件字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 为JOIN字段创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
– 为ORDER BY字段创建索引
SQL> CREATE INDEX idx_fgedu_user_create_time ON fgedu_user(create_time);
# 2. 为高选择性的字段创建索引
– 高选择性字段适合创建索引
SQL> CREATE INDEX idx_fgedu_user_email ON fgedu_user(user_email); 更多学习教程公众号风哥教程itpux_com
– 低选择性字段不适合创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
# 3. 避免创建过多索引
– 索引会占用存储空间
– 索引会影响插入、更新、删除性能
– 只为必要的字段创建索引
# 4. 定期维护索引
– 重建索引
SQL> ALTER INDEX idx_fgedu_user_status REBUILD;
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
2.2.2 索引使用策略
# 1. 确保查询使用索引
– 查看执行计划
SQL> EXPLAIN SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 检查是否使用索引
SQL> SELECT * FROM v$sql_plan WHERE sql_id = ‘xxx’;
# 2. 避免索引失效
– 避免在索引字段上使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 避免隐式类型转换
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_id = ‘1’;
– 避免使用NOT、!=、<>
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
# 3. 使用复合索引
– 创建复合索引
SQL> CREATE INDEX idx_fgedu_user_status_time ON fgedu_user(user_status, create_time);
– 使用复合索引 from DB视频:www.itpux.com
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1 AND create_time >= ‘2024-01-01’;
# 4. 使用覆盖索引
– 创建覆盖索引
SQL> CREATE INDEX idx_fgedu_user_cover ON fgedu_user(user_status, user_id, user_name);
– 使用覆盖索引
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 检查是否使用索引
SQL> SELECT * FROM v$sql_plan WHERE sql_id = ‘xxx’;
# 2. 避免索引失效
– 避免在索引字段上使用函数
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
– 避免隐式类型转换
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_id = ‘1’;
– 避免使用NOT、!=、<>
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
# 3. 使用复合索引
– 创建复合索引
SQL> CREATE INDEX idx_fgedu_user_status_time ON fgedu_user(user_status, create_time);
– 使用复合索引 from DB视频:www.itpux.com
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1 AND create_time >= ‘2024-01-01’;
# 4. 使用覆盖索引
– 创建覆盖索引
SQL> CREATE INDEX idx_fgedu_user_cover ON fgedu_user(user_status, user_id, user_name);
– 使用覆盖索引
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1;
2.3 查询优化策略
2.3.1 查询重写策略
# 1. 使用JOIN代替子查询
– 使用JOIN
SQL> SELECT u.user_id, u.user_name, o.order_id
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
WHERE u.user_status = 1;
– 避免使用子查询
SQL> SELECT user_id, user_name, order_id
FROM fgedu_user
WHERE user_status = 1
AND user_id IN (SELECT user_id FROM fgedu_order);
# 2. 使用EXISTS代替IN
– 使用EXISTS
SQL> SELECT user_id, user_name
FROM fgedu_user u
WHERE EXISTS (
SELECT 1 FROM fgedu_order o WHERE o.user_id = u.user_id
);
– 避免使用IN
SQL> SELECT user_id, user_name
FROM fgedu_user
WHERE user_id IN (SELECT user_id FROM fgedu_order);
# 3. 使用UNION ALL代替UNION
– 使用UNION ALL
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1
UNION ALL
SELECT user_id, user_name FROM fgedu_user WHERE user_status = 2;
– 避免使用UNION
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1
UNION
SELECT user_id, user_name FROM fgedu_user WHERE user_status = 2;
# 4. 使用批量操作
– 使用批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
– 避免循环插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (3, ‘user3’, ‘user3@fgedu.net.cn’);
– 使用JOIN
SQL> SELECT u.user_id, u.user_name, o.order_id
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
WHERE u.user_status = 1;
– 避免使用子查询
SQL> SELECT user_id, user_name, order_id
FROM fgedu_user
WHERE user_status = 1
AND user_id IN (SELECT user_id FROM fgedu_order);
# 2. 使用EXISTS代替IN
– 使用EXISTS
SQL> SELECT user_id, user_name
FROM fgedu_user u
WHERE EXISTS (
SELECT 1 FROM fgedu_order o WHERE o.user_id = u.user_id
);
– 避免使用IN
SQL> SELECT user_id, user_name
FROM fgedu_user
WHERE user_id IN (SELECT user_id FROM fgedu_order);
# 3. 使用UNION ALL代替UNION
– 使用UNION ALL
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1
UNION ALL
SELECT user_id, user_name FROM fgedu_user WHERE user_status = 2;
– 避免使用UNION
SQL> SELECT user_id, user_name FROM fgedu_user WHERE user_status = 1
UNION
SELECT user_id, user_name FROM fgedu_user WHERE user_status = 2;
# 4. 使用批量操作
– 使用批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
– 避免循环插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (3, ‘user3’, ‘user3@fgedu.net.cn’);
生产环境建议：根据业务需求和数据特点，制定SQL编写规范和优化策略，确保SQL语句的高效执行。定期审查和优化SQL，持续提升数据库性能。
Part03-生产环境项目实施方案
3.1 SQL优化实施步骤
3.1.1 慢查询识别
# 1. 开启慢查询日志
– 查看慢查询配置
SQL> select * from v$parameter where name = ‘SLOW_QUERY_TIME’;
– 设置慢查询时间阈值（单位：秒）
SQL> alter system set ‘SLOW_QUERY_TIME’ = 2 both;
– 开启慢查询日志
SQL> alter system set ‘SLOW_QUERY_LOG’ = 1 both;
# 2. 查看慢查询日志
– 查看慢查询日志文件
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
– 查看慢查询统计信息
SQL> select * from v$sql where elapsed_time > 2000000;
# 3. 分析慢查询
– 查看慢查询的执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看慢查询的资源使用情况
SQL> select sql_text, elapsed_time, cpu_time, buffer_gets from v$sql where sql_id = ‘xxx’;
– 查看慢查询配置
SQL> select * from v$parameter where name = ‘SLOW_QUERY_TIME’;
– 设置慢查询时间阈值（单位：秒）
SQL> alter system set ‘SLOW_QUERY_TIME’ = 2 both;
– 开启慢查询日志
SQL> alter system set ‘SLOW_QUERY_LOG’ = 1 both;
# 2. 查看慢查询日志
– 查看慢查询日志文件
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
– 查看慢查询统计信息
SQL> select * from v$sql where elapsed_time > 2000000;
# 3. 分析慢查询
– 查看慢查询的执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看慢查询的资源使用情况
SQL> select sql_text, elapsed_time, cpu_time, buffer_gets from v$sql where sql_id = ‘xxx’;
3.1.2 SQL优化执行
# 1. 分析执行计划
– 查看执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看详细的执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘xxx’));
# 2. 优化SQL语句
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 重写SQL语句
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 验证优化效果
– 查看优化后的执行计划
SQL> EXPLAIN SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 执行优化后的SQL
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 查看执行统计信息
SQL> select elapsed_time, cpu_time, buffer_gets from v$sql where sql_id = ‘xxx’;
– 查看执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看详细的执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘xxx’));
# 2. 优化SQL语句
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 重写SQL语句
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 验证优化效果
– 查看优化后的执行计划
SQL> EXPLAIN SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 执行优化后的SQL
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 查看执行统计信息
SQL> select elapsed_time, cpu_time, buffer_gets from v$sql where sql_id = ‘xxx’;
3.2 执行计划分析
3.2.1 执行计划查看
# 1. 使用EXPLAIN查看执行计划
– 查看简单执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1, 0]
2 #PRJT2: [0, 1, 0]; exp_num(4), is_mem(FALSE)
3 #BLKUP2: [0, 1, 0]; IDX_FGEDU_USER_STATUS(FGEDU_USER)
4 #SSEK2: [0, 1, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
# 2. 使用DBMS_XPLAN查看详细执行计划
– 查看详细执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
执行计划：
PLAN_TABLE_OUTPUT
—————————————————————–
SQL_ID xxx
Plan hash value: 1234567890
—————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_USER | 1 | 50 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_USER_STATUS | 1 | | 1 |
—————————————————————–
# 3. 使用AUTOTRACE查看执行计划和统计信息
– 开启AUTOTRACE
SQL> SET AUTOTRACE ON;
– 执行查询
SQL> SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看执行计划和统计信息
Execution Plan
———————————————————-
Plan hash value: 1234567890
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
———————————————————-
| 0 | SELECT STATEMENT | | 1 | 50 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_USER | 1 | 50 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_USER_STATUS | 1 | | 1 |
———————————————————-
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
123 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
– 查看简单执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1, 0]
2 #PRJT2: [0, 1, 0]; exp_num(4), is_mem(FALSE)
3 #BLKUP2: [0, 1, 0]; IDX_FGEDU_USER_STATUS(FGEDU_USER)
4 #SSEK2: [0, 1, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
# 2. 使用DBMS_XPLAN查看详细执行计划
– 查看详细执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
执行计划：
PLAN_TABLE_OUTPUT
—————————————————————–
SQL_ID xxx
Plan hash value: 1234567890
—————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_USER | 1 | 50 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_USER_STATUS | 1 | | 1 |
—————————————————————–
# 3. 使用AUTOTRACE查看执行计划和统计信息
– 开启AUTOTRACE
SQL> SET AUTOTRACE ON;
– 执行查询
SQL> SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看执行计划和统计信息
Execution Plan
———————————————————-
Plan hash value: 1234567890
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
———————————————————-
| 0 | SELECT STATEMENT | | 1 | 50 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_USER | 1 | 50 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_USER_STATUS | 1 | | 1 |
———————————————————-
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
123 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.2.2 执行计划分析
# 1. 分析访问路径
– 全表扫描（TABLE ACCESS FULL）
– 表示扫描整个表
– 通常效率较低
– 应该创建索引避免全表扫描
– 索引扫描（INDEX RANGE SCAN）
– 表示使用索引扫描
– 通常效率较高
– 应该确保查询使用索引
# 2. 分析连接方式
– 嵌套循环连接（NESTED LOOPS）
– 适用于小表连接大表
– 驱动表应该选择小表
– 应该确保连接字段有索引
– 哈希连接（HASH JOIN）
– 适用于大表连接
– 需要足够的内存
– 应该确保内存参数配置合理
– 排序合并连接（MERGE JOIN）
– 适用于排序后的数据连接
– 需要排序操作
– 应该避免不必要的排序
# 3. 分析统计信息
– 逻辑读（consistent gets）
– 表示从缓冲区读取的数据块数量
– 应该尽量减少逻辑读
– 物理读（physical reads）
– 表示从磁盘读取的数据块数量
– 应该尽量减少物理读
– 排序操作（sorts）
– 表示排序操作的次数
– 应该尽量减少排序操作
– 全表扫描（TABLE ACCESS FULL）
– 表示扫描整个表
– 通常效率较低
– 应该创建索引避免全表扫描
– 索引扫描（INDEX RANGE SCAN）
– 表示使用索引扫描
– 通常效率较高
– 应该确保查询使用索引
# 2. 分析连接方式
– 嵌套循环连接（NESTED LOOPS）
– 适用于小表连接大表
– 驱动表应该选择小表
– 应该确保连接字段有索引
– 哈希连接（HASH JOIN）
– 适用于大表连接
– 需要足够的内存
– 应该确保内存参数配置合理
– 排序合并连接（MERGE JOIN）
– 适用于排序后的数据连接
– 需要排序操作
– 应该避免不必要的排序
# 3. 分析统计信息
– 逻辑读（consistent gets）
– 表示从缓冲区读取的数据块数量
– 应该尽量减少逻辑读
– 物理读（physical reads）
– 表示从磁盘读取的数据块数量
– 应该尽量减少物理读
– 排序操作（sorts）
– 表示排序操作的次数
– 应该尽量减少排序操作
3.3 SQL调优工具
3.4.1 SQL调优Advisor
# 1. 使用SQL调优Advisor
– 创建调优任务
SQL> DECLARE
task_name VARCHAR2(100);
BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘xxx’,
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 3600,
task_name => ‘fgedu_sql_tuning_task’
);
DBMS_OUTPUT.PUT_LINE(‘Task created: ‘ || task_name);
END;
/
– 执行调优任务
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘fgedu_sql_tuning_task’);
– 查看调优报告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘fgedu_sql_tuning_task’) FROM dual;
# 2. 调优报告分析
– 查看调优建议
SQL> SELECT * FROM DBMS_ADVISOR.TASK_RECOMMENDATIONS(‘fgedu_sql_tuning_task’);
– 查看调优原因
SQL> SELECT * FROM DBMS_ADVISOR.TASK_RATIONALE(‘fgedu_sql_tuning_task’);
– 查看调优动作
SQL> SELECT * FROM DBMS_ADVISOR.TASK_ACTIONS(‘fgedu_sql_tuning_task’);
– 创建调优任务
SQL> DECLARE
task_name VARCHAR2(100);
BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘xxx’,
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 3600,
task_name => ‘fgedu_sql_tuning_task’
);
DBMS_OUTPUT.PUT_LINE(‘Task created: ‘ || task_name);
END;
/
– 执行调优任务
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘fgedu_sql_tuning_task’);
– 查看调优报告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘fgedu_sql_tuning_task’) FROM dual;
# 2. 调优报告分析
– 查看调优建议
SQL> SELECT * FROM DBMS_ADVISOR.TASK_RECOMMENDATIONS(‘fgedu_sql_tuning_task’);
– 查看调优原因
SQL> SELECT * FROM DBMS_ADVISOR.TASK_RATIONALE(‘fgedu_sql_tuning_task’);
– 查看调优动作
SQL> SELECT * FROM DBMS_ADVISOR.TASK_ACTIONS(‘fgedu_sql_tuning_task’);
3.4.2 SQL性能分析工具
# 1. 使用AWR报告
– 生成AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看AWR报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
# 2. 使用ASH报告
– 查看ASH报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
# 3. 使用SQL监控
– 查看SQL监控信息
SQL> SELECT * FROM v$sql_monitor WHERE sql_id = ‘xxx’;
– 查看SQL监控详情
SQL> SELECT * FROM v$sql_monitor WHERE sql_id = ‘xxx’ AND status = ‘EXECUTING’;
– 生成AWR快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
– 查看AWR报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_snap => 100,
end_snap => 101
));
# 2. 使用ASH报告
– 查看ASH报告
SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
dbid => 1234567890,
instance_number => 1,
begin_time => SYSDATE – 1/24,
end_time => SYSDATE
));
# 3. 使用SQL监控
– 查看SQL监控信息
SQL> SELECT * FROM v$sql_monitor WHERE sql_id = ‘xxx’;
– 查看SQL监控详情
SQL> SELECT * FROM v$sql_monitor WHERE sql_id = ‘xxx’ AND status = ‘EXECUTING’;
风哥提示：SQL调优工具可以帮助我们快速定位和解决SQL性能问题。掌握SQL调优工具的使用，是提高SQL优化效率的重要手段。
Part04-生产案例与实战讲解
4.1 慢查询优化案例
4.1.1 案例描述
某企业DM数据库出现慢查询问题，经分析发现某条查询语句执行时间过长，需要优化该查询语句。
4.1.2 分析步骤
# 1. 问题分析
– 查看慢查询日志
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
# slow_query.log内容
# Time: 2024-01-01 10:00:00
# User@Host: fgedu[fgedu] @ [192.168.1.100]
# Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 1000000
# SET timestamp=1704067200;
# SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(4), is_mem(FALSE)
3 #BLKUP2: [0, 1000, 0]; FGEDU_USER
4 #CSCN2: [0, 1000, 0]; FGEDU_USER
– 查看表结构
SQL> DESC fgedu_user;
Name Type Nullable
———— ———— ——–
USER_ID INT N
USER_NAME VARCHAR(50) N
USER_EMAIL VARCHAR(100) Y
USER_STATUS INT Y
CREATE_TIME TIMESTAMP Y
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
INDEX_NAME COLUMN_NAME
——————– ————
PK_FGEDU_USER USER_ID
# 2. 优化方案
– 为user_status字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化查询语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 执行优化后的查询
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(3), is_mem(FALSE)
3 #BLKUP2: [0, 1000, 0]; IDX_FGEDU_USER_STATUS(FGEDU_USER)
4 #SSEK2: [0, 1000, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
# 4. 效果验证
– 执行优化后的查询
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
USER_ID USER_NAME USER_EMAIL
——– ———- —————
1 fgedu_user1 fgedu_user1@fgedu.net.cn
2 fgedu_user2 fgedu_user2@fgedu.net.cn
3 user3 user3@fgedu.net.cn
…
– 查看执行统计信息
SQL> SELECT elapsed_time, cpu_time, buffer_gets FROM v$sql WHERE sql_id = ‘xxx’;
ELAPSED_TIME CPU_TIME BUFFER_GETS
———— ———- ————
12345 10000 100
# 5. 实施结果
– 查询时间从5.2秒降低到0.1秒
– 逻辑读从1000000降低到100
– 物理读从100000降低到0
– 查询性能提升98%
– 查看慢查询日志
$ tail -f /dm/fgdata/fgedudb/log/slow_query.log
# slow_query.log内容
# Time: 2024-01-01 10:00:00
# User@Host: fgedu[fgedu] @ [192.168.1.100]
# Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 1000000
# SET timestamp=1704067200;
# SELECT * FROM fgedu_user WHERE user_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT * FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(4), is_mem(FALSE)
3 #BLKUP2: [0, 1000, 0]; FGEDU_USER
4 #CSCN2: [0, 1000, 0]; FGEDU_USER
– 查看表结构
SQL> DESC fgedu_user;
Name Type Nullable
———— ———— ——–
USER_ID INT N
USER_NAME VARCHAR(50) N
USER_EMAIL VARCHAR(100) Y
USER_STATUS INT Y
CREATE_TIME TIMESTAMP Y
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
INDEX_NAME COLUMN_NAME
——————– ————
PK_FGEDU_USER USER_ID
# 2. 优化方案
– 为user_status字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化查询语句，只查询需要的字段
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 执行优化后的查询
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(3), is_mem(FALSE)
3 #BLKUP2: [0, 1000, 0]; IDX_FGEDU_USER_STATUS(FGEDU_USER)
4 #SSEK2: [0, 1000, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
# 4. 效果验证
– 执行优化后的查询
SQL> SELECT user_id, user_name, user_email FROM fgedu_user WHERE user_status = 1;
USER_ID USER_NAME USER_EMAIL
——– ———- —————
1 fgedu_user1 fgedu_user1@fgedu.net.cn
2 fgedu_user2 fgedu_user2@fgedu.net.cn
3 user3 user3@fgedu.net.cn
…
– 查看执行统计信息
SQL> SELECT elapsed_time, cpu_time, buffer_gets FROM v$sql WHERE sql_id = ‘xxx’;
ELAPSED_TIME CPU_TIME BUFFER_GETS
———— ———- ————
12345 10000 100
# 5. 实施结果
– 查询时间从5.2秒降低到0.1秒
– 逻辑读从1000000降低到100
– 物理读从100000降低到0
– 查询性能提升98%
4.2 复杂查询优化案例
4.2.1 案例描述
某企业DM数据库出现复杂查询性能问题，经分析发现某条复杂查询语句执行时间过长，需要优化该查询语句。
4.2.2 分析步骤
# 1. 问题分析
– 查看原始查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u, fgedu_order o, fgedu_product p
WHERE u.user_id = o.user_id
AND o.product_id = p.product_id
AND u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u, fgedu_order o, fgedu_product p
WHERE u.user_id = o.user_id
AND o.product_id = p.product_id
AND u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(5), is_mem(FALSE)
3 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
4 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
5 #CSCN2: [0, 10000, 0]; FGEDU_USER
6 #CSCN2: [0, 100000, 0]; FGEDU_ORDER
7 #CSCN2: [0, 10000, 0]; FGEDU_PRODUCT
– 查看表结构
SQL> DESC fgedu_user;
SQL> DESC fgedu_order;
SQL> DESC fgedu_product;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_ORDER’;
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_PRODUCT’;
# 2. 优化方案
– 为连接字段创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
SQL> CREATE INDEX idx_fgedu_order_product_id ON fgedu_order(product_id);
– 为查询条件字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> CREATE INDEX idx_fgedu_order_status ON fgedu_order(order_status);
SQL> CREATE INDEX idx_fgedu_product_status ON fgedu_product(product_status);
– 优化查询语句，使用JOIN代替逗号连接
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
SQL> CREATE INDEX idx_fgedu_order_product_id ON fgedu_order(product_id);
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> CREATE INDEX idx_fgedu_order_status ON fgedu_order(order_status);
SQL> CREATE INDEX idx_fgedu_product_status ON fgedu_product(product_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_order’);
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_product’);
– 执行优化后的查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(5), is_mem(FALSE)
3 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
4 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
5 #SSEK2: [0, 10000, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
6 #SSEK2: [0, 100000, 0]; scan_type(ASC), IDX_FGEDU_ORDER_STATUS(FGEDU_ORDER), scan_range[1,1]
7 #SSEK2: [0, 10000, 0]; scan_type(ASC), IDX_FGEDU_PRODUCT_STATUS(FGEDU_PRODUCT), scan_range[1,1]
# 4. 效果验证
– 执行优化后的查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
USER_ID USER_NAME ORDER_ID ORDER_AMOUNT PRODUCT_NAME
——– ———- ——— ————- —————
1 fgedu_user1 1001 100.00 product1
2 fgedu_user2 1002 200.00 product2
3 user3 1003 300.00 product3
…
– 查看执行统计信息
SQL> SELECT elapsed_time, cpu_time, buffer_gets FROM v$sql WHERE sql_id = ‘xxx’;
ELAPSED_TIME CPU_TIME BUFFER_GETS
———— ———- ————
23456 20000 1000
# 5. 实施结果
– 查询时间从10秒降低到0.5秒
– 逻辑读从1000000降低到1000
– 物理读从100000降低到0
– 查询性能提升95%
– 查看原始查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u, fgedu_order o, fgedu_product p
WHERE u.user_id = o.user_id
AND o.product_id = p.product_id
AND u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u, fgedu_order o, fgedu_product p
WHERE u.user_id = o.user_id
AND o.product_id = p.product_id
AND u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(5), is_mem(FALSE)
3 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
4 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
5 #CSCN2: [0, 10000, 0]; FGEDU_USER
6 #CSCN2: [0, 100000, 0]; FGEDU_ORDER
7 #CSCN2: [0, 10000, 0]; FGEDU_PRODUCT
– 查看表结构
SQL> DESC fgedu_user;
SQL> DESC fgedu_order;
SQL> DESC fgedu_product;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_ORDER’;
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_PRODUCT’;
# 2. 优化方案
– 为连接字段创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
SQL> CREATE INDEX idx_fgedu_order_product_id ON fgedu_order(product_id);
– 为查询条件字段创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> CREATE INDEX idx_fgedu_order_status ON fgedu_order(order_status);
SQL> CREATE INDEX idx_fgedu_product_status ON fgedu_product(product_status);
– 优化查询语句，使用JOIN代替逗号连接
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_order_user_id ON fgedu_order(user_id);
SQL> CREATE INDEX idx_fgedu_order_product_id ON fgedu_order(product_id);
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
SQL> CREATE INDEX idx_fgedu_order_status ON fgedu_order(order_status);
SQL> CREATE INDEX idx_fgedu_product_status ON fgedu_product(product_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_order’);
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_product’);
– 执行优化后的查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
– 查看执行计划
SQL> EXPLAIN SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
执行计划：
1 #NSET2: [0, 1000, 0]
2 #PRJT2: [0, 1000, 0]; exp_num(5), is_mem(FALSE)
3 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
4 #HASH2 INNER JOIN: [0, 1000, 0]; key_num(1), part_num(1)
5 #SSEK2: [0, 10000, 0]; scan_type(ASC), IDX_FGEDU_USER_STATUS(FGEDU_USER), scan_range[1,1]
6 #SSEK2: [0, 100000, 0]; scan_type(ASC), IDX_FGEDU_ORDER_STATUS(FGEDU_ORDER), scan_range[1,1]
7 #SSEK2: [0, 10000, 0]; scan_type(ASC), IDX_FGEDU_PRODUCT_STATUS(FGEDU_PRODUCT), scan_range[1,1]
# 4. 效果验证
– 执行优化后的查询
SQL> SELECT u.user_id, u.user_name, o.order_id, o.order_amount, p.product_name
FROM fgedu_user u
INNER JOIN fgedu_order o ON u.user_id = o.user_id
INNER JOIN fgedu_product p ON o.product_id = p.product_id
WHERE u.user_status = 1
AND o.order_status = 1
AND p.product_status = 1;
USER_ID USER_NAME ORDER_ID ORDER_AMOUNT PRODUCT_NAME
——– ———- ——— ————- —————
1 fgedu_user1 1001 100.00 product1
2 fgedu_user2 1002 200.00 product2
3 user3 1003 300.00 product3
…
– 查看执行统计信息
SQL> SELECT elapsed_time, cpu_time, buffer_gets FROM v$sql WHERE sql_id = ‘xxx’;
ELAPSED_TIME CPU_TIME BUFFER_GETS
———— ———- ————
23456 20000 1000
# 5. 实施结果
– 查询时间从10秒降低到0.5秒
– 逻辑读从1000000降低到1000
– 物理读从100000降低到0
– 查询性能提升95%
4.3 批量操作优化案例
4.3.1 案例描述
某企业DM数据库批量操作性能问题，经分析发现批量插入、更新、删除操作执行时间过长，需要优化批量操作。
4.3.2 分析步骤
# 1. 问题分析
– 查看原始批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (3, ‘user3’, ‘user3@fgedu.net.cn’);
…
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
– 查看执行时间
SQL> SET TIMING ON
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
Executed in 0.01 seconds
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
Executed in 0.01 seconds
…
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
Executed in 0.01 seconds
– 总执行时间：10000 * 0.01 = 100秒
# 2. 优化方案
– 使用批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’),
…
(10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
– 使用批量更新
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 10000);
– 使用批量删除
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, 3, …, 10000);
# 3. 实施步骤
– 批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’),
(4, ‘user4’, ‘user4@fgedu.net.cn’),
(5, ‘user5’, ‘user5@fgedu.net.cn’);
Executed in 0.05 seconds
– 批量更新
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, 4, 5);
Executed in 0.03 seconds
– 批量删除
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, 3, 4, 5);
Executed in 0.02 seconds
# 4. 效果验证
– 批量插入10000条记录
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
…
(10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
Executed in 10 seconds
– 批量更新10000条记录
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, …, 10000);
Executed in 5 seconds
– 批量删除10000条记录
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, …, 10000);
Executed in 3 seconds
# 5. 实施结果
– 批量插入性能提升：100秒 → 10秒，提升90%
– 批量更新性能提升：100秒 → 5秒，提升95%
– 批量删除性能提升：100秒 → 3秒，提升97%
– 查看原始批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (3, ‘user3’, ‘user3@fgedu.net.cn’);
…
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
– 查看执行时间
SQL> SET TIMING ON
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
Executed in 0.01 seconds
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’);
Executed in 0.01 seconds
…
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
Executed in 0.01 seconds
– 总执行时间：10000 * 0.01 = 100秒
# 2. 优化方案
– 使用批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’),
…
(10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
– 使用批量更新
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 10000);
– 使用批量删除
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, 3, …, 10000);
# 3. 实施步骤
– 批量插入
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’),
(4, ‘user4’, ‘user4@fgedu.net.cn’),
(5, ‘user5’, ‘user5@fgedu.net.cn’);
Executed in 0.05 seconds
– 批量更新
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, 4, 5);
Executed in 0.03 seconds
– 批量删除
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, 3, 4, 5);
Executed in 0.02 seconds
# 4. 效果验证
– 批量插入10000条记录
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
…
(10000, ‘fgedu_user10000’, ‘fgedu_user10000@fgedu.net.cn’);
Executed in 10 seconds
– 批量更新10000条记录
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, …, 10000);
Executed in 5 seconds
– 批量删除10000条记录
SQL> DELETE FROM fgedu_user WHERE user_id IN (1, 2, …, 10000);
Executed in 3 seconds
# 5. 实施结果
– 批量插入性能提升：100秒 → 10秒，提升90%
– 批量更新性能提升：100秒 → 5秒，提升95%
– 批量删除性能提升：100秒 → 3秒，提升97%
生产环境建议：根据业务需求和数据特点，优化SQL语句，提高查询性能。定期审查和优化SQL，持续提升数据库性能。使用批量操作提高效率，减少数据库资源消耗。
Part05-风哥经验总结与分享
5.1 SQL编写最佳实践
DM数据库SQL编写最佳实践：
- 遵循命名规范：使用有意义的表名、字段名，遵循命名规范
- 遵循格式规范：使用统一的格式规范，提高SQL可读性
- 避免SELECT *：只查询需要的字段，避免SELECT *
- 使用WHERE条件：使用WHERE条件过滤数据，减少数据传输
- 合理使用索引：为经常查询的字段创建索引，确保查询使用索引
- 避免子查询：避免使用子查询，使用JOIN代替
- 使用批量操作：使用批量插入、更新、删除，提高效率
- 使用LIMIT：使用LIMIT限制返回结果数量
- 定期优化SQL：定期审查和优化SQL，持续提升性能
- 使用调优工具：使用SQL调优工具，快速定位和解决问题
5.2 常见问题与解决方案
# 1. 慢查询问题
– 症状：查询执行时间过长
– 原因：缺少索引、SQL语句不合理、数据量过大
– 解决方案：创建索引、优化SQL语句、分页查询
# 2. 全表扫描问题
– 症状：查询执行计划显示全表扫描
– 原因：缺少索引、索引失效、统计信息不准确
– 解决方案：创建索引、修复索引失效、更新统计信息
# 3. 索引失效问题
– 症状：查询不使用索引
– 原因：在索引字段上使用函数、隐式类型转换、使用NOT、!=等
– 解决方案：避免在索引字段上使用函数、避免隐式类型转换、使用=、IN等
# 4. 子查询性能问题
– 症状：子查询执行时间过长
– 原因：子查询效率低、子查询结果集大
– 解决方案：使用JOIN代替子查询、使用EXISTS代替IN
# 5. 批量操作性能问题
– 症状：批量操作执行时间过长
– 原因：使用循环操作、事务过大
– 解决方案：使用批量操作、分批提交事务
# 6. 锁等待问题
– 症状：查询等待锁释放
– 原因：长事务、锁冲突
– 解决方案：优化长事务、减少锁冲突
– 症状：查询执行时间过长
– 原因：缺少索引、SQL语句不合理、数据量过大
– 解决方案：创建索引、优化SQL语句、分页查询
# 2. 全表扫描问题
– 症状：查询执行计划显示全表扫描
– 原因：缺少索引、索引失效、统计信息不准确
– 解决方案：创建索引、修复索引失效、更新统计信息
# 3. 索引失效问题
– 症状：查询不使用索引
– 原因：在索引字段上使用函数、隐式类型转换、使用NOT、!=等
– 解决方案：避免在索引字段上使用函数、避免隐式类型转换、使用=、IN等
# 4. 子查询性能问题
– 症状：子查询执行时间过长
– 原因：子查询效率低、子查询结果集大
– 解决方案：使用JOIN代替子查询、使用EXISTS代替IN
# 5. 批量操作性能问题
– 症状：批量操作执行时间过长
– 原因：使用循环操作、事务过大
– 解决方案：使用批量操作、分批提交事务
# 6. 锁等待问题
– 症状：查询等待锁释放
– 原因：长事务、锁冲突
– 解决方案：优化长事务、减少锁冲突
5.3 SQL优化检查清单
DM数据库SQL优化检查清单：
- 命名规范检查：表名、字段名、别名是否符合命名规范
- 格式规范检查：SQL语句格式是否统一、可读
- SELECT *检查：是否避免使用SELECT *
- WHERE条件检查：是否使用WHERE条件过滤数据
- 索引使用检查：查询是否使用索引
- 子查询检查：是否避免使用子查询
- 函数使用检查：是否在WHERE条件中使用函数
- 批量操作检查：是否使用批量操作
- 执行计划检查：执行计划是否合理
- 性能指标检查：执行时间、逻辑读、物理读等指标是否合理
持续改进：SQL优化是一个持续的过程，需要根据业务需求和系统运行情况，不断调整和优化SQL，确保系统的高性能和稳定性。建立完善的SQL优化体系，是数据库性能管理的保障。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
