在實(shí)際工作中，無(wú)論是對數據庫系統(DBMS)，還是對數據庫應用系統(DBAS)，查詢(xún)優(yōu)化一直是一個(gè)熱門(mén)話(huà)題。一個(gè)成功的數據庫應用系統的開(kāi)發(fā)，肯定會(huì )在查詢(xún)優(yōu)化上付出很多心血。對查詢(xún)優(yōu)化的處理，不僅會(huì )影響到數據庫工作效率，而且會(huì )給公司帶來(lái)實(shí)實(shí)在在的效益問(wèn)題。MySQL是一個(gè)開(kāi)源軟件，但它的性能絲毫不遜于商業(yè)數據庫，它的速度已經(jīng)相當快。但要想做好查詢(xún)優(yōu)化，還要考慮一些技巧上的東西。下面是我學(xué)習和使用MySQL以來(lái)的一些對優(yōu)化查詢(xún)的感受。
首先，我們應該考慮哪些因素能影響到查詢(xún)優(yōu)化?可以分為：機器硬件、索引、系統參數、查詢(xún)技巧等。對于一個(gè)查詢(xún)優(yōu)化問(wèn)題，我們應該首先考慮哪各方面呢?索引的使用應該再考慮的首條，毋庸置疑的是，在沒(méi)有使用索引的前提下，想用其它招數來(lái)大幅度改善性能的做法往往收獲甚微，純粹是在浪費時(shí)間，可事實(shí)無(wú)絕對，有些場(chǎng)合反而需要去抑制優(yōu)化程序的行為。
我們先來(lái)說(shuō)說(shuō)索引。索引能提高查詢(xún)效率的原因之一是它可以讓我們知道最后一個(gè)符合條件的數據行出現在什么位置，此后的數據行都用不著(zhù)在檢查了，另一個(gè)原因是人們已經(jīng)發(fā)明了很多中定位算法來(lái)迅速查出第一個(gè)符合條件的數據行出現在什么位置，用不著(zhù)從索引的開(kāi)頭通過(guò)線(xiàn)性?huà)呙璺ㄈザㄎ灰粋€(gè)匹配項。
MySQL在建立索引時(shí)，對不同的數據表有不同的細節：MyISAM數據表，數據行將被保存在數據文件里，其索引值將被保存在索引文件里，而B(niǎo)DB處理程序把同一個(gè)BDB數據表的數據值和索引值保存在同一個(gè)文件里，InnoDB處理程序則是把所有InnoDB數據表的數據值和索引值都保存在同一個(gè)表空間里。這些看似與建立索引無(wú)關(guān)，其實(shí)是很重要的，待會(huì )我后說(shuō)道。還有，索引不僅能給單據表帶來(lái)好處，然而，索引給涉及多個(gè)數據表的關(guān)聯(lián)查詢(xún)帶來(lái)的好處就更打了。
建立索引也要考慮它的缺點(diǎn)。首先，索引需要消耗磁盤(pán)空間(現在磁盤(pán)已經(jīng)很便宜了)，索引越多，消耗的空間也就有多。對于MyISAM數據表，過(guò)多的索引會(huì )使索引文件優(yōu)先與數據文件達到尺寸上限。對于BDB數據表，因為它把數據值和索引值都保存在同一個(gè)文件里，所以增加索引必然會(huì )使他更快地達到BDB數據文件的尺寸上限。InnoDB數據表共同分享著(zhù)InnoDB表空間里的存儲空間，所以增加索引必然會(huì )加快InnoDB表空間的消耗速度。不過(guò)，只要還能增加磁盤(pán)空間，就能通過(guò)給InnoDB表空間增加組件的辦法擴充之。
其次，索引過(guò)多，會(huì )減慢被索引的數據列上的插入、刪除和修改操作的速度。因為在寫(xiě)入記錄時(shí)，MySQL還必須修改與之有關(guān)的所有索引。
下面我們考慮應該選擇什么樣的數據列來(lái)創(chuàng )建索引?怎樣才能創(chuàng )建出最使用的索引?怎樣處理數據類(lèi)型給索引帶來(lái)的影響?原則是：
1、 引應該創(chuàng )建在搜索、排序、歸組等操作所涉及的數據列上，只在輸出報告里出現的數據列不是好的候選。
2、 盡量使用唯一化索引。因為如果數據列里有很多彼此重復的值，建立在其上的索引就不會(huì )有好的效果。
3、 盡量對比較短的值進(jìn)行索引。
4、 對于復合索引，MySQL會(huì )先匹配它的第一個(gè)索引列，如果匹配不上它就不會(huì )使用這個(gè)索引了。
5、 不要建立過(guò)多的索引。
6、 考慮將在這個(gè)數據列上進(jìn)行怎樣的比較操作。對于HEAP數據表來(lái)說(shuō)，它只會(huì )用“=”來(lái)比較，其他比較操作就幫不上忙了。
7、 利用慢查詢(xún)日志來(lái)找出那些性能低劣的查詢(xún)。不過(guò)不要認為這是絕對的，因為MySQL會(huì )將凡是沒(méi)使用索引的查詢(xún)一并寫(xiě)入這個(gè)日志里，要有選擇的查看這個(gè)日志哦。
8、 要盡量把數據列聲明為NOT NULL，這樣可以排除在對空值的查詢(xún)，在有些時(shí)候，能起到很大的作用。
9、 對于字符串數據列，如果其取值是有限的，應優(yōu)先考慮使用ENUM類(lèi)型，因為MySQL是用處理數值型的方法來(lái)處理這種字符串的，速度比處理字符串要快得多。
10、 對于那些聲明為可變長(cháng)度的數據列(如varchar型)，時(shí)間長(cháng)了會(huì )使得數據表出現很多碎片，減慢查詢(xún)效率，我們可以用OPTIMIZE TABLE語(yǔ)句對之進(jìn)行整理。
11、 將數據壓縮到BLOB數據列里，就能只通過(guò)一次性查詢(xún)找到數據，或者把BLOB數據列單獨存入一個(gè)文件。
12、 有時(shí)我們可以人為的為一個(gè)數據表建立索引，然后查詢(xún)這個(gè)索引達到快速查詢(xún)的目的。例如：我們先給一個(gè)數據表再建一個(gè)數據列，根據數據表里其他數據列計算出一個(gè)散列值并保存到剛建的數據列里，這樣就可以通過(guò)搜索散列值的方法去檢索想要找到數據行。
MySQL查詢(xún)優(yōu)化程序是盡可能的使用索引，而且是盡可能地使用那些最為挑剔的索引，以便盡可能多和盡可能快的排除那些不符合查詢(xún)條件的數據行?？梢砸勒找韵略瓌t幫助優(yōu)化程序：
1. 盡量對同類(lèi)型的數據列進(jìn)行比較，必要時(shí)可以先用ALTER TABLE語(yǔ)句把其中之一的類(lèi)型修改為與另一個(gè)數據列相同。
2. 盡量讓已經(jīng)索引的數據列在比較表達式中單獨出現。
3. 盡量減輕模糊匹配查詢(xún)，例如：不要在LIKE模式的開(kāi)頭使用通配符等。
4. 多試幾種查詢(xún)命令。
5. 不要濫用MySQL的類(lèi)型自動(dòng)轉換功能，如：數字3就不能寫(xiě)成字符‘3’，加重MySQL的類(lèi)型轉換，會(huì )使它的性能下降。
下面說(shuō)說(shuō)加載數據。
加載數據時(shí)要采用批量加載，盡量減少MySQL對索引的刷新率，例如：LOAD DATA 語(yǔ)句要比INSERT 語(yǔ)句效率高，如果必須使用INSERT 語(yǔ)句，請盡量使他們集中在一起，減少對索引的刷新次數。對于支持事務(wù)處理機制的數據表類(lèi)型，應該把這些INSERT 語(yǔ)句放在同一個(gè)事務(wù)里，對于不支持事務(wù)處理機制的數據表類(lèi)型，應該現對數據表進(jìn)行寫(xiě)鎖定，然后在數據表鎖定期間發(fā)出這些INSERT語(yǔ)句。對于大量數據，可以先加載數據在建立索引。
在機器硬件方面：
1. 應為機器安裝更多內存。
2. 購置高速磁盤(pán)以縮短I/O等待時(shí)間。
3. 使用多個(gè)物理硬盤(pán)，提高并行操作能力。
4. 使用多個(gè)處理器。
在一個(gè)具體的數據庫應用系統中，要根據實(shí)際需要來(lái)采用各種優(yōu)化策略，以便使顧客與公司達到雙贏(yíng)的目的。
聯(lián)系客服
