1、服務(wù)器開發(fā)中的Mysql 數(shù)據(jù)庫開發(fā)優(yōu)化作者：胡章優(yōu)日期：2007-9-4MSN & Mail:huzhangyou20020:引言在CSDN也總看到很多朋友討論數(shù)據(jù)庫優(yōu)化的問題，另外在官方的mysql討論組也有類似的討論，本篇文章以其說出自筆者之手，倒不如說是筆者經(jīng)過閱讀，整理而成的一篇集眾家之長的小paper.大部分概念來自于一些優(yōu)秀的DBA以及相應(yīng)的參考書籍，后面引文中將詳細(xì)列出，如果某部分內(nèi)容屬于您原創(chuàng)，同時筆者沒有注明，或者侵犯了您的權(quán)利，請通過開頭的聯(lián)系方式聯(lián)系筆者，將以最快的速度處理。另外本文在撰寫過程中可能沒有同服務(wù)器開發(fā)連貫起來，因為對于系統(tǒng)來說，他們同樣是服務(wù)器。我們只在使

2、用他作為存儲系統(tǒng)服務(wù)器。需要指出的是MYSQL系統(tǒng)的編譯選項優(yōu)化本文不列入討論，可以參考官方的一些文檔。另外在寫本篇內(nèi)容的時候，對以下人員表示感謝：老婆兵()劉博張博（幫我借書）1:索引正確使用索引的出現(xiàn)驗證了一個叫做 空間換時間的概念，也就是占用多一些的時間，來達(dá)到提高搜索定位匹配效率的概念。從數(shù)據(jù)結(jié)構(gòu)的角度分析，MYSQL的索引都是在B樹1可以搜索 b-tree filetype:pdf可以得到很多相關(guān)的paper,筆者參考了 http:/www.cs.tau.ac.il/ohadrode/Ex/btree_env.pdf中存儲的。而同時索引帶來的代價不僅僅限于上面的內(nèi)容，在數(shù)據(jù)的插入，更

3、新，刪除的時候，操作的速度同樣會有一些損耗，這個應(yīng)該不難理解。就是比如，插入一行新的數(shù)據(jù)時，需要計算索引的位置，并更新索引數(shù)據(jù)，這部分的時間是應(yīng)該考慮進(jìn)去的。所以我們也不是隨便對于一些需要搜索的內(nèi)容就加入索引，或者說不是索引越多越好，有時候不適當(dāng)?shù)氖褂盟饕炊鴷蟮挠绊懶阅?比如頻繁進(jìn)行插入，更新的數(shù)據(jù)表，而搜索查詢比較少時候，應(yīng)該考慮避免索引的使用。比如對于如下數(shù)據(jù)表或者字段，是可以考慮不加入索引的：a) 很小的數(shù)據(jù)表b) 頻繁插入，更新，刪除的表，而搜索比較少另外在嵌套查詢中，對表的順序存取對查詢效率可能產(chǎn)生致命的影響。比如采用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，

4、那么這個查詢就要查詢1000*1000*1000行數(shù)據(jù)。避免這種情況的主要方法就是對連接的列進(jìn)行索引。例如，兩個表：學(xué)生表（學(xué)號、姓名、年齡）和選課表（學(xué)號、課程號、成績）。如果兩個表要做連接，就要在“學(xué)號” 這個連接字段上建立索引。還可以使用并集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強迫優(yōu)化器使用順序存取3有點晦澀，原文參考。上面的例子在理解的時候可能有點晦澀，但是給我們一個比較形象的數(shù)據(jù)概念，就是一個嵌套查詢中，如果3層的話，不使用索引，每層1000行比較，整個比較就達(dá)到了10億次之多，可想而知這是一個多么龐大的計算量。一個列涉及到查找，分組，排序，索引將

5、可以達(dá)到提高性能的效果。這些列包含聯(lián)合查詢的列，或者where,group by,order by 子句4.見Mysql 完全手冊 第 16章 第一節(jié)例子說明一下：Select * from blog join user on blog.userid=user.id where title=服務(wù)器開發(fā) order by id;對于上面的查詢，如果是一種經(jīng)常性的動作，很明顯按照上面的說法，我們應(yīng)該在blog表的userid以及user表的id上面設(shè)置索引，同時由于title是where子句，所以我們也應(yīng)該設(shè)置索引。而blog的id字段屬于order by子句，所以同樣也應(yīng)該設(shè)置索引。應(yīng)該提到的另

6、外一個功能就是如何監(jiān)視索引效率情況。MYSQL 完全手冊一書中第十六章第一節(jié)提到：可以使用SHOW STATUS或者mysqladmin擴展命令顯示。如果索引正在工作，Handler_read_key的值將會很高.下圖為筆者某個服務(wù)器使用SHOW STATUS的查詢結(jié)果報告:上面提到的值中:Handler_read_key代表了一個行索引被值讀的次數(shù).有點不好理解,筆者試圖使用搜索引擎獲取關(guān)于這個值的詳細(xì)信息.-未完待續(xù)Handler_read_rnd_next的高值意味這查詢運行低效,且索引的建立被認(rèn)為是一種補救. -未完待續(xù)另外如果已經(jīng)在表中裝載了數(shù)據(jù),并且按照希望的方式建立了索引,可以經(jīng)

7、常使用 ANALYZE TABLE user,blog;以保證優(yōu)化程序總可以使用最高效率的索引5引自MYSQL完全手冊原文 第十六章 第一節(jié).對于索引的的建立,還應(yīng)該遵循以下規(guī)則:如何包好NULL值的列都不會出現(xiàn)在索引中,既如果某行存在NULL值,即使對他進(jìn)行索引也不會有效率的提高, 任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的6本文已經(jīng)丟失作者,而且很多轉(zhuǎn)載的地方都極其不負(fù)責(zé)任,丟失了里面的SQL語句,還好在CSDN的一個blog中找到: 。索引的創(chuàng)建，一般應(yīng)該盡可量在比較小的字段列上面設(shè)置，這樣將會使得索引占據(jù)較少的資源。所以如果我們將在某

8、一列考慮設(shè)立索引的時候，應(yīng)該盡可量的估計該字段的長度預(yù)設(shè)值，這樣將會帶來比較好的效果。2:數(shù)據(jù)庫類型Innodb,BDB,IASM,MyIASM其他，各自的優(yōu)勢劣勢 本小節(jié)提到的數(shù)據(jù)庫類型,對于MYSQL來說,包含如下幾種:ISAMISAM是一個定義明確且歷經(jīng)時間考驗的數(shù)據(jù)表格管理方法，它在設(shè)計之時就考慮到數(shù)據(jù)庫被查詢的次數(shù)要遠(yuǎn)大于更新的次數(shù)。因此，ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內(nèi)存和存儲資源。ISAM的兩個主要不足之處在于，它不支持事務(wù)處理，也不能夠容錯：如果你的硬盤崩潰了，那么數(shù)據(jù)文件就無法恢復(fù)了。如果你正在把ISAM用在關(guān)鍵任務(wù)應(yīng)用程序里，那就必須經(jīng)常備份你所有的實時數(shù)

9、據(jù)，通過其復(fù)制特性，MySQL能夠支持這樣的備份應(yīng)用程序。MyISAMMyISAM是MySQL的ISAM擴展格式和缺省的數(shù)據(jù)庫引擎。除了提供ISAM里所沒有的索引和字段管理的大量功能，MyISAM還使用一種表格鎖定的機制，來優(yōu)化多個并發(fā)的讀寫操作。其代價是你需要經(jīng)常運行OPTIMIZE TABLE命令，來恢復(fù)被更新機制所浪費的空間。MyISAM還有一些有用的擴展，例如用來修復(fù)數(shù)據(jù)庫文件的MyISAMChk工具和用來恢復(fù)浪費空間的MyISAMPack工具。MyISAM強調(diào)了快速讀取操作，這可能就是為什么MySQL受到了Web開發(fā)如此青睞的主要原因：在Web開發(fā)中你所進(jìn)行的大量數(shù)據(jù)操作都是讀取操作

10、。所以，大多數(shù)虛擬主機提供商和Internet平臺提供商（Internet Presence Provider，IPP）只允許使用MyISAM格式。HEAPHEAP允許只駐留在內(nèi)存里的臨時表格。駐留在內(nèi)存里讓HEAP要比ISAM和MyISAM都快，但是它所管理的數(shù)據(jù)是不穩(wěn)定的，而且如果在關(guān)機之前沒有進(jìn)行保存，那么所有的數(shù)據(jù)都會丟失。在數(shù)據(jù)行被刪除的時候，HEAP也不會浪費大量的空間。HEAP表格在你需要使用SELECT表達(dá)式來選擇和操控數(shù)據(jù)的時候非常有用。要記住，在用完表格之后就刪除表格。讓我再重復(fù)一遍：在你用完表格之后，不要忘記刪除表格。InnoDB和Berkley DBInnoDB和Ber

11、kley DB（BDB）數(shù)據(jù)庫引擎都是造就MySQL靈活性的技術(shù)的直接產(chǎn)品，這項技術(shù)就是MySQL+ API。在使用MySQL的時候，你所面對的每一個挑戰(zhàn)幾乎都源于ISAM和MyISAM數(shù)據(jù)庫引擎不支持事務(wù)處理也不支持外來鍵。盡管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了對事務(wù)處理和外來鍵的支持，這兩點都是前兩個引擎所沒有的。如前所述，如果你的設(shè)計需要這些特性中的一者或者兩者，那你就要被迫使用后兩個引擎中的一個了。如果感覺自己的確技術(shù)高超，你還能夠使用MySQL+來創(chuàng)建自己的數(shù)據(jù)庫引擎。這個API為你提供了操作字段、記錄、表格、數(shù)據(jù)庫、連接、安全帳號的功能，以及建立諸

12、如MySQL這樣DBMS所需要的所有其他無數(shù)功能。深入講解API已經(jīng)超出了本文的范圍，但是你需要了解MySQL+的存在及其可交換引擎背后的技術(shù)，這一點是很重要的。估計這個插件式數(shù)據(jù)庫引擎的模型甚至能夠被用來為MySQL創(chuàng)建本地的XML提供器（XML provider）。（任何讀到本文的MySQL+開發(fā)人員可以把這一點當(dāng)作是個要求。）7同樣該文失去作者，若原創(chuàng)作者看到請聯(lián)系筆者。目前摘錄地址一些筆者對各自格式的使用上的理解分析8這是對不同數(shù)據(jù)庫類型的簡介的介紹：a) MYISAM支持快速讀取操作，并帶有鎖定表格方式，優(yōu)化并發(fā)操作。b) InnoDB以及BDB支持事務(wù)以及外鍵，所以如果您的開發(fā)需要

13、這些功能，請選擇該類型數(shù)據(jù)庫。3:正確選擇字段大小長度由于MYSQL提供了非常多的內(nèi)定字段數(shù)據(jù)類型，所以也給開發(fā)帶來了很多方便，然而方便的同時也應(yīng)該考慮效率的問題，包括正確的選擇字段類型，以及字段長度。比如要存儲一個人的姓名，就不應(yīng)該使用TEXT，而可以使用varchar(20)或者varchar(25)920或者25表示長度，如果再加長，考慮實際情況也沒有必要。開發(fā)中應(yīng)該用下面的技術(shù)使表的性能更好并且使存儲空間最小10本部分內(nèi)容來自： 1. 盡可能地使用最有效(最小)的類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)化類型。 2. 如果可能使表更小，使用較小的整數(shù)類型。例如，MEDIUMINT

14、經(jīng)常比INT好一些。 3. 如果可能，聲明列為NOT NULL。它使任何事情更快而且你為每列節(jié)省一位。注意如果在你的應(yīng)用程序中你確實需要NULL，你應(yīng)該毫無疑問使用它，只是避免缺省地在所有列上有它。 4. 如果你沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。5. 每張表應(yīng)該有盡可能短的主索引。這使一行的辨認(rèn)容易而有效。 6. 對每個表，你必須決定使用哪種存儲/索引方法。7. 只創(chuàng)建你確實需要的索引。索引對檢索有好處但是當(dāng)你需要快速存儲東西時就變得糟糕。如果你主要通過搜索列的組合來存取一個表，以它們做一個索引。第一個索引部分

15、應(yīng)該是最常用的列。如果你總是使用許多列，你應(yīng)該首先以更多的副本使用列以獲得更好的列索引壓縮。 8. 如果很可能一個索引在頭幾個字符上有唯一的前綴，僅僅索引該前綴比較好。MySQL支持在一個字符列的一部分上的索引。更短的索引更快，不僅因為他們占較少的磁盤空間而且因為他們將在索引緩存中給你更多的命中率并且因此有更少磁盤尋道。9. 在一些情形下，分割一個經(jīng)常被掃描進(jìn)2個表的表是有益的。特別是如果它是一個動態(tài)格式的表并且它可能使一個能用來掃描后找出相關(guān)行的較小靜態(tài)格式的表。4:正確使用Like在使用%通配符搜索的時候,對于%huzhangyou%的搜索是無法使用索引的,而可以采取huzhangyou%

16、或者%huzhangyou的搜索方法,以達(dá)到使用索引的目的.這點在搜索的時候一定要注意.比如:select * from employee where last_name like %cliton%和select * from employee where last_name like c%或者select * from employee where last_name like %c5:插入數(shù)據(jù)優(yōu)化11來自插入一個記錄的時間由下列組成： 1. 連接：(3) 2. 發(fā)送查詢給服務(wù)器：(2) 3. 分析查詢：(2) 4. 插入記錄：（1 x 記錄大?。?5. 插入索引：（1 x 索引） 6. 關(guān)

17、閉：(1) 這里的數(shù)字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個并發(fā)運行的查詢做一次)。 表的大小以N log N (B 樹)的速度減慢索引的插入。 加快插入的一些方法： i. 如果你同時從同一客戶插入很多行，使用多個值表的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。 ii. 如果你從不同客戶插入很多行，你能通過使用INSERT DELAYED語句得到更高的速度。iii. 注意，用MyISAM，如果在表中沒有刪除的行，能在SELECT:s正在運行的同時插入行。 iv. 當(dāng)從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多I

18、NSERT語句快20倍。 當(dāng)表有很多索引時，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列過程： 1. 有選擇地用CREATE TABLE創(chuàng)建表。例如使用mysql或Perl-DBI。 2. 執(zhí)行FLUSH TABLES，或外殼命令mysqladmin flush-tables。 3. 使用myisamchk -keys-used=0 -rq /path/to/db/tbl_name。這將從表中刪除所有索引的使用。 4. 用LOAD DATA INFILE把數(shù)據(jù)插入到表中，這將不更新任何索引，因此很快。 5. 如果你有myisampack并且想要壓縮表，在它上面運行myi

19、sampack。 6. 用myisamchk -r -q /path/to/db/tbl_name再創(chuàng)建索引。這將在將它寫入磁盤前在內(nèi)存中創(chuàng)建索引樹，并且它更快，因為避免大量磁盤尋道。結(jié)果索引樹也被完美地平衡。 7. 執(zhí)行FLUSH TABLES，或外殼命令mysqladmin flush-tables。 這個過程將被構(gòu)造進(jìn)在MySQL的某個未來版本的LOAD DATA INFILE。你可以鎖定你的表以加速插入。mysql LOCK TABLES a WRITE;mysql INSERT INTO a VALUES (1,23),(2,34),(4,33);mysql INSERT INTO

20、a VALUES (8,26),(6,29);mysql UNLOCK TABLES;主要的速度差別是索引緩沖區(qū)僅被清洗到磁盤上一次，在所有INSERT語句完成后。一般有與有不同的INSERT語句那樣奪的索引緩沖區(qū)清洗。如果你能用一個單個語句插入所有的行，鎖定就不需要。鎖定也將降低多連接測試的整體時間，但是對某些線程最大等待時間將上升(因為他們等待鎖)。例如：thread 1 does 1000 insertsthread 2, 3, and 4 does 1 insertthread 5 does 1000 inserts如果你不使用鎖定，2、3和4將在1和5前完成。如果你使用鎖定，2、3和

21、4將可能不在1或5前完成，但是整體時間應(yīng)該快大約40%。因為INSERT, UPDATE和DELETE操作在MySQL中是很快的，通過為多于大約5次連續(xù)不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個LOCK TABLES，偶爾隨后做一個UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導(dǎo)致獲得好的性能。當(dāng)然，LOAD DATA INFILE對裝載數(shù)據(jù)仍然是更快的。 為了對LOAD DATA INFILE和INSERT得到一些更快的速度，擴大關(guān)鍵字緩沖區(qū)。6:搜索數(shù)據(jù)優(yōu)化Between and 查詢，in查詢,exists

22、查詢的效率比較，實際的例子效率。1. Order by語句12ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中(象聯(lián)接或者附加等)。任何在Order by語句的非索引項或者有計算表達(dá)式都將降低查詢速度。仔細(xì)檢查order by語句以找出非索引項或者表達(dá)式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在order by子句中使用表達(dá)式。2. NOT我們在查詢時經(jīng)常在where子句使用一些邏輯表達(dá)式，如大于、小于、等于以及不等于等等

23、，也可以使用and(與)、or(或)以及not(非)。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子：. where not (status =VALID) 如果要使用NOT，則應(yīng)在取反的短語前面加上括號，并在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等于()運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例：. where status INVALID 再看下面這個例子：select * from employee where salary3000; 對這個查詢，可以改寫為不使用NOT：select *

24、 from employee where salary3000; 雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。3. IN和EXISTS有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。第一種格式是使用IN操作符：. where column in(select * from . where .); 第二種格式是使用EXIST操作符：. where exists (select X from .where .); 我相信

25、絕大多數(shù)人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠(yuǎn)比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。第二種格式中，子查詢以select X開始。運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作(這里假定在where語句中使用的列存在索引)。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造起來要比IN子查詢困難一些。通過使用EXIST，Oracle系統(tǒng)會首先檢查主查詢，然后運行子查詢直到它找到第一個匹配項，這就節(jié)省了時間。Oracle系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就