SQL語句性能調整(1)
2024-07-21 02:40:28
供稿：網友
一、 索引(INDEX)使用的問題1． 索引(INDEX)，用還是不用？這是個的問題。 是全表掃描還是索引范圍掃描主要考慮SQL的查詢速度問題。這里主要關心讀取的記錄的數目。根據DONALD K .BURLESON的說法,使用索引范圍掃描的原則是：
對于數據有原始排序的表，讀取少于表記錄數40%的查詢應該使用索引范圍掃描。對讀取多于表記錄數40%的查詢應全表掃描。
對于未排序的表，讀取少于表記錄數7%的查詢應該使用索引范圍掃描,反之，對讀取多于表記錄數7%的查詢應全表掃描。
注：在不同的書中，對是否使用索引的讀取記錄的百分比值不太一致，基本上是一個經驗值，但是讀取記錄的百分比越低，使用索引越有效。 2． 假如列上有建索引，什么SQL查詢是有用索引(INDEX)的？什么SQL查詢是沒有用索引(INDEX)的？
存在下面情況的SQL，不會用到索引：
存在數據類型隱形轉換的,如：
select * from staff_member where staff_id=’123’；
列上有數學運算的，如：
select * from staff_member where salary*2<10000;
使用不等于(<> )運算的，如：
select * from staff_member where dept_no<>2001;
使用substr字符串函數的，如：
select * from staff_member where substr(last_name,1,4)=’FRED’;
‘%’通配符在第一個字符的，如:
select * from staff_member where first_name like ‘%DON’;
字符串連接()的，如：
select * from staff_member where first_name’’=’DONALD’ 3． 函數的索引日期類型也是很輕易用到的，而且在SQL語句中會使用to_char函數以查詢具體的的范圍日期。如：select * from staff_member where TO_CHAR(birth_day,’YYYY’)=’2003’; 我們可以建立基于函數的索引如：CREATE INDEX Ind_emp_birth ON staff_member (to_char((birth_day,’YYYY’));
二、 SQL語句排序優化1． 排序發生的情況：
SQL中包含group by 子句
SQL 中包含order by 子句
SQL 中包含 distinct 子句
SQL 中包含 minus 或 union操作
創建索引時2． 排序在內存還是在磁盤中進行？在內存執行的排序速度要比在磁盤執行的排序速度快14000倍。假如是專用連接，排序內存根據INIT.ORA的sort_area_size進行分配，假如是多線程服務連接，排序內存根據large_pool_size進行分配。sort_area_size的增大可以減少磁盤排序，但是過大將使Oracle性能降低，因為所用的連接回話都會分配到一個sort_area_size大小的內存，所以，為了提高有限的查詢速度，可能會浪費大量的內存。增加sort_multiblock_read_count的值使每次讀取更多的內容，減少運行次數，提高性能。 三、SQL子查詢的調整1、理解關聯子查詢和非關聯子查詢。
下面是一個非關聯子查詢：
select staff_name from staff_member where staff_id
in (select staff_id from staff_func);
而下面是一個關聯子查詢：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id); 以上返回的結果集是相同的，可是它們的執行開銷是不同的：
非關聯查詢的開銷——非關聯查詢時子查詢只會執行一次，而且結果是排序好的，并保存在一個ORACLE的臨時段中，其中的每一個記錄在返回時都會被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結果集排序，以及將臨時數據段進行排序會增加大量的系統開銷。
關聯查詢的開銷——對返回到父查詢的的記錄來說，子查詢會每行執行一次。因此，我們必須保證任何可能的時候子查詢用到索引。2、XISTS子句和IN子句
帶IN的關聯子查詢是多余的，因為IN子句和子查詢中相關的操作的功能是一樣的。如：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
為非關聯子查詢指定EXISTS子句是不適當的，因為這樣會產生笛卡乘積。如：
select staff_name from staff_member where staff_id
Exists (select staff_id from staff_func);
盡量不要使用NOT IN子句。使用MINUS 子句都比NOT IN 子句快，雖然使用MINUS子句要進行兩次查詢：
select staff_name from staff_member where staff_id in (select staff_id from staff_member MINUS select staff_id from staff_func where func_id like ‘81%’);
3、 任何可能的時候，用標準連接或內嵌視圖改寫子查詢。 一、 索引(INDEX)使用的問題1． 索引(INDEX)，用還是不用？這是個的問題。 是全表掃描還是索引范圍掃描主要考慮SQL的查詢速度問題。這里主要關心讀取的記錄的數目。根據DONALD K .BURLESON的說法,使用索引范圍掃描的原則是：
對于數據有原始排序的表，讀取少于表記錄數40%的查詢應該使用索引范圍掃描。對讀取多于表記錄數40%的查詢應全表掃描。
對于未排序的表，讀取少于表記錄數7%的查詢應該使用索引范圍掃描,反之，對讀取多于表記錄數7%的查詢應全表掃描。
注：在不同的書中，對是否使用索引的讀取記錄的百分比值不太一致，基本上是一個經驗值，但是讀取記錄的百分比越低，使用索引越有效。 2． 假如列上有建索引，什么SQL查詢是有用索引(INDEX)的？什么SQL查詢是沒有用索引(INDEX)的？
存在下面情況的SQL，不會用到索引：
存在數據類型隱形轉換的,如：
select * from staff_member where staff_id=’123’；
列上有數學運算的，如：
select * from staff_member where salary*2<10000;
使用不等于(<> )運算的，如：
select * from staff_member where dept_no<>2001;
使用substr字符串函數的，如：
select * from staff_member where substr(last_name,1,4)=’FRED’;
‘%’通配符在第一個字符的，如:
select * from staff_member where first_name like ‘%DON’;
字符串連接()的，如：
select * from staff_member where first_name’’=’DONALD’ 3． 函數的索引日期類型也是很輕易用到的，而且在SQL語句中會使用to_char函數以查詢具體的的范圍日期。如：select * from staff_member where TO_CHAR(birth_day,’YYYY’)=’2003’; 我們可以建立基于函數的索引如：CREATE INDEX Ind_emp_birth ON staff_member (to_char((birth_day,’YYYY’));
二、 SQL語句排序優化1． 排序發生的情況：
SQL中包含group by 子句
SQL 中包含order by 子句
SQL 中包含 distinct 子句
SQL 中包含 minus 或 union操作
創建索引時2． 排序在內存還是在磁盤中進行？在內存執行的排序速度要比在磁盤執行的排序速度快14000倍。假如是專用連接，排序內存根據INIT.ORA的sort_area_size進行分配，假如是多線程服務連接，排序內存根據large_pool_size進行分配。sort_area_size的增大可以減少磁盤排序，但是過大將使ORACLE性能降低，因為所用的連接回話都會分配到一個sort_area_size大小的內存，所以，為了提高有限的查詢速度，可能會浪費大量的內存。增加sort_multiblock_read_count的值使每次讀取更多的內容，減少運行次數，提高性能。 三、SQL子查詢的調整1、理解關聯子查詢和非關聯子查詢。
下面是一個非關聯子查詢：
select staff_name from staff_member where staff_id
in (select staff_id from staff_func);
而下面是一個關聯子查詢：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id); 以上返回的結果集是相同的，可是它們的執行開銷是不同的：
非關聯查詢的開銷——非關聯查詢時子查詢只會執行一次，而且結果是排序好的，并保存在一個ORACLE的臨時段中，其中的每一個記錄在返回時都會被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結果集排序，以及將臨時數據段進行排序會增加大量的系統開銷。
關聯查詢的開銷——對返回到父查詢的的記錄來說，子查詢會每行執行一次。因此，我們必須保證任何可能的時候子查詢用到索引。2、XISTS子句和IN子句
帶IN的關聯子查詢是多余的，因為IN子句和子查詢中相關的操作的功能是一樣的。如：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
為非關聯子查詢指定EXISTS子句是不適當的，因為這樣會產生笛卡乘積。如：
select staff_name from staff_member where staff_id
Exists (select staff_id from staff_func);
盡量不要使用NOT IN子句。使用MINUS 子句都比NOT IN 子句快，雖然使用MINUS子句要進行兩次查詢：
select staff_name from staff_member where staff_id in (select staff_id from staff_member MINUS select staff_id from staff_func where func_id like ‘81%’);
3、 任何可能的時候，用標準連接或內嵌視圖改寫子查詢。
