共計 7055 個字符，預計需要花費 18 分鐘才能閱讀完成。
本篇內容介紹了“sql 如何利用索引消除排序”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
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 如何利用索引消除排序”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
