一、MySQL的主要適用場景

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

變長字段使用varchar，不要使用char

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)合索引；而這些語句where b=10,where c=221,where b>=12 and c=2則無法用到這個聯(lián)合索引。

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

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

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

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

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

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

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

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

永遠用小結(jié)果集驅(qū)動大記錄集，因為在mysql中，只有Nested Join一種Join方式，就是說mysql的join是通過嵌套循環(huán)來實現(xiàn)的。通過小結(jié)果集驅(qū)動大記錄集這個原則來減少嵌套循環(huán)的循環(huán)次數(shù)，以減少IO總量及CPU運算次數(shù)

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

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

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

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

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

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

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

對重復(fù)執(zhí)行相同的query進行合并，減少IO次數(shù)。

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