詳解MySQL性能優(yōu)化（二）

更新時(shí)間：2015年08月03日 17:03:56 投稿：lijiao

本文對(duì)MySQL性能優(yōu)化進(jìn)行了詳細(xì)的總結(jié)與介紹,需要的朋友可以參考下

接著上一篇學(xué)習(xí)：http://m.dhdzp.com/article/70528.htm

七、MySQL數(shù)據(jù)庫(kù)Schema設(shè)計(jì)的性能優(yōu)化
高效的模型設(shè)計(jì)

適度冗余-讓Query盡兩減少Join

大字段垂直分拆-summary表優(yōu)化

大表水平分拆-基于類(lèi)型的分拆優(yōu)化

統(tǒng)計(jì)表-準(zhǔn)實(shí)時(shí)優(yōu)化

合適的數(shù)據(jù)類(lèi)型

時(shí)間存儲(chǔ)格式總類(lèi)并不是太多，我們常用的主要就是DATETIME，DATE和TIMESTAMP這三種了。從存儲(chǔ)空間來(lái)看TIMESTAMP最少，四個(gè)字節(jié)，而其他兩種數(shù)據(jù)類(lèi)型都是八個(gè)字節(jié)，多了一倍。而TIMESTAMP的缺點(diǎn)在于他只能存儲(chǔ)從1970年之后的時(shí)間，而另外兩種時(shí)間類(lèi)型可以存放最早從1001年開(kāi)始的時(shí)間。如果有需要存放早于1970年之前的時(shí)間的需求，我們必須放棄TIMESTAMP類(lèi)型，但是只要我們不需要使用1970年之前的時(shí)間，最好盡量使用TIMESTAMP來(lái)減少存儲(chǔ)空間的占用。

字符存儲(chǔ)類(lèi)型

CHAR[(M)]類(lèi)型屬于靜態(tài)長(zhǎng)度類(lèi)型，存放長(zhǎng)度完全以字符數(shù)來(lái)計(jì)算，所以最終的存儲(chǔ)長(zhǎng)度是基于字符集的，如latin1則最大存儲(chǔ)長(zhǎng)度為255字節(jié)，但是如果使用gbk則最大存儲(chǔ)長(zhǎng)度為510字節(jié)。CHAR類(lèi)型的存儲(chǔ)特點(diǎn)是不管我們實(shí)際存放多長(zhǎng)數(shù)據(jù)，在數(shù)據(jù)庫(kù)中都會(huì)存放M個(gè)字符，不夠的通過(guò)空格補(bǔ)上，M默認(rèn)為1。雖然CHAR會(huì)通過(guò)空格補(bǔ)齊存放的空間，但是在訪問(wèn)數(shù)據(jù)的時(shí)候，MySQL會(huì)忽略最后的所有空格，所以如果我們的實(shí)際數(shù)據(jù)中如果在最后確實(shí)需要空格，則不能使用CHAR類(lèi)型來(lái)存放。

VARCHAR[(M)]屬于動(dòng)態(tài)存儲(chǔ)長(zhǎng)度類(lèi)型，僅存占用實(shí)際存儲(chǔ)數(shù)據(jù)的長(zhǎng)度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT這四種類(lèi)型同屬于一種存儲(chǔ)方式，都是動(dòng)態(tài)存儲(chǔ)長(zhǎng)度類(lèi)型，不同的僅僅是最大長(zhǎng)度的限制。

事務(wù)優(yōu)化

1. 臟讀：臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另外一個(gè)事務(wù)也訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。

2. 不可重復(fù)讀：是指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒(méi)有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問(wèn)該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱(chēng)為是不可重復(fù)讀。

3. 幻讀：是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒(méi)有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺(jué)一樣。

Innodb在事務(wù)隔離級(jí)別方面支持的信息如下：

1.READ UNCOMMITTED

常被成為Dirty Reads（臟讀），可以說(shuō)是事務(wù)上的最低隔離級(jí)別：在普通的非鎖定模式下SELECT的執(zhí)行使我們看到的數(shù)據(jù)可能并不是查詢發(fā)起時(shí)間點(diǎn)的數(shù)據(jù)，因而在這個(gè)隔離度下是非Consistent Reads（一致性讀）；

2.READ COMMITTED

這一隔離級(jí)別下，不會(huì)出現(xiàn)DirtyRead，但是可能出現(xiàn)Non-RepeatableReads(不可重復(fù)讀)和PhantomReads（幻讀）。

3. REPEATABLE READ

REPEATABLE READ隔離級(jí)別是InnoDB默認(rèn)的事務(wù)隔離級(jí)。在REPEATABLE READ隔離級(jí)別下，不會(huì)出現(xiàn)DirtyReads，也不會(huì)出現(xiàn)Non-Repeatable Read，但是仍然存在PhantomReads的可能性。

4.SERIALIZABLE

SERIALIZABLE隔離級(jí)別是標(biāo)準(zhǔn)事務(wù)隔離級(jí)別中的最高級(jí)別。設(shè)置為SERIALIZABLE隔離級(jí)別之后，在事務(wù)中的任何時(shí)候所看到的數(shù)據(jù)都是事務(wù)啟動(dòng)時(shí)刻的狀態(tài)，不論在這期間有沒(méi)有其他事務(wù)已經(jīng)修改了某些數(shù)據(jù)并提交。所以，SERIALIZABLE事務(wù)隔離級(jí)別下，PhantomReads也不會(huì)出現(xiàn)。

八、可擴(kuò)展性設(shè)計(jì)之?dāng)?shù)據(jù)切分

數(shù)據(jù)的垂直切分

數(shù)據(jù)的垂直切分，也可以稱(chēng)之為縱向切分。將數(shù)據(jù)庫(kù)想象成為由很多個(gè)一大塊一大塊的“數(shù)據(jù)塊”（表）組成，我們垂直的將這些“數(shù)據(jù)塊”切開(kāi)，然后將他們分散到多臺(tái)數(shù)據(jù)庫(kù)主機(jī)上面。這樣的切分方法就是一個(gè)垂直（縱向）的數(shù)據(jù)切分。

垂直切分的優(yōu)點(diǎn)

◆數(shù)據(jù)庫(kù)的拆分簡(jiǎn)單明了，拆分規(guī)則明確；

◆應(yīng)用程序模塊清晰明確，整合容易；

◆數(shù)據(jù)維護(hù)方便易行，容易定位；

垂直切分的缺點(diǎn)

◆部分表關(guān)聯(lián)無(wú)法在數(shù)據(jù)庫(kù)級(jí)別完成，需要在程序中完成；

◆對(duì)于訪問(wèn)極其頻繁且數(shù)據(jù)量超大的表仍然存在性能平靜，不一定能滿足要求；

◆事務(wù)處理相對(duì)更為復(fù)雜；

◆切分達(dá)到一定程度之后，擴(kuò)展性會(huì)遇到限制；

◆過(guò)讀切分可能會(huì)帶來(lái)系統(tǒng)過(guò)渡復(fù)雜而難以維護(hù)。

數(shù)據(jù)的水平切分

數(shù)據(jù)的垂直切分基本上可以簡(jiǎn)單的理解為按照表按照模塊來(lái)切分?jǐn)?shù)據(jù)，而水平切分就不再是按照表或者是功能模塊來(lái)切分了。一般來(lái)說(shuō)，簡(jiǎn)單的水平切分主要是將某個(gè)訪問(wèn)極其平凡的表再按照某個(gè)字段的某種規(guī)則來(lái)分散到多個(gè)表之中，每個(gè)表中包含一部分?jǐn)?shù)據(jù)。

水平切分的優(yōu)點(diǎn)

◆表關(guān)聯(lián)基本能夠在數(shù)據(jù)庫(kù)端全部完成；

◆不會(huì)存在某些超大型數(shù)據(jù)量和高負(fù)載的表遇到瓶頸的問(wèn)題；

◆應(yīng)用程序端整體架構(gòu)改動(dòng)相對(duì)較少；

◆事務(wù)處理相對(duì)簡(jiǎn)單；

◆只要切分規(guī)則能夠定義好，基本上較難遇到擴(kuò)展性限制；

水平切分的缺點(diǎn)

◆切分規(guī)則相對(duì)更為復(fù)雜，很難抽象出一個(gè)能夠滿足整個(gè)數(shù)據(jù)庫(kù)的切分規(guī)則；

◆后期數(shù)據(jù)的維護(hù)難度有所增加，人為手工定位數(shù)據(jù)更困難；

◆應(yīng)用系統(tǒng)各模塊耦合度較高，可能會(huì)對(duì)后面數(shù)據(jù)的遷移拆分造成一定的困難。

數(shù)據(jù)切分與整合中可能存在的問(wèn)題

1.引入分布式事務(wù)的問(wèn)題

完全可以將一個(gè)跨多個(gè)數(shù)據(jù)庫(kù)的分布式事務(wù)分拆成多個(gè)僅處于單個(gè)數(shù)據(jù)庫(kù)上面的小事務(wù)，并通過(guò)應(yīng)用程序來(lái)總控各個(gè)小事務(wù)。當(dāng)然，這樣作的要求就是我們的俄應(yīng)用程序必須要有足夠的健壯性，當(dāng)然也會(huì)給應(yīng)用程序帶來(lái)一些技術(shù)難度。

2.跨節(jié)點(diǎn)Join的問(wèn)題

推薦通過(guò)應(yīng)用程序來(lái)進(jìn)行處理，先在驅(qū)動(dòng)表所在的MySQLServer中取出相應(yīng)的驅(qū)動(dòng)結(jié)果集，然后根據(jù)驅(qū)動(dòng)結(jié)果集再到被驅(qū)動(dòng)表所在的MySQL Server中取出相應(yīng)的數(shù)據(jù)。

3.跨節(jié)點(diǎn)合并排序分頁(yè)問(wèn)題

從多個(gè)數(shù)據(jù)源并行的取數(shù)據(jù)，然后應(yīng)用程序匯總處理。

九、可擴(kuò)展性設(shè)計(jì)之Cache與Search的利用

通過(guò)引入Cache（Redis、Memcached），減少數(shù)據(jù)庫(kù)的訪問(wèn)，增加性能。

通過(guò)引入Search（Lucene、Solr、ElasticSearch），利用搜索引擎高效的全文索引和分詞算法，以及高效的數(shù)據(jù)檢索實(shí)現(xiàn)，來(lái)解決數(shù)據(jù)庫(kù)和傳統(tǒng)的Cache軟件完全無(wú)法解決的全文模糊搜索、分類(lèi)統(tǒng)計(jì)查詢等功能。