mysql培訓(xùn)經(jīng)典教程11第八章

美河學習在線第8是一個表的集合，而表又是行和列的集合。在發(fā)布一條SELECT查詢從表中進行檢索因此，本章將試圖解釋并給出優(yōu)化MySQL的不同方法的一些例子。但是記住總是有我們首先討論索引，因為它是加快查詢的最重要的工具。還有其他加快查詢的技術(shù)，但是最有效的莫過于恰當?shù)厥褂盟饕?。在L以及怎樣為表選擇索引。下一節(jié)，我們將討論MySQL的查詢優(yōu)化程序。除了知道怎樣1000100倍。注意你需要存例如對下面這樣的一個student表： | | |english|chinese|history 12| 66 93 67 56| 78 52 75 10| 54 89 74 4| 99 83 48 39|William 43 96 52 74| 42 40 61 86| 49 85 78 37| 49 63 47 89| 94 31 52 這樣，我們試圖對它進行一個特定查詢時，就不得不做一個全表的掃描，速度很h | |english | 54|William 43| 42| 49| 49 |indexforenglish 42 43 49 49 54 66 78 94 99 如上表，此索引存儲在索引文件中，包含表中每行的english列值，但此索引是在english的基礎(chǔ)上排序的?，F(xiàn)在，不需要逐行搜索全表查找匹配的條款，而是可以利用索引進行查找。假如我們要查找分數(shù)小于60的所有行，那么可以掃描索引，結(jié)果得出5 | |english | 42|William 43| 49| 49| 54 假如有三個未索引的表t1、t2、t3，分別只包含列c1、c2、c3，每個表分別由含有數(shù)值1到1000的1000行組成。查找對應(yīng)值相等的表行組合的查詢?nèi)缦滤荆?0003個相等的值。如果我們在無索引的出與WHERE子句相配的那些組合?？赡艿慕M合數(shù)目為1000×1000×1000（十億），MySQL1000行的情形。如果t2t2t1t3t1進到表t1t1在此情形下，我們?nèi)匀粚Ρ韙1執(zhí)行了一個完全掃描，但能夠在表t2和t3上進行如上所述，MySQL利用索引加速了WHERE子句中與條件相配的行的搜索，或者col1col2上，適當?shù)男锌梢灾苯颖蝗〕?。如果分開的單col1col2上，優(yōu)化器試圖通過決定哪個索引將找到更少的行并來找出mysql>ALTERTABLEtble_nameADDINDEX(col1);mysql>ALTERTABLEtble_nameADDINDEX(col1);果你有一個3行列索引(col1,col2,col3)，你已經(jīng)索引了在(col1)、(col1,col2)和示的SELECT語句：mysql>SELECT*FROMtbl_nameWHEREcol1=val1;mysql>SELECT*FROMtbl_nameWHEREcol2=val2;如果LIKE參數(shù)是一個不以一個通配符字符起始的一個常數(shù)字符串，MySQLmysql>select*fromtbl_namewherekey_colLIKE"Patrick%";mysql>select*fromtbl_namewherekey_colLIKE"Pat%_ck%";考慮有"Pat"<=key_col<"Pau"的行。mysql>select*fromtbl_namewherekey_colLIKEother_col;在第一條語句中，LIKE值以一個通配符字符開始。在第二條語句中，LIKE值不是MySQL通常使用找出最少數(shù)量的行的索引。一個索引被用于你與下列操作符作比較的列：=、>、>=、<、<=、BETWEEN和一個有一個非通配符前綴象'something%'的LIKE的列。WHEREAND層次使用索引，將不使用來索AND條件組中。 /*index=1ORindex=2 /*Noindex /*NoindexspansallrowsMySQL索引(PRIMARY、UNIQUEINDEX)B樹中存儲。字符串是自動地壓縮前綴和結(jié)尾空間。CREATEINDEX句法。如果排序或分組在一個可用索引的最左面前綴上進行(例如，ORDERBYkey_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵一般情況下，如果MySQL能夠知道怎樣用索引來更快地處理查詢，它就會這樣8.4.4節(jié)記錄裝載和修改的速度中，我們將更為詳細地介紹這些性能問題，并討論1換句話說，最適合索引的列是出現(xiàn)在WHERE子句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT關(guān)鍵字后的選擇列表中的列，例如： col_d 當然，所選擇的列和用于WHERE子句的列也可能是相同的。關(guān)鍵是，列出現(xiàn)在選出現(xiàn)在連接子句中的列或出現(xiàn)在形如col1=col2的表達式中的列是很適合索引的列。查詢中的col_b和col_c就是這樣的例子。如果MySQL能利用連接列來優(yōu)化一個2考慮某列中值的分布。對于惟一值的列，索引的效果最好，而具有多個重復(fù)值的”，則對此列進行索引沒有多大用處（。3如果有一個CHAR(200)列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。對前10個或20個字符進行索引能夠節(jié)省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤I/O較少，較短的值比較起來更快。更為重要4在創(chuàng)建一個n列的索引時，實際是創(chuàng)建了MySQL可利用的n個索引。多列索引綴。（這與索引一個列的前綴不同，索引一個列的前綴是利用該的前n個字符作為索引假如一個表在分別名為state、city和zip的三個列上有一個索引。索引中的行是按state/city/zip的次序存放的，因此，索引中的行也會自動按state/city的順序和state的順序存放。這表示，即使在查詢中只指定state值或只指定state和city的值，MySQL也MySQL不能使用不涉及左前綴的搜索。例如，如果按city或zip進行搜索，則不zip代碼（13），則此索引5更多的工作。索引太多，也可能會使MySQL選擇不到所要使用的最好索引。只保持所6LIKE如索引是優(yōu)化查詢的最常用也是最有效的的方法，一個數(shù)據(jù)表，尤其是容量很大的1盡可能地使用最有效(最小)的類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)如果可能使表更小，使用較小的整數(shù)類型。例如，MEDIUMINT經(jīng)常比INT好如果可能，聲明列為NOTNULL。它使任何事情更快而且你為每列節(jié)省一位。2CHAR列而不選擇VARCHAR列。所要權(quán)衡的是使用定長列時，表所占用的空間更特例NULL。4ENUM如果有一個只含有限數(shù)目的特定值的列，那么應(yīng)該考慮將其轉(zhuǎn)換為 BLOBTEXT1、使用BLOB和TEXT用BLOB存儲應(yīng)用程序中包裝或未包裝的數(shù)據(jù)，有可能使原來需要幾個檢索操作才2、使用BLOB和TEXT另一方面，BLOB值也有自己的固有問題，特別是在進行大量的DELETE或UPDATE操作時更是如此。刪除BLOB會在表中留下一個大空白，在以后將需用一個除非有必要，否則應(yīng)避免檢索較大的BLOB或TEXT值。例如，除非肯定WHERE子句能夠?qū)⒔Y(jié)果恰好限制在所想要的行上，否則SELECT*查詢不是一個好辦法。這樣做可能會將非常大的BLOB值無目的地從網(wǎng)絡(luò)上拖過來。這是存儲在另一列中的BLOB標識信息很有用的另一種情形?？梢运阉髟摿幸源_定想要的行，然后從限定的行中檢索BLOB值。3BLOB列更為突出，因為它們尺寸的變化非常大。經(jīng)常使用OPTIMIZETABLE有助于具有諸如“<”或“>=”這樣的操作符的范圍搜索沒有用處）MySQL3.23版及以上版本中，散列值可利用MD5()函數(shù)產(chǎn)生。散列索引對BLOB列特別有用。有一事要注意，在MySQL3.23.2以前的版本中，不能索引BLOB類型。甚至是在3.23.2或更新的版本中，利用散列值作為標識值來查找BLOB值也比搜索BLOB列本身更快。將BLOB在某些情況下，將BLOB列從表中移出放入另一個副表可能具有一定的意義，條件是移出BLOB列后可將表轉(zhuǎn)換為定長行格式。這樣會減少主表中的碎片，而且能利用定ANALYSE如果使用的是MySQL3.23或更新的版本，應(yīng)該執(zhí)行PROCEDUREANALYSE()，maxmemory（8192)analyse嘗試尋找所有不同值的時候應(yīng)該分配給每列的SELECT...FROM...WHERE...PROCEDUREANALYSE([maxmysql>SELECT*FROMstudentPROCEDUREANALYSE();相應(yīng)輸出中有一列是關(guān)于表中每列的最佳列類型的建議。第二個例子要求PROCEDUREANALYSE()不要建議含有多于16個值或取多于256字節(jié)的ENUM類型（可根據(jù)需要更改這些值）。如果沒有這樣的限制，輸出可能會很長；ENUM的定義根據(jù)PROCEDUREANALYSE的輸出，會發(fā)現(xiàn)可以對表進行更改以利用更有效的類型。如果希望更改值類型，使用ALTERTABLE語句即可。8.2.3ANALYSESQL語句EXPLAINSQL當你在一條SELECT語句前放上關(guān)鍵詞EXPLAIN，MySQL到記錄的更快的SELECT。 EXPLAINtbl_name是DESCRIBEtbl_nameSHOWCOLUMNSFROMtbl_name的key_lenMySQLNULLNULL。注意這告訴我們MySQL將實際使用一個多部鍵值的幾個部分。 ExtraOnlyindex，這意味著信息只用索引樹中的信息檢索出的。通ExtrawhereusedWHERE子通過相乘EXPLAIN輸出的rows行的所有值，你能得到一個關(guān)于一個聯(lián)結(jié)要多好的提示。這應(yīng)該粗略地告訴你MySQL必須檢驗多少行以執(zhí)行查詢。 | |type|possible_keys|key|key_len|ref|rows| |student|ALL| |NULL NULL|NULL 13 | |ALL| |NULL NULL|NULL 9|whereused SELECT總的來說，當你想要使一個較慢的SELECT...WHERE更快，檢查的第一件事情是用索引完成。你可以使用EXPLAIN來確定哪個索引用于一條SELECT語句。見7.22EXPLAIN句法(得到關(guān)于一條SELECT的信息)。然，對唯一索引，這總是1。）為了根據(jù)一個索引排序一個索引和數(shù)據(jù)，使用myisamchk--sort-index--sort-MySQLWHERE于DELETE和UPDATE語句。->(aANDbANDc)OR(aANDbANDcAND->B=5OR用一個表時，對任何NOTNULL表達式也這樣做。6、無效常數(shù)表達式的早期檢測。MySQLSELECT語句是不可能的并7、如果你不使用GROUPBY或分組函數(shù)(COUNT()、MIN()……)，HAVING與在一個UNIQUE索引、或一個PRIMARYKEY的WHERE子句一起使用的表，這里所有的索引部分使用一個常數(shù)表達式并且索引部分被定義為NOTmysql>SELECT*FROMt1,t210、對聯(lián)結(jié)表的最好聯(lián)結(jié)組合是通過嘗試所有可能性來找到:(ORDERBY和GROUPBY的列來自同一個表，那么當廉潔時，該表首先被選中。11、如果有一個ORDERBY子句和一個不同的GROUPBY子句，或如果ORDERBY或GROUPBY包含不是來自聯(lián)結(jié)隊列中的第一個表的其他表的列，創(chuàng)建一個臨時13、因為DISTINCT被變換到在所有的列上的一個GROUPBY，DISTINCTmysql>SELECTMAX(key_part2)FROMtbl_namemysql>SELECT...FROMtbl_namemysql>SELECT...FROMtbl_namemysql>SELECTCOUNT(*)FROMtbl_nameWHEREkey_part1=val1ANDkey_part2=val2;mysql>SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;MySQLLEFT在MySQL中，ALEFTJOINB有一個循環(huán)依賴，MySQL將發(fā)出一個錯誤。6AWHEREBLEFT列測試：column_nameISNULL，這里column_name被聲明為NOTNULL的列，那么MySQL在它已經(jīng)找到了匹配LEFTJOIN條件的一行后，將停止在更多的行后尋找(對一MySQL2LIMITORDERBY，MySQL#行，將結(jié)束排然后計算摘要直到鍵值改變。在這種情況下，LIMITGROUP很多時候關(guān)心的是優(yōu)化SELECT查詢，因為它們是最常用的查詢，而且確定怎樣優(yōu)較短的SQL語句比較長的SQL語句要快，因為它們涉及服務(wù)器方的分析較INSERT插入記錄：（1x記錄大小插入索引：（1x索引開INSERT語句快(在一些情況中幾倍)。LOADDATAINFILE。這通常比使用很多INSERT語句快20倍。2FLUSHTABLESmysqladminflush-tables3myisamchk--keys-used=0-rq/path/to/db/tbl_name。這將從表中刪除所有4LOADDATAINFILEmysql>INSERTINTOaVALUES(8,26),(6,29);mysql>UNLOCKTABLES;主要的速度差別是索引緩沖區(qū)僅被清洗到磁盤上一次，在所有INSERT語句完成thread1does1000thread2,3,and4does1thread5does1000如果你不使用鎖定，2、3和4將在1和5前完成。如果你使用鎖定，2、34將可能不在1或5前完成，但是整體時間應(yīng)該快大約40%。因為INSERT,UPDATE和東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個LOCKTABLES，偶爾隨后做一個UNLOCKTABLES(大約每1000行)以允許另外的線程存取表。這仍然將導(dǎo)致獲得好的性能。當然，LOADDATAINFILE對裝載數(shù)據(jù)仍然是更快UPDATESELECT查詢。寫速度依賴于被更新數(shù)據(jù)大這樣做，時不時地OPTIMIZETABLE是非常重要的。DELETE如果表是索引的，則可利用批量插入（LOADDATA或多行的INSERT語句）來減DROPINDEX和CREATEINDEX另一種可供選擇的方法是利用myisamchk或isamchk禁用和啟用索引。這需要在shell>isamchk--keys-used=0tbl_name對具有.MYI擴展名的索引文件的MyISAM表使用myisamchk，對具有.ISM擴展名的索引文件的ISAM表使用isamchk。在向表中裝入數(shù)據(jù)后，按如下激活索引：shell>isamchk--recover--quick--keys-used=0tbl_name一般希望避免在頻繁更新的表上長時間運行SELECT查詢。長時間運行SELECT查詢是INSERT操作，那么先將記錄存入一個臨時表，然后定期地將這些記錄加入主表中。SELECT查詢語句的爭用，因此，執(zhí)行更快。其次，從臨時表將記錄裝入主表的總時間這個策略的一個應(yīng)用是進入Web服務(wù)器的Web頁訪問MySQL數(shù)據(jù)庫。在此情形刷新的另一策略是使用MyISAM表的DELAYED_KEY_WRITE表創(chuàng)建選項（如果將--delayed-key-write選項啟動mysqld即可。在此情形下，索引塊寫操作延遲到必須刷新塊以便為其他索引值騰出空間為止，或延遲到執(zhí)行了一個flush-tables命令后，或延遲到該索引表關(guān)閉。SQLEXPLAINSQL查詢的效SQL語句的原則，主要是檢索記錄和裝載數(shù)據(jù)時如何優(yōu)化SQL語句的原則。安全性來自于如果當寫入一個靜態(tài)MyISAM文件時，你的計算機崩潰，myisamchk能很注意，在MySQL中，所有索引總能被重建。OPTIMIZEtablemyisamchk整理一張表。如果你在同一個表中有象某些VARCHAR或BLOB列那樣存取/改變的靜態(tài)數(shù)內(nèi)存(HEAP堆tab2tab3的聯(lián)結(jié)創(chuàng)建一張臨時表，因為用相同列(tab1.a

tab2.aasa2,tab3.aasa3tab2,tab3tab2.a=tab3.aandc=靜態(tài)(定長)所有的CHAR、NUMERIC和DECIMAL列充填到列寬度。所有字符串列是動態(tài)的(除了那些長度不到4的列)。不同于包含NULL值的列)。如果字符串列在刪除尾部空白后有零長度，或數(shù)字列有零運行myisamchk-r以使性能更好。使用myisamchk-eitbl_name做一些統(tǒng)計。3+(numberofcolumns+7)/+(numberofchar+lengthof+(numberofNULLcolumns+7)/20個字節(jié)，因此下一增大將可能在同一鏈連中。如果不是，將有另外一個鏈接。你可以用myisamchk-ed檢查有多少鏈接。所有的鏈接可以用myisamchk-r刪除。myisampackMySQL擴展電子郵件支持的客戶可以為其內(nèi)部使用保留一個myisampack拷貝。解壓縮代碼存在于所有MySQL分發(fā)，以便甚至沒有myisampack每個記錄被單獨壓縮(很小的存取開銷)。對一個記錄的頭是定長的(1-3字節(jié))，取決值在0到255的范圍，一個BIGINT列(8個字節(jié))可以作為一個TINYINT列(1字節(jié))存能用myisamchk解壓縮。MySQLISAMB樹索引并且VARCHAR列，空白壓縮使索mysqld被關(guān)掉或崩潰，它們將丟失，但是因為它們MySQL2個值之間大概有多少行。這被優(yōu)化器使用來決定使用哪開、關(guān)閉和創(chuàng)建操作將會很慢。如果你執(zhí)行在許多不同表上的SELECT語句，當表緩存MySQL2個線程在間。更改調(diào)度特性還能保證特定的查詢處理得更快。我們先來看一下MySQL的缺省調(diào)索（SELECT）的客戶機程序為讀取程序。執(zhí)行修改表操作（DELETE，INSERT，REPLACE或UPDATE）的另一個客戶機程序為寫入程序。1然后其他客戶在一個使用的表上發(fā)出一個UPDATE；這個客戶將等待直到SELECT完成。另一個客戶在同一個表上發(fā)出另一個SELECT語句；因為UPDATE比SELECT有更高的優(yōu)先級，該SELECT將等待UPDATE的完成。它也將等待第一個SELECT完成！SELECT語句運行得更快；你可能必須創(chuàng)建一些摘要(summary)表做到這用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比在INSERT語句前執(zhí)行。通過使用SQL命令：SETSQL_LOW_PRIORITY_UPDATES=1，你可從一個特定線程指定所有的更改應(yīng)該由用低優(yōu)先級完成。見SETOPTION句法。你可以用HIGH_PRIORITY屬性指明一個特定SELECT是很重要的。見如果你有關(guān)于INSERT結(jié)合SELECT的問題，切換到使用新的MyISAM表，因為它們支持并發(fā)的SELECT和INSERT。如果你有關(guān)于SELECT和DELETE的問題，LIMIT選項的DELETE可以幫助你。見DELETE句法。2、INSERTDELAYED如果其他客戶機可能執(zhí)行冗長的SELECT語句，而且您不希望等待插入完成，此時INSERTDELAYED很有用。發(fā)布INSERTDELAYED的客戶機可以更快地繼續(xù)執(zhí)行，不過應(yīng)該對正常的INSERT和INSERTDELAYED性能之間的差異有所認識。如果INSERTDELAYED存在語法錯誤，則向客戶機發(fā)出一個錯誤，如果正常，便不發(fā)出信息。例如，在此語句返回時，不能相信所取得的AUTO_INCREMENT值。也得不到惟個狀態(tài)。其他還表示，如果INSERTDELAYED語句的行在等待插入中被排隊，并且服務(wù)器崩潰或被終止（用kill-9），那么這些行將丟失。正常的TERM終止不會這樣，服表的鎖?？梢灾苯佑肔OCKTABLES來完成這項工作，但一般服務(wù)器的鎖管理器會在需UNLOCKTABLES釋放，但服務(wù)器也會自動釋放它所獲得的鎖。MySQL允許借助幾個查詢限修飾符對其調(diào)度策略施加影響。其中之一是DELETE、INSERT、LOADDATA、REPLACE和UPDATE語句的關(guān)鍵字。另一個是SELECT語句的HIGH_PRIORITY關(guān)鍵字。第三個是INSERTREPLACE語句的DELAYED如果寫入請求為LOW_PRIORITY的請求，則不將該寫入操作視為具有比讀取操作種調(diào)度的更改從理論上說，其含義為LOW_PRIORITY寫入可能會永遠被阻塞。當正在處理前面的讀取請求時，只要另一個讀取請求到達，這個新的請求允許排在LOW_PRIORITY寫入之前。SELECT查詢的HIGH_PRIORITY關(guān)鍵字作用類似。它使SELECT插在正在等待INSERT的DELAYED修飾符作用如下，在表的一個INSERTDELAYED請求到達此調(diào)度修飾符并非出現(xiàn)在所有MySQL版本中。下面的表列出了這些修飾符和支持這些修飾符的MySQL版本?？衫么吮韥砼袛嗨褂玫腗ySQL版本具有什么樣的功 LOADDATALOW_PRIORITY3.23.0LOCKTABLES...LOW_PRIORITY3.22.8REPLACELOW_PRIORITY3.22.5REPLACEDELAYED3.22.15SELECT...HIGH_PRIORITY UPDATELOW_PRIORITY3.22.5 長類型CHAR。特別是進行大量的DELETE或UPDATE操作時更是如此。刪除BLOB會在表中本節(jié)介紹了有關(guān)數(shù)據(jù)表的優(yōu)化技巧，主要內(nèi)容有，選擇表的類型，打開盡量少的的數(shù)據(jù)庫，應(yīng)該經(jīng)常使用myisamchk維護程序來清除碎片，優(yōu)化表時要選擇一個好的時前面各段介紹了普通的MySQL用戶利用表創(chuàng)建和索引操作，以及利用查詢的編寫能夠進行的優(yōu)化。不過，還有一些只能由MySQL管理員和系統(tǒng)管理員來完成的優(yōu)化，這些管理員在MySQL服務(wù)器或運行MySQL的機器上具有控制權(quán)。有的服務(wù)器參數(shù)直上，并且第n塊在第(nmodnumber