MySQL數(shù)據(jù)庫優(yōu)化

1、-作者xxxx-日期xxxxMySQL數(shù)據(jù)庫優(yōu)化【精品文檔】MySQL數(shù)據(jù)庫優(yōu)化（一）作者: 葉金榮, 出處:IT專家網(wǎng),責任編輯: 李書琴, 2008-06-06 09:30數(shù)據(jù)庫優(yōu)化是一項很復(fù)雜的工作，因為這最終需要對系統(tǒng)優(yōu)化的很好理解才行。盡管對系統(tǒng)或應(yīng)用系統(tǒng)的了解不多的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。數(shù)據(jù)庫優(yōu)化是一項很復(fù)雜的工作，因為這最終需要對系統(tǒng)優(yōu)化的很好理解才行。盡管對系統(tǒng)或應(yīng)用系統(tǒng)的了解不多的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。本章主要講解了幾種優(yōu)化MySQL的方法，并且給出了例子。記著，總有各

2、種辦法能讓系統(tǒng)運行的更快，當然了，這需要更多的努力。1 優(yōu)化概述讓系統(tǒng)運行得快得最重要因素是數(shù)據(jù)庫基本的設(shè)計。并且還必須清楚您的系統(tǒng)要用來做什么，以及存在的瓶頸。最常見的系統(tǒng)瓶頸有以下幾種：磁盤搜索。它慢慢地在磁盤中搜索數(shù)據(jù)塊。對現(xiàn)代磁盤來說，平時的搜索時間基本上小于10毫秒，因此理論上每秒鐘可以做100次磁盤搜索。這個時間對于全新的新磁盤來說提高的不多，并且對于只有一個表的情況也是如此。加快搜索時間的方法是將數(shù)據(jù)分開存放到多個磁盤中。磁盤讀/寫。當磁盤在正確的位置上時，就需要讀取數(shù)據(jù)。對現(xiàn)代磁盤來說，磁盤吞吐量至少是10-20MB/秒。這比磁盤搜索的優(yōu)化更容易，因為可以從多個媒介中并行地讀取

3、數(shù)據(jù)。CPU周期。數(shù)據(jù)存儲在主內(nèi)存中(或者它已經(jīng)在主內(nèi)存中了)，這就需要處理這些數(shù)據(jù)以得到想要的結(jié)果。存在多個?硐啾饒詿嬡萘坷此蹈竅拗頻囊蛩亍2還孕砝此擔俁韌2皇俏侍狻?內(nèi)存帶寬。當CPU要將更多的數(shù)據(jù)存放在CPU緩存中時，主內(nèi)存的帶寬就是瓶頸了。在大多數(shù)系統(tǒng)中，這不是常見的瓶頸，不過也是要注意的一個因素。1.1 MySQL 設(shè)計的局限性當使用MyISAM存儲引擎時，MySQL會使用一個快速數(shù)據(jù)表鎖以允許同時多個讀取和一個寫入。這種存儲引擎的最大問題是發(fā)生在一個單一的表上同時做穩(wěn)定的更新操作及慢速查詢。如果這種情況在某個表中存在，可以使用另一種表類型。詳情請看15 MySQL Storage

4、Engines and Table Types。MySQL可以同時在事務(wù)及非事務(wù)表下工作。為了能夠平滑的使用非事務(wù)表(發(fā)生錯誤時不能回滾)，有以下幾條規(guī)則： 所有的字段都有默認值 如果字段中插入了一個錯誤的值，比如在數(shù)字類型字段中插入過大數(shù)值，那么MySQL會將該字段值置為最可能的值而不是給出一個錯誤。數(shù)字類型的值是0，最小或者最大的可能值。字符串類型，不是空字符串就是字段所能存儲的最大長度。 所有的計算表達式都會返回一個值而報告條件錯誤，例如 1/0 返回 NULL。這些規(guī)則隱含的意思是，不能使用MySQL來檢查字段內(nèi)容。相反地，必須在存儲到數(shù)據(jù)庫前在應(yīng)用程序中來檢查。詳情請看1.8.6 H

5、ow MySQL Deals with Constraints 和 14.1.4 INSERT Syntax。1.2 應(yīng)用設(shè)計的可移植性由于各種不同的數(shù)據(jù)庫實現(xiàn)了各自的SQL標準，這就需要我們盡量使用可移植的SQL應(yīng)用。查詢和插入操作很容易就能做到可移植，不過由于更多的約束條件的要求就越發(fā)困難。想要讓一個應(yīng)用在各種數(shù)據(jù)庫系統(tǒng)上快速運行，就變得更困難了。為了能讓一個復(fù)雜的應(yīng)用做到可移植，就要先看這個應(yīng)用運行于哪種數(shù)據(jù)庫系統(tǒng)之上，然后看這些數(shù)據(jù)庫系統(tǒng)都支持哪些特性。每個數(shù)據(jù)庫系統(tǒng)都有某些不足。也就是說，由于設(shè)計上的一些妥協(xié)，導(dǎo)致了性能上的差異?？梢杂肕ySQL的 crash-me 程序來看選定的數(shù)

6、據(jù)庫服務(wù)器上可以使用的函數(shù)，類型，限制等。crash-me 不會檢查各種可能存在的特性，不過這仍然是合乎情理的理解，大約做了450次測試。一個 crash-me 的信息類型的例子就是，它會告訴您如果想使用Informix 或 DB2的話，就不能使字段名長度超過18個字符。crash-me 程序和MySQL基準使每個準數(shù)據(jù)庫都實現(xiàn)了的?？梢酝ㄟ^閱讀這些基準程序是怎么寫的，自己就大概有怎樣做才能讓程序獨立于各種數(shù)據(jù)庫這方面的想法了。這些程序可以在MySQL源代碼的 sql-bench 目錄下找到。他們大部分都是用Perl寫的，并且使用DBI接口。由于它提供了獨立于數(shù)據(jù)庫的各種訪問方式，因此用DBI

7、來解決各種移植性的問題。想要看到 crash-me 的結(jié)果，可以訪問： 訪問 可以看到基準的結(jié)果。如果您想努力做到獨立于數(shù)據(jù)庫，這就需要對各種SQL服務(wù)器的瓶頸都有一些很好的想法。例如，MySQL對于 MyISAM 類型的表在檢索以及更新記錄時非常快，但是在有并發(fā)的慢速讀取及寫入記錄時卻有一定的問題。作為Oracle來說，它在訪問剛剛被更新的記錄時有很大的問題(直到結(jié)果被刷新到磁盤中)。事務(wù)數(shù)據(jù)庫一般地在從日志表中生成摘要表這方面的表現(xiàn)不怎么好，因為在這種情況下，行記錄鎖幾乎沒用。為了能讓應(yīng)用程序真正的做到獨立于數(shù)據(jù)庫，就必須把操作數(shù)據(jù)的接口定義的簡單且可擴展。由于C+在很多系統(tǒng)上都可以使用，

8、因此使用C+作為數(shù)據(jù)庫的基類結(jié)果很合適。如果使用了某些數(shù)據(jù)庫獨有的特定功能(比如 REPLACE 語句就只在MySQL中獨有)，這就需要通過編寫替代方法來在其他數(shù)據(jù)庫中實現(xiàn)這個功能。盡管這些替代方法可能會比較慢，但是它能讓其他數(shù)據(jù)庫實現(xiàn)同樣的功能。在MySQL中，可以在查詢語句中使用 /*! */ 語法來增加MySQL特有的關(guān)鍵字。然而在很多其他數(shù)據(jù)庫中，/*/ 卻被當成了注釋(并且被忽略)。如果有時候更高的性能比數(shù)據(jù)結(jié)果的精確更重要，就像在一些Web應(yīng)用中那樣，這可以使用一個應(yīng)用層來緩存結(jié)果，這可能會有更高的性能。通過讓舊數(shù)據(jù)在一定時間后過期，來合理的更新緩存。這是處理負載高峰期時的一種方法

9、，這種情況下，可以通過加大緩存容量和過期時間直到負載趨于正常。這種情況下，建表信息中就要包含了初始化緩存的容量以及正常刷新數(shù)據(jù)表的頻率。一個實現(xiàn)應(yīng)用層緩存的可選方案是使用MySQL的查詢緩存(query cache)。啟用查詢緩存后，數(shù)據(jù)庫就會根據(jù)一些詳情來決定哪些結(jié)果可以被重用。它大大簡化了應(yīng)用程序，詳情請看5.11 The MySQL Query Cache。1.3 我們都用MySQL來做什么本章描述了一個MySQL的早期應(yīng)用。在MySQL最開始的開發(fā)過程中，MySQL本來是要準備給大客戶用的，他們是瑞典的2個最大的零售商，他們用于貨物存儲數(shù)據(jù)管理。我們每周從所有的商店中得到交易利潤累計結(jié)

10、果，以此給商店的老板提供有用的信息，幫助他們分析如果更好的打廣告以影響他們的客戶。數(shù)據(jù)量相當?shù)拇?每個月的交易累計結(jié)果大概有7百萬)，而且還需要顯示4-10年間的數(shù)據(jù)。我們每周都得到客戶的需求，他們要求能瞬間地得到數(shù)據(jù)的最新報表。我們把每個月的全部信息存儲在一個壓縮的交易表中以解決這個問題。我們有一些簡單的宏指令集，它們能根據(jù)不同的標準從存儲的交易表中根據(jù)字段分組(產(chǎn)品組、客戶id、商店等等)取得結(jié)果。我們用一個小Perl腳本動態(tài)的生成Web頁面形式的報表。這個腳本解析Web頁面，執(zhí)行SQL語句，并且插入結(jié)果。我們還可以用PHP或者mod_perl來做這個工作，不過當時還沒有這2個工具。為了得

11、到圖形數(shù)據(jù)，我們還寫了一個簡單的C語言工具，用于執(zhí)行SQL查詢并且將結(jié)果做成GIF圖片。這個工具同樣是Perl腳本解析Web頁面后動態(tài)執(zhí)行的。很多情況下，只要拷貝現(xiàn)有的腳本簡單的修改里面的SQL查詢語句就能產(chǎn)生新的報表了。有時候，就需要在現(xiàn)存的累計表中增加更多的字段或者新建一個。這個操作十分簡單，因為我們在磁盤上存儲有所有的交易表(總共大概有50G的交易表以及20G的其他客戶資料)。我們還允許客戶通過ODBC直接訪問累計表，這樣的話，那些高級用戶就可以自己利用這些數(shù)據(jù)做試驗了。這個系統(tǒng)工作的很好，并且在適度的Sun Ultra SPARC工作站(2x200MHz)上處理數(shù)據(jù)沒有任何問題。最終這

12、個系統(tǒng)移植到了Linux上。1.4 MySQL 基準套件本章本來要包括MySQL基準套件(以及 crash-me)的技術(shù)描述的，但是至今還未寫?，F(xiàn)在，您可以通過查看MySQL發(fā)布源代碼 sql-bench 目錄下的代碼以及結(jié)果有一個更好的想法?；鶞侍准褪窍敫嬖V用戶執(zhí)行什么樣的SQL查詢表現(xiàn)的更好或者更差。請注意，這個基準是單線程的，因此它度量了操作執(zhí)行的最少時間。我們未來打算增加多線程測試的基準套件。想要使用基準套件，必備以下幾個條件：基準套件在MySQL的發(fā)布源代碼中就有?？梢匀?下載發(fā)布版或者使用現(xiàn)有開發(fā)代碼樹(詳情請看2.3.3 Installing from the Developm

13、ent Source Tree)?；鶞誓_本是用Perl寫的，它用Perl的DBI模塊來連接數(shù)據(jù)庫，因此必須安裝DBI模塊。并且還需要每個要做測試的服務(wù)器上都有特定的BDB驅(qū)動程序。例如，為了測試MySQL、PostgreSQL和DB2，就必須安裝 DBD:mysql, DBD:Pg 及 DBD:DB2 模塊。詳情請看2.7 Perl Installation Note。取得MySQL的分發(fā)源代碼后，就能在 sql-bench 目錄下看到基準套件。想要運行這些基準測試，請先搭建好服務(wù)，然后進入 sql-bench 目錄，執(zhí)行 run-all-tests 腳本：shell cd sql-bench

14、shell perl run-all-tests -server=server_nameserver_name 可以是任何一個可用的服務(wù)。想要列出所有的可用選項和支持的服務(wù)，只要調(diào)用以下命令：shell perl run-all-tests -helpcrash-me 腳本也是放在 sql-bench 目錄下。crash-me 通過執(zhí)行真正的查詢以試圖判斷數(shù)據(jù)庫都支持什么特性、性能表現(xiàn)以及限制。例如，它可以判斷： 都支持什么字段類型 支持多少索引 支持什么樣的函數(shù) 能支持多大的查詢 VARCHAR 字段類型能支持多大可以從 上找到各種不同數(shù)據(jù)庫 crash-me 的結(jié)果。更多的信息請訪問 。1

15、.5 使用您自己的基準請確定對您的數(shù)據(jù)庫或者應(yīng)用程序做基準測試，以發(fā)現(xiàn)它們的瓶頸所在。解決這個瓶頸(或者使用一個假的模塊來代替)之后，就能很容易地找到下一個瓶頸了。即使應(yīng)用程序當前總體的表現(xiàn)可以接受，不過還是至少要做好找到每個瓶頸的計劃，說不定某天您就希望應(yīng)用程序能有更好的性能。從MySQL的基準套件中就能找到一個便攜可移植的基準測試程序了。詳情請看7.1.4 The MySQL Benchmark Suite。您可以從基準套件中的任何一個程序，做適當?shù)男薷囊赃m合您的需要。通過整個方式，您就可以有各種不同的辦法來解決問題，知道哪個程序才是最快的。.net 上找到。當系統(tǒng)負載十分繁重的時候，通常

16、就會發(fā)生問題。我們就有很多客戶聯(lián)系我們說他們有一個(測試過的)生產(chǎn)系統(tǒng)也遭遇了負載問題。在很多情況下，性能問題歸結(jié)于數(shù)據(jù)庫的基本設(shè)計(例如，在高負載下掃描數(shù)據(jù)表的表現(xiàn)不好)、操作系統(tǒng)、或者程序庫等因素。很多時候，這些問題在還沒有正式用于生產(chǎn)前相對更容易解決。為了避免發(fā)生這樣的問題，最好讓您的應(yīng)用程序在可能的最差的負載下做基準測試!可以使用Super Smack，在 可以找到。從它名字的意思就能想到，只要您愿意，它就能讓您的系統(tǒng)死掉，因此確認只在開發(fā)系統(tǒng)上做測試。2 優(yōu)化 SELECT 語句及其他查詢首先，影響所有語句的一個因素是：您的權(quán)限設(shè)置越復(fù)雜，那么開銷就越大。使用比較簡單的 GRANT

17、語句能讓MySQL減少在客戶端執(zhí)行語句時權(quán)限檢查的開銷。例如，如果沒有設(shè)定任何表級或者字段級的權(quán)限，那么服務(wù)器就無需檢查 tables_priv 和 columns_priv 表的記錄了。同樣地，如果沒有對帳戶設(shè)定任何資源限制的話，那么服務(wù)器也就無需做資源使用統(tǒng)計了。如果有大量查詢的話，花點時間來規(guī)劃簡單的授權(quán)機制以減少服務(wù)器權(quán)限檢查的開銷是值得的。如果問題處在一些MySQL特定的表達式或者函數(shù)上，則可以通過 mysql 客戶端程序使用 BENCHMARK() 函數(shù)做一個定時測試。它的語法是：BENCHMARK(loop_count,expression)。例如：mysql SELECT BE

18、NCHMARK(1000000,1+1);+-+| BENCHMARK(1000000,1+1) |+-+| 0 |+-+1 row in set (0.32 sec)上述結(jié)果是在Pentium II 400MHz的系統(tǒng)上執(zhí)行得到的。它告訴我們：MySQL在這個系統(tǒng)上可以在0.32秒內(nèi)執(zhí)行 1,000,000 次簡單的加法運算。所有的MySQL函數(shù)都應(yīng)該被最優(yōu)化，不過仍然有些函數(shù)例外。BENCHMARK() 是一個用于檢查查詢語句中是否存在問題的非常好的工具。MySQL數(shù)據(jù)庫優(yōu)化（二）作者: 葉金榮, 出處:IT專家網(wǎng),責任編輯: 李書琴, 2008-06-11 09:55EXPLAIN 語句

19、可以被當作 DESCRIBE 的同義詞來用，也可以用來獲取一個MySQL要執(zhí)行的 SELECT 語句的相關(guān)信息。EXPLAIN tbl_name 語法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一樣。MySQL數(shù)據(jù)庫優(yōu)化（一）1. EXPLAIN 語法(得到SELECT 的相關(guān)信息)EXPLAINtbl_name或者:EXPLAINSELECTselect_optionstableIN 語句可以被當作 DESCRIBE 的同義詞來用，也可以用來獲取一個MySQL要執(zhí)行的 SELECT 語句的相關(guān)信息。EXPLAIN tbl_name 語法和

20、 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一樣。當在一個 SELECT 語句前使用關(guān)鍵字 EXPLAIN 時，MYSQL會解釋了即將如何運行該 SELECT 語句，它顯示了表如何連接、連接的順序等信息。本章節(jié)主要講述了第二種 EXPLAIN 用法。在 EXPLAIN 的幫助下，您就知道什么時候該給表添加索引，以使用索引來查找記錄從而讓 SELECT 運行更快。如果由于不恰當使用索引而引起一些問題的話，可以運行 ANALYZE TABLE 來更新該表的統(tǒng)計信息，例如鍵的基數(shù)，它能幫您在優(yōu)化方面做出更好的選擇。您還可以查看優(yōu)化程序是否以最佳的順

21、序來連接數(shù)據(jù)表。為了讓優(yōu)化程序按照 SELECT 語句中的表名的順序做連接，可以在查詢的開始使用 SELECT STRAIGHT_JOIN 而不只是 SELECT。EXPLAIN 返回了一行記錄，它包括了 SELECT 語句中用到的各個表的信息。這些表在結(jié)果中按照MySQL即將執(zhí)行的查詢中讀取的順序列出來。MySQL用一次掃描多次連接(single-sweep, multi-join) 的方法來解決連接。這意味著MySQL從第一個表中讀取一條記錄，然后在第二個表中查找到對應(yīng)的記錄，然后在第三個表中查找，依次類推。當所有的表都掃描完了，它輸出選擇的字段并且回溯所有的表，直到找不到為止，因為有的表

22、中可能有多條匹配的記錄下一條記錄將從該表讀取，再從下一個表開始繼續(xù)處理。在MySQL version 4.1中，EXPLAIN 輸出的結(jié)果格式改變了，使得它更適合例如 UNION 語句、子查詢以及派生表的結(jié)構(gòu)。更令人注意的是，它新增了2個字段： id 和 select_type。當你使用早于MySQL 4.1的版本就看不到這些字段了。EXPLAIN 結(jié)果的每行記錄顯示了每個表的相關(guān)信息，每行記錄都包含以下幾個字段：id本次 SELECT 的標識符。在查詢中每個 SELECT 都有一個順序的數(shù)值。select_typeSELECT 的類型，可能會有以下幾種： SIMPLE簡單的 SELECT (

23、沒有使用 UNION 或子查詢) PRIMARY最外層的 SELECT。 UNION第二層，在SELECT 之后使用了 UNION 。 DEPENDENT UNIONUNION 語句中的第二個 SELECT，依賴于外部子查詢 SUBQUERY子查詢中的第一個 SELECT DEPENDENT SUBQUERY子查詢中的第一個 SUBQUERY 依賴于外部的子查詢 DERIVED派生表 SELECT(FROM 子句中的子查詢)table記錄查詢引用的表。type表連接類型。以下列出了各種不同類型的表連接，依次是從最好的到最差的： system表只有一行記錄(等于系統(tǒng)表)。這是 const 表連接

24、類型的一個特例。 const表中最多只有一行匹配的記錄，它在查詢一開始的時候就會被讀取出來。由于只有一行記錄，在余下的優(yōu)化程序里該行記錄的字段值可以被當作是一個恒定值。const 表查詢起來非?？?，因為只要讀取一次!const 用于在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比較的情形。下面的幾個查詢中，tbl_name 就是 const 表了：SELECT*FROMtbl_nameWHEREprimary_key=1; SELECT*FROMtbl_name WHEREprimary_key_part1=1ANDprimary_key_part2=2; eq_ref從該表中

25、會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯(lián)合。與 const 類型不同的是，這是最好的連接類型。它用在索引所有部分都用于做連接并且這個索引是一個 PRIMARY KEY 或 UNIQUE 類型。eq_ref 可以用于在進行=做比較時檢索字段。比較的值可以是固定值或者是表達式，表達式中可以使用表里的字段，它們在讀表之前已經(jīng)準備好了。以下的幾個例子中，MySQL使用了 eq_ref 連接來處理 ref_table：SELECT*FROMref_table,other_table WHEREref_table.key_column=other_table.column; SELECT*

26、FROMref_table,other_table WHEREref_table.key_column_part1=other_table.column ANDref_table.key_column_part2=1; ref該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯(lián)合。ref 用于連接程序使用鍵的最左前綴或者是該鍵不是 PRIMARY KEY 或 UNIQUE 索引(換句話說，就是連接程序無法根據(jù)鍵值只取得一條記錄)的情況。當根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時，這就是一個不錯的連接類型。ref 還可以用于檢索字段使用 = 操作符來比較的時候。以下的幾個例子中，My

27、SQL將使用 ref 來處理 ref_table：SELECT*FROMref_tableWHEREkey_column=expr; SELECT*FROMref_table,other_table WHEREref_table.key_column=other_table.column; SELECT*FROMref_table,other_table WHEREref_table.key_column_part1=other_table.column ANDref_table.key_column_part2=1; ref_or_null這種連接類型類似 ref，不同的是MySQL會在檢索

28、的時候額外的搜索包含 NULL 值的記錄。這種連接類型的優(yōu)化是從MySQL 4.1.1開始的，它經(jīng)常用于子查詢。在以下的例子中，MySQL使用 ref_or_null 類型來處理 ref_table：SELECT*FROMref_table WHEREkey_column=exprORkey_columnISNULL; index_merge這種連接類型意味著使用了 Index Merge 優(yōu)化方法。這種情況下，key字段包括了所有使用的索引，key_len 包括了使用的鍵的最長部分。詳情請看7.2.5 How MySQL Optimizes OR Clauses。 unique_subque

29、ry這種類型用例如一下形式的 IN 子查詢來替換 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery 只是用來完全替換子查詢的索引查找函數(shù)效率更高了。 index_subquery這種連接類型類似 unique_subquery。它用子查詢來代替 IN，不過它用于在子查詢中沒有唯一索引的情況下，例如以下形式：value IN (SELECT key_column FROM single_table WHERE some_expr) range只有在給定范圍的記錄才會被取出來，利用索

30、引來取得一條記錄。key 字段表示使用了哪個索引。key_len 字段包括了使用的鍵的最長部分。這種類型時 ref 字段值是 NULL。range 用于將某個字段和一個定植用以下任何操作符比較時 =, , , =, , =, IS NULL, , BETWEEN, 或 IN：SELECT*FROMtbl_name WHEREkey_column=10; SELECT*FROMtbl_name WHEREkey_columnBETWEEN10and20; SELECT*FROMtbl_name WHEREkey_columnIN(10,20,30); SELECT*FROMtbl_name WH

31、EREkey_part1=10ANDkey_part2IN(10,20,30); index連接類型跟 ALL 一樣，不同的是它只掃描索引樹。它通常會比 ALL 快點，因為索引文件通常比數(shù)據(jù)文件小。MySQL在查詢的字段知識單獨的索引的一部分的情況下使用這種連接類型。 ALL將對該表做全部掃描以和從前一個表中取得的記錄作聯(lián)合。這時候如果第一個表沒有被標識為 const 的話就不大好了，在其他情況下通常是非常糟糕的。正常地，可以通過增加索引使得能從表中更快的取得記錄以避免 ALL。possible_keyspossible_keys 字段是指MySQL在搜索表記錄時可能使用哪個索引。注意，這個字

32、段完全獨立于 EXPLAIN 顯示的表順序。這就意味著 possible_keys 里面所包含的索引可能在實際的使用中沒用到。如果這個字段的值是 NULL，就表示沒有索引被用到。這種情況下，就可以檢查 WHERE 子句中哪些字段那些字段適合增加索引以提高查詢的性能。就這樣，創(chuàng)建一下索引，然后再用 EXPLAIN 檢查一下。詳細的查看章節(jié)14.2.2 ALTER TABLE Syntax。想看表都有什么索引，可以通過 SHOW INDEX FROM tbl_name 來看。keykey 字段顯示了MySQL實際上要用的索引。當沒有任何索引被用到的時候，這個字段的值就是 NULL。想要讓MySQL

33、強行使用或者忽略在 possible_keys 字段中的索引列表，可以在查詢語句中使用關(guān)鍵字FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 類型表，可以使用 ANALYZE TABLE 來幫助分析使用使用哪個索引更好。如果是 MyISAM 類型表，運行命令 myisamchk -analyze 也是一樣的效果。詳細的可以查看章節(jié) ANALYZE TABLE Syntax和5.7.2 Table Maintenance and Crash Recovery。key_lenkey_len 字段顯示了MySQL使用索引的

34、長度。當 key 字段的值為 NULL 時，索引的長度就是 NULL。注意，key_len 的值可以告訴你在聯(lián)合索引中MySQL會真正使用了哪些索引。refref 字段顯示了哪些字段或者常量被用來和 key 配合從表中查詢記錄出來。rowsrows 字段顯示了MySQL認為在查詢中應(yīng)該檢索的記錄數(shù)。Extra本字段顯示了查詢中MySQL的附加信息。以下是這個字段的幾個不同值的解釋：DistinctMySQL當找到當前記錄的匹配聯(lián)合結(jié)果的第一條記錄之后，就不再搜索其他記錄了。Not existsMySQL在查詢時做一個 LEFT JOIN 優(yōu)化時，當它在當前表中找到了和前一條記錄符合 LEFT

35、JOIN 條件后，就不再搜索更多的記錄了。下面是一個這種類型的查詢例子：SELECT*FROMt1LEFTJOINt2ONt1.id=t2.id WHEREt2.idISNULL;假使 t2.id 定義為 NOT NULL。這種情況下，MySQL將會掃描表 t1 并且用 t1.id 的值在 t2 中查找記錄。當在 t2 中找到一條匹配的記錄時，這就意味著 t2.id 肯定不會都是 NULL，就不會再在 t2 中查找相同 id 值的其他記錄了。也可以這么說，對于 t1 中的每個記錄，MySQL只需要在 t2 中做一次查找，而不管在 t2 中實際有多少匹配的記錄。range checked for

36、 each record (index map: #)MySQL沒找到合適的可用的索引。取代的辦法是，對于前一個表的每一個行連接，它會做一個檢驗以決定該使用哪個索引(如果有的話)，并且使用這個索引來從表里取得記錄。這個過程不會很快，但總比沒有任何索引時做表連接來得快。Using filesortMySQL需要額外的做一遍從而以排好的順序取得記錄。排序程序根據(jù)連接的類型遍歷所有的記錄，并且將所有符合 WHERE 條件的記錄的要排序的鍵和指向記錄的指針存儲起來。這些鍵已經(jīng)排完序了，對應(yīng)的記錄也會按照排好的順序取出來。詳情請看7.2.9 How MySQL Optimizes ORDER BY。Us

37、ing index字段的信息直接從索引樹中的信息取得，而不再去掃描實際的記錄。這種策略用于查詢時的字段是一個獨立索引的一部分。Using temporaryMySQL需要創(chuàng)建臨時表存儲結(jié)果以完成查詢。這種情況通常發(fā)生在查詢時包含了GROUP BY 和 ORDER BY 子句，它以不同的方式列出了各個字段。Using whereWHERE 子句將用來限制哪些記錄匹配了下一個表或者發(fā)送給客戶端。除非你特別地想要取得或者檢查表種的所有記錄，否則的話當查詢的 Extra 字段值不是 Using where 并且表連接類型是 ALL 或 index 時可能表示有問題。如果你想要讓查詢盡可能的快，那么就應(yīng)

38、該注意 Extra 字段的值為Using filesort 和 Using temporary 的情況。你可以通過 EXPLAIN 的結(jié)果中 rows 字段的值的乘積大概地知道本次連接表現(xiàn)如何。它可以粗略地告訴我們MySQL在查詢過程中會查詢多少條記錄。如果是使用系統(tǒng)變量 max_join_size 來取得查詢結(jié)果，這個乘積還可以用來確定會執(zhí)行哪些多表 SELECT 語句。詳情請看7.5.2 Tuning Server Parameters。下面的例子展示了如何通過 EXPLAIN 提供的信息來較大程度地優(yōu)化多表聯(lián)合查詢的性能。假設(shè)有下面的 SELECT 語句，正打算用 EXPLAIN 來檢測

39、：EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.Su

40、bmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;在這個例子中，先做以下假設(shè)： 要比較的字段定義如下：Table Column Column Type tt ActualPC CHAR(10) tt AssignedPC CHAR(10) tt ClientID CHAR(10) et EMPLOYID CHAR(15) do CUSTNMBR CHAR(15) 數(shù)據(jù)表的索引如下：Table Index tt ActualP

41、C tt AssignedPC tt ClientID et EMPLOYID (primary key) do CUSTNMBR (primary key) 的值是不均勻分布的。在任何優(yōu)化措施未采取之前，經(jīng)過 EXPLAIN 分析的結(jié)果顯示如下：table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74do ALL PRIMARY NULL NULL NULL 2135et_1 ALL PRIMARY NULL NULL NULL 74tt ALL AssignedPC, NULL N

42、ULL NULL 3872 ClientID, ActualPC range checked for each record (key map: 35)由于字段 type 的對于每個表值都是 ALL，這個結(jié)果意味著MySQL對所有的表做一個迪卡爾積;這就是說，每條記錄的組合。這將需要花很長的時間，因為需要掃描每個表總記錄數(shù)乘積的總和。在這情況下，它的積是 74 * 2135 * 74 * 3872 = 45,268,558,720 條記錄。如果數(shù)據(jù)表更大的話，你可以想象一下需要多長的時間。在這里有個問題是當字段定義一樣的時候，MySQL就可以在這些字段上更快的是用索引(對 ISAM 類型的表來

43、說，除非字段定義完全一樣，否則不會使用索引)。在這個前提下，VARCHAR 和 CHAR是一樣的除非它們定義的長度不一致。由于 tt.ActualPC 定義為 CHAR(10)，et.EMPLOYID 定義為 CHAR(15)，二者長度不一致。為了解決這個問題，需要用 ALTER TABLE 來加大 ActualPC 的長度從10到15個字符： mysqlALTERTABLEttMODIFYActualPCVARCHAR(15);現(xiàn)在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)了。再來執(zhí)行一次 EXPLAIN 語句看看結(jié)果：table type possi

44、ble_keys key key_len ref rows Extratt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPCdo ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1)et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1)et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

45、這還不夠，它還可以做的更好：現(xiàn)在 rows 值乘積已經(jīng)少了74倍。這次查詢需要用2秒鐘。第二個改變是消除在比較 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 中字段的長度不一致問題：mysql ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), - MODIFY ClientID VARCHAR(15);現(xiàn)在 EXPLAIN 的結(jié)果如下：table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL

46、 NULL 74tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPCet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1這看起來已經(jīng)是能做的最好的結(jié)果了。遺留下來的問題是，MySQL默認地認為字段tt.ActualPC 的值是均勻分布的，然而表 tt 并非如此。幸好，我們可以很方便的讓MySQL分析索引的分布：mysqlANALYZETABLEtt;到此為止，表連接已經(jīng)優(yōu)化

47、的很完美了，EXPLAIN 的結(jié)果如下： table type possible_keys key key_len ref rows Extratt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPCet eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1請注意，EXPLAIN 結(jié)果中的 rows 字段的值也是My

48、SQL的連接優(yōu)化程序大致猜測的，請檢查這個值跟真實值是否基本一致。如果不是，可以通過在 SELECT 語句中使用 STRAIGHT_JOIN 來取得更好的性能，同時可以試著在 FROM分句中用不同的次序列出各個表。MySQL數(shù)據(jù)庫優(yōu)化（三）作者: 葉金榮, 出處:IT專家網(wǎng),責任編輯: 李書琴, 2008-06-11 10:05這個章節(jié)講述了優(yōu)化程序如何處理 WHERE 子句。例子中使用了 SELECT 語句，但是在 DELETE 和 UPDATE 語句中對 WHERE 子句的優(yōu)化是一樣的。注意，關(guān)于MySQL優(yōu)化的工作還在繼續(xù)，因此本章節(jié)還沒結(jié)束。MySQL做了很多優(yōu)化工作，而不僅僅是文檔中

49、提到的這些。1. 估算查詢性能在大多數(shù)情況下，可以通過統(tǒng)計磁盤搜索次數(shù)來估算查詢的性能。對小表來說，通常情況下只需要搜索一次磁盤就能找到對應(yīng)的記錄(因為索引可能已經(jīng)緩存起來了)。對大表來說，大致可以這么估算，它使用B樹做索引，想要找到一條記錄大概需要搜索的次數(shù)為：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length) + 1。在MySQL中，一個索引塊通常是1024bytes，數(shù)據(jù)指針通常是4bytes。對于一個有500,000條記錄、索引長度為3bytes(medium in

50、teger)的表來說，根據(jù)上面的公式計算得到需要做 log(500,000)/log(1024/3*2/(3+4) + 1 = 4 次搜索。這個表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存儲空間(假定典型的索引緩沖區(qū)的2/3)，因此應(yīng)該會有更多的索引在內(nèi)存中，并且可能只需要1到2次調(diào)用就能找到對應(yīng)的記錄。對于寫來說，大概需要4次(甚至更多)搜索才能找到新的索引位置，更新記錄時通常需要2次搜索。請注意，前面的討論中并沒有提到應(yīng)用程序的性能會因為log N的值越大而下降。只要所有的東西都能由操作系統(tǒng)或者SQL服務(wù)器緩存起來，那么性能只會因為數(shù)據(jù)表越大而稍微下降。當數(shù)據(jù)越

51、來越大之后，就不能全部放到緩存中去了，就會越來越慢了，除非應(yīng)用程序是被磁盤搜索約束的(它跟隨著的log N值增加而增加)。為了避免這種情況，可以在數(shù)據(jù)量增大以后也隨著增大索引緩存容量。對 MyISAM 類型表來說，索引緩存容量是由系統(tǒng)變量 key_buffer_size 控制的。2. SELECT 查詢的速度通常情況下，想要讓一個比較慢的 SELECT . WHERE 查詢變得更快的第一件事就是，先檢查看看是否可以增加索引。所有對不同表的訪問都通常使用索引?？梢允褂?EXPLAIN 語句來判斷 SELECT 使用了哪些索引。詳情請看7.4.5 How MySQL Uses Indexes和7.

52、2.1 EXPLAIN Syntax (Get Information About a SELECT)。以下是幾個常用的提高 MyISAM 表查詢速度的忠告：想要讓MySQL將查詢優(yōu)化的速度更快些，可以在數(shù)據(jù)表已經(jīng)加載完全部數(shù)據(jù)后執(zhí)行行 ANALYZE TABLE 或運行 myisamchk -analyze 命令。它更新了每個索引部分的值，這個值意味著相同記錄的平均值(對于唯一索引來說，這個值則一直都是 1)。MySQL就會在當你使用基于一個非恒量表達式的兩表連接時，根據(jù)這個值來決定使用哪個索引。想要查看結(jié)果，可以在分析完數(shù)據(jù)表后運行 SHOW INDEX FROM tbl_name 查看 Cardinality 字段的值。myisamchk -description -verbose 顯示了索引的分布信息。想要根據(jù)一個索引來排序數(shù)據(jù)，可以運行 myisamchk -sort-index -sort-records=1 (如果想要在索引 1 上做排序)。這對于