• 您的位置：創(chuàng)新互聯(lián) >> 網(wǎng)站建設 >> 網(wǎng)站數(shù)據(jù)庫 >> MySQL優(yōu)化全攻略-相關數(shù)據(jù)庫命令
  

MySQL優(yōu)化全攻略-相關數(shù)據(jù)庫命令

• 作者：譚孝梅 文章來源：網(wǎng)站數(shù)據(jù)庫 點擊數(shù)：1241 更新時間：2011-9-11
• MySQL優(yōu)化全攻略-相關數(shù)據(jù)庫命令
  我們討論的是數(shù)據(jù)庫性能優(yōu)化的另一方面，即運用數(shù)據(jù)庫服務器內(nèi)建的工具輔助性能分析和優(yōu)化。
  ▲ SHOW
  執(zhí)行下面這個命令可以了解服務器的運行狀態(tài)：mysql >show status;
  該命令將顯示出一長列狀態(tài)變量及其對應的值，其中包括：被中止訪問的用戶數(shù)量，被中止的連接數(shù)量，嘗試連接的次數(shù)，并發(fā)連接數(shù)量最大值，以及其他許多有用的信息。這些信息對于確定系統(tǒng)問題和效率低下的原因是十分有用的。
  SHOW命令除了能夠顯示出MySQL服務器整體狀態(tài)信息之外，它還能夠顯示出有關日志文件、指定數(shù)據(jù)庫、表、索引、進程和許可權限表的寶貴信息。
  ▲ EXPLAIN
  EXPLAIN能夠分析SELECT命令的處理過程。這不僅對于決定是否要為表加上索引很有用，而且對于了解MySQL處理復雜連接的過程也很有用。
  下面這個例子顯示了如何用EXPLAIN提供的信息逐步地優(yōu)化連接查詢。（本例來自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命令中出現(xiàn)的表定義如下：
  表定義
  表　　　　列　　　　　　列類型
  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值分布不均勻
  在進行任何優(yōu)化之前，EXPLAIN對SELECT執(zhí)行分析的結果如下：
  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為每一個表進行了完全連接！這個操作是相當耗時的，因為待處理行的數(shù)量達到每一個表行數(shù)的乘積！即，這里的總處理行數(shù)為74 * 2135 * 74 * 3872 = 45,268,558,720。
  這里的問題之一在于，如果數(shù)據(jù)庫列的聲明不同，MySQL（還）不能有效地運用列的索引。在這個問題上，VARCHAR和CHAR是一樣的，除非它們聲明的長度不同。由于tt.ActualPC聲明為CHAR(10)，而 et.EMPLOYID聲明為CHAR(15)，因此這里存在列長度不匹配問題。
  為了解決這兩個列的長度不匹配問題，用ALTER TABLE命令把ActualPC列從10個字符擴展到15字符，如下所示：mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
  現(xiàn)在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)了，執(zhí)行EXPLAIN進行分析得到的結果如下所示：
  table type possible_keys key key_len ref rows Extra
  tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
  do 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
  
  這還算不上完美，但已經(jīng)好多了（行數(shù)的乘積現(xiàn)在少了一個系數(shù)74）�，F(xiàn)在這個SQL命令執(zhí)行 大概需要數(shù)秒鐘時間。 為了避免tt.AssignedPC = et_1.EMPLOYID以及tt.ClientID = do.CUSTNMBR比較中的列長度不匹配，我們可以進行如下改動：
  mysql > ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
  MODIFY ClientID VARCHAR(15);
  現(xiàn)在EXPLAIN顯示的結果如下：
  table type possible_keys key key_len ref rows Extra
  et ALL PRIMARY NULL NULL NULL 74
  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
  
  這個結果已經(jīng)比較令人滿意了。余下的問題在于，默認情況下，MySQL假定tt.ActualPC列的值均勻分布，而事實上tt表的情況并非如此。幸而，我們可以很容易地讓MySQL知道這一點：
  shell > myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
  shell > mysqladmin refresh
  現(xiàn)在這個連接操作已經(jīng)非常理想，EXPLAIN分析的結果如下：
  table type possible_keys key key_len ref rows Extra
  tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
  et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
  et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
  do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
  ▲ OPTIMIZE
  OPTIMIZE能夠恢復和整理磁盤空間以及數(shù)據(jù)碎片，一旦對包含變長行的表進行了大量的更新或者刪除，進行這個操作就非常有必要了。OPTIMIZE當前只能用于MyISAM和BDB表。
  結束語：
  從編譯數(shù)據(jù)庫服務器開始、貫穿整個管理過程，能夠改善MySQL性能的因素實在非常多，本文只涉及了其中很小的一部分。
• 上一篇文章：換服務器IP對百度影響最小的技巧
  下一篇文章：如何選擇一個有利推廣的虛擬空間