opengauss教程FG037-openGauss SQL优化与索引优化
内容简介
本篇文章详细介绍openGauss数据库的SQL优化与索引优化，包括SQL语句的优化技巧、索引的设计原则、索引的使用方法以及相关的性能优化策略。风哥教程参考opengauss官方文档SQL参考手册和性能调优指南。
SQL优化和索引优化是数据库性能调优的重要组成部分，掌握这些优化技巧对于提高数据库性能至关重要。通过本文的学习，您将掌握openGauss SQL优化和索引优化的最佳实践。
本文通过实战案例，详细讲解SQL语句的优化技巧、索引的设计原则以及相关的性能优化策略，帮助您在生产环境中高效优化数据库性能。
目录大纲
Part01-基础概念与理论知识
1.1 SQL优化概述
SQL优化是指通过改进SQL语句的写法、使用合适的索引、调整数据库参数等方法，提高SQL语句的执行效率。SQL优化的目标是减少SQL语句的执行时间，降低系统资源的消耗。
SQL优化的主要内容包括：
- SQL语句的写法优化
- 索引的设计与使用
- 执行计划的优化
- 数据库参数的调整
- 表结构的设计优化
1.2 索引概述
索引是数据库中用于提高查询性能的数据结构，它可以帮助数据库快速定位数据，减少数据扫描的范围。索引的主要作用是加速数据的检索，提高查询性能。
索引的主要特点：
- 可以加速数据的检索
- 可以加速表与表之间的连接
- 可以强制数据的唯一性
- 会增加数据的插入、更新和删除操作的开销
- 会占用额外的存储空间
1.3 索引类型
openGauss支持多种类型的索引，主要包括：
- B树索引：最常用的索引类型，适用于等值查询、范围查询和排序操作
- 位图索引：适用于低基数列的查询
- 哈希索引：适用于等值查询，不支持范围查询
- GIN索引：适用于全文搜索和数组类型的查询
- GiST索引：适用于地理空间数据和全文搜索
- SP-GiST索引：适用于非平衡数据结构的查询
- BRIN索引：适用于大数据量的范围查询
Part02-生产环境规划与建议
2.1 SQL优化使用场景
SQL优化适用于以下场景：
- 慢SQL优化：优化执行时间较长的SQL语句
- 高并发场景：优化高并发环境下的SQL语句
- 大数据量查询：优化大数据量下的查询性能
- 系统性能调优：整体提升系统性能
- 数据库迁移：评估和优化SQL语句在新环境中的性能
风哥提示：
2.2 索引设计原则
索引设计的基本原则：
- 选择合适的列：为经常查询的列、连接条件列、排序列创建索引
- 考虑列的基数：高基数列适合创建索引，低基数列适合位图索引
- 避免过度索引：过多的索引会增加写操作的开销
- 使用复合索引：对于多列查询，使用复合索引可以提高性能
- 注意索引的顺序：复合索引的顺序应该与查询条件的顺序一致
- 考虑索引的选择性：选择性高的列适合创建索引
2.3 性能影响因素
影响SQL性能和索引性能的主要因素包括：
- 数据量：数据量越大，查询性能越差
- 索引设计：合适的索引可以显著提高性能
- SQL语句写法：不同的SQL写法会导致不同的执行计划
- 统计信息：准确的统计信息可以帮助优化器生成更好的执行计划
- 系统资源：CPU、内存、磁盘IO等系统资源的状况
- 数据库参数：数据库参数的设置会影响查询性能
Part03-生产环境项目实施方案
3.1 SQL优化策略
SQL优化的策略包括：
学习交流加群风哥微信: itpux-com
- 优化WHERE子句：避免使用NOT、!=、OR等操作符，避免在WHERE子句中使用函数
- 优化JOIN操作：使用合适的连接方式，避免笛卡尔积
- 优化聚合操作：使用合适的聚合函数，避免在聚合函数中使用DISTINCT
- 优化排序操作：为ORDER BY列创建索引，避免不必要的排序
- 优化子查询：使用连接查询替代相关子查询
- 使用绑定变量：使用绑定变量可以减少硬解析
- 限制返回结果集：使用LIMIT子句限制返回结果的数量
3.2 索引优化策略
索引优化的策略包括：
- 创建合适的索引：为经常查询的列、连接条件列、排序列创建索引
- 使用复合索引：对于多列查询，使用复合索引可以提高性能
- 注意索引的顺序：复合索引的顺序应该与查询条件的顺序一致
- 避免过度索引：过多的索引会增加写操作的开销
- 定期维护索引：定期重建索引，保持索引的健康状态
- 使用部分索引：对于特定条件的查询，使用部分索引可以提高性能
- 使用表达式索引：对于函数表达式的查询，使用表达式索引可以提高性能
3.3 复杂查询优化方法
复杂查询的优化方法包括：
- 分解复杂查询：将复杂查询分解为多个简单查询
- 使用临时表：将中间结果存储在临时表中
- 使用CTE：使用CTE提高查询的可读性和性能
- 优化子查询：使用连接查询替代相关子查询
- 使用物化视图：对于频繁执行的复杂查询，使用物化视图
- 调整查询顺序：调整表的连接顺序，优化执行计划
Part04-生产案例与实战讲解
4.1 SQL优化实战
优化WHERE子句
学习交流加群风哥QQ113257174
— 未优化的查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE salary > 8000 AND department = ‘技术部’;
— 未优化的查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE salary > 8000 AND department = ‘技术部’;
QUERY PLAN
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=3 width=36) (actual time=0.014..0.021 rows=3 loops=1)
Filter: ((salary > 8000) AND ((department)::text = ‘技术部’::text))
Rows Removed by Filter: 11
Planning Time: 0.045 ms
Execution Time: 0.035 ms
(5 rows)
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=3 width=36) (actual time=0.014..0.021 rows=3 loops=1)
Filter: ((salary > 8000) AND ((department)::text = ‘技术部’::text))
Rows Removed by Filter: 11
Planning Time: 0.045 ms
Execution Time: 0.035 ms
(5 rows)
— 优化：为department和salary列创建复合索引
fgedudb=> CREATE INDEX idx_fgedu_employee_dept_salary ON fgedu_employee(department, salary);
fgedudb=> CREATE INDEX idx_fgedu_employee_dept_salary ON fgedu_employee(department, salary);
CREATE INDEX
— 优化后的查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE salary > 8000 AND department = ‘技术部’;
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE salary > 8000 AND department = ‘技术部’;
QUERY PLAN
—————————————————————————————————————————————
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.67 rows=3 width=36) (actual time=0.022..0.028 rows=3 loops=1)
Recheck Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_fgedu_employee_dept_salary (cost=0.00..4.33 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Planning Time: 0.120 ms
Execution Time: 0.047 ms
(7 rows)
—————————————————————————————————————————————
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.67 rows=3 width=36) (actual time=0.022..0.028 rows=3 loops=1)
Recheck Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_fgedu_employee_dept_salary (cost=0.00..4.33 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Planning Time: 0.120 ms
Execution Time: 0.047 ms
(7 rows)
优化子查询
更多视频教程www.fgedu.net.cn
— 未优化的子查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee
WHERE department IN (SELECT dept_name FROM fgedu_department WHERE dept_location = ‘北京’);
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee
WHERE department IN (SELECT dept_name FROM fgedu_department WHERE dept_location = ‘北京’);
QUERY PLAN
——————————————————————————————————————————————————
Hash Semi Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.031..0.039 rows=6 loops=1)
Hash Cond: ((fgedu_employee.department)::text = (fgedu_department.dept_name)::text)
-> Seq Scan on fgedu_employee (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.121 ms
Execution Time: 0.063 ms
(9 rows)
——————————————————————————————————————————————————
Hash Semi Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.031..0.039 rows=6 loops=1)
Hash Cond: ((fgedu_employee.department)::text = (fgedu_department.dept_name)::text)
-> Seq Scan on fgedu_employee (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.121 ms
Execution Time: 0.063 ms
(9 rows)
— 优化：使用连接查询替代子查询
fgedudb=> EXPLAIN ANALYZE
SELECT e.* FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE d.dept_location = ‘北京’;
fgedudb=> EXPLAIN ANALYZE
SELECT e.* FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE d.dept_location = ‘北京’;
QUERY PLAN
——————————————————————————————————————————————————
Hash Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.030..0.038 rows=6 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.120 ms更多学习教程公众号风哥教程itpux_com
Execution Time: 0.062 ms
(9 rows)
——————————————————————————————————————————————————
Hash Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.030..0.038 rows=6 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.120 ms更多学习教程公众号风哥教程itpux_com
Execution Time: 0.062 ms
(9 rows)
4.2 索引优化实战
创建合适的索引
— 查看表结构
fgedudb=> \d fgedu_employee;
fgedudb=> \d fgedu_employee;
Table “public.fgedu_employee”
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+———+
emp_id | integer | | not null |
emp_name | character varying(50) | | |
department | character varying(50) | | |
salary | integer | | |
hire_date | date | | |
Indexes:
“fgedu_employee_pkey” PRIMARY KEY, btree (emp_id)
“idx_fgedu_employee_dept_salary” btree (department, salary)
“idx_fgedu_employee_salary” btree (salary)
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+———+
emp_id | integer | | not null |
emp_name | character varying(50) | | |
department | character varying(50) | | |
salary | integer | | |
hire_date | date | | |
Indexes:
“fgedu_employee_pkey” PRIMARY KEY, btree (emp_id)
“idx_fgedu_employee_dept_salary” btree (department, salary)
“idx_fgedu_employee_salary” btree (salary)
— 分析索引使用情况
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE hire_date > ‘2023-06-01’;
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE hire_date > ‘2023-06-01’;
from DB视频:www.itpux.com
QUERY PLAN
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=5 width=36) (actual time=0.013..0.020 rows=5 loops=1)
Filter: (hire_date > ‘2023-06-01’::date)
Rows Removed by Filter: 9
Planning Time: 0.044 ms
Execution Time: 0.034 ms
QUERY PLAN
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=5 width=36) (actual time=0.013..0.020 rows=5 loops=1)
Filter: (hire_date > ‘2023-06-01’::date)
Rows Removed by Filter: 9
Planning Time: 0.044 ms
Execution Time: 0.034 ms
— 优化：为hire_date列创建索引
fgedudb=> CREATE INDEX idx_fgedu_employee_hire_date ON fgedu_employee(hire_date);
fgedudb=> CREATE INDEX idx_fgedu_employee_hire_date ON fgedu_employee(hire_date);
CREATE INDEX
— 分析优化后的查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE hire_date > ‘2023-06-01’;
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE hire_date > ‘2023-06-01’;
QUERY PLAN
———————————————————————————————————————————-
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.69 rows=5 width=36) (actual time=0.021..0.027 rows=5 loops=1)
Recheck Cond: (hire_date > ‘2023-06-01’::date)
Heap Blocks: exact=4
-> Bitmap Index Scan on idx_fgedu_employee_hire_date (cost=0.00..4.33 rows=5 width=0) (actual time=0.014..0.014 rows=5 loops=1)
Index Cond: (hire_date > ‘2023-06-01’::date)
Planning Time: 0.116 ms
Execution Time: 0.045 ms
(7 rows)
———————————————————————————————————————————-
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.69 rows=5 width=36) (actual time=0.021..0.027 rows=5 loops=1)
Recheck Cond: (hire_date > ‘2023-06-01’::date)
Heap Blocks: exact=4
-> Bitmap Index Scan on idx_fgedu_employee_hire_date (cost=0.00..4.33 rows=5 width=0) (actual time=0.014..0.014 rows=5 loops=1)
Index Cond: (hire_date > ‘2023-06-01’::date)
Planning Time: 0.116 ms
Execution Time: 0.045 ms
(7 rows)
4.3 复杂查询优化实战
优化复杂查询
— 复杂查询
fgedudb=> EXPLAIN ANALYZE
SELECT d.dept_name, COUNT(*) AS emp_count, AVG(e.salary) AS avg_salary, MAX(e.salary) AS max_salary
FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE e.hire_date > ‘2023-01-01’
GROUP BY d.dept_name
HAVING AVG(e.salary) > 7000
ORDER BY avg_salary DESC;
fgedudb=> EXPLAIN ANALYZE
SELECT d.dept_name, COUNT(*) AS emp_count, AVG(e.salary) AS avg_salary, MAX(e.salary) AS max_salary
FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE e.hire_date > ‘2023-01-01’
GROUP BY d.dept_name
HAVING AVG(e.salary) > 7000
ORDER BY avg_salary DESC;
QUERY PLAN
———————————————————————————————————————————————————————————-
Sort (cost=53.79..53.80 rows=1 width=44) (actual time=0.073..0.074 rows=2 loops=1)
Sort Key: (avg(e.salary)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=53.76..53.78 rows=1 width=44) (actual time=0.062..0.065 rows=2 loops=1)
Group Key: d.dept_name
Filter: (avg(e.salary) > 7000)
Rows Removed by Filter: 1
-> Hash Join (cost=23.38..48.29 rows=14 width=36) (actual time=0.031..0.040 rows=14 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..23.25 rows=14 width=36) (actual time=0.014..0.020 rows=14 loops=1)
Filter: (hire_date > ‘2023-01-01’::date)
Rows Removed by Filter: 0
-> Hash (cost=23.25..23.25 rows=3 width=28) (actual time=0.011..0.011 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=3 width=28) (actual time=0.007..0.009 rows=3 loops=1)
Planning Time: 0.124 ms
Execution Time: 0.097 ms
(15 rows)
———————————————————————————————————————————————————————————-
Sort (cost=53.79..53.80 rows=1 width=44) (actual time=0.073..0.074 rows=2 loops=1)
Sort Key: (avg(e.salary)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=53.76..53.78 rows=1 width=44) (actual time=0.062..0.065 rows=2 loops=1)
Group Key: d.dept_name
Filter: (avg(e.salary) > 7000)
Rows Removed by Filter: 1
-> Hash Join (cost=23.38..48.29 rows=14 width=36) (actual time=0.031..0.040 rows=14 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..23.25 rows=14 width=36) (actual time=0.014..0.020 rows=14 loops=1)
Filter: (hire_date > ‘2023-01-01’::date)
Rows Removed by Filter: 0
-> Hash (cost=23.25..23.25 rows=3 width=28) (actual time=0.011..0.011 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=3 width=28) (actual time=0.007..0.009 rows=3 loops=1)
Planning Time: 0.124 ms
Execution Time: 0.097 ms
(15 rows)
— 优化：使用CTE提高可读性
fgedudb=> EXPLAIN ANALYZE
WITH emp_data AS (
SELECT e.department, e.salary
FROM fgedu_employee e
WHERE e.hire_date > ‘2023-01-01’
)
SELECT d.dept_name, COUNT(*) AS emp_count, AVG(ed.salary) AS avg_salary, MAX(ed.salary) AS max_salary
FROM emp_data ed
JOIN fgedu_department d ON ed.department = d.dept_name
GROUP BY d.dept_name
HAVING AVG(ed.salary) > 7000
ORDER BY avg_salary DESC;
fgedudb=> EXPLAIN ANALYZE
WITH emp_data AS (
SELECT e.department, e.salary
FROM fgedu_employee e
WHERE e.hire_date > ‘2023-01-01’
)
SELECT d.dept_name, COUNT(*) AS emp_count, AVG(ed.salary) AS avg_salary, MAX(ed.salary) AS max_salary
FROM emp_data ed
JOIN fgedu_department d ON ed.department = d.dept_name
GROUP BY d.dept_name
HAVING AVG(ed.salary) > 7000
ORDER BY avg_salary DESC;
QUERY PLAN
———————————————————————————————————————————————————————————-
Sort (cost=53.79..53.80 rows=1 width=44) (actual time=0.072..0.073 rows=2 loops=1)
Sort Key: (avg(ed.salary)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=53.76..53.78 rows=1 width=44) (actual time=0.061..0.064 rows=2 loops=1)
Group Key: d.dept_name
Filter: (avg(ed.salary) > 7000)
Rows Removed by Filter: 1
-> Hash Join (cost=23.38..48.29 rows=14 width=36) (actual time=0.031..0.040 rows=14 loops=1)
Hash Cond: ((ed.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee ed (cost=0.00..23.25 rows=14 width=36) (actual time=0.014..0.020 rows=14 loops=1)
Filter: (hire_date > ‘2023-01-01’::date)
Rows Removed by Filter: 0
-> Hash (cost=23.25..23.25 rows=3 width=28) (actual time=0.011..0.011 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=3 width=28) (actual time=0.007..0.009 rows=3 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.096 ms
(15 rows)
———————————————————————————————————————————————————————————-
Sort (cost=53.79..53.80 rows=1 width=44) (actual time=0.072..0.073 rows=2 loops=1)
Sort Key: (avg(ed.salary)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=53.76..53.78 rows=1 width=44) (actual time=0.061..0.064 rows=2 loops=1)
Group Key: d.dept_name
Filter: (avg(ed.salary) > 7000)
Rows Removed by Filter: 1
-> Hash Join (cost=23.38..48.29 rows=14 width=36) (actual time=0.031..0.040 rows=14 loops=1)
Hash Cond: ((ed.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee ed (cost=0.00..23.25 rows=14 width=36) (actual time=0.014..0.020 rows=14 loops=1)
Filter: (hire_date > ‘2023-01-01’::date)
Rows Removed by Filter: 0
-> Hash (cost=23.25..23.25 rows=3 width=28) (actual time=0.011..0.011 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=3 width=28) (actual time=0.007..0.009 rows=3 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.096 ms
(15 rows)
4.4 性能对比测试
有索引 vs 无索引性能对比
— 无索引查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE department = ‘技术部’ AND salary > 8000;
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE department = ‘技术部’ AND salary > 8000;
QUERY PLAN
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=3 width=36) (actual time=0.014..0.021 rows=3 loops=1)
Filter: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Rows Removed by Filter: 11
Planning Time: 0.045 ms
Execution Time: 0.035 ms
(5 rows)
—————————————————————————————————————————–
Seq Scan on fgedu_employee (cost=0.00..22.75 rows=3 width=36) (actual time=0.014..0.021 rows=3 loops=1)
Filter: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Rows Removed by Filter: 11
Planning Time: 0.045 ms
Execution Time: 0.035 ms
(5 rows)
— 有索引查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE department = ‘技术部’ AND salary > 8000;
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee WHERE department = ‘技术部’ AND salary > 8000;
QUERY PLAN
—————————————————————————————————————————————
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.67 rows=3 width=36) (actual time=0.022..0.028 rows=3 loops=1)
Recheck Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_fgedu_employee_dept_salary (cost=0.00..4.33 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Planning Time: 0.120 ms
Execution Time: 0.047 ms
(7 rows)
—————————————————————————————————————————————
Bitmap Heap Scan on fgedu_employee (cost=4.33..13.67 rows=3 width=36) (actual time=0.022..0.028 rows=3 loops=1)
Recheck Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_fgedu_employee_dept_salary (cost=0.00..4.33 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (((department)::text = ‘技术部’::text) AND (salary > 8000))
Planning Time: 0.120 ms
Execution Time: 0.047 ms
(7 rows)
子查询 vs 连接查询性能对比
— 子查询
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee
WHERE department IN (SELECT dept_name FROM fgedu_department WHERE dept_location = ‘北京’);
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee
WHERE department IN (SELECT dept_name FROM fgedu_department WHERE dept_location = ‘北京’);
QUERY PLAN
——————————————————————————————————————————————————
Hash Semi Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.031..0.039 rows=6 loops=1)
Hash Cond: ((fgedu_employee.department)::text = (fgedu_department.dept_name)::text)
-> Seq Scan on fgedu_employee (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.121 ms
Execution Time: 0.063 ms
(9 rows)
——————————————————————————————————————————————————
Hash Semi Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.031..0.039 rows=6 loops=1)
Hash Cond: ((fgedu_employee.department)::text = (fgedu_department.dept_name)::text)
-> Seq Scan on fgedu_employee (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.121 ms
Execution Time: 0.063 ms
(9 rows)
— 连接查询
fgedudb=> EXPLAIN ANALYZE
SELECT e.* FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE d.dept_location = ‘北京’;
fgedudb=> EXPLAIN ANALYZE
SELECT e.* FROM fgedu_employee e
JOIN fgedu_department d ON e.department = d.dept_name
WHERE d.dept_location = ‘北京’;
QUERY PLAN
——————————————————————————————————————————————————
Hash Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.030..0.038 rows=6 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.120 ms
Execution Time: 0.062 ms
(9 rows)
——————————————————————————————————————————————————
Hash Join (cost=23.38..47.89 rows=6 width=36) (actual time=0.030..0.038 rows=6 loops=1)
Hash Cond: ((e.department)::text = (d.dept_name)::text)
-> Seq Scan on fgedu_employee e (cost=0.00..22.75 rows=14 width=36) (actual time=0.013..0.019 rows=14 loops=1)
-> Hash (cost=23.25..23.25 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_department d (cost=0.00..23.25 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)
Filter: ((dept_location)::text = ‘北京’::text)
Rows Removed by Filter: 2
Planning Time: 0.120 ms
Execution Time: 0.062 ms
(9 rows)
Part05-风哥经验总结与分享
5.1 SQL优化最佳实践
- 优化WHERE子句：避免使用NOT、!=、OR等操作符，避免在WHERE子句中使用函数
- 优化JOIN操作：使用合适的连接方式，避免笛卡尔积
- 优化聚合操作：使用合适的聚合函数，避免在聚合函数中使用DISTINCT
- 优化排序操作：为ORDER BY列创建索引，避免不必要的排序
- 优化子查询：使用连接查询替代相关子查询
- 使用绑定变量：使用绑定变量可以减少硬解析
- 限制返回结果集：使用LIMIT子句限制返回结果的数量
- 定期分析执行计划：定期分析系统中的慢SQL执行计划
5.2 索引优化最佳实践
- 创建合适的索引：为经常查询的列、连接条件列、排序列创建索引
- 使用复合索引：对于多列查询，使用复合索引可以提高性能
- 注意索引的顺序：复合索引的顺序应该与查询条件的顺序一致
- 避免过度索引：过多的索引会增加写操作的开销
- 定期维护索引：定期重建索引，保持索引的健康状态
- 使用部分索引：对于特定条件的查询，使用部分索引可以提高性能
- 使用表达式索引：对于函数表达式的查询，使用表达式索引可以提高性能
- 分析索引使用情况：定期分析索引的使用情况，删除 unused 的索引
5.3 生产环境使用建议
- 建立SQL审核机制：建立SQL审核机制，确保SQL语句的质量
- 监控慢SQL：定期监控系统中的慢SQL，及时优化
- 定期维护索引：定期重建索引，保持索引的健康状态
- 更新统计信息：定期执行ANALYZE命令更新统计信息
- 优化表结构：根据业务需求优化表结构，避免冗余字段
- 使用分区表：对于大表，使用分区表可以提高查询性能
- 调整数据库参数：根据系统需求调整数据库参数，如work_mem、random_page_cost等
- 培训开发人员：培训开发人员了解SQL优化和索引优化的基本知识
风哥提示：在生产环境中，SQL优化和索引优化是数据库性能调优的重要组成部分。要定期分析执行计划，找出性能瓶颈，并采取相应的优化措施。同时，要注意索引的维护和统计信息的更新，确保优化器能够生成准确的执行计划。
在优化SQL语句时，要关注WHERE子句、JOIN操作、聚合操作等因素，风哥提示：避免在WHERE子句中使用函数可以提高查询性能。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息