5.數(shù)據(jù)庫(kù)

要求: 能熟練使用偿洁、部署、調(diào)優(yōu)沟优、問(wèn)題排查涕滋、懂原理

1.關(guān)系型數(shù)據(jù)庫(kù): MySQL/Oracle/PostSQL

1.InnoDB引擎,事務(wù)處理機(jī)制

由于mysql中有兩套日志機(jī)制，一套是存儲(chǔ)層的redo log挠阁，另一套是server層的binlog宾肺，每次更新數(shù)據(jù)都要對(duì)兩個(gè)日志進(jìn)行更新。為了防止寫日志時(shí)只寫了其中一個(gè)而沒(méi)有寫另外一個(gè)侵俗，mysql使用了一個(gè)叫兩階段提交的方式保證事務(wù)的一致性

執(zhí)行流程:

1. 首先執(zhí)行器會(huì)找引擎取ID=2這一行數(shù)據(jù)
2. 拿到數(shù)據(jù)后會(huì)把數(shù)據(jù)進(jìn)行+1操作锨用，然后調(diào)用引擎接口把新數(shù)據(jù)寫入
3. 引擎將數(shù)據(jù)更新到內(nèi)存中，并將操作記錄到redo log里隘谣，此時(shí)redo log處于prepare狀態(tài)黔酥。但它不會(huì)提交事務(wù)，只是通知執(zhí)行器已經(jīng)完成任務(wù)洪橘，可以隨時(shí)提交跪者。
4. 執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫入磁盤
5. 最后執(zhí)行器調(diào)用引擎的事務(wù)接口熄求，把redo log改為提交狀態(tài)渣玲，更新完成。

2.MySQL優(yōu)化

粗說(shuō):

1. 加索引的時(shí)候盡量準(zhǔn)確弟晚，避免造成不必要的鎖定影響其他查詢忘衍。

2. 盡量減少給予范圍的數(shù)據(jù)檢索（間隙鎖）逾苫，避免因?yàn)殚g隙鎖帶來(lái)的影響，鎖定了不該鎖定的記錄枚钓。

3. 盡量控制事務(wù)的大小铅搓，減少鎖定的資源量和鎖定時(shí)間。

4. 盡量使用較低級(jí)別的事務(wù)隔離搀捷，減少 MySQL 因?yàn)槭聞?wù)隔離帶來(lái)的成本星掰。

細(xì)說(shuō):

1. 頻繁變動(dòng)的字段, 不適合建索引
2. 離散度越高越好, 離散低不適合建索引
3. like "asd%" 都不一定用索引,其他位置都不用索引查詢
4. 比對(duì)規(guī)則從左往右, 離散高放前邊, 一個(gè)索引器反復(fù), 最左前綴原則
5. 去掉冗余索引, 有聯(lián)合索引, 就不要單個(gè)的聯(lián)合索引的列
6. 能用覆蓋索引(主鍵), 就千萬(wàn)不要 select *, 速度至少快1倍
7. 用or 索引會(huì)失效

3.MySQL鎖相關(guān)

image-20200420125031591.png

鎖分類:

• 表級(jí)鎖：對(duì)整張表加鎖。開銷小嫩舟，加鎖快氢烘；不會(huì)出現(xiàn)死鎖；鎖定粒度大家厌，發(fā)生鎖沖突的概率最高播玖，并發(fā)度最低象颖。

• 行級(jí)鎖：對(duì)某行記錄加鎖护锤。開銷大比藻，加鎖慢饲鄙；會(huì)出現(xiàn)死鎖煞额；鎖定粒度最小幽邓，發(fā)生鎖沖突的概率最低证逻，并發(fā)度也最高义桂。

• 頁(yè)面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間畴栖；會(huì)出現(xiàn)死鎖随静；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般吗讶。

image-20200420131311713.png

表級(jí)鎖:

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖燎猛，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前，必須先取得該表的 IS 鎖照皆。

意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖重绷，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前，必須先取得該表的 IX 鎖膜毁。

注：意向共享鎖和意向排他鎖是數(shù)據(jù)庫(kù)主動(dòng)加的昭卓，不需要我們手動(dòng)處理。對(duì)于 UPDATE瘟滨、DELETE 和 INSERT 語(yǔ)句候醒，InnoDB 會(huì)自動(dòng)給數(shù)據(jù)集加排他鎖。

行級(jí)鎖:

共享鎖（S）：當(dāng)一個(gè)事務(wù)讀取一條記錄的時(shí)候杂瘸，不會(huì)阻塞其他事務(wù)對(duì)同一記錄的讀請(qǐng)求倒淫，但會(huì)阻塞對(duì)其的寫請(qǐng)求。當(dāng)讀鎖釋放后败玉，才會(huì)執(zhí)行其他事務(wù)的寫操作敌土。

例如：select … lock in share mode

排他鎖（X）：當(dāng)一個(gè)事務(wù)對(duì)一條記錄進(jìn)行寫操作時(shí)镜硕，會(huì)阻塞其他事務(wù)對(duì)同一表的讀寫操作，當(dāng)該鎖釋放后返干，才會(huì)執(zhí)行其他事務(wù)的讀寫操作兴枯。

共享鎖，排他鎖矩欠，意向共享鎖财剖，意向排他鎖兼容圖例

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容， InnoDB 就將請(qǐng)求的鎖授予該事務(wù)晚顷；反之峰伙， 如果兩者不兼容疗疟，該事務(wù)就要等待鎖釋放该默。

image-20200420131855568.png

間隙鎖

前面談到行鎖是針對(duì)一條記錄進(jìn)行加鎖策彤。當(dāng)對(duì)一個(gè)范圍內(nèi)的記錄加鎖的時(shí)候栓袖，我們稱之為間隙鎖。

當(dāng)使用范圍條件索引數(shù)據(jù)時(shí)店诗，InnoDB 會(huì)對(duì)符合條件的數(shù)據(jù)索引項(xiàng)加鎖裹刮。對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”庞瘸，InnoDB 也會(huì)對(duì)這個(gè)“間隙”加鎖捧弃，這就是間隙鎖。間隙鎖和行鎖合稱（Next-Key鎖）擦囊。

死鎖

兩個(gè)事務(wù)都需要獲得對(duì)方持有的排他鎖才能繼續(xù)完成任務(wù)违霞，這種互相等待對(duì)方釋放資源的情況就是死鎖。

避免死鎖：

1. 第一步查詢瞬场，第二步更新.查詢時(shí)直接上排它鎖
2. 同時(shí)需要查詢修改多個(gè)表,按同樣的順序進(jìn)行加鎖

MySQL 鎖定情況的查詢

在實(shí)際開發(fā)中無(wú)法避免數(shù)據(jù)被鎖的問(wèn)題买鸽，那么我們可以通過(guò)哪些手段來(lái)查詢鎖呢？

表級(jí)鎖可以通過(guò)兩個(gè)變量的查詢：

Table_locks_immediate贯被，產(chǎn)生表級(jí)鎖的次數(shù)眼五。

Table_locks_waited，數(shù)顯表級(jí)鎖而等待的次數(shù)彤灶。

行級(jí)鎖可以通過(guò)下面幾個(gè)變量查詢：

Innodb_row_lock_current_waits看幼，當(dāng)前正在等待鎖定的數(shù)量。

Innodb_row_lock_time（重要）幌陕，從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)長(zhǎng)诵姜。

Innodb_row_lock_time_avg（重要），每次等待所花平均時(shí)間苞轿。

Innodb_row_lock_time_max茅诱，從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次花費(fèi)時(shí)間逗物。

Innodb_row_lock_waits（重要），從系統(tǒng)啟動(dòng)到現(xiàn)在總共等待的次數(shù)瑟俭。

特別注意,行鎖使用不當(dāng)會(huì)造成排它鎖:

原文鏈接：https://blog.csdn.net/claram/java/article/details/54023216

1翎卓、只根據(jù)主鍵進(jìn)行查詢，并且查詢到數(shù)據(jù)摆寄，主鍵字段產(chǎn)生行鎖失暴。

begin;
select * from goods where id = 1 for update;
commit;
2、只根據(jù)主鍵進(jìn)行查詢微饥，沒(méi)有查詢到數(shù)據(jù)逗扒，不產(chǎn)生鎖。

begin;
select * from goods where id = 1 for update;
commit;
3欠橘、根據(jù)主鍵矩肩、非主鍵含索引（name）進(jìn)行查詢，并且查詢到數(shù)據(jù)肃续，主鍵字段產(chǎn)生行鎖黍檩，name字段產(chǎn)生行鎖。

begin;
select * from goods where id = 1 and name='prod11' for update;
commit;

4始锚、根據(jù)主鍵刽酱、非主鍵含索引（name）進(jìn)行查詢，沒(méi)有查詢到數(shù)據(jù)瞧捌，不產(chǎn)生鎖棵里。

begin;
select * from goods where id = 1 and name='prod12' for update;
commit;
5、根據(jù)主鍵姐呐、非主鍵不含索引（name）進(jìn)行查詢殿怜，并且查詢到數(shù)據(jù)，如果其他線程按主鍵字段進(jìn)行再次查詢皮钠，則主鍵字段產(chǎn)生行鎖稳捆，如果其他線程按非主鍵不含索引字段進(jìn)行查詢，則非主鍵不含索引字段產(chǎn)生表鎖麦轰，如果其他線程按非主鍵含索引字段進(jìn)行查詢乔夯，則非主鍵含索引字段產(chǎn)生行鎖，如果索引值是枚舉類型款侵，mysql也會(huì)進(jìn)行表鎖末荐，這段話有點(diǎn)拗口，大家仔細(xì)理解一下新锈。

begin;
select * from goods where id = 1 and name='prod11' for update;
commit;
6甲脏、根據(jù)主鍵、非主鍵不含索引（name）進(jìn)行查詢，沒(méi)有查詢到數(shù)據(jù)块请，不產(chǎn)生鎖娜氏。

begin;
select * from goods where id = 1 and name='prod12' for update;
commit;
7、根據(jù)非主鍵含索引（name）進(jìn)行查詢墩新，并且查詢到數(shù)據(jù)贸弥，name字段產(chǎn)生行鎖。

begin;
select * from goods where name='prod11' for update;
commit;
8海渊、根據(jù)非主鍵含索引（name）進(jìn)行查詢绵疲，沒(méi)有查詢到數(shù)據(jù)，不產(chǎn)生鎖臣疑。

begin;
select * from goods where name='prod11' for update;
commit;
9盔憨、根據(jù)非主鍵不含索引（name）進(jìn)行查詢，并且查詢到數(shù)據(jù)讯沈，name字段產(chǎn)生表鎖郁岩。

begin;
select * from goods where name='prod11' for update;
commit;
10、根據(jù)非主鍵不含索引（name）進(jìn)行查詢芙盘，沒(méi)有查詢到數(shù)據(jù)驯用，name字段產(chǎn)生表鎖脸秽。

begin;
select * from goods where name='prod11' for update;
commit;
11儒老、只根據(jù)主鍵進(jìn)行查詢，查詢條件為不等于记餐，并且查詢到數(shù)據(jù)驮樊，主鍵字段產(chǎn)生表鎖。

begin;
select * from goods where id <> 1 for update;
commit;
12片酝、只根據(jù)主鍵進(jìn)行查詢囚衔，查詢條件為不等于，沒(méi)有查詢到數(shù)據(jù)雕沿，主鍵字段產(chǎn)生表鎖练湿。

begin;
select * from goods where id <> 1 for update;
commit;
13、只根據(jù)主鍵進(jìn)行查詢审轮，查詢條件為 like肥哎，并且查詢到數(shù)據(jù)，主鍵字段產(chǎn)生表鎖疾渣。

begin;
select * from goods where id like '1' for update;
commit;
14篡诽、只根據(jù)主鍵進(jìn)行查詢，查詢條件為 like榴捡，沒(méi)有查詢到數(shù)據(jù)杈女，主鍵字段產(chǎn)生表鎖。

begin;
select * from goods where id like '1' for update;
commit;

樂(lè)觀鎖、悲觀鎖

• 悲觀鎖方案：每次獲取商品時(shí)达椰，對(duì)該商品加排他鎖翰蠢。也就是在用戶A獲取獲取 id=1 的商品信息時(shí)對(duì)該行記錄加鎖，期間其他用戶阻塞等待訪問(wèn)該記錄啰劲。悲觀鎖適合寫入頻繁的場(chǎng)景躏筏。

• 樂(lè)觀鎖方案：每次獲取商品時(shí)，不對(duì)該商品加鎖呈枉。在更新數(shù)據(jù)的時(shí)候需要比較程序中的庫(kù)存量與數(shù)據(jù)庫(kù)中的庫(kù)存量是否相等趁尼，如果相等則進(jìn)行更新，反之程序重新獲取庫(kù)存量猖辫，再次進(jìn)行比較酥泞，直到兩個(gè)庫(kù)存量的數(shù)值相等才進(jìn)行數(shù)據(jù)更新。樂(lè)觀鎖適合讀取頻繁的場(chǎng)景

商城瀏覽商品下單這種高并發(fā)場(chǎng)景下,一般采用讀寫分離啃憎、異構(gòu)數(shù)據(jù)庫(kù)結(jié)構(gòu),讀從Redis等緩存庫(kù)里讀,并發(fā)下單操作,鎖機(jī)制可以用lua腳本芝囤、Redisson、zookpeer等方式做分布式鎖辛萍、或者M(jìn)ySQL的排它鎖,同時(shí)要防止死鎖

事務(wù)隔離級(jí)別

讀未提交（read uncommitted）：它是最低的事務(wù)隔離級(jí)別悯姊，一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被別的事務(wù)看到贩毕。有臟讀的可能性悯许。

讀提交（read committed）：保證一個(gè)事物提交后才能被另外一個(gè)事務(wù)讀取。另外一個(gè)事務(wù)不能讀取該事物未提交的數(shù)據(jù)辉阶∠群荆可避免臟讀的發(fā)生，但是可能會(huì)造成不可重復(fù)讀谆甜。

可重復(fù)讀（repeatable read MySQL 默認(rèn)方式）：多次讀取同一范圍的數(shù)據(jù)會(huì)返回第一次查詢的快照垃僚，即使其他事務(wù)對(duì)該數(shù)據(jù)做了更新修改。事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的规辱。即：事務(wù)A在讀到一條數(shù)據(jù)之后谆棺，此時(shí)事務(wù)B對(duì)該數(shù)據(jù)進(jìn)行了修改并提交，那么事務(wù)A再讀該數(shù)據(jù)罕袋，讀到的還是原來(lái)的內(nèi)容.

串行化（serializable）：是最可靠的事務(wù)隔離級(jí)別改淑。“寫”會(huì)加“排他鎖”炫贤，“讀”會(huì)加“共享鎖”溅固。

2.NoSQL數(shù)據(jù)庫(kù): MongoDB/Elasticsearch(redis、hbase)

3.熟悉一些NewSQL數(shù)據(jù)庫(kù)

4.海量數(shù)據(jù)庫(kù)存儲(chǔ)解決方案及原理