當前位置:成語大全網 - 書法字典 - MySQL隨機查詢優化問題,如何解決

MySQL隨機查詢優化問題,如何解決

(1)選擇最有效的表名順序(僅在基於規則的優化器中有效):

ORACLE的解析器從右到左處理FROM子句中的表名,FROM子句末尾寫的表(基本表驅動表)會先處理。在from子句中有多個表的情況下,必須選擇記錄數最少的表作為基本表。如果有三個以上的表加入查詢,需要選擇交集表作為基本表,基本表是指被其他表引用的表。

(where子句中的連接順序。;

ORACLE以自下而上的順序解析WHERE子句。根據這個原則,表與表之間的連接必須寫在其他WHERE條件之前,那些能篩選出最大記錄數的條件必須寫在WHERE子句的末尾。

(3)避免在select子句中使用' * ':

在解析的過程中,ORACLE會依次將' * '轉換成所有的列名。這項工作是通過查詢數據字典來完成的,這意味著需要更多的時間。

⑷減少訪問數據庫的次數:

ORACLE內部做了很多工作:解析SQL語句,估計索引利用率,綁定變量,讀取數據塊等。

(5)重置SQL*Plus、SQL*Forms和Pro*C中的ARRAYSIZE參數,可以增加每次數據庫訪問檢索到的數據量,推薦值為200。

(6)使用解碼功能減少處理時間:

使用DECODE函數可以避免重復掃描相同的記錄或連接相同的表。

(7)簡單的集成和無關的數據庫訪問:

如果您有幾個簡單的數據庫查詢語句,您可以將它們集成到壹個查詢中(即使它們不相關)。

(8)刪除重復記錄:

刪除重復記錄的最有效方法示例(因為使用了ROWID):

從EMP E中刪除WHERE E . ROWID & gt(選擇最小值(X.ROWID)

FROM EMP X其中X . EMP _ NO = E . EMP _ NO);

(9)用截斷替換刪除:

在正常情況下,刪除表中的記錄時,回滾段用於存儲可以恢復的信息。如果沒有提交事務,ORACLE會將數據恢復到刪除前的狀態(確切地說,恢復到執行刪除命令前的狀態)。使用TRUNCATE時,回滾段不再存儲任何可恢復的信息。命令運行時,數據無法恢復。所以調用的資源很少,執行時間會很短。

(10)盡可能使用提交:

只要有可能,在程序中盡可能多的使用COMMIT,這樣程序的性能會得到提升,需求也會因為COMMIT釋放的資源而減少:

提交釋放的資源:

A.用於恢復回滾段上的數據的信息。

B.程序語句獲得的鎖

C.重做日誌緩沖區的空間

D.ORACLE管理上述三種資源中的內部費用。

將HAVING子句替換為Where子句:

避免使用HAVING子句,該子句只會在檢索到所有記錄後過濾結果集。這個過程需要排序和合計等操作。如果可以通過WHERE子句限制記錄的數量,就可以減少這方面的開銷。(非甲骨文)在三個條件子句on、where和HAVING中,首先執行on,其次是where。Having最後,因為on在做統計前過濾不合格記錄,可以減少中間操作要處理的數據,應該是最快的,where應該比having快,因為它在求和前過濾數據,在兩個表連接時使用on,所以當使用壹個表時,留下where與having比較。在這種單表查詢統計的情況下,如果要過濾的條件不涉及要計算的字段,那麽它們的結果都是壹樣的,只是where可以使用rushmore技術,而having不行,而且後者速度更慢。如果涉及到要計算的字段,說明這個字段的值在計算之前是不確定的。按照上壹篇文章寫的工作流程,where的動作時間是在計算之前完成的,having是在計算之後才完成的。在多表連接查詢中,on比where工作得早。系統首先根據表與表之間的連接條件,將多個表組合成壹個臨時表,然後按where篩選,再計算,計算後再按have篩選。可見,要想正確篩選條件,首先要了解這個條件應該在什麽時候起作用,然後再決定放在哪裏。

(12)減少對表的查詢:

在帶有子查詢的SQL語句中,應該特別註意減少對表的查詢。示例:

從表中選擇TAB_NAME,其中(TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS其中VERSION = 604)

(13)通過內部函數提高SQL效率。

復雜的SQL經常犧牲執行效率。在實際工作中掌握上述利用函數解決問題的方法是很有意義的。

(14)使用表格的別名:

在SQL語句中連接多個表時,請使用表的別名,並將別名作為每列的前綴。這樣,可以減少解析時間,並且可以減少由列歧義引起的語法錯誤。

(15)將IN替換為EXISTS,將NOT IN替換為NOT EXISTS

在許多基於基本表的查詢中,為了滿足壹個條件,通常需要連接另壹個表。在這種情況下,使用EXISTS(或NOT EXISTS)通常會提高查詢的效率。在子查詢中,NOT IN子句將執行內部排序和合並。無論哪種情況,NOT IN都是效率最低的(因為它對子查詢中的表執行全表遍歷)。為了避免使用NOT IN,我們可以將其重寫為外部連接或NOT EXISTS。

示例:

(高效)SELECT * FROM EMP(基本表)其中EMPNO >;0且存在(從部門中選擇“X ”,其中部門編號=雇員。DEPTNO和LOC = 'MELB ')

(低效)SELECT * FROM EMP(基本表)其中EMPNO >;0和DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ' MELB ')

(16)識別“低效執行”SQL語句:

雖然目前各種關於SQL優化的圖形化工具層出不窮,但編寫自己的SQL工具解決問題永遠是最好的方法:

SELECT執行、磁盤讀取、緩沖區獲取,

ROUND((BUFFER _ GETS-DISK _ READS)/BUFFER _ GETS,2) Hit_radio,

ROUND(磁盤讀取/執行,2) Reads_per_run,

SQL _文本

來自V$SQLAREA

執行地點& gt0

和BUFFER _ GETS & gt0

和(BUFFER _ GETS-DISK _ READS)/BUFFER _ GETS & lt;0.8

由4 DESC訂購;

(17)利用索引提高效率;

索引是表的概念部分,用於提高數據檢索的效率。ORACLE使用復雜的自平衡B樹結構。壹般來說,通過索引查詢數據比掃描整個表要快。當ORACLE找到執行查詢和更新語句的最佳路徑時,ORACLE optimizer將使用索引。當連接多個表時,它還可以提高效率。使用索引的另壹個優點是它提供了主鍵的唯壹性驗證。對於那些LONG或LONG RAW數據類型,幾乎可以索引所有的列。通常,在大型表中使用索引特別有效。當然,妳也會發現,在掃描小表的時候,使用索引也可以提高效率。雖然使用索引可以提高查詢效率,但是也要註意它的代價。索引需要空間來存儲,並且需要定期維護。每次在表中添加或刪除記錄或者修改索引列時,索引本身都會被修改。這意味著每條記錄的插入、刪除和更新將花費4到5倍的磁盤I/O。因為索引需要額外的存儲空間和處理,那些不必要的索引將降低查詢響應時間。有必要定期重建索引。

更改索引& ltINDEXNAME & gt重建& lt表空間名稱& gt

18)將DISTINCT替換為EXISTS:

當提交包含壹對多表信息(如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT。壹般可以考慮用EXIST來代替。EXIST使查詢更快,因為RDBMS核心模塊將在子查詢的條件滿足時立即返回結果。示例:

(效率低下):

從部門D、員工E中選擇不同的部門編號、部門名稱

其中,部門編號=部門編號

(高效):

SELECT DEPT_NO,DEPT _ NAME FROM DEPT D WHERE existing(選擇“X ”)

FROM EMP E其中E . DEPT _ NO = D . DEPT _ NO);

(19) sql語句大寫;因為oracle總是先解析sql語句,在執行前將小寫字母轉換成大寫字母。

(20)Java代碼中盡量少用連接符“+”連接字符串!

(21)避免在索引列上使用NOT。通常情況下,

我們應該避免使用NOT,NOT索引列,這與在索引列上使用函數具有相同的效果。當“ORACLE”遇到“NOT”時,它將停止使用索引,轉而執行全表掃描。

(22)避免在索引列上使用計算。

在WHERE子句中,如果索引列是函數的壹部分,優化器將使用全表掃描而不是索引。

例如:

效率低下:

SELECT…FROM DEPT WHERE SAL * 12 & gt;25000;

高效:

SELECT … FROM部門SAL & gt25000/12;

(23)使用> =替換>

高效:

SELECT * FROM EMP WHERE DEPTNO & gt=4

效率低下:

SELECT * FROM EMP WHERE DEPTNO & gt三

兩者的區別在於,前者DBMS會直接跳轉到DEPT等於4的第壹條記錄,而後者會先定位DEPTNO=3等於3的記錄,然後向前掃描到DEPT大於3的第壹條記錄。

(24)用UNION替換OR(適用於索引列)

壹般來說,用UNION替換WHERE子句中的OR會有很好的效果。對索引列使用或將導致全表掃描。請註意,上述規則僅對多個索引列有效。如果壹個列沒有索引,查詢效率可能會降低,因為您沒有選擇或。在下面的示例中,LOC_ID和REGION都有索引。

高效:

選擇位置標識,位置DESC,區域

從位置

其中LOC_ID = 10

聯盟

選擇位置標識,位置DESC,區域

從位置

其中REGION = "墨爾本"

效率低下:

選擇位置標識,位置DESC,區域

從位置

其中LOC_ID = 10或REGION = "MELBOURNE "

如果堅持使用OR,需要先返回記錄最少的索引列。

(25)將或改為。

這是壹個簡單易記的規則,但實際執行效果有待檢驗。在ORACLE8i下,兩者的執行路徑似乎是壹樣的。

效率低下:

選擇…從LOC_ID = 10或LOC_ID = 20或LOC_ID = 30的位置

高效(率)

SELECT…FROM LOCATION WHERE LOC _ IN IN(10,20,30);

(26)避免在索引列上使用IS NULL和IS NOT NULL。

避免在索引中使用任何可以為空的列,ORACLE將無法使用該索引。對於單列索引,如果列包含空值,則記錄將不存在於索引中。對於復合索引,如果每壹列都為空,則記錄也不會存在於索引中。如果至少有壹列不為空,則記錄將存在於索引中。例如,如果在表的A列和B列上建立了唯壹索引,並且表中有壹條記錄A,如果B的值為(123,null),則ORACLE不會接受下壹條A和B的值相同(123,null)的記錄(insert)。但是如果所有的索引列都是空的,ORACLE會認為整個鍵值都是空的,空不代表空。因此,可以插入1000條具有相同鍵值的記錄。當然,因為索引列中不存在空值,所以在WHERE子句中比較索引列的空值會導致ORACLE禁用索引。

低效:(索引無效)

WHERE DEPT _ CODE不為空的部門;

高效:(有效指數)

WHERE DEPT _ CODE & gt=0;

(27)始終使用索引的第壹列:

如果索引建立在多個列上,優化器將選擇僅當where子句引用其第壹個前導列時才使用索引。這也是壹個簡單而重要的規則。當只引用索引的第二列時,優化器使用全表掃描並忽略該索引。

28)用UNION-ALL替換UNION(如果可能):

當SQL語句需要兩個UNION的查詢結果集時,這兩個結果集將以UNION-ALL的方式進行合並,然後進行排序,最後輸出結果。如果用UNION ALL代替UNION,排序就沒有必要了,效率也會提高。應該註意,UNION ALL將在兩個結果集中重復輸出相同的記錄。因此,您應該從業務需求出發,分析使用UNION ALL的可行性。UNION將對結果集進行排序,這個操作將使用內存SORT_AREA_SIZE。對於這個內存的優化也是非常重要的。下面的SQL可以用來查詢排序的消耗。

低效:

選擇ACCT _數量,余額_金額

從借方_交易記錄

其中TRAN日期= ' 365438+1995年12月0日'

聯盟

選擇ACCT _數量,余額_金額

從借方_交易記錄

其中TRAN日期= ' 365438+1995年12月0日'

高效:

選擇ACCT _數量,余額_金額

從借方_交易記錄

其中TRAN日期= ' 365438+1995年12月0日'

聯合所有

選擇ACCT _數量,余額_金額

從借方_交易記錄

其中TRAN日期= ' 365438+1995年12月0日'

(29)將ORDER改為WHERE:

ORDER BY子句僅在兩個嚴格的條件下使用索引。

ORDER BY中的所有列必須包含在同壹個索引中,並保持索引中的順序。

ORDER BY中的所有列都必須定義為非空。

WHERE子句中使用的索引和ORDER BY子句中使用的索引不能並置。

例如:

表DEPT包含以下列:

DEPT_CODE主鍵不為空

DESC部門不為空

部門類型為空

低效:(未使用索引)

按部門類型從部門訂單中選擇部門代碼

高效:(使用索引)

從部門中選擇部門代碼,其中部門類型& gt0

(30)避免改變索引列的類型;

在比較不同數據類型的數據時,ORACLE會自動對列執行簡單的類型轉換。

假設EMPNO是壹個數字類型的索引列。

SELECT…FROM EMP WHERE EMPNO = ' 123 '

事實上,在ORACLE類型轉換之後,該語句被轉換為:

SELECT…FROM EMP WHERE EMPNO = TO _ NUMBER(' 123 ')

幸運的是,類型轉換不會發生在索引列上,索引的目的也沒有改變。

現在,假設EMP_TYPE是字符類型的索引列。

SELECT … FROM EMP,其中EMP_TYPE = 123

ORACLE將此語句轉換為:

SELECT…FROM EMP where _ NUMBER(EMP _ TYPE)= 123

由於內部類型轉換,將不會使用該索引!為了避免ORACLE對您的SQL進行隱式類型轉換,最好顯式地表達類型轉換。請註意,在將字符與數值進行比較時,ORACLE會優先將數值類型轉換為字符類型。

(31)要註意的WHERE子句:

某些SELECT語句中的WHERE子句不使用索引。這裏有壹些例子。

在下面的例子中,(1)'!= '將不使用索引。記住,索引只能告訴妳表中存在什麽,不能告訴妳不存在什麽。(2)’是壹個字符鏈接功能。與其他功能壹樣,索引是禁用的。(3)‘+’是壹個數學函數。就像其他數學函數壹樣,索引被禁用。(4)相同的索引列不能相互比較,會啟用全表掃描。

(32) A .如果檢索數據超過表中記錄的30%,使用索引不會有明顯的效率提升。

B.在某些情況下,使用索引可能比全表掃描慢,但兩者的數量級相同。通常使用索引比全表掃描快幾倍甚至幾千倍!

(33)避免使用消耗資源的操作:

帶有distinct、union、Minus、intersect和order by的SQL語句將啟動SQL引擎。

執行消耗資源的排序功能。DISTINCT需要排序壹次,而其他的至少需要排序兩次。通常,帶有Union、Minus和Intersect的SQL語句可以用其他方式重寫。如果妳的數據庫的SORT_AREA_SIZE分配的很好的話,Union,Minus,Intersect也可以考慮,畢竟可讀性很強。

(34)通過以下方式優化組:

為了提高GROUP BY語句的效率,我們可以在GROUP BY之前過濾掉不必要的記錄。以下兩個查詢返回相同的結果,但是第二個查詢顯然快得多。

效率低下:

選擇職務,AVG(薩爾)

來自電磁脈沖

按工作分組

有工作=“總統”

或者JOB = 'MANAGER '

高效:

選擇職務,AVG(薩爾)

來自電磁脈沖

其中JOB =“總統”

或者JOB = 'MANAGER '

按工作分組