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

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

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

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

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

二、MySQL架構(gòu)圖：

三、MySQL基礎(chǔ)知識(shí)拓展 —>sql語(yǔ)句相關(guān)-降落傘—>…

四、MySQL 數(shù)據(jù)庫(kù)設(shè)計(jì)： —>數(shù)據(jù)庫(kù)設(shè)計(jì)-降落傘—>…

規(guī)則1：一般情況可以選擇MyISAM存儲(chǔ)引擎，如果需要事務(wù)支持必須使用InnoDB存儲(chǔ)引擎。

注意：MyISAM存儲(chǔ)引擎 B-tree索引有一個(gè)很大的限制：參與一個(gè)索引的所有字段的長(zhǎng)度之和不能超過(guò)1000字節(jié)。另外MyISAM數(shù)據(jù)和索引是分開(kāi)，而InnoDB的數(shù)據(jù)存儲(chǔ)是按聚簇(cluster)索引有序排列的，主鍵是默認(rèn)的聚簇(cluster)索引，因此MyISAM雖然在一般情況下，查詢(xún)性能比InnoDB高，但I(xiàn)nnoDB的以主鍵為條件的查詢(xún)性能是非常高的。

規(guī)則2：命名規(guī)則。

數(shù)據(jù)庫(kù)和表名應(yīng)盡可能和所服務(wù)的業(yè)務(wù)模塊名一致

服務(wù)與同一個(gè)子模塊的一類(lèi)表應(yīng)盡量以子模塊名(或部分單詞)為前綴或后綴

表名應(yīng)盡量包含與所存放數(shù)據(jù)對(duì)應(yīng)的單詞

字段名稱(chēng)也應(yīng)盡量保持和實(shí)際數(shù)據(jù)相對(duì)應(yīng)

聯(lián)合索引名稱(chēng)應(yīng)盡量包含所有索引鍵字段名或縮寫(xiě)，且各字段名在索引名中的順序應(yīng)與索引鍵在索引中的索引順序一致，并盡量包含一個(gè)類(lèi)似idx的前綴或后綴，以表明期對(duì)象類(lèi)型是索引。

約束等其他對(duì)象也應(yīng)該盡可能包含所屬表或其他對(duì)象的名稱(chēng)，以表明各自的關(guān)系

規(guī)則3：數(shù)據(jù)庫(kù)字段類(lèi)型定義

經(jīng)常需要計(jì)算和排序等消耗CPU的字段,應(yīng)該盡量選擇更為迅速的字段，如用TIMESTAMP(4個(gè)字節(jié)，最小值1970-01-01 00:00:00)代替Datetime（8個(gè)字節(jié)，最小值1001-01-01 00:00:00）,通過(guò)整型替代浮點(diǎn)型和字符型

變長(zhǎng)字段使用varchar，不要使用char

對(duì)于二進(jìn)制多媒體數(shù)據(jù)，流水隊(duì)列數(shù)據(jù)(如日志)，超大文本數(shù)據(jù)不要放在數(shù)據(jù)庫(kù)字段中

規(guī)則4：業(yè)務(wù)邏輯執(zhí)行過(guò)程必須讀到的表中必須要有初始的值。避免業(yè)務(wù)讀出為負(fù)或無(wú)窮大的值導(dǎo)致程序失敗

規(guī)則5：并不需要一定遵守范式理論，適度的冗余，讓Query盡量減少Join (三大范式)

規(guī)則6：訪問(wèn)頻率較低的大字段拆分出數(shù)據(jù)表。有些大字段占用空間多，訪問(wèn)頻率較其他字段明顯要少很多，這種情況進(jìn)行拆分，頻繁的查詢(xún)中就不需要讀取大字段，造成IO資源的浪費(fèi)。

規(guī)則7：大表可以考慮水平拆分。大表影響查詢(xún)效率，根據(jù)業(yè)務(wù)特性有很多拆分方式，像根據(jù)時(shí)間遞增的數(shù)據(jù)，可以根據(jù)時(shí)間來(lái)分。以id劃分的數(shù)據(jù)，可根據(jù)id%數(shù)據(jù)庫(kù)個(gè)數(shù)的方式來(lái)拆分。

一.數(shù)據(jù)庫(kù)索引

規(guī)則8：業(yè)務(wù)需要的相關(guān)索引是根據(jù)實(shí)際的設(shè)計(jì)所構(gòu)造sql語(yǔ)句的where條件來(lái)確定的，業(yè)務(wù)不需要的不要建索引，不允許在聯(lián)合索引（或主鍵）中存在多于的字段。特別是該字段根本不會(huì)在條件語(yǔ)句中出現(xiàn)。

規(guī)則9：唯一確定一條記錄的一個(gè)字段或多個(gè)字段要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢(xún)效率建普通索引

規(guī)則10：業(yè)務(wù)使用的表，有些記錄數(shù)很少，甚至只有一條記錄，為了約束的需要，也要建立索引或者設(shè)置主鍵。

規(guī)則11：對(duì)于取值不能重復(fù)，經(jīng)常作為查詢(xún)條件的字段，應(yīng)該建唯一索引(主鍵默認(rèn)唯一索引)，并且將查詢(xún)條件中該字段的條件置于第一個(gè)位置。沒(méi)有必要再建立與該字段有關(guān)的聯(lián)合索引。

規(guī)則12：對(duì)于經(jīng)常查詢(xún)的字段，其值不唯一，也應(yīng)該考慮建立普通索引，查詢(xún)語(yǔ)句中該字段條件置于第一個(gè)位置，對(duì)聯(lián)合索引處理的方法同樣。

規(guī)則13：業(yè)務(wù)通過(guò)不唯一索引訪問(wèn)數(shù)據(jù)時(shí)，需要考慮通過(guò)該索引值返回的記錄稠密度，原則上可能的稠密度最大不能高于0.2，如果稠密度太大，則不合適建立索引了。

當(dāng)通過(guò)這個(gè)索引查找得到的數(shù)據(jù)量占到表內(nèi)所有數(shù)據(jù)的20%以上時(shí)，則需要考慮建立該索引的代價(jià)，同時(shí)由于索引掃描產(chǎn)生的都是隨機(jī)I/O，生其效率比全表順序掃描的順序I/O低很多。數(shù)據(jù)庫(kù)系統(tǒng)優(yōu)化query的時(shí)候有可能不會(huì)用到這個(gè)索引。

規(guī)則14：需要聯(lián)合索引(或聯(lián)合主鍵)的數(shù)據(jù)庫(kù)要注意索引的順序。SQL語(yǔ)句中的匹配條件也要跟索引的順序保持一致。

注意：索引的順勢(shì)不正確也可能導(dǎo)致嚴(yán)重的后果。

規(guī)則15：表中的多個(gè)字段查詢(xún)作為查詢(xún)條件，不含有其他索引，并且字段聯(lián)合值不重復(fù)，可以在這多個(gè)字段上建唯一的聯(lián)合索引，假設(shè)索引字段為 (a1,a2,…an),則查詢(xún)條件(a1 op val1,a2 op val2,…am op valm)m<=n,可以用到索引，查詢(xún)條件中字段的位置與索引中的字段位置是一致的。

規(guī)則16：聯(lián)合索引的建立原則(以下均假設(shè)在數(shù)據(jù)庫(kù)表的字段a,b,c上建立聯(lián)合索引(a,b,c))

聯(lián)合索引中的字段應(yīng)盡量滿(mǎn)足過(guò)濾數(shù)據(jù)從多到少的順序，也就是說(shuō)差異最大的字段應(yīng)該房子第一個(gè)字段

建立索引盡量與SQL語(yǔ)句的條件順序一致，使SQL語(yǔ)句盡量以整個(gè)索引為條件，盡量避免以索引的一部分(特別是首個(gè)條件與索引的首個(gè)字段不一致時(shí))作為查詢(xún)的條件

Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40為條件可以用到此聯(lián)合索引；而這些語(yǔ)句where b=10,where c=221,where b>=12 and c=2則無(wú)法用到這個(gè)聯(lián)合索引。

當(dāng)需要查詢(xún)的數(shù)據(jù)庫(kù)字段全部在索引中體現(xiàn)時(shí)，數(shù)據(jù)庫(kù)可以直接查詢(xún)索引得到查詢(xún)信息無(wú)須對(duì)整個(gè)表進(jìn)行掃描(這就是所謂的key-only)，能大大的提高查詢(xún)效率。 當(dāng)a，ab，abc與其他表字段關(guān)聯(lián)查詢(xún)時(shí)可以用到索引

當(dāng)a，ab，abc順序而不是b，c，bc，ac為順序執(zhí)行Order by或者group不要時(shí)可以用到索引

以下情況時(shí)，進(jìn)行表掃描然后排序可能比使用聯(lián)合索引更加有效 a.表已經(jīng)按照索引組織好了 b.被查詢(xún)的數(shù)據(jù)站所有數(shù)據(jù)的很多比例。

規(guī)則17：重要業(yè)務(wù)訪問(wèn)數(shù)據(jù)表時(shí)。但不能通過(guò)索引訪問(wèn)數(shù)據(jù)時(shí)，應(yīng)該確保順序訪問(wèn)的記錄數(shù)目是有限的，原則上不得多于10.

二.Query語(yǔ)句與應(yīng)用系統(tǒng)優(yōu)化

規(guī)則18：合理構(gòu)造Query語(yǔ)句

Insert語(yǔ)句中，根據(jù)測(cè)試，批量一次插入1000條時(shí)效率最高，多于1000條時(shí)，要拆分，多次進(jìn)行同樣的插入，應(yīng)該合并批量進(jìn)行。注意query語(yǔ)句的長(zhǎng)度要小于mysqld的參數(shù) max_allowed_packet

查詢(xún)條件中各種邏輯操作符性能順序是and,or,in,因此在查詢(xún)條件中應(yīng)該盡量避免使用在大集合中使用in

永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大記錄集，因?yàn)樵趍ysql中，只有Nested Join一種Join方式，就是說(shuō)mysql的join是通過(guò)嵌套循環(huán)來(lái)實(shí)現(xiàn)的。通過(guò)小結(jié)果集驅(qū)動(dòng)大記錄集這個(gè)原則來(lái)減少嵌套循環(huán)的循環(huán)次數(shù)，以減少I(mǎi)O總量及CPU運(yùn)算次數(shù)

盡量?jī)?yōu)化Nested Join內(nèi)層循環(huán)。

只取需要的columns，盡量不要使用select *

僅僅使用最有效的過(guò)濾字段，where 字句中的過(guò)濾條件少為好

盡量避免復(fù)雜的Join和子查詢(xún)

Mysql在并發(fā)這塊做得并不是太好，當(dāng)并發(fā)量太高的時(shí)候，整體性能會(huì)急劇下降，這主要與Mysql內(nèi)部資源的爭(zhēng)用鎖定控制有關(guān)，MyIsam用表鎖，InnoDB好一些用行鎖。

規(guī)則19：應(yīng)用系統(tǒng)的優(yōu)化

合理使用cache，對(duì)于變化較少的部分活躍數(shù)據(jù)通過(guò)應(yīng)用層的cache緩存到內(nèi)存中，對(duì)性能的提升是成數(shù)量級(jí)的。

對(duì)重復(fù)執(zhí)行相同的query進(jìn)行合并，減少I(mǎi)O次數(shù)。

事務(wù)相關(guān)性最小原則