數(shù)據(jù)庫(kù)三范式

1. 每一列都已經(jīng)是不可拆分的最小單元
2. 保證每張表只描述一件事
3. 不傳遞非主鍵的的依賴

事物的四大特性

1. 原子性 要么執(zhí)行剔蹋，要么都不執(zhí)行
2. 一致性 事物執(zhí)行前后，數(shù)據(jù)從一個(gè)有效狀態(tài)遷移到另一個(gè)有效狀態(tài)搁进，這個(gè)有效可以是數(shù)據(jù)庫(kù)月數(shù)，也可以是我們的業(yè)務(wù)月數(shù)昔头，在ACID中饼问，我們利用AID來(lái)保證C
3. 隔離性 事物之間根據(jù)不同的隔離級(jí)別具有隔離屬性
4. 持久性 事物一旦提交，就不能再被修改揭斧、回滾

事物問(wèn)題

1. 臟讀 讀到其他事物未提交的數(shù)據(jù)
2. 幻讀 讀到其他事物已提交插入的數(shù)據(jù)莱革，數(shù)量不一致
3. 不可重復(fù)讀 讀到其他事物已提交修改的數(shù)據(jù)，值不一致

事物隔離級(jí)別

1. 串行化 所有請(qǐng)求串行執(zhí)行讹开，規(guī)避所有事物問(wèn)題
2. 可重復(fù)讀 在同一個(gè)事物中能讀到相同的數(shù)據(jù)盅视，規(guī)避不可重讀和臟讀，mysql通過(guò)mvcc解決了幻讀
3. 讀已提交 只會(huì)讀到其他事物已經(jīng)提交的數(shù)據(jù)旦万，規(guī)避臟讀
4. 讀未提交 可以讀到所有狀態(tài)數(shù)據(jù)
5. readonly（oracle特有事務(wù)闹击， 串行化、讀已提交成艘、readonly）

Mysql默認(rèn)事物隔離級(jí)別

可重復(fù)讀赏半。
歷史原因跟binlog有關(guān)系贺归，以前的binlog是statement形式，就是條sql断箫，在非可重復(fù)讀場(chǎng)景下會(huì)有問(wèn)題拂酣；

binlog模式

• statement模式，記錄每條提交的sql
• row模式仲义，記錄每一行修改后的數(shù)據(jù) 新版本對(duì)row模式也做了優(yōu)化踱葛，并不是所有的修改都會(huì)以row 來(lái)記錄，像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來(lái)記錄
• mixed模式光坝，在mixed模式下尸诽，mysql會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，也是在statement和row之間選擇一種盯另。

Mysql的引擎類型

• innodb 支持事務(wù) 表鎖性含、行鎖、間隙鎖
• myIsam 不支持事務(wù) 表鎖 適用于只讀或?qū)懮僭Ч撸瑪?shù)據(jù)行少的場(chǎng)景
• dbd 不支持事務(wù) 表鎖 頁(yè)面鎖
• memory 不支持事務(wù) 表鎖

MyIsam和Innodb的區(qū)別

• MyIsam 不支持事務(wù)商蕴；表鎖；非聚簇索引芝发；存儲(chǔ)文件有三個(gè)绪商，表結(jié)構(gòu)、索引辅鲸、數(shù)據(jù)文件
• Innodb 支持事物格郁；表鎖、行鎖独悴；聚簇索引例书；存儲(chǔ)文件兩個(gè)，表機(jī)構(gòu)刻炒、聚簇索引文件

Innodb事務(wù)實(shí)現(xiàn)

通過(guò)transaction命令或者begin命令開(kāi)啟决采，每一個(gè)事務(wù)創(chuàng)建的時(shí)候會(huì)分配一個(gè)唯一自增的事務(wù)ID，數(shù)據(jù)行存在一個(gè)隱藏字段就是上一次修改此行記錄的事務(wù)ID

Innodb邏輯存儲(chǔ)結(jié)構(gòu)

• 表空間 共享表空間：undolog坟奥、事務(wù) 私有表空間：表結(jié)構(gòu)树瞭、索引、數(shù)據(jù)
• 段Or表
• 區(qū) 物理上連續(xù)的幾個(gè)頁(yè)
• 頁(yè)Or塊 16K

text這些大文本是如何存儲(chǔ)的

使用溢出頁(yè)爱谁，在表私有空間中晒喷，有一些位置存儲(chǔ)大型數(shù)據(jù)比如text，聚簇索引上存儲(chǔ)數(shù)據(jù)開(kāi)頭的一部分?jǐn)?shù)據(jù)和這個(gè)溢出頁(yè)的地址

Mysql中的鎖

• 表鎖 LOCK TABLES 給表顯式加表鎖
• 行鎖 在索引上實(shí)現(xiàn)的管行，所以如果要加行鎖厨埋，必須是走索引查詢的語(yǔ)句，否則會(huì)降級(jí)為表鎖
• 頁(yè)鎖
• 間隙鎖 用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)荡陷，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖雨效；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”废赞，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖徽龟，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）；可以防止當(dāng)前讀的幻讀
• 讀鎖 select默認(rèn)不加讀鎖唉地，可以使用 lock in share mode据悔；排斥寫(xiě)鎖
• 寫(xiě)鎖 update、delete耘沼、insert默認(rèn)加寫(xiě)鎖极颓；select for update；排斥讀鎖和寫(xiě)鎖
• 意向鎖 為了同時(shí)支持行鎖與表鎖群嗤，在行鎖加讀鎖時(shí)菠隆，需要先申請(qǐng)表級(jí)意向鎖，自動(dòng)的

Mysql中的索引數(shù)據(jù)結(jié)構(gòu)

B+Tree
Hash
fulltext 全文索引 倒排
RTree 三維空間上的索引狂秘，每個(gè)節(jié)點(diǎn)維護(hù)了多個(gè)指針指向空間上的相鄰點(diǎn)

為什么用B+樹(shù)

索引很大不能存儲(chǔ)在內(nèi)存骇径，需要存儲(chǔ)在磁盤上，這樣就會(huì)有IO問(wèn)題者春，磁盤一次可以預(yù)讀頁(yè)數(shù)據(jù)破衔，要充分利用預(yù)讀，索引的數(shù)據(jù)結(jié)構(gòu)要盡量保證在查詢過(guò)程中減少IO次數(shù)钱烟，索引存儲(chǔ)在邏輯存儲(chǔ)結(jié)構(gòu)頁(yè)上晰筛，一頁(yè)為16k，查找數(shù)據(jù)過(guò)程

B+樹(shù)在可以在一頁(yè)上維護(hù)2048個(gè)節(jié)點(diǎn)忠售，兩層就可以索引400w行數(shù)據(jù)（理論上）
B+樹(shù)的葉子節(jié)點(diǎn)上維護(hù)了指向上一條和下一條記錄的指針传惠，形成一個(gè)雙向鏈表，范圍查找效果好稻扬；

Hash 無(wú)法實(shí)現(xiàn)范圍查找
紅黑樹(shù) 樹(shù)高問(wèn)題
B樹(shù) 每個(gè)節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，每頁(yè)上的數(shù)據(jù)量就會(huì)少羊瘩，就比B+樹(shù)高

什么是聚簇索引

innodb的文件組成只有兩個(gè)泰佳，一個(gè)索引文件、一個(gè)數(shù)據(jù)庫(kù)描述文件尘吗；而數(shù)據(jù)存儲(chǔ)在索引文件中
innodb的table的主鍵索引的逝她，葉子節(jié)點(diǎn)，存儲(chǔ)了這行記錄的完整數(shù)據(jù)睬捶，以索引查詢數(shù)據(jù)的時(shí)候黔宛，能直接拿到數(shù)據(jù)，不需要再去另外的磁盤查詢數(shù)據(jù)擒贸，減少IO

Mysql執(zhí)行流程

查詢等過(guò)程如下：權(quán)限校驗(yàn)—》查詢緩存—》分析器—》優(yōu)化器—》權(quán)限校驗(yàn)—》執(zhí)行器—》引擎
更新等流程如下：分析器----》權(quán)限校驗(yàn)----》執(zhí)行器—》引擎—redo log prepare—》binlog—》redo log commit

Mysql執(zhí)行計(jì)劃explain

id 有順序的編號(hào)臀晃，查詢順序觉渴，有幾個(gè)select就有幾行
selectType 查詢類型
table 表名
type 重要標(biāo)志
>const 索引一次命中，匹配一條記錄
>system 表中只有一行記錄
>eq_ref 唯一索引掃描徽惋，只有一條記錄
>ref 非唯一索引掃描返回匹配的某個(gè)值
>range 質(zhì)檢所給定范圍的行案淋，使用一個(gè)索引來(lái)選擇 一般用于between <>
>index 只遍歷索引樹(shù)
>all 全表掃描
key 使用的索引
rows 估算掃描行數(shù)
filtered 有效行與掃描行的百分比 越高越好
extra 擴(kuò)展信息
>using where 檢索過(guò)程使用了where過(guò)濾 性能好
>using index 使用了覆蓋索引 性能好
>using filesort 使用了文件排序，sql語(yǔ)句用到了orderby 但是orderby的列沒(méi)有索引险绘，性能差
>using temporary 使用臨時(shí)表保存結(jié)果 性能極差 多見(jiàn)于groupby語(yǔ)句

什么是覆蓋索引

一次查詢使用的索引包含所有返回信息踢京，不需要回表

sql優(yōu)化

1. 盡量使用較低的隔離級(jí)別；
2. 精心設(shè)計(jì)索引宦棺， 并盡量使用索引訪問(wèn)數(shù)據(jù)瓣距， 使加鎖更精確， 從而減少鎖沖突的機(jī)會(huì)
3. 選擇合理的事務(wù)大小代咸，小事務(wù)發(fā)生鎖沖突的幾率也更小
4. 給記錄集顯示加鎖時(shí)蹈丸，最好一次性請(qǐng)求足夠級(jí)別的鎖。比如要修改數(shù)據(jù)的話侣背，最好直接申請(qǐng)排他鎖白华，而不是先申請(qǐng)共享鎖，修改時(shí)再請(qǐng)求排他鎖贩耐，這樣容易產(chǎn)生死鎖
5. 不同的程序訪問(wèn)一組表時(shí)弧腥，應(yīng)盡量約定以相同的順序訪問(wèn)各表，對(duì)一個(gè)表而言潮太，盡可能以固定的順序存取表中的行管搪。這樣可以大大減少死鎖的機(jī)會(huì)
6. 盡量用相等條件訪問(wèn)數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響
7. 不要申請(qǐng)超過(guò)實(shí)際需要的鎖級(jí)別
8. 除非必須铡买，查詢時(shí)不要顯示加鎖更鲁。 MySQL的MVCC可以實(shí)現(xiàn)事務(wù)中的查詢不用加鎖，優(yōu)化事務(wù)性能奇钞；MVCC只在COMMITTED READ（讀提交）和REPEATABLE READ（可重復(fù)讀）兩種隔離級(jí)別下工作
9. 對(duì)于一些特定的事務(wù)澡为，可以使用表鎖來(lái)提高處理速度或減少死鎖的可能

undolog redolog binlog relaylog

undolog 隱藏字段undolog指針，事務(wù)修改時(shí)景埃，老數(shù)據(jù)進(jìn)入undolog媒至，更新數(shù)據(jù)行上的undolog指針；用于事務(wù)回滾谷徙，和控制可見(jiàn)性拒啰；undolog是一段存儲(chǔ)空間，會(huì)被覆蓋丟棄
redolog 事物執(zhí)行期間完慧，記錄的事物修改的數(shù)據(jù)谋旦，存儲(chǔ)A->B；數(shù)據(jù)庫(kù)宕機(jī)時(shí)，回滾進(jìn)行中的事務(wù)
binlog 數(shù)據(jù)庫(kù)日志文件册着，用于記錄數(shù)據(jù)庫(kù)sql記錄拴孤，和數(shù)據(jù)主從同步，只記錄已提交的事務(wù)
relaylog 從庫(kù)維護(hù)的binlog緩沖指蚜，主從同步時(shí)乞巧，binglog進(jìn)入relaylog，從庫(kù)執(zhí)行relaylog的binlog

當(dāng)前讀和快照讀

當(dāng)前讀
當(dāng)前讀指的是讀取數(shù)據(jù)當(dāng)前最新數(shù)據(jù)摊鸡。update绽媒、insert、delete免猾、select for update（排他鎖）是辕、select lock in share mode。讀取數(shù)據(jù)需要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄猎提，會(huì)對(duì)讀取的記錄進(jìn)行加鎖获三。

快照讀
快照讀指的是在讀取數(shù)據(jù)時(shí)，生成讀取快照锨苏，在同一個(gè)事物中可能會(huì)一直讀取此快照的數(shù)據(jù)疙教。快照讀讀到的數(shù)據(jù)可能不是最新的伞租，可能是歷史版本的數(shù)據(jù)贞谓，這些歷史版本的數(shù)據(jù)就是從undo log中獲取的。
事物中的select 不加鎖的情況會(huì)執(zhí)行快照讀葵诈，快照讀依賴readview來(lái)實(shí)現(xiàn)裸弦。
快照讀的前提是隔離級(jí)別不是串行級(jí)別，串行級(jí)別下的快照讀會(huì)退化成當(dāng)前讀作喘。

MVCC可見(jiàn)性邏輯

多版本并發(fā)控制理疙，減少數(shù)據(jù)庫(kù)使用過(guò)程中的讀寫(xiě)沖突，無(wú)鎖泞坦。
ReadView 讀視圖窖贤，維護(hù)當(dāng)前活躍的事物列表trx_list，活躍的最小事務(wù)low_limit_id贰锁，即將生成的下一個(gè)事物up_limit_id主之，還有一個(gè)當(dāng)前事物trx_id
1.比較trx_id是否小于low_limit_id 或者為當(dāng)前事物ID，如果為true李根，則代表修改此行數(shù)據(jù)的事物早已提交或者就是當(dāng)前事務(wù)進(jìn)行的修改，當(dāng)前記錄可見(jiàn)几睛。否則進(jìn)入下一步判斷房轿。
2.比較trx_id是否大于等于up_limit_id，如果為true，則代表修改此行記錄的事物晚于當(dāng)前讀視圖創(chuàng)建囱持，當(dāng)前記錄不可見(jiàn)夯接，根據(jù)DB_ROLL_PTR undo log指針找到上一條記錄，從新進(jìn)行可見(jiàn)性分析纷妆。否則進(jìn)入下一步判斷
3.判斷trx_id是否在trx_list列表中盔几，如果在，代表修改此行記錄的事物還未提交掩幢，當(dāng)前事務(wù)不可以讀取當(dāng)前記錄逊拍，根據(jù)DB_ROLL_PTR undo log指針找到上一條記錄，從新進(jìn)行可見(jiàn)性分析际邻。否則說(shuō)明數(shù)據(jù)在readview生成的時(shí)候已經(jīng)提交芯丧，當(dāng)期事物可以讀取當(dāng)前記錄。

事物隔離級(jí)別與MVCC

在RR級(jí)別下世曾，事物進(jìn)行快照讀時(shí)會(huì)檢查當(dāng)前事物是否已經(jīng)創(chuàng)建過(guò)ReadView缨恒，如果存在，則使用已經(jīng)創(chuàng)建的轮听，這也是實(shí)現(xiàn)可重復(fù)讀的方法骗露。
在RC級(jí)別下，事物每一次快照讀都會(huì)創(chuàng)建一個(gè)新的ReadView血巍，這樣就會(huì)造成不可重復(fù)的和幻讀的問(wèn)題萧锉。

Mysql如何解決的幻讀

當(dāng)前讀 行鎖和間隙鎖
快照讀 MVCC

Mysql主從同步原理

事務(wù)提交之后，由主庫(kù)記入binlog藻茂，并同步給從庫(kù)驹暑，進(jìn)入從庫(kù)的relaylog，從庫(kù)從relaylog中讀取binlog辨赐，執(zhí)行sql优俘；
binlog 三種模式，statement rows mixed

replace和insert

replace語(yǔ)句在執(zhí)行過(guò)程中會(huì)嘗試insert掀序，而insert過(guò)程首先會(huì)檢查主鍵或唯一索引約束帆焕，insert語(yǔ)句的insert過(guò)程一旦檢測(cè)到?jīng)_突會(huì)直接拋出異常，replace語(yǔ)句的insert過(guò)程檢測(cè)到?jīng)_突會(huì)選擇進(jìn)行delete或直接update操作不恭；
repace語(yǔ)句在執(zhí)行insert過(guò)程中如果檢測(cè)到主鍵沖突叶雹，則會(huì)刪除沖突數(shù)據(jù)然后進(jìn)行插入操作；如果檢測(cè)到唯一約束沖突换吧，則直接進(jìn)行update操作折晦；
Mysql對(duì)replace和insert語(yǔ)句有優(yōu)化，多條insert操作會(huì)合并成insert into table values(),(),()…… binlog中只有一條沾瓦；多條replace操作會(huì)合并成replace into table values(),(),()…… binlog中只有一條满着；洗數(shù)據(jù)時(shí)盡量使用這兩種語(yǔ)法谦炒，可以有效降低主從延遲

Mysql中如何實(shí)現(xiàn)分頁(yè)

limit offset,size 語(yǔ)法，會(huì)有深度分頁(yè)問(wèn)題风喇，越來(lái)越慢宁改；推薦使用 >pxId limit 1000的寫(xiě)法，會(huì)利用主鍵索引

count(*) count(1) count(col)區(qū)別

count() 和count(1) 都是統(tǒng)計(jì)行數(shù)魂莫，而count(col) 是統(tǒng)計(jì)col列非null的行數(shù)
官方文當(dāng)說(shuō)count() 和count(1)的優(yōu)化是一樣的
count(col)慢是因?yàn)?要從索引樹(shù)中拿出來(lái)所有的該列判斷是否為空还蹲，沒(méi)有索引更慘，要全表掃描
反正用count(*)就對(duì)了