遇到慢SQL该怎么办？（下）(慢sql怎么处理)

表本身包含大量数据
尽管openGauss对于大的行存表处理性能非常优秀，但表本身的数据情况依然是导致慢SQL的重要原因。一般来说，具有以下几种情况：

1. 表的数据量很大，且很少被缓存，导致语句需要扫描的元组很多；

2. 表的数据量很大，在修改、删除数据时需要修改较多的元组；

3. 向表中插入的数据量很大；

4. 业务上需要检索出的数据量很多；

5. 频繁的数据修改，导致表中存在很多死元组（dead tuple），影响扫描性能；

表的数据量较大导致的慢SQL问题，一般需要从业务上进行入手，直接通过修改数据库来达到优化慢SQL的目的是很难实现的。因此，需要用户分析具体的业务，对业务数据进行冷热分离、分库分表、使用分布式中间件等。如果希望在数据库层进行优化，则可以通过增加宿主机的内存，进而增加max_process_memory、shared_buffers、work_mem等的大小；使用性能更佳的磁盘；适当创建索引；使用表空间调整磁盘布局等。

SQL语句写得很差
由SQL语句写法问题导致的慢SQL也相对多见，这类写得比较差的慢SQL也被俗称为“烂SQL”。多数情况都下，由“烂SQL”导致的索引失效的问题较多，对于这种情况，可参考前面的描述对SQL语句进行改写，使其能够使用到索引。

除了修改慢SQL使其能够使用索引，下面还列出了几种比较常见的、可能优化openGauss数据库性能的SQL改写规则：

改写规则

改写条件

改写说明

原始查询语句示例

改写后语句示例

将'select distinct *'改写为'select *'

所查询表格含唯一列或主键

通过确定tuple无重复,去掉distinct，从而省去去重步骤，提升效率

select distinct * from bmsql_customer limit 10;

select * from bmsql_customer limit 10;

将having子句中条件放到where子句中

将谓词表达式提前，可有效缩减group时的数据集

select cfg_name from bmsql_config group by cfg_name having cfg_name='1'

select cfg_name from bmsql_config where cfg_name = '1' group by cfg_name

简化where子句中谓词表达式

某些复杂谓词无法有效触发openGauss内的rewrite逻辑，无法使用索引扫描

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id + 1> 3

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id > 2

将order by或group by中的无用列去掉

group by或order by涉及的列包含在where子句中的等值表达式中

去掉无用字段，SQL更为简洁

select cfg_name from bmsql_config where cfg_name='2' group by cfg_name order by cfg_name, cfg_value

select cfg_name from bmsql_config where cfg_name = '2' order by cfg_value

去掉where子句中永为真的表达式

去掉无用字段，SQL更为简洁

select * from bmsql_config where 1=1 and 2=2 limit 10

select * from bmsql_config limit 10

将union转换为union all

避免了去重带来的执行代价

select * from bmsql_config union select * from bmsql_config

select * from bmsql_config union all select * from bmsql_config

将delete语句转换为truncate语句

无where子句

将DML语句转换为DDL语句，一次性回收表空间，执行速度更快

delete from bmsql_config

truncate table bmsql_config

将where子句中'or'连接的等式转换为'in'结构

'in'结构可加快过滤速度

select * from bmsql_stock where s_w_id=10 or s_w_id=1 or s_w_id=100 or s_i_id=1 or s_i_id=10

select * from bmsql_stock where s_w_id in (1,10,100) or s_i_id in(1,10)

将self join查询拆分为效率更高两个子查询

1) self join查询。

2) where子句包含相同列差值的范围查询。

例如1<a.id-b.id<10，其中a,b为同一个表的两个alias。

通过等值谓词加快查询速度

select a.c_id from bmsql_customer a, bmsql_customer b where a.c_id - b.c_id <= 20 and a.c_id > b.c_id

select * from (select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20) and a.c_id > b.c_id union all select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20 + 1) and a.c_id - b.c_id <= 20)

对于业务系统，SQL语句上线之前的审计工作基本都可以覆盖上述的场景，业内也具备很多对SQL语句进行改写的工具，不过这些工具的一些改写规则并不是绝对意义上的等值改写。而且，很多改写条件对于openGauss来说不见得有效，因为openGauss在数据库内部也存在rewrite逻辑。

DBMind平台会进一步演进SQL语句的智能改写功能，提供给用户在线的交互式智能查询改写能力，预计在未来的版本中与用户见面。

总结
我们在上面已经列出了能够导致慢SQL的原因，基本覆盖了在openGauss上造成慢SQL的大多数原因。不过，one-by-one 手动地进行慢SQL检查对于用户来说工作量确实太大。故而，openGauss的DBMind功能本身已经集成了对慢SQL进行智能根因识别的能力，用户可以通过运行下述命令在后台启动慢SQL根因分析功能（需要首先部署Prometheus以及expoter，以便能够采集到监控指标）：

gs_dbmind service start -c confpath --only-run slow_query_diagnosis
注：显式指定 --only-run 参数可以仅启动被选择的DBMind服务项

被诊断后的慢SQL会存储在元数据库（存放诊断结果的数据库）中，用户可以通过下述命令查看：

gs_dbmind component slow_query_diagnosis show -c confpath --query SQL --start-time timestamps0 --end-time timestamps1
也可以通过与Grafana联合来展示慢SQL的分析结果，DBMind也提供了简单的Grafana配置模板，可供用户参考：

https://github.com/opengauss-mirror/openGauss-server/blob/master/src/gausskernel/dbmind/tools/misc/grafana-template-slow-query-analysis.json
由于openGauss官方网站的发行包中的DBMind可能滞后于代码托管平台（gitee或github）上的最新代码，直接编译openGauss又需要花费很多的时间。故而，如果用户只是想单纯提取最新的DBMind功能，可以通过下面的Linux命令来实现：

git clone -b master --depth 1 https://gitee.com/opengauss/openGauss-server.git
cd openGauss-server/src/gausskernel/dbmind/
mv tools dbmind
tar zcf dbmind.tar.gz gs_dbmind dbmind
将生成的dbmind.tar.gz 压缩包在合适的部署位置解压即可。

当然，如果用户希望手动检查一下慢SQL的原因，也可以根据附表的检查项来检查慢SQL的产生原因。

附表：慢SQL检查列表

检查项

检查方式（系统表或系统视图）

检查方法

语句执行时存在锁竞争

dbe_perf.statement_history(start_time,finish_time, query)、pg_locks(pid, mode, locktype, grant)、pg_stat_activity(xact_start, query_start, query, pid)

查询在语句执行期间是否被阻塞。

表中死元组占比超过设定阈值

dbe_perf.statement_history(query, dbname, schemaname)

pg_stat_users_tables(relname, schemaname,n_live_tup, n_dead_tup)

n_dead_tup / n_live_tup，占比超过阈值则认为表过度膨胀 (默认阈值：0.2)。

语句扫描行数较多

dbe_perf.statement_history(n_tuples_fetched, n_tuples_returned, n_live_tup, n_dead_tup)

n_tuples_fetched+n_tuples_returned，超过阈值则认为过大（默认阈值：10000）。

语句缓存命中率较低

dbe_perf.statement_history(n_blocks_fetched, n_blocks_hit)

n_block_hit / n_block_fetched，小于阈值则认为较低（默认阈值：0.95）

慢SQL(delete、insert、update)相关表存在冗余索引

dbe_perf.statement_history(dbname, schemaname, query), pg_stat_user_indexes(schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch)

pg_indexes(schemaname, tablename, indexname, indexdef)

SQL相关表满足：① 不是唯一索引；② (idx_scan, idx_tup_read,idx_tup_fetch)=(0,0,0)；③ 索引不在数据库的（'pg_catalog', 'information_schema','snapshot', 'dbe_pldeveloper'）schema下。如果满足则认为次索引为冗余索引，否则为有效索引。

更新数据量较多

dbe_perf.statement_history(query, n_tuples_updated)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_updated超过阈值则认为更新数据量较多（默认阈值：1000）。

插入数据量较多

dbe_perf.statement_history(query, n_tuples_inserted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_inserted超过阈值则认为插入数据量较多（默认阈值：1000）。

删除数据量较多

dbe_perf.statement_history(query, n_tuples_deleted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_deleted超过阈值则认为删除数据量较多（默认阈值：1000）。