譯者：葉金榮（Email:
7 MySQL 優(yōu)化
數(shù)據(jù)庫優(yōu)化是一項很復雜的工作，因為這最終需要對系統(tǒng)優(yōu)化的很好理解才行。盡管對系統(tǒng)或應(yīng)用系統(tǒng)的了解不多的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。
本章主要講解了幾種優(yōu)化MySQL的方法，并且給出了例子。記著，總有各種辦法能讓系統(tǒng)運行的更快，當然了，這需要更多的努力。
7.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)化更容易，因為可以從多個媒介中并行地讀取數(shù)據(jù)。
- CPU周期。數(shù)據(jù)存儲在主內(nèi)存中（或者它已經(jīng)在主內(nèi)存中了），這就需要處理這些數(shù)據(jù)以得到想要的結(jié)果。存在多個?硐啾饒詿嬡萘坷此蹈竅拗頻囊蛩?。策€孕"砝此擔俁韌ǔ２皇俏侍狻?
- 內(nèi)存帶寬。當CPU要將更多的數(shù)據(jù)存放在CPU緩存中時，主內(nèi)存的帶寬就是瓶頸了。在大多數(shù)系統(tǒng)中，這不是常見的瓶頸，不過也是要注意的一個因素。
7.1.1 MySQL 設(shè)計的局限性
當使用
MyISAM存儲引擎時，MySQL會使用一個快速數(shù)據(jù)表鎖以允許同時多個讀取和一個寫入。這種存儲引擎的最大問題是發(fā)生在一個單一的表上同時做穩(wěn)定的更新操作及慢速查詢。如果這種情況在某個表中存在，可以使用另一種表類型。詳情請看"15 MySQL Storage 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 How MySQL Deals with Constraints 和 "14.1.4 INSERT Syntax"。
7.1.2 應(yīng)用設(shè)計的可移植性
由于各種不同的數(shù)據(jù)庫實現(xiàn)了各自的SQL標準，這就需要我們盡量使用可移植的SQL應(yīng)用。查詢和插入操作很容易就能做到可移植，不過由于更多的約束條件的要求就越發(fā)困難。想要讓一個應(yīng)用在各種數(shù)據(jù)庫系統(tǒng)上快速運行，就變得更困難了。
為了能讓一個復雜的應(yīng)用做到可移植，就要先看這個應(yīng)用運行于哪種數(shù)據(jù)庫系統(tǒng)之上，然后看這些數(shù)據(jù)庫系統(tǒng)都支持哪些特性。
每個數(shù)據(jù)庫系統(tǒng)都有某些不足。也就是說，由于設(shè)計上的一些妥協(xié)，導致了性能上的差異。
可以用MySQL的 crash-me 程序來看選定的數(shù)據(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來解決各種移植性的問題。
想要看到 crash-me 的結(jié)果，可以訪問：http://dev.mysql.com/tech-resources/crash-me.php. 訪問 http://dev.mysql.com/tech-resources/benchmarks 可以看到基準的結(jié)果。
如果您想努力做到獨立于數(shù)據(jù)庫，這就需要對各種SQL服務(wù)器的瓶頸都有一些很好的想法。例如，MySQL對于 MyISAM 類型的表在檢索以及更新記錄時非?？?，但是在有并發(fā)的慢速讀取及寫入記錄時卻有一定的問題。作為Oracle來說，它在訪問剛剛被更新的記錄時有很大的問題（直到結(jié)果被刷新到磁盤中）。事務(wù)數(shù)據(jù)庫一般地在從日志表中生成摘要表這方面的表現(xiàn)不怎么好，因為在這種情況下，行記錄鎖幾乎沒用。
為了能讓應(yīng)用程序真正的做到獨立于數(shù)據(jù)庫，就必須把操作數(shù)據(jù)的接口定義的簡單且可擴展。由于C++在很多系統(tǒng)上都可以使用，因此使用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ù)在一定時間后過期，來合理的更新緩存。這是處理負載高峰期時的一種方法，這種情況下，可以通過加大緩存容量和過期時間直到負載趨于正常。
這種情況下，建表信息中就要包含了初始化緩存的容量以及正常刷新數(shù)據(jù)表的頻率。
一個實現(xiàn)應(yīng)用層緩存的可選方案是使用MySQL的查詢緩存（query cache）。啟用查詢緩存后，數(shù)據(jù)庫就會根據(jù)一些詳情來決定哪些結(jié)果可以被重用。它大大簡化了應(yīng)用程序，詳情請看"5.11 The MySQL Query Cache"。
7.1.3 我們都用MySQL來做什么
本章描述了一個MySQL的早期應(yīng)用。
在MySQL最開始的開發(fā)過程中，MySQL本來是要準備給大客戶用的，他們是瑞典的2個最大的零售商，他們用于貨物存儲數(shù)據(jù)管理。
我們每周從所有的商店中得到交易利潤累計結(jié)果，以此給商店的老板提供有用的信息，幫助他們分析如果更好的打廣告以影響他們的客戶。
數(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個工具。
為了得到圖形數(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ù)沒有任何問題。最終這個系統(tǒng)移植到了Linux上。
7.1.4 MySQL 基準套件
本章本來要包括MySQL基準套件（以及 crash-me）的技術(shù)描述的，但是至今還未寫?，F(xiàn)在，您可以通過查看MySQL發(fā)布源代碼 `sql-bench‘ 目錄下的代碼以及結(jié)果有一個更好的想法。
基準套件就是想告訴用戶執(zhí)行什么樣的SQL查詢表現(xiàn)的更好或者更差。
請注意，這個基準是單線程的，因此它度量了操作執(zhí)行的最少時間。我們未來打算增加多線程測試的基準套件。
想要使用基準套件，必備以下幾個條件：
- 基準套件在MySQL的發(fā)布源代碼中就有?？梢匀?http://dev.mysql.com/downloads/ 下載發(fā)布版或者使用現(xiàn)有開發(fā)代碼樹（詳情請看"2.3.3 Installing from the Development 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-benchshell> perl run-all-tests --server=server_name
server_name 可以是任何一個可用的服務(wù)。想要列出所有的可用選項和支持的服務(wù)，只要調(diào)用以下命令：
shell> perl run-all-tests --help
crash-me 腳本也是放在 `sql-bench‘ 目錄下。crash-me 通過執(zhí)行真正的查詢以試圖判斷數(shù)據(jù)庫都支持什么特性、性能表現(xiàn)以及限制。例如，它可以判斷：
- 都支持什么字段類型
- 支持多少索引
- 支持什么樣的函數(shù)
- 能支持多大的查詢
VARCHAR字段類型能支持多大
可以從 http://dev.mysql.com/tech-resources/crash-me.php 上找到各種不同數(shù)據(jù)庫 crash-me 的結(jié)果。更多的信息請訪問 http://dev.mysql.com/tech-resources/benchmarks。
7.1.5 使用您自己的基準
請確定對您的數(shù)據(jù)庫或者應(yīng)用程序做基準測試，以發(fā)現(xiàn)它們的瓶頸所在。解決這個瓶頸（或者使用一個假的模塊來代替）之后，就能很容易地找到下一個瓶頸了。即使應(yīng)用程序當前總體的表現(xiàn)可以接受，不過還是至少要做好找到每個瓶頸的計劃，說不定某天您就希望應(yīng)用程序能有更好的性能。
從MySQL的基準套件中就能找到一個便攜可移植的基準測試程序了。詳情請看"7.1.4 The MySQL Benchmark Suite"。您可以從基準套件中的任何一個程序，做適當?shù)男薷囊赃m合您的需要。通過整個方式，您就可以有各種不同的辦法來解決問題，知道哪個程序才是最快的。
另一個基準套件是開放源碼的數(shù)據(jù)庫基準，可以在 http://osdb.sourceforge.net 上找到。
當系統(tǒng)負載十分繁重的時候，通常就會發(fā)生問題。我們就有很多客戶聯(lián)系我們說他們有一個（測試過的）生產(chǎn)系統(tǒng)也遭遇了負載問題。在很多情況下，性能問題歸結(jié)于數(shù)據(jù)庫的基本設(shè)計（例如，在高負載下掃描數(shù)據(jù)表的表現(xiàn)不好）、操作系統(tǒng)、或者程序庫等因素。很多時候，這些問題在還沒有正式用于生產(chǎn)前相對更容易解決。
為了避免發(fā)生這樣的問題，最好讓您的應(yīng)用程序在可能的最差的負載下做基準測試！可以使用Super Smack，在 http://jeremy.zawodny.com/mysql/super-smack 可以找到。從它名字的意思就能想到，只要您愿意，它就能讓您的系統(tǒng)死掉，因此確認只在開發(fā)系統(tǒng)上做測試。
7.2 優(yōu)化 SELECT 語句及其他查詢
首先，影響所有語句的一個因素是：您的權(quán)限設(shè)置越復雜，那么開銷就越大。
使用比較簡單的 GRANT 語句能讓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 BENCHMARK(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() 是一個用于檢查查詢語句中是否存在問題的非常好的工具。
