Mysql的Explain的參數(shù)？

答：

• Explain的作用：

1. 分析出表的讀取順序
2. 數(shù)據(jù)讀取操作的操作類型
3. 哪些索引可以使用
4. 哪些索引被實際使用
5. 表之間的引用
6. 每張表有多少行被優(yōu)化器查詢

• Explain的具體參數(shù)：

1. id id代表執(zhí)行select子句或操作表的順序,例如,上述的執(zhí)行結(jié)果代表只有一次執(zhí)行而且執(zhí)行順序是第一(因為只有一個id為1的執(zhí)行結(jié)果),id分別有三種不同的執(zhí)行結(jié)果,分別如下:（1）id相同,執(zhí)行順序由上至下（2）id不同,如果是子查詢,id的序號會遞增,id值越大,優(yōu)先級越高,越先被執(zhí)行（3）id相同和不同,同時存在,遵從優(yōu)先級高的優(yōu)先執(zhí)行,優(yōu)先級相同的按照由上至下的順序執(zhí)行
2. select_type：查詢的類型,主要用于區(qū)別普通查詢,聯(lián)合查詢,子查詢等復雜查詢
（1）simple:簡單的select查詢,查詢中不包含子查詢或union查詢
（2）primary:查詢中若包含任何復雜的子部分,最外層查詢則被標記為primary
（3）subquery:在select 或where 列表中包含了子查詢
（4）derived: 在from列表中包含的子查詢被標記為derived,mysql會遞歸這些子查詢,把結(jié)果放在臨時表里
（5）union:做第二個select出現(xiàn)在union之后,則被標記為union,若union包含在from子句的子查詢中,外層select將被標記為derived
（6）union result: 從union表獲取結(jié)果的select
3. table：顯示一行的數(shù)據(jù)時關(guān)于哪張表的
4. type：告訴我們對表使用的訪問方式，主要包含如下集中類型。
注意：查詢類型從最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情況下,得至少保證達到range級別,最好能達到ref

這個參數(shù)的優(yōu)化在于你的查詢方式，如果發(fā)現(xiàn)type 在range以下，就必須優(yōu)化，想著自己會不會可能用錯列查詢了，可以不可換成主鍵查詢。或者是唯一性索引的列。

（1）all： 全表掃描,是最差的一種查詢類型
（2） const：表示通過索引一次就找到了,const即常量,它用于比較primary key或unique索引,因為只匹配一行數(shù)據(jù),所以效率很快,如將主鍵置于where條件中,mysql就能將該查詢轉(zhuǎn)換為一個常量。
（3） eq_ref： 唯一性索引掃描,對于每個索引鍵,表中只有一條記錄與之匹配,常見于主鍵或唯一索引掃描
（4） fulltext： 進行全文索引檢索。
（5） index： index類型只遍歷索引樹,這通常比All快,因為索引文件通常比數(shù)據(jù)文件小,index是從索引中讀取,all從硬盤中讀取。
（6） range： 只檢索給定范圍的行,使用一個索引來選擇行,如where語句中出現(xiàn)了between,<,>,in等查詢,這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束于另一點，不用掃描全部索引。
（7） ref： 非唯一性索引掃描,返回匹配某個單獨值的行,它可能會找到多個符合條件的行,所以他應該屬于查找和掃描的混合體
（8） system：系統(tǒng)表，表中只有一行數(shù)據(jù)；
5. possible_keys：指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用

該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個適當?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

這里的優(yōu)化我們要想著，這里的索引有可能被用上，我們是否可以通過改寫SQL語句讓他用上索引，加快查詢速率。

6. key：
key列顯示MySQL實際決定使用的鍵（索引）

如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7. key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的）

不損失精確性的情況下，長度越短越好
8. ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

9. rows：

表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)
10. Extra：

該列包含MySQL解決查詢的詳細信息,有以下幾種情況：

Using where:列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾

Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢

Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”

Using join buffer：改值強調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，那應該注意，根據(jù)查詢的具體情況可能需要添加索引來改進能。

Impossible where：這個值強調(diào)了where語句會導致沒有符合條件的行。

Select tables optimized away：這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

MySQL主從復制？

答：

• 原理：根據(jù)二進制日志文件來實現(xiàn)，這個二進制日志是用來記錄一些數(shù)據(jù)庫變化的數(shù)據(jù)的。主庫中發(fā)生修改時，記錄到二進制文件中，然后從庫啟動一個IO線程去接受改變的二進制文件內(nèi)容，然后在用SQL線程去執(zhí)行，并且這里的主從復制分為兩種：基于行的復制，基于語句的復制，這兩種復制都是基于二進制日志。在實際應用中，MySQL是兩種混合用，如果基于語句的無法完成任務，那么就使用基于行的。

• 作用： 1、做數(shù)據(jù)的熱備，作為后備數(shù)據(jù)庫，主數(shù)據(jù)庫服務器故障后，可切換到從數(shù)據(jù)庫繼續(xù)工作，避免數(shù)據(jù)丟失。架構(gòu)的擴展。業(yè)務量越來越大,I/O訪問頻率過高，單機無法滿足，此時做多庫的存儲，降低磁盤I/O訪問的評率，提高單個機器的I/O性能。2、讀寫分離，使數(shù)據(jù)庫能支持更大的。在報表中尤其重要。由于部分報表sql語句非常的慢，導致鎖表，影響前臺服務。如果前臺使用master，報表使用slave，那么報表sql將不會造成前臺鎖，保證了前臺速度。

• 帶來的問題： 1、主從延遲：MySQL默認的主從復制是異步的，如果在主庫插?數(shù)據(jù)后?上去從庫查詢，可能會發(fā)?查不到的情況。正常情況下主從復制會存在毫秒級的延遲，在DB負載較?的情況下可能存在秒級延遲甚?更久，但即使是毫秒級的延遲，對于實時性要求較?的業(yè)務來說也是不可忽視的。所以在?些關(guān)鍵的查詢場景，我們會將查詢請求綁定到主庫來避免主從延遲的問題。2、從庫的數(shù)量是有限的：?個主庫能掛載的從庫數(shù)量是很有限的，沒辦法做到?限的?平擴展。從庫越多，雖然理論上能承受的QPS就越?，但是從庫過多會導致主庫主從復制IO壓?更?，造成更?的延遲，從?影響業(yè)務，所以?般來說只會在主庫后掛載有限的?個從庫。3、?法解決TPS?的問題：從庫雖然能解決QPS?的問題，但沒辦法解決TPS?的問題，所有的寫請求只有主庫能處理，?旦TPS過?，DB依然有宕機的?險。

1. MySql主庫在事務提交時會把數(shù)據(jù)變更作為事件記錄在二進制日志Binlog中；
2. 主庫推送二進制日志文件Binlog中的事件到從庫的中繼日志Relay Log中，之后從庫根據(jù)中繼日志重做數(shù)據(jù)變更操作，通過邏輯復制來達到主庫和從庫的數(shù)據(jù)一致性；
3. MySql通過三個線程來完成主從庫間的數(shù)據(jù)復制，其中Binlog Dump線程跑在主庫上，I/O線程和SQL線程跑著從庫上；
4. 當在從庫上啟動復制時，首先創(chuàng)建I/O線程連接主庫，主庫隨后創(chuàng)建Binlog Dump線程讀取數(shù)據(jù)庫事件并發(fā)送給I/O線程，I/O線程獲取到事件數(shù)據(jù)后更新到從庫的中繼日志Relay Log中去，之后從庫上的SQL線程讀取中繼日志Relay Log中更新的數(shù)據(jù)庫事件并應用，如下圖所示。

圖例

• 基于語句的復制（邏輯復制）：主庫會記錄那些造成數(shù)據(jù)更改的SQL，當備庫讀取并重放這些事件時，實際上只是把主庫上執(zhí)行過的SQL再執(zhí)行一遍。比如UPDATE enormous_table SET col1= O（更新全表）
  優(yōu)點：實現(xiàn)相當簡單,并且占用的資源少，比如更新上百萬條數(shù)據(jù)的SQL，他只需要記錄那個SQL語句，而不需要記錄整個改變的數(shù)據(jù)
  缺點：同一條SQL在主庫和備庫上執(zhí)行的時間可能稍微或很不相同，因此在傳輸?shù)亩M制日志中，除了查詢語句，還包括了一些元數(shù)據(jù)信息，如當前的時間戳。即便如此，還存在著一些無法被正確復制的SQL?？赡苓@個語句調(diào)用了獲取當前時間這個函數(shù)，但是因為主備庫執(zhí)行時間不同，那么很有可能導致數(shù)據(jù)不一致。
• 基于行的復制（物理復制）：這種方式會將實際數(shù)據(jù)記錄在二進制日志中。
  優(yōu)點：可以正確地復制每一行?？梢蕴幚砘谡Z句復制會出錯的情況
  缺點：對于一些全表操作的SQL，會很消耗資源，采用基于語句的會節(jié)省很多資源

• 配置過程：

1. 在主庫中先建立數(shù)據(jù)同步用戶，賦予相應的權(quán)限，讓備庫能從主庫中讀取數(shù)據(jù)。
2. 配置主庫和備庫，在My.cnf文件中配置，最主要的就是配置Server ID，這個ID必須全局唯一，另外還有開啟二進制日志

[mysqld]
## 設置server_id，同一局域網(wǎng)中需要唯一
server_id=102
## 指定不需要同步的數(shù)據(jù)庫名稱
binlog-ignore-db=mysql
## 開啟二進制日志功能，以備Slave作為其它數(shù)據(jù)庫實例的Master時使用
log-bin=mall-mysql-slave1-bin
## 設置二進制日志使用內(nèi)存大?。ㄊ聞眨?binlog_cache_size=1M
## 設置使用的二進制日志格式（mixed,statement,row）
binlog_format=mixed
## 二進制日志過期清理時間。默認值為0，表示不自動清理。
expire_logs_days=7
## 跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
## 如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致
slave_skip_errors=1062
## relay_log配置中繼日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave將復制事件寫進自己的二進制日志
log_slave_updates=1
## slave設置為只讀（具有super權(quán)限的用戶除外）
read_only=1

3. 在MySQL中用命令配置
   change master to master_host='192.168.6.132', master_user='slave', master_password='123456', master_port=3307, master_log_file='mall-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;

主從復制延遲應該如何解決？

答：

• 原因： 1、主庫DML請求頻繁：某些業(yè)務高峰期間，特別是對于數(shù)據(jù)庫主庫有大量的寫請求操作，即大量insert、delete、update等并發(fā)操作的情況下，會出現(xiàn)主從復制延時問題。在短時間產(chǎn)生了大量的binlog。這些操作需要全部同步到從庫，并且執(zhí)行，因此產(chǎn)生了主從的數(shù)據(jù)復制延時。從庫來不及同步 2、從庫自身壓力過大：有時候，從庫性能壓力很大的情況下，跟不上主庫的更新速度，就產(chǎn)生了主從復制延時。3、從庫的機器性能比主庫要差：跟不上主庫的速度

• 解決：1、降低多線程大事務并發(fā)的概率，優(yōu)化業(yè)務邏輯
  2、優(yōu)化SQL，避免慢SQL，減少批量操作，建議寫腳本以update-sleep這樣的形式完成。3、提高從庫機器的配置，減少主庫寫binlog和從庫讀binlog的效率差。4、盡量采用短的鏈路，也就是主庫和從庫服務器的距離盡量要短，提升端口帶寬，減少binlog傳輸?shù)木W(wǎng)絡延時。5、實時性要求的業(yè)務讀強制走主庫，從庫只做災備，備份。

MySQL組提交？

答：
redo log 落盤機制
mysql innodb刷盤參數(shù)解析
redo log 和 bin log組提交
組提交解決的問題
2pc 文章
MySQL binlog和redo的組提交

• 組提交（group_commit）:組提交 (group commit) 是為了優(yōu)化寫日志時的刷磁盤問題，從最初只支持 InnoDB redo log 組提交，到 5.6 官方版本同時支持 redo log 和 binlog 組提交，大大提高了 MySQL 的事務處理性能。

• 在沒有開啟binlog時： Redo log的刷盤操作將會是最終影響MySQL TPS的瓶頸所在。為了緩解這一問題，MySQL使用了組提交，將多個刷盤操作合并成一個，如果說10個事務依次排隊刷盤的時間成本是10，那么將這10個事務一次性一起刷盤的時間成本則近似于1。

• 當開啟binlog時： 因為主從數(shù)據(jù)庫一致性問題為了保證Redo log和binlog的數(shù)據(jù)一致性，MySQL使用了二階段提交，由binlog作為事務的協(xié)調(diào)者。而引入二階段提交使得binlog又成為了性能瓶頸，先前的Redo log組提交不能允許執(zhí)行，mysql 采用 鎖將redo log 和 bin log 串行化（因為如果你允許組提交，也就是說你提交的時候會順帶把其他組的redo log 也提交了，但是commit的順序可能不一樣，最終導致數(shù)據(jù)不一致，也就是說，事務的順序為 T1、T2、T3、commit順序為：c2、c3、c1、此時如果bin log寫完成了，但是commit沒完成到c1就關(guān)機了，重啟后就會回滾事務，但是從服務器的binlog還是有這個記錄的 。這里本質(zhì)就是redo log 和 commit順序不一致導致的）。為了再次緩解這一問題，MySQL增加了binlog的組提交，目的同樣是將binlog的多個刷盤操作合并成一個，結(jié)合Redo log本身已經(jīng)實現(xiàn)的 組提交，分為三個階段(Flush 階段、Sync 階段、Commit 階段)完成binlog 組提交，最大化每次刷盤的收益，弱化磁盤瓶頸，提高性能。

自 5.1 之后，binlog 和 innodb 采用類似兩階段提交的方式，不過不支持 group commit；在 5.6 中，將 binlog 的 commit 階段分為三個階段：flush stage、sync stage 以及 commit stage。這三個階段中，每個階段都會去維護一個隊列，各個列表的定義如下。

Mutex_queue m_queue[STAGE_COUNTER];

如上，每個階段都在維護一個隊列，第一個進入該隊列的作為 leader 線程，否則作為 follower 線程；leader 線程會收集 follower 的事務，并負責做 sync，follower 線程等待 leader 通知操作完成。

盡管維護了三個隊列，但隊列中所有的 THD 實際上都是通過 next_to_commit 連接起來。binlog 在事務提交階段，也就是在 MYSQL_BIN_LOG::ordered_commit() 函數(shù)中，開始 3 個階段的流程。

接下來，看看 MySQL 中事務是如何提交的。

2階段提交

以上提到單個事務的二階段提交過程，能夠保證 InnoDB 和 binlog 保持一致，但是在并發(fā)的情況下怎么保證存儲引擎和 binlog 提交的順序一致？當并發(fā)提交的時，如果兩者不一致會造成什么影響？

• 組提交異常：
  
  并發(fā)提交異常

如上所示，事務按照 T1、T2、T3 順序開始執(zhí)行，并依相同次序按照寫入 binlog 日志文件系統(tǒng)緩存，調(diào)用 fsync() 進行一次組提交，將日志文件永久寫入磁盤。

但是存儲引擎提交的順序為 T2、T3、T1，當 T2、T3 提交事務之后做了一個 On-line 的備份程序新建一個 slave 來做復制；而搭建備庫時，CHANGE MASTER TO 的日志偏移量在 T3 事務之后。

那么事務 T1 在備機恢復 MySQL 數(shù)據(jù)庫時，發(fā)現(xiàn) T1 未在存儲引擎內(nèi)提交，那么在恢復時，T1 事務就會被回滾，此時就會導致主備數(shù)據(jù)不一致。

• 結(jié)論：上圖的并發(fā)提交的時候，導致了prepare的順序和commit的順序不同，事務執(zhí)行的順序也就不同了，導致了數(shù)據(jù)錯誤，需要保證 binlog 的寫入順序和 InnoDB 事務提交順序一致，用于 xtrabackup 備份恢復。

• 解決方案：接下來，看看如何保證 binlog 寫入順序和存儲引擎提交順序是一致的，并且能夠進行 binlog 的組提交？5.6 引入了組提交，并將提交過程分成 Flush stage、Sync stage、Commit stage 三個階段。這樣，事務提交時分為了如下的階段：

1. InnoDB, Prepare：SQL已經(jīng)成功執(zhí)行并生成了相應的redo和undo內(nèi)存日志；
2. Binlog, Flush Stage：所有已經(jīng)注冊線程都將寫入binlog緩存；
3. Binlog, Sync Stage：binlog緩存將sync到磁盤，sync_binlog=1時該隊列中所有事務的binlog將永久寫入磁盤；
4. InnoDB, Commit stage：leader根據(jù)順序調(diào)用存儲引擎提交事務；

每個 Stage 階段都有各自的隊列，從而使每個會話的事務進行排隊，提高并發(fā)性能。

如果當一個線程注冊到一個空隊列時，該線程就做為該隊列的 leader，后注冊到該隊列的線程均為 follower，后續(xù)的操作，都由 leader 控制隊列中 follower 行為。

leader 同時會帶領(lǐng)當前隊列的所有 follower 到下一個 stage 去執(zhí)行，當遇到下一個 stage 為非空隊列時，leader 會變成 follower 注冊到此隊列中；注意：follower 線程絕不可能變成 leader 。

也就是利用隊列的有序行去解決順序不一致的問題，并且利用三個隊列提高并發(fā)性，如果是一個隊列的話會比較難處理，每一個隊列各司其職，責任清晰

MySQL并行復制？

答：

• 組提交（Group Commit）：是logic clock并行復制的基礎(chǔ)。Group Commit將所有的事務進行了分組，并為每個事務分配了last_committed和sequence_number。last_committed表示數(shù)據(jù)庫中上一個事務的提交編號，同一組事務的last_committed的值相同；sequence_number是順序增長的，每個事務對應一個序列號。

• MySQL5.6實現(xiàn)：MySQL 5.6版本開啟并行復制功能，那么SQL線程就變?yōu)榱薱oordinator線程，coordinator線程主要負責以前兩部分的內(nèi)容：1、若判斷可以并行執(zhí)行，那么選擇worker線程執(zhí)行事務的二進制日志 2、若判斷不可以并行執(zhí)行，如該操作是DDL，亦或者是事務跨schema操作，則等待所有的worker線程執(zhí)行完成之后，再執(zhí)行當前的日志。

上述機制實現(xiàn)了基于schema的并行復制存在問題：并行復制效果并不高，如果用戶實例僅有一個庫，那么就無法實現(xiàn)并行回放，甚至性能會比原來的單線程更差。而單庫多表是比多庫多表更為常見的一種情形 。

• MySQL5.7實現(xiàn)：MySQL 5.7才可稱為真正的并行復制，這其中最為主要的原因就是slave服務器的回放與主機是一致的即master服務器上是怎么并行執(zhí)行的slave上就怎樣進行并行回放。不再有庫的并行復制限制，對于二進制日志格式也無特殊的要求（基于庫的并行復制也沒有要求）

binlog 中記錄了 sequence_number 和 last_commited，如上圖，mysqlbinlog 解析日志可以看到這兩個值。

sequence_number 是自增事務 ID，last_commited 代表上一個提交的事務 ID。

如果兩個事務的 last_commited 相同，說明這兩個事務是在同一個 Group 內(nèi)提交的。

很顯然的，主庫能同時進入prepare階段的事務之間不會沖突，那么這些事務在備庫回放時也不會沖突。因為如果沖突了，先到的事務會獲取鎖，直到事務提交，這樣后面的事務也沒辦法執(zhí)行，所以同時進入prepare的就一定是不會沖突的事務。

MySQL 5.7并行復制的思想簡單易懂，一言以蔽之：** 一個組提交的事務都是可以并行回放 ，因為這些事務都已進入到事務的prepare階段，則說明事務之間沒有任何沖突（否則就不可能提交）。**

索引除了提升效率還有啥優(yōu)點？

答：

1. 可以大大加快 數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
2. 索引可以幫助避免一些排序和臨時表的產(chǎn)生，因為索引本身就是有序的
3. 索引可以將隨機IO變?yōu)轫樞騃O，因為我們運用索引的時候，可以通過B+樹的葉子節(jié)點去訪問下一個隔壁的葉子節(jié)點，如果我們沒有索引，可能要在這個不同的地方去訪問數(shù)據(jù)，順序IO的檢索速度更快。

為什么不建議使用過長的字段建立索引？

答：知道了InnoDB的索引實現(xiàn)后，就很容易明白為什么不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調(diào)的字段作為主鍵在InnoDB中不是個好主意，因為InnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，而使用自增字段作為主鍵則是一個很好的選擇。

B+樹和B樹的區(qū)別，為什么MySQL使用B+樹？

答：
B 樹& B+樹兩者有何異同呢？

B 樹的所有節(jié)點既存放鍵(key) 也存放 數(shù)據(jù)(data)，而 B+樹只有葉子節(jié)點存放 key 和 data，其他內(nèi)節(jié)點只存放 key。
B樹的葉子節(jié)點都是獨立的;B+樹的葉子節(jié)點有一條引用鏈指向與它相鄰的葉子節(jié)點。
B樹的檢索的過程相當于對范圍內(nèi)的每個節(jié)點的關(guān)鍵字做二分查找，可能還沒有到達葉子節(jié)點，檢索就結(jié)束了。而 B+樹的檢索效率就很穩(wěn)定了，任何查找都是從根節(jié)點到葉子節(jié)點的過程，葉子節(jié)點的順序檢索很明顯。

優(yōu)缺點：

1. B 樹檢索效率不穩(wěn)定，可能沒到葉子節(jié)點就結(jié)束了，而B+樹只有到葉子節(jié)點才有數(shù)據(jù)，查詢穩(wěn)定
2. B+樹有一條引用連向相鄰節(jié)點，更容易支持范圍搜索，而B+樹可能要不斷的遞歸搜索。
3. B+樹只在葉子節(jié)點存儲數(shù)據(jù)，那么這樣可以在增加在索引層的數(shù)量，減少B+樹的層數(shù)，使查詢更快

快照讀在提交讀和可重復讀級別下有什么區(qū)別？

答：在讀提交(RC)，可重復讀(RR)兩個不同的事務的隔離級別下，快照讀有什么不同呢？RC下，快照讀總是能讀到最新的行數(shù)據(jù)快照，當然，必須是已提交事務寫入的。RR下，某個事務首次read記錄的時間為T，未來不會讀取到T時間之后已提交事務寫入的記錄，以保證連續(xù)相同的read讀到相同的結(jié)果集。所以RC存在著幻讀和不可重復讀，而RP下全都解決了。

MyISAM 和 InnoDB 的區(qū)別？

答：
1. 是否支持行級鎖

MyISAM 只有表級鎖(table-level locking)，而 InnoDB 支持行級鎖(row-level locking)和表級鎖,默認為行級鎖。

也就說，MyISAM 一鎖就是鎖住了整張表，這在并發(fā)寫的情況下是多么滴憨憨??！這也是為什么 InnoDB 在并發(fā)寫的時候，性能更牛皮了！

2. 是否支持事務
MyISAM 不提供事務支持。

InnoDB 提供事務支持，具有提交(commit)和回滾(rollback)事務的能力

3. 是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

4. 是否支持數(shù)據(jù)庫異常崩潰后的安全恢復

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的數(shù)據(jù)庫在異常崩潰后，數(shù)據(jù)庫重新啟動的時候會保證數(shù)據(jù)庫恢復到崩潰前的狀態(tài)。這個恢復的過程依賴于 redo log 。

拓展：

MySQL InnoDB 引擎使用 redo log(重做日志) 保證事務的持久性，使用 undo log(回滾日志) 來保證事務的原子性。
MySQL InnoDB 引擎通過 鎖機制、MVCC 等手段來保證事務的隔離性（ 默認支持的隔離級別是 REPEATABLE-READ ）。
保證了事務的持久性、原子性、隔離性之后，一致性才能得到保障。

5. 是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

講真，這個對比有點廢話，畢竟 MyISAM 連行級鎖都不支持。

MVCC 可以看作是行級鎖的一個升級，可以有效減少加鎖操作，提供性能。

何為索引？有什么作用？

答：所謂索引就是用某種數(shù)據(jù)結(jié)構(gòu)來存儲我們的數(shù)據(jù)庫數(shù)據(jù)，讓我們查詢更加快。

索引一定會回表嗎？

答：不一定，這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么不需要進行回表查詢。聚簇索引是不用回表的，因為相應的葉子節(jié)點就是對應的數(shù)據(jù)

簡述事務的特性(ACID)？

答：
1. 原子性： 事務是最小的執(zhí)行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用；
2. 一致性：執(zhí)行事務前后，數(shù)據(jù)保持一致，例如轉(zhuǎn)賬業(yè)務中，無論事務是否成功，轉(zhuǎn)賬者和收款人的總額應該是不變的；（也就是臟讀問題）
3. 隔離性： 并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事務不被其他事務所干擾，各并發(fā)事務之間數(shù)據(jù)庫是獨立的；
4. 持久性： 一個事務被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應該對其有任何影響。

一條sql語句在mysql中如何執(zhí)行的？

答：

概念分析

• 連接器：身份認證和權(quán)限相關(guān)(登錄 MySQL 的時候)。主要負責用戶登錄數(shù)據(jù)庫，進行用戶的身份認證，包括校驗賬戶密碼，權(quán)限等操作。
• 查詢緩存：執(zhí)行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本后移除，因為這個功能不太實用，因為一般緩存我們會在其他層次去解決，另外查詢語句一般是多變的，而且數(shù)據(jù)也是多變的）。
• 分析器： 沒有命中緩存的話，SQL 語句就會經(jīng)過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。第一步，詞法分析，一條 SQL 語句有多個字符串組成，首先要提取關(guān)鍵字，比如 select，提出查詢的表，提出字段名，提出查詢條件等等。做完這些操作后，就會進入第二步。第二步，語法分析，主要就是判斷你輸入的 sql 是否正確，是否符合 MySQL 的語法。
• 優(yōu)化器：按照 MySQL 認為最優(yōu)的方案去執(zhí)行。比如多個索引的時候該如何選擇索引，多表查詢的時候如何選擇關(guān)聯(lián)順序等。
• 執(zhí)行器：執(zhí)行語句，然后從存儲引擎返回數(shù)據(jù)。

具體語句

• 查詢語句：權(quán)限校驗（如果命中緩存）--->查詢緩存--->分析器--->優(yōu)化器--->權(quán)限校驗--->執(zhí)行器--->引擎

• 更新語句：分析器---->權(quán)限校驗---->執(zhí)行器--->引擎---redo log(prepare 狀態(tài))--->binlog--->redo log(commit狀態(tài))

例子:update tb_student A set A.age='19' where A.name=' 張三 ';
其實這條語句也基本上會沿著上一個查詢的流程走，只不過執(zhí)行更新的時候肯定要記錄日志啦，這就會引入日志模塊了，MySQL 自帶的日志模塊是 binlog（歸檔日志） ，所有的存儲引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來探討這個語句的執(zhí)行流程。流程如下：

1. 先查詢到張三這一條數(shù)據(jù)，如果有緩存，也是會用到緩存。
   2 .然后拿到查詢的語句，把 age 改為 19，然后調(diào)用引擎 API 接口，寫入這一行數(shù)據(jù)，InnoDB 引擎把數(shù)據(jù)保存在內(nèi)存中，同時記錄 redo log，此時 redo log 進入 prepare 狀態(tài)，然后告訴執(zhí)行器，執(zhí)行完成了，隨時可以提交。
2. 執(zhí)行器收到通知后記錄 binlog，然后調(diào)用引擎接口，提交 redo log 為提交狀態(tài)。
3. 更新完成。

數(shù)據(jù)庫三范式和BC范式？

答：

1 .第一范式（1NF）:列不可再分

1.每一列屬性都是不可再分的屬性值，確保每一列的原子性
2.兩列的屬性相近或相似或一樣，盡量合并屬性一樣的列，確保不產(chǎn)生冗余數(shù)據(jù)
例子：如果我們有班級這個字段，（大三1班），但是我們的需求中需要知道年級，和班級分別是什么，那么就得分開。
比如說我們的數(shù)據(jù)庫需要存儲用戶發(fā)表的一篇文章數(shù)據(jù)，（我們只有這么一張表，用戶信息和其他信息都存在這里的）
(用戶ID，姓名，文章編號，文章內(nèi)容，文章類型編號，文章類型名，所屬學院，所屬學院院長，文章收藏數(shù))

3. 產(chǎn)生的問題：

（1）數(shù)據(jù)冗余：一個學生選了多門課程
(111、吳某、0、xxx、123、算法、互金、xxx、1000)
(111、吳某、1、xxx、321、后端、互金、xxx、1000)
我們可以看出學院和院長都是多余的

（2）插入異常：
那么現(xiàn)在來一個轉(zhuǎn)學生B，轉(zhuǎn)學生初來乍到還沒有發(fā)表文章，但是學生信息必須先錄入那該怎么辦呢？我們會發(fā)現(xiàn)我們遭遇了這樣的窘狀：
(111、吳某、null、null、后端、互金、xxx、1000)
當我們在錄入文章編號和文章名的時候，完全無法填寫，那么我們就無法插入新的記錄。造成了插入異常。
（3）刪除異常：
那么現(xiàn)在假設我們的B同學發(fā)表了一篇文章，然后不滿意刪除了：如果我們B同學恰恰只有了1篇文章，那么我們刪除的這條信息，也就將他整個從數(shù)據(jù)庫中刪除。我們發(fā)現(xiàn)，剛剛老師在刪除小B選的語文課記錄時，這個關(guān)系模式中同樣包含了小B的姓名學號等其他信息，當我們刪除這條記錄以后，可能會給我們帶來其他數(shù)據(jù)的損失，這就是刪除異常。
（4）更新異常：
如果我們有同學改個名，那么他所選的全部課程，也就都需要改名，這不合理，而且會導致資源消耗非常大。

2 .第二范式（2NF）屬性完全依賴于主鍵

1. 所以從函數(shù)依賴關(guān)系上，我們可以將原來的表分為
   （1）. R0（用戶ID，用戶名，所屬學院，所屬學院院長）
   （2）. R2 (文章類型編號，文章類型名)
   （3）. R3（用戶ID，文章編號，文章內(nèi)容，文章收藏數(shù)）
2. 存在的問題：同上，還是會導致數(shù)據(jù)冗余、插入異常、刪除異常、更新異常。因為還是有重復列

3 .第三范式（3NF）屬性不依賴于其它非主屬性 ，屬性直接依賴于主鍵

所以變成第三范式
（1）R0（用戶ID，用戶名，所屬學院）
（2）R2 (文章類型編號，文章類型名)
（3）R3（用戶ID，文章編號，文章內(nèi)容，文章收藏數(shù)）
（4）R4(所屬學院，所屬學院院長)

4. BC范式：消除主屬性對于碼的依賴（3NF只是消除其他屬性對非主屬性的依賴）

倉庫(倉庫編號，倉庫管理員編號，貨物編號，倉庫總?cè)藬?shù)，倉庫貨物總數(shù))
其中每一個倉庫管理員只管理一個倉庫。
那么我們可以發(fā)現(xiàn)這里其實主碼可以有兩種，分別是：

1. (倉庫編號) 可唯一確定 (倉庫管理員編號，倉庫總?cè)藬?shù)、倉庫貨物總數(shù))
2. (倉庫管理員編號) 可唯一確定 (倉庫編號，倉庫總?cè)藬?shù)、倉庫貨物總數(shù))
   必須要承認上述關(guān)系是符合第三范式的吧，但是有沒有覺得這樣倉庫管理員編號會出現(xiàn)大量的沒必要的冗余啊，因此BC范式就是解決這個問題的，需要將其改為兩個表，順便可以將貨物的數(shù)量加進來。

至于為什么上面關(guān)系中我不將貨物數(shù)量加進來，是因為一旦加進來后那個關(guān)系就不符合第二范式了，想想看，如果加入貨物數(shù)量，那么主鍵就變成了(倉庫編號，貨物編號)，可是倉庫管理員只與倉庫編號有關(guān)，不依賴于貨物編號了呀，就不構(gòu)成對主鍵的完全依賴關(guān)系了。

下面放上BC范式的修改版：

1. 倉庫與管理員表(倉庫編號，倉庫管理員編號)
2. 倉庫貨物表(倉庫編號，貨物編號，貨物數(shù)量)

Mysql commit后是先寫binlog還是redolog？

答：

• 假設一：先寫redo log再寫binlog
  想象一下，如果數(shù)據(jù)庫系統(tǒng)在寫完一個事務的redo log時發(fā)生crash，而此時這個事務的binlog還沒有持久化。在數(shù)據(jù)庫恢復后，主庫會根據(jù)redo log中去完成此事務的重做，主庫中就有可這個事務的數(shù)據(jù)。但是，由于此事務并沒有產(chǎn)生binlog，即使主庫恢復后，關(guān)于此事務的數(shù)據(jù)修改也不會同步到從庫上，這樣就產(chǎn)生了主從不一致的錯誤。（假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟。由于我們前面說過的，redo log 寫完之后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復回來，所以恢復后這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條語句。然后你會發(fā)現(xiàn)，如果需要用這個 binlog 來恢復臨時庫的話，由于這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同。）

• 假設二：先寫binlog再寫redo log
  想象一下，如果數(shù)據(jù)庫系統(tǒng)在寫完一個事務的binlog時發(fā)生crash，而此時這個事務的redo log還沒有持久化，或者說此事務的redo log還沒記錄完（至少沒有記錄commit log）。在數(shù)據(jù)庫恢復后，從庫會根據(jù)主庫中記錄的binlog去回放此事務的數(shù)據(jù)修改。但是，由于此事務并沒有產(chǎn)生完整提交的redo log，主庫在恢復后會回滾該事務，這樣也會產(chǎn)生主從不一致的錯誤。（如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復以后這個事務無效，所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個日志。所以，在之后用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同。）

通過上面的假設和分析，我們可以看出，不管是先寫redo log還是先寫binlog，都有可能會產(chǎn)生主從不一致的錯誤，那么MySQL又是怎么做到binlog和redo log的一致性的呢？

在MySQL內(nèi)部，在事務提交時利用兩階段提交(內(nèi)部XA的兩階段提交)很好地解決了上面提到的binlog和redo log的一致性問題：

1. 第一階段： InnoDB Prepare階段。此時SQL已經(jīng)成功執(zhí)行，并生成事務ID(xid)信息及redo和undo的內(nèi)存日志。此階段InnoDB會寫事務的redo log，但要注意的是，此時redo log只是記錄了事務的所有操作日志，并沒有記錄提交（commit）日志，因此事務此時的狀態(tài)為Prepare。此階段對binlog不會有任何操作。
2. 第二階段：commit 階段，這個階段又分成兩個步驟。第一步寫binlog（先調(diào)用write()將binlog內(nèi)存日志數(shù)據(jù)寫入文件系統(tǒng)緩存，再調(diào)用fsync()將binlog文件系統(tǒng)緩存日志數(shù)據(jù)永久寫入磁盤）；第二步完成事務的提交（commit），此時在redo log中記錄此事務的提交日志（增加commit 標簽）。

可以看出，此過程中是先寫redo log再寫binlog的。但需要注意的是，在第一階段并沒有記錄完整的redo log（不包含事務的commit標簽），而是在第二階段記錄完binlog后再寫入redo log的commit 標簽。還要注意的是，在這個過程中是以第二階段中binlog的寫入與否作為事務是否成功提交的標志。

兩階段事務圖

redolog中的事務如果經(jīng)歷了二階段提交中的prepare階段，則會打上prepare標識，如果經(jīng)歷commit階段，則會打上commit標識（此時redolog和binlog均已落盤）。

Step1. 按順序掃描redolog，如果redolog中的事務既有prepare標識，又有commit標識，就直接提交（復制redolog disk中的數(shù)據(jù)頁到磁盤數(shù)據(jù)頁）

Step2 .如果redolog事務只有prepare標識，沒有commit標識，則說明當前事務在commit階段crash了，binlog中當前事務是否完整未可知，此時拿著redolog中當前事務的XID（redolog和binlog中事務落盤的標識），去查看binlog中是否存在此XID

a. 如果binlog中有當前事務的XID，則提交事務（復制redolog disk中的數(shù)據(jù)頁到磁盤數(shù)據(jù)頁）

b. 如果binlog中沒有當前事務的XID，則回滾事務（使用undolog來刪除redolog中的對應事務）

什么是數(shù)據(jù)庫事務，MySQL 為什么會使用 InnoDB 作為默認選項？

答：事務可以認為是批量操作的總和，這批操作只能全部完成或者全部

a) 支持ACID，簡單地說就是支持事務完整性、一致性；
b) 支持行鎖，以及類似ORACLE的一致性讀，多用戶并發(fā)；
c) 獨有的聚集索引主鍵設計方式，可大幅提升并發(fā)讀寫性能；
d) 支持外鍵；
e) 支持崩潰數(shù)據(jù)自修復；

mysql分頁查詢和limit、offset原理和優(yōu)化？

答：

1. 原理：MySQL并不是跳過offset行，而是取offset+N行，然后返回放棄前offset行，返回N行，那當offset特別大的時候，效率就非常的低下，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進行SQL改寫。

2. 優(yōu)化：

a)：如果在我們的主鍵是自增的，我們可以通過一個記錄前面第一頁的最后一條記錄的主鍵ID，然后在查詢條件的where后面的第一個位置加上（防止索引失效）where id>這個我們緩存的ID，因為自增的原因，我們也可以斷定后面的ID肯定是小于這個 ID，并且通過主鍵走了索引，大大減少無效查詢，前面那些沒有必要的數(shù)據(jù)都省去了，但是這里就需要我們每次去存儲上一次分頁的最后一條數(shù)據(jù)的ID（或者通過子查詢，也就是下面的語句）

SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
LIMIT 2;
因為子查詢是在索引上完成的，而普通的查詢時在數(shù)據(jù)文件上完成的，通常來說，索引文件要比數(shù)據(jù)文件小得多，所以操作起來也會更有效率。

實際可以利用類似策略模式的方式去處理分頁，比如判斷如果是一百頁以內(nèi)，就使用最基本的分頁方式，大于一百頁，則使用子查詢的分頁方式。

b)：第二方案還是走索引，就是我們先根據(jù)條件查詢出對應數(shù)據(jù)對應的ID，然后再根據(jù)ID去查詢我們具體要的內(nèi)容，因為我的這里查詢的只是ID，而不是*，并且主鍵ID上有索引，所以這里并不會回表查詢更具體的數(shù)據(jù)。就減少了回表查詢的時間，然后我們再根據(jù)這些ID，作為條件去查出數(shù)據(jù)，這里主要優(yōu)化的就是將回表查詢的時間省去了，假如 現(xiàn)在是 limmit 300w 1 ，那么我們?nèi)绻凑赵瓉淼姆桨?，Mysql會將前300W的數(shù)據(jù)都查出來，并且會回表查詢出更具體的內(nèi)容，然后最后前300W數(shù)據(jù)都是丟棄的，只拿一條數(shù)據(jù)，這說明我們前面300W的回表查詢都是無用功。而我們現(xiàn)在的方式是，先根據(jù)條件查詢出對應的主鍵ID，這個過程也會進行查詢300W數(shù)據(jù)，但是不會回表，因為ID就是索引列，然后返回1條的ID，然后我們就直接根據(jù)這個1條的ID去查詢并回表，所以這里我們只回表了我們要的數(shù)據(jù)的那一次

SELECT * FROM tableName
WHERE id in (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
LIMIT 2;

mysql什么時候適合建索引，什么時候不適合?

答：

1. 當某個列經(jīng)常是全表掃描的
2. 列的區(qū)分度不高，比如性別，只有男和女，這種區(qū)分度就不高，索引最多也就能幫你分出是男還是女
3. 大部分時間都是適合簡歷索引的
4. 經(jīng)常Update， insert，delete的 表，因為索引的維護也是需要另外的空間的，如果你的表查詢非常的少，那么建立索引其實相當于沒建立

mysql中的三種日志類型？

答：binlog、redo log和undo log

日志詳解

注意binlog沒有crash safe的功能，因為binlog寫入的時機跟redo log的時機不同，redo log是write ahead log

1. binlog：redo log 它是物理日志，記錄內(nèi)容是“在某個數(shù)據(jù)頁上做了什么修改”，屬于 InnoDB 存儲引擎。

• 而 binlog 是邏輯日志，記錄內(nèi)容是語句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”，屬于MySQL Server 層。

• 不管用什么存儲引擎，只要發(fā)生了表數(shù)據(jù)更新，都會產(chǎn)生 binlog 日志。

• 那 binlog 到底是用來干嘛的？

• 可以說MySQL數(shù)據(jù)庫的數(shù)據(jù)備份、主備、主主、主從都離不開binlog，需要依靠binlog來同步數(shù)據(jù)，保證數(shù)據(jù)一致性。

2. redo log：redo log（重做日志）是InnoDB存儲引擎獨有的，它讓MySQL擁有了崩潰恢復能力。比如 MySQL 實例掛了或宕機了，重啟時，InnoDB存儲引擎會使用redo log恢復數(shù)據(jù)，保證數(shù)據(jù)的持久性與完整性。

• MySQL 中數(shù)據(jù)是以頁為單位，你查詢一條記錄，會從硬盤把一頁的數(shù)據(jù)加載出來，加載出來的數(shù)據(jù)叫數(shù)據(jù)頁，會放入到 Buffer Pool 中。

• 后續(xù)的查詢都是先從 Buffer Pool 中找，沒有命中再去硬盤加載，減少硬盤 IO 開銷，提升性能。

• 更新表數(shù)據(jù)的時候，也是如此，發(fā)現(xiàn) Buffer Pool 里存在要更新的數(shù)據(jù)，就直接在 Buffer Pool 里更新。

• 然后會把“在某個數(shù)據(jù)頁上做了什么修改”記錄到重做日志緩存（redo log buffer）里，接著刷盤到 redo log 文件里。

• 刷盤時機：InnoDB 存儲引擎為 redo log 的刷盤策略提供了 innodb_flush_log_at_trx_commit 參數(shù)，它支持三種策略：0 ：設置為 0 的時候，表示每次事務提交時不進行刷盤操作。1 ：設置為 1 的時候，表示每次事務提交時都將進行刷盤操作（默認值）。2 ：設置為 2 的時候，表示每次事務提交時都只把 redo log buffer 內(nèi)容寫入 page cache
  

  刷盤圖

3. undo log
   我們知道如果想要保證事務的原子性，就需要在異常發(fā)生時，對已經(jīng)執(zhí)行的操作進行回滾，在 MySQL 中，恢復機制是通過 回滾日志（undo log） 實現(xiàn)的，所有事務進行的修改都會先記錄到這個回滾日志中，然后再執(zhí)行相關(guān)的操作。如果執(zhí)行過程中遇到異常的話，我們直接利用 回滾日志 中的信息將數(shù)據(jù)回滾到修改之前的樣子即可！并且，回滾日志會先于數(shù)據(jù)持久化到磁盤上。這樣就保證了即使遇到數(shù)據(jù)庫突然宕機等情況，當用戶再次啟動數(shù)據(jù)庫的時候，數(shù)據(jù)庫還能夠通過查詢回滾日志來回滾將之前未完成的事務。

• 另外，MVCC 的實現(xiàn)依賴于：隱藏字段、Read View、undo log。在內(nèi)部實現(xiàn)中，InnoDB 通過數(shù)據(jù)行的 DB_TRX_ID 和 Read View 來判斷數(shù)據(jù)的可見性，如不可見，則通過數(shù)據(jù)行的 DB_ROLL_PTR 找到 undo log 中的歷史版本。每個事務讀到的數(shù)據(jù)版本可能是不一樣的，在同一個事務中，用戶只能看到該事務創(chuàng)建 Read View 之前已經(jīng)提交的修改和該事務本身做的修改
  三個日志參考鏈接

除了索引還有其他的嗎（做了什么優(yōu)化）查詢這么快？底層的buffer機制？

答：

1. 說到buffer機制，首先要說的就是底層數(shù)據(jù)庫讀取數(shù)據(jù)的方式，其實在行的基礎(chǔ)上，還有一個頁的概念，在底層Mysql讀數(shù)據(jù)是按頁讀取的。每個數(shù)據(jù)頁存放著多條的數(shù)據(jù)，MySQL在執(zhí)行增刪改首先會定位到這條數(shù)據(jù)所在數(shù)據(jù)頁，然后會將數(shù)據(jù)所在的數(shù)據(jù)頁加載到 Buffer Pool 中。這樣也可以說是預讀。磁盤讀寫，并不是按需讀取，而是按頁讀取，一次至少讀一頁數(shù)據(jù)（一般是4K），如果未來要讀取的數(shù)據(jù)就在頁中，就能夠省去后續(xù)的磁盤IO，提高效率（并且一般你讀那條數(shù)據(jù)的左右兩邊的數(shù)據(jù)也很可能會被讀）。
2. 然后是管理緩存頁的方式，也就是說如何去淘汰和移動緩存中的數(shù)據(jù)，這里MySQL使用的是LRU，但是傳統(tǒng)LRU有兩個問題：
3. 預讀失效：由于預讀 (Read-Ahead)，提前把頁放入了緩沖池，但最終 MySQL 并沒有從頁中讀取數(shù)據(jù)，稱為預讀失效。
   解決思路：（1）讓預讀失敗的頁，停留在緩沖池 LRU 里的時間盡可能短；（2）讓真正被讀取的頁，才挪到緩沖池 LRU 的頭部；
   解決方案：（1）將LRU將節(jié)點分為了新生代 (new sublist)，老生代 (old sublist)。（2）新老生代收尾相連，即：新生代的尾 (tail) 連接著老生代的頭 (head)；（3）新頁（例如被預讀的頁）加入緩沖池時，只加入到老生代頭部：如果數(shù)據(jù)真正被讀取（預讀成功），才會加入到新生代的頭部。如果數(shù)據(jù)沒有被讀取，則會比新生代里的 “熱數(shù)據(jù)頁” 更早被淘汰出緩沖池
   
   新生代與老年代

2. 緩沖池污染：當某一個 SQL 語句，要批量掃描大量數(shù)據(jù)時，可能導致把緩沖池的所有頁都替換出去，導致大量熱數(shù)據(jù)被換出，MySQL 性能急劇下降，這種情況叫緩沖池污染。也就是說有些數(shù)據(jù)雖然預讀成功了，但是他只讀取一次，就會導致原來那些一直被讀取的數(shù)據(jù)失效了
   解決思路：即使是預讀成功也不立即加入到頭部。而是在訪問T次后才放到頭部
   解決方案：1）假設 T = 老生代停留時間窗口；（2）插入老生代頭部的頁，即使立刻被訪問，并不會立刻放入新生代頭部；（3）只有滿足 “被訪問” 并且 “在老生代停留時間” 大于 T，才會被放入新生代頭部；

Buffer機制底層詳解

簡述數(shù)據(jù)庫中什么情況下進行分庫，什么情況下進行分表？

答：

1. 水平分庫：以字段為依據(jù)，按照一定策略（hash、range等），將一個庫中的數(shù)據(jù)拆分到多個庫中。

結(jié)果：每個庫的結(jié)構(gòu)都一樣；每個庫的數(shù)據(jù)都不一樣，沒有交集；所有庫的并集是全量數(shù)據(jù)；

場景：系統(tǒng)絕對并發(fā)量上來了，分表難以根本上解決問題，并且還沒有明顯的業(yè)務歸屬來垂直分庫。分析：庫多了，io和cpu的壓力自然可以成倍緩解。

2. 水平分表：以字段為依據(jù)，按照一定策略（hash、range等），將一個表中的數(shù)據(jù)拆分到多個表中。

結(jié)果：每個表的結(jié)構(gòu)都一樣；每個表的數(shù)據(jù)都不一樣，沒有交集；所有表的并集是全量數(shù)據(jù)；

場景：系統(tǒng)絕對并發(fā)量并沒有上來，只是單表的數(shù)據(jù)量太多，影響了SQL效率，加重了CPU負擔，以至于成為瓶頸。分析：表的數(shù)據(jù)量少了，單次SQL執(zhí)行效率高，自然減輕了CPU的負擔。

3. 垂直分庫：以表為依據(jù)，按照業(yè)務歸屬不同，將不同的表拆分到不同的庫中。

結(jié)果：每個庫的結(jié)構(gòu)都不一樣；每個庫的數(shù)據(jù)也不一樣，沒有交集；所有庫的并集是全量數(shù)據(jù)；

場景：系統(tǒng)絕對并發(fā)量上來了，并且可以抽象出單獨的業(yè)務模塊。

分析：到這一步，基本上就可以服務化了。例如，隨著業(yè)務的發(fā)展一些公用的配置表、字典表等越來越多，這時可以將這些表拆到單獨的庫中，甚至可以服務化。再有，隨著業(yè)務的發(fā)展孵化出了一套業(yè)務模式，這時可以將相關(guān)的表拆到單獨的庫中，甚至可以服務化

4. 垂直分表：以字段為依據(jù)，按照字段的活躍性，將表中字段拆到不同的表（主表和擴展表）中。

結(jié)果：每個表的結(jié)構(gòu)都不一樣；每個表的數(shù)據(jù)也不一樣，一般來說，每個表的字段至少有一列交集，一般是主鍵，用于關(guān)聯(lián)數(shù)據(jù)；所有表的并集是全量數(shù)據(jù)；
場景：系統(tǒng)絕對并發(fā)量并沒有上來，表的記錄并不多，但是字段多，并且熱點數(shù)據(jù)和非熱點數(shù)據(jù)在一起，單行數(shù)據(jù)所需的存儲空間較大。以至于數(shù)據(jù)庫緩存的數(shù)據(jù)行減少，查詢時會去讀磁盤數(shù)據(jù)產(chǎn)生大量的隨機讀IO，產(chǎn)生IO瓶頸。
分析：可以用列表頁和詳情頁來幫助理解。垂直分表的拆分原則是將熱點數(shù)據(jù)（可能會冗余經(jīng)常一起查詢的數(shù)據(jù)）放在一起作為主表，非熱點數(shù)據(jù)放在一起作為擴展表。這樣更多的熱點數(shù)據(jù)就能被緩存下來，進而減少了隨機讀IO。拆了之后，要想獲得全部數(shù)據(jù)就需要關(guān)聯(lián)兩個表來取數(shù)據(jù)。但記住，千萬別用join，因為join不僅會增加CPU負擔并且會將兩個表耦合在一起（必須在一個數(shù)據(jù)庫實例上）。關(guān)聯(lián)數(shù)據(jù)，應該在業(yè)務Service層做文章，分別獲取主表和擴展表數(shù)據(jù)然后用關(guān)聯(lián)字段關(guān)聯(lián)得到全部數(shù)據(jù)。

簡述一致性哈希算法的實現(xiàn)方式及原理？

一致性hash

聯(lián)合索引的存儲結(jié)構(gòu)是什么？

答：

例圖

最左前綴匹配原則也是如此，因為你聯(lián)合索引是按索引列的順序排的，只有第一個是全局有序的，而第二個只有在第一個值相同的情況下才會產(chǎn)生局部有序

簡述 MySQL 的主從同步機制，如果同步失敗會怎么樣？

答：1. 主從數(shù)據(jù)會不一致，這樣一般是從庫的進度要比主庫的慢。

解決：

• 方式一
  stop slave;

表示跳過一步錯誤，后面的數(shù)字可變

set global sql_slave_skip_counter =1;

start slave;

之后再用mysql> show slave status\G 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

select for update是表鎖還是行鎖？

當使用select ... for update ...where ...時，mysql進行row lock還是table lock只取決于是否能使用索引（例如主鍵，unique字段），能則為行鎖，否則為表鎖；未查到數(shù)據(jù)則無鎖。而 使用'<>','like'等操作時，索引會失效，自然進行的是table lock

1、InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。

2、由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。應用設計的時候要注意這一點。

3、當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖。

4、即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執(zhí)行計劃，以確認是否真正使用了索引。

5、檢索值的數(shù)據(jù)類型與索引字段不同，雖然MySQL能夠進行數(shù)據(jù)類型轉(zhuǎn)換，但卻不會使用索引，從而導致InnoDB使用表鎖。通過用explain檢查兩條SQL的執(zhí)行計劃，我們可以清楚地看到了這一點。

如果我想要強制走某個索引，能實現(xiàn)嗎？

mysql強制索引和禁止某個索引

1、mysql強制使用索引:force index(索引名或者主鍵PRI)

例如:

select * from table force index(PRI) limit 2;(強制使用主鍵)

select * from table force index(ziduan1_index) limit 2;(強制使用索引"ziduan1_index")

select * from table force index(PRI,ziduan1_index) limit 2;(強制使用索引"PRI和ziduan1_index")

2、mysql禁止某個索引：ignore index(索引名或者主鍵PRI)

例如:

select * from table ignore index(PRI) limit 2;(禁止使用主鍵)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

mysql的隱式轉(zhuǎn)換是否走索引？

答：

CREATE TABLE `user_message` (
  `user_id` varchar(50) NOT NULL COMMENT '用戶ID',
  `msg_id` int(11) NOT NULL COMMENT '消息ID',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;

上述結(jié)構(gòu)會導致索引失效

• 隱式轉(zhuǎn)換是什么？
  當算子兩邊的操作數(shù)類型不一致時，MySQL會發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容，這些轉(zhuǎn)換是隱式發(fā)生的。下面描述了比較操作的隱式轉(zhuǎn)換：
  1.如果一個或兩個參數(shù)均為NULL，則比較結(jié)果為NULL；但是 <=> 相等比較運算符除外，對于NULL <=> NULL，結(jié)果為true，無需轉(zhuǎn)換。

2. 如果比較操作中的兩個參數(shù)都是字符串，則將它們作為字符串進行比較。
3. 如果兩個參數(shù)都是整數(shù)，則將它們作為整數(shù)進行比較。
4. 如果十六進制不是和數(shù)字作比較，它會被視作是二進制字符串。
5. 如果參數(shù)之一是TIMESTAMP或DATETIME列，而另一個參數(shù)是常量，則在執(zhí)行比較之前，該常量將轉(zhuǎn)換為時間戳，但對于IN() 內(nèi)的參數(shù)不執(zhí)行此操作。為了安全起見，在進行比較時，請始終使用完整的時間、日期或時間字符串。例如，要在日期和時間參數(shù)上使用 BETWEEN 函數(shù)時，最好使用 CAST() 函數(shù)把參數(shù)顯示轉(zhuǎn)換成所需的數(shù)據(jù)類型。
6. 一個或多個表中的單行子查詢不視為常量。例如，如果子查詢返回的整數(shù)要與DATETIME值進行比較，則比較將作為兩個整數(shù)完成，子查詢返回的整數(shù)不轉(zhuǎn)換為時間值。參見上一條，這種情況下請使用CAST()將子查詢的結(jié)果整數(shù)值轉(zhuǎn)換為DATETIME。
7. 如果參數(shù)之一是十進制值，則比較取決于另一個參數(shù)。如果另一個參數(shù)是十進制或整數(shù)值，則將參數(shù)作為十進制值進行比較；如果另一個參數(shù)是浮點值，則將參數(shù)作為浮點值進行比較。
8. 在所有其他情況下，將參數(shù)作為浮點數(shù)（實數(shù)）進行比較。例如，將字符串和數(shù)字操作數(shù)進行比較，將其作為浮點數(shù)的比較。

• 按理說，兩邊都是浮點數(shù)，那么應該能使用索引，為什么執(zhí)行時沒有使用到索引？

MySQL在執(zhí)行我們的查詢SQL時，會 CAST 函數(shù)把每一行主鍵列的值轉(zhuǎn)換成浮點數(shù)，然后再與條件參數(shù)做比較。而 InnoDB 存儲引擎中，在索引列上使用函數(shù)會導致索引失效，所以最后導致了全表掃描。
我們只需要把 SQL 中 WHERE 條件改成字符串，就可以使用到主鍵索引了：

MySQLWAL技術(shù)?

答：
上文提到MySQL在執(zhí)行事務操作時，會先寫redo log，redo log是記錄數(shù)據(jù)修改時的物理操作，寫入redo log之后需要等數(shù)據(jù)真的執(zhí)行了物理操作之后再執(zhí)行下一步操作嗎？顯然不是這樣。redo log其實只是記錄如何操作物理數(shù)據(jù)的日志，MySQL通過寫redo log避免直接寫磁盤，大大提高了寫入速度，這個技術(shù)就是Write-Ahead Logging。

最后小結(jié)一下，大家可能會有疑惑WAL技術(shù)雖然不需要把更新的數(shù)據(jù)實時持久化，但是也需要寫日志，而日志本身也是持久化的，寫磁盤的次數(shù)似乎總體上并沒有減少，甚至可能增加，這種想法本身是正確的，但是WAL技術(shù)之所以能提高數(shù)據(jù)更新的效率，主要原因在于寫日志是一個順序讀寫的過程，而要更新的數(shù)據(jù)頁本身是隨機的；另一個原因是MySQL對于持久化redo log和binlog也做了優(yōu)化，使用了組提交減少fsync的次數(shù)。