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

2019-09-08 23:26:57

字體：大中小

供稿：網(wǎng)友

▲ SHOW
　　
執(zhí)行下面這個(gè)命令可以了解服務(wù)器的運(yùn)行狀態(tài)：
　　

以下為引用的內(nèi)容：

　　mysql >show status;

　
        該命令將顯示出一長(zhǎng)列狀態(tài)變量及其對(duì)應(yīng)的值，其中包括：被中止訪問(wèn)的用戶數(shù)量，被中止的連接數(shù)量，嘗試連接的次數(shù)，并發(fā)連接數(shù)量最大值，以及其他許多有用的信息。這些信息對(duì)于確定系統(tǒng)問(wèn)題和效率低下的原因是十分有用的。
　　
SHOW命令除了能夠顯示出MySQL服務(wù)器整體狀態(tài)信息之外，它還能夠顯示出有關(guān)日志文件、指定數(shù)據(jù)庫(kù)、表、索引、進(jìn)程和許可權(quán)限表的寶貴信息。請(qǐng)?jiān)L問(wèn)http://www.mysql.com/doc/S/H/SHOW.html了解更多信息。
　　
　　▲ EXPLAIN
　　
         EXPLAIN能夠分析SELECT命令的處理過(guò)程。這不僅對(duì)于決定是否要為表加上索引很有用，而且對(duì)于了解MySQL處理復(fù)雜連接的過(guò)程也很有用。
　　
         下面這個(gè)例子顯示了如何用EXPLAIN提供的信息逐步地優(yōu)化連接查詢。（本例來(lái)自MySQL文檔，見(jiàn)http://www.mysql.com/doc/E/X/EXPLAIN.html。原文寫(xiě)到這里似乎有點(diǎn)潦草了事，特加上此例。）
　　
　　假定用EXPLAIN分析的SELECT命令如下所示：
　　

以下為引用的內(nèi)容：

　　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)的表定義如下：
　　
　　※表定義
　　
　　表列列類(lèi)型
　　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值分布不均勻
　　
　　在進(jìn)行任何優(yōu)化之前，EXPLAIN對(duì)SELECT執(zhí)行分析的結(jié)果如下：
　　
　　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)

　　
　　每一個(gè)表的type都是ALL，它表明MySQL為每一個(gè)表進(jìn)行了完全連接！這個(gè)操作是相當(dāng)耗時(shí)的，因?yàn)榇幚硇械臄?shù)量達(dá)到每一個(gè)表行數(shù)的乘積！即，這里的總處理行數(shù)為74 * 2135 * 74 * 3872 = 45,268,558,720。
　　
　　這里的問(wèn)題之一在于，如果數(shù)據(jù)庫(kù)列的聲明不同，MySQL（還）不能有效地運(yùn)用列的索引。在這個(gè)問(wèn)題上，VARCHAR和CHAR是一樣的，除非它們聲明的長(zhǎng)度不同。由于tt.ActualPC聲明為CHAR(10)，而et.EMPLOYID聲明為CHAR(15)，因此這里存在列長(zhǎng)度不匹配問(wèn)題。
　　
　　為了解決這兩個(gè)列的長(zhǎng)度不匹配問(wèn)題，用ALTER TABLE命令把ActualPC列從10個(gè)字符擴(kuò)展到15字符，如下所示：
　　

以下為引用的內(nèi)容：

　　mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
　　
　　現(xiàn)在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)了，執(zhí)行EXPLAIN進(jìn)行分析得到的結(jié)果如下所示：
　　
　　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)在少了一個(gè)系數(shù)74）?，F(xiàn)在這個(gè)SQL命令執(zhí)行大概需要數(shù)秒鐘時(shí)間。
　　
　　為了避免tt.AssignedPC = et_1.EMPLOYID以及tt.ClientID = do.CUSTNMBR比較中的列長(zhǎng)度不匹配，我們可以進(jìn)行如下改動(dòng)：
　　
　　mysql > ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
　　MODIFY ClientID　VARCHAR(15);

　　現(xiàn)在EXPLAIN顯示的結(jié)果如下：
　　

以下為引用的內(nèi)容：

　　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

　
　　這個(gè)結(jié)果已經(jīng)比較令人滿意了。

　　余下的問(wèn)題在于，默認(rèn)情況下，MySQL假定tt.ActualPC列的值均勻分布，而事實(shí)上tt表的情況并非如此。幸而，我們可以很容易地讓MySQL知道這一點(diǎn)：
　　
　　shell > myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
　　shell > mysqladmin refresh
　　
　　現(xiàn)在這個(gè)連接操作已經(jīng)非常理想，EXPLAIN分析的結(jié)果如下：
　　

以下為引用的內(nèi)容：

　　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能夠恢復(fù)和整理磁盤(pán)空間以及數(shù)據(jù)碎片，一旦對(duì)包含變長(zhǎng)行的表進(jìn)行了大量的更新或者刪除，進(jìn)行這個(gè)操作就非常有必要了。OPTIMIZE當(dāng)前只能用于MyISAM和BDB表。
　　
　　結(jié)束語(yǔ)：
　　
　　從編譯數(shù)據(jù)庫(kù)服務(wù)器開(kāi)始、貫穿整個(gè)管理過(guò)程，能夠改善MySQL性能的因素實(shí)在非常多，本文只涉及了其中很小的一部分。

MYSQL

上一篇：MYSQL中怎樣設(shè)列的默認(rèn)值為Now()的介紹

下一篇：使用mysqlhotcopy備份MySQL