MySQL性能優化總結

MySQL性能優化全攻略

資料庫性能優化涉及到系統硬體和軟體的方方面面，本文讨論的主要是編譯和配置優化、伺服器參數調整、如何選用合适的表類型，以及如何用資料庫内建的指令輔助分析和優化性能，特别是如何用EXPLAIN輔助優化查詢的性能。

　　許多新手往往把重新編譯源代碼看成是一種無可避免的災禍，其實編譯源代碼還能對程式的最終性能起到顯著的影響。編譯過程可以用不同流水線上裝配同樣型号的汽車比拟：第一條流水線由素質較低的勞工操作，裝配程式未能盡善盡美，零件裝配誤差較大；第二條流水線由高素質的技術勞工操作，汽車裝配程式合理，且利用最好的工具保證産品的高品質。雖然兩條流水線上裝配出來的汽車外觀一模一樣，但兩種汽車的性能表現卻可能大不相同。對于編譯器來說情況也完全相似，有些編譯器裝配出來的程式要比其他編譯器的更好。

　　編譯時考慮所有可用的選項也是極其重要的。很可能某些編譯器的預設選項值不能符合要求，或者，為了滿足應用的特定需求，我們需要指定一些特殊的編譯選項。正如MySQL文檔所指出的，隻要采用了更好的編譯器或者使用更合理的編譯選項，應用性能的提高程度可以達到10-30%。

　　既然如此，編譯時具體應該注意哪些問題才能讓MySQL資料庫運作得更快呢？

 ▲ 使用pgcc編譯器

　　如果系統使用的是奔騰處理器，那麼pgcc（Pentium GCC）正是為這些系統下運作的程式提供的專用編譯器。pgcc是gcc編譯器（http://www.gnu.org/software/gcc/）的奔騰優化版，用pgcc編譯MySQL代碼可以讓整體性能提高10%以上！關于pgcc的更多資訊，請參見http://www.goof.com/pcg/。當然，如果系統使用的不是奔騰處理器，采用這種方法提高MySQL的運作速度就不合适了，因為正如其名字所示，pgcc是專門為奔騰系統提供的。

 ▲ 把mysqld編譯成靜态模式

 　　以不帶共享庫的形式編譯mysqld同樣可以提高性能。在配置行加入下面這個選項可以将mysqld編譯成靜态模式：

 % >./configure -with-mysqld-ldflags=-all-static [--其他配置選項]

 ▲ 配置示例

　　下面的配置指令經常用于提高MySQL的性能：

% >CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6

-mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti"

./configure --prefix=/usr/local --enable-assembler --with-mysqld-ldflags=-all-static

--disable-shared

　　詳細解釋每個gcc選項的作用已經超出了本文的範圍，請通路gcc的說明文檔了解這些資訊（http://gcc.gnu.org/）。注意不要拘泥于這個例子，請在指令行執行man gcc仔細了解每一個gcc選項的含義。

正确的編譯方法固然重要，但它隻是提高MySQL伺服器性能工作的一部分。MySQL伺服器的許多參數會影響伺服器的性能表現，而且我們可以把這些參數儲存到配置檔案，使得每次MySQL伺服器啟動時這些參數都自動發揮作用。這個配置檔案就是my.cnf。

　　MySQL伺服器提供了my.cnf檔案的幾個示例，它們可以在/usr/local/mysql/share/mysql/目錄下找到，名字分别為my-small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf。檔案名字中關于規模的說明描述了該配置檔案适用的系統類型。例如，如果運作MySQL伺服器的系統記憶體不多，而且MySQL隻是偶爾使用，那麼使用my-small.cnf配置檔案最為理想，這個配置檔案告訴mysqld daemon使用最少的系統資源。反之，如果MySQL伺服器用于支援一個大規模的線上商場，系統擁有2G的記憶體，那麼使用mysql-huge.cnf最為合适。

　　要使用上述示例配置檔案，我們應該先複制一個最适合要求的配置檔案，并把它命名為my.cnf。這個複制得到的配置檔案可以按照如下三種方式使用：

全局：把這個my.cnf檔案複制到伺服器的/etc目錄，此時檔案中所定義的參數将全局有效，即對該伺服器上運作的所有MySQL資料庫伺服器都有效。

　　局部：把這個my.cnf檔案複制到[MYSQL-INSTALL-DIR]/var/将使該檔案隻對指定的伺服器有效，其中[MYSQL-INSTALL-DIR]表示安裝MySQL的目錄。

　　使用者：最後，我們還可以把該檔案的作用範圍局限到指定的使用者，這隻需把my.cnf檔案複制到使用者的根目錄即可。

　　那麼，如何設定my.cnf檔案中的參數呢？或者進一步說，哪些參數是我們可以設定的呢？所有這些參數都對MySQL伺服器有着全局性的影響，但同時每一個參數都和MySQL的特定部分關系較為密切。例如，max_connections參數屬于mysqld一類。那麼，如何才能得知這一點呢？這隻需執行如下指令：

% >/usr/local/mysql/libexec/mysqld --help

　　該指令将顯示出和mysqld有關的各種選項和參數。要尋找這些參數非常友善，因為這些參數都在“Possible variables for option --set-variable (-O) are”這行内容的後面。找到這些參數之後，我們就可以在my.cnf檔案中按照如下方式設定所有這些參數：

set-variable = max_connections=100

　　這行代碼的效果是：同時連接配接MySQL伺服器的最大連接配接數量限制為100。不要忘了在my.cnf檔案[mysqld]小節加上一個set-variable指令，具體請參見配置檔案中的示例。

　　MySQL實際上支援五種不同的表類型，有些人可能會對此感到不同尋常。這五種類型分别是BDB、HEAP、ISAM、MERGE以及MyISAM。其中BDB類型單獨屬于一類，稱為“事務安全型”（transaction-safe），其餘的表類型屬于第二類，稱為“非事務安全型”（non-transaction-safe）。下面我們詳細介紹這些表類型。

　　事務安全型

 ▲ BDB

　　BDB全稱是“Berkeley DB”，它是MySQL具有事務能力的表類型，由Sleepycat Software （http://www.sleepycat.com）開發。BDB表類型提供了MySQL使用者長久期盼的功能，即事務控制能力。在任何RDBMS中，事務控制能力都是一種極其重要和寶貴的功能。事務控制能力使得我們能夠確定一組指令确實已經全部執行成功，或者確定當任何一個指令出現錯誤時所有指令的執行結果均被回退。可以想象，在電子銀行這類應用中事務控制能力是極其重要的。

　非事務安全型

▲ HEAP

　　HEAP表是通路資料速度最快的MySQL表，這是因為這類表使用儲存在記憶體中的散列索引。但有極其重要的一點必須注意，如果MySQL或者伺服器崩潰，HEAP表中的資料将會丢失！

▲ ISAM

　　ISAM表類型是MyISAM出現之前MySQL的預設表類型，是以現在這種表類型是不推薦使用的，建議改用MyISAM表。

 ▲ MERGE

　　MERGE是一種值得關注的新式表類型，在3.23.25版中提供。MERGE表實際上由一組同樣的MyISAM表合并而成。之是以要把多個同樣的表合并成一個，主要是出于性能上的考慮，因為它能夠提高搜尋速度、提高修複效率、節省磁盤空間。

目前的MERGE表類型仍舊屬于BETA版本，但相信正式版本很快就會出現。

▲ MyISAM

　　MyISAM表類型是MySQL預設的表類型。MyISAM表類型以ISAM為基礎，但增加了許多有用的擴充。下面是部分用MyISAM表類型取代ISAM表類型的原因：

　　MyISAM表比ISAM表要小，因而占用資源更少。

　　MyISAM表在不同的平台間二進制可移植。

　　MyISAM還有其他許多優點。請通路http://www.mysql.com/doc/I/S/ISAM.html檢視關于該表類型的完整說明。

　　表的類型在建立表時指定。在下面這個例子中我們建立了一個HEAP類型的表：

 mysql >CREATE TABLE email_addresses TYPE=HEAP (

- >email char(55) NOT NULL,

- >name char(30) NOT NULL,

- >PRIMARY KEY(email) );

 　　建立BDB表需要更多的配置參數，請參考http://www.mysql.com/doc/B/D/BDB_overview.html了解完整說明以及要使用BDB表應該做哪些準備。

　　MySQL 4.0将增加兩種新的表類型，即Innobase和Gemeni。關于這兩種表類型現在能夠得到的資訊還不多。

　　關于MySQL表類型，有待學習的知識實在太多，本文簡短的介紹不可能做到完整和詳盡。建議通路MySQL文檔（http://www.mysql.com）了解更詳盡的資訊。

　　接下來我們要讨論的是資料庫性能優化的另一方面，即運用資料庫伺服器内建的工具輔助性能分析和優化。

 ▲ SHOW

　　執行下面這個指令可以了解伺服器的運作狀态：

mysql >show status;

　　該指令将顯示出一長列狀态變量及其對應的值，其中包括：被中止通路的使用者數量，被中止的連接配接數量，嘗試連接配接的次數，并發連接配接數量最大值，以及其他許多有用的資訊。這些資訊對于确定系統問題和效率低下的原因是十分有用的。

　　SHOW指令除了能夠顯示出MySQL伺服器整體狀态資訊之外，它還能夠顯示出有關日志檔案、指定資料庫、表、索引、程序和許可權限表的寶貴資訊。請通路http://www.mysql.com/doc/S/H/SHOW.html了解更多資訊。

▲ EXPLAIN

　　EXPLAIN能夠分析SELECT指令的處理過程。這不僅對于決定是否要為表加上索引很有用，而且對于了解MySQL處理複雜連接配接的過程也很有用。

　　下面這個例子顯示了如何用EXPLAIN提供的資訊逐漸地優化連接配接查詢。（本例來自MySQL文檔，見http://www.mysql.com/doc/E/X/EXPLAIN.html。原文寫到這裡似乎有點潦草了事，特加上此例。）

　　假定用EXPLAIN分析的SELECT指令如下所示：

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.SubmitTime IS NULL

AND tt.ActualPC = et.EMPLOYID

AND tt.AssignedPC = et_1.EMPLOYID

AND tt.ClientID = do.CUSTNMBR;

SELECT指令中出現的表定義如下：

　　 ※表定義

表 列 列類型

tt ActualPC CHAR(10)

tt AssignedPC CHAR(10)

tt ClientID CHAR(10)

et EMPLOYID CHAR(15)

do CUSTNMBR CHAR(15)

　　 ※索引

表 索引

tt ActualPC

tt AssignedPC

tt ClientID

et EMPLOYID (主鍵)

do CUSTNMBR (主鍵)

　　 ※tt.ActualPC值分布不均勻

在進行任何優化之前，EXPLAIN對SELECT執行分析的結果如下：

table type possible_keys key key_len ref rows Extra

et ALL PRIMARY NULL NULL NULL 74

do ALL PRIMARY NULL NULL NULL 2135

et_1 ALL PRIMARY NULL NULL NULL 74

tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872

range checked for each record (key map: 35)

 　　每一個表的type都是ALL，它表明MySQL為每一個表進行了完全連接配接！這個操作是相當耗時的，因為待處理行的數量達到每一個表行數的乘積！即，這裡的總處理行數為74 * 2135 * 74 * 3872 = 45,268,558,720。

　　這裡的問題之一在于，如果資料庫列的聲明不同，MySQL（還）不能有效地運用列的索引。在這個問題上，VARCHAR和CHAR是一樣的，除非它們聲明的長度不同。由于tt.ActualPC聲明為CHAR(10)，而et.EMPLOYID聲明為CHAR(15)，是以這裡存在列長度不比對問題。

　　為了解決這兩個列的長度不比對問題，用ALTER TABLE指令把ActualPC列從10個字元擴充到15字元，如下所示：

mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

現在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)了，執行EXPLAIN進行分析得到的結果如下所示：

tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used

range checked for each record (key map: 1)

et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

這還算不上完美，但已經好多了（行數的乘積現在少了一個系數74）。現在這個SQL指令執行大概需要數秒鐘時間。

為了避免tt.AssignedPC = et_1.EMPLOYID以及tt.ClientID = do.CUSTNMBR比較中的列長度不比對，我們可以進行如下改動：

mysql > ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),

MODIFY ClientID VARCHAR(15);

現在EXPLAIN顯示的結果如下：

tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used

et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1

do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

這個結果已經比較令人滿意了。

餘下的問題在于，預設情況下，MySQL假定tt.ActualPC列的值均勻分布，而事實上tt表的情況并非如此。幸而，我們可以很容易地讓MySQL知道這一點：

shell > myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt

shell > mysqladmin refresh

現在這個連接配接操作已經非常理想，EXPLAIN分析的結果如下：

▲ OPTIMIZE

 　　OPTIMIZE能夠恢複和整理磁盤空間以及資料碎片，一旦對包含變長行的表進行了大量的更新或者删除，進行這個操作就非常有必要了。OPTIMIZE目前隻能用于MyISAM和BDB表。

　　結束語：從編譯資料庫伺服器開始、貫穿整個管理過程，能夠改善MySQL性能的因素實在非常多，本文隻涉及了其中很小的一部分。盡管如此，我們希望本文讨論的内容能夠對你有所幫助。