一右遭、MySQL簡介

MySQL一般特制完整的MySQLRDBMS践盼，是一個(gè)開源的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（Relational Database Management System）舔株，現(xiàn)在屬于Oracle公司茄靠。隨著MySQL功能的不斷完善嚷缭，性能不斷提高，又有開源免費(fèi)的優(yōu)勢看杭，越來越多的企業(yè)選擇使用MySQL忠藤，而放棄商用收費(fèi)的Oracle。

二楼雹、MySQL結(jié)構(gòu)

1模孩、邏輯結(jié)構(gòu)

MySQL采用的是客戶/服務(wù)器體系結(jié)構(gòu)，因此實(shí)際使用時(shí)贮缅，有兩個(gè)程序：?

1. 一個(gè)是MySQL服務(wù)器程序榨咐，指的是mysqlId程序，運(yùn)行在數(shù)據(jù)庫服務(wù)器上谴供，負(fù)責(zé)在網(wǎng)絡(luò)上監(jiān)聽并處理來自客戶端的服務(wù)請求根據(jù)這些請求去訪問數(shù)據(jù)庫的內(nèi)容块茁，再把有關(guān)信息回傳給客戶；

2. 另一個(gè)程序是MySQL客戶端程序憔鬼，負(fù)責(zé)連接到數(shù)據(jù)庫服務(wù)器龟劲，并通過發(fā)出命令來告知服務(wù)器它想要的操作。

從下面簡單的邏輯圖中可以看出轴或，MySQL內(nèi)部大致分為三層：?

1. 最上層是大部分基于網(wǎng)絡(luò)的C/S服務(wù)都有的部分昌跌，比如連接處理、授權(quán)認(rèn)證照雁、安全等蚕愤；

2. 第二層包括MySQL的很多核心服務(wù)功能，包括查詢解析饺蚊、分析萍诱、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如污呼，日期裕坊、時(shí)間、數(shù)學(xué)和加密函數(shù)）燕酷，所有的跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過程籍凝、觸發(fā)器、視圖等苗缩。

3. 第三層包含了存儲(chǔ)引擎饵蒂，存儲(chǔ)引擎負(fù)責(zé)MySQL中歐數(shù)據(jù)的存儲(chǔ)和提取，是數(shù)據(jù)庫中非常重要非常核心的部分酱讶，也是MySQL區(qū)別與其他數(shù)據(jù)庫的一個(gè)重要特性退盯。

不同的存儲(chǔ)引擎有個(gè)字的特點(diǎn)，MySQL支持插入式的存儲(chǔ)引擎泻肯，可以根據(jù)實(shí)際情況選擇最合適的存儲(chǔ)引擎渊迁。不過目前對于絕大部分應(yīng)用來說，MySQL默認(rèn)的存儲(chǔ)引擎InnoDB應(yīng)該就是其最佳選擇灶挟。我覺得剛開始學(xué)習(xí)的時(shí)候介紹很多不常用甚至不會(huì)用到的存儲(chǔ)引擎雖然可能會(huì)橫向比較宫纬，讓開發(fā)人員更了解每個(gè)存儲(chǔ)引擎的特性，但是這樣對開發(fā)人員造成的困擾更大膏萧，還不如專心學(xué)習(xí)一種用途最廣泛的存儲(chǔ)引擎漓骚，屏蔽其他干擾，學(xué)到一定深度后再了解其他存儲(chǔ)引擎的不同點(diǎn)榛泛。

2蝌蹂、體系結(jié)構(gòu)

具體查看MySQL在物理上的體系結(jié)構(gòu)，從上到下依次是：

1. 連接池組件

2. 管理服務(wù)和工具組件曹锨、SQL接口組件孤个、查詢分析器組件、優(yōu)化器組件沛简、緩沖組件

3. 插件式存儲(chǔ)引擎

4. 物理文件

三齐鲤、 數(shù)據(jù)庫和實(shí)例

區(qū)分兩個(gè)詞

數(shù)據(jù)庫指物理上的存儲(chǔ)文件斥废，實(shí)例是用來操作數(shù)據(jù)庫文件的。在MySQL數(shù)據(jù)庫中给郊，實(shí)例與數(shù)據(jù)庫通常是一一對應(yīng)的牡肉，這時(shí)兩個(gè)詞可以互換，但在集群情況下淆九，可能存在一個(gè)數(shù)據(jù)庫被對個(gè)數(shù)據(jù)實(shí)例使用的情況.

MySQL是單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫统锤，實(shí)例在系統(tǒng)上的表現(xiàn)就是一個(gè)進(jìn)程。

四炭庙、MySQL不得不說的八個(gè)陷阱

Mysql安裝簡單饲窿，速度較快，功能豐富焕蹄。另外它還是開源運(yùn)動(dòng)的標(biāo)桿逾雄，它的偉大成就向我們展示了一個(gè)成功的公司是可以建立在開源代碼之上的。

然而用過mysql的人都曾對著顯示器揮舞過拳頭腻脏。但你不可能發(fā)明一種每秒能保存成千上萬行互聯(lián)網(wǎng)數(shù)據(jù)嘲驾，并且一點(diǎn)錯(cuò)誤都沒有的技術(shù)吧。

以下列舉了8個(gè)開源關(guān)系型數(shù)據(jù)庫的缺陷迹卢，其中不僅限于MySQL辽故，還有是針對關(guān)系型數(shù)據(jù)庫的。只有明白了關(guān)系型數(shù)據(jù)庫和MySQL腐碱，才能更好地避免在使用MySQL中盡量少地遇到一些意外誊垢。

1、根深蒂固的bugs

任何大的軟件包都有 bug症见。但稍微深入了解一下喂走，就會(huì)發(fā)現(xiàn)和 Mysql 相關(guān)的 bugs 自成體系。突然你就需要留心谋作，因?yàn)?NULL 并不是以同樣的方式出現(xiàn)芋肠，外鍵約束也沒有像你想像的那樣執(zhí)行，連主鍵自動(dòng)增長也會(huì)出錯(cuò)遵蚜。

小問題大量存在帖池，而且并不總是可以修復(fù)，這就是為什么一些人保持一個(gè)列表吭净。還好 MySQL 維護(hù)著一個(gè)非常好的 bug 報(bào)告系統(tǒng)睡汹，讓我們可以知道我些我們無法想像的事情，知道其他人也在經(jīng)受同樣的磨難寂殉。

2囚巴、關(guān)系表的不靈活性

關(guān)系表具有條理性，條理性是好的——但是，它使得程序員不得不編造或硬塞一些數(shù)據(jù)到已經(jīng)定義好模式的列中彤叉。NoSQL開始越來越受到歡迎的原因之一庶柿，就是它為程序員提供了足夠的靈活性，來加速數(shù)據(jù)庫的使用秽浇。如果一個(gè)街道地址需要增加一行浮庐，那么，你可以將它很容易地插入到一個(gè)NoSQL文檔中兼呵。如果你想添加一個(gè)完整的新的數(shù)據(jù)塊，無論它包含什么內(nèi)容腊敲，文檔模型也可以原封不動(dòng)地接受你的數(shù)據(jù)击喂，而不必改為它要求的數(shù)據(jù)格式。

試想一下碰辅，你用整數(shù)格式建立了一個(gè)全部是郵編的表格懂昂。這個(gè)表是十分高效的，它執(zhí)行的規(guī)則也很好没宾。突然一次凌彬，有人上傳了一個(gè)使用了連字符的九位數(shù)郵編⊙ィ或者還有可能铲敛，你得到了一位來自加拿大客戶的信件，上面寫有郵政編碼会钝。

這時(shí)伐蒋，一切都亂了。老板要求網(wǎng)站要在幾小時(shí)內(nèi)恢復(fù)正常工作迁酸。然而先鱼，現(xiàn)在已經(jīng)沒有時(shí)間來重建數(shù)據(jù)庫。程序員可以做什么奸鬓？也許焙畔，可以使用黑客手段把加拿大郵政編碼由base64的數(shù)字格式改為base 10格式？或者設(shè)置一個(gè)使用轉(zhuǎn)義編碼的輔助表格串远，用來說明真正的郵政編碼或者其他宏多？誰知道呢？到處都有黑客澡罚，他們都是危險(xiǎn)的绷落。但你沒有時(shí)間來搞定它。

MySQL的關(guān)聯(lián)規(guī)則讓每個(gè)人都誠實(shí)和謹(jǐn)慎始苇，但它能強(qiáng)制我們避開易受攻擊和欺騙的麻煩砌烁。

3、存儲(chǔ)引擎混亂

總體來說，Mysql的存儲(chǔ)引擎接口定義還算良好的函喉。MySQL不是實(shí)際上的同一的數(shù)據(jù)庫避归。它是由幾個(gè)數(shù)據(jù)庫組成，它們的大多數(shù)細(xì)節(jié)都被統(tǒng)一的表面掩蓋了管呵。開始時(shí)有一個(gè)MyISAM引擎梳毙，它很快但在前后一致上不能做到完備。有時(shí)你需要速度并且可以接受不一致的結(jié)果時(shí)是很好的捐下。

當(dāng)人們需要更多時(shí)账锹，具備完整事務(wù)支持的Inno DB出現(xiàn)了。但這還不夠】澜螅現(xiàn)在奸柬，它可能有20種存儲(chǔ)引擎的選擇——這足以使一個(gè)數(shù)據(jù)庫管理員瘋狂。當(dāng)然婴程，有時(shí)在不同的存儲(chǔ)引擎之間切換而不必重寫你的SQL是很好的廓奕，但是切換后總會(huì)帶來混亂。這個(gè)表格我選擇的引擎是MyISAM還是innoDB呢档叔？或者桌粉，我決定輸出的數(shù)據(jù)是CSV格式的嗎？

4衙四、JOIN聯(lián)合查詢

曾經(jīng)铃肯，將數(shù)據(jù)分表保存是計(jì)算機(jī)科學(xué)史上的偉大創(chuàng)新。分開后的表不僅結(jié)構(gòu)簡單传蹈，使用上也簡化了許多缘薛。但它卻需要使用join語句來進(jìn)行查詢。

sql通過一系列join構(gòu)建的復(fù)雜查詢將開發(fā)者推入了困惑與絕望的深淵卡睦。而且存儲(chǔ)引擎也需要以最優(yōu)的方式來高效地解析join語句宴胧。開發(fā)者需要絞盡腦汁編寫查詢語句，然后數(shù)據(jù)庫對其進(jìn)行解析表锻。

這就是很多注重運(yùn)行速度的開發(fā)者放棄數(shù)據(jù)分表轉(zhuǎn)而使用不規(guī)范數(shù)據(jù)表的原因恕齐。不區(qū)分?jǐn)?shù)據(jù)實(shí)體，將所有數(shù)據(jù)保存到一個(gè)大表中——以避免復(fù)雜的查詢瞬逊。這樣確實(shí)很快显歧，并且服務(wù)器也不會(huì)耗盡內(nèi)存。

現(xiàn)在的磁盤空間很廉價(jià)确镊。8TB的磁盤已經(jīng)在售士骤，更大容量的也將上市。我們不再需要為使用join而絞盡腦汁了蕾域。

5拷肌、分支的混亂

毋庸置疑到旦，一個(gè)可靠的、得到良好支持的MySQL分支巨缘，可以帶來競爭和選擇添忘，但是它也引起困惑和混亂。更糟糕的是若锁，一個(gè)稱為MariaDB的ＭySQL分支搁骑，由Monty Widenius維護(hù)著。他同樣也在參與編寫MySQL又固。那么仲器，Maria DB是真正獨(dú)立的值得我們擁護(hù)的嗎？或者它是MySQL？我們是否應(yīng)該堅(jiān)持使用由創(chuàng)建原始mysql數(shù)據(jù)庫的組織運(yùn)營的核心代碼？或者我們應(yīng)該加入那些被認(rèn)為更聰明的戳晌，往往很酷的背叛者？

如何獲取關(guān)于兼容性的信息煤辨？雖然Maria DB和MySQL十分相似裳涛，但它們之間也有差異木张。這就是大家一直都在爭論它的原因。在性能方面端三，在我們查詢的范圍內(nèi)舷礼，在兩個(gè)陣營中，也許它們的工作方式相同郊闯，但也許不同妻献，也許將來會(huì)不同。

6团赁、開發(fā)MySQL的動(dòng)機(jī)

雖然MySQL是一款成功的開源產(chǎn)品育拨，但它仍屬于商業(yè)中的一款產(chǎn)品，專業(yè)開發(fā)者需要靠它來獲得利益欢摄，當(dāng)然熬丧，最直接的利益就是薪資。當(dāng)大多數(shù)用戶在持續(xù)地享受開源許可證帶來的最佳體驗(yàn)時(shí)怀挠，毫無疑問這家公司還在為賺取足夠的錢來維持運(yùn)營而努力析蝴。這導(dǎo)致自由代碼在“社區(qū)版”和出售給企業(yè)的完整產(chǎn)品之間產(chǎn)生了奇怪的分岐。

我們應(yīng)該對這款產(chǎn)品付錢嗎绿淋？這種在社區(qū)版開展經(jīng)營的行為是否公平闷畸？企業(yè)版中額外的功能是不是一個(gè)噱頭，以引誘我們不斷付費(fèi)的呢吞滞？這至少說明一點(diǎn)佑菩，它是另一些需要回答的問題：選用哪個(gè)版本？遵照哪種許可證？選用它的哪個(gè)功能集倘待？

7疮跑、原生JSON支持的缺乏

通過安裝MySQL查看其年齡，然后你就知道需要添加哪些驅(qū)動(dòng)程序使它變得可用凸舵。MySQL通常在3306端口上通信祖娘，一般輸出的是它本身難以理解的格式化數(shù)據(jù)。如果要讓你的代碼和它通信啊奄，你必須添加另一層代碼渐苏，將MySQL的語言轉(zhuǎn)換成有用的東西。這些層的代碼菇夸，以庫的形式分發(fā)琼富，經(jīng)常需要人們購買一個(gè)商業(yè)許可證。

現(xiàn)代數(shù)據(jù)存儲(chǔ)層通常直接以JSON通信庄新。雖然MySQL和Maria DB現(xiàn)在有能力解析SQL中的JSON部分鞠眉，但這還遠(yuǎn)遠(yuǎn)不夠，原生的JSON接口已經(jīng)被廣泛使用于CouchDB择诈、MongoDB械蹋，或任何最新的工具中。

8羞芍、封閉源和專有模塊的興起

雖然MySQL是開源的哗戈，但除了一些在”開源核心“周邊開發(fā)的一些較新的、非開源的代碼和專有模塊荷科。程序員也需要賺錢唯咬、需要生活，Oracle需要拿它的辛苦成果來換錢畏浆，這是一種現(xiàn)實(shí)胆胰，也是商業(yè)的性質(zhì)。使用MySQL你也不可以免費(fèi)得到任何東西刻获。

要求MySQL始終堅(jiān)持在一個(gè)很高的標(biāo)準(zhǔn)上蜀涨，這有點(diǎn)不公平，因?yàn)殚_源的成功可能是一個(gè)圈套将鸵。它開始可以免費(fèi)勉盅，但并不意味著它可以始終免費(fèi)。如果企業(yè)需要更多新的功能顶掉，他們就要通過各種方式付費(fèi)來獲取草娜。有時(shí)向Oracle付費(fèi)，比自己來編寫代碼要便宜得多痒筒。有時(shí)商業(yè)的宰闰、不開源的代碼是有意義的茬贵。

MySQL雖然作為一個(gè)成功的開源系統(tǒng)，但以上這些問題也總不可避免地出現(xiàn)移袍，這就需要我們在它們發(fā)生之前有個(gè)深刻的認(rèn)識解藻，才能在今后的應(yīng)用中避免不必要的麻煩。

在此我向大家推薦一個(gè)架構(gòu)學(xué)習(xí)交流群葡盗。交流學(xué)習(xí)群號：575745314 ?里面會(huì)分享一些資深架構(gòu)師錄制的視頻錄像：有Spring螟左，MyBatis，Netty源碼分析觅够，高并發(fā)胶背、高性能、分布式喘先、微服務(wù)架構(gòu)的原理钳吟，JVM性能優(yōu)化、分布式架構(gòu)等這些成為架構(gòu)師必備的知識體系窘拯。還能領(lǐng)取免費(fèi)的學(xué)習(xí)資源红且，目前受益良多

五、MySQL分布式集群搭建

1 準(zhǔn)備集群搭建環(huán)境

??? 使用6臺(tái)虛擬機(jī)來搭建MYSQL集群涤姊，相應(yīng)的實(shí)驗(yàn)環(huán)境與對應(yīng)的MYSQL節(jié)點(diǎn)之間的對應(yīng)關(guān)系如下圖所示：

管理節(jié)點(diǎn)（MGM）：這類節(jié)點(diǎn)的作用是管理MySQLCluster內(nèi)的其他節(jié)點(diǎn)暇番，如提供配置數(shù)據(jù)，并停止節(jié)點(diǎn)砂轻，運(yùn)行備份等奔誓。由于這類節(jié)點(diǎn)負(fù)責(zé)管理其他節(jié)點(diǎn)的配置斤吐，應(yīng)該在啟動(dòng)其他節(jié)點(diǎn)之前啟動(dòng)這類節(jié)點(diǎn)搔涝。MGM節(jié)點(diǎn)是用命令“ndb_mgmd”啟動(dòng)的；

數(shù)據(jù)節(jié)點(diǎn)（NDB）：這類節(jié)點(diǎn)用于保存Cluster的數(shù)據(jù)和措，數(shù)據(jù)節(jié)點(diǎn)的數(shù)目與副本的數(shù)目相關(guān)庄呈，是片段的倍數(shù)。例如派阱，對于兩個(gè)副本诬留，每個(gè)副本有兩個(gè)片段，那么就有4個(gè)數(shù)據(jù)節(jié)點(diǎn)贫母，沒有必要設(shè)定過多的副本文兑，在NDB中數(shù)據(jù)會(huì)盡量的保存在內(nèi)存中。數(shù)據(jù)節(jié)點(diǎn)使用命令“ndb”啟動(dòng)的腺劣；

SQL節(jié)點(diǎn)：這是用來訪問Cluster數(shù)據(jù)的節(jié)點(diǎn)绿贞，對于MySQL?Cluster，客戶端節(jié)點(diǎn)是使用NDB Cluster存儲(chǔ)引擎的傳統(tǒng)MySQL服務(wù)器橘原。通常籍铁，SQL節(jié)點(diǎn)使用命令“mysqld-ndbcluster”啟動(dòng)的涡上；

2、準(zhǔn)備安裝包

在官網(wǎng)上下載mysql的安裝包: mysql-cluster-gpl-7.4.11-Linux-glibc2.5-x86_64.tar.gz拒名，并進(jìn)行解壓吩愧。

3、集群搭建流程

1]將上述安裝包解壓出來的文件都移到/usr/local/mysql下增显；

2]運(yùn)行script目錄下的mysql-install-db.sh腳本雁佳，運(yùn)行命令為./mysql-install-db.sh ? --user=root--basedir =/usr/local/mysql --datadir=/usr/local/mysql；注意其中用戶為root的名稱需要跟配置文件my.cnf中的相同同云；

在管理節(jié)點(diǎn)甘穿，數(shù)據(jù)節(jié)點(diǎn)，SQL節(jié)點(diǎn)上都執(zhí)行上述安裝命令梢杭，從而完成對mysql的安裝温兼；

4、集群配置與啟動(dòng)

1]在管理節(jié)點(diǎn)上需要完成對于集群整體的配置配置：在/var/lib/mysql-cluster/config.ini中實(shí)現(xiàn)如下的配置信息：

2]在數(shù)據(jù)節(jié)點(diǎn)中需要在my.cnf中完成對于數(shù)據(jù)節(jié)點(diǎn)的相關(guān)配置信息武契，如下：

需要指明配置的數(shù)據(jù)節(jié)點(diǎn)的根目錄募判，數(shù)據(jù)目錄，socket連接配置咒唆，用戶配置届垫，以及對應(yīng)的管理節(jié)點(diǎn)的ip地址配置；將配置完成的配置文件移動(dòng)到/etc/my.cnf全释，完成装处；

3]在SQL節(jié)點(diǎn)上完成對于SQL節(jié)點(diǎn)的配置信息，同樣的是在my.cnf中完成相應(yīng)配置信息浸船，并將配置文件移動(dòng)到/etc/my.cnf中妄迁，相應(yīng)的配置信息的設(shè)定如下所示：

完成以上配置后，就可以啟動(dòng)集群中的各個(gè)節(jié)點(diǎn)了李命。

5登淘、集群啟動(dòng)

在啟動(dòng)mysql集群的時(shí)候，注意首先要啟動(dòng)管理節(jié)點(diǎn)封字，并依次啟動(dòng)其他等若干個(gè)節(jié)點(diǎn)黔州，相應(yīng)的啟動(dòng)步驟如下：

1]在管理節(jié)點(diǎn)上，切換到/usr/local/mysql/bin目錄下阔籽，執(zhí)行ndb_mgmd -f /var/lib/mysql-cluster/config.ini命令流妻，完成管理節(jié)點(diǎn)的啟動(dòng)；

2]在各個(gè)數(shù)據(jù)節(jié)點(diǎn)上笆制，切換到/usr/local/mysql/bin目錄下绅这，執(zhí)行ndbd --initial(第一次啟動(dòng)時(shí)，否則執(zhí)行ndbd即可)项贺，完成對數(shù)據(jù)節(jié)點(diǎn)的啟動(dòng)君躺；

3]在各個(gè)SQL節(jié)點(diǎn)上峭判，同樣切換到/usr/local/mysql/bin目錄下，執(zhí)行mysqld_safe --user=root完成啟動(dòng)棕叫；

4]在管理節(jié)點(diǎn)上運(yùn)行ndb_mgm命令林螃，進(jìn)入數(shù)據(jù)庫管理的客戶端，輸入show命令俺泣，查看與之相連接的各個(gè)節(jié)點(diǎn)的狀態(tài)疗认；

5]在SQL節(jié)點(diǎn)上分別進(jìn)入系統(tǒng)的安全狀態(tài)，并完成對root用戶的密碼修改伏钠，運(yùn)行以下指令横漏，進(jìn)行密碼修改：

A use mysql，切換到mysql數(shù)據(jù)庫熟掂；

B UPDATE user SET Password = PASSWORD('123456')WHERE user = 'root';從而實(shí)現(xiàn)對root密碼的修改缎浇；

C flush privilege，完成修改赴肚；

6]修改使得任意主機(jī)都能連得上mysql素跺，進(jìn)行如下修改，同樣安裝第5步進(jìn)入安全模式誉券，并完成相應(yīng)的修改指厌，如下：

grant all on‘*.*’to ‘root@'%' identified by '123456';

這樣就可以使得任意一個(gè)主機(jī)都可以通過root用戶來登錄mysql了；

6踊跟、集群測試

在集群上的一個(gè)SQL節(jié)點(diǎn)上執(zhí)行創(chuàng)建數(shù)據(jù)庫踩验，并創(chuàng)建一張表，并完成相應(yīng)的數(shù)據(jù)插入商玫，如下：

A create database ctest箕憾； ? ? ? ? ? ? ? ? ? ? ? ? ?//創(chuàng)建數(shù)據(jù)庫

B create table test（

id int primarykey； ? ? ? ? ? ? ? ? ?//創(chuàng)建一張表

）决帖；

C insert into test (id)values(1); ? ? ? ? ? //完成數(shù)據(jù)插入

登錄另外一個(gè)SQL節(jié)點(diǎn)厕九，并執(zhí)行SQL查詢操作蓖捶，看數(shù)據(jù)庫中是否已經(jīng)有數(shù)據(jù)地回，如下：

select * from ctest;

如果有數(shù)據(jù)，表示數(shù)據(jù)插入成功俊鱼；

7刻像、關(guān)閉集群

1]首先關(guān)閉管理節(jié)點(diǎn)和數(shù)據(jù)節(jié)點(diǎn)，需要在管理節(jié)點(diǎn)上執(zhí)行命令并闲，如下：./ndb_mgm -e shutdown;

2]然后關(guān)閉SQL節(jié)點(diǎn)细睡，在SQL節(jié)點(diǎn)上執(zhí)行命令/usr/local/mysql/support-fies/mysql.server stop(其中/usr/local/mysql/是mysql的安裝目錄).從而關(guān)閉SQL節(jié)點(diǎn)

在此我向大家推薦一個(gè)架構(gòu)學(xué)習(xí)交流群。交流學(xué)習(xí)群號：575745314 ?里面會(huì)分享一些資深架構(gòu)師錄制的視頻錄像：有Spring帝火，MyBatis溜徙，Netty源碼分析湃缎，高并發(fā)、高性能蠢壹、分布式嗓违、微服務(wù)架構(gòu)的原理，JVM性能優(yōu)化图贸、分布式架構(gòu)等這些成為架構(gòu)師必備的知識體系蹂季。還能領(lǐng)取免費(fèi)的學(xué)習(xí)資源，目前受益良多

六疏日、MySQL 的最佳實(shí)踐

數(shù)據(jù)庫操作是當(dāng)今 Web 應(yīng)用程序中的主要瓶頸偿洁。 不僅是 DBA（數(shù)據(jù)庫管理員）需要為各種性能問題操心，程序員為做出準(zhǔn)確的結(jié)構(gòu)化表沟优，優(yōu)化查詢性能和編寫更優(yōu)代碼涕滋，也要費(fèi)盡心思。 在本文中挠阁，我列出了一些針對程序員的 MySQL 優(yōu)化技術(shù)何吝。

在我們開始學(xué)習(xí)之前，我補(bǔ)充一點(diǎn)：你可以在 Envato Market 上找到大量的 MySQL 腳本和實(shí)用程序鹃唯。

1.優(yōu)化查詢的查詢緩存

大部分MySQL服務(wù)器都有查詢緩存功能爱榕。這是提高性能的最有效的方法之一，這是由數(shù)據(jù)庫引擎私下處理的坡慌。當(dāng)同一個(gè)查詢被多次執(zhí)行黔酥，結(jié)果會(huì)直接從緩存里提取，這樣速度就很快洪橘。

主要的問題是跪者，這對程序員來說太簡單了，不容易看到熄求，我們很多人都容易忽略渣玲。我們實(shí)際上是可以組織查詢緩存執(zhí)行任務(wù)的。

查詢緩存在第一行不執(zhí)行的原因在于CURDTE()功能的使用弟晚。這適用于所有的非確定性功能忘衍，就像NOW()和RAND()等等。卿城。枚钓。因?yàn)楣δ芊祷氐慕Y(jié)果是可變的。MySQL決定禁用查詢器的查詢緩存瑟押。我們所需要做的是通過添加一額外一行PHP搀捷，在查詢前阻止它發(fā)生。

2. EXPLAIN你的選擇查詢

使用EXPLAIN關(guān)鍵詞可以幫助了解MySQL是怎樣運(yùn)行你的查詢的多望。這有助于發(fā)現(xiàn)瓶頸和查詢或表結(jié)構(gòu)的其它問題嫩舟。

EXPLAIN的查詢結(jié)果會(huì)展示哪一個(gè)索引被使用過氢烘，表示怎樣掃描和儲(chǔ)存的，等等家厌。威始。。

選擇一個(gè)SELECT查詢（一個(gè)有連接的復(fù)雜查詢會(huì)更好）像街，在它的前面添加關(guān)鍵詞EXPLAIN黎棠，這樣就可以直接使用數(shù)據(jù)庫了。結(jié)果會(huì)以一個(gè)漂亮的表來展示镰绎。例如脓斩，就好比我執(zhí)行連接時(shí)忘了添加一欄的索引：?

現(xiàn)在它只會(huì)從表2里面掃描9和16行，而非掃描7883行畴栖。經(jīng)驗(yàn)法則是乘以所有“行”那一欄的數(shù)字随静，你的查詢性能會(huì)跟結(jié)果數(shù)字成比例的。

3. 獲取唯一行時(shí)使用LIMIT 1

有時(shí)當(dāng)你查表時(shí)吗讶，你已經(jīng)知道你正在查找的結(jié)果只有一行燎猛。你可能正在獲取唯一記錄，或者你可能只是查詢是否存在滿足你的WHERE子句條件的記錄照皆。

在這種情況下重绷，將LIMIT 1添加到查詢條件中可以提高性能。這樣膜毁，數(shù)據(jù)庫引擎將在找到剛剛第一個(gè)記錄之后停止掃描記錄昭卓，而不是遍歷整個(gè)表或索引。

4. 索引搜索字段

索引不僅僅是為了主鍵或唯一鍵瘟滨。如果你會(huì)在你的表中按照任何列搜索候醒，你就都應(yīng)該索引它們。

正如你所看到的杂瘸，這個(gè)規(guī)則也適用于如 “l(fā)ast_name LIKE ‘a(chǎn)%’”的部分字符串搜索倒淫。當(dāng)從字符串的開頭搜索時(shí)，MySQL就可以使用那一列的索引败玉。

你也應(yīng)該明白什么樣搜索可以不使用有規(guī)律的索引敌土。例如，當(dāng)搜索一個(gè)單詞時(shí)（例如绒怨，”WHERE post_content LIKE ‘%apple%’”）纯赎，你將不會(huì)看到普通索引的好處。你最好使用 mysql 全文搜索或者構(gòu)建你自己的索引解決方案南蹂。

5. 索引并對連接使用同樣的字段類型

如果你的應(yīng)用程序包含許多連接查詢, 你需要確保連接的字段在兩張表上都建立了索引。 這會(huì)影響MySQL如何內(nèi)部優(yōu)化連接操作念恍。

此外,被連接的字段,需要使用同樣類型六剥。例如, 如果你使用一個(gè)DECIMAL字段, 連接另一張表的INT字段, MySQL將無法使用至少一個(gè)索引晚顷。 即使字符編碼也需要使用相同的字符類型。

6. 不要ORDER BY RAND()

起初這是一個(gè)聽起來挺酷的技巧, 讓許多菜鳥程序員陷入了這個(gè)陷阱疗疟。但你可能不知道该默，一旦你開始在查詢中使用它，你創(chuàng)建了非巢咄可怕的查詢瓶頸栓袖。

如果你真的需要對結(jié)果隨機(jī)排序, 這有一個(gè)更好的方法。補(bǔ)充一些額外代碼,你將可以防止當(dāng)數(shù)據(jù)成指數(shù)級增長時(shí)造成的瓶頸店诗。關(guān)鍵問題是裹刮，MySQL必須在排序之前對表中的每一行執(zhí)行RAND()操作（這需要處理能力），并且僅僅給出一行庞瘸。

所以挑選一個(gè)小于結(jié)果數(shù)的隨機(jī)數(shù)捧弃，并將其用作LIMIT子句中的偏移量。

7. 避免使用SELECT *

從數(shù)據(jù)表中讀取的數(shù)據(jù)越多擦囊，查詢操作速度就越慢违霞。它增加了磁盤操作所需的時(shí)間。此外瞬场，當(dāng)數(shù)據(jù)庫服務(wù)器與Web服務(wù)器分開時(shí)买鸽，由于必須在服務(wù)器之間傳輸數(shù)據(jù)，將會(huì)有更長的網(wǎng)絡(luò)延遲贯被。

這是一個(gè)好習(xí)慣：當(dāng)你使用SELECT語句時(shí)總是指定你需要的列癞谒。

8. 幾乎總是有一個(gè)id字段

在每個(gè)以id列為PRIMARY KEY的數(shù)據(jù)表中，優(yōu)先選擇AUTO_INCREMENT或者INT刃榨。 也可以優(yōu)選使用UNSIGNED弹砚，因?yàn)樵撝挡荒転樨?fù)的。

即使你擁有一個(gè)具有唯一用戶名字段的用戶表枢希，也不要將其作為主鍵桌吃。 VARCHAR字段作為主鍵（檢索）速度較慢。通過內(nèi)部ID引用所有的用戶數(shù)據(jù)苞轿，你的代碼中將更加結(jié)構(gòu)化茅诱。

有些后臺(tái)操作是由MySQL引擎本身完成的，它在內(nèi)部使用主鍵字段搬卒。當(dāng)數(shù)據(jù)庫設(shè)置越復(fù)雜（集群瑟俭，分區(qū)等…），這就變得更加重要了契邀。

這個(gè)規(guī)則的一個(gè)可能的例外是“關(guān)聯(lián)表”摆寄，用于兩個(gè)表之間的多對多類型的關(guān)聯(lián)。例如，“posts_tags”表中包含兩列：post_id微饥，tag_id逗扒，用于保存表名為“post”和“tags”的兩個(gè)表之間的關(guān)系。這些表可以具有包含兩個(gè)id字段的PRIMARY鍵欠橘。

9. 相比VARCHAR優(yōu)先使用ENUM

ENUM枚舉類型是非尘丶纾快速和緊湊的。在內(nèi)部它們像TINYINT一樣存儲(chǔ)肃续，但它們可以包含和顯示字符串值黍檩。這使他們成為某些領(lǐng)域的完美候選。

如果有一個(gè)字段只包含幾種不同的值始锚，請使用ENUM而不是VARCHAR刽酱。例如，它可以是名為“status”的列疼蛾，并且只包含諸如“active”肛跌，“inactive”，“pending”察郁，“expired”等的值…

關(guān)于如何重構(gòu)你的數(shù)據(jù)表衍慎，甚至有一種方法是可以從MySQL本身得到“建議”。 當(dāng)你有一個(gè)VARCHAR字段皮钠，它實(shí)際上建議你將該列類型更改為ENUM稳捆。這通過調(diào)用PROCEDURE ANALYZE()來完成。

在此我向大家推薦一個(gè)架構(gòu)學(xué)習(xí)交流群麦轰。交流學(xué)習(xí)群號：575745314 ?里面會(huì)分享一些資深架構(gòu)師錄制的視頻錄像：有Spring乔夯，MyBatis，Netty源碼分析款侵，高并發(fā)末荐、高性能、分布式新锈、微服務(wù)架構(gòu)的原理甲脏，JVM性能優(yōu)化妹笆、分布式架構(gòu)等這些成為架構(gòu)師必備的知識體系拳缠。還能領(lǐng)取免費(fèi)的學(xué)習(xí)資源墩新，目前受益良多

10. 使用PROCEDURE ANALYSE()獲取建議

PROCEDURE ANALYSE() 將使用MySQL分析列結(jié)構(gòu)和表中的實(shí)際數(shù)據(jù)，為你提供一些建議海渊。它只有在數(shù)據(jù)表中有實(shí)際數(shù)據(jù)時(shí)才有用，因?yàn)檫@在分析決策時(shí)很重要切省。

例如最岗，如果你創(chuàng)建了一個(gè)INT類型的主鍵朝捆，但沒有太多行懒豹，MySQL則可能建議您改用MEDIUMINT脸秽。或者如果你使用VARCHAR字段驮樊，如果表里只有很少的取值片酝，你可能會(huì)得到一個(gè)建議是將其轉(zhuǎn)換為ENUM雕沿。

你也可以在其中一個(gè)表視圖中單擊phpmyadmin中的“建議表結(jié)構(gòu)”鏈接來執(zhí)行此操作审轮。

請記住疾渣，這些只是建議。 如果你的數(shù)據(jù)表變得越來越大杈女，他們甚至可能不是正確的建議薄疚。至于如何修改最終是你來決定。

11. 如果可以的話使用NOT NULL

除非你有非常重要的理由使用NULL值砰碴，否則你應(yīng)該設(shè)置你的列為NOT NULL呈枉。

首先猖辫，問一下你自己在空字符串值和NULL值之間（對應(yīng)INT字段：0 vs. NULL）是否有任何的不同.如果沒有理由一起使用這兩個(gè)，那么你就不需要一個(gè)NULL字段（你知道在Oracle中NULL和空字符串是一樣的嗎芝囤？）悯姊。

NULL列需要額外的空間贩毕，他們增加了你的比較語句的復(fù)雜度辉阶。如果可以的話盡量避免它們谆甜。當(dāng)然，我理解一些人冈在，他們也許有非常重要的理由使用NULL值包券，這不總是一件壞事溅固。

摘自MySQL 文檔：

“行空列需要額外的空間來記錄它們的值是否為空兰珍。 對于MyISAM表,每個(gè)NULL列需要一點(diǎn)額外的,圍捕到最近的字節(jié)”掠河。

12. 預(yù)處理語句

使用預(yù)處理語句有諸多好處唠摹，包括更高的性能和更好的安全性。

預(yù)處理語句默認(rèn)情況下會(huì)過濾綁定到它的變量煮甥，這對于避免SQL注入攻擊極為有效成肘。當(dāng)然你也可以指定要過濾的變量双霍。但這些方法更容易出現(xiàn)人為錯(cuò)誤，也更容易被程序員遺忘蟹演。這在使用框架或 ORM 的時(shí)候會(huì)出現(xiàn)一些問題。

既然我們關(guān)注性能鸣个，那就應(yīng)該說說這個(gè)方面的好處囤萤。當(dāng)在應(yīng)用中多次使用同一個(gè)查詢的時(shí)候是趴，它的好處特別明顯唆途。既然向同一個(gè)預(yù)備好的語句中傳入不同的參數(shù)值肛搬，MySQL 對這個(gè)語句也只會(huì)進(jìn)行一次解析。

同時(shí)蛤奢，最新版本的 MySQL 在傳輸預(yù)備好的語句時(shí)會(huì)采用二進(jìn)制形式啤贩，這樣做的作用非常明顯痹屹，而且對減少網(wǎng)絡(luò)延遲很有幫助腹纳。

曾經(jīng)有一段時(shí)間，許多程序員為了一個(gè)重要的原因則避免使用預(yù)處理語句雄驹。這個(gè)原因就是医舆，它們不會(huì)被MySQL 緩存蔬将。不過在 5.1 版本的某個(gè)時(shí)候央星，查詢緩存也得到的支持莉给。

想在 PHP 中使用預(yù)處理語句颓遏，你可以看看 mysqli 擴(kuò)展 或使用數(shù)據(jù)抽象層，如 PDO滤灯。

13. 無緩沖查詢

通常當(dāng)你從腳本執(zhí)行一個(gè)查詢，在它可以繼續(xù)后面的任務(wù)之前將需要等待查詢執(zhí)行完成演训。你可以使用無緩沖的查詢來改變這一情況样悟。

在PHP 文檔中對 mysql_unbuffered_query() f函數(shù)有一個(gè)很好的解釋：

“mysql_unbuffered_query()發(fā)送SQL查詢查詢MySQL沒有自動(dòng)抓取和緩沖結(jié)果行mysql_query()窟她。 這節(jié)省了大量的內(nèi)存生產(chǎn)大型結(jié)果集的SQL查詢,你可以在結(jié)果集后立即開始工作第一行被檢索到的是你不必等到完整的SQL查詢已經(jīng)完成震糖〉跛担”

然而优炬，它有一定的局限性蠢护。你必須在執(zhí)行另一個(gè)查詢之前讀取所有的行或調(diào)用mysql_free_result() 葵硕。另外你不能在結(jié)果集上使用mysql_num_rows() 或 mysql_data_seek() 懈凹。

14. 使用 UNSIGNED INT 存儲(chǔ)IP地址

很多程序員沒有意識到可以使用整數(shù)類型的字段來存儲(chǔ) IP 地址介评，所以一直使用 VARCHAR(15) 類型的字段尊沸。使用 INT 只需要 4 個(gè)字節(jié)的空間洼专，而且字段長度固定屁商。

必須確保列是 UNSINGED INT 類型蜡镶，因?yàn)?IP 地址可能會(huì)用到 32 位無符號整型數(shù)據(jù)的每一個(gè)位官还。

在查詢中可以使用 INET_ATON() 來把一個(gè)IP轉(zhuǎn)換為整數(shù)望伦，用 INET_NTOA() 來進(jìn)行相反的操作煎殷。在 PHP 也有類似的函數(shù)豪直，ip2long() 和 long2ip()弓乙。

15. 固定長度（靜態(tài)）的表會(huì)更快

（譯者注：這里提到的表的長度，實(shí)際是指表頭的長度垢乙，即表中每條數(shù)據(jù)占用的空間大小追逮，而不是指表的數(shù)據(jù)量）

如果表中所有列都是“固定長度”钮孵，那么這個(gè)表被認(rèn)為是“靜態(tài)”或“固定長度”的巴席。不固定的列類型包括 VARCHAR漾唉、TEXT赵刑、BLOB等场刑。即使表中只包含一個(gè)這些類型的列牵现，這個(gè)表就不再是固定長度的瞎疼，MySQL 引擎會(huì)以不同的方式來處理它贼急。

固定長度的表會(huì)提高性能竿裂，因?yàn)?MySQL 引擎在記錄中檢索的時(shí)候速度會(huì)更快腻异。如果想讀取表中的某一地，它可以直接計(jì)算出這一行的位置影斑。如果行的大小不固定矫户，那就需要在主鍵中進(jìn)行檢索皆辽。

它們也易于緩存驱闷，崩潰后容易重建空另。不過它們也會(huì)占用更多空間扼菠。例如循榆，如果你把一個(gè) VARCHAR(20) 的字符改為 CHAR(20) 類型冯痢，它會(huì)總是占用 20 個(gè)字節(jié)，不管里面存的是什么內(nèi)容瘩将。

你可以使用“垂直分區(qū)”技術(shù)苇侵，將長度變化的列拆分到另一張表中。來看看：

16. 垂直分區(qū)

垂直分區(qū)是為了優(yōu)化表結(jié)構(gòu)而對其進(jìn)行縱向拆分的行為弱贼。

示例 1: 你可能會(huì)有一張用戶表吮旅，包含家庭住址庇勃，而這個(gè)不是一個(gè)常用數(shù)據(jù)责嚷。這時(shí)候你可以選擇把表拆分開罕拂，將住址信息保存到另一個(gè)表中爆班。這樣你的主用戶表就會(huì)更小蛋济。如你所知碗旅，表越小越快祟辟。

示例 2: 表中有一個(gè) “l(fā)ast_login” 字段旧困，用戶每次登錄網(wǎng)站都會(huì)更新這個(gè)字段吼具，而每次更新都會(huì)導(dǎo)致這個(gè)表緩存的查詢數(shù)據(jù)被清空拗盒。這種情況下你可以將那個(gè)字段放到另一張表里陡蝇，保持用戶表更新量最小登夫。

不過你也需要確保不會(huì)經(jīng)常聯(lián)合查詢分開后的兩張表恼策，要不然你就得忍受由這帶來的性能下降。

17. 拆分大型DELETE或INSERT語句

如果你需要在網(wǎng)站上執(zhí)行大型DELETE或INSERT查詢鲁纠，則需要注意不要影響網(wǎng)絡(luò)流量改含。當(dāng)執(zhí)行大型語句時(shí)捍壤，它會(huì)鎖表并使你的Web應(yīng)用程序停止鹃觉。

Apache運(yùn)行許多并行進(jìn)程/線程盗扇。 因此它執(zhí)行腳本效率很高疗隶。所以服務(wù)器不期望打開過多的連接和進(jìn)程斑鼻，這很消耗資源坚弱，特別是內(nèi)存输虱。

如果你鎖表很長時(shí)間（如30秒或更長），在一個(gè)高流量的網(wǎng)站，會(huì)導(dǎo)致進(jìn)程和查詢堆積命贴，處理這些進(jìn)程和查詢可能需要很長時(shí)間污茵，最終甚至使你的網(wǎng)站崩潰泞当。

如果你的維護(hù)腳本需要?jiǎng)h除大量的行，只需使用LIMIT子句陋桂，以避免阻塞。

18.越少的列越快

對于數(shù)據(jù)庫引擎，磁盤可能是最重要的瓶頸。更小更緊湊的數(shù)據(jù)巷查、減少磁盤傳輸量，通常有助于性能提高崇败。

MySQL文檔Storage Requirements 有所有數(shù)據(jù)類型清單后室。

如果已知表具有很少的行，則沒有理由是主鍵類型為INT将饺，可以用MEDIUMINT、SMALLINT代替刮吧，甚至在某些情況下使用TINYINT湖饱。 如果不需要完整時(shí)間記錄，請使用DATE而不是DATETIME杀捻。

確保留下合理的擴(kuò)展空間井厌，不然你可能會(huì)像Slashdot這樣。

19. 選擇正確的存儲(chǔ)引擎

MySQL有兩個(gè)主要存儲(chǔ)引擎水醋，MyISAM和InnoDB旗笔。 每個(gè)都有自己的優(yōu)點(diǎn)和缺點(diǎn)拄踪。

MyISAM適用于讀取繁重的應(yīng)用程序蝇恶，但是當(dāng)有很多寫入時(shí)它不能很好地?cái)U(kuò)展。 即使你正在更新一行的一個(gè)字段惶桐，整個(gè)表也被鎖定撮弧，并且在語句執(zhí)行完成之前，其他進(jìn)程甚至無法讀取該字段姚糊。 MyISAM在計(jì)算SELECT COUNT（*）的查詢時(shí)非郴哐埽快。

InnoDB是一個(gè)更復(fù)雜的存儲(chǔ)引擎救恨，對于大多數(shù)小的應(yīng)用程序贸辈，它比MyISAM慢。 但它支持基于行的鎖定肠槽，使其更好地?cái)U(kuò)展擎淤。 它還支持一些更高級的功能，比如事務(wù)秸仙。

● MyISAM存儲(chǔ)引擎

● InnoDB存儲(chǔ)引擎

20. 使用對象關(guān)系映射器（ORM, Object Relational Mapper）

通過使用ORM（對象關(guān)系映射器）嘴拢，你可以獲得一定的性能提升寂纪。ORM可以完成的一切事情，手動(dòng)編碼也可完成孝冒。但這可能意味著需要太多額外的工作迈倍，并且需要高水平的專業(yè)知識捣域。

ORM以“延遲加載”著稱。這意味著它們僅在需要時(shí)獲取實(shí)際值迹鹅。但是你需要小心處理他們贞言，否則你可能最終創(chuàng)建了許多微型查詢，這會(huì)降低數(shù)據(jù)庫性能弟蚀。

ORM還可以將多個(gè)查詢批處理到事務(wù)中酗失，其操作速度比向數(shù)據(jù)庫發(fā)送單個(gè)查詢快得多。

目前我最喜歡的PHP-ORM是Doctrine捶闸。我寫了一篇關(guān)于如何安裝Doctrine與CodeIgniter的文章（install Doctrine with CodeIgniter）拖刃。

21. 小心使用持久連接

持久連接意味著減少重建連接到MySQL的成本兑牡。 當(dāng)持久連接被創(chuàng)建時(shí)，它將保持打開狀態(tài)直到腳本完成運(yùn)行亿虽。 因?yàn)锳pache重用它的子進(jìn)程边酒，下一次進(jìn)程運(yùn)行一個(gè)新的腳本時(shí)，它將重用相同的MySQL連接坯认。

理論上看起來不錯(cuò)牛哺。 但從我個(gè)人（和許多其他人）的經(jīng)驗(yàn)看來劳吠，這個(gè)功能可能會(huì)導(dǎo)致更多麻煩。 你可能會(huì)出現(xiàn)連接數(shù)限制問題淳附、內(nèi)存問題等等。

Apache總是并行運(yùn)行的别凹，它創(chuàng)建許多子進(jìn)程洽糟。 這是持久連接在這種環(huán)境中不能很好工作的主要原因。 在你考慮使用mysql_pconnect()之前拍霜，請咨詢你的系統(tǒng)管理員薪介。