SQL性能调优经验总结-EW帮帮网

SQL调优

一概述

数据库在优化SQL的时候，并不总是能得到最优的执行路径，有时会遇到执行计划选择不准确，某些优化不支持等情况，因此在使用数据库的过程中经常会遇到SQL性能的问题，此时可以通过分析SQL的执行计划，来发现SQL是在那个阶段执行慢，然后再进行相应的优化。

下面以LightDB为例，先介绍下SQL性能分析的方法，然后再介绍下我了解的以及工作过程中使用过的SQL调优方法。

二基础知识

SQL调优主要是优化查询部分性能，其他还包括导数据，插入等的性能调优。下面先介绍一些基本知识。

SQL执行机制

SQL语句在数据库中的执行主要分为如下几个部分：

词法&语法解析通过Flex + Bison生成解析树
语义分析结合元数据对解析树进行处理生成查询树，
查询重写根据规则对查询树进行重写，比如对视图的查询，改写为对基表的查询
查询优化对查询树进行等价改写并根据代价模型生成执行计划树
查询执行从底向上执行计划树

在这里插入图片描述

执行计划

执行计划是在上述的查询优化阶段生成的代价最小的执行路径，是数据库为运行SQL语句而执行的操作步骤序列。

执行计划是SQL性能分析及调优的核心，通过执行计划可以了解到如下信息：：

表的访问方法
表连接的方法
表连接的顺序
过滤条件，连接条件，排序，聚合等操作

分析执行计划

Explain

通过explain 命令展示SQL的执行计划及语句的耗时等情况。然后我们可以对explain的结果进行分析。

在分析时可以通过裁剪SQL来简化分析。比如：

去掉外面的嵌套SQL，比如select * from (select from t1 join t2 on t1.key1=t2.key1)
先explain一下，看看具体是慢在那部分，然后再拆分出最慢的部分分析。

Explain 用法

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

这里 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]   默认TRUE
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]  默认TRUE
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

不同的选项可以展示不同的数据，下面对 ANALYZE, BUFFERS进行介绍。其他选项可以参考官方文档.

ANALYZE 会实际执行SQL，并显示实际的运行时间和其他统计信息。默认为false。
BUFFERS 显示缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、临时块读取和写入的次数以及track_io_timing启用时读取和写入数据文件块所用的时间（毫秒）。一次命中表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据；本地块包含着来自于临时表和索引的数据；而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入*块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子结点所用到的块数。在文本格式中，只会打印非零值。它默认为FALSE。

使用例子

create table t1(key1 int, key2 int, key3 int, key4 text, key5 timestamp);
create table t2(key1 int, key2 text, key3 timestamp);
create table t3(key1 int, key2 text, key3 timestamp);

insert into t1 select (random()*100)::integer, (random()*100)::integer, (random()*100)::integer, md5(random()::text) , now() - ((random()*1000)::integer||' day')::interval as testtime FROM generate_series(1, 1000) as s; 
insert into t2 select (random()*100)::integer, md5(random()::text) , now() - ((random()*1000)::integer||' day')::interval as testtime FROM generate_series(1, 1000) as s; 
insert into t3 select (random()*100)::integer, md5(random()::text) , now() - ((random()*1000)::integer||' day')::interval as testtime FROM generate_series(1, 1000) as s; 

create index i_t1_key1 on t1(key1);
create index i_t1_key2 on t1(key2);
create index i_t1_key3 on t1(key3);
create index i_t2_key1 on t2(key1);
create index i_t3_key1 on t3(key1);

analyze t1;
analyze t2;
analyze t3;

explain (analyze true, buffers true)
select t1.key2,t2.key2,t3.key2  from
  t1 join t2 on t1.key1=t2.key1
  join t3 on t1.key2=t3.key1
  where t2.key3 > now() - ((random()*1000)::integer||' day')::interval
  and t1.key3 = 2;

在这里插入图片描述

关键字解读：

cost=33.05..68.84  为当前节点估计的代价， 其中33.05为启动代价（读取到第一条数据之前的代价），68.84为总的代价
actual time=0.834..1.485 为当前节点的实际执行的时间，也分为启动时间和总执行时间
rows 为当前节点向上返回的行数
width 为每行平均宽度
loops 为循环次数
buffers： 
	shared read 表示共享块读取数（从磁盘读取）
	shared hint 共享块命中数
	shared dirtied 共享块标记为脏数
	shared written 共享块写入数（写入磁盘）
	本地块，临时块也有上述数据
I/O Timings 读写磁盘时间 毫秒
planning Time 生成执行计划的时间
Execution Time 执行执行计划的时间

在第二次执行的时候，由下面的截图可以知道数据都在缓存中了，都命中了

在这里插入图片描述

三 SQL性能优化点

1. 合理使用 analyze 命令

analyze 用于收集表的统计信息，自动vacuum进程在执行vacuum时，当表数据修改到达阈值时会自动执行analyze。目前自动vacuum进程每隔autovacuum_naptime(默认15min)秒尝试在每个数据库中启动一个工作者执行vacuum（因此，如果安装中有*N个数据库，则每 autovacuum_naptime/N*秒将启动一个新的工作者）。在同一时间只允许最多autovacuum_max_workers(默认3)个工作者进程运行。如果有超过autovacuum_max_workers个数据库需要被处理，下一个数据库将在第一个工作者结束后马上被处理。

当表中数据大量变化，统计信息与实际的数据会有较大差异，此时生成的执行计划就可能不准，最好手动执行下vacuum analyze table_name。

2. 检查索引使用情况

2.1 索引是否存在

当通过explain 分析表扫描为seqscan且过滤数据较多，耗时较久, 就要注意相应字段上是否创建了索引。
在这里插入图片描述

如果过滤的数据量少，即使有索引，也不会走索引，如下所示：

在这里插入图片描述

需要注意的是加索引会导致写入性能下降，因为写入也要写入索引，不能乱加索引。

2.2 最左前缀

最左前缀

pg 不需要满足最左前缀也能使用索引，但效率不高，因为如果不满足最左前缀实际是使用索引进行全表扫描。比如索引（a,b）,用 b 进行查找，会扫描所有叶子节点。
mysql 不满足最左前缀不会走索引

在这里插入图片描述

2.3 保证值的数据类型和字段数据类型一致

不一致不会使用索引，不使用索引大多数情况下都会导致执行变慢

在这里插入图片描述

3. 是否使用并行查询

一般情况下，并行查询可以极高的提升查询效率。但某些情况下，本可以使用并行查询却不能并行执行，或并行度不够，一般有如下几个原因：

使用了被标记为PARALLEL UNSAFE的函数，用户自定义的函数在能并行执行时，需要标记为parallel safe（什么时候可以标记，参考官方文档，搜索parallel safe）,以便并行执行。
worker 进程总数超过了 max_worker_processes（后台进程最大数量，默认为CPU * 2），并发创建的进程归属于后台进程
用于并发的后台进程总数超过 max_parallel_workers（默认为CPU * 2）。
The client sends an Execute message with a non-zero fetch count. 如 jdbc 设置了 fetchsize 或者maxrows。
表或索引大小为到达并行阈值，min_parallel_table_scan_size（默认2GB）/min_parallel_index_scan_size（默认128MB），可以根据需要进行调整

4. 调整过滤条件顺序（调整连接顺序）

有时候有些过滤条件耗时过大，比如过滤条件中有子查询，此时可以选择先用其他条件过滤减少行数，然后再用此条件过滤。

比如下面的SQL(LightDB-A下)，通过调整连接顺序，让其他表先连接，先过滤，然后在执行耗时大的过滤条件, 由原先的几小时跑不出来，改为几秒执行结束：

SELECT DISTINCT A.RYDM, X.RQ
FROM A
INNER JOIN X ON 1 = 1
INNER JOIN C ON A.INBBM = C.INBBM AND C.GKBZ = 3
WHERE C.JSID >= 0
          AND C.JSID < 721779131067
          AND A.GKBZ = 3
          AND A.ZWMC = 1
          AND X.RQ BETWEEN A.RQKSRQ AND COALESCE(A.RQJSRQ, '9999-9-9')
          AND NOT EXISTS(
                SELECT 1 FROM D WHERE D.PersonalCode = A.RYDM AND D.EndDate = X.RQ)
          AND C.JZRQ = (SELECT JZRQ
                        FROM B
                        WHERE A.INBBM = INBBM
                          AND GKBZ = 3
                          AND JZRQ <= X.RQ
                        ORDER BY JZRQ DESC
                        LIMIT 1)

直接执行，会先关联 a 和 c 表，此时关联后结果集较大，然后再与x表关联，用C.JZRQ = ()过滤，耗时极大，而如果先对A表和X表进行关联（通过添加/*+leading(C (A X))*/），结果集较小（实际为0），再与C表关联，然后用C.JZRQ = ()过滤，耗时较小。

5. 消除排序

显式的排序操作一般都会导致执行变慢，可以通过如下两种方式消除：

索引查出的数据是有序的，因此可以通过添加索引来消除（通过索引查出的数据是有序的）

在这里插入图片描述

有时存在索引，仍然会有排序，此时可能是因为选择了hashjoin，可以通过hint强制使用nestloop 或mergejoin

6. 数据库参数是否合理

通过查看执行计划可以发现存在tmp read/write，说明缓存不够，有磁盘读写，可以考虑调整work_mem参数。

在这里插入图片描述

需要注意的是work_mem 为单个进程的工作内存，最大使用内存可能会到max_connections*work_mem。在设置的时候需要考虑这一点。

其他内存相关参数还有：maintenance_work_mem，shared_buffers， temp_buffers等**，**具体可以看附录的性能相关参数

7. 设置优化器相关 guc & 使用hint

如下所示通过查看执行计划发现表关联方式不合适时，可以通过使用hint，或调整 guc 参数来控制表关联方式。

下面是某条SQL的部分执行计划，此SQL要跑300s左右。

在这里插入图片描述

通过使用hashjoin hint 或设置enable_nestloop=false , 这条SQL只需要跑4s左右。

8.1 LightDB支持hint

可通过官方参考文档查询

在这里插入图片描述

8.2 优化相关的GUC参数

可以通过如下SQL查询查询优化相关的参数

select name from pg_settings where category like 'Query Tuning%';

或者使用show enable% 查看路径启用选项

在这里插入图片描述

9. 扩展统计信息

由规划器通常会假设多个条件是彼此独立的，这种假设在列值相互关联的情况下是不成立的，这样会导致使用多个列去过滤数据时，会估计的不准。可以通过[CREATE STATISTICS](http://www.light-pg.com/docs/lightdb-cn/current/sql-createstatistics.html)创建扩展统计信息对象来，来得到更好的估计值。可参考扩展统计信息

10. 改写SQL

10.1 表尽量通过显示join 关联（planning time 过长）

如下所示在表多的时候，若干通过逗号分隔表进行关联会可能导致生成执行计划的时间过长。

在这里插入图片描述

可以通过改成显示的join on , 这是因为在join on 下join_collapse_limit 参数会限制同一层表的最大关联个数，默认值为8，如果不是join on ，就没有限制，就会生成很多的关联路径。

在这里插入图片描述

可以参考此文档。

10.2 去掉标量子查询

标量子查询类似于nestloop, 对于符合条件的每一行，都会执行一次子查询，在LightDB-A 中对性能影响尤其明显，因为每此子查询都需要网络交互（LightDB-X 不需网络交互，且会通过参数化路径优化）。

可以改为left join等形式去掉标量子查询。比如某一SQL，在LightDB-A 中要执行几个小时，通过修改只需几秒即可。下面为简化后的示例：

--修改前
SELECT
(SELECT UnitNVRestored
                FROM B
                WHERE InnerCode = A.InnerCode
                  AND TradingDay >= A.AccessionDate
                ORDER BY TradingDay ASC
                LIMIT 1) AS BeginUnitNV,
(SELECT UnitNVRestored
                FROM B
                where InnerCode = A.InnerCode
                  AND TradingDay <= COALESCE(A.DimissionDate, '9999-1-1')
                ORDER BY TradingDay DESC
                limit 1) AS EndUnitNV,
A.xxx
from A 
JOIN D ON A.InnerCode = D.InnerCode;

-- 修改后
select f1.BeginUnitNV , f2.EndUnitNV,f1.xxx from 
(select 
UnitNVRestored BeginUnitNV,
               A.*,row_number() over(partition by A.InnerCode, A.id order by TradingDay) rn
        from A
          JOIN D ON A.InnerCode = D.InnerCode
          left join B X1 on X1.InnerCode = A.InnerCode
                  AND X1.TradingDay >= A.AccessionDate
) f1, 
(select A.innercode, A.id, UnitNVRestored EndUnitNV, 
        row_number() over(partition by A.InnerCode, A.id order by TradingDay DESC) rn
        from A JOIN D ON A.InnerCode = D.InnerCode
        left join B X1 on X1.InnerCode = A.InnerCode
        AND X1.TradingDay <= COALESCE(A.DimissionDate, '9999-1-1')
) f2
where f1.rn=1 and f2.rn=1 and f1.InnerCode = f2.InnerCode and f1.id = f2.id;

10.3 转换内连接为半连接

比如下面的SQL：

SELECT MIN(B.UnitNVRestored) AS UnitNVRestored, B.InnerCode 
      FROM B,  A 
      WHERE B.InnerCode = A.Code and B.TradingDay >= A.BeginDay GROUP BY B.InnerCode

B表数据量为10万级别， A表数据量为千万级别，由于表数据量很大，关联后没group前的临时表能到达10亿级别，此时会用到磁盘文件，因此执行很慢，由于查询的都为B表的字段，可以通过把SQL转为semijoin形式,修改如下：

SELECT MIN(B.UnitNVRestored) AS UnitNVRestored, B.InnerCode 
      FROM  B WHERE exists(select 1 from A 
      WHERE B.InnerCode = A.Code and B.TradingDay >= A.BeginDay) GROUP BY B.InnerCode;

此时只需管理10万级别次数，且只需在B表中找到一条相同数据即可（原先innerjoin 需要找出所有数据）

10.4 not in 不能转为antijoin

在LightDB-X (截止23.4) 中 not in 不能上拉，也即不能转换为 antijoin 执行，可以改为使用not exists （not exists 可以被优化为antjoin 执行）。

10.5 or 转为使用union

使用or 时，数据库不能使用索引来进行查询，通过改成union,可以利用索引，Oracle 有把or 自动转换为union 的优化，LightDB 目前还没有。

如果条件互斥，可以使用union all， union all 没有去重操作，效率更高。

Note: 需要注意的是转换为union后SQL是否等价，如原先的查询查出来的行中有字段都相同的行（select * from t1 where a=1 or b=1），那么改成union后会去重，导致行数变少。此时可以考虑使用rowid作为查询字段，使行唯一，不被去重。

create table t1 (key1 int, key2 int, key3 int);
insert into t1 values(1,2,1);
insert into t1 values(2,1,1);

lightdb@test_o=# select key3 from t1 where key1=1 or key2=1;
 key3 
------
    1
    1
(2 rows)
lightdb@test_o=# select key3 from t1 where key1=1 union select key3 from t1 where key2=1;
 key3 
------
    1
(1 row)
lightdb@test_o=# select key3 from (select rowid, key3 from t1 where key1=1 union select rowid ,key3 from t1 where key2=1);
 key3 
------
    1
    1
(2 rows)

11 导数据性能优化

11. 1 批量提交数据

begin;
insert;
insert;
...

commit;

11.2 使用copy

先删索引导数据，再创建索引

11.3 增加`max_wal_size`

临时增大max_wal_size配置变量也可以让大量数据载入更快。这是因为向LightDB中载入大量的数据将导致检查点的发生比平常（由checkpoint_timeout配置变量指定）更频繁。无论何时发生一个检查点时，所有脏页都必须被刷写到磁盘上。通过在批量数据载入时临时增加max_wal_size，所需的检查点数目可以被缩减。

shared_buffers 系统内存的 25%~40%

在这里插入图片描述

四附录

性能相关参数


参数	含义	调整思路
shared_buffers（int）	LightDB共享内存缓冲区大小，该缓冲区为所有连接共用。默认设置为内存的15%	提高该值，可以减少磁盘I/O，
work_mem(int)	内部排序和hash操作使用的工作内存大小，连接不共用。TP模式下为4MB，AP模式下为内存的0.2%	在使用到临时磁盘文件时，可以考虑提高该值。
hash_mem_multiplier (浮点)	用于计算基于哈希的操作可以使用的最大内存量：work_mem*hash_mem_multiplier, 默认值为1.0	在使用到临时磁盘文件时，可以考虑提高该值。在不增加work_mem的情况下，可以增大hash_mem_multiplier来增加hash使用的内存
maintenance_work_mem(int)	指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存量。TP模式下为64MB，AP模式下为内存的0.1%	在create index 慢时可以考虑调大此参数
wal_buffers（int）	日志缓冲区，日志缓冲区的大小。默认设置为128MB	单事务的数据修改量大于wal_buffers,可以考虑调大。并发短事务较多，可以考虑调大。
effective_cache_size(int)	优化器对一个查询可以使用的磁盘缓存的估计，默认值为内存的70%	值越大，越有可能使用索引扫描；而如果值很小，则会使用顺序扫描。一般不用调。
commit_delay（int）	日志写到wal_buffer上到wal_buffer写到磁盘的时间间隔。与commit_sibling配合使用。默认10微妙	如果并发的写事务较多，可以调大此值
commit_siblings（int）	在执行commit_delay延迟时，要求的并发活动事务的最小数目。没有达到此阈值，不会等待commit_delay。默认5.	在高并行写情况下，调大此值，可以提高一次wal刷盘的组提交事务数。

SQL性能调优经验总结

SQL调优

一 概述

二 基础知识

SQL执行机制

执行计划

分析执行计划

Explain

Explain 用法

使用例子

三 SQL性能优化点

1. 合理使用 analyze 命令

2. 检查索引使用情况

2.1 索引是否存在

2.2 最左前缀

2.3 保证值的数据类型和字段数据类型一致

3. 是否使用并行查询

4. 调整过滤条件顺序（调整连接顺序）

5. 消除排序

6. 数据库参数是否合理

7. 设置优化器相关 guc & 使用hint

8.1 LightDB支持hint

8.2 优化相关的GUC参数

9. 扩展统计信息

10. 改写SQL

10.1 表尽量通过显示join 关联（planning time 过长）

10.2 去掉标量子查询

10.3 转换内连接为半连接

10.4 not in 不能转为antijoin

10.5 or 转为使用union

11 导数据性能优化

11. 1 批量提交数据

11.2 使用copy

先删索引导数据，再创建索引

11.3 增加max_wal_size

四 附录

性能相关参数

网站公告

今日签到

热门文章

最新发布

一概述

二基础知识

11.3 增加`max_wal_size`

四附录