MySQL的性能調(diào)優(yōu)一

一、MySQL 與其他數(shù)據(jù)庫(kù)的簡(jiǎn)單比較

1.1性能比較

        性能方面，一直是MySQL 引以為自豪的一個(gè)特點(diǎn)。在權(quán)威的第三方評(píng)測(cè)機(jī)構(gòu)多次測(cè)試較量各種數(shù)據(jù)庫(kù)TPCC 值的過(guò)程中，MySQL 一直都有非常優(yōu)異的表現(xiàn)，而且在其他所有商用的通用數(shù)據(jù)庫(kù)管理系統(tǒng)中，僅僅只有Oracle 數(shù)據(jù)庫(kù)能夠與其一較高下。至于各種數(shù)據(jù)庫(kù)詳細(xì)的性能數(shù)據(jù)，我這里就不便記錄，大家完全可以通過(guò)網(wǎng)上第三方評(píng)測(cè)機(jī)構(gòu)公布的數(shù)據(jù)了解具體細(xì)節(jié)信息。

        MySQL 一直以來(lái)奉行一個(gè)原則，那就是在保證足夠的穩(wěn)定性的前提下，盡可能的提高自身的處理能力。也就是說(shuō)，在性能和功能方面，MySQL 第一考慮的要素主要還是性能，MySQL希望自己是一個(gè)在滿足客戶99%的功能需求的前提下，花掉剩下的大部分精力來(lái)性能努力，而不是希望自己是成為一個(gè)比其他任何數(shù)據(jù)庫(kù)的功能都要強(qiáng)大的數(shù)據(jù)庫(kù)產(chǎn)品。

1.2可靠性

        關(guān)于可靠性的比較，并沒(méi)有太多詳細(xì)的評(píng)測(cè)比較數(shù)據(jù)，但是從目前業(yè)界的交流中可以了解到，幾大商業(yè)廠商的數(shù)據(jù)庫(kù)的可靠性肯定是沒(méi)有太多值得懷疑的。但是做為開(kāi)源數(shù)據(jù)庫(kù)管理系統(tǒng)的代表，MySQL 也有非常優(yōu)異的表現(xiàn)，而并不是像有些人心中所懷疑的那樣，因?yàn)椴皇巧虡I(yè)廠商所提供，就會(huì)不夠穩(wěn)定不夠健壯。從當(dāng)前最火的Facebook 這樣大型的網(wǎng)站都是使用MySQL 數(shù)據(jù)庫(kù)，就可以看出，MySQL 在穩(wěn)定可靠性方面，并不會(huì)比我們的商業(yè)廠商的產(chǎn)品有太多遜色。而且排在全球前10 位的大型網(wǎng)站里面，大部分都有部分業(yè)務(wù)是運(yùn)行在MySQL數(shù)據(jù)庫(kù)環(huán)境上，如Yahoo，Google 等。

        總的來(lái)說(shuō)，MySQL 數(shù)據(jù)庫(kù)在發(fā)展過(guò)程中一直有自己的三個(gè)原則：簡(jiǎn)單、高效、可靠。從上面的簡(jiǎn)單比較中，我們也可以看出，在MySQL 自己的所有三個(gè)原則上面，沒(méi)有哪一項(xiàng)是做得不好的。而且，雖然功能并不是MySQL 自身所追求的三個(gè)原則之一，但是考慮到當(dāng)前用戶量的急劇增長(zhǎng)，用戶需求越來(lái)越越多樣化，MySQL 也不得不在功能方面做出大量的努力，來(lái)不斷滿足客戶的新需求。比如最近版本中出現(xiàn)的Eent Scheduler（類似于Oracle 的Job 功能），Partition 功能，自主研發(fā)的Maria 存儲(chǔ)引擎在功能方面的擴(kuò)展，F(xiàn)alcon 存儲(chǔ)引擎對(duì)事務(wù)的支持等等，都證明了MySQL 在功能方面也開(kāi)始了不懈的努力。

        任何一種產(chǎn)品，都不可能是完美的，也不可能適用于所有用戶。我們只有衡量了每一種產(chǎn)品的各種特性之后，從中選擇出一種最適合于自身的產(chǎn)品。

二、MySQL 的主要適用場(chǎng)景

        據(jù)說(shuō)目前MySQL 用戶已經(jīng)達(dá)千萬(wàn)級(jí)別了，其中不乏企業(yè)級(jí)用戶?？梢哉f(shuō)是目前最為流行的開(kāi)源數(shù)據(jù)庫(kù)管理系統(tǒng)軟件了。任何產(chǎn)品都不可能是萬(wàn)能的，也不可能適用于所有的應(yīng)用場(chǎng)景。

        那么MySQL 到底在什么場(chǎng)景下適用什么場(chǎng)景下不適用呢？

1、Web 網(wǎng)站系統(tǒng)

        Web 站點(diǎn)，是MySQL 最大的客戶群，也是MySQL 發(fā)展史上最為重要的支撐力量，這一點(diǎn)在最開(kāi)始的MySQL Server 簡(jiǎn)介部分就已經(jīng)說(shuō)明過(guò)。

        MySQL 之所以能成為Web 站點(diǎn)開(kāi)發(fā)者們最青睞的數(shù)據(jù)庫(kù)管理系統(tǒng)，是因?yàn)镸ySQL 數(shù)據(jù)庫(kù)的安裝配置都非常簡(jiǎn)單，使用過(guò)程中的維護(hù)也不像很多大型商業(yè)數(shù)據(jù)庫(kù)管理系統(tǒng)那么復(fù)雜，而且性能出色。還有一個(gè)非常重要的原因就是MySQL 是開(kāi)放源代碼的，完全可以免費(fèi)使用。

2、日志記錄系統(tǒng)

        MySQL 數(shù)據(jù)庫(kù)的插入和查詢性能都非常的高效，如果設(shè)計(jì)地較好，在使用MyISAM 存儲(chǔ)引擎的時(shí)候，兩者可以做到互不鎖定，達(dá)到很高的并發(fā)性能。所以，對(duì)需要大量的插入和查詢?nèi)罩居涗浀南到y(tǒng)來(lái)說(shuō)，MySQL 是非常不錯(cuò)的選擇。比如處理用戶的登錄日志，操作日志等，都是非常適合的應(yīng)用場(chǎng)景。

3、數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)

        隨著現(xiàn)在數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)量的飛速增長(zhǎng)，我們需要的存儲(chǔ)空間越來(lái)越大。數(shù)據(jù)量的不斷增長(zhǎng)，使數(shù)據(jù)的統(tǒng)計(jì)分析變得越來(lái)越低效，也越來(lái)越困難。怎么辦？這里有幾個(gè)主要的解決思路，一個(gè)是采用昂貴的高性能主機(jī)以提高計(jì)算性能，用高端存儲(chǔ)設(shè)備提高I/O 性能，效果理想，但是成本非常高；第二個(gè)就是通過(guò)將數(shù)據(jù)復(fù)制到多臺(tái)使用大容量硬盤的廉價(jià)pc server上，以提高整體計(jì)算性能和I/O 能力，效果尚可，存儲(chǔ)空間有一定限制，成本低廉；第三個(gè)，通過(guò)將數(shù)據(jù)水平拆分，使用多臺(tái)廉價(jià)的pc server 和本地磁盤來(lái)存放數(shù)據(jù)，每臺(tái)機(jī)器上面都只有所有數(shù)據(jù)的一部分，解決了數(shù)據(jù)量的問(wèn)題，所有pc server 一起并行計(jì)算，也解決了計(jì)算能力問(wèn)題，通過(guò)中間代理程序調(diào)配各臺(tái)機(jī)器的運(yùn)算任務(wù)，既可以解決計(jì)算性能問(wèn)題又可以解決I/O 性能問(wèn)題，成本也很低廉。在上面的三個(gè)方案中，第二和第三個(gè)的實(shí)現(xiàn)，MySQL 都有較大的優(yōu)勢(shì)。通過(guò)MySQL 的簡(jiǎn)單復(fù)制功能，可以很好的將數(shù)據(jù)從一臺(tái)主機(jī)復(fù)制到另外一臺(tái)，不僅僅在局域網(wǎng)內(nèi)可以復(fù)制，在廣域網(wǎng)同樣可以。當(dāng)然，很多人可能會(huì)說(shuō)，其他的數(shù)據(jù)庫(kù)同樣也可以做到，不是只有MySQL 有這樣的功能。確實(shí)，很多數(shù)據(jù)庫(kù)同樣能做到，但是MySQL是免費(fèi)的，其他數(shù)據(jù)庫(kù)大多都是按照主機(jī)數(shù)量或者cpu 數(shù)量來(lái)收費(fèi)，當(dāng)我們使用大量的pcserver 的時(shí)候，license 費(fèi)用相當(dāng)驚人。第一個(gè)方案，基本上所有數(shù)據(jù)庫(kù)系統(tǒng)都能夠?qū)崿F(xiàn)，但是其高昂的成本并不是每一個(gè)公司都能夠承擔(dān)的。

4、嵌入式系統(tǒng)

        嵌入式環(huán)境對(duì)軟件系統(tǒng)最大的限制是硬件資源非常有限，在嵌入式環(huán)境下運(yùn)行的軟件系統(tǒng)，必須是輕量級(jí)低消耗的軟件。MySQL 在資源的使用方面的伸縮性非常大，可以在資源非常充裕的環(huán)境下運(yùn)行，也可以在資源非常少的環(huán)境下正常運(yùn)行。它對(duì)于嵌入式環(huán)境來(lái)說(shuō)，是一種非常合適的數(shù)據(jù)庫(kù)系統(tǒng)，而且MySQL 有專門針對(duì)于嵌入式環(huán)境的版本。

三、Query 語(yǔ)句對(duì)系統(tǒng)性能的影響

        我想對(duì)于各位來(lái)說(shuō)，肯定都清楚SQL 語(yǔ)句的優(yōu)劣是對(duì)性能有影響的，但是到底有多大影響可能每個(gè)人都會(huì)有不同的體會(huì)，每個(gè)SQL 語(yǔ)句在優(yōu)化之前和優(yōu)化之后的性能差異也是各不相同，所以對(duì)于性能差異到底有多大這個(gè)問(wèn)題我們我們這里就不做詳細(xì)分析了。我們重點(diǎn)分析實(shí)現(xiàn)同樣功能的不同SQL 語(yǔ)句在性能方面會(huì)產(chǎn)生較大的差異的根本原因，并通過(guò)一個(gè)較為典型的示例來(lái)對(duì)我們的分析做出相應(yīng)的驗(yàn)證。

        為什么返回完全相同結(jié)果集的不同SQL 語(yǔ)句，在執(zhí)行性能方面存在差異呢？這里我們先從SQL 語(yǔ)句在數(shù)據(jù)庫(kù)中執(zhí)行并獲取所需數(shù)據(jù)這個(gè)過(guò)程來(lái)做一個(gè)大概的分析了。

當(dāng)MySQL Server 的連接線程接收到Client 端發(fā)送過(guò)來(lái)的SQL 請(qǐng)求之后，會(huì)經(jīng)過(guò)一系列的分解Parse，進(jìn)行相應(yīng)的分析。然后，MySQL 會(huì)通過(guò)查詢優(yōu)化器模塊（Optimizer）根據(jù)該SQL 所設(shè)涉及到的數(shù)據(jù)表的相關(guān)統(tǒng)計(jì)信息進(jìn)行計(jì)算分析，然后再得出一個(gè)MySQL 認(rèn)為最合理最優(yōu)化的數(shù)據(jù)訪問(wèn)方式，也就是我們常說(shuō)的“執(zhí)行計(jì)劃”，然后再根據(jù)所得到的執(zhí)行計(jì)劃通過(guò)調(diào)用存儲(chǔ)引擎借口來(lái)獲取相應(yīng)數(shù)據(jù)。然后再將存儲(chǔ)引擎返回的數(shù)據(jù)進(jìn)行相關(guān)處理，并以Client 端所要求的格式作為結(jié)果集返回給Client 端的應(yīng)用程序。

        注：這里所說(shuō)的統(tǒng)計(jì)數(shù)據(jù)，是我們通過(guò)ANALYZE TABLE 命令通知MySQL 對(duì)表的相關(guān)數(shù)據(jù)做分析之后所獲得到的一些數(shù)據(jù)統(tǒng)計(jì)量。這些統(tǒng)計(jì)數(shù)據(jù)對(duì)MySQL 優(yōu)化器而言是非常重要的，優(yōu)化器所生成的執(zhí)行計(jì)劃的好壞，主要就是由這些統(tǒng)計(jì)數(shù)據(jù)所決定的。實(shí)際上，在其他一些數(shù)據(jù)庫(kù)管理軟件中也有類似相應(yīng)的統(tǒng)計(jì)數(shù)據(jù)。

我們都知道，在數(shù)據(jù)庫(kù)管理軟件中，最大的性能瓶頸就是在于磁盤IO，也就是數(shù)據(jù)的存取操作上面。而對(duì)于同一份數(shù)據(jù)，當(dāng)我們以不同方式去尋找其中的某一點(diǎn)內(nèi)容的時(shí)候，所需要讀取的數(shù)據(jù)量可能會(huì)有天壤之別，所消耗的資源也自然是區(qū)別甚大。所以，當(dāng)我們需要從數(shù)據(jù)庫(kù)中查詢某個(gè)數(shù)據(jù)的時(shí)候，所消耗資源的多少主要就取決于數(shù)據(jù)庫(kù)以一個(gè)什么樣的數(shù)據(jù)讀取方式來(lái)完成我們的查詢請(qǐng)求，也就是取決于SQL 語(yǔ)句的執(zhí)行計(jì)劃。

        對(duì)于唯一一個(gè)SQL 語(yǔ)句來(lái)說(shuō)，經(jīng)過(guò)MySQL Parse 之后分解的結(jié)構(gòu)都是固定的，只要統(tǒng)計(jì)信息穩(wěn)定，其執(zhí)行計(jì)劃基本上都是比較固定的。而不同寫法的SQL 語(yǔ)句，經(jīng)過(guò)MySQL Parse 之后分解的結(jié)構(gòu)結(jié)構(gòu)就可能完全不同，即使優(yōu)化器使用完全一樣的統(tǒng)計(jì)信息來(lái)進(jìn)行優(yōu)化，最后所得出的執(zhí)行計(jì)劃也可能完全不一樣。而執(zhí)行計(jì)劃又是決定一個(gè)SQL 語(yǔ)句最終的資源消耗量的主要因素。所以，實(shí)現(xiàn)功能完全一樣的SQL 語(yǔ)句，在性能上面可能會(huì)有差別巨大的性能消耗。當(dāng)然，如果功能一樣，而且經(jīng)過(guò)MySQL 的優(yōu)化器優(yōu)化之后的執(zhí)行計(jì)劃也完全一致的不同SQL 語(yǔ)句在資源消耗方面可能就相差很小了。當(dāng)然這里所指的消耗主要是IO 資源的消耗，并不包括CPU 的消耗。

下面我們將通過(guò)一兩個(gè)具體的示例來(lái)分析寫法不一樣而功能完全相同的兩條SQL 的在性能方面的差異。

示例一

        需求：取出某個(gè)group（假設(shè)id 為100）下的用戶編號(hào)（id），用戶昵稱（nick_name）、用戶性別（ sexuality ） 、用戶簽名（ sign ） 和用戶生日（ birthday ） ， 并按照加入組的時(shí)間（user_group.gmt_create）來(lái)進(jìn)行倒序排列，取出前20 個(gè)。

解決方案一、


解決方案二、


我們先來(lái)看看執(zhí)行計(jì)劃：

        執(zhí)行計(jì)劃對(duì)比分析：

        解決方案一中的執(zhí)行計(jì)劃顯示MySQL 在對(duì)兩個(gè)參與Join 的表都利用到了索引，user_group 表利用了user_group_gid_ind 索引（ key: user_group_gid_ind ） ， user 表利用到了主鍵索引（ key:PRIMARY），在參與Join 前MySQL 通過(guò)Where 過(guò)濾后的結(jié)果集與user 表進(jìn)行Join，最后通過(guò)排序取出Join 后結(jié)果的“l(fā)imit 100,20”條結(jié)果返回。

        解決方案二的SQL 語(yǔ)句利用到了子查詢，所以執(zhí)行計(jì)劃會(huì)稍微復(fù)雜一些，首先可以看到兩個(gè)表都和解決方案1 一樣都利用到了索引（所使用的索引也完全一樣），執(zhí)行計(jì)劃顯示該子查詢以u(píng)ser_group 為驅(qū)動(dòng)，也就是先通過(guò)user_group 進(jìn)行過(guò)濾并馬上進(jìn)行這一論的結(jié)果集排序，也就取得了SQL 中的“l(fā)imit 100,20”條結(jié)果，然后與user 表進(jìn)行Join，得到相應(yīng)的數(shù)據(jù)。這里可能有人會(huì)懷疑在自查詢中從user_group表所取得與user 表參與Join的記錄條數(shù)并不是20 條，而是整個(gè)group_id=1 的所有結(jié)果。那么清大家看看該執(zhí)行計(jì)劃中的第一行，該行內(nèi)容就充分說(shuō)明了在外層查詢中的所有的20 條記錄全部被返回。 通過(guò)比較兩個(gè)解決方案的執(zhí)行計(jì)劃，我們可以看到第一中解決方案中需要和user 表參與Join 的記錄數(shù)MySQL 通過(guò)統(tǒng)計(jì)數(shù)據(jù)估算出來(lái)是31156，也就是通過(guò)user_group 表返回的所有滿足group_id=1 的記錄數(shù)（系統(tǒng)中的實(shí)際數(shù)據(jù)是20000）。而第二種解決方案的執(zhí)行計(jì)劃中，user 表參與Join 的數(shù)據(jù)就只有20條，兩者相差很大，通過(guò)本節(jié)最初的分析，我們認(rèn)為第二中解決方案應(yīng)該明顯優(yōu)于第一種解決方案。

        下面我們通過(guò)對(duì)比兩個(gè)解決覺(jué)方案的SQL 實(shí)際執(zhí)行的profile 詳細(xì)信息，來(lái)驗(yàn)證我們上面的判斷。由于SQL 語(yǔ)句執(zhí)行所消耗的最大兩部分資源就是IO和CPU，所以這里為了節(jié)約篇幅，僅列出BLOCK IO 和CPU兩項(xiàng)profile 信息：先打開(kāi)profiling 功能，然后分別執(zhí)行兩個(gè)解決方案的SQL 語(yǔ)句：



查看系統(tǒng)中的profile 信息，剛剛執(zhí)行的兩個(gè)SQL 語(yǔ)句的執(zhí)行profile 信息已經(jīng)記錄下來(lái)了：




        我們先看看兩條SQL 執(zhí)行中的IO 消耗，兩者區(qū)別就在于“Sorting result”，我們回顧一下前面執(zhí)行計(jì)劃的對(duì)比，兩個(gè)解決方案的排序過(guò)濾數(shù)據(jù)的時(shí)機(jī)不一樣，排序后需要取得的數(shù)據(jù)量一個(gè)是20000，一個(gè)是20，正好和這里的profile 信息吻合，第一種解決方案的“Sorting result”的IO 值是第二種解決方案的將近500 倍。然后再來(lái)看看CPU 消耗，所有消耗中，消耗最大的也是“Sorting result”這一項(xiàng)，第一個(gè)消耗多出的緣由和上面IO 消耗差異是一樣的。結(jié)論：通過(guò)上面兩條功能完全相同的SQL 語(yǔ)句的執(zhí)行計(jì)劃分析，以及通過(guò)實(shí)際執(zhí)行后的profile 數(shù)據(jù)的驗(yàn)證，都證明了第二種解決方案優(yōu)于第一種解決方案。同時(shí)通過(guò)后者的實(shí)際驗(yàn)證，也再次證明了我們前面所做的執(zhí)行計(jì)劃基本決定了SQL 語(yǔ)句性能。
 

首發(fā)：黑馬程序員javaEE培訓(xùn)學(xué)院
首發(fā)：http://java.itheima.com/