來源丨經(jīng)授權(quán)轉(zhuǎn)自 三分惡（ID：Fighter3FullStack）
作者丨三分惡
基礎(chǔ) MySQ Logo 作為SQL Boy，基礎(chǔ)部分不會有人不會吧？面試也不怎么問，基礎(chǔ)掌握不錯的小伙伴可以跳過 這一部分。當然，可能會現(xiàn)場寫一些SQL語句，SQ語句可以通過?？?、LeetCode、LintCode之類的網(wǎng)站來練習(xí)。
1. 什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？ 內(nèi)連接（inner join）：取得兩張表中滿足存在連接匹配關(guān)系的記錄。 外連接（outer join）：不只取得兩張表中滿足存在連接匹配關(guān)系的記錄，還包括某張表（或兩張表）中不滿足匹配關(guān)系的記錄。 交叉連接（cross join）：顯示兩張表所有記錄一一對應(yīng)，沒有匹配關(guān)系進行篩選，它是笛卡爾積在SQL中的實現(xiàn)，如果A表有m行，B表有n行，那么A和B交叉連接的結(jié)果就有m*n行。 笛卡爾積：是數(shù)學(xué)中的一個概念，例如集合A={a,b}，集合B={1,2,3}，那么A??B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。 2. 那MySQL 的內(nèi)連接、左連接、右連接有有什么區(qū)別？ MySQL的連接主要分為內(nèi)連接和外連接，外連接常用的有左連接、右連接。
MySQL-joins-來源菜鳥教程 inner join 內(nèi)連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結(jié)果集 left join 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。 right join 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。 3.說一下數(shù)據(jù)庫的三大范式？ 數(shù)據(jù)庫三范式 第一范式：數(shù)據(jù)表中的每一列（每個字段）都不可以再拆分。例如用戶表，用戶地址還可以拆分成國家、省份、市，這樣才是符合第一范式的。 第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。例如訂單表里，存儲了商品信息（商品價格、商品類型），那就需要把商品ID和訂單ID作為聯(lián)合主鍵，才滿足第二范式。 第三范式：在滿足第二范式的基礎(chǔ)上，表中的非主鍵只依賴于主鍵，而不依賴于其他非主鍵。例如訂單表，就不能存儲用戶信息（姓名、地址）。 你設(shè)計遵守范式嗎？ 三大范式的作用是為了控制數(shù)據(jù)庫的冗余，是對空間的節(jié)省，實際上，一般互聯(lián)網(wǎng)公司的設(shè)計都是反范式的，通過冗余一些數(shù)據(jù)，避免跨表跨庫，利用空間換時間，提高性能。
4.varchar與char的區(qū)別？ varchar char ：
如果插入數(shù)據(jù)的長度小于char的固定長度時，則用空格填充； 因為長度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因為其長度固定，所以會占據(jù)多余的空間，是空間換時間的做法； 對于char來說，最多能存放的字符個數(shù)為255，和編碼無關(guān) varchar ：
插入的數(shù)據(jù)是多長，就按照多長來存儲； varchar在存取方面與char相反，它存取慢，因為長度不固定，但正因如此，不占據(jù)多余的空間，是時間換空間的做法； 對于varchar來說，最多能存放的字符個數(shù)為65532 日常的設(shè)計，對于長度相對固定的字符串，可以使用char，對于長度不確定的，使用varchar更合適一些。
5.blob和text有什么區(qū)別？ blob用于存儲二進制數(shù)據(jù)，而text用于存儲大字符串。 blob沒有字符集，text有一個字符集，并且根據(jù)字符集的校對規(guī)則對值進行排序和比較 6.DATETIME和TIMESTAMP的異同？ 相同點 ：
兩個數(shù)據(jù)類型存儲時間的表現(xiàn)格式一致。均為 YYYY-MM-DD HH:MM:SS 兩個數(shù)據(jù)類型都包含「日期」和「時間」部分。 兩個數(shù)據(jù)類型都可以存儲微秒的小數(shù)秒（秒后6位小數(shù)秒） 區(qū)別 ：
DATETIME和TIMESTAMP的區(qū)別 日期范圍 ：DATETIME 的日期范圍是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的時間范圍是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
存儲空間 ：DATETIME 的存儲空間為 8 字節(jié)；TIMESTAMP 的存儲空間為 4 字節(jié)
時區(qū)相關(guān) ：DATETIME 存儲時間與時區(qū)無關(guān)；TIMESTAMP 存儲時間與時區(qū)有關(guān)，顯示的值也依賴于時區(qū)
默認值 ：DATETIME 的默認值為 null；TIMESTAMP 的字段默認不為空(not null)，默認值為當前時間(CURRENT_TIMESTAMP)
7.MySQL中 in 和 exists 的區(qū)別？ MySQL中的in語句是把外表和內(nèi)表作hash 連接，而exists語句是對外表作loop循環(huán)，每次loop循環(huán)再對內(nèi)表進行查詢。我們可能認為exists比in語句的效率要高，這種說法其實是不準確的，要區(qū)分情景：
如果查詢的兩個表大小相當，那么用in和exists差別不大。 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。 not in 和not exists：如果查詢語句使用了not in，那么內(nèi)外表都進行全表掃描，沒有用到索引；而not extsts的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。 8.MySQL里記錄貨幣用什么字段類型比較好？ 貨幣在數(shù)據(jù)庫中MySQL常用Decimal和Numric類型表示，這兩種類型被MySQL實現(xiàn)為同樣的類型。他們被用于保存與貨幣有關(guān)的數(shù)據(jù)。
例如salary DECIMAL(9,2)，9(precision)代表將被用于存儲值的總的小數(shù)位數(shù)，而2(scale)代表將被用于存儲小數(shù)點后的位數(shù)。存儲在salary列中的值的范圍是從-9999999.99到9999999.99。
DECIMAL和NUMERIC值作為字符串存儲，而不是作為二進制浮點數(shù)，以便保存那些值的小數(shù)精度。
之所以不使用float或者double的原因：因為float和double是以二進制存儲的，所以有一定的誤差。
9.MySQL怎么存儲emoji??? MySQL可以直接使用字符串存儲emoji。
但是需要注意的，utf8 編碼是不行的，MySQL中的utf8是閹割版的 utf8，它最多只用 3 個字節(jié)存儲字符，所以存儲不了表情。那該怎么辦？
需要使用utf8mb4編碼。
alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null ;
10.drop、delete與truncate的區(qū)別？ 三者都表示刪除，但是三者有一些差別：
delete truncate drop 類型 屬于DML 屬于DDL 屬于DDL 回滾 可回滾 不可回滾 不可回滾 刪除內(nèi)容 表結(jié)構(gòu)還在，刪除表的全部或者一部分數(shù)據(jù)行 表結(jié)構(gòu)還在，刪除表中的所有數(shù)據(jù) 從數(shù)據(jù)庫中刪除表，所有數(shù)據(jù)行，索引和權(quán)限也會被刪除 刪除速度 刪除速度慢，需要逐行刪除 刪除速度快 刪除速度最快
因此，在不再需要一張表的時候，用drop；在想刪除部分數(shù)據(jù)行時候，用delete；在保留表而刪除所有數(shù)據(jù)的時候用truncate。
11.UNION與UNION ALL的區(qū)別？ 如果使用UNION ALL，不會合并重復(fù)的記錄行 12.count(1)、count(*) 與 count(列名) 的區(qū)別？ 三種計數(shù)方式 執(zhí)行效果 ：
count(*)包括了所有的列，相當于行數(shù)，在統(tǒng)計結(jié)果的時候，不會忽略列值為NULL
count(1)包括了忽略所有列，用1代表代碼行，在統(tǒng)計結(jié)果的時候，不會忽略列值為NULL
count(列名)只包括列名那一列，在統(tǒng)計結(jié)果的時候，會忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計數(shù)，即某個字段值為NULL時，不統(tǒng)計。
執(zhí)行速度 ：
列名為主鍵，count(列名)會比count(1)快 列名不為主鍵，count(1)會比count(列名)快 如果表多個列并且沒有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于 count（*） 如果有主鍵，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的 如果表只有一個字段，則 select count（*）最優(yōu)。 13.一條SQL查詢語句的執(zhí)行順序？ 查詢語句執(zhí)行順序 FROM ：對FROM子句中的左表<left_table>和右表<right_table>執(zhí)行笛卡兒積（Cartesianproduct），產(chǎn)生虛擬表VT1
ON ：對虛擬表VT1應(yīng)用ON篩選，只有那些符合<join_condition>的行才被插入虛擬表VT2中
JOIN ：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3。如果FROM子句包含兩個以上表，則對上一個連接生成的結(jié)果表VT3和下一個表重復(fù)執(zhí)行步驟1）～步驟3），直到處理完所有的表為止
WHERE ：對虛擬表VT3應(yīng)用WHERE過濾條件，只有符合<where_condition>的記錄才被插入虛擬表VT4中
GROUP BY ：根據(jù)GROUP BY子句中的列，對VT4中的記錄進行分組操作，產(chǎn)生VT5
CUBE|ROLLUP ：對表VT5進行CUBE或ROLLUP操作，產(chǎn)生表VT6
HAVING ：對虛擬表VT6應(yīng)用HAVING過濾器，只有符合<having_condition>的記錄才被插入虛擬表VT7中。
SELECT ：第二次執(zhí)行SELECT操作，選擇指定的列，插入到虛擬表VT8中
DISTINCT ：去除重復(fù)數(shù)據(jù)，產(chǎn)生虛擬表VT9
ORDER BY ：將虛擬表VT9中的記錄按照<order_by_list>進行排序操作，產(chǎn)生虛擬表VT10。11）
LIMIT ：取出指定行的記錄，產(chǎn)生虛擬表VT11，并返回給查詢用戶
數(shù)據(jù)庫架構(gòu) 14.說說 MySQL 的基礎(chǔ)架構(gòu)? 在這里插入圖片描述 MySQL邏輯架構(gòu)圖主要分三層：
客戶端：最上層的服務(wù)并不是MySQL所獨有的，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)。比如連接處理、授權(quán)認證、安全等等。 Server層：大多數(shù)MySQL的核心服務(wù)功能都在這一層，包括查詢解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時間、數(shù)學(xué)和加密函數(shù)），所有跨存儲引擎的功能都在這一層實現(xiàn)：存儲過程、觸發(fā)器、視圖等。 存儲引擎層：第三層包含了存儲引擎。存儲引擎負責(zé)MySQL中數(shù)據(jù)的存儲和提取。Server層通過API與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。 15.一條 SQL 查詢語句在 MySQL 中如何執(zhí)行的？ 先檢查該語句是否有權(quán)限，如果沒有權(quán)限，直接返回錯誤信息，如果有權(quán)限會先查詢緩存 (MySQL8.0 版本以前)。 如果沒有緩存，分析器進行語法分析，提取 sql 語句中 select 等關(guān)鍵元素，然后判斷 sql 語句是否有語法錯誤，比如關(guān)鍵詞是否正確等等。 語法解析之后，MySQL的服務(wù)器會對查詢的語句進行優(yōu)化，確定執(zhí)行的方案。 完成查詢優(yōu)化后，按照生成的執(zhí)行計劃調(diào)用數(shù)據(jù)庫引擎接口，返回執(zhí)行結(jié)果。 存儲引擎 16.MySQL有哪些常見存儲引擎？ 主要存儲引擎 主要存儲引擎以及功能如下：
功能 MylSAM MEMORY InnoDB 存儲限制 256TB RAM 64TB 支持事務(wù) No No Yes 支持全文索引 Yes No Yes 支持樹索引 Yes Yes Yes 支持哈希索引 No Yes Yes 支持數(shù)據(jù)緩存 No N/A Yes 支持外鍵 No No Yes
MySQL5.5之前，默認存儲引擎是MylSAM，5.5之后變成了InnoDB。
InnoDB支持的哈希索引是自適應(yīng)的，InnoDB會根據(jù)表的使用情況自動為表生成哈希索引，不能人為干預(yù)是否在一張表中生成哈希索引。
MySQL 5.6開始InnoDB支持全文索引。
17.那存儲引擎應(yīng)該怎么選擇？ 大致上可以這么選擇：
大多數(shù)情況下，使用默認的InnoDB就夠了。如果要提供提交、回滾和恢復(fù)的事務(wù)安全（ACID 兼容）能力，并要求實現(xiàn)并發(fā)控制，InnoDB 就是比較靠前的選擇了。 如果數(shù)據(jù)表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率。 如果只是臨時存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時表，存放查詢的中間結(jié)果。 使用哪一種引擎可以根據(jù)需要靈活選擇，因為存儲引擎是基于表的，所以一個數(shù)據(jù)庫中多個表可以使用不同的引擎以滿足各種性能和實際需求。使用合適的存儲引擎將會提高整個數(shù)據(jù)庫的性能。
18.InnoDB和MylSAM主要有什么區(qū)別？ PS:MySQL8.0都開始慢慢流行了，如果不是面試，MylSAM其實可以不用怎么了解。
InnoDB和MylSAM主要有什么區(qū)別 1. 存儲結(jié)構(gòu) ：每個MyISAM在磁盤上存儲成三個文件；InnoDB所有的表都保存在同一個數(shù)據(jù)文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。
2. 事務(wù)支持 ：MyISAM不提供事務(wù)支持；InnoDB提供事務(wù)支持事務(wù)，具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全特性。
3 最小鎖粒度 ：MyISAM只支持表級鎖，更新時會鎖住整張表，導(dǎo)致其它查詢和更新都會被阻塞InnoDB支持行級鎖。
4. 索引類型 ：MyISAM的索引為聚簇索引，數(shù)據(jù)結(jié)構(gòu)是B樹；InnoDB的索引是非聚簇索引，數(shù)據(jù)結(jié)構(gòu)是B+樹。
5. 主鍵必需 ：MyISAM允許沒有任何索引和主鍵的表存在；InnoDB如果沒有設(shè)定主鍵或者非空唯一索引，**就會自動生成一個6字節(jié)的主鍵(用戶不可見)**，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。
6. 表的具體行數(shù) ：MyISAM保存了表的總行數(shù)，如果select count(*) from table;會直接取出出該值; InnoDB沒有保存表的總行數(shù)，如果使用select count(*) from table；就會遍歷整個表;但是在加了wehre條件后，MyISAM和InnoDB處理的方式都一樣。
7. 外鍵支持 ：MyISAM不支持外鍵；InnoDB支持外鍵。
日志 19.MySQL日志文件有哪些？分別介紹下作用？ MySQL主要日志 MySQL日志文件有很多，包括 ：
錯誤日志 （error log）：錯誤日志文件對MySQL的啟動、運行、關(guān)閉過程進行了記錄，能幫助定位MySQL問題。慢查詢?nèi)罩?/strong>（slow query log）：慢查詢?nèi)罩臼怯脕碛涗泩?zhí)行時間超過 long_query_time 這個變量定義的時長的查詢語句。通過慢查詢?nèi)罩荆梢圆檎页瞿男┎樵冋Z句的執(zhí)行效率很低，以便進行優(yōu)化。 一般查詢?nèi)罩?/strong>（general log）：一般查詢?nèi)罩居涗浟怂袑ySQL數(shù)據(jù)庫請求的信息，無論請求是否正確執(zhí)行。 二進制日志 （bin log）：關(guān)于二進制日志，它記錄了數(shù)據(jù)庫所有執(zhí)行的DDL和DML語句（除了數(shù)據(jù)查詢語句select、show等），以事件形式記錄并保存在二進制文件中。還有兩個InnoDB存儲引擎特有的日志文件：
重做日志 （redo log）：重做日志至關(guān)重要，因為它們記錄了對于InnoDB存儲引擎的事務(wù)日志。回滾日志 （undo log）：回滾日志同樣也是InnoDB引擎提供的日志，顧名思義，回滾日志的作用就是對數(shù)據(jù)進行回滾。當事務(wù)對數(shù)據(jù)庫進行修改，InnoDB引擎不僅會記錄redo log，還會生成對應(yīng)的undo log日志；如果事務(wù)執(zhí)行失敗或調(diào)用了rollback，導(dǎo)致事務(wù)需要回滾，就可以利用undo log中的信息將數(shù)據(jù)回滾到修改之前的樣子。20.binlog和redo log有什么區(qū)別？ bin log會記錄所有與數(shù)據(jù)庫有關(guān)的日志記錄，包括InnoDB、MyISAM等存儲引擎的日志，而redo log只記InnoDB存儲引擎的日志。
記錄的內(nèi)容不同，bin log記錄的是關(guān)于一個事務(wù)的具體操作內(nèi)容，即該日志是邏輯日志。而redo log記錄的是關(guān)于每個頁（Page）的更改的物理情況。
寫入的時間不同，bin log僅在事務(wù)提交前進行提交，也就是只寫磁盤一次。而在事務(wù)進行的過程中，卻不斷有redo ertry被寫入redo log中。
寫入的方式也不相同，redo log是循環(huán)寫入和擦除，bin log是追加寫入，不會覆蓋已經(jīng)寫的文件。
21.一條更新語句怎么執(zhí)行的了解嗎？ 更新語句的執(zhí)行是Server層和引擎層配合完成，數(shù)據(jù)除了要寫入表中，還要記錄相應(yīng)的日志。
update執(zhí)行 執(zhí)行器先找引擎獲取ID=2這一行。ID是主鍵，存儲引擎檢索數(shù)據(jù)，找到這一行。如果ID=2這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。
執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上1，比如原來是N，現(xiàn)在就是N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到redo log里面，此時redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)。
執(zhí)行器生成這個操作的binlog，并把binlog寫入磁盤。
執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交（commit）狀態(tài)，更新完成。
從上圖可以看出，MySQL在執(zhí)行更新語句的時候，在服務(wù)層進行語句的解析和執(zhí)行，在引擎層進行數(shù)據(jù)的提取和存儲；同時在服務(wù)層對binlog進行寫入，在InnoDB內(nèi)進行redo log的寫入。
不僅如此，在對redo log寫入時有兩個階段的提交，一是binlog寫入之前prepare狀態(tài)的寫入，二是binlog寫入之后commit狀態(tài)的寫入。
22.那為什么要兩階段提交呢？ 為什么要兩階段提交呢？直接提交不行嗎？
我們可以假設(shè)不采用兩階段提交的方式，而是采用“單階段”進行提交，即要么先寫入redo log，后寫入binlog；要么先寫入binlog，后寫入redo log。這兩種方式的提交都會導(dǎo)致原先數(shù)據(jù)庫的狀態(tài)和被恢復(fù)后的數(shù)據(jù)庫的狀態(tài)不一致。
先寫入redo log，后寫入binlog：
在寫完redo log之后，數(shù)據(jù)此時具有crash-safe能力，因此系統(tǒng)崩潰，數(shù)據(jù)會恢復(fù)成事務(wù)開始之前的狀態(tài)。但是，若在redo log寫完時候，binlog寫入之前，系統(tǒng)發(fā)生了宕機。此時binlog沒有對上面的更新語句進行保存，導(dǎo)致當使用binlog進行數(shù)據(jù)庫的備份或者恢復(fù)時，就少了上述的更新語句。從而使得id=2這一行的數(shù)據(jù)沒有被更新。
先寫redo log，后寫bin log的問題 先寫入binlog，后寫入redo log：
寫完binlog之后，所有的語句都被保存，所以通過binlog復(fù)制或恢復(fù)出來的數(shù)據(jù)庫中id=2這一行的數(shù)據(jù)會被更新為a=1。但是如果在redo log寫入之前，系統(tǒng)崩潰，那么redo log中記錄的這個事務(wù)會無效，導(dǎo)致實際數(shù)據(jù)庫中id=2這一行的數(shù)據(jù)并沒有更新。
先寫bin log，后寫redo log的問題 簡單說，redo log和binlog都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個狀態(tài)保持邏輯上的一致。
23.redo log怎么刷入磁盤的知道嗎？ redo log的寫入不是直接落到磁盤，而是在內(nèi)存中設(shè)置了一片稱之為redo log buffer的連續(xù)內(nèi)存空間，也就是redo 日志緩沖區(qū)。
redo log緩沖 什么時候會刷入磁盤？
在如下的一些情況中，log buffer的數(shù)據(jù)會刷入磁盤：
log buffer 的大小是有限的，如果不停的往這個有限大小的 log buffer 里塞入日志，很快它就會被填滿。如果當前寫入 log buffer 的redo 日志量已經(jīng)占滿了 log buffer 總?cè)萘康拇蠹s一半 左右，就需要把這些日志刷新到磁盤上。
在事務(wù)提交時，為了保證持久性，會把log buffer中的日志全部刷到磁盤。注意，這時候，除了本事務(wù)的，可能還會刷入其它事務(wù)的日志。
有一個后臺線程，大約每秒都會刷新一次log buffer中的redo log到磁盤。
重做日志緩存、重做日志文件都是以塊（block） 的方式進行保存的，稱之為重做日志塊（redo log block） ,塊的大小是固定的512字節(jié)。我們的redo log它是固定大小的，可以看作是一個邏輯上的 log group ，由一定數(shù)量的log block 組成。
redo log分塊和寫入 它的寫入方式是從頭到尾開始寫，寫到末尾又回到開頭循環(huán)寫。
其中有兩個標記位置：
write pos是當前記錄的位置，一邊寫一邊后移，寫到第3號文件末尾后就回到0號文件開頭。checkpoint是當前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到磁盤。
write pos和checkpoint 當write_pos追上checkpoint時，表示redo log日志已經(jīng)寫滿。這時候就不能接著往里寫數(shù)據(jù)了，需要執(zhí)行checkpoint規(guī)則騰出可寫空間。
所謂的checkpoint規(guī)則 ，就是checkpoint觸發(fā)后，將buffer中日志頁都刷到磁盤。
SQL 優(yōu)化 24.慢SQL如何定位呢？ 慢SQL的監(jiān)控主要通過兩個途徑：
發(fā)現(xiàn)慢SQL 慢查詢?nèi)罩?/strong>：開啟MySQL的慢查詢?nèi)罩?，再通過一些工具比如mysqldumpslow去分析對應(yīng)的慢查詢?nèi)罩?，當然現(xiàn)在一般的云廠商都提供了可視化的平臺。 服務(wù)監(jiān)控 ：可以在業(yè)務(wù)的基建中加入對慢SQL的監(jiān)控，常見的方案有字節(jié)碼插樁、連接池擴展、ORM框架過程，對服務(wù)運行中的慢SQL進行監(jiān)控和告警。25.有哪些方式優(yōu)化慢SQL？ 慢SQL的優(yōu)化，主要從兩個方面考慮，SQL語句本身的優(yōu)化，以及數(shù)據(jù)庫設(shè)計的優(yōu)化。
SQL優(yōu)化 避免不必要的列 這個是老生常談，但還是經(jīng)常會出的情況，SQL查詢的時候，應(yīng)該只查詢需要的列，而不要包含額外的列，像slect * 這種寫法應(yīng)該盡量避免。
分頁優(yōu)化 在數(shù)據(jù)量比較大，分頁比較深的情況下，需要考慮分頁的優(yōu)化。
例如：
select * from table where type = 2 and level = 9 order by id asc limit 190289 ,10 ;優(yōu)化方案：
延遲關(guān)聯(lián)
先通過where條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過主鍵id提取數(shù)據(jù)行，而不是通過原來的二級索引提取數(shù)據(jù)行
例如：
select a.* from table a, select id from table where type = 2 and level = 9 order by id asc limit 190289 ,10 ) bwhere a.id = b.id
書簽方式
書簽方式就是找到limit第一個參數(shù)對應(yīng)的主鍵值，根據(jù)這個主鍵值再去過濾并limit
例如：
select * from table where id >select * from table where type = 2 and level = 9 order by id asc limit 190 索引優(yōu)化 合理地設(shè)計和使用索引，是優(yōu)化慢SQL的利器。
利用覆蓋索引
InnoDB使用非主鍵索引查詢數(shù)據(jù)時會回表，但是如果索引的葉節(jié)點中已經(jīng)包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引
例如對于如下查詢：
select name from test where city='上海'
我們將被查詢的字段建立到聯(lián)合索引中，這樣查詢結(jié)果就可以直接從索引中獲取
alter table test add index idx_city_name (city, name );低版本避免使用or查詢
在 MySQL 5.0 之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來替代，因為早期的 MySQL 版本使用 or 查詢可能會導(dǎo)致索引失效，高版本引入了索引合并，解決了這個問題。
避免使用 != 或者 <> 操作符
SQL中，不等于操作符會導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描，即使比較的字段上有索引
解決方法：通過把不等于操作符改成or，可以使用索引，避免全表掃描
例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了
適當使用前綴索引
適當?shù)厥褂们熬Y所云，可以降低索引的空間占用，提高索引的查詢效率。
比如，郵箱的后綴都是固定的“@xxx.com”，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引
alter table test add index index2(email(6));
PS:需要注意的是，前綴索引也存在缺點，MySQL無法利用前綴索引做order by和group by 操作，也無法作為覆蓋索引
避免列上函數(shù)運算
要避免在列字段上進行算術(shù)運算或其他表達式運算，否則可能會導(dǎo)致存儲引擎無法正確使用索引，從而影響了查詢的效率
select * from test where id + 1 = 50 ;select * from test where month (updateTime) = 7 ;正確使用聯(lián)合索引
使用聯(lián)合索引的時候，注意最左匹配原則。
JOIN優(yōu)化 優(yōu)化子查詢
盡量使用 Join 語句來替代子查詢，因為子查詢是嵌套查詢，而嵌套查詢會新創(chuàng)建一張臨時表，而臨時表的創(chuàng)建與銷毀會占用一定的系統(tǒng)資源以及花費一定的時間，同時對于返回結(jié)果集比較大的子查詢，其對查詢性能的影響更大
小表驅(qū)動大表
關(guān)聯(lián)查詢的時候要拿小表去驅(qū)動大表，因為關(guān)聯(lián)的時候，MySQL內(nèi)部會遍歷驅(qū)動表，再去連接被驅(qū)動表。
比如left join，左表就是驅(qū)動表，A表小于B表，建立連接的次數(shù)就少，查詢速度就被加快了。
select name from A left join B ;
適當增加冗余字段
增加冗余字段可以減少大量的連表查詢，因為多張表的連表查詢性能很低，所有可以適當?shù)脑黾尤哂嘧侄?，以減少多張表的關(guān)聯(lián)查詢，這是以空間換時間的優(yōu)化策略
避免使用JOIN關(guān)聯(lián)太多的表
《阿里巴巴Java開發(fā)手冊》規(guī)定不要join超過三張表，第一join太多降低查詢的速度，第二join的buffer會占用更多的內(nèi)存。
如果不可避免要join多張表，可以考慮使用數(shù)據(jù)異構(gòu)的方式異構(gòu)到ES中查詢。
排序優(yōu)化 利用索引掃描做排序
MySQL有兩種方式生成有序結(jié)果：其一是對結(jié)果集進行排序的操作，其二是按照索引順序掃描得出的結(jié)果自然是有序的
但是如果索引不能覆蓋查詢所需列，就不得不每掃描一條記錄回表查詢一次，這個讀操作是隨機IO，通常會比順序全表掃描還慢
因此，在設(shè)計索引時，盡可能使用同一個索引既滿足排序又用于查找行
例如：
--建立索引（date,staff_id,customer_id） select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時，才能夠使用索引來對結(jié)果做排序
UNION優(yōu)化 條件下推
MySQL處理union的策略是先創(chuàng)建臨時表，然后將各個查詢結(jié)果填充到臨時表中最后再來做查詢，很多優(yōu)化策略在union查詢中都會失效，因為它無法利用索引
最好手工將where、limit等子句下推到union的各個子查詢中，以便優(yōu)化器可以充分利用這些條件進行優(yōu)化
此外，除非確實需要服務(wù)器去重，一定要使用union all，如果不加all關(guān)鍵字，MySQL會給臨時表加上distinct選項，這會導(dǎo)致對整個臨時表做唯一性檢查，代價很高。
26.怎么看執(zhí)行計劃（explain），如何理解其中各個字段的含義？ explain是sql優(yōu)化的利器，除了優(yōu)化慢sql，平時的sql編寫，也應(yīng)該先explain，查看一下執(zhí)行計劃，看看是否還有優(yōu)化的空間。
直接在 select 語句之前增加explain 關(guān)鍵字，就會返回執(zhí)行計劃的信息。
explain explain id 列：MySQL會為每個select語句分配一個唯一的id值
select_type 列，查詢的類型，根據(jù)關(guān)聯(lián)、union、子查詢等等分類，常見的查詢類型有SIMPLE、PRIMARY。
table 列：表示 explain 的一行正在訪問哪個表。
type 列：最重要的列之一。表示關(guān)聯(lián)類型或訪問類型，即 MySQL 決定如何查找表中的行。
性能從最優(yōu)到最差分別為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system
system：當表僅有一行記錄時(系統(tǒng)表)，數(shù)據(jù)量很少，往往不需要進行磁盤IO，速度非常快
const
const：表示查詢時命中 primary key 主鍵或者 unique 唯一索引，或者被連接的部分是一個常量(const)值。這類掃描效率極高，返回數(shù)據(jù)量少，速度非?？?。
eq_ref
eq_ref：查詢時命中主鍵primary key 或者 unique key索引， type 就是 eq_ref。
ref_or_null
ref_or_null：這種連接類型類似于 ref，區(qū)別在于 MySQL會額外搜索包含NULL值的行。
index_merge
index_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個以上的索引。
unique_subquery
unique_subquery：替換下面的 IN子查詢，子查詢返回不重復(fù)的集合。
index_subquery
index_subquery：區(qū)別于unique_subquery，用于非唯一索引，可以返回重復(fù)值。
range
range：使用索引選擇行，僅檢索給定范圍內(nèi)的行。簡單點說就是針對一個有索引的字段，給定范圍檢索數(shù)據(jù)。在where語句中使用 bettween...and、<、>、<=、in 等條件查詢 type 都是 range。
index
index：Index 與ALL 其實都是讀全表，區(qū)別在于index是遍歷索引樹讀取，而ALL是從硬盤中讀取。
possible_keys 列：顯示查詢可能使用哪些索引來查找，使用索引優(yōu)化sql的時候比較重要。
key 列：這一列顯示 mysql 實際采用哪個索引來優(yōu)化對該表的訪問，判斷索引是否失效的時候常用。
ref 列：ref 列展示的就是與索引列作等值匹配的值，常見的有：const（常量），func，NULL，字段名。
rows 列：這也是一個重要的字段，MySQL查詢優(yōu)化器根據(jù)統(tǒng)計信息，估算SQL要查到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù)，這個值非常直觀顯示SQL的效率好壞，原則上rows越少越好。
Extra 列：顯示不適合在其它列的額外信息，雖然叫額外，但是也有一些重要的信息：
Using index：表示MySQL將使用覆蓋索引，以避免回表 Using where：表示會在存儲引擎檢索之后再進行過濾 Using temporary ：表示對查詢結(jié)果排序時會使用一個臨時表。 索引 索引可以說是MySQL面試中的重中之重，一定要徹底拿下。
27.能簡單說一下索引的分類嗎？ 從三個不同維度對索引分類：
索引分類 例如從基本使用使用的角度來講：
主鍵索引: InnoDB主鍵是默認的索引，數(shù)據(jù)列不允許重復(fù)，不允許為NULL，一個表只能有一個主鍵。 唯一索引: 數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個表允許多個列創(chuàng)建唯一索引。 普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。 組合索引：多列值組成一個索引，用于組合搜索，效率大于索引合并 28.為什么使用索引會加快查詢？ 傳統(tǒng)的查詢方法，是按照表的順序遍歷的，不論查詢幾條數(shù)據(jù)，MySQL需要將表的數(shù)據(jù)從頭到尾遍歷一遍。
在我們添加完索引之后，MySQL一般通過BTREE算法生成一個索引文件，在查詢數(shù)據(jù)庫時，找到索引文件進行遍歷，在比較小的索引數(shù)據(jù)里查找，然后映射到對應(yīng)的數(shù)據(jù)，能大幅提升查找的效率。
和我們通過書的目錄，去查找對應(yīng)的內(nèi)容，一樣的道理。
索引加快查詢遠離 29.創(chuàng)建索引有哪些注意點？ 索引雖然是sql性能優(yōu)化的利器，但是索引的維護也是需要成本的，所以創(chuàng)建索引，也要注意：
索引應(yīng)該建在查詢應(yīng)用頻繁的字段
在用于 where 判斷、 order 排序和 join 的(on)字段上創(chuàng)建索引。
索引的個數(shù)應(yīng)該適量
索引需要占用空間；更新時候也需要維護。
區(qū)分度低的字段，例如性別，不要建索引。
離散度太低的字段，掃描的行數(shù)降低的有限。
頻繁更新的值，不要作為主鍵或者索引
維護索引文件需要成本；還會導(dǎo)致頁分裂，IO次數(shù)增多。
組合索引把散列性高(區(qū)分度高)的值放在前面
為了滿足最左前綴匹配原則
創(chuàng)建組合索引，而不是修改單列索引。
組合索引代替多個單列索引（對于單列索引，MySQL基本只能使用一個索引，所以經(jīng)常使用多個條件查詢時更適合使用組合索引）
過長的字段，使用前綴索引。當字段值比較長的時候，建立索引會消耗很多的空間，搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個就叫前綴索引。
不建議用無序的值(例如身份證、UUID )作為索引
當主鍵具有不確定性，會造成葉子節(jié)點頻繁分裂，出現(xiàn)磁盤存儲的碎片化
30.索引哪些情況下會失效呢？ 如果字段類型是字符串，where時一定用引號括起來，否則會因為隱式類型轉(zhuǎn)換，索引失效
聯(lián)合索引，查詢時的條件列不是聯(lián)合索引中的第一個列，索引失效。
在索引列上使用mysql的內(nèi)置函數(shù)，索引失效。
索引字段上使用（！= 或者 < >，not in）時，可能會導(dǎo)致索引失效。
索引字段上使用is null， is not null，可能導(dǎo)致索引失效。
左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣，可能導(dǎo)致索引失效。
MySQL優(yōu)化器估計使用全表掃描要比使用索引快,則不使用索引。
31.索引不適合哪些場景呢？ 32.索引是不是建的越多越好呢？ 當然不是。
索引雖然會提高查詢效率，但是會降低更新表的效率 。比如每次對表進行增刪改操作，MySQL不僅要保存數(shù)據(jù)，還有保存或者更新對應(yīng)的索引文件。33.MySQL索引用的什么數(shù)據(jù)結(jié)構(gòu)了解嗎？ MySQL的默認存儲引擎是InnoDB，它采用的是B+樹結(jié)構(gòu)的索引。
B+樹：只有葉子節(jié)點才會存儲數(shù)據(jù)，非葉子節(jié)點只存儲鍵值。葉子節(jié)點之間使用雙向指針連接，最底層的葉子節(jié)點形成了一個雙向有序鏈表。 B+樹索引 在這張圖里，有兩個重點：
最外面的方塊，的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（粉色所示）和指針（黃色/灰色所示），如根節(jié)點磁盤包含數(shù)據(jù)項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的數(shù)據(jù)存在于葉子節(jié)點即3、4、5……、65。非葉子節(jié)點只不存儲真實的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項，如17、35并不真實存在于數(shù)據(jù)表中。 葉子節(jié)點之間使用雙向指針連接，最底層的葉子節(jié)點形成了一個雙向有序鏈表，可以進行范圍查詢。 34.那一棵B+樹能存儲多少條數(shù)據(jù)呢？ B+樹存儲數(shù)據(jù)條數(shù) 假設(shè)索引字段是 bigint 類型，長度為 8 字節(jié)。指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)，這樣一共 14 字節(jié)。非葉子節(jié)點(一頁)可以存儲 16384/14=1170 個這樣的 單元(鍵值+指針)，代表有 1170 個指針。
樹深度為 2 的時候，有 1170^2 個葉子節(jié)點，可以存儲的數(shù)據(jù)為 1170*1170*16=21902400 。
在查找數(shù)據(jù)時一次頁的查找代表一次 IO，也就是說，一張 2000 萬左右的表，查詢數(shù)據(jù)最多需要訪問 3 次磁盤。
所以在 InnoDB 中 B+ 樹深度一般為 1-3 層，它就能滿足千萬級的數(shù)據(jù)存儲。
35.為什么要用 B+ 樹，而不用普通二叉樹？ 可以從幾個維度去看這個問題，查詢是否夠快，效率是否穩(wěn)定，存儲數(shù)據(jù)多少，以及查找磁盤次數(shù)。
為什么不用普通二叉樹？
普通二叉樹存在退化的情況，如果它退化成鏈表，相當于全表掃描。平衡二叉樹相比于二叉查找樹來說，查找效率更穩(wěn)定，總體的查找速度也更快。
為什么不用平衡二叉樹呢？
讀取數(shù)據(jù)的時候，是從磁盤讀到內(nèi)存。如果樹這種數(shù)據(jù)結(jié)構(gòu)作為索引，那每查找一次數(shù)據(jù)就需要從磁盤中讀取一個節(jié)點，也就是一個磁盤塊，但是平衡二叉樹可是每個節(jié)點只存儲一個鍵值和數(shù)據(jù)的，如果是 B+ 樹，可以存儲更多的節(jié)點數(shù)據(jù)，樹的高度也會降低，因此讀取磁盤的次數(shù)就降下來啦，查詢效率就快。
36.為什么用 B+ 樹而不用 B 樹呢？ B+相比較B樹，有這些優(yōu)勢：
它是 B Tree 的變種，B Tree 能解決的問題，它都能解決。
B Tree 解決的兩大問題：每個節(jié)點存儲更多關(guān)鍵字；路數(shù)更多
掃庫、掃表能力更強
如果我們要對表進行全表掃描，只需要遍歷葉子節(jié)點就可以 了，不需要遍歷整棵 B+Tree 拿到所有的數(shù)據(jù)。
B+Tree 的磁盤讀寫能力相對于 B Tree 來說更強，IO次數(shù)更少
根節(jié)點和枝節(jié)點不保存數(shù)據(jù)區(qū)， 所以一個節(jié)點可以保存更多的關(guān)鍵字，一次磁盤加載的關(guān)鍵字更多，IO次數(shù)更少。
排序能力更強
因為葉子節(jié)點上有下一個數(shù)據(jù)區(qū)的指針，數(shù)據(jù)形成了鏈表。
效率更加穩(wěn)定
B+Tree 永遠是在葉子節(jié)點拿到數(shù)據(jù)，所以 IO 次數(shù)是穩(wěn)定的。
37.Hash 索引和 B+ 樹索引區(qū)別是什么？ B+ 樹支持聯(lián)合索引的最左側(cè)原則，Hash 索引不支持。
B+ 樹支持 order by 排序，Hash 索引不支持。
B+ 樹使用 like 進行模糊查詢的時候，like 后面（比如 % 開頭）的話可以起到優(yōu)化的作用，Hash 索引根本無法進行模糊查詢。
38.聚簇索引與非聚簇索引的區(qū)別？ 首先理解聚簇索引不是一種新的索引，而是而是一種數(shù)據(jù)存儲方式 。聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。我們熟悉的兩種存儲引擎——MyISAM采用的是非聚簇索引，InnoDB采用的是聚簇索引。
可以這么說：
索引的數(shù)據(jù)結(jié)構(gòu)是樹，聚簇索引的索引和數(shù)據(jù)存儲在一棵樹上，樹的葉子節(jié)點就是數(shù)據(jù)，非聚簇索引索引和數(shù)據(jù)不在一棵樹上。 聚簇索引和非聚簇索引 一個表中只能擁有一個聚簇索引，而非聚簇索引一個表可以存在多個。
聚簇索引，索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序；索引，索引中索引的邏輯順序與磁盤上行的物理存儲順序不同。
聚簇索引：物理存儲按照索引排序；非聚集索引：物理存儲不按照索引排序；
39.回表了解嗎？ 在InnoDB存儲引擎里，利用輔助索引查詢，先通過輔助索引找到主鍵索引的鍵值，再通過主鍵值查出主鍵索引里面沒有符合要求的數(shù)據(jù)，它比基于主鍵索引的查詢多掃描了一棵索引樹，這個過程就叫回表。
例如:select * from user where name = '張三’;
InnoDB回表 40.覆蓋索引了解嗎？ 在輔助索引里面，不管是單列索引還是聯(lián)合索引，如果 select 的數(shù)據(jù)列只用輔助索引中就能夠取得，不用去查主鍵索引，這時候使用的索引就叫做覆蓋索引，避免了回表。
比如，select name from user where name = '張三’;
覆蓋索引 41.什么是最左前綴原則/最左匹配原則？ 注意：最左前綴原則、最左匹配原則、最左前綴匹配原則這三個都是一個概念。
最左匹配原則 ：在InnoDB的聯(lián)合索引中，查詢的時候只有匹配了前一個/左邊的值之后，才能匹配下一個。
根據(jù)最左匹配原則，我們創(chuàng)建了一個組合索引，如 (a1,a2,a3)，相當于創(chuàng)建了（a1）、(a1,a2)和 (a1,a2,a3) 三個索引。
為什么不從最左開始查，就無法匹配呢？
比如有一個user表，我們給 name 和 age 建立了一個組合索引。
ALTER TABLE user add INDEX comidx_name_phone (name,age) ;
組合索引在 B+Tree 中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，它是按照從左到右的順序來建立搜索樹的 (name 在左邊，age 在右邊)。
組合索引 從這張圖可以看出來，name 是有序的，age 是無序的。當 name 相等的時候， age 才是有序的。
這個時候我們使用 where name= '張三' and age = '20 '去查詢數(shù)據(jù)的時候， B+Tree 會優(yōu)先比較 name 來確定下一步應(yīng)該搜索的方向，往左還是往右。如果 name 相同的時候再比較age。但是如果查詢條件沒有 name，就不知道下一步應(yīng)該查哪個 節(jié)點，因為建立搜索樹的時候 name 是第一個比較因子，所以就沒用上索引。
42.什么是索引下推優(yōu)化？ 索引條件下推優(yōu)化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于優(yōu)化數(shù)據(jù)查詢。
不使用索引條件下推優(yōu)化時存儲引擎通過索引檢索到數(shù)據(jù)，然后返回給MySQL Server，MySQL Server進行過濾條件的判斷。
當使用索引條件下推優(yōu)化時，如果存在某些被索引的列的判斷條件時，MySQL Server將這一部分判斷條件下推 給存儲引擎，然后由存儲引擎通過判斷索引是否符合MySQL Server傳遞的條件，只有當索引符合條件時才會將數(shù)據(jù)檢索出來返回給MySQL服務(wù)器。
例如一張表，建了一個聯(lián)合索引（name, age），查詢語句：select * from t_user where name like '張%' and age=10;，由于name使用了范圍查詢，根據(jù)最左匹配原則：
不使用ICP，引擎層查找到name like '張%'的數(shù)據(jù)，再由Server層去過濾age=10這個條件，這樣一來，就回表了兩次，浪費了聯(lián)合索引的另外一個字段age。
沒有使用ICP 但是，使用了索引下推優(yōu)化，把where的條件放到了引擎層執(zhí)行，直接根據(jù)name like '張%' and age=10的條件進行過濾，減少了回表的次數(shù)。
使用ICP 索引條件下推優(yōu)化可以減少存儲引擎查詢基礎(chǔ)表的次數(shù)，也可以減少MySQL服務(wù)器從存儲引擎接收數(shù)據(jù)的次數(shù)。
鎖 43.MySQL中有哪幾種鎖，列舉一下？ MySQL中的鎖 如果按鎖粒度劃分，有以下3種：
表鎖：開銷小，加鎖快；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低;不會出現(xiàn)死鎖。 行鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高。 頁鎖：開銷和加鎖速度介于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般 如果按照兼容性，有兩種，
共享鎖（S Lock）,也叫讀鎖（read lock），相互不阻塞。 排他鎖（X Lock），也叫寫鎖（write lock），排它鎖是阻塞的，在一定時間內(nèi)，只有一個請求能執(zhí)行寫入，并阻止其它鎖讀取正在寫入的數(shù)據(jù)。 44.說說InnoDB里的行鎖實現(xiàn)? 我們拿這么一個用戶表來表示行級鎖，其中插入了4行數(shù)據(jù)，主鍵值分別是1,6,8,12，現(xiàn)在簡化它的聚簇索引結(jié)構(gòu)，只保留數(shù)據(jù)記錄。
簡化的主鍵索引 InnoDB的行鎖的主要實現(xiàn)如下：
記錄鎖就是直接鎖定某行記錄。當我們使用唯一性的索引(包括唯一索引和聚簇索引)進行等值查詢且精準匹配到一條記錄時，此時就會直接將這條記錄鎖定。例如select * from t where id =6 for update;就會將id=6的記錄鎖定。
記錄鎖 間隙鎖(Gap Locks) 的間隙指的是兩個記錄之間邏輯上尚未填入數(shù)據(jù)的部分,是一個左開右開空間 。
間隙鎖 間隙鎖就是鎖定某些間隙區(qū)間的。當我們使用用等值查詢或者范圍查詢，并且沒有命中任何一個record，此時就會將對應(yīng)的間隙區(qū)間鎖定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就會將(1,6)區(qū)間鎖定。
臨鍵指的是間隙加上它右邊的記錄組成的左開右閉區(qū)間 。比如上述的(1,6]、(6,8]等。
臨鍵鎖 臨鍵鎖就是記錄鎖(Record Locks)和間隙鎖(Gap Locks)的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。當我們使用范圍查詢，并且命中了部分record記錄，此時鎖住的就是臨鍵區(qū)間。注意，臨鍵鎖鎖住的區(qū)間會包含最后一個record的右邊的臨鍵區(qū)間。例如select * from t where id > 5 and id <= 7 for update;會鎖住(4,7]、(7,+∞)。mysql默認行鎖類型就是臨鍵鎖(Next-Key Locks)。當使用唯一性索引，等值查詢匹配到一條記錄的時候，臨鍵鎖(Next-Key Locks)會退化成記錄鎖；沒有匹配到任何記錄的時候，退化成間隙鎖。
間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都是用來解決幻讀問題的，在已提交讀（READ COMMITTED）隔離級別下，間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都會失效！
上面是行鎖的三種實現(xiàn)算法，除此之外，在行上還存在插入意向鎖。
Insert Intention Lock 插入意向鎖 一個事務(wù)在插入一條記錄時需要判斷一下插入位置是不是被別的事務(wù)加了意向鎖 ，如果有的話，插入操作需要等待，直到擁有 gap鎖 的那個事務(wù)提交。但是事務(wù)在等待的時候也需要在內(nèi)存中生成一個 鎖結(jié)構(gòu) ，表明有事務(wù)想在某個 間隙 中插入新記錄，但是現(xiàn)在在等待。這種類型的鎖命名為 Insert Intention Locks ，也就是插入意向鎖 。
假如我們有個T1事務(wù)，給(1,6)區(qū)間加上了意向鎖，現(xiàn)在有個T2事務(wù)，要插入一個數(shù)據(jù)，id為4，它會獲取一個（1,6）區(qū)間的插入意向鎖，又有有個T3事務(wù)，想要插入一個數(shù)據(jù)，id為3，它也會獲取一個（1,6）區(qū)間的插入意向鎖，但是，這兩個插入意向鎖鎖不會互斥。
插入意向鎖 45.意向鎖是什么知道嗎？ 意向鎖是一個表級鎖，不要和插入意向鎖搞混。
意向鎖的出現(xiàn)是為了支持InnoDB的多粒度鎖，它解決的是表鎖和行鎖共存的問題。
當我們需要給一個表加表鎖的時候，我們需要根據(jù)去判斷表中有沒有數(shù)據(jù)行被鎖定，以確定是否能加成功。
假如沒有意向鎖，那么我們就得遍歷表中所有數(shù)據(jù)行來判斷有沒有行鎖；
有了意向鎖這個表級鎖之后，則我們直接判斷一次就知道表中是否有數(shù)據(jù)行被鎖定了。
有了意向鎖之后，要執(zhí)行的事務(wù)A在申請行鎖（寫鎖）之前，數(shù)據(jù)庫會自動先給事務(wù)A申請表的意向排他鎖。當事務(wù)B去申請表的互斥鎖時就會失敗，因為表上有意向排他鎖之后事務(wù)B申請表的互斥鎖時會被阻塞。
意向鎖 46.MySQL的樂觀鎖和悲觀鎖了解嗎？ 悲觀鎖 （Pessimistic Concurrency Control）：悲觀鎖認為被它保護的數(shù)據(jù)是極其不安全的，每時每刻都有可能被改動，一個事務(wù)拿到悲觀鎖后，其他任何事務(wù)都不能對該數(shù)據(jù)進行修改，只能等待鎖被釋放才可以執(zhí)行。
數(shù)據(jù)庫中的行鎖，表鎖，讀鎖，寫鎖均為悲觀鎖。
樂觀鎖（Optimistic Concurrency Control） 樂觀鎖認為數(shù)據(jù)的變動不會太頻繁。
樂觀鎖通常是通過在表中增加一個版本(version)或時間戳(timestamp)來實現(xiàn)，其中，版本最為常用。
事務(wù)在從數(shù)據(jù)庫中取數(shù)據(jù)時，會將該數(shù)據(jù)的版本也取出來(v1)，當事務(wù)對數(shù)據(jù)變動完畢想要將其更新到表中時，會將之前取出的版本v1與數(shù)據(jù)中最新的版本v2相對比，如果v1=v2，那么說明在數(shù)據(jù)變動期間，沒有其他事務(wù)對數(shù)據(jù)進行修改，此時，就允許事務(wù)對表中的數(shù)據(jù)進行修改，并且修改時version會加1，以此來表明數(shù)據(jù)已被變動。
如果，v1不等于v2，那么說明數(shù)據(jù)變動期間，數(shù)據(jù)被其他事務(wù)改動了，此時不允許數(shù)據(jù)更新到表中，一般的處理辦法是通知用戶讓其重新操作。不同于悲觀鎖，樂觀鎖通常是由開發(fā)者實現(xiàn)的。
47.MySQL 遇到過死鎖問題嗎，你是如何解決的？ 排查死鎖的一般步驟是這樣的：
（1）查看死鎖日志 show engine innodb status;
（2）找出死鎖 sql
（3）分析 sql 加鎖情況
（4）模擬死鎖案發(fā)
（5）分析死鎖日志
（6）分析死鎖結(jié)果
當然，這只是一個簡單的流程說明，實際上生產(chǎn)中的死鎖千奇百怪，排查和解決起來沒那么簡單。
事務(wù) 48.MySQL 事務(wù)的四大特性說一下？ 事務(wù)四大特性 原子性：事務(wù)作為一個整體被執(zhí)行，包含在其中的對數(shù)據(jù)庫的操作要么全部被執(zhí)行，要么都不執(zhí)行。 一致性：指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)不會被破壞，假如 A 賬戶給 B 賬戶轉(zhuǎn) 10 塊錢，不管成功與否，A 和 B 的總金額是不變的。 隔離性：多個事務(wù)并發(fā)訪問時，事務(wù)之間是相互隔離的，即一個事務(wù)不影響其它事務(wù)運行效果。簡言之，就是事務(wù)之間是進水不犯河水的。 持久性：表示事務(wù)完成以后，該事務(wù)對數(shù)據(jù)庫所作的操作更改，將持久地保存在數(shù)據(jù)庫之中。 49.那ACID靠什么保證的呢？ 事務(wù)的隔離性 是通過數(shù)據(jù)庫鎖的機制實現(xiàn)的。 事務(wù)的一致性 由undo log來保證：undo log是邏輯日志，記錄了事務(wù)的insert、update、deltete操作，回滾的時候做相反的delete、update、insert操作來恢復(fù)數(shù)據(jù)。 事務(wù)的原子性 和持久性 由redo log來保證：redolog被稱作重做日志，是物理日志，事務(wù)提交的時候，必須先將事務(wù)的所有日志寫入redo log持久化，到事務(wù)的提交操作才算完成。 ACID靠什么保證 50.事務(wù)的隔離級別有哪些？MySQL 的默認隔離級別是什么？ 事務(wù)的四個隔離級別 可重復(fù)讀（Repeatable Read）
MySQL默認的事務(wù)隔離級別是可重復(fù)讀 (Repeatable Read)。
51.什么是幻讀，臟讀，不可重復(fù)讀呢？ 事務(wù) A、B 交替執(zhí)行，事務(wù) A 讀取到事務(wù) B 未提交的數(shù)據(jù)，這就是臟讀 。
在一個事務(wù)范圍內(nèi)，兩個相同的查詢，讀取同一條記錄，卻返回了不同的數(shù)據(jù)，這就是不可重復(fù)讀 。
事務(wù) A 查詢一個范圍的結(jié)果集，另一個并發(fā)事務(wù) B 往這個范圍中插入 / 刪除了數(shù)據(jù)，并靜悄悄地提交，然后事務(wù) A 再次查詢相同的范圍，兩次讀取得到的結(jié)果集不一樣了，這就是幻讀 。
不同的隔離級別，在并發(fā)事務(wù)下可能會發(fā)生的問題：
隔離級別 臟讀 不可重復(fù)讀 幻讀 Read Uncommited 讀取未提交 是 是 是 Read Commited 讀取已提交 否 是 否 Repeatable Read 可重復(fù)讀 否 否 是 Serialzable 可串行化 否 否 否
52.事務(wù)的各個隔離級別都是如何實現(xiàn)的？ 讀未提交
讀未提交，就不用多說了，采取的是讀不加鎖原理。
事務(wù)讀不加鎖，不阻塞其他事務(wù)的讀和寫 事務(wù)寫阻塞其他事務(wù)寫，但不阻塞其他事務(wù)讀； 讀取已提交&可重復(fù)讀
讀取已提交和可重復(fù)讀級別利用了ReadView和MVCC，也就是每個事務(wù)只能讀取它能看到的版本（ReadView）。
READ COMMITTED：每次讀取數(shù)據(jù)前都生成一個ReadView REPEATABLE READ ：在第一次讀取數(shù)據(jù)時生成一個ReadView 串行化
串行化的實現(xiàn)采用的是讀寫都加鎖的原理。
串行化的情況下，對于同一行事務(wù)，寫會加寫鎖，讀會加讀鎖。當出現(xiàn)讀寫鎖沖突的時候，后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。
53.MVCC了解嗎？怎么實現(xiàn)的？ MVCC(Multi Version Concurrency Control)，中文名是多版本并發(fā)控制，簡單來說就是通過維護數(shù)據(jù)歷史版本，從而解決并發(fā)訪問情況下的讀一致性問題。關(guān)于它的實現(xiàn)，要抓住幾個關(guān)鍵點，隱式字段、undo日志、版本鏈、快照讀&當前讀、Read View 。
版本鏈
對于InnoDB存儲引擎，每一行記錄都有兩個隱藏列DB_TRX_ID、DB_ROLL_PTR
DB_TRX_ID，事務(wù)ID，每次修改時，都會把該事務(wù)ID復(fù)制給DB_TRX_ID；DB_ROLL_PTR，回滾指針，指向回滾段的undo日志。表隱藏列 假如有一張user表，表中只有一行記錄，當時插入的事務(wù)id為80。此時，該條記錄的示例圖如下：
在這里插入圖片描述 接下來有兩個DB_TRX_ID分別為100、200的事務(wù)對這條記錄進行update操作，整個過程如下：
update操作 由于每次變動都會先把undo日志記錄下來，并用DB_ROLL_PTR指向undo日志地址。因此可以認為，對該條記錄的修改日志串聯(lián)起來就形成了一個版本鏈，版本鏈的頭節(jié)點就是當前記錄最新的值 。如下：
MVCC ReadView
對于Read Committed和Repeatable Read隔離級別來說，都需要讀取已經(jīng)提交的事務(wù)所修改的記錄，也就是說如果版本鏈中某個版本的修改沒有提交，那么該版本的記錄時不能被讀取的。所以需要確定在Read Committed和Repeatable Read隔離級別下，版本鏈中哪個版本是能被當前事務(wù)讀取的。于是就引入了ReadView這個概念來解決這個問題。
Read View就是事務(wù)執(zhí)行快照讀 時，產(chǎn)生的讀視圖，相當于某時刻表記錄的一個快照，通過這個快照，我們可以獲?。?/p>事務(wù)和ReadView
m_ids ：表示在生成 ReadView 時當前系統(tǒng)中活躍的讀寫事務(wù)的事務(wù)id 列表。
min_trx_id ：表示在生成 ReadView 時當前系統(tǒng)中活躍的讀寫事務(wù)中最小的 事務(wù)id ，也就是 m_ids 中的最小值。
max_trx_id ：表示生成 ReadView 時系統(tǒng)中應(yīng)該分配給下一個事務(wù)的 id 值。
creator_trx_id ：表示生成該 ReadView 的事務(wù)的 事務(wù)id
有了這個 ReadView ，這樣在訪問某條記錄時，只需要按照下邊的步驟判斷記錄的某個版本是否可見：
如果被訪問版本的 DB_TRX_ID 屬性值與 ReadView 中的 creator_trx_id 值相同，意味著當前事務(wù)在訪問它自己修改過的記錄，所以該版本可以被當前事務(wù)訪問。 如果被訪問版本的 DB_TRX_ID 屬性值小于 ReadView 中的 min_trx_id 值，表明生成該版本的事務(wù)在當前事務(wù)生成 ReadView 前已經(jīng)提交，所以該版本可以被當前事務(wù)訪問。 如果被訪問版本的 DB_TRX_ID 屬性值大于 ReadView 中的 max_trx_id 值，表明生成該版本的事務(wù)在當前事務(wù)生成 ReadView 后才開啟，所以該版本不可以被當前事務(wù)訪問。 如果被訪問版本的 DB_TRX_ID 屬性值在 ReadView 的 min_trx_id 和 max_trx_id 之間，那就需要判斷一下trx_id 屬性值是不是在 m_ids 列表中，如果在，說明創(chuàng)建 ReadView 時生成該版本的事務(wù)還是活躍的，該版本不可以被訪問；如果不在，說明創(chuàng)建 ReadView 時生成該版本的事務(wù)已經(jīng)被提交，該版本可以被訪問。 如果某個版本的數(shù)據(jù)對當前事務(wù)不可見的話，那就順著版本鏈找到下一個版本的數(shù)據(jù)，繼續(xù)按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最后一個版本。如果最后一個版本也不可見的話，那么就意味著該條記錄對該事務(wù)完全不可見，查詢結(jié)果就不包含該記錄。
在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔離級別的的一個非常大的區(qū)別就是它們生成ReadView的時機不同。
READ COMMITTED 是每次讀取數(shù)據(jù)前都生成一個ReadView ，這樣就能保證自己每次都能讀到其它事務(wù)提交的數(shù)據(jù)；REPEATABLE READ 是在第一次讀取數(shù)據(jù)時生成一個ReadView ，這樣就能保證后續(xù)讀取的結(jié)果完全一致。
高可用/性能 54.數(shù)據(jù)庫讀寫分離了解嗎？ 讀寫分離的基本原理是將數(shù)據(jù)庫讀寫操作分散到不同的節(jié)點上，下面是基本架構(gòu)圖：
讀寫分離 讀寫分離的基本實現(xiàn)是:
數(shù)據(jù)庫服務(wù)器搭建主從集群，一主一從、一主多從都可以。 數(shù)據(jù)庫主機負責(zé)讀寫操作，從機只負責(zé)讀操作。 數(shù)據(jù)庫主機通過復(fù)制將數(shù)據(jù)同步到從機，每臺數(shù)據(jù)庫服務(wù)器都存儲了所有的業(yè)務(wù)數(shù)據(jù)。 業(yè)務(wù)服務(wù)器將寫操作發(fā)給數(shù)據(jù)庫主機，將讀操作發(fā)給數(shù)據(jù)庫從機。 55.那讀寫分離的分配怎么實現(xiàn)呢？ 將讀寫操作區(qū)分開來，然后訪問不同的數(shù)據(jù)庫服務(wù)器，一般有兩種方式：程序代碼封裝和中間件封裝。
程序代碼封裝指在代碼中抽象一個數(shù)據(jù)訪問層（所以有的文章也稱這種方式為 '中間層封裝' ） ，實現(xiàn)讀寫操作分離和數(shù)據(jù)庫服務(wù)器連接的管理。例如，基于 Hibernate 進行簡單封裝，就可以實現(xiàn)讀寫分離：
業(yè)務(wù)代碼封裝 目前開源的實現(xiàn)方案中，淘寶的 TDDL (Taobao Distributed Data Layer, 外號：頭都大了）是比較有名的。
中間件封裝指的是獨立一套系統(tǒng)出來，實現(xiàn)讀寫操作分離和數(shù)據(jù)庫服務(wù)器連接的管理。中間件對業(yè)務(wù)服務(wù)器提供 SQL 兼容的協(xié)議，業(yè)務(wù)服務(wù)器無須自己進行讀寫分離。
對于業(yè)務(wù)服務(wù)器來說，訪問中間件和訪問數(shù)據(jù)庫沒有區(qū)別，事實上在業(yè)務(wù)服務(wù)器看來，中間件就是一個數(shù)據(jù)庫服務(wù)器。
其基本架構(gòu)是：
數(shù)據(jù)庫中間件 56.主從復(fù)制原理了解嗎？ master數(shù)據(jù)寫入，更新binlog master創(chuàng)建一個dump線程向slave推送binlog slave連接到master的時候，會創(chuàng)建一個IO線程接收binlog，并記錄到relay log中繼日志中 slave再開啟一個sql線程讀取relay log事件并在slave執(zhí)行，完成同步 主從復(fù)制 57.主從同步延遲怎么處理？ 主從同步延遲的原因
一個服務(wù)器開放Ｎ個鏈接給客戶端來連接的，這樣有會有大并發(fā)的更新操作, 但是從服務(wù)器的里面讀取 binlog 的線程僅有一個，當某個 SQL 在從服務(wù)器上執(zhí)行的時間稍長 或者由于某個 SQL 要進行鎖表就會導(dǎo)致，主服務(wù)器的 SQL 大量積壓，未被同步到從服務(wù)器里。這就導(dǎo)致了主從不一致， 也就是主從延遲。
主從同步延遲的解決辦法
解決主從復(fù)制延遲有幾種常見的方法:
寫操作后的讀操作指定發(fā)給數(shù)據(jù)庫主服務(wù)器 例如，注冊賬號完成后，登錄時讀取賬號的讀操作也發(fā)給數(shù)據(jù)庫主服務(wù)器。這種方式和業(yè)務(wù)強綁定，對業(yè)務(wù)的侵入和影響較大，如果哪個新來的程序員不知道這樣寫代碼，就會導(dǎo)致一個bug。
這就是通常所說的 '二次讀取' ，二次讀取和業(yè)務(wù)無綁定，只需要對底層數(shù)據(jù)庫訪問的 API 進行封裝即可，實現(xiàn)代價較小，不足之處在于如果有很多二次讀取，將大大增加主機的讀操作壓力。例如，黑客暴力破解賬號，會導(dǎo)致大量的二次讀取操作，主機可能頂不住讀操作的壓力從而崩潰。
關(guān)鍵業(yè)務(wù)讀寫操作全部指向主機，非關(guān)鍵業(yè)務(wù)采用讀寫分離 例如，對于一個用戶管理系統(tǒng)來說，注冊 + 登錄的業(yè)務(wù)讀寫操作全部訪問主機，用戶的介紹、爰好、等級等業(yè)務(wù)，可以采用讀寫分離，因為即使用戶改了自己的自我介紹，在查詢時卻看到了自我介紹還是舊的，業(yè)務(wù)影響與不能登錄相比就小很多，還可以忍受。
58.你們一般是怎么分庫的呢？ 垂直分庫：以表為依據(jù)，按照業(yè)務(wù)歸屬不同，將不同的表拆分到不同的庫中。 垂直分庫 水平分庫：以字段為依據(jù)，按照一定策略（hash、range 等），將一個庫中的數(shù)據(jù)拆分到多個庫中。 水平分庫 59.那你們是怎么分表的？ 水平分表：以字段為依據(jù)，按照一定策略（hash、range 等），將一個表中的數(shù)據(jù)拆分到多個表中。 垂直分表：以字段為依據(jù)，按照字段的活躍性，將表中字段拆到不同的表（主表和擴展表）中。 表拆分 60.水平分表有哪幾種路由方式？ 什么是路由呢？就是數(shù)據(jù)應(yīng)該分到哪一張表。
水平分表主要有三種路由方式：
范圍路由 ：選取有序的數(shù)據(jù)列 （例如，整形、時間戳等） 作為路由的條件，不同分段分散到不同的數(shù)據(jù)庫表中。我們可以觀察一些支付系統(tǒng)，發(fā)現(xiàn)只能查一年范圍內(nèi)的支付記錄，這個可能就是支付公司按照時間進行了分表。
范圍路由 范圍路由設(shè)計的復(fù)雜點主要體現(xiàn)在分段大小的選取上，分段太小會導(dǎo)致切分后子表數(shù)量過多，增加維護復(fù)雜度；分段太大可能會導(dǎo)致單表依然存在性能問題，一般建議分段大小在 100 萬至2000 萬之間，具體需要根據(jù)業(yè)務(wù)選取合適的分段大小。
范圍路由的優(yōu)點是可以隨著數(shù)據(jù)的增加平滑地擴充新的表。例如，現(xiàn)在的用戶是 100 萬，如果增加到 1000 萬，只需要增加新的表就可以了，原有的數(shù)據(jù)不需要動。范圍路由的一個比較隱含的缺點是分布不均勻，假如按照 1000 萬來進行分表，有可能某個分段實際存儲的數(shù)據(jù)量只有 1000 條，而另外一個分段實際存儲的數(shù)據(jù)量有 900 萬條。
Hash 路由 ：選取某個列 （或者某幾個列組合也可以） 的值進行 Hash 運算，然后根據(jù) Hash 結(jié)果分散到不同的數(shù)據(jù)庫表中。同樣以訂單 id 為例，假如我們一開始就規(guī)劃了 4個數(shù)據(jù)庫表，路由算法可以簡單地用 id % 4 的值來表示數(shù)據(jù)所屬的數(shù)據(jù)庫表編號，id 為 12的訂單放到編號為 50的子表中，id為 13的訂單放到編號為 61的字表中。
Hash路由 Hash 路由設(shè)計的復(fù)雜點主要體現(xiàn)在初始表數(shù)量的選取上，表數(shù)量太多維護比較麻煩，表數(shù)量太少又可能導(dǎo)致單表性能存在問題。而用了 Hash 路由后，增加子表數(shù)量是非常麻煩的，所有數(shù)據(jù)都要重分布。Hash 路由的優(yōu)缺點和范圍路由基本相反，Hash 路由的優(yōu)點是表分布比較均勻，缺點是擴充新的表很麻煩，所有數(shù)據(jù)都要重分布。
配置路由 ：配置路由就是路由表，用一張獨立的表來記錄路由信息。同樣以訂單id 為例，我們新增一張 order_router 表，這個表包含 orderjd 和 tablejd 兩列 , 根據(jù) orderjd 就可以查詢對應(yīng)的 table_id。配置路由設(shè)計簡單，使用起來非常靈活，尤其是在擴充表的時候，只需要遷移指定的數(shù)據(jù)，然后修改路由表就可以了。
配置路由 配置路由的缺點就是必須多查詢一次，會影響整體性能；而且路由表本身如果太大（例如，幾億條數(shù)據(jù)） ，性能同樣可能成為瓶頸，如果我們再次將路由表分庫分表，則又面臨一個死循環(huán)式的路由算法選擇問題。
61.不停機擴容怎么實現(xiàn)？ 實際上，不停機擴容，實操起來是個非常麻煩而且很有風(fēng)險的操作，當然，面試回答起來就簡單很多。
建立好新的庫表結(jié)構(gòu)，數(shù)據(jù)寫入久庫的同時，也寫入拆分的新庫
數(shù)據(jù)遷移，使用數(shù)據(jù)遷移程序，將舊庫中的歷史數(shù)據(jù)遷移到新庫
使用定時任務(wù)，新舊庫的數(shù)據(jù)對比，把差異補齊
第一階段 把對數(shù)據(jù)的讀切換到新庫
第二階段 經(jīng)過一段時間，確定舊庫沒有請求之后，就可以下線老庫 第三階段 62.常用的分庫分表中間件有哪些？ 63.那你覺得分庫分表會帶來什么問題呢？ 從分庫的角度來講：
使用關(guān)系型數(shù)據(jù)庫，有很大一點在于它保證事務(wù)完整性。
而分庫之后單機事務(wù)就用不上了，必須使用分布式事務(wù)來解決。
在一個庫中的時候我們還可以利用 JOIN 來連表查詢，而跨庫了之后就無法使用 JOIN 了。
此時的解決方案就是在業(yè)務(wù)代碼中進行關(guān)聯(lián) ，也就是先把一個表的數(shù)據(jù)查出來，然后通過得到的結(jié)果再去查另一張表，然后利用代碼來關(guān)聯(lián)得到最終的結(jié)果。
這種方式實現(xiàn)起來稍微比較復(fù)雜，不過也是可以接受的。
還有可以適當?shù)娜哂嘁恍┳侄?/strong>。比如以前的表就存儲一個關(guān)聯(lián) ID，但是業(yè)務(wù)時常要求返回對應(yīng)的 Name 或者其他字段。這時候就可以把這些字段冗余到當前表中，來去除需要關(guān)聯(lián)的操作。
還有一種方式就是數(shù)據(jù)異構(gòu) ，通過binlog同步等方式，把需要跨庫join的數(shù)據(jù)異構(gòu)到ES等存儲結(jié)構(gòu)中，通過ES進行查詢。
從分表的角度來看：
跨節(jié)點的 count,order by,group by 以及聚合函數(shù)問題 只能由業(yè)務(wù)代碼來實現(xiàn)或者用中間件將各表中的數(shù)據(jù)匯總、排序、分頁然后返回。
數(shù)據(jù)遷移，容量規(guī)劃，擴容等問題 數(shù)據(jù)的遷移，容量如何規(guī)劃，未來是否可能再次需要擴容，等等，都是需要考慮的問題。
數(shù)據(jù)庫表被切分后，不能再依賴數(shù)據(jù)庫自身的主鍵生成機制，所以需要一些手段來保證全局主鍵唯一。
還是自增，只不過自增步長設(shè)置一下。比如現(xiàn)在有三張表，步長設(shè)置為3，三張表 ID 初始值分別是1、2、3。這樣第一張表的 ID 增長是 1、4、7。第二張表是2、5、8。第三張表是3、6、9，這樣就不會重復(fù)了。 UUID，這種最簡單，但是不連續(xù)的主鍵插入會導(dǎo)致嚴重的頁分裂，性能比較差。 分布式 ID，比較出名的就是 Twitter 開源的 sonwflake 雪花算法 運維 64.百萬級別以上的數(shù)據(jù)如何刪除？ 關(guān)于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數(shù)據(jù)的增加,修改,刪除,都會產(chǎn)生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。
所以，在我們刪除數(shù)據(jù)庫百萬級別數(shù)據(jù)的時候，查詢MySQL官方手冊得知刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。
所以我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引 刪除完成后重新創(chuàng)建索引創(chuàng)建索引也非?？?/section> 65.百萬千萬級大表如何添加字段？ 當線上的數(shù)據(jù)庫數(shù)據(jù)量到達幾百萬、上千萬的時候，加一個字段就沒那么簡單，因為可能會長時間鎖表。
大表添加字段，通常有這些做法：
通過中間表轉(zhuǎn)換過去
創(chuàng)建一個臨時的新表，把舊表的結(jié)構(gòu)完全復(fù)制過去，添加字段，再把舊表數(shù)據(jù)復(fù)制過去，刪除舊表，新表命名為舊表的名稱，這種方式可能回丟掉一些數(shù)據(jù)。
用pt-online-schema-change
pt-online-schema-change是percona公司開發(fā)的一個工具，它可以在線修改表結(jié)構(gòu)，它的原理也是通過中間表。
先在從庫添加 再進行主從切換
如果一張表數(shù)據(jù)量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換后再將其他幾個節(jié)點上添加字段。
66.MySQL 數(shù)據(jù)庫 cpu 飆升的話，要怎么處理呢？ 排查過程：
（1）使用 top 命令觀察，確定是 mysqld 導(dǎo)致還是其他原因。
（2）如果是 mysqld 導(dǎo)致的，show processlist，查看 session 情況，確定是不是有消耗資源的 sql 在運行。
（3）找出消耗高的 sql，看看執(zhí)行計劃是否準確， 索引是否缺失，數(shù)據(jù)量是否太大。
處理：
（1）kill 掉這些線程 (同時觀察 cpu 使用率是否下降)，
（2）進行相應(yīng)的調(diào)整 (比如說加索引、改 sql、改內(nèi)存參數(shù))
（3）重新跑這些 SQL。
其他情況：
也有可能是每個 sql 消耗資源并不多，但是突然之間，有大量的 session 連進來導(dǎo)致 cpu 飆升，這種情況就需要跟應(yīng)用一起來分析為何連接數(shù)會激增，再做出相應(yīng)的調(diào)整，比如說限制連接數(shù)等
參考：
[2]. 《MySQL技術(shù)內(nèi)幕 InnoDB存儲引擎》 [4]. 《MySQL 是怎樣運行的：從根兒上理解 MySQL》 [5].[快問快答，MySQL 面試奪命 20 問](https://mp.weixin.qq.com/s/aiD91w3ez48o-SiOAOSK-A) [7].[100道MySQL數(shù)據(jù)庫經(jīng)典面試題解析（收藏版）](https:///post/6844904166939164680) [8].[MySQL索引從基礎(chǔ)到原理，看這一篇就夠了](https://blog.csdn.net/sinat_40770656/article/details/114339535) [9].[圖文并茂的帶你徹底理解悲觀鎖與樂觀鎖 ](https://mp.weixin.qq.com/s?__biz=Mzg3NzU5NTIwNg==&mid=2247487996&idx=1&sn=cafae3423e5ffa903a0c0a94a355f981&source=41#wechat_redirect) [10]. [最完整MySQL數(shù)據(jù)庫面試題（2020最新版）](https://zhuanlan.zhihu.com/p/112857507) [11]. [MySQL提升筆記（2）：存儲引擎盤點](https://fighter3.blog.csdn.net/article/details/115741692) [13].[不會看 Explain執(zhí)行計劃，勸你簡歷別寫熟悉 SQL優(yōu)化 ](https:///post/6844904163969630221) [14]. [為了讓你徹底弄懂 MySQL 事務(wù)日志，我通宵肝出了這份圖解！](https://www./10740.html) [15]. 《極客時間 高并發(fā)系統(tǒng)設(shè)計40問》 [16]. 《極客時間 從零開始學(xué)架構(gòu)》 [17]. [在面試時被問到，為什么MySQL數(shù)據(jù)庫數(shù)據(jù)量大了要進行分庫分表？](https://www.zhihu.com/question/459955079) [18]. [為了讓你徹底弄懂 MySQL 事務(wù)日志，我通宵肝出了這份圖解！](https://www./10740.html) [19]. [MySQL 三萬字精華總結(jié) + 面試100 問，和面試官扯皮綽綽有余（收藏系列](https:///post/6850037271233331208) [20].[面試中的老大難-mysql事務(wù)和鎖，一次性講清楚！](https:///post/6855129007336521741#heading-13) [21]. [一文徹底讀懂MySQL事務(wù)的四大隔離級別 ](https:///post/6844904115353436174#heading-16) [22].[MySQL存儲引擎－－MyISAM與InnoDB區(qū)別](https://segmentfault.com/a/1190000008227211) [23]. [解決死鎖之路（終結(jié)篇） - 再見死鎖](https://www./archives/2018/04/solving-dead-locks-four.html) [24]. [大眾點評訂單系統(tǒng)分庫分表實踐](https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html)
