Mysql的存儲引擎

1.InnoDB存儲引擎：InnoDB存儲引擎支持事務(wù)，其設(shè)計目標(biāo)主要面向在線事務(wù)處理（OLTP）的應(yīng)用。其特點是行鎖設(shè)計，支持外鍵，并支持非鎖定鎖，即默認(rèn)讀取操作不會產(chǎn)生鎖。從Mysql5.5.8版本開始，InnoDB存儲引擎是默認(rèn)的存儲引擎。

2.MyISAM存儲引擎：MyISAM存儲引擎不支持事務(wù)、表鎖設(shè)計，支持全文索引，主要面向一些OLAP數(shù)據(jù)庫應(yīng)用。InnoDB的數(shù)據(jù)文件本身就是主索引文件，而MyISAM的主索引和數(shù)據(jù)是分開的。

3.NDB存儲引擎：NDB存儲引擎是一個集群存儲引擎，其結(jié)構(gòu)是share nothing的集群架構(gòu)，能提供更高的可用性。NDB的特點是數(shù)據(jù)全部放在內(nèi)存中（從MySQL 5.1版本開始，可以將非索引數(shù)據(jù)放在磁盤上），因此主鍵查找的速度極快，并且通過添加NDB數(shù)據(jù)存儲節(jié)點可以線性地提高數(shù)據(jù)庫性能，是高可用、高性能的集群系統(tǒng)。NDB存儲引擎的連接操作是在MySQL數(shù)據(jù)庫層完成的，而不是在存儲引擎層完成的。這意味著，復(fù)雜的連接操作需要巨大的網(wǎng)絡(luò)開銷，因此查詢速度很慢。如果解決了這個問題，NDB存儲引擎的市場應(yīng)該是非常巨大的。

4.Memory存儲引擎：Memory存儲引擎（之前稱HEAP存儲引擎）將表中的數(shù)據(jù)存放在內(nèi)存中，如果數(shù)據(jù)庫重啟或發(fā)生崩潰，表中的數(shù)據(jù)都將消失。它非常適合用于存儲臨時數(shù)據(jù)的臨時表，以及數(shù)據(jù)倉庫中的緯度表。Memory存儲引擎默認(rèn)使用哈希索引，而不是我們熟悉的B+樹索引。雖然Memory存儲引擎速度非常快，但在使用上還是有一定的限制。比如，只支持表鎖，并發(fā)性能較差，并且不支持TEXT和BLOB列類型。最重要的是，存儲變長字段時是按照定常字段的方式進(jìn)行的，因此會浪費內(nèi)存。

5.Archive存儲引擎：Archive存儲引擎只支持INSERT和SELECT操作，從MySQL 5.1開始支持索引。Archive存儲引擎使用zlib算法將數(shù)據(jù)行（row）進(jìn)行壓縮后存儲，壓縮比一般可達(dá)1∶10。正如其名字所示，Archive存儲引擎非常適合存儲歸檔數(shù)據(jù)，如日志信息。Archive存儲引擎使用行鎖來實現(xiàn)高并發(fā)的插入操作，但是其本身并不是事務(wù)安全的存儲引擎，其設(shè)計目標(biāo)主要是提供高速的插入和壓縮功能。

6.Maria存儲引擎：Maria存儲引擎是新開發(fā)的引擎，設(shè)計目標(biāo)主要是用來取代原有的MyISAM存儲引擎，從而成為MySQL的默認(rèn)存儲引擎。它可以看做是MyISAM的后續(xù)版本。Maria存儲引擎的特點是：支持緩存數(shù)據(jù)和索引文件，應(yīng)用了行鎖設(shè)計，提供了MVCC功能，支持事務(wù)和非事務(wù)安全的選項，以及更好的BLOB字符類型的處理性能。

事務(wù)的基本要素

原子性： 事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動作要么全部完成，要么完全不起作用；

一致性： 執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，多個事務(wù)對同一個數(shù)據(jù)讀取的結(jié)果是相同的；

隔離性： 并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨立的；

持久性： 一個事務(wù)被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。

什么是臟讀？幻讀？不可重復(fù)讀？

臟讀(Drity Read)：某個事務(wù)已更新一份數(shù)據(jù)，另一個事務(wù)在此時讀取了同一份數(shù)據(jù)，由于某些原因，前一個RollBack了操作，則后一個事務(wù)所讀取的數(shù)據(jù)就會是不正確的。

不可重復(fù)讀(Non-repeatable read):在一個事務(wù)的兩次查詢之中數(shù)據(jù)不一致，這可能是兩次查詢過程中間插入了一個事務(wù)更新的原有的數(shù)據(jù)。

幻讀(Phantom Read):在一個事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一致，例如有一個事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個事務(wù)卻在此時插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就會發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒有的。

事務(wù)隔離級別（必考）

READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會導(dǎo)致臟讀、幻讀或不可重復(fù)讀。

READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。

REPEATABLE-READ(可重復(fù)讀)： 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。

SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務(wù)依次逐個執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復(fù)讀以及幻讀。

SQL執(zhí)行順序

SQL的執(zhí)行順序：from---where--group by---having---select---order by

為什么選擇B+樹作為索引結(jié)構(gòu)（必考）

Hash索引：Hash索引底層是哈希表，哈希表是一種以key-value存儲數(shù)據(jù)的結(jié)構(gòu)，所以多個數(shù)據(jù)在存儲關(guān)系上是完全沒有任何順序關(guān)系的，所以，對于區(qū)間查詢是無法直接通過索引查詢的，就需要全表掃描。所以，哈希索引只適用于等值查詢的場景。而B+ 樹是一種多路平衡查詢樹，所以他的節(jié)點是天然有序的（左子節(jié)點小于父節(jié)點、父節(jié)點小于右子節(jié)點），所以對于范圍查詢的時候不需要做全表掃描

二叉查找樹：解決了排序的基本問題，但是由于無法保證平衡，可能退化為鏈表。

平衡二叉樹：通過旋轉(zhuǎn)解決了平衡的問題，但是旋轉(zhuǎn)操作效率太低。

紅黑樹：通過舍棄嚴(yán)格的平衡和引入紅黑節(jié)點，解決了 AVL旋轉(zhuǎn)效率過低的問題，但是在磁盤等場景下，樹仍然太高，IO次數(shù)太多。

B+樹：在B樹的基礎(chǔ)上，將非葉節(jié)點改造為不存儲數(shù)據(jù)純索引節(jié)點，進(jìn)一步降低了樹的高度；此外將葉節(jié)點使用指針連接成鏈表，范圍查詢更加高效。

索引B+樹的葉子節(jié)點都可以存哪些東西（必考）

可能存儲的是整行數(shù)據(jù)，也有可能是主鍵的值。B+樹的葉子節(jié)點存儲了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引。而索引B+ Tree的葉子節(jié)點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引

?查詢在什么時候不走（預(yù)期中的）索引（必考）

1.模糊查詢 %like

2.索引列參與計算,使用了函數(shù)

3.非最左前綴順序

4.where單列索引對null判斷

5.where不等于

6.or操作有至少一個字段沒有索引

7.需要回表的查詢結(jié)果集過大（超過配置的范圍）

覆蓋索引

指一個查詢語句的執(zhí)行只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。也可以稱之為實現(xiàn)了索引覆蓋。

為什么Mysql數(shù)據(jù)庫存儲不建議使用NULL

1. NOT IN子查詢在有NULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e。

2. 索引問題，單列索引無法存儲NULL值，where對null判斷會不走索引。

3. 如果在兩個字段進(jìn)行拼接（CONCAT函數(shù)），首先要各字段進(jìn)行非null判斷，否則只要任意一個字段為空都會造成拼接的結(jié)果為null

4. 如果有 Null column 存在的情況下，count(Null column)需要格外注意，null 值不會參與統(tǒng)計。

5. Null列需要更多的存儲空間：需要一個額外的字節(jié)作為判斷是否為NULL的標(biāo)志位

sql如何優(yōu)化

創(chuàng)建并使用正確的索引

只返回需要的字段

減少交互次數(shù)（批量提交）

設(shè)置合理的Fetch Size（數(shù)據(jù)每次返回給客戶端的條數(shù)）

explain命令概要

1.id:select選擇標(biāo)識符

2.select_type:表示查詢的類型。

3.table:輸出結(jié)果集的表

4.partitions:匹配的分區(qū)

5.type:表示表的連接類型

6.possible_keys:表示查詢時，可能使用的索引

7.key:表示實際使用的索引

8.key_len:索引字段的長度

9.ref:列與索引的比較

10.rows:掃描出的行數(shù)(估算的行數(shù))

11.filtered:按表條件過濾的行百分比

12.Extra:執(zhí)行情況的描述和說明

explain 中的 select_type（查詢的類型）

1.SIMPLE(簡單SELECT，不使用UNION或子查詢等)

2.PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)

3.UNION(UNION中的第二個或后面的SELECT語句)

4.DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢)

5.UNION RESULT(UNION的結(jié)果，union語句中第二個select開始后面所有select)

6.SUBQUERY(子查詢中的第一個SELECT，結(jié)果不依賴于外部查詢)

7.DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)

8.DERIVED(派生表的SELECT, FROM子句的子查詢)

9.UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)

explain 中的 type（表的連接類型）

1. system：最快，主鍵或唯一索引查找常量值，只有一條記錄，很少能出現(xiàn)

2. const：PK或者unique上的等值查詢

3. eq_ref：PK或者unique上的join查詢，等值匹配，對于前表的每一行(row)，后表只有一行命中

4. ref：非唯一索引，等值匹配，可能有多行命中

5. range：索引上的范圍掃描，例如：between/in

6. index：索引上的全集掃描，例如：InnoDB的count

7. ALL：最慢，全表掃描(full table scan)

explain 中的 Extra（執(zhí)行情況的描述和說明）

1. Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示 mysql服務(wù)器將在存儲引擎檢索行后再進(jìn)行過濾

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

3. Using filesort：當(dāng)Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”

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

5. Impossible where：這個值強(qiáng)調(diào)了where語句會導(dǎo)致沒有符合條件的行（通過收集統(tǒng)計信息不可能存在結(jié)果）。

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

7. No tables used：Query語句中使用from dual 或不含任何from子句

binlog,redolog,undolog都是什么，起什么作用

1.undoLog 也就是我們常說的回滾日志文件 主要用于事務(wù)中執(zhí)行失敗，進(jìn)行回滾，以及MVCC中對于數(shù)據(jù)歷史版本的查看。由引擎層的InnoDB引擎實現(xiàn),是邏輯日志,記錄數(shù)據(jù)修改被修改前的值,比如"把id='B' 修改為id = 'B2' ，那么undo日志就會用來存放id ='B'的記錄”。當(dāng)一條數(shù)據(jù)需要更新前,會先把修改前的記錄存儲在undolog中,如果這個修改出現(xiàn)異常,,則會使用undo日志來實現(xiàn)回滾操作,保證事務(wù)的一致性。當(dāng)事務(wù)提交之后，undo log并不能立馬被刪除,而是會被放到待清理鏈表中,待判斷沒有事物用到該版本的信息時才可以清理相應(yīng)undolog。它保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本，用于回滾，同時可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀。

2.redoLog 是重做日志文件是記錄數(shù)據(jù)修改之后的值，用于持久化到磁盤中。redo log包括兩部分：一是內(nèi)存中的日志緩沖(redo log buffer)，該部分日志是易失性的；二是磁盤上的重做日志文件(redo log file)，該部分日志是持久的。由引擎層的InnoDB引擎實現(xiàn),是物理日志,記錄的是物理數(shù)據(jù)頁修改的信息,比如“某個數(shù)據(jù)頁上內(nèi)容發(fā)生了哪些改動”。當(dāng)一條數(shù)據(jù)需要更新時,InnoDB會先將數(shù)據(jù)更新，然后記錄redoLog 在內(nèi)存中，然后找個時間將redoLog的操作執(zhí)行到磁盤上的文件上。不管是否提交成功我都記錄，你要是回滾了，那我連回滾的修改也記錄。它確保了事務(wù)的持久性。每個InnoDB存儲引擎至少有1個重做日志文件組（group），每個文件組下至少有2個重做日志文件，如默認(rèn)的ib_logfile0和ib_logfile1。為了得到更高的可靠性，用戶可以設(shè)置多個的鏡像日志組（mirrored log groups），將不同的文件組放在不同的磁盤上，以此提高重做日志的高可用性。在日志組中每個重做日志文件的大小一致，并以循環(huán)寫入的方式運行。InnoDB存儲引擎先寫重做日志文件1，當(dāng)達(dá)到文件的最后時，會切換至重做日志文件2，再當(dāng)重做日志文件2也被寫滿時，會再切換到重做日志文件1中。

3.MVCC多版本并發(fā)控制是MySQL中基于樂觀鎖理論實現(xiàn)隔離級別的方式，用于讀已提交和可重復(fù)讀取隔離級別的實現(xiàn)。在MySQL中，會在表中每一條數(shù)據(jù)后面添加兩個字段：最近修改該行數(shù)據(jù)的事務(wù)ID，指向該行（undolog表中）回滾段的指針。Read View判斷行的可見性，創(chuàng)建一個新事務(wù)時，copy一份當(dāng)前系統(tǒng)中的活躍事務(wù)列表。意思是，當(dāng)前不應(yīng)該被本事務(wù)看到的其他事務(wù)id列表。已提交讀隔離級別下的事務(wù)在每次查詢的開始都會生成一個獨立的ReadView,而可重復(fù)讀隔離級別則在第一次讀的時候生成一個ReadView，之后的讀都復(fù)用之前的ReadView。

?InnoDB的行鎖/表鎖

共享鎖(S)：用法lock in share mode，又稱讀鎖，允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。若事務(wù)T對數(shù)據(jù)對象A加上S鎖，則事務(wù)T可以讀A但不能修改A，其他事務(wù)只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這保證了其他事務(wù)可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。

排他鎖(X)：用法for update，又稱寫鎖，允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。若事務(wù)T對數(shù)據(jù)對象A加上X鎖，事務(wù)T可以讀A也可以修改A，其他事務(wù)不能再對A加任何鎖，直到T釋放A上的鎖。在沒有索引的情況下，InnoDB只能使用表鎖。

什么是死鎖？怎么解決？

死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。

常見的解決死鎖的方法

1、如果不同程序會并發(fā)存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機(jī)會。

2、在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；

3、對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產(chǎn)生的概率；

如果業(yè)務(wù)處理不好可以用分布式事務(wù)鎖或者使用樂觀鎖

數(shù)據(jù)庫的樂觀鎖和悲觀鎖是什么？怎么實現(xiàn)的？

數(shù)據(jù)庫管理系統(tǒng)（DBMS）中的并發(fā)控制的任務(wù)是確保在多個事務(wù)同時存取數(shù)據(jù)庫中同一數(shù)據(jù)時不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫的統(tǒng)一性。樂觀并發(fā)控制（樂觀鎖）和悲觀并發(fā)控制（悲觀鎖）是并發(fā)控制主要采用的技術(shù)手段。

悲觀鎖：假定會發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作。在查詢完數(shù)據(jù)的時候就把事務(wù)鎖起來，直到提交事務(wù)。實現(xiàn)方式：使用數(shù)據(jù)庫中的鎖機(jī)制

樂觀鎖：假設(shè)不會發(fā)生并發(fā)沖突，只在提交操作時檢查是否違反數(shù)據(jù)完整性。在修改數(shù)據(jù)的時候把事務(wù)鎖起來，通過version的方式來進(jìn)行鎖定。實現(xiàn)方式：樂一般會使用版本號機(jī)制或CAS算法實現(xiàn)。

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優(yōu)缺點，不可認(rèn)為一種好于另一種，像樂觀鎖適用于寫比較少的情況下（多讀場景），即沖突真的很少發(fā)生的時候，這樣可以省去了鎖的開銷，加大了系統(tǒng)的整個吞吐量。

但如果是多寫的情況，一般會經(jīng)常產(chǎn)生沖突，這就會導(dǎo)致上層應(yīng)用會不斷的進(jìn)行retry，這樣反倒是降低了性能，所以一般多寫的場景下用悲觀鎖就比較合適

Mysql讀寫分離以及主從同步

原理：主庫將變更寫binlog日志，然后從庫連接到主庫后，從庫有一個IO線程，將主庫的binlog日志拷貝到自己本地，寫入一個中繼日志中，接著從庫中有一個sql線程會從中繼日志讀取binlog，然后執(zhí)行binlog日志中的內(nèi)容，也就是在自己本地再執(zhí)行一遍sql，這樣就可以保證自己跟主庫的數(shù)據(jù)一致。

問題：這里有很重要一點，就是從庫同步主庫數(shù)據(jù)的過程是串行化的，也就是說主庫上并行操作，在從庫上會串行化執(zhí)行，由于從庫從主庫拷貝日志以及串行化執(zhí)行sql特點，在高并發(fā)情況下，從庫數(shù)據(jù)一定比主庫慢一點，是有延時的，所以經(jīng)常出現(xiàn)，剛寫入主庫的數(shù)據(jù)可能讀不到了，要過幾十毫秒，甚至幾百毫秒才能讀取到。還有一個問題，如果突然主庫宕機(jī)了，然后恰巧數(shù)據(jù)還沒有同步到從庫，那么有些數(shù)據(jù)可能在從庫上是沒有的，有些數(shù)據(jù)可能就丟失了。所以mysql實際上有兩個機(jī)制，一個是半同步復(fù)制，用來解決主庫數(shù)據(jù)丟失問題，一個是并行復(fù)制，用來解決主從同步延時問題。

半同步復(fù)制：semi-sync復(fù)制，指的就是主庫寫入binlog日志后，就會將強(qiáng)制此時立即將數(shù)據(jù)同步到從庫，從庫將日志寫入自己本地的relay log之后，接著會返回一個ack給主庫，主庫接收到至少一個從庫ack之后才會認(rèn)為寫完成。

并發(fā)復(fù)制：指的是從庫開啟多個線程，并行讀取relay log中不同庫的日志，然后并行重放不同庫的日志，這樣庫級別的并行。（將主庫分庫也可緩解延遲問題）