程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> MYSQL教程:索引和查詢優化程序

MYSQL教程:索引和查詢優化程序

編輯:更多數據庫知識

數據庫是數據的集合,與數學的集合論有密不可分的關系。

為提高查詢速度,我們可以:

  • 對數據表添加索引,以加快搜索速度;

  • 通過編程技巧最大限度地利用索引;

  • 優化查詢語句,以使服務器最快響應多客戶的請求。

  • 研究硬件處理過程,減少物理約束。

2.1. 索引

索引技術是關系數據查詢中最重要的技術。如果要加提升數據庫的性能,索引優化是首先應該考慮的。因為它能使我們的數據庫得到最大性能方面的提升。

索引的優點:

  • 沒有索引的表是沒有排序的數據集合,如果要查詢數據需進行全表掃描。有索引的表是一個在索引列上排序了數據表,可通過索引快速定位記錄。在MyISAM和ISAM數據表中,數據行保存在數據文件中,索引保存在索引文件中。BDB與InnoDB數據表把數據與索引放在同一個文件中。

  • 在多表關聯查詢中,索引的作用就更大。如果沒有索引,在最壞的情況下,全表掃描的次數可能是各表數據行的組合個數,可能是一個天文數字。這樣的查詢是破壞性的,可能會造成數據庫癱瘓。

  • 對於使用了MIN()或是MAX()函數的查詢,如果相關的數據列上有索引,MySQL能直接找到該最大、最小值的行,根本不用一個一個地去檢查數據行。

  • 索引加快ORDER BY 和 GROUP BY子句的操作。

  • 當在數值型數據列上查詢數據,而該列有索引,索引能使MySQL根本不用去讀取數據行,直接從索引取值。

索引的缺點:

  • 索引需占用磁盤空間。

  • 索引會減慢在索引數據列上的插入、刪除和修改操作。

索引列的選擇

  • 索引應該創建在搜索、排序、分組等操作所涉及的數據列上。也就是說,在where子句,關聯檢索中的from子句、order by或group by子句中出現過的數據列最適合用來創建索引。

  • 盡量使用唯一索引,它能使索引發揮最好的效能。

  • 盡量用比較短的值進行索引。當對字符串進行索引時,應該指定一個前綴長度,比如對字符串的前10位或20位的字符進行排序,而不用把整個字符串幾十個字符用來索引排序。這樣能減少磁盤I/O,提高處理速度。最重要的一點是,鍵值越短,索引緩沖區裡容納的鍵值也就越多,而MySQL同時保存在內存裡的索引越多,索引緩沖區的命中率也就越高。當然,只對數據列第一個字符進行索引是沒什麼意義的。

  • 充分利用最左前綴。所謂最左前綴也就是在復合索引中最邊的索引列。如復合索引(a,b,c) ,其中a就是最左前綴。它是使用率最高的索引,需認真選擇。

  • 不要建太多索引,索引是會消耗系統資源的,要適可而止。

  • 索引主要用於<、<=、=、>=、>、BETWEEN等的比較操作中,所以索引應該建立在與這樣操作相關的數據列上。

  • 利用慢查詢日志來找出性能差的查詢,通過mysqldumpslow可查看該日志。針對性能差的查詢可利用索引來加快查詢速度。

2.2. 查詢優化程序

當我們發一條查詢命令時,MySQL分對它進行分析,以優化查詢。把explain語名放到查詢前面可顯示查詢的執行路線,對優化查詢提供有用的信息。以下幾個原則可幫助系統挑選和使用索引:

  • 盡量對同類型的數據列進行比較。如:VARCHAR(5)和VARCHAR(5)是同類型的,CHAR(5)和VARCHAR(5)是不同類型的。

  • 盡量讓索引的數據列在比較表達式中單獨出現,不要把它包含在函數或復雜表達式。否則索引會不起作用。

  • 盡量不要在LIKE模式的開頭使用通配符。如:%string%。

  • 對於MyISAM和BDB數據表,用ANALYZE TABLE語句讓服務器對索引鍵值的分布進行分析,為優化程序提供更有價值的信息。另一個方法是用myisamchk --analyze(適用於MyISAM表)或isamchk --analyze(適用於ISAM表)命令。

  • 用EXPLAIN語句來分析查詢語句的執行效率。檢查查詢所使用的索引是不是能夠迅速地排除不符合條件的數據行,如果不是,可以試著用STRAIGHT_JOIN強制各有關數據表按指定順序進行關聯。

  • 嘗試查詢的不同寫法,比較運行情況。

  • 不要濫用MySQL的類型自動轉換功能。自動轉換會減慢查詢的速度並會使有關的索引失效。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved