Mysql的Explain的參數(shù)？

答：

• Explain的作用：

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

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

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

這個(gè)參數(shù)的優(yōu)化在于你的查詢(xún)方式，如果發(fā)現(xiàn)type 在range以下，就必須優(yōu)化，想著自己會(huì)不會(huì)可能用錯(cuò)列查詢(xún)了，可以不可換成主鍵查詢(xún)?；蛘呤俏ㄒ恍运饕牧?。

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

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

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

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

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢(xún)中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

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

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

9. rows：

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

該列包含MySQL解決查詢(xún)的詳細(xì)信息,有以下幾種情況：

Using where:列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾

Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢(xún)

Using filesort：MySQL中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”

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

Impossible where：這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒(méi)有符合條件的行。

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

MySQL主從復(fù)制？

答：

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

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

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

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

圖例

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

• 配置過(guò)程：

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

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

主從復(fù)制延遲應(yīng)該如何解決？

答：

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

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

MySQL組提交？

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

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

• 在沒(méi)有開(kāi)啟binlog時(shí)： Redo log的刷盤(pán)操作將會(huì)是最終影響MySQL TPS的瓶頸所在。為了緩解這一問(wèn)題，MySQL使用了組提交，將多個(gè)刷盤(pán)操作合并成一個(gè)，如果說(shuō)10個(gè)事務(wù)依次排隊(duì)刷盤(pán)的時(shí)間成本是10，那么將這10個(gè)事務(wù)一次性一起刷盤(pán)的時(shí)間成本則近似于1。

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

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

Mutex_queue m_queue[STAGE_COUNTER];

如上，每個(gè)階段都在維護(hù)一個(gè)隊(duì)列，第一個(gè)進(jìn)入該隊(duì)列的作為 leader 線程，否則作為 follower 線程；leader 線程會(huì)收集 follower 的事務(wù)，并負(fù)責(zé)做 sync，follower 線程等待 leader 通知操作完成。

盡管維護(hù)了三個(gè)隊(duì)列，但隊(duì)列中所有的 THD 實(shí)際上都是通過(guò) next_to_commit 連接起來(lái)。binlog 在事務(wù)提交階段，也就是在 MYSQL_BIN_LOG::ordered_commit() 函數(shù)中，開(kāi)始 3 個(gè)階段的流程。

接下來(lái)，看看 MySQL 中事務(wù)是如何提交的。

2階段提交

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

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

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

但是存儲(chǔ)引擎提交的順序?yàn)?T2、T3、T1，當(dāng) T2、T3 提交事務(wù)之后做了一個(gè) On-line 的備份程序新建一個(gè) slave 來(lái)做復(fù)制；而搭建備庫(kù)時(shí)，CHANGE MASTER TO 的日志偏移量在 T3 事務(wù)之后。

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

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

• 解決方案：接下來(lái)，看看如何保證 binlog 寫(xiě)入順序和存儲(chǔ)引擎提交順序是一致的，并且能夠進(jìn)行 binlog 的組提交？5.6 引入了組提交，并將提交過(guò)程分成 Flush stage、Sync stage、Commit stage 三個(gè)階段。這樣，事務(wù)提交時(shí)分為了如下的階段：

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

每個(gè) Stage 階段都有各自的隊(duì)列，從而使每個(gè)會(huì)話的事務(wù)進(jìn)行排隊(duì)，提高并發(fā)性能。

如果當(dāng)一個(gè)線程注冊(cè)到一個(gè)空隊(duì)列時(shí)，該線程就做為該隊(duì)列的 leader，后注冊(cè)到該隊(duì)列的線程均為 follower，后續(xù)的操作，都由 leader 控制隊(duì)列中 follower 行為。

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

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

MySQL并行復(fù)制？

答：

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

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

上述機(jī)制實(shí)現(xiàn)了基于schema的并行復(fù)制存在問(wèn)題：并行復(fù)制效果并不高，如果用戶(hù)實(shí)例僅有一個(gè)庫(kù)，那么就無(wú)法實(shí)現(xiàn)并行回放，甚至性能會(huì)比原來(lái)的單線程更差。而單庫(kù)多表是比多庫(kù)多表更為常見(jiàn)的一種情形 。

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

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

sequence_number 是自增事務(wù) ID，last_commited 代表上一個(gè)提交的事務(wù) ID。

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

很顯然的，主庫(kù)能同時(shí)進(jìn)入prepare階段的事務(wù)之間不會(huì)沖突，那么這些事務(wù)在備庫(kù)回放時(shí)也不會(huì)沖突。因?yàn)槿绻麤_突了，先到的事務(wù)會(huì)獲取鎖，直到事務(wù)提交，這樣后面的事務(wù)也沒(méi)辦法執(zhí)行，所以同時(shí)進(jìn)入prepare的就一定是不會(huì)沖突的事務(wù)。

MySQL 5.7并行復(fù)制的思想簡(jiǎn)單易懂，一言以蔽之：** 一個(gè)組提交的事務(wù)都是可以并行回放 ，因?yàn)檫@些事務(wù)都已進(jìn)入到事務(wù)的prepare階段，則說(shuō)明事務(wù)之間沒(méi)有任何沖突（否則就不可能提交）。**

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

答：

1. 可以大大加快 數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
2. 索引可以幫助避免一些排序和臨時(shí)表的產(chǎn)生，因?yàn)樗饕旧砭褪怯行虻?/li>
3. 索引可以將隨機(jī)IO變?yōu)轫樞騃O，因?yàn)槲覀冞\(yùn)用索引的時(shí)候，可以通過(guò)B+樹(shù)的葉子節(jié)點(diǎn)去訪問(wèn)下一個(gè)隔壁的葉子節(jié)點(diǎn)，如果我們沒(méi)有索引，可能要在這個(gè)不同的地方去訪問(wèn)數(shù)據(jù)，順序IO的檢索速度更快。

為什么不建議使用過(guò)長(zhǎng)的字段建立索引？

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

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

答：
B 樹(shù)& B+樹(shù)兩者有何異同呢？

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

優(yōu)缺點(diǎn)：

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

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

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

MyISAM 和 InnoDB 的區(qū)別？

答：
1. 是否支持行級(jí)鎖

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

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

2. 是否支持事務(wù)
MyISAM 不提供事務(wù)支持。

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

3. 是否支持外鍵

MyISAM 不支持，而 InnoDB 支持。

4. 是否支持?jǐn)?shù)據(jù)庫(kù)異常崩潰后的安全恢復(fù)

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的數(shù)據(jù)庫(kù)在異常崩潰后，數(shù)據(jù)庫(kù)重新啟動(dòng)的時(shí)候會(huì)保證數(shù)據(jù)庫(kù)恢復(fù)到崩潰前的狀態(tài)。這個(gè)恢復(fù)的過(guò)程依賴(lài)于 redo log 。

拓展：

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

5. 是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

講真，這個(gè)對(duì)比有點(diǎn)廢話，畢竟 MyISAM 連行級(jí)鎖都不支持。

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

何為索引？有什么作用？

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

索引一定會(huì)回表嗎？

答：不一定，這涉及到查詢(xún)語(yǔ)句所要求的字段是否全部命中了索引，如果全部命中了索引，那么不需要進(jìn)行回表查詢(xún)。聚簇索引是不用回表的，因?yàn)橄鄳?yīng)的葉子節(jié)點(diǎn)就是對(duì)應(yīng)的數(shù)據(jù)

簡(jiǎn)述事務(wù)的特性(ACID)？

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

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

答：

概念分析

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

具體語(yǔ)句

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

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

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

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

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

答：

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

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

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

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

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

2 .第二范式（2NF）屬性完全依賴(lài)于主鍵

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

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

所以變成第三范式
（1）R0（用戶(hù)ID，用戶(hù)名，所屬學(xué)院）
（2）R2 (文章類(lèi)型編號(hào)，文章類(lèi)型名)
（3）R3（用戶(hù)ID，文章編號(hào)，文章內(nèi)容，文章收藏?cái)?shù)）
（4）R4(所屬學(xué)院，所屬學(xué)院院長(zhǎng))

4. BC范式：消除主屬性對(duì)于碼的依賴(lài)（3NF只是消除其他屬性對(duì)非主屬性的依賴(lài)）

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

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

至于為什么上面關(guān)系中我不將貨物數(shù)量加進(jìn)來(lái)，是因?yàn)橐坏┘舆M(jìn)來(lái)后那個(gè)關(guān)系就不符合第二范式了，想想看，如果加入貨物數(shù)量，那么主鍵就變成了(倉(cāng)庫(kù)編號(hào)，貨物編號(hào))，可是倉(cāng)庫(kù)管理員只與倉(cāng)庫(kù)編號(hào)有關(guān)，不依賴(lài)于貨物編號(hào)了呀，就不構(gòu)成對(duì)主鍵的完全依賴(lài)關(guān)系了。

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

1. 倉(cāng)庫(kù)與管理員表(倉(cāng)庫(kù)編號(hào)，倉(cāng)庫(kù)管理員編號(hào))
2. 倉(cāng)庫(kù)貨物表(倉(cāng)庫(kù)編號(hào)，貨物編號(hào)，貨物數(shù)量)

Mysql commit后是先寫(xiě)binlog還是redolog？

答：

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

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

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

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

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

可以看出，此過(guò)程中是先寫(xiě)redo log再寫(xiě)binlog的。但需要注意的是，在第一階段并沒(méi)有記錄完整的redo log（不包含事務(wù)的commit標(biāo)簽），而是在第二階段記錄完binlog后再寫(xiě)入redo log的commit 標(biāo)簽。還要注意的是，在這個(gè)過(guò)程中是以第二階段中binlog的寫(xiě)入與否作為事務(wù)是否成功提交的標(biāo)志。

兩階段事務(wù)圖

redolog中的事務(wù)如果經(jīng)歷了二階段提交中的prepare階段，則會(huì)打上prepare標(biāo)識(shí)，如果經(jīng)歷commit階段，則會(huì)打上commit標(biāo)識(shí)（此時(shí)redolog和binlog均已落盤(pán)）。

Step1. 按順序掃描redolog，如果redolog中的事務(wù)既有prepare標(biāo)識(shí)，又有commit標(biāo)識(shí)，就直接提交（復(fù)制redolog disk中的數(shù)據(jù)頁(yè)到磁盤(pán)數(shù)據(jù)頁(yè)）

Step2 .如果redolog事務(wù)只有prepare標(biāo)識(shí)，沒(méi)有commit標(biāo)識(shí)，則說(shuō)明當(dāng)前事務(wù)在commit階段crash了，binlog中當(dāng)前事務(wù)是否完整未可知，此時(shí)拿著redolog中當(dāng)前事務(wù)的XID（redolog和binlog中事務(wù)落盤(pán)的標(biāo)識(shí)），去查看binlog中是否存在此XID

a. 如果binlog中有當(dāng)前事務(wù)的XID，則提交事務(wù)（復(fù)制redolog disk中的數(shù)據(jù)頁(yè)到磁盤(pán)數(shù)據(jù)頁(yè)）

b. 如果binlog中沒(méi)有當(dāng)前事務(wù)的XID，則回滾事務(wù)（使用undolog來(lái)刪除redolog中的對(duì)應(yīng)事務(wù)）

什么是數(shù)據(jù)庫(kù)事務(wù)，MySQL 為什么會(huì)使用 InnoDB 作為默認(rèn)選項(xiàng)？

答：事務(wù)可以認(rèn)為是批量操作的總和，這批操作只能全部完成或者全部

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

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

答：

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

2. 優(yōu)化：

a)：如果在我們的主鍵是自增的，我們可以通過(guò)一個(gè)記錄前面第一頁(yè)的最后一條記錄的主鍵ID，然后在查詢(xún)條件的where后面的第一個(gè)位置加上（防止索引失效）where id>這個(gè)我們緩存的ID，因?yàn)樽栽龅脑颍覀円部梢詳喽ê竺娴腎D肯定是小于這個(gè) ID，并且通過(guò)主鍵走了索引，大大減少無(wú)效查詢(xún)，前面那些沒(méi)有必要的數(shù)據(jù)都省去了，但是這里就需要我們每次去存儲(chǔ)上一次分頁(yè)的最后一條數(shù)據(jù)的ID（或者通過(guò)子查詢(xún)，也就是下面的語(yǔ)句）

SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1)
LIMIT 2;
因?yàn)樽硬樵?xún)是在索引上完成的，而普通的查詢(xún)時(shí)在數(shù)據(jù)文件上完成的，通常來(lái)說(shuō)，索引文件要比數(shù)據(jù)文件小得多，所以操作起來(lái)也會(huì)更有效率。

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

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

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

mysql什么時(shí)候適合建索引，什么時(shí)候不適合?

答：

1. 當(dāng)某個(gè)列經(jīng)常是全表掃描的
2. 列的區(qū)分度不高，比如性別，只有男和女，這種區(qū)分度就不高，索引最多也就能幫你分出是男還是女
3. 大部分時(shí)間都是適合簡(jiǎn)歷索引的
4. 經(jīng)常Update， insert，delete的 表，因?yàn)樗饕木S護(hù)也是需要另外的空間的，如果你的表查詢(xún)非常的少，那么建立索引其實(shí)相當(dāng)于沒(méi)建立

mysql中的三種日志類(lèi)型？

答：binlog、redo log和undo log

日志詳解

注意binlog沒(méi)有crash safe的功能，因?yàn)閎inlog寫(xiě)入的時(shí)機(jī)跟redo log的時(shí)機(jī)不同，redo log是write ahead log

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

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

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

• 那 binlog 到底是用來(lái)干嘛的？

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

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

• MySQL 中數(shù)據(jù)是以頁(yè)為單位，你查詢(xún)一條記錄，會(huì)從硬盤(pán)把一頁(yè)的數(shù)據(jù)加載出來(lái)，加載出來(lái)的數(shù)據(jù)叫數(shù)據(jù)頁(yè)，會(huì)放入到 Buffer Pool 中。

• 后續(xù)的查詢(xún)都是先從 Buffer Pool 中找，沒(méi)有命中再去硬盤(pán)加載，減少硬盤(pán) IO 開(kāi)銷(xiāo)，提升性能。

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

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

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

  刷盤(pán)圖

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

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

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

答：

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

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

Buffer機(jī)制底層詳解

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

答：

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

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

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

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

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

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

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

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

場(chǎng)景：系統(tǒng)絕對(duì)并發(fā)量上來(lái)了，并且可以抽象出單獨(dú)的業(yè)務(wù)模塊。

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

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

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

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

一致性hash

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

答：

例圖

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

簡(jiǎn)述 MySQL 的主從同步機(jī)制，如果同步失敗會(huì)怎么樣？

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

解決：

• 方式一
  stop slave;

表示跳過(guò)一步錯(cuò)誤，后面的數(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是表鎖還是行鎖？

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

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

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

3、當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會(huì)使用行鎖來(lái)對(duì)數(shù)據(jù)加鎖。

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

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

如果我想要強(qiáng)制走某個(gè)索引，能實(shí)現(xiàn)嗎？

mysql強(qiáng)制索引和禁止某個(gè)索引

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

例如:

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

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

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

2、mysql禁止某個(gè)索引：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 '用戶(hù)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)會(huì)導(dǎo)致索引失效

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

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

• 按理說(shuō)，兩邊都是浮點(diǎn)數(shù)，那么應(yīng)該能使用索引，為什么執(zhí)行時(shí)沒(méi)有使用到索引？

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

MySQLWAL技術(shù)?

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

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