MySQL性能優化全攻略－ 相關數據庫命令

　　我們要討論的是數據庫性能優化的另一方面即運用數據庫服務器內建的工具輔助性能分析和優化
　　
　　▲ SHOW
　　
　　執行下面這個命令可以了解服務器的運行狀態
　　
　　mysql >show status;
　　
　　該命令將顯示出一長列狀態變量及其對應的值其中包括被中止訪問的用戶數量被中止的連接數量嘗試連接的次數並發連接數量最大值以及其他許多有用的信息這些信息對於確定系統問題和效率低下的原因是十分有用的
　　
　　SHOW命令除了能夠顯示出MySQL服務器整體狀態信息之外它還能夠顯示出有關日志文件指定數據庫表索引進程和許可權限表的寶貴信息請訪問了解更多信息
　　
　　▲ EXPLAIN
　　EXPLAIN能夠分析SELECT命令的處理過程這不僅對於決定是否要為表加上索引很有用而且對於了解MySQL處理復雜連接的過程也很有用
　　
　　下面這個例子顯示了如何用EXPLAIN提供的信息逐步地優化連接查詢（本例來自MySQL文檔見原文寫到這裡似乎有點潦草了事特加上此例）
　　
　　假定用EXPLAIN分析的SELECT命令如下所示
　　
　　EXPLAIN SELECT ttTicketNumber ttTimeIn
　　ttProjectReference ttEstimatedShipDate
　　ttActualShipDate ttClientID
　　ttServiceCodes ttRepetitiveID
　　ttCurrentProcess ttCurrentDPPerson
　　ttRecordVolume ttDPPrinted etCOUNTRY
　　et_COUNTRY doCUSTNAME
　　FROM tt et et AS et_ do
　　WHERE ttSubmitTime IS NULL
　　AND ttActualPC = etEMPLOYID
　　AND ttAssignedPC = et_EMPLOYID
　　AND ttClientID = doCUSTNMBR;
　　
　　SELECT命令中出現的表定義如下
　　
　　※表定義
　　
　　表 列 列類型
　　tt ActualPC CHAR()
　　tt AssignedPC CHAR()
　　tt ClientID CHAR()
　　et EMPLOYID CHAR()
　　do CUSTNMBR CHAR()
　　
　　※索引
　　
　　表 索引
　　tt ActualPC
　　tt AssignedPC
　　tt ClientID
　　et EMPLOYID (主鍵)
　　do CUSTNMBR (主鍵)
　　
　　※ttActualPC值分布不均勻
　　
　　在進行任何優化之前EXPLAIN對SELECT執行分析的結果如下
　　
　　table type possible_keys key key_len ref rows Extra
　　et ALL PRIMARY NULL NULL NULL
　　do ALL PRIMARY NULL NULL NULL
　　et_ ALL PRIMARY NULL NULL NULL
　　tt ALL AssignedPCClientIDActualPC NULL NULL NULL
　　range checked for each record (key map: )
　　
　　每一個表的type都是ALL它表明MySQL為每一個表進行了完全連接！這個操作是相當耗時的因為待處理行的數量達到每一個表行數的乘積！即這裡的總處理行數為 * * * =
　　
　　這裡的問題之一在於如果數據庫列的聲明不同MySQL（還）不能有效地運用列的索引在這個問題上VARCHAR和CHAR是一樣的除非它們聲明的長度不同由於ttActualPC聲明為CHAR()而etEMPLOYID聲明為CHAR()因此這裡存在列長度不匹配問題
　　
　　為了解決這兩個列的長度不匹配問題用ALTER TABLE命令把ActualPC列從個字符擴展到字符如下所示
　　
　　mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR();
　　
　　現在ttActualPC和etEMPLOYID都是VARCHAR()了執行EXPLAIN進行分析得到的結果如下所示
　　
　　table type possible_keys key key_len ref rows Extra
　　tt ALL AssignedPCClientIDActualPC NULL NULL NULL where used
　　do ALL PRIMARY NULL NULL NULL
　　range checked for each record (key map: )
　　et_ ALL PRIMARY NULL NULL NULL
　　range checked for each record (key map: )
　　
　　et eq_ref PRIMARY PRIMARY ttActualPC
　　
　　這還算不上完美但已經好多了（行數的乘積現在少了一個系數）現在這個SQL命令執行大概需要數秒鐘時間
　　
　　為了避免ttAssignedPC = et_EMPLOYID以及ttClientID = doCUSTNMBR比較中的列長度不匹配我們可以進行如下改動
　　
　　mysql > ALTER TABLE tt MODIFY AssignedPC VARCHAR()
　　MODIFY ClientID VARCHAR();
　　
　　現在EXPLAIN顯示的結果如下
　　
　　table type possible_keys key key_len ref rows Extra
　　et ALL PRIMARY NULL NULL NULL
　　tt ref AssignedPCClientIDActualPC ActualPC etEMPLOYID where used
　　et_ eq_ref PRIMARY PRIMARY ttAssignedPC
　　do eq_ref PRIMARY PRIMARY ttClientID
　　
　　這個結果已經比較令人滿意了
　　
　　余下的問題在於默認情況下MySQL假定ttActualPC列的值均勻分布而事實上tt表的情況並非如此幸而我們可以很容易地讓MySQL知道這一點
　　
　　shell > myisamchk analyze PATH_TO_MYSQL_DATABASE/tt
　　shell > mysqladmin refresh
　　
　　現在這個連接操作已經非常理想EXPLAIN分析的結果如下
　　
　　table type possible_keys key key_len ref rows Extra
　　tt ALL AssignedPCClientIDActualPC NULL NULL NULL where used
　　et eq_ref PRIMARY PRIMARY ttActualPC
　　et_ eq_ref PRIMARY PRIMARY ttAssignedPC
　　do eq_ref PRIMARY PRIMARY ttClientID
　　
　　▲ OPTIMIZE
　　
　　OPTIMIZE能夠恢復和整理磁盤空間以及數據碎片一旦對包含變長行的表進行了大量的更新或者刪除進行這個操作就非常有必要了OPTIMIZE當前只能用於MyISAM和BDB表
　　
　　結束語從編譯數據庫服務器開始貫穿整個管理過程能夠改善MySQL性能的因素實在非常多本文只涉及了其中很小的一部分盡管如此我們希望本文討論的內容能夠對你有所幫助

From:http://tw.wingwit.com/Article/program/MySQL/201311/29426.html