导图社区 数据库SQL优化策略
- 26
- 0
- 0
- 举报
数据库SQL优化策略
达梦、PGSQL、Oracle数据库Sql执行计划关键字解释及优化策略，有需要的朋友赶紧收藏吧！
编辑于2024-03-20 14:05:44- SQL优化
- 优化策略
- SQL执行计划
- 相似推荐
- 大纲
数据库SQL优化策略
SQL优化概述
SQL优化的重要性
提高查询效率
减少服务器资源消耗
提高数据安全性
SQL优化的目标
减少查询时间
减少服务器资源消耗
提高数据安全性
SQL优化方法
查询优化
使用索引
创建索引
使用索引
避免全表扫描
使用LIMIT限制结果集
使用WHERE子句
避免子查询
使用JOIN代替子查询
避免使用NOT IN
使用LEFT JOIN代替NOT IN
避免使用OR
使用UNION代替OR
避免使用DISTINCT
使用GROUP BY代替DISTINCT
避免使用ORDER BY RAND()
使用其他方法生成随机值
表优化
选择合适的存储引擎
MyISAM
InnoDB
MEMORY
优化表结构
使用合适的数据类型
使用合适的字段长度
使用合适的字段类型
优化索引
选择合适的索引类型
优化索引字段顺序
优化索引字段数量
优化表分区
水平分区
垂直分区
服务器优化
优化服务器配置
内存优化
CPU优化
磁盘IO优化
优化服务器参数
优化连接数
优化缓冲区大小
优化查询缓存
优化数据库备份
定期备份数据库
使用合适的备份策略
SQL优化工具
EXPLAIN
查看查询执行计划
分析查询性能
SHOW PROFILES
查看查询性能
分析查询瓶颈
Slow Query Log
记录慢查询
分析慢查询原因
Percona Toolkit
分析查询性能
优化查询语句
MySQL Workbench
分析查询性能
优化查询语句
达梦数据库SQL执行计划
概念描述
达梦数据库跟oracle、mysql一样，也有自己的优化器，优化器会根据需要执行的sql，计算出执行该sql使用的各种方案的代价（即时间），然后会选择一个代价最低的方案，去执行SQL。也就是执行计划。
常用的查看执行计划有两种方式：
1.EXPLAIN+SQL语句
查看执行计划，在需要执行的sql前，加上explain关键字即可。
2.使用ET工具
ET是达梦数据库自带的SQL 性能分析工具，能统计执行计划中，每个操作符的时间花费（将代价显示为具体的时间），从而定位到有性能问题的操作，指导我们去优化。
测试验证
输出说明
名称：计划节点的操作符。即上面的CSCN2/PRJT2等列信息。
代价：每个操作符花费的时间，这里以数字表示。【】的第一部分
结果集：每个操作符返回的结果条数。【】的第二部分
行数据处理长度：执行节点的字节数。【】的第三部分
附加信息：每个操作符执行的操作。【】后面的部分。
常用操作符
1. PRJT2: 投影运算，用于选择表达式项的计算；广泛用于查询、排序、函数索引创建等。
2. NSET2: 结果集收集，一般是查询计划的顶层节点。
3. SLCT2: 选择运算，用于查询条件的过滤。
4. NEST LOOP INNER JOIN2（简写为NLI2）：嵌套循环内连接；没有索引可用，且无法用HASH（如不等值比较），则可能使用NLI2。
5. MERGE INNER JOIN3（简写为MI3）：归并内连接；有索引可用时，有可能使用MI3。
6. NEST LOOP FULL JOIN2（简写为NLFO2）：嵌套循环全外连接；一般不等值连接时使用。
7. NEST LOOP LEFT JOIN2（简写为NLLO2）：嵌套循环左外连接；一般不等值连接时使用。
8. HASH LEFT JOIN2（简写为HLO2）：HASH左外连接；一般等值连接时使用。
9. HASH RIGHT JOIN2（简写为HRO2）：HASH右外连接；一般等值连接时使用。
10. HASH FULL JOIN2（简写为HFO2）：HASH全外连接；等值连接时可用。
11. TOPN2：取前N个记录。
12. HASH LEFT SEMI JOIN2：HASH 左半连接； 扫描左表建立hash表，扫描右表探测HASH表，最后输出被探测到的左表的行
13. CSCN2/VSCN2 聚集索引扫描（cluster index scan）; VSCN2用于列存储表
14. DSCN Dynamic table scan; 动态视图扫描
15. DELETE/VDEL2 删除数据, 其中VDEL2用于列存储表
16. SORT3 排序
17. TEMP TABLE SPOOL临时表，临时存放数据；如delete/update时，临时存放ROWID, PK等定位信息
18. CSEK2 聚集索引数据定位；(cluster index seek)
19. SSEK2 2级索引数据定位；(second index seek)
20. BLKUP2 (Batch lookup), 使用2级别索引的定位信息， 在聚集索引中查找数据
21. NEST LOOP SEMI JOIN2 嵌套循环半连接。通常用于无法使用HASH, 索引的不等值的In/Exists；效率比较差。
22. HASH2 INNER JOIN HASH内连接（HI3）；无法利用索引时，系统一般采用HASH连接。
23. CONST VALUE LIST 系统自动创建的一个常量列表，用于与实体表做连接。
24. SSCN 索引全扫描
ET工具查看真实的执行计划
--开启ET。需要使用DBA角色
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
--关闭ET。ET会对数据库性能有影响，使用完后记得关闭以保证数据库性能不受影响。
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);
--查看ET是否开启
select * from v$parameter t where NAME = 'MONITOR_SQL_EXEC';
select * from v$parameter t where NAME = 'ENABLE_MONITOR';
--ENABLE_MONITOR，动态参数(系统级)
--MONITOR_SQL_EXEC，动态参数(会话级)
索引
函数索引
基于函数的索引促进了限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。
位图索引
位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的 where 子句中带有 AND 和 OR 谓词时，效率更加明显。
优化回表BLKUP2
执行计划的每一行，就是一个计划节点，计划节点里的操作符，就是这个计划节点干了什么事。
当select语句还要求查询建立的联合索引以外其他字段的值，因此就产生了回表操作，即二次扫描（操作符BLKUP2）。
合理的减少回表BLKUP2的计划操作，可以减少语句的执行成本，减少内存消耗，加快执行时间。
PGSQL
执行计划存储
关系型数据库通常可以使用EXPLAIN命令显示SQL的执行计划
ANALYZE，执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。
VERBOSE，显示关于计划的额外信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为FALSE。
COSTS，包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。
BUFFERS，包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为FALSE。
TIMING，在输出中包括实际启动时间以及在每个结点中花掉的时间。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为TRUE。
SUMMARY，在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用ANALYZE 时默认包含摘要信息，但默认情况下不包含摘要信息，但可以使用此选项启用摘要信息。 使用EXPLAIN EXECUTE中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。
FORMAT，指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。这个参数默认被设置为TEXT。
statement，你想查看其执行计划的任何SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS或者CREATE MATERIALIZED VIEW AS语句。
我们可以通过explain，explain analyze，explain verbose来获取执行计划
但是explain查询的当前缓存的执行计划， 在实际中估算的成本可能是不准确的，因为很可能估算的成本和你实际运行的成本不一致。而，explain analyze，explain verbose则会实际执行sql，在某些场景不会允许。
（可以尝试采用开启一个事务后，explain analyze，explain verbose查看执行计划，最后rollback）
执行计划解读
cost=0.00..22.32，0.00代表启动成本，22.32代表返回所有数据的成本。
rows=1032：表示返回多少行。
width=56，表示每行平均宽度。
actual time=0.060..1.167，实际花费的时间。
loops=1，循环的次数
Output，输出的字段名
Buffers，缓冲命中数
shared read，代表数据来自disk(磁盘)而并非cache(缓存)，当再次执行sql，会发现变成shared hit，说明数据已经在cache中
Planning Time，生成执行计划的时间
Execution Time，执行执行计划的时间
常见扫描方式
Seq Scan
全表顺序扫描
Index Only Scan
按索引顺序扫描，通过VM减少回表，绝大数情况下不需要回表
Index Scan
按索引顺序扫描，并回表
Bitmap Index Scan+Bitmap Heap Scan
按索引取得的BLOCKID排序，然后根据BLOCKID顺序回表扫描，然后再根据条件过滤掉不符合条件的记录。
这种扫描方法，主要解决了离散数据(索引字段的逻辑顺序与记录的实际存储顺序非常离散的情况)，需要大量离散回表扫描的情况。
Hash Join
哈希JOIN，较小的数据集来构建HASH表，然后用较大的数据集去做探测。
右表会先被扫描并且被载入到一个哈希表，使用连接列作为哈希键。接下来左表被扫描，扫描中找到的每一行的连接属性值被用作哈希键在哈希表中查找匹配的行。 当查询涉及两个以上的表时，最终结果必须由一个连接步骤树构成，每个连接步骤有两个输入。规划器会检查不同可能的连接序列来找到代价最小的那一个。
Nested Loop
嵌套循环。其中一个表扫描一次，另一个表则循环多次。
Semi Join（半连接-效率较低）
在半连接中，只返回左表中存在的行，而不返回右表中的行。Nested Loop Semi Join是一种使用嵌套循环的半连接方式，它首先扫描左表，然后对于左表中的每一行，扫描右表以查找匹配的行。如果找到匹配的行，则返回左表中的行，否则将继续扫描右表，直到找到匹配的行或扫描完整个右表。
它总是有两个子集：最上面的一个是外部集，下面的一个是内部集。
Merge Join
需要两个JOIN的表的KEY都是先排好顺序的，如果有索引没有排序过程。Merge Join两个表都只扫描一次。
在连接开始之前，每一个表都按照连接的列排好序。然后两个表会被并行扫描，匹配的行被整合成连接行。由于这种连接中每个表只被扫描一次。它所要求的排序可以通过一个显式的排序步骤得到，或使用一个连接键上的索引按适当顺序扫描关系得到。
pg_show_plans模块
pg_show_plans 是一个显示所有当前运行的SQL语句的查询计划的模块。
Oracle
1 概述
执行计划：sql语句执行的过程或访问路径的描述
作用：判断sql语句执行效率的依据
2 概念
优化方式
RBO（Rule-Based Optimization）
基于规则的优化器，Oracle10g已被启用
完全按照sql写法生成的执行计划，不考虑表中数据的大小
CBO（Cost-Based optimization）
基于代价的优化器，Oracle10g以后已取代RBO
计算各种可能的“代价cost"，从中选用cost最低的方案
表访问方式
table access full(全表扫描）
table access by rowid(通过rowid扫描）
table access by index scan(索引扫描）
index unique scan(索引唯一扫描）
每次至多返回一条记录
有下列两种情况（当查询字段有下列约束时）
unique
primary key
index range scan (索引引范围扫描)
每次至少返回一条记录
一般有下列三种情况
在唯一索引列上使用了范围操作符（如：> < >= <= between）
在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
对非唯一索引列上进行的任何查询
index full scan(索引全扫描)
order by 唯一索引列
index fast full scan(索引|快速扫描)
与 index full scan 类似，只是不进行排序
index skip scan (索引跳跃扫描)
必须是 组合索引
除了前导列（索引中第一列）外的其他列作为条件
表连接方式
1. 概念 表连接类型：咱写 sql 语句时，表之间的关联关系 表连接方式：执行计划里面的表连接方式 2. 表连接类型 -- 等值连接 (1) 内连接 inner join，简写 join (2) 左连接 left join (3) 右连接 right join (4) 全连接 full join 3. 表连接方式 (1) 嵌套循环 nested loops (2) 哈希连接 hash join (3) 排序合并连接 sort merge join (4) 笛卡尔积 cartesian product
nested loops（嵌套循环）
Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。对于被连接的数据子集较小的情况，nested loop连接是个较好的选择
驱动表返回几条，被驱动表访问多少次
小量数据的连接或小表大表连接
无任何限制
驱动表的限制条件和被驱动表的连接条件上创建索引
hash join (散列连接)
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
表都最多访问 1 次
大数据的连接或小表大表连接
只能使用 =
索引列无要求，与单表情况无异
sort merge join (排序合并连接)
是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配
表都最多访问 1 次
大数据的连接且有排序操作
无任何限制
索引可以消除排序
Cross join(笛卡尔连接）
如果两个表做表连接而没有连接条件，而会产生笛卡尔积，在实际工作中应该尽可能避免笛卡尔积
3 执行计划
预估的
sql 还未执行，“预估”的执行计划，并不一定准确，来自“PLAN_TABLE"
真实的
sql 已经执行，“真实”的执行计划，来自"V$SQL_PLAN"
参数说明
Starts 该 sql 执行的次数
E-Rows 预计返回的行数
A-Rows 实际返回的行数。可以和 E-Rows 比对，确定哪一步出现了问题
A-Time 每一步实际执行的时间
Buffers 每一步实际执行的逻辑读或一致性读
Reads 每一步实际执行的物理读
Writes 每一步实际执行的物理写
OMem 最优执行模式所需的内存评估值
1Mem one-pass模式所需的内存评估值
Used_Mem 则为当前操作实际执行时消耗的内存括号里面为 (发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示0)