MySQL的查詢峻堰，優(yōu)化讹开，事務(wù)，鎖
幾類數(shù)據(jù)是不適合在數(shù)據(jù)庫(kù)中存放的
1.二進(jìn)制多媒體數(shù)據(jù)
數(shù)據(jù)庫(kù)空間資源消耗嚴(yán)重
存儲(chǔ)很消耗數(shù)據(jù)庫(kù)主機(jī)的CPU資源
2.流水隊(duì)列數(shù)據(jù)
數(shù)據(jù)庫(kù)為了保證事務(wù)的安全性以及可恢復(fù)性捐名，需要記錄所有變更的日志信息旦万。
流水隊(duì)列數(shù)據(jù)的用途決定了存放這種數(shù)據(jù)的表中的數(shù)據(jù)會(huì)被不斷的INSERT，UPDATE镶蹋， DELETE成艘，而每一個(gè)操作都要生成與之對(duì)應(yīng)的日志信息。
可以使用第三方隊(duì)列軟件實(shí)現(xiàn)對(duì)Queue數(shù)據(jù)的處理能力贺归，性能將會(huì)提升淆两。
3.超大文本數(shù)據(jù)
5.0.3之前，VARCHAR類型數(shù)據(jù)最長(zhǎng)能夠存放255個(gè)字節(jié)牧氮，如果需要存儲(chǔ)更長(zhǎng)的文本數(shù)據(jù)到一個(gè)字段琼腔，必須使用TEXT類型(最大存放64KB)瑰枫，甚至是LONGTEXT類型（最大4GB）踱葛。
5.0.3開(kāi)始丹莲，VARCHAR類型的最大程度調(diào)整到了64KB，當(dāng)實(shí)際數(shù)據(jù)小于255Byte的時(shí)候尸诽，實(shí)際存儲(chǔ)空間和實(shí)際數(shù)據(jù)長(zhǎng)度一樣甥材，但是一旦超過(guò)了255Byte之后，占用的存儲(chǔ)空間是實(shí)際數(shù)據(jù)長(zhǎng)度的2倍性含。
使用Cache的時(shí)候大概考慮兩個(gè)方面
數(shù)據(jù)的變更頻率
數(shù)據(jù)的訪問(wèn)概率
比如：
1.系統(tǒng)各種配置及規(guī)則數(shù)據(jù)
變動(dòng)頻率非常低洲赵，訪問(wèn)概率很高
2.活躍用戶的基本信息數(shù)據(jù)
很少有系統(tǒng)的活躍用戶能夠達(dá)到其用戶總量的級(jí)別
也很少有用戶會(huì)頻繁修改自己的基本信息
但是，用戶的基本信息在應(yīng)用系統(tǒng)中訪問(wèn)的概率頻繁
3.準(zhǔn)實(shí)時(shí)數(shù)據(jù)
基于時(shí)間斷統(tǒng)計(jì)數(shù)據(jù)不會(huì)實(shí)時(shí)更新商蕴，很少需要增量更新
關(guān)于優(yōu)化
書中舉了個(gè)栗子：
實(shí)現(xiàn)每個(gè)用戶查看各自相冊(cè)（假設(shè)每個(gè)列表顯示10張照片）
能夠在相片名稱后面顯示該相片的留言數(shù)量叠萍。
方法1：
SELECT id, subject, url FROM photo WHERE user_id = ? LIMIT 10
通過(guò)第一步結(jié)果集中的10個(gè)相片id循環(huán)運(yùn)行10次：
SELECT COUNT(*) FROM photo_comment WHERE photo_id = ?
方法2：
第一步一樣，查詢出10個(gè)
SELECT photo_id, COUNT(*) FROM photo_comment WHERE photo_id IN (?) GROUP BY photo_id
其比較通過(guò)以下幾個(gè)條件
解析等的SQL語(yǔ)句數(shù)目：
每次提交SQL不管是相同還是不同绪商，都需要進(jìn)行完全解析苛谷，主要的消耗資源是數(shù)據(jù)庫(kù)主機(jī)的CPU。
網(wǎng)絡(luò)資源消耗：
網(wǎng)絡(luò)資源交互格郁，執(zhí)行了11條SQL語(yǔ)句腹殿，所以會(huì)產(chǎn)生11次網(wǎng)絡(luò)交互。結(jié)合上一部分知道例书，通過(guò)線程連接模塊聯(lián)系到數(shù)據(jù)庫(kù)主機(jī)上锣尉。
數(shù)據(jù)庫(kù)IO操作
IO操作在數(shù)據(jù)庫(kù)系統(tǒng)中是非常昂貴的資源，尤其是當(dāng)功能的PV較大的時(shí)候决采。
結(jié)果集處理次數(shù)和結(jié)果集大小
應(yīng)用數(shù)據(jù)處理的數(shù)據(jù)拼接
上述2個(gè)方案自沧，第二種方案的SQL效率要比第一種方案要好。
關(guān)于Cache的使用
1.Cache系統(tǒng)的不合理利用會(huì)導(dǎo)致Cache命中低下树瞭，導(dǎo)致數(shù)據(jù)庫(kù)訪問(wèn)量的增加
2.對(duì)可擴(kuò)展性的過(guò)度追求暂幼，對(duì)象拆的過(guò)于離散造成大量的JOIN語(yǔ)句，而MySQL的主要優(yōu)勢(shì)在于處理簡(jiǎn)單邏輯查詢
3.對(duì)數(shù)據(jù)庫(kù)的過(guò)度依賴
將大量更適合存放于文件系統(tǒng)中的數(shù)據(jù)存入數(shù)據(jù)庫(kù)中移迫，造成了數(shù)據(jù)庫(kù)資源的浪費(fèi)旺嬉，影響總體性能
4.過(guò)渡理想化系統(tǒng)的用戶體驗(yàn)
大量非核心業(yè)務(wù)消耗過(guò)多的資源
數(shù)據(jù)庫(kù)中的事務(wù)和鎖：
MySQL使用了是3種鎖類型（級(jí)別）的鎖定機(jī)制：
行級(jí)鎖定：
鎖定對(duì)象的粒度極小
由于鎖定粒度小，發(fā)生資源爭(zhēng)用的概率也是最小
能帶來(lái)大的并發(fā)處理能力厨埋，提高一些高并發(fā)應(yīng)用系統(tǒng)的性能
弊端
由于粒度小邪媳，獲取鎖和釋放鎖要做的事情也就更多，帶來(lái)的消耗也就更大荡陷。
行級(jí)鎖也最容易產(chǎn)生死鎖雨效。
InnoDB和NDB Cluster存儲(chǔ)引擎
表級(jí)鎖定：
最大粒度的鎖定機(jī)制
實(shí)現(xiàn)簡(jiǎn)單，帶來(lái)的系統(tǒng)負(fù)面影響最小废赞，獲取鎖和釋放鎖的速度很快徽龟。
可以很好的避免死鎖問(wèn)題
弊端
出現(xiàn)鎖定資源爭(zhēng)用概率比較高
并發(fā)程度不好
主要是一些非事務(wù)性的存儲(chǔ)引擎：MyISAM，Memory唉地，CSV
頁(yè)級(jí)鎖定：
鎖定粒度介于行級(jí)鎖定和表級(jí)鎖定
資源開(kāi)銷和并發(fā)處理能力也介于二者之間
主要是
隨著鎖定資源粒度的減小据悔，鎖定相同數(shù)據(jù)量數(shù)據(jù)所消耗的內(nèi)存數(shù)量會(huì)越多传透，實(shí)現(xiàn)算法也會(huì)越復(fù)雜。遇到鎖等待的可能性也就越低极颓，并發(fā)的程度越高朱盐。
關(guān)于表級(jí)鎖定
分為兩種類型：一種是讀鎖定，一種是寫鎖定菠隆。
主要通過(guò)四個(gè)隊(duì)列來(lái)維護(hù)來(lái)那種鎖定:
//當(dāng)前只有讀寫鎖的所有線程相關(guān)信息都可以在這兩個(gè)中找到
Current read -lock queue
Current write-lock queue
//正在等待的鎖定資源信息存放在下面兩個(gè)中
Pending read-lock queue
Pending write-lock queue
分別為當(dāng)前正在鎖定中的讀和寫的鎖定信息兵琳，等待讀和寫的鎖定信息。
隊(duì)列中的信息按照獲取鎖的時(shí)間依次存放骇径。
MySQl內(nèi)部定義實(shí)現(xiàn)了11中鎖定類型躯肌，有系統(tǒng)中一個(gè)枚舉變量thr_lock_type定義：
IGNORE發(fā)生鎖請(qǐng)求的時(shí)候內(nèi)部交互使用，鎖定結(jié)構(gòu)和隊(duì)列中并不會(huì)有任何信息存儲(chǔ)
UNLOCK釋放鎖定請(qǐng)求的鎖類型
READ讀鎖定
WRITE寫鎖定
READ_WITH_SHARED_LOCKS在innodb中使用破衔，select ... lock in share mode
READ_HIGH_PRIORITY高優(yōu)先級(jí)鎖定
READ_NO_INSERT不允許Concurent Insert的鎖定
Concurent Insert允許一邊讀羡榴，一邊插入
WRITE_ALLOW_WRITE當(dāng)存儲(chǔ)引擎自行處理鎖定的時(shí)候，MySQL可以允許其他線程獲取讀或者寫鎖定运敢。
WRITE_ALLOW_READ對(duì)表做DDL的時(shí)候校仑，MySQL允許其他線程獲取讀鎖定。重建表之后再RENAME而實(shí)現(xiàn)該功能传惠，所以整個(gè)過(guò)程任然可以提供讀服務(wù)迄沫。
WRITE_CONCURRENT_INSERT正在進(jìn)行Concurrent_insert時(shí)候使用的鎖定方式，除了READ_NO_INSERT之外其他任何讀鎖定都不會(huì)被阻塞卦方。
WRITE_DELAYED在使用INSERT_DELAYED時(shí)候的鎖定類型羊瘩。
INSERT_DELAYED，當(dāng)一個(gè)客戶端使用INSERT_DELAYED的時(shí)候盼砍，會(huì)立刻從服務(wù)器獲得一個(gè)確定尘吗。并且被排入隊(duì)列，當(dāng)表沒(méi)有被其他線程使用的時(shí)候浇坐，此行被插入睬捶。
WRITE_LOW_PRIORITY顯示聲明的低級(jí)別鎖定方式。
WRITE_ONLY鎖定異常中斷后系統(tǒng)進(jìn)行CLOSE TABLE操作近刘。
讀鎖定：
申請(qǐng)獲取讀鎖定資源的時(shí)候需要滿足兩個(gè)條件：主要檢查請(qǐng)求資源的寫鎖定和是否有等待
1.請(qǐng)求資源當(dāng)前沒(méi)有被寫鎖定
2.寫鎖定等待隊(duì)列中沒(méi)有更高優(yōu)先級(jí)的寫鎖定等待
如果滿足兩個(gè)條件擒贸，請(qǐng)求會(huì)被立即通過(guò)。相關(guān)信息存入Current read-lock quenu 中觉渴，如果沒(méi)有一個(gè)條件滿足介劫，被迫進(jìn)入等待隊(duì)列Pending read-lock queue中等待釋放。
寫鎖定：
檢查順序：
Current write-lock queue //如果沒(méi)有案淋，進(jìn)Pending
Pending write-lock queue //如果沒(méi)有座韵，進(jìn)Current read-lock queue,如果鎖定存在，進(jìn)Pending等待
Current read-lock queue
特殊情況：
請(qǐng)求鎖的類型為WRITE_DELAYED
請(qǐng)求鎖的類型為WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE踢京，同時(shí)Current read lock是READ_NO_INSERT (READ_NO_INSERT在這種情況下會(huì)被阻塞)
這兩種情況下誉碴，寫鎖定直接進(jìn)Current write-lock queue
讀寫請(qǐng)求隊(duì)列中的寫鎖請(qǐng)求的優(yōu)先級(jí)規(guī)則：
除了READ_HIGH_PRIORITY的讀鎖定之外宦棺，Pending write-lock queue中的WRITE寫鎖定能夠阻塞所有其他讀鎖定。
READ_HIGH_PRORITY讀鎖定能夠阻塞所有的Pending write-lock queue中的寫鎖定翔烁。
除了WRITE寫鎖定之外，Pending write-lock queue中其他的任何寫鎖定都比讀鎖定的優(yōu)先級(jí)的旨涝。
寫鎖定出現(xiàn)在Current write-lock queue之后蹬屹，會(huì)阻塞除了以下情況的所有鎖定請(qǐng)求：
某些引擎下，可以允許WRITE_CONCURRENT_INSERT 寫鎖定請(qǐng)求
寫鎖定為WRITE_ALLOW_WRITE,允許除了WRITE_ONLY之外所有讀和寫的所有請(qǐng)求
WRITE_ALLOW_READ的時(shí)候白华，允許READ_NO_INSERT之外的所有讀鎖定請(qǐng)求
WRITE_DELAYED的時(shí)候慨默，允許除了READ_NO_INSERT之外所有的讀鎖定請(qǐng)求。
WRITE_CONCURRENT_INSERT的時(shí)候弧腥，允許除了READ_NO_INSERT以外的所有讀鎖定請(qǐng)求厦取。
行級(jí)鎖定并不是MySQL自己實(shí)現(xiàn)的鎖定方式，而是有其他存儲(chǔ)引擎自己實(shí)現(xiàn)的管搪。
InnoDB
innoDB和Oracle的數(shù)據(jù)庫(kù)有不少的相似的地方虾攻。
InnoDB的行級(jí)鎖分為兩類，共享鎖 排它鎖
鎖定機(jī)制中為了讓行級(jí)別鎖定和表級(jí)別鎖定共存更鲁，使用了意向鎖霎箍，有了意向共享鎖和意向排它鎖
意向鎖的作用是放一個(gè)事務(wù)在需要獲取資源鎖定的時(shí)候，如果遇到所需資源被排它鎖占用之后澡为，可以在鎖定行的表之上添加一個(gè)合適的意向鎖漂坏。
意向共享鎖可以有多個(gè)，意向排它鎖只可以有一個(gè)媒至。
對(duì)比Oracle
Oracle 的鎖定數(shù)據(jù)是通過(guò)鎖定某行所在記錄的物理Block上的事務(wù)槽上加表級(jí)鎖定信息顶别。
InnoDB通過(guò)數(shù)據(jù)的第一個(gè)索引鍵之前和最后一個(gè)索引鍵之后的空余空間上表級(jí)鎖定信息。
Innodb鎖定的實(shí)現(xiàn)成為間隙鎖。Query執(zhí)行范圍查找的話蔗候，會(huì)鎖定范圍內(nèi)所有的索引鍵值肌蜻，即是鍵值并不存在。而這些不存在的鍵值會(huì)導(dǎo)致無(wú)法插入条篷。
InnoDB 各事務(wù)隔離級(jí)別下鎖定：
Read Uncommited
Read commited
Repeatable Read
Serializable
InnoDB 發(fā)生死鎖后，會(huì)判斷發(fā)生死鎖的事務(wù)各自更改數(shù)據(jù)量判斷事務(wù)的大小蛤织，回滾記錄最少的赴叹！
如果涉及到多個(gè)存儲(chǔ)引擎的話，Innodb沒(méi)辦法檢測(cè)死鎖指蚜，這種情況下只能使用超時(shí)限制來(lái)解決死鎖乞巧。
下一篇將Query的查詢優(yōu)化！
