介紹
MySQL是在開發(fā)過程中使用的最多的一個關(guān)系型數(shù)據(jù)庫。所以了解和掌握對它的調(diào)優(yōu)是很有必要的。
MySQL數(shù)據(jù)類型
MySQL的支持哪些數(shù)據(jù)類型，對應(yīng)的大小及用途等信息，附上幾個表格。
引擎
MySQL的官方引擎主要有兩個：MyISAM和InnoDB。
MyISAM最大的特點是支持全文索引、查詢效率較高。缺點是不支持事物、表級鎖。
InnoDB是MySQL5.5之后MySQL默認(rèn)引擎。特點是支持ACID事務(wù)、支持外鍵、支持行級鎖提高了并發(fā)效率。
有關(guān)事務(wù)、外鍵、鎖下面會一一介紹。
數(shù)據(jù)庫事務(wù)
數(shù)據(jù)庫中事務(wù)的特性：ACID，這個也是上面說的InnoDB支持ACID事務(wù)。
ACID分別為：原子性、一致性、隔離性、持久性。
原子性：事務(wù)的操作要么全部成功要么失敗回滾。
一致性：事務(wù)不能破壞數(shù)據(jù)庫的一致性和完整性。例如事務(wù)操作了多張表，要么多張表全是操作前的數(shù)據(jù)要么多張表全是操作后的數(shù)據(jù)。
隔離性：隔離性是針對并發(fā)問題，多用戶的事務(wù)相互隔離。
持久性：事務(wù)提交成功后對數(shù)據(jù)庫的修改是永久的。
數(shù)據(jù)庫并發(fā)
剛才提到事務(wù)的隔離性是針對并發(fā)問題的。如果對事務(wù)沒有進(jìn)行隔離會出現(xiàn)什么問題呢？先看一張圖。
如果沒有進(jìn)行事務(wù)隔離，則將會出現(xiàn)臟讀、不可重復(fù)度、幻讀等情況，分別說明一下。
臟讀：事物A讀取到了事物B未提交的數(shù)據(jù)。
不可重復(fù)讀：事物A多次讀區(qū)數(shù)據(jù)庫中同一條數(shù)據(jù)時返回不同的數(shù)據(jù)。
幻讀：事物A執(zhí)行完全相同的語句時返回結(jié)果集不同。與不可重復(fù)的區(qū)別是不可重復(fù)是讀區(qū)一條數(shù)據(jù)。幻讀是由于增加和刪除記錄導(dǎo)致的。
不同的事務(wù)隔離級別能解決不同的問題，如上圖所示。MySQL默認(rèn)是可重復(fù)讀級別。
事務(wù)分類
事務(wù)分為：扁平化事務(wù)、帶保存點扁平化事務(wù)、鏈?zhǔn)聞?wù)、嵌套事務(wù)、分布式事務(wù)。
扁平化事務(wù)：要么全部成功，要么全部回滾。這個也是使用最多的。
帶保存點扁平化事務(wù)：是在事務(wù)執(zhí)行過程中插入保存點，事務(wù)失敗時回滾到保存點。
鏈?zhǔn)聞?wù)：可以回滾到最近的保存點。和帶保存點扁平化事務(wù)的區(qū)別在于帶保存點扁平化事務(wù)可以回滾到任意保存點。
嵌套事務(wù)：事務(wù)的嵌套，如果上層事務(wù)回滾所有事務(wù)回滾。
分布式事務(wù)：分布式系統(tǒng)的扁平化事務(wù)，這個塊內(nèi)容很多后面可以單獨拿出來寫一篇。
MySQL鎖
上面說過InnoDB是行級鎖，MyISAM是表級鎖。下面介紹一下幾個常見的鎖。
行級鎖：鎖的顆粒度小，并發(fā)效率高，可能出現(xiàn)鎖死。
表級鎖：鎖的顆粒度大，并發(fā)效率低，無鎖死。
共享鎖：也叫讀鎖，就是其他事務(wù)可以讀就是不能寫。上鎖語句：lock in share mode。
排他鎖：排他鎖就是寫鎖，其他事務(wù)不能讀取，也不能寫。UPDATE、DELETE 和 INSERT 語句，InnoDB 會自動給涉及的數(shù)據(jù)集加排他鎖。上鎖語句：select for update
需要說明的是：行級鎖和表級鎖是鎖的顆粒度，而共享鎖和排他鎖是鎖的策略。
外鍵
InnoDB支持外鍵，那外鍵是什么呢？簡單介紹一下。
外鍵：A表保存的B表的主鍵id。
可能會想，這個不是一直都這樣嗎？其實不是的一般情況下，外鍵開發(fā)人員知道，但是數(shù)據(jù)庫不知道，現(xiàn)在數(shù)據(jù)庫知道了以后就可以幫我們確保數(shù)據(jù)的完整性和一致性。比如如果刪除主表的那從表自動刪除等等。
索引
索引是我們最常用的優(yōu)化方式，可以大幅提升數(shù)據(jù)庫的查詢性能。當(dāng)然它也有代價，代價就是犧牲磁盤空間以及新增、修改、刪除等操作增加額外開銷。
索引分為：唯一索引、主鍵索引、普通索引、聯(lián)合索引、全文索引
唯一索引：全表唯一，允許未空。
主鍵索引：全表唯一，不允許未空。
普通索引：允許索引列值相同。
聯(lián)合索引：是由多個列組成的索引，并不是多個索引組成。聯(lián)合索引遵循《最左原則》所以選擇列時順序很重要。
全文索引：只能在 CHAR、VARCHAR、TEXT 類型字段上使用，底層使用倒排索引實現(xiàn)。這個慎用，會非常消耗磁盤資源。
MySQL調(diào)優(yōu)
MySQL的調(diào)優(yōu)是經(jīng)常用到的所有很有必要掌握，大概調(diào)優(yōu)思路如下：
表結(jié)構(gòu)+索引 ?-> SQL優(yōu)化 ?->MySQL參數(shù)優(yōu)化 ?-> ?硬件及系統(tǒng)配置
當(dāng)然最有效果的是最右邊的方案，但也是成本最高的。
下面羅列一下優(yōu)化方案：
1.要在設(shè)計表結(jié)構(gòu)時，考慮數(shù)據(jù)庫的水平與垂直擴(kuò)展能力。
2.要選擇合適的字段大小及類型。
3.避免一個表子段過多。
4.做適當(dāng)?shù)姆捶妒剑⒁膺m當(dāng)?shù)摹?/p>
5.對經(jīng)常查詢的維度要做好索引。
6.列盡量設(shè)置為not null，因為MySQL對可以為null的列無法做查詢優(yōu)化，允許未null使得索引統(tǒng)計更加復(fù)雜，以及占用更多的空間。
7.通過慢查詢?nèi)罩緛磲槍π缘膬?yōu)化。
8.學(xué)會使用Explain來分析語句。
9.盡量使用索引排序。
以上是對單庫、單表的優(yōu)化。后面會單獨寫分區(qū)、分表、分褲、讀寫分離等等。
