AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 性能调优指南 性能调优最佳实践 SQL调优思路

SQL调优思路

更新时间：
产品详情
我的收藏

本文介绍多种SQL调优的思路。

缺少索引

索引缺失，通过执行计划发现SQL没有使用索引，查询效率低，创建索引后性能提升。示例如下：

  1. 准备测试数据。

    CREATE TABLE a(id int);
INSERT INTO a SELECT generate_series(1,1000000);

  2. 执行查询计划。

    • 不添加索引。

      EXPLAIN ANALYZE SELECT * FROM a WHERE id = 10;

      返回结果如下：

                                                          QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12315.50 rows=5875 width=4) (actual time=0.574..52.157 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on a  (cost=0.00..10728.00 rows=2448 width=4) (actual time=9.118..26.290 rows=0 loops=3)
         Filter: (id = 10)
         Rows Removed by Filter: 333333
 Planning Time: 0.473 ms
 Execution Time: 52.188 ms
(8 rows)

    • 添加索引。

      CREATE INDEX ON a(id);
EXPLAIN ANALYZE SELECT * FROM a WHERE id = 10;

      返回结果如下：

                                                         QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Index Only Scan using a_id_idx on a  (cost=0.42..1.54 rows=1 width=4) (actual time=0.117..0.118 rows=1 loops=1)
   Index Cond: (id = 10)
   Heap Fetches: 0
 Planning Time: 0.141 ms
 Execution Time: 0.133 ms
(5 rows)

最优执行计划

执行计划并非最优执行计划，SQL执行时间未达到预期速度，通过执行计划发现SQL有调优空间，创建合适的索引。示例如下：

  1. 创建测试表格。

    CREATE TABLE test(id int, name int);
INSERT INTO test SELECT random()*(id/100),random()*(id/100) FROM generate_series(1,100000) t(id);

  2. 创建不同索引，执行查询计划。

    • 在id列创建索引，使用最优执行计划前。

      CREATE INDEX ON test(id);
EXPLAIN ANALYZE SELECT * FROM test WHERE id = 1 and name =10;

      返回结果如下：

                                                             QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=5.14..337.45 rows=2 width=8) (actual time=0.073..0.338 rows=8 loops=1)
   Recheck Cond: (id = 1)
   Filter: (name = 10)
   Rows Removed by Filter: 678
   Heap Blocks: exact=199
   ->  Bitmap Index Scan on test_id_idx  (cost=0.00..5.14 rows=500 width=0) (actual time=0.028..0.028 rows=686 loops=1)
         Index Cond: (id = 1)
 Planning Time: 0.111 ms
 Execution Time: 0.361 ms
(9 rows)

    • 在id和name列创建索引，使用最优执行计划。

      CREATE INDEX ON test(id,name);
EXPLAIN ANALYZE SELECT * FROM test WHERE id = 1 and name =10;

      返回结果如下：

                                                               QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_id_name_idx on test  (cost=0.29..3.63 rows=2 width=8) (actual time=0.066..0.076 rows=8 loops=1)
   Index Cond: ((id = 1) AND (name = 10))
   Heap Fetches: 8
 Planning Time: 0.086 ms
 Execution Time: 0.100 ms
(5 rows)

SQL改写

SQL执行时间慢，通过创建索引没有提升空间，需要改写SQL针对性的优化。

说明

  • 此处未提供具体的测试表格结构。您可以使用您所在业务的具体表格，并依据以下改写思路对SQL语句进行相应修改。

  • 仅在PolarDB PostgreSQL版（兼容Oracle）中支持rownum语法。

  • SQL改写前：

    SELECT * 
FROM (
    SELECT row_.*, rownum AS rownum_
    FROM (
        SELECT DISTINCT 
            device0_.id AS id1_1_, 
            device0_.app_id AS app_id2_1_, 
            device0_.app_version AS app_version3_1_, 
            device0_.created_time AS created_time4_1_, 
            device0_.fcm_token AS fcm_token5_1_, 
            device0_.member_id AS member_id12_1_,
            device0_.name AS name6_1_, 
            device0_.notification AS notification7_1_,
            device0_.sys_version AS sys_version8_1_, 
            device0_.token AS token9_1_,
            device0_.type AS type10_1_, 
            device0_.updated_time AS updated_time11_1_ 
        FROM dm_device device0_
        WHERE device0_.notification = 1 
          AND device0_.token IS NOT NULL
        ORDER BY device0_.id ASC
    ) row_
    WHERE rownum <= 1000
)
WHERE rownum_ > 10;

    返回结果如下：

                                                             QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on _unnamed_subquery_0 (cost=417184.72..473136.18 rows=133218 width=723) (actual time=2000.039..2888.102 rows=990 loops=1)
  Filter: (_unnamed_subquery_0.rownum_ > 10)
  Rows Removed by Filter: 10
  Buffers: shared hit=52736, temp read=33458 written=33473
  -> Subquery Scan on row_ (cost=417184.72..468140.52 rows=399653 width=723) (actual time=2000.022..2887.974 rows=1000 loops=1)
      Filter: (rownum <= 1000)
      Rows Remove by Filter: 1190532
      Buffers: shared hit=52736, temp read=33458 written=33473
      -> Unique (cost=417184.72..453153.52 rows=1198960 width=715) (actual time=2000.016..2804.040 rows=1191532 loops=1)
          Buffers: shared hit=52736, temp read=33458 written=33473
          -> Sort (cost=417184.72..420182.12 rows=1198960 width=715) (actual time=2000.014..2416.502 rows=1191532 loops=1)
              Sort Key: device0_.id, device0_.app_id, device0_.app_version, device0_.created_time, device0_.fcm_token, device0_.number_id, device0_.name, device0_.sys_version, device0_.token,device0_.type, device0_.updated_time
              Sort Method: external merge Disk: 267664kB
              Buffers: shared hit=52736, temp read=33458 written=33473
              -> Seq Scan on dm_device device0_ (cost=0.00..72905.11 rows=1198960 width=715) (actual time=0.100..608.593 rows=1191532 loops=1)
                  Filter: ((token IS NOT NULL) AND (notification = '1'::numeric))
                  Rows Remove by Filter: 421997
                  Buffers: shared hit=52736
Planning Time: 22.127 ms
Execution Time: 2954.789 ms
(20 rows)

  • SQL改写后：

    SELECT *
FROM (
    SELECT row_.*, rownum AS rownum_
    FROM (
        SELECT 
            device0_.id AS id1_1_,
            device0_.app_id AS app_id2_1_,
            device0_.app_version AS app_version3_1_,
            device0_.created_time AS created_time4_1_,
            device0_.fcm_token AS fcm_token5_1_,
            device0_.member_id AS member_id12_1_,
            device0_.name AS name6_1_,
            device0_.notification AS notification7_1_,
            device0_.sys_version AS sys_version8_1_,
            device0_.token AS token9_1_,
            device0_.type AS type10_1_,
            device0_.updated_time AS updated_time11_1_
        FROM dm_device device0_
        WHERE device0_.notification = 1
        AND device0_.token IS NOT NULL
        ORDER BY device0_.id ASC 
        LIMIT 1000
    ) row_
    WHERE rownum <= 1000
)
WHERE rownum_ > 10;

    返回结果如下：

                                                             QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
Subquery Scan on _unnamed_subquery_0 (cost=0.43..93.61 rows=111 width=723) (actual time=0.032..1.071 rows=990 loops=1)
  Filter: (_unnamed_subquery_0.rownum_ > 10)
  Rows Removed by Filter: 10
  Buffers: shared hit=59
  -> Subquery Scan on row_ (cost=0.43..89.45 rows=333 width=723) (actual time=0.020..0.942 rows=1000 loops=1)
      Filter: (rownum <= 1000)
      Buffers: shared hit=59
      -> Limit (cost=0.43..76.95 rows=1000 width=715) (actual time=0.019..0.771 rows=1000 loops=1)
          Buffers: shared hit=59
          -> Index Scan using dm_device_pkey on dm_device device0_ (cost=0.43..91742.47 rows=1198960 width=715) (actual time=0.018..0.675 rows=1000 loops=1)
              Filter: ((token IS NOT NULL) AND (notification = '1'::numeric))
              Rows Remove by Filter: 431
              Buffers: shared hit=59
Planning Time: 1.459 ms
Execution Time: 1.147 ms
(15 rows)

SQL改写思路：

  • 使用LIMIT代替rownum：原始查询中采用rownum限制返回的行数，对应使用LIMIT改写。简化SQL语句结构，增加可读性，便于理解和维护。

  • 性能优化：LIMIT能够直接在数据库引擎层面进行优化，相比于后续处理的rownum，可减少中间结果集的大小，有助于提高查询的整体性能。

上一篇：网络问题下一篇：安全合规
文档推荐