中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

sql如何利用索引消除排序

發布時間：2021-08-26 15:12:52 來源：億速云 閱讀：170 作者：chen 欄目：數據庫

本篇內容介紹了“sql如何利用索引消除排序”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

1.1.1  現象描述

我們經常碰到這種SQL：

Select * from tab where col1 >‘x’ order by col2 desc limit 0,5;

執行時間很長，需要優化。

這種問題，我們在首頁展示時經常碰到。這里我們介紹兩個經典的優化思路。

1.1.2  處理方法

1.1.2.1 使用環境

數據庫版本：DM Database Server x64 V8.1.0.156-Build(2019.05.05-106384)SEC

環境：個人pc環境

1.1.2.2 構造數據

說明：

新建一個50列的表，并插入5w數據。

代碼：

drop table t_test_1;

-- 創建一個 50 列的表

declare

tb_s int default 1;

col_s int default 50;

a_sql clob default 'create table t_test_';

--b_sql clob default 'create huge table ht_test_';

begin

for rrs in 1..tb_s loop

    a_sql:=a_sql||rrs||'(';

    --b_sql=b_sql||rrs||'(';

    for rs in 1..col_s-1 loop

        a_sql:=a_sql||'col_'||rs||' varchar(50),';

        --b_sql=b_sql||'col_'||rs||' varchar(50),';

    end loop;

   -- a_sql:=a_sql||'col_'||col_s||' int);'; -- 不帶主鍵

    a_sql:=a_sql||'col_'||col_s||' int primary key,col_end datetime)';

    --b_sql=b_sql||'col_'||col_s||' int primary key)';

     execute IMMEDIATE a_sql;

-- dbms_output.put_line(a_sql);

    --execute immediate b_sql;

    --select cast(b_sql as varchar);

    a_sql:='create table t_test_';

    --b_sql='create huge table ht_test_';

end loop;

NULL;

end;

-- 生成50000測試數據

--初始化基礎數據

declare

tb_s int default 1;

col_s int default 50;

ROW_S INT DEFAULT 50000;

a_sql clob default 'INSERT INTO t_test_';

commit_i int default 10000;

begin

for rrs in 1..tb_s loop

--for rrs in 9..tb_s loop

FOR RRRS IN 1..ROW_S LOOP

    a_sql:=a_sql||rrs||' VALUES(';

    for rs in 1..col_s-1 loop

        a_sql:=a_sql||' dbms_random.string(''x'',50),';

    end loop;

    a_sql:=a_sql||RRRS||',sysdate-dbms_random.value(1000000))';

    EXECUTE immediate a_sql;

  -- dbms_output.put_line (a_sql);

    --select cast(a_sql as varchar);

    a_sql:='INSERT INTO t_test_';

    commit_i:=commit_i-1;

    if(commit_i =0)then

    commit;

    commit_i:=10000;

    end if;

END LOOP;

end loop;

NULL;

end;

1.1.2.3 查看計劃和優化語句
1.1.2.4 要查詢的SQL、計劃和執行時間

說明：這里所有的執行，都不取第一次的執行時間；執行比為【當前方法的執行時間/所有方法的最小執行時間】

說明

處理

語句

計劃

執行時間（秒）

執行比

A0 【原語句和計劃和執行時間】

null

select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end   desc limit 0,5;

1   #NSET2: [217,   5, 2376]
  2     #PRJT2: [217, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [217, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #SLCT2: [38, 2500, 2376];   T_TEST_1.COL_14 > '1'
  5           #CSCN2: [38, 50000, 2376];   INDEX33559006(T_TEST_1)

0.078

9.75

A1 【常規優化 1 ，給col_14添加索引】

create index idx_t_test_1_1 on t_test_1(col_14);

select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end   desc limit 0,5;

1   #NSET2: [190,   5, 2376]
  2     #PRJT2: [190, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [190, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [11, 2500, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]

0.324

40.5

B1 【有人會說，這里優化的不對，要把col_end也帶上】

create index idx_t_test_1_2 on t_test_1(col_14,col_end);

select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end   desc limit 0,5;

1   #NSET2: [190,   5, 2376]
  2     #PRJT2: [190, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [190, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [11, 2500, 2376];   IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [11, 2500, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]

0.324

40.5

B2 【col_end   desc 呢】

create index idx_t_test_1_3 on t_test_1(col_14,col_end desc);

select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end   desc limit 0,5;

1   #NSET2: [190,   5, 2376]
  2     #PRJT2: [190, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [190, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [11, 2500, 2376];   IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [11, 2500, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]

0.324

40.5

B2 【在B2的基礎上調整順序】

create index idx_t_test_1_4 on t_test_1(col_end desc,col_14);

select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end   desc limit 0,5;

1   #NSET2: [190,   5, 2376]
  2     #PRJT2: [190, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [190, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [11, 2500, 2376];   IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [11, 2500, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]

0.324

40.5

B3

我們看看有什么相關的參數
  select * from A8 where para_name like '%TOP%'
  我們把這個看起來像是有關系的參數，修改為1，看下計劃和執行時間。

select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where   T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;

1   #NSET2: [0,   5, 2376]
  2     #PRJT2: [0, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #TOPN2: [0, 5, 2376]; top_num(5),   top_off(0)
  4         #SLCT2: [0, 100, 2376];   T_TEST_1.COL_14 > '1'
  5           #BLKUP2: [0, 100, 2376];   IDX_T_TEST_1_4(T_TEST_1)
  6             #SSCN: [0, 100, 2376];   IDX_T_TEST_1_4(T_TEST_1)

0.008

1

B4


select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ *   from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;

1   #NSET2: [190,   5, 2376]
  2     #PRJT2: [190, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [190, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [11, 2500, 2376];   IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [11, 2500, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]

0.324

40.5

B5


select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where   T_TEST_1.COL_14='1' order by col_end desc limit 0,5;

1   #NSET2: [0,   5, 2376]
  2     #PRJT2: [0, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #TOPN2: [0, 5, 2376];   top_num(5), top_off(0)
  4         #SLCT2: [0, 100, 2376];   T_TEST_1.COL_14 = '1'
  5           #BLKUP2: [0, 100, 2376];   IDX_T_TEST_1_4(T_TEST_1)
  6             #SSCN: [0, 100, 2376];   IDX_T_TEST_1_4(T_TEST_1)

0.008

1

B6


select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ *   from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;

1   #NSET2: [5,   5, 2376]
  2     #PRJT2: [5, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #TOPN2: [5, 5, 2376];   top_num(5), top_off(0)
  4         #BLKUP2: [5, 1250, 2376];   IDX_T_TEST_1_3(T_TEST_1)
  5           #SSEK2: [5, 1250, 2376];   scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max))


0

B7

update t_test_1 set col_14='1' where rownum<=30000 ;commit;

select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ *   from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;

1   #NSET2: [5,   5, 2376]
  2     #PRJT2: [5, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #TOPN2: [5, 5, 2376];   top_num(5), top_off(0)
  4         #BLKUP2: [5, 1250, 2376];   IDX_T_TEST_1_3(T_TEST_1)
  5           #SSEK2: [5, 1250, 2376];   scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max))

0.008

1

B8


select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)   no_index(t_test_1,IDX_T_TEST_1_3)*/ * from t_test_1 where T_TEST_1.COL_14='1'   order by col_end desc limit 0,5;

1   #NSET2: [95,   5, 2376]
  2     #PRJT2: [95, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [95, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [5, 1250, 2376];   IDX_T_TEST_1_1(T_TEST_1)
  5           #SSEK2: [5, 1250, 2376];   scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range['1','1']

0.186

23.25

B9


select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)   no_index(t_test_1,IDX_T_TEST_1_3)
  no_index(t_test_1,IDX_T_TEST_1_1)*/ * from t_test_1 where T_TEST_1.COL_14='1'   order by col_end desc limit 0,5;

1   #NSET2: [95,   5, 2376]
  2     #PRJT2: [95, 5, 2376];   exp_num(52), is_atom(FALSE)
  3       #SORT3: [95, 5, 2376];   key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
  4         #BLKUP2: [5, 1250, 2376];   IDX_T_TEST_1_2(T_TEST_1)
  5           #SSEK2: [5, 1250, 2376];   scan_type(ASC), IDX_T_TEST_1_2(T_TEST_1), scan_range[('1',min),('1',max))

0.343

42.875

解讀分析：

1.  通過A0，和A1，展示的我們通常碰到的場景，一個查詢涉及到的表沒有索引，于是我們在查詢列上建立索引。在這個例子當中，我們可以看到，本來沒有走索引的語句，通過我們建立索引后，確實走索引了，但是效率反而下降了4倍多（從原來的0.087秒，變為了0.324秒）。

2.  對于有經驗一點的，可能會想到排序列是不是也應該需要放到索引中（這里一般是不需要的，后面有機會再詳細介紹；但是特殊情況是需要的，之類就是特殊情況），所以B1和B2的思路是對的，但是實際上，思路需要走到B2-1才是對的。

“sql如何利用索引消除排序”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！

向AI問一下細節
推薦閱讀：
  1. SQL之索引
  2. SQL 排序

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

sql
AI
助
手
邵武市| 麦盖提县| 白山市| 祁东县| 全椒县| 潼关县| 莫力| 内黄县| 昭苏县| 蒙城县| 富源县| 旅游| 屏南县| 兴山县| 阳西县| 绿春县| 奎屯市| 环江| 宜兰市| 栾城县| 涞源县| 灵武市| 荔浦县| 通山县| 四子王旗| 新民市| 新竹市| 班玛县| 云安县| 浦江县| 喀什市| 湖口县| 离岛区| 九江县| 甘泉县| 长宁区| 巫溪县| 盐山县| 昆山市| 高州市| 仁怀市|