MySQL數據庫優(yōu)化（一）

作者：葉金榮，出處:IT專家網，責任箱輯：李書琴，

2008-06-0609:30

數據庫優(yōu)化是一項很更雜的工作，因為這最終需耍對系統(tǒng)優(yōu)化的很好理解才行。盡管對系統(tǒng)或應用系統(tǒng)的了解不多

的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。

國

數據庫優(yōu)化是?項很攵雜的工作，因為這最終需要對系統(tǒng)優(yōu)化的很好理的才行。盡管對系統(tǒng)或應用系統(tǒng)的了解不多

的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。

本章主要講解了幾種優(yōu)化MySQL的方法，并且給出了例子。記著，總有各種辦法能讓系統(tǒng)運行的更快，當然了，這

需要更多的努力。

1優(yōu)化概述

讓系統(tǒng)運行得快得最重要因素是數據庫基本的設計。并且還必須清楚您的系統(tǒng)要川來做什么，以及存在的瓶頸。

最常見的系統(tǒng)瓶頸有以下幾種：

進盤搜索.它慢慢地在磁盤中搜索數據塊.對現代磁盤來設，平時的搜索時間基本上小于10富秒，因此理論上每秒

鐘可以做100次磁盤搜索。這個時間對于全新的新磁盤來說提高的不多，并且對于只有一個表的情況也是如此。加快搜

索時間的方法是將數據分開存放到多個磁盤中。

磁盤讀/寫。當磁盤在正確的位置上時，就需要讀取數據。對現代磁盤來說，磁盤吞吐玨至少是10-20MB/杪。這比

磁盤搜索的優(yōu)化更容易，因為可以從多個媒介中并行地讀取數據。

CPU周期。數據存儲在主內存中（或者它己經在主內存中了），這就需要處理這些數據以得到想要的結果。存在多個？

嗣啾饒佳媛禁坷此蹈竅拗頻囊蛋亍?2還孕"硅此擔俁韌U2皇俏侍狡？

內存帶寬.當CPU要將更多的數據存放在CPU緩存中時，主內存的帶寬就是瓶頸了。在大多數系統(tǒng)中，這不是常

見的瓶頸，不過也是要注意的一個因素。

1.1MySQL設計的局限性

當使川MylSAM存儲引擎時,MySQL會使川一個快速數據表鎖以允許同時多個讀取和一個寫入。這種存儲弓擎的

最大問題是發(fā)生在一個單一的表上同時做穩(wěn)定的更新操作及慢速查詢。如果這種情況在某個表中存在，可以使用另一種

表類型。詳情請看”15MySQLStorageEnginesandTableTypes",

MySQL可以同時在事務及非事務表下工作。為了能夠平滑的使用非事務表（發(fā)生錯誤時不能回滾），有以

下幾條規(guī)則：

所有的字段都有汰認但

如果字段中插入r一個“錯誤"的值，比如在數字類型字段中插入過大數值，那么MySQL會將該字段伯置為"最可能的他■?而不是給出

一個錯誤。數字類型的殖是0,最小或者最大的可能燒。字符串類型，不是空字符串就是字段所能存儲的最大長度。

所有的計算表達式都會返回?個值而報告條件錯誤，例如1/0返回NULL：

這些規(guī)則隱含的意思是，不能使用MySQL來檢查字段內容。相反地，必須在存儲到數據庠前在應用程序中來檢查.

詳情請看18.6HowMySQLDealswithConstraints和"1414INSERTSyntax".

1.2應用設計的可移植性

由于各種不同的數據庫實現了各自的SQL標準，這就需要我們盡量使用可移植的SQL應用。查詢和插入操作很容

易就能做到可移植，不過由r?更多的約束條件的要求就越發(fā)困難.想要讓一個應用在各種數據庫系統(tǒng)上快速運行，就變

得更困難了。

為了能讓?個復雜的應用做到可移植，就要先看這個應用運行于哪種數據庫系統(tǒng)之上，然后看這些數據庫系統(tǒng)都支

持哪些特性。

每個數據庫系統(tǒng)都有某些不足。也就是說，由于設計上的一些妥協(xié)，導致了性能上的差異。

可以川MySQL的crash-me程序來看選定的數據庫服務器上可以使川的函數，類型，限制等。crash-me不會檢杳

各種可能存在的特性，不過這仍然是合乎情理的理解，大約做了450次測試。

?個crash-me的信息類型的例子就是，它會告訴您如果想使用Informix或DB2的話，就不能使字段名長度超過

18個字符。

crash-me程序和MySQL基隹使將個準數據庫都實現了的?？梢酝ㄟ^閱讀這些基設程序是怎么寫的，自己就大概仃

怎樣做才能讓程序獨立于各種數據庫這方面的想法廣。這些程序可以在MySQL源代碼的'sql-bench'目錄下找到。他

們大部分都是用Perl寫的，并且使用DBI接口。由于它提供了獨立于數據庫的各種訪問方式，因此用DBI來解決祚種移

他性的問題。

想要看.crash-m.的結果，可以訪問：.com/tech-resources/crash-me.php.訪.可以看到基準的結果。

如果您想努力做到獨立于數據庫，這就需要對各種SQL服務器的瓶頸都有一些很好的想法。例如，MySQLXf丁

MylSAM類型的表在檢索以及更新記錄時非?？?，但是在有并發(fā)的慢速讀取及寫入記錄時卻有一定的問題。作為Oracle

來說，它在訪問剛剛被更新的記錄時有很大的問題（直到結果被刷新到磁盤中）.事務數據庫?般地在從口志表中生成摘

要表這方面的表現不怎么好，因為在這種情況下，行記錄鎖幾乎沒用。

為了能讓應用程序真正的做到獨立于數據庫，就必須把操作數據的接口定義的簡單且可擴展。由于C++在很多系統(tǒng)

上都可以使川，因此使用C++作為數據庫的基類結果很合適。

如果使用了某些數據庫獨有的特定功能（比如REPLACE語句就只在MySQL中獨有），這就需要通過編寫替代方法

來在其他數據庫中實現這個功能。盡管這些替代方法可能會比較慢，但是它能讓其他數據庫實現同樣的功能。

在MySQL中，可以在查詢語句中使用/*!*/語法來增加MySQL特有的關鍵字。然而在很多其他數據庫中，廣/卻

被當成了注釋（并且被忽略）。

如果有時候更高的性能比數據結果的精確更重要，就像在一些Web應用中那樣，這可以使用一個應用層來緩存結果,

這可能會有更高的性能“通過讓舊數據在一定時間后過期，來合理的更新緩存。這是處理負載高峰期時的一種方法，這

種情況下，可以通過加大緩存容量和過期時間直到負我趨于正常。

這種情況E,建表信息中就要包含了初始化緩存的容量以及正常刷新數據表的頻率。

一個實現應用層緩存的可選方案是使用MySQL的查詢緩存（querycache）。啟用查詢緩存后，數據庫就會根據一些

詳情來決定哪些結果可以被重用。它大大簡化了應用程序，詳情請看”5.11TheMySQLQueryCache”。

1.3我們都用MySQL來做什么

本章描述了一個MySQL的早期應用。

在MySQL最開始的開發(fā)過程中，MySQL本來是要準備給大客戶用的，他們是瑞典的2個最大的零售商，他們用于

貨物存儲數據管理。

我們每周從所有的商店中得到交易利澗累計結果，以此給商店的老板梃供有用的信息.，加助他們分析如果更好的打

廣告以影響他們的客戶。

數據量相當的大（每個月的交易累計結果大概有7百萬），而且還需要顯示4-10年間的數據。我們每周都得到客戶的

需求，他們要求能‘瞬間’地得至!數據的最新報表.

我們把每個月的全部信息存儲在一個壓縮的‘交易’表中以解決這個問題。我們有一些簡單的宏指令集，它們能根

據不同的標準從存儲的‘交易'表中根據字段分組（產品組、客戶id、商店等等）取得結果。我們用一個小Perl腳本動態(tài)

的生成Web頁面形式的報表。這個腳本解析Web頁面，執(zhí)行SQL語句，并且插入結果。我們還可以用PHP或者

mod_perl來做這個工作，不過當時還沒有這2個工具。

為了得到圖形數據，我們還寫了一個簡單的C語言工具，用于執(zhí)行SQL查詢并且將結果做成GIF圖片。這個工具

同樣是Perl腳木解析Web頁面后動態(tài)執(zhí)行的。

很多情況下，只要拷貝現有他腳本簡單的修改里面的SQL查詢語句就能產生新的報表了.有時候，就需要在現存的

累計表中增加更多的字段或者新建一個。這個操作I?分簡單，因為我們在磁盤上存儲有所有的交易表（總共大概有50G

的交易表以及20G的其他客戶資料）。

我們還允許客戶通過ODBC直接訪問累計表，這樣的話，那些高級用戶就可以自己利用這些數據做試驗了°

這個系統(tǒng)工作的很好，并且在適度的SunUltraSPARC工作站（2x200MHz）上處理數據沒有任何問題。最終這個系

統(tǒng)移植到/Linux上。

1.4MySQL基準套件

本章本來要包括MySQL基準套件（以及crash-me）的技術描述的，但是至今還木寫?，F在，您可以通過直看MySQL

發(fā)布源代碼'sql-bench'目錄下的代碼以及結果有一個更好的想法。

基準套件就是想告訴用戶執(zhí)行什么樣的SQL查詢表現的更好或者更差.

請注意，這個基準是單線程的，因此它度量了操作執(zhí)行的最少時間。我們未來打算增加多線程測試的基準套件。

想要使用基準套件，必備以下幾個條件：

基準套件在MySQL的發(fā)布源代碼中就有?？梢匀ハ螺d發(fā)布版或者使用現有開發(fā)代碼樹（詳情請看23.3Installing

fromtheDevelopmentSourceTree"）.

基準腳本是用Perl寫的，它用Perl的DBI模塊來連接數據庫，因此必須安裝DBI模塊.并且還需要每個要做測試

的服務器上都有特定的BDB.驅動程序。例如，為了測試MySQL、PostgeSQL和DB2,就必須安裝DBD::mys。，

DBD::Pg及DBD::DB2模塊。。情請看”2.7PerlInstallationNote%

取得MySQL的分發(fā)源代碼后，就能在'sql-bench'目錄下看到基準套件。想要運行這些基準測試，請先搭建好股

務，然后進入'sql-bench'目錄,執(zhí)行run-all-tests腳本：

shell>cdsql-bench

shell>perirun-all-tests-server=server_name

server_name可以是任何一個可用的服務。想要列出所有的可用優(yōu)項和支持的服務，只要調用以下命令：

shell>perlrun-all-tests-help

crash-me腳本也是放在'sql-bench,目錄下。crash-me通過執(zhí)行真正的查詢以試圖判斷數據陣都支持

什么特性、性能表現以及限制。例如，它可以判斷：

都支持什么字段類型

支持多少索引

支持什么樣的函數

能支持多大的查詢

VARCHAR字段類型非支持多大

可以從上找到各種不同數據庫crash-me的結果。更多的信息請訪問。

1.5使用您自己的基準

請確定對您的數據庫或者應用程序做基準測試，以發(fā)現它們的腰頸協(xié)任。解決這個骯頸(或者使用一個假的模決來代

替)之后，就能很容易地找到下一個瓶頸了.即使應用程序當前總體的表現可以接受，不過還是至少要做好找到每個粒頸

的計劃，說不定某天您就希望應用程序能有更好的性能。

從MySQL的基準套件中就能找到一個便攜可移植的基準測試程序了。詳情請看”7.1.4TheMySQLBenchmark

Suite"。您可以從基準套件中的任何一個程序，做適當的修改以適合您的需要。通過整個方式，您就可以有各種不同的辦

法來解決問題，知道哪個程序才是最快的。

另一個基準套件是開放源碼制數據庫基準，可以在.net上找到。

力系統(tǒng)負載十分繁里的時候，通常就會發(fā)生問題。我們就有很多客戶我系我們說他們有一個(測試過的)生產系統(tǒng)也

遭遇了負載問題。在很多情況下，性能問題歸結于數據庫的基本設計(例如，在高負載下掃描數據表的表現不好)、噪作系

統(tǒng)、或者程序庫等因素。很多時候，這些問題在還沒有正式用于生產前相對更容易解決。

為了避免發(fā)生這樣的問題，垠好讓您的應用程序在可能的最差的負載卜做基準測試何以使用SuperSmack,

可以找到。從它名字的意思就能想到，只要您愿意，它就能讓您的系統(tǒng)死抻，因此確認只在開發(fā)系統(tǒng)上做測試。

2優(yōu)化SELECT語句及其他查詢

首先，影響所有語句的一個位素是：您的權限設置越紅雜，那么開箱就越大。

使用比較簡小的GRANT語句能讓MySQL減少在客戶端執(zhí)行語句時權限檢查的開銷。例如，如果沒有設定任何表

級或者字段級的權限，那么服務器就無需檢查tables_priv和columns_priv表的記錄了.同樣地，如果沒有對帳戶設定

任何資源限制的話，那么股務器也就無需做資源使用統(tǒng)計了。如果有大量直詢的話，花點時間來規(guī)劃簡單的授權機制以

減少服務器權限檢查的開銷是值得的.

如果問題處在一些MySQL特定的表達式或者函數上，則可以通過mysql客戶端程序使用BENCHMARK()

函數做一個定時測試。它的語法是：BENCHMARK(loop_count,expression)o例如：

mysql>SELECTBENCHMARK(1OOOOOO,1+1);

++

IBENCHMARK(1000000,1+1)|

+-...............-...................+

I0|

+-...............-...................+

1rowinset（0.32sec）

上述結果是在PentiumII400MHz的系統(tǒng)上執(zhí)行得到的。它告訴我們：MySQL在這個系統(tǒng)上可以在0.32秒內執(zhí)行

1,000,000次簡單的加法運算。

所有的MySQL函數都應該被最優(yōu)化，不過仍然有些函數例外，BENCHMARKO是一個用「檢查查詢語句中是否存

在問題的非常好的工具。

MySQL數據庫優(yōu)化（二）

作者：葉金榮，出處:IT專家網，責任期輯：李書琴，

2008-06-11（19:55

EXPLAIN語句可以被當作DESCR舊E的同義詞來用，也可以川來茯取一個MySQL要執(zhí)行的SELECT語句的

相關信息八FXPIAINthLnamP語法和RFSCRIRFthLnamP或SHOV/COIIJMNSFROMthLnamP一樣-

MySQL數據庫優(yōu)化（一）

1.EXPLAI.語法（得至IJSELEC.的相關信息）

EXPLAINtblname

或者：

EXPLAINSELECTselectoptions

tablelN語句可以被當作DESCR舊E的同義詞來用，也可以用來獲取一個MySQL要執(zhí)行的SELECT語句的相

關信息。EXPLAINtbLname語法和DESCR舊Elbl_name或SHOWCOLUMNSFROMtbl.name一樣。當在個

SELECT語句前使用關鍵字EXPLAIN時，MYSQL會解釋了即將如何運行該SELECT語句，它顯示了表如何連接、

連接的順序等信息。本章節(jié)主要講述了第二種EXPLAIN用法。

在EXPLAIN的幫助下，您就知道什么時候該給表添加索引，以使用索引來查找記錄從而讓SELECT運行更快。

如果由于不恰當使用索引而引起一些問題的話，可以運行ANALYZETABLE來更新該表的統(tǒng)計信息，例如鍵的基

數，它能幫您在優(yōu)化方面做出更好的選擇。

您還可以杳看優(yōu)化程序是否以最佳的順序來連接數據表。為J'讓優(yōu)化程序按照SELECT語句中的表名的順序做連

接，可以在查詢的開始使用SELECTSTRAIGHT_JOIN而不只是SELECT.

EXPLAIN返回/一行記錄，它包括「SELECT語句中川到的各個袤的信息。這些表在結果中按照MySQL即將執(zhí)

行的查詢中讀取的眼序列出來。MySQL用一次掃描多次連接（single-sweep,multi-join）的方法來解決連接。這意味著

MySQL從第一個表中讀取一條記錄，然比在第二個表中查找到時應的記錄，然后在第三個表中查找，依次類推。當所有

的表都掃描完了，它輸出選擇的字段并且回溯所有的表，宜到找不到為止，因為有的表中可能有多條匹配的記錄下一條

記錄將從該表讀取，再從卜.一個表開始繼續(xù)處理。

在MySQLversion4.1中，EXPLAIN輸出的結果格式改變了，使得它更適合例如UNION語句、子查詢以及派生表

的結構。更令人注意的是，它新增了2個字段：id和selectjype,當你使用早于MySQL4.1的版本就看不到這些字

段了。

EXPLAIN結果的每行記錄顯示了每個表的相關信息，每行記錄都包含以下幾個字段：

id

本次SELECT的標識符.在杳詢中每個SELECT都有一個順序的數值。

select_type

SELECT的類型，可能會有以卜幾種：

SIMPLE

簡單的SELECT（沒有使用JNION或子查詢）

PRIMARY

最外層的SELECT0

UNION

第二層，在SELECT之后使用了UNION.

DEPENDENTUNION

UNION語句中的第二個SELECT,依賴于外部子查詢

SUBQUERY

子查聞中的第一個SELECT

DEPENDENTSUBQUERY

子杳詢中的第一個SUBQUERY依賴于外部的子查詢

DERIVED

派生表SELECT（FROM子句中的子查詢）

table

記錄查詢引用的表.

type

衣性按類型。以下列出「各種不同類型的表連按，依次是從最好的到最爰的:

system

表只有一行記錄（等于系統(tǒng)表）。這是const表連接類型的?個特例。

const

表中最多只有一行匹配的記錄，它在查i旬一開始的時候就會被讀取出來。由于只有一行記錄，在余卜的優(yōu)億程

序里該行記錄的字段值可以被當作是一個恒定值。const表杳詢起來非?？?，因為只要讀取一次!const用于在和

PRIMARYKEY或UNIQUE索引中有固定值比較的情形。下面的幾個查詢中,tbl_name就是const表了：

SELECT*FROMtblnameWHEREprimarykey=1;

SELECT*FROMtbl_name

WHEREprimary_key_part1=1ANDprimary_key_part2=2;

eq_ref

從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯(lián)合。與const類型不同的是，這是

最好的連接類型。它用在索引所有部分都用于做連接并且這個索引是一個PRIMARYKEY或UNIQUE類型。

eq_ref可以用于在進行三"做比較時檢索字段。比較的值可以是固定值或者是表達式，表達式中可以使用表里的字

段，它們在讀表之前已經準備好了.以下的幾個例子中，MySQL使用了eq_ref連接來處理refjable:

SELECT,FROMrefjable.otierjable

WHEREref_table.key_column=other_table.column;

SELECT*FROMrefjable.olierjable

WHEREret_table.key_column_part1=other_table.column

ANDref_table.key_column_part2=1：

該表中所布■符合檢索值的記錄都會被取出來和從上?個表中取出來的記錄作聯(lián)合。ref用于連接程序使用犍的

最左前綴或者是該鍵不是PRIMARYKEY或UNIQUE索引（換句話說，就是連接程序無法根據鍵值只取得一條

記錄）的情況。當根據鍵值只查詢到少數幾條匹配的記錄時，這就是?個不錯的連接類型。ref還可以用于檢索字

段使川=操作符來比較的時候。以下的幾個例子中，MySQL將使用ref來處理refjable:

SELECT*FROMrefjableWHEREkey_column=expr;

SELECT*FROMreftable.otiertable

WHEREref_table.key_column-other_table.column;

SELECT,FROMreftable.otierjable

WHERErefjable.key_column_part1=other_table.column

ANDref_table.key_column_part2=1;

refornull

這種連接類型類似ref,不同的是MySQL會在檢索的時候額外的搜索包含NULL值的記錄。這種連接類型

的優(yōu)化是從MySQL4.1.1開始的，它經常用于子查詢。在以下的例子中，MySQL使用ref_or_null類型來處理

ref_table:

SELECT*FROMrefjable

WHEREkey_column=exprORkey_columnISNULL：

in<Jex_merge

這種連接類型意味著使用了IndexMerge優(yōu)化方法。這種情況下,key字段包括了所有使用的索引，

keyjen包括了使用的鍵的最長部分。詳情請看“725HowMySQLOptimizesORClauses",

uniquesubquery

這種類型用例如一下形式的N子查詢來替換ref:

valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)

uniquesubquery只是用來完全替換子查詢的索引查找函數效率更高了。

index_subquery

這種連接類型類似unique_subquery。它用子查詢來代替IN,不過它用于在子查詢中沒有唯一索引的情況下，例如

以下形式：

valueIN(SELECTkey_columnFROMsinglejableWHEREsome_expr)

range

只有在給定范圍的記錄才會被取出來，利用索引來取得一條記錄。key字段表示使用了哪個索引。keyjen字

段包括了使用的鍵的最長部分,這種類型時ref字段值是NULL.,rarge用于將某個字段和一個定植用以卜任何

操作符比較時=,<>,>,>=,<,<=,ISNULL,<=>,BETWEEN,或IN:

SELECT*FROM

WHEREkey_column=10；

SELECT*FROMtbl_name

WHEREkey_columnBETWEEN10and20；

SELECT*FROMtbl_nam@

WHEREkey_columnIN(10,20,30);

SELECT,FROMtbl_name

WHEREkey_part1=10ANDkey_part2IN(10,20,30);

index

連接類型跟ALL一樣，不同的是它只掃描索引樹。它通常會比ALL快點，因為索引文件通常比數據文

件小。MySQL在查詢的字段知識碓獨的索引的一部分的情況下使用這種連接類型.

ALL

將對該表做全部掃描以和從打一個表中取得的記錄作聯(lián)合。這時候如果第一個表沒有被標識為const的話就不大好

了，在其他情況下通常是非常糟糕的。正常地，可以通過增加索引使得能從表中更快的取得記錄以避免ALL”

possible_keys

possible_keys字段是指MySQL在搜索表記錄時可能使用哪個索引.注意，這個字段完全獨立于EXPLAIN顯示

的表順序。這就意味著possible_keys里面所包含的索引可能在實際的使用中沒用到。如果這個字段的值是NU-L,就

表示沒有索引被用到。這種情況卜；就可以檢查WHERE了句中哪些字段那些字段適合增加索引以提高查詢的性能.

就這樣，創(chuàng)建一下索引，然后再用EXPLAIN檢查一下。詳細的查看章節(jié),14.2.2ALTERTABLESyntax"。想看表都有

什么索引，可以通過SHOWINDEXFROMtbl_name來看。

key

key字段顯示了MySQL實際上要用的索引。而沒有任何索引被用到的時候，這個字段的值就是NULL.想要讓

MySQL強行使用或者忽略在possible_keys字段中的索引列表，可以在咨詢語句中使用關鍵字FORCEINDEX,USE

INDEX,或IGNOREINDEX,如果是MylSAM和BDB類型表，可以使用ANALYZETABLE來幫助分析使用費用哪

個索引更好.如果是MylSAM類型表，運行命令myisamchk-analyze也是?樣的效果。詳細的可以查看章節(jié)”1452.1

ANALYZETABLESyntax"和"5.7.2TabicMaintenanceandCrashRecovery"

key_len

key_len字段顯示了MySQL使用索引的長度，當key字段的值為NULL時，索引的長度就是NULL..注就

key_len的值可以告訴你在聯(lián)合索引中MySQL會真正使用了哪些索引。

ref

ref字段顯示了哪些字段或者常量被用來和key配合從表中查詢記錄出來。

rows

rows字段顯示了MySQL認為在查詢中應該檢索的記錄數。

Extra

本字段顯示了查詢中MySQL的附加信息。以下是這個字段的幾個不同值的解釋：

Distinct

MySQL當找到當前記錄的匹配聯(lián)合結果的第一條記錄之后，就不再搜索其他記錄了。

Notexists

MySQL在查詢時做一個LEFTJOIN優(yōu)化時，當它在當前表中找到了和前一條記錄符合LEFTJOIN條件

后，就不再搜索更多的記錄了。卜面是一個這種類型的查詢例子：

SELECT*FROM11LEFTJOINt2ONH.id=t2.id

WHEREt2.idISNULL;

假使t2.id定義為NOTNULLO這種情況下,MySQL將會掃描表t1并且川tl.id的值在t2中查找記錄。當在t2

中找到一條匹配的記錄時，這就意味著t2.id肯定不會都是NULL,就不會再在t2中查找相同id值的其他記錄了。也

可以這么說，對于H中的每個記錄,MySQL只需要在t2中做一次查找，而不管在t2中實際有多少匹配的記錄。

rangecheckedforeachrecord(indexmap:#)

MySQL沒找到合適的可用的索引。取代的辦法是，時丁前一個表的每一個行連接，它會做一個檢驗以決定該更用哪

個索引(如果有的話)，并且使用這個索引來從表里取得記錄。這個過程不會很快，但總比沒有任何索引時做表連接來得

快.

Using

MySQL需要額外的做一遍從而以排好的順序取得記錄。排序程序根據連接的類型遍歷所有的記錄，并且將所有符合

WHERE條件的記錄的要排序的鍵和指向記錄的指針存儲起來。這些鍵匕經排完序了，對應的記錄也會按照排好的順序

取出來。詳情請看”7.2.9HowMySQLOptimizesORDERBY”。

Usingindex

字段的信息直接從索引樹中外信息取得，而不再去掃描實際的記錄。這種策略用于查詢時的字段是一個獨立索引的

一部分.

Usingtemporary

MySQL需要創(chuàng)建臨時表存儲結果以完成查詢。這種情況通常發(fā)生在互詢時包含了GROUPBY和ORDER3YT

句，它以不同的方式列出了各個字段。

Usingwhere

WHERE子句將用來限制哪些記錄匹配了下一個表或者發(fā)送給客戶端.除非你特別地想要取得或者檢杳表種的所有

記錄，否則的話當查詢的Extra字段值不是Usingwhere并且表連接類型是ALL或index時可能表示有問題，

如果你想要讓查詢盡可能的快，那么就應該注意Extra字段的值為Using和Usingtemporary的情況。

你可以通過EXPLAIN的結果中rows字段的值的乘枳大概地知道本次連接表現如何。它可以粗略地告訴我們

MySQL在查詢過程中會置詢多少條記錄。如果是使用系統(tǒng)變量maxjoin_size來取得筐詢結果，這個乘積還可以用來

確定會執(zhí)行哪些多表SELECT語句。詳情請看"7.5.2TuningServerParameters"。

卜面的例子展示了如何通過EXPLAIN提供的信息來較大程度地優(yōu)化多表聯(lián)合衽詢的性能。

假設有下面的SELECT語句，正打算用EXPLAIN來檢測：

EXPLAINSELECTtt.TicketNurrber,tt.Timeln,

tt.ProjectReference,tt.EstimatedShipDate,

tt.ActualShipDate,tt.ClientID,

tt.ServiceCodes,tt.RepetidvelD,

tt.CurrentProcess,tt.CurrentDPPerson,

tt.RecordVolume,tt.DPPrinted,et.COUNTRY,

et_l.COUNTRY,do.CUSTNAME

FROMtt,et,etASet_l,do

WHEREtt.SubmitTimeISNULL

ANDtt.ActualPC=etEMPLOYID

ANDtt.AssignedPC=et_l.EMPLOYID

ANDtt.ClientID=do.CUSTNMBR;

在這個例子中，先儂以下假設:

要比較的字段定義如F:

ColumnColumnType

Table

ttActualPCCHAR(10)

ttAssignedPCCHAR(10)

ttClientTDCHAR(10)

etEMPLOYIDCHAR(15)

doCUSTNWBRCHAR(15)

數據表的索引如下：

Index

Table

ttActualPC

ttAssignedPC

ttClientID

etEMPLOYID(primarykey)

doCUSTNMBR(primarykey)

tt.ActualPC的值是不均勻分布的.

在任何優(yōu)化措施未采取之前，經過EXPLAIN分析的結果顯示如下:

tabletypepossible_keyskeykey_lenrefrowsExtra

etALLPRIMARYNULLNULLNULL74

doALLPRIMARYNULLNULLNULL2135

et1ALLPRIMARYNULLNULLNULL74

ttALLAssignedPC,NULLNULLNULL3872

ClientID,

ActualPC

rangecheckedforeachrecord(keymap:35)

由于字段type的對于每個表值都是ALL,這個結果意味著MySQL對所有的表做一個迪卡爾枳;這就是說，每條記

錄的組合。這將需要花很長的時間，因為需要掃描每個表總記錄數乘枳的總和。在這情況下，它的積是74*2135-74-

3872=45.268,558,720條記錄。如果數據表更大的話，你可以想象一下需要多長的時間。

在這里有個問題是當字段定義一樣的時候,MySQL就可以在這些字段上更快的是川索引(對ISAM類型的表來說，

除非字段定義完全一樣，否則不會使用索引)。在這個前提下，VARCHAR和CHAR是一樣的除非它們定義的長度不一

致.由于tt.ActualPC定義為CHAR(10),et.EMPLOYID定義為CHAR(15),二者長度不一致。

為了解決這個問題，需要用ALTERTABLE來加大ActualPC的長度從10到15個字符：

mysql>ALTERTABLEttMODIFYActualPCVARCHAR(15)；

現在tt.ActualPC和et.EMPLOYID都是VARCIIAR(15)

了。再來執(zhí)行?次EXPLAIN語句看看結果：

tabletypepossible_keyskeykey_lenrefrowsExtra

ttALLAssignedPC,NULLNULLNULL3872Using

CllentID,where

ActualPC

doALLPRIMARYNULLNULLNULL2135

idiigeclieckedfuteacliiecuid(key111dp.1)

et_lALLPRIMARYNULLNULLNULL74

rangecheckedforeachrecord(keymap:1)

eteq_refPRIMARYPRIMARY15tt.ActualPC1

這還不夠，它還可以做的更好：現在rows值乘積已經少了74倍.這次查詢需要用2秒鐘。

第二個改變是消除在比較tt.AssignedPC=et1.EMPLOYID和It.ClientlD=do.CUSTNMBR中字段的改

度不一致問題：

mysql>ALTERTABLEttMODIFYAssignedPCVARCHAR(15),

->MODIFYClientIDVARCHAR(15)；

現在EXPLAIN的結果如下：

tabletypepossible_keyskeykey_lenrefrowsExtra

etALLPRIMARYNULLNULLNULL74

ttrefAssignedPC,ActualPC15et.EMPLOYID52Using

ClientID,where

ActualPC

et_ieq_refPRIMARYPRIMARY15tt.AssignedPC1

doeq_refPRIMARYPRIMARY15tt.ClientID1

這看起來已經是能做的最好的結果J'。

遺留下來的問題是，MySQL默認地認為字段tt.ActualPC的值是均勻分布的，然而表tt并非如此.幸好，我

們可以很方便的讓MySQL分析索引的分布：

mysql>ANALYZETABLEtt；

到此為止，表連接已經優(yōu)化的很完美了，EXPLAIN的結果如下:

tabletypepossible_keyskeykeylenrefrowsExtra

ttALLAssignedPCNUL_NULLNULL3872Using

ClientID,where

ActualPC

eteq_refPRIMARYPRIMARY15tt.ActualPC1

et_leq_refPRIMARYPRIMARY15tt.AssignedPC1

doeq_refPRIMARYPRIMARYlbtt.LhentlD1

請注意，EXPLAIN結果中的rows字段的值也是MySQL的連接優(yōu)化程序大致猜測的，請檢查這個值跟真實誼是否

基本一致。如果不是，可以通過在SELECT語句中使用STRAIGHT_JOIN來取得更好的性能，同時可以試著在

FROM

分句中用不同的次序列出各個表。

MySQL數據庫優(yōu)化(三)

作者：葉金榮，出處：n?專家網，責任簿我：李書琴，

2008-06-1110:05

這個章節(jié)講述了優(yōu)化程序如何處理WHERE子句。例子中使用了SELECT語句，但是在DELETE和UPDATE

語句中對WHERE了句的優(yōu)化是?樣的。注意，關于MySQL優(yōu)化的工作還在維續(xù)，因此本章節(jié)還沒結束。MySQL做

了很多優(yōu)化工作，而不僅僅是文檔中提到的這些。

1.估算查詢性能

在大多數情況下，可以通過統(tǒng)計磁盤搜索次數來估算查詢的性能.時小表來說，通常情況下只需要搜索一次磁盤就

能找到對應的記錄(因為索引可能已經緩存起來J')。對大表來說，大致可以這么估算，它使用B樹做索引，想要找到一條

記錄大概需要搜索的次數為:log(row_count)/log(index_block_length/3,2/(indexjength+data_pointer_length))+

1。

在MySQL中，一個索引塊通常是1024bytes,數據指針通常是4bytes.對于一個有500,000條記錄、索引長度為

3bytes(mediuminteger)的表來說根據上面的公指計算得到需要做log(500,000)/log(1024/3*2/(3-?-4))+1=4次搜索。

這個表的索引大概需要500,000*7*3/2=5.2MB的存儲空間(假定典型的索引緩沖區(qū)的2/3),因此應該會有更多的

索引在內存中，并且可能只需要1到2次調用就能找到對應的記錄。

對于寫來說，大概需要4次(甚至更多)搜索才能找到新的索引位置，更新記錄時通常需要2次搜索。

請注意，前面的討論中并沒有提到應用程序的性能會因為logN的值遴大而卜降。只要所有的東西都能由操作系統(tǒng)

或者SQL服務器緩存起來，那么性能只會因為數據表越大而稍微下降。當數據越來越大之后，就不能全部放到緩存中去

了，就會越來越慢了，除非應用程序是被磁盤搜索約束的(它跟隨著的log7值增加而增加)。為了避免這種情況，uj"以在

數據量增大以后也隨著增大索引線存容量。對MylSAM類型表來說，索引緩存容量是由系統(tǒng)變量key_buffer_size控

制的。

2.SELEC.杳詢的速度

通常情況下，想要讓一個比較慢.SELEC....WHER.查詢變得更快的第一件事就是，先檢查看看是否可以煙加索引。

所有對不同表的訪問都通常使用索用?？梢允?EXPLAI.語句來判.SELEC.使用了哪些索引。洋情請看

"7.4..Ho.MySQ.Use.Indexes"-fli"7.2..EXPLAI.Synta.(Ge.Informatio.Abou.SELECT)”。

以下是幾個常用的提高MylSAM表查詢速度的忠告：

想要讓MySQL將查詢優(yōu)化的速度更快些，可以在數據表已經加載完全部數據后執(zhí)行行ANALYZETABLE或運行

myisamchk-analyze命令.它更新了每個索引部分的值，這個值意味著相同記錄的平均值(對于唯一索引來說，這個值

則一直都是1)。MySQL就會在當你使用基于一個非恒量表達式的兩表連接時，根據這個值來決定使用哪個索引。想要

查看結果，可以在分析完數據表后運行SHOWINDEXFROMtbl_name查看Cardinality字段的值。myisamch<

-description-verbose顯示了索引的分布信息。

想要根據一個索引來排序數據，可以運行myisamchk-sort-index-sort-records=1(如果想要在索引1上做排

序)“這對于布??個唯?索引并且想根據這個索引的順序依次讀取記錄的話來說是?個提高查詢速度的好辦法。不過要注

忌的是，第一次在一個大表上做排序的話將會耗菸很長時間。

3.MySQL如何優(yōu).WHER.了句

這個章節(jié)講述了優(yōu)化程序如何處理WHERE子句。例子中使用了SELECT語句，但是在DELETE和UPDATE

語句中對WHERE子句的優(yōu)化是一樣的。注意，關于MySQL優(yōu)化的工作還在繼續(xù)，因此本章節(jié)還沒結束。MySQL做

了很多優(yōu)化工作，而不僅僅是文檔中提到的這些。

MySQL的一些優(yōu)化做法如下：

去除不必要的括號：

((aANDb)ANDcOR({(aANDb)AND(cANDd))))

->(aANDbANDc)OR(aANDbANDcANDd)

展開常量：

(a

->b>5ANDb=cANDa=5

去除常量條件(任展開常世時靠要)：

(B>=5ANDB=5)OR(B=6AND5=5)OR(B=7AND5=6)

->B=5ORB=6

常或表達示在索引中只計算一次

在單獨一個表上做COUNTC)而不使用WHERE時，對于MylSAM和HEAP表就會直接從表信息中檢索結

果。在單獨一個表上做任何表NOTNULL達式置詢時也是這樣做。

預先探測無效的常量表達式。MySQL會快速探測一些不可能的SELECT語句并且不返回任何記錄。

當沒用GROUPBY或分組函數時,HAVING和WHERE合并(COUNT。,MIN()等也是如此)。

為表連接中的每個表構造一個簡潔的WHERE語句，以得到更快的WHERE計算值并且盡快跳過記錄。

查詢中所有的常量表都會比其他表更早讀取。一個常量表符合以下幾個條件：

空表或者只有一條記錄。

與布?個UNIQUE索引、或?個PRIMARYKEY的WHERE子句?起使用的表，這里所TT的索引部分和常數表

達式做比較并且索引部分被定義為NOTNULLo

以下的幾個表都會被當成常量表：

SELECT,FROMtWHEREprimary_key=1;

SELECT*FROM11,t2

WHEREt1.primary_key=1ANDt2.primary_key?t1.id;

MySQL會進各種可能找到表連接最好的連接方法。如果在ORDERBY和GROUPBY了句中的所有字段都來

自同一個表的話，那么在連接時這個表就會優(yōu)先處理。

如果有ORDERB丫子句和一個不同的GROUPBY于句，或者如果ORDERBY或GROUPBY中的字段都來

白其他的表而非連接順序中的第一個表的話，就會創(chuàng)建一個臨時表了。

如果使用SQL_SMALL_RESULT,MySQL就會使用內存臨時表了。

所行的表索引都會查詢，最好的情況就是所TT的索引都會被用到，除非優(yōu)化程序認為全表掃描的效率更高.同時，數

據表掃描是基于判斷最好的索引:范圍超過數據表的30%,現在，優(yōu)化程序復雜多兒它基于對一些附加因素的估計，例

如表大小，記錄總數，I/O塊大小，因此就不能根據?個固定的百分比來決定是選擇使用索引還是直接掃描數據表。

在某些情況下,MySQL可以直接從索引中取得記錄而無需資詢數據文件。如果所有在索引中使用的字段都是數字類

型的話，只而要用素引樹就能完成r［向。

每條記錄輸出之前，那些沒仃匹配HAVING子句的就會被跳過.

以卜幾個查詢速度非常快：

SELECTCOUNTC)FROMtb_name;

SELECTMIN(key_part1).MAX(key_part1)FROMtbl_name;

SELECTMAX(keypart2)FROMtblname

WHEREkey_part1=constant;

SELECT...FROMtbl_name

ORDERBYkey_part1,key_part2,...LIMIT10;

SELECT...FROMtbl_name

ORDERBYkey_pamDESC,key_part2DESC,...LIMIT10;

以卜幾個查詢都是使用索引樹，假使那些索引字段都是數字型：

SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_partl=val;

SELECTCOUNT(-)FROMtb_name

WHEREkey_partl=vallANDkey_part2=val2：

SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;

以下幾個杳詢使用索引來取得經過順序排序后的記錄而無需經過獨立的排序步驟：

SELECT...FROMtbLname

ORDERBYkey_part1,key_part2,...;

SELECT...FROMtblname

ORDERBYkey_part1DESC,key_part2DESC,...;

4.MySQ.如何優(yōu)。子句

IndexMerge方法用,使用,ef,ref_or_null,或range掃描取得的記錄合并起來放到一起作為結果。這種方法在表

條件是或條件ref,ref_or_null,或range,并且這些條件可以用不同的鍵時采用。

?join1?類型的優(yōu)化是從MySQL5.0.0開始才有的，代表者在索引的性能上有若標志性的改進，因為使用老規(guī)則的話,

數據庫最多只能對每個引用表使用一個索引。

在EXPLAIN的結果中，這種方法在type字段中表現為index_merge.這種情況卜，key字段包含了所有例用的

索引列表，并且key_len字段包含了使用的索引的最長索引部分列表。

例如：

SELECT*FROMtbl_nameWHEREkey_part1.10ORkey_parl2-20;

SELECT*FROMtbl_name

WHERE(key_par11=10ORkey_part2=20)ANDnon_key_part=30;

SELECT*FROMt1,t2

WHERE(H.keyiIN(1,2)0Rtl.key2LIKE'value%')

ANDt2.key1=t1.some_col;

SELECT*FROMt1,t2

WHEREI1.key1-1

AND(t2.key1=t1.some_colORt2.key2=t1.some_co!2);

5.MySQ.如何優(yōu).I.NULL

MySQL在col_nameISNULL時做和col_name=constant_value?樣的優(yōu)化"例如，MySQL使用索引或

者范圍來根據ISNULL搜索NULL。

SELECT,FROMWHEREkey_colISNULL;

SELECT*FROMtblnameWHEREkey_col<=>NULL;

SELECT*FROMtbl_name

WHEREkeycol=const1ORkeycol=const2ORkeycolISNULL;

如果一個WHERE子句包括了一個col_nameISNULL條件，并且這個字段聲明為NOTNULL,那么這個表達

式就會被優(yōu)化。當字段可能無論如何都會產生NULL值時，就不會再做優(yōu)化了例如，當它來自一個LEFTJOIN中右邊

的一個表時。

MySQL4.1.1或更高會對連接col_name=exprANDcoLnameISNULL做額外的優(yōu)化，常見的就是子查詢。

EXPLAIN當優(yōu)化起作用時會顯示ref_or_nulL

優(yōu)化程序會為任何索引部分處理ISNULL,

以下幾個例子中都做優(yōu)化了，假使字段a和表t2中b有索引了:

SELECT*FROMt1WHEREt1.a=exprORt1.aISNULL;

SELECT,FROMI1,t2WHEFEt1.a-t2.aORt2.aISNULL;

SELECT*FROMt1,t2

WHERE(I1.a-t2.aORt2.aISNULL)ANDt2.b-t1.b;

SELECT*FROMt1,t2

WHEREt1.a=t2.aAND(t2.b=d.bORt2.bISNULL)；

SELECT*FROMH,tZ

WHERE(|1.a=t2.aANDt2.aISNULLAND...)

OR(t1.a-t2.aANDt2.aISNLLLAND

ref_or_null首先讀取引用修然后獨立掃描鍵值為NULL的記錄。

請注意，優(yōu)化程序只會處理一個ISNULL級別。下面的行詢中,MySQL只會使用健來查詢表達式(t1.a=12.a

ANDt2.aISNULL)而無法使在b上使用索引部分:

SELECT*FROMt1,t2

WHERE(t1.a=t2.aANDt2.a