SQL調優指南—智能索引推薦

注意事項

索引推薦功能僅針對您目前指定的SQL查詢語句進行分析與推薦。在根據推薦的資訊建立索引前，您需要評估建立該索引對其它查詢的影響。

環境說明

TPC-H是業界常用的基準測試方法，由TPC委員會制定釋出，用于評測資料庫的分析型查詢能力。TPC-H基準測試方法包含8張資料表、22條複雜的SQL查詢（即Q1~Q22）。下圖為執行TPC-H中的Q17（小訂單收入查詢）的傳回資訊，可檢視到執行該查詢語句消耗的時間為28.76秒。本文将通過智能索引推薦功能，優化該查詢語句的執行效率。

1. 查詢智能索引推薦資訊如需查詢某個查詢語句的智能索引推薦資訊，您隻需在該查詢語句前增加EXPLAIN ADVISOR指令，示例如下：

EXPLAIN ADVISOR
SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem,
     part
WHERE p_partkey = l_partkey
  AND p_brand = 'Brand#23'
  AND p_container = 'MED BOX'
  AND l_quantity <
    (SELECT 0.2 * avg(`l_quantity`)
     FROM lineitem
     WHERE l_partkey = p_partkey);      

1. 執行上述指令後，PolarDB-X将傳回推薦的索引建立語句、添加索引前後的代價等資訊，詳細的傳回資訊及其注釋如下所示：說明

• • 本案例中，預計磁盤I/O提升百分比為3024.7%，表明使用推薦的索引将帶來較大的收益。
  • 當PolarDB-X無法推薦索引時，傳回資訊中會建議您在業務低峰期，對目标表執行Analyze Table指令重新整理統計資訊（該操作會消耗較大的I/O資源）。當統計資訊更新後，再次執行索引推薦可獲得更準确的索引。SQL複制代碼。

IMPROVE_VALUE: 2465.3%        # 預計綜合代價提升百分比
  IMPROVE_CPU: 59377.4%       # 預計CPU提升百分比
  IMPROVE_MEM: 0.4%           # 預計記憶體提升百分比
   IMPROVE_IO: 3024.7%        # 預計磁盤I/O提升百分比
  IMPROVE_NET: 2011.1%        # 預計網絡傳輸提升百分比
 BEFORE_VALUE: 4.711359845E8  # 添加索引前綜合代價值
   BEFORE_CPU: 1.19405577E7   # 添加索引前CPU估算值
   BEFORE_MEM: 426811.2       # 添加索引前記憶體消耗估算值
    BEFORE_IO: 44339          # 添加索引前磁盤I/O估算值
   BEFORE_NET: 47.5           # 添加索引前網絡傳輸估算值
  AFTER_VALUE: 1.83655008E7   # 添加索引後綜合代價值
    AFTER_CPU: 20075.8        # 添加索引後CPU估算值
    AFTER_MEM: 425016         # 添加索引後記憶體消耗估算值
     AFTER_IO: 1419           # 添加索引後磁盤I/O估算值
    AFTER_NET: 2.2            # 添加索引後網絡傳輸估算值
 ADVISE_INDEX: ALTER TABLE `lineitem` ADD  INDEX `__advise_index_lineiteml_partkey`(`l_partkey`);
/* ADVISE_INDEX中的内容為推薦的索引建立語句 */
     NEW_PLAN:                # 添加索引後預計執行計劃
Project(avg_yearly="$f0 / ?0")
  HashAgg($f0="SUM(l_extendedprice)")
    Filter(condition="l_quantity < $16 * f17w0$o0")
      SortWindow(p_partkey="p_partkey", l_partkey="l_partkey", l_quantity="l_quantity", l_extendedprice="l_extendedprice", $16="$16", f5w0$o0="window#0AVG($2)", Reference Windows="window#0=window(partition {1} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED PRECEDING aggs [AVG($2)])")
        MemSort(sort="l_partkey ASC")
          BKAJoin(condition="l_partkey = p_partkey", type="inner")
            Gather(concurrent=true)
              LogicalView(tables="[0000,0001].part", shardCount=2, sql="SELECT `p_partkey` FROM `part` AS `part` WHERE ((`p_brand` = ?) AND (`p_container` = ?))")
            Gather(concurrent=true)
              LogicalView(tables="[0000,0001].lineitem", shardCount=2, sql="SELECT `l_partkey`, `l_quantity`, `l_extendedprice`, ? AS `$16` FROM `lineitem` AS `lineitem` WHERE (`l_partkey` IN (...))")
         INFO: LOCAL_INDEX    # 其它資訊      

1. 根據推薦資訊建立索引

1. 1. 評估建立該索引帶來的收益，然後根據傳回結果ADVISE_INDEX中的SQL語句建立索引。

ALTER TABLE `lineitem` ADD  INDEX `__advise_index_lineiteml_partkey`(`l_partkey`);      

1. 1. 再次執行TPC-H中的Q17（小訂單收入查詢），耗時減少至1.41秒，查詢效率得到大幅提升。