MYSQL 那點(diǎn)破事！索引、SQL調(diào)優(yōu)、事務(wù)、B+樹(shù)、分表 ....

大家好，我是Tom哥~

為了便于大家查找問(wèn)題，了解全貌，整理個(gè)目錄，我們可以快速全局了解關(guān)于mysql數(shù)據(jù)庫(kù)，面試官一般喜歡問(wèn)哪些問(wèn)題

接下來(lái)，我們逐條來(lái)看看每個(gè)問(wèn)題及答案

MyISAM 和 InnoDB 的區(qū)別？

答案：InnoDB 支持 事務(wù)、外鍵、聚集索引，通過(guò)MVCC來(lái)支持高并發(fā)，索引和數(shù)據(jù)存儲(chǔ)在一起。InnoDB 不保存表的具體行數(shù)，執(zhí)行 select count(*) from table 時(shí)需要全表掃描。而MyISAM 用一個(gè)變量保存了整個(gè)表的行數(shù)。

InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖，并發(fā)能力低。MySQL 將默認(rèn)存儲(chǔ)引擎是 InnoDB

mysql 鎖有哪些類(lèi)型？

答案：mysql鎖分為共享鎖( S lock ) 、排他鎖 ( X lock )，也叫做讀鎖和寫(xiě)鎖。根據(jù)粒度，可以分為表鎖、頁(yè)鎖、行鎖。

什么是間隙鎖？

答案：間隙鎖是可重復(fù)讀級(jí)別下才會(huì)有的鎖，mysql會(huì)幫我們生成了若干左開(kāi)右閉的區(qū)間，結(jié)合MVCC和間隙鎖可以解決幻讀問(wèn)題。

如何避免死鎖？

答案：死鎖的四個(gè)必要條件：1、互斥 2、請(qǐng)求與保持 3、環(huán)路等待 4、不可剝奪。

• 合理的設(shè)計(jì)索引，區(qū)分度高的列放到組合索引前面，使業(yè)務(wù) SQL 盡可能通過(guò)索引定位更少的行，減少鎖競(jìng)爭(zhēng)。
• 調(diào)整業(yè)務(wù)邏輯 SQL 執(zhí)行順序， 避免 update/delete 長(zhǎng)時(shí)間持有鎖的 SQL 在事務(wù)前面。
• 避免大事務(wù)，將大事務(wù)拆成多個(gè)小事務(wù)
• 以固定的順序訪問(wèn)表和行。比如兩個(gè)更新數(shù)據(jù)的事務(wù)，事務(wù) A 更新數(shù)據(jù)的順序?yàn)?1，2;事務(wù) B 更新數(shù)據(jù)的順序?yàn)?2，1。這樣更可能會(huì)造成死鎖。
• 在并發(fā)比較高的系統(tǒng)中，不要顯式加鎖，特別是是在事務(wù)里顯式加鎖。如 select … for update 語(yǔ)句，如果是在事務(wù)里（運(yùn)行了 start transaction 或設(shè)置了autocommit 等于0）,那么就會(huì)鎖定所查找到的記錄。
• 盡量用主鍵/索引去查找記錄
• 優(yōu)化 SQL 和表設(shè)計(jì)，減少同時(shí)占用太多資源的情況。比如說(shuō)，避免多個(gè)表join，將復(fù)雜 SQL 分解為多個(gè)簡(jiǎn)單的 SQL。

數(shù)據(jù)庫(kù)的隔離級(jí)別？

答案：讀未提交、讀已提交、可重復(fù)讀（mysql的默認(rèn)級(jí)別，每次讀取結(jié)果都一樣，但是有可能產(chǎn)生幻讀）、串行化。

Mysql有哪些類(lèi)型的索引？

答案：

• 普通索引：一個(gè)索引只包含一個(gè)列，一個(gè)表可以有多個(gè)單列索引。
• 唯一索引：索引列的值必須唯一，但允許有空值
• 復(fù)合索引：多列值組成一個(gè)索引，專(zhuān)門(mén)用于組合搜索，其效率大于索引合并
• 聚簇索引：也稱(chēng)為主鍵索引，是一種數(shù)據(jù)存儲(chǔ)方式。B+Tree結(jié)構(gòu)，非葉子節(jié)點(diǎn)包含健值和指針，葉子節(jié)點(diǎn)包含索引列和行數(shù)據(jù)。一張表只能有一個(gè)聚簇索引。
• 非聚簇索引：不是聚簇索引，就是非聚簇索引。葉子節(jié)點(diǎn)只是存索引列和主鍵id。如果sql還要返回除了索引列的其他字段信息，需要回表，第一次索引一般是順序IO，回表的操作屬于隨機(jī)IO?；乇淼拇螖?shù)越多，性能越差。此時(shí)我們推薦覆蓋索引

什么是覆蓋索引和回表？

答案：

1、覆蓋索引，指的是在一次查詢(xún)中，一個(gè)索引包含所有需要查詢(xún)的字段的值，可能是返回值或where條件

select buyer_id from order where money>100

假如我們創(chuàng)建了一個(gè)(money，buyer_id)的聯(lián)合索引，索引的葉子節(jié)點(diǎn)包含了buyer_id的信息，則不會(huì)再回表查詢(xún)。

2、回表，指查詢(xún)時(shí)一些字段值拿不到，需要到主鍵索引B+樹(shù)再查一次。

Mysql的最左前綴原則？

答案：即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配，直到遇到范圍查詢(xún)（如：> 、< 、between、like等）

例子：where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)組合索引，d是用不到索引的；如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

線上SQL的調(diào)優(yōu)經(jīng)驗(yàn)？

答案：

• 1、slow_query_log 日志中收集到的慢 SQL ，結(jié)合 explain 分析是否命中索引。
• 2、減少索引掃描行數(shù)，有針對(duì)性的優(yōu)化慢 SQL。
• 3、建立聯(lián)合索引，由于聯(lián)合索引的每個(gè)葉子節(jié)點(diǎn)包含檢索字段的信息，按最左前綴原則匹配后，再按其它條件過(guò)濾，減少回表的數(shù)據(jù)量。
• 4、還可以使用虛擬列和聯(lián)合索引來(lái)提升復(fù)雜查詢(xún)的執(zhí)行效率。

官方為什么建議采用自增id 作為主鍵？

答案：自增id是連續(xù)的，插入過(guò)程也是順序的，總是插入在最后，減少了頁(yè)分裂，有效減少數(shù)據(jù)的移動(dòng)。所以盡量不要使用字符串（如：UUID）作為主鍵。

索引為什么采用B+樹(shù)，而不用B-樹(shù)，紅黑樹(shù)？

答案：提升查詢(xún)速度，首先要減少磁盤(pán)IO次數(shù)，也就是要降低樹(shù)的高度。

• 平衡二叉樹(shù)、紅黑樹(shù)，都屬于二叉樹(shù)。時(shí)間復(fù)雜度為O(n)，當(dāng)表的數(shù)據(jù)量上千萬(wàn)時(shí)，樹(shù)的深度很深，mysql讀取時(shí)消耗大量 IO。另外，InnoDB引擎采用頁(yè)為單位讀取，每個(gè)節(jié)點(diǎn)一頁(yè)，但是二叉樹(shù)每個(gè)節(jié)點(diǎn)儲(chǔ)存一個(gè)關(guān)鍵詞，導(dǎo)致空間浪費(fèi)。
• B-樹(shù)，非葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，占用較多空間，導(dǎo)致每個(gè)節(jié)點(diǎn)的指針少很多，無(wú)形增加了樹(shù)的深度。
• B+樹(shù)數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)只存儲(chǔ)健值+指針，索引樹(shù)更加扁平，三層深度可以支持千萬(wàn)級(jí)表存儲(chǔ)。同時(shí)葉子節(jié)點(diǎn)之間通過(guò)鏈表關(guān)聯(lián)，范圍查找更快。
• 更多內(nèi)容，參考 mysql 一棵 B+ 樹(shù)能存多少條數(shù)據(jù)？

事務(wù)的特性有哪些？

答案：ACID。

• 原子性。一個(gè)事務(wù)中的操作要么全部成功，要么全部失敗。
• 持久性。永久保存在數(shù)據(jù)庫(kù)中。
• 一致性?？偸菑囊粋€(gè)一致性的狀態(tài)轉(zhuǎn)換到另一個(gè)一致性的狀態(tài)
• 隔離性。一個(gè)事務(wù)的修改在提交前，其他事務(wù)是感知不到的

如何實(shí)現(xiàn)分布式事務(wù)？

答案：

• 1、流水任務(wù)，最終一致性，前提是接口要支持冪等性
• 2、事務(wù)消息
• 3、二階段提交
• 4、三階段提交
• 5、TCC
• 6、Seata 框架
• 7、更多內(nèi)容，參考 如何解決分布式事務(wù)

日常工作中，MySQL 如何做優(yōu)化？

答案：

• 1、分頁(yè)優(yōu)化。比如電梯直達(dá)，limit 100000,10 先查找起始的主鍵id，再通過(guò)id>#{value}往后取10條
• 2、盡量使用覆蓋索引，索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢(xún)的字段，減少回表查詢(xún)
• 3、SQL優(yōu)化（索引優(yōu)化、小表驅(qū)動(dòng)大表、虛擬列、適當(dāng)增加冗余字段減少連表查詢(xún)、聯(lián)合索引、排序優(yōu)化、慢日志 Explain 分析執(zhí)行計(jì)劃）。
• 4、設(shè)計(jì)優(yōu)化（避免使用NULL、用簡(jiǎn)單數(shù)據(jù)類(lèi)型如int、減少 text 類(lèi)型、分庫(kù)分表）。
• 5、硬件優(yōu)化（使用SSD 減少 I/O 時(shí)間、足夠大的網(wǎng)絡(luò)帶寬、盡量大的內(nèi)存）

mysql 主從同步具體過(guò)程？

答案：

• master主庫(kù)，有數(shù)據(jù)更新，將此次更新的事件類(lèi)型寫(xiě)入到主庫(kù)的binlog文件中
• 主庫(kù)會(huì)創(chuàng)建log dump 線程通知slave有數(shù)據(jù)更新
• slave，向master節(jié)點(diǎn)的 log dump線程請(qǐng)求一份指定binlog文件位置的副本，并將請(qǐng)求回來(lái)的binlog存到本地的Relay log 中繼日志中
• slave 再開(kāi)啟一個(gè)SQL 線程讀取Relay log事件，并在本地執(zhí)行redo操作。將發(fā)生在主庫(kù)的事件在本地重新執(zhí)行一遍，從而保證主從數(shù)據(jù)同步

什么是主從延遲？

答案：指一個(gè)寫(xiě)入SQL操作在主庫(kù)執(zhí)行完后，將數(shù)據(jù)完整同步到從庫(kù)會(huì)有一個(gè)時(shí)間差，稱(chēng)之為主從延遲。計(jì)算公式：

• 主庫(kù)生成一條寫(xiě)入SQL的binlog，里面會(huì)有一個(gè)時(shí)間字段，記錄寫(xiě)入的時(shí)間戳 t1
• binlog 同步到從庫(kù)后，一旦開(kāi)始執(zhí)行，取當(dāng)前時(shí)間 t2
• t2-t1，就是延遲時(shí)間

注意：不同服務(wù)器要保持時(shí)鐘一致

主從延遲排查方法？

答案：通過(guò) show slave status 命令輸出的Seconds_Behind_Master參數(shù)的值來(lái)判斷

• 為零：表示主從復(fù)制良好
• 正值：表示主從已經(jīng)出現(xiàn)延時(shí)，數(shù)字越大，表示從庫(kù)延遲越嚴(yán)重

主從延遲要怎么解決？

答案：

• 看業(yè)務(wù)的接受程度。如果不能接受延遲，那么建議強(qiáng)制走主庫(kù)查詢(xún)
• 可以考慮引入緩存，更新主庫(kù)后同步寫(xiě)入緩存，保證緩存的及時(shí)性
• 提升從庫(kù)的機(jī)器配置，提高從庫(kù)binlog的同步效率
• 縮短主、從庫(kù)的網(wǎng)絡(luò)距離，減少binlog的網(wǎng)絡(luò)傳輸時(shí)間
• 一主多從，每個(gè)從庫(kù)都啟一個(gè)線程從主庫(kù)同步 binlog，導(dǎo)致主庫(kù)壓力過(guò)大，可以采用canal 增量訂閱&消費(fèi)組件，緩解主庫(kù)壓力。
• 因?yàn)閿?shù)據(jù)庫(kù)必須要等到事務(wù)完成之后才會(huì)寫(xiě)入binlog，所以減少大事務(wù)的執(zhí)行，盡量控制數(shù)量，分批執(zhí)行。
• 5.6版本之前，從庫(kù)是單線程復(fù)制，當(dāng)遇到執(zhí)行慢的sql時(shí)，就會(huì)阻塞后面的同步。5.7 版本后支持多線程復(fù)制，可以在從服務(wù)上設(shè)置slave_parallel_workers為一個(gè)大于0的數(shù)，然后把slave_parallel_type參數(shù)設(shè)置為LOGICAL_CLOCK
• 為從庫(kù)增加浮動(dòng)IP，并通過(guò)腳本檢測(cè)從庫(kù)的延遲，延遲大于指定閾值時(shí)，將浮動(dòng)IP切換至Master庫(kù)，追平后再切換回從庫(kù)。

如果數(shù)據(jù)量太大怎么辦？

答案：mysql表的數(shù)據(jù)量一般控制在千萬(wàn)級(jí)別，如果再大的話，就要考慮分庫(kù)分表。除了分表外，列舉了面對(duì)海量數(shù)據(jù)業(yè)務(wù)的一些常見(jiàn)優(yōu)化手段

• 緩存加速
• 讀寫(xiě)分離
• 垂直拆分
• 分庫(kù)分表
• 冷熱數(shù)據(jù)分離
• ES助力復(fù)雜搜索
• NoSQL
• NewSQL
• 更多內(nèi)容，參考 海量數(shù)據(jù)業(yè)務(wù)有哪些優(yōu)化手段？

分表后ID如何保證全局唯一呢？

答案：分庫(kù)分表后，多張表共用一套全局id，原來(lái)單表主鍵自增方式滿(mǎn)足不了要求。我們需要重新設(shè)計(jì)一套id生成器。特點(diǎn)：全局唯一、高性能、高可用、方便接入。

• UUID
• 數(shù)據(jù)庫(kù)自增ID
• 數(shù)據(jù)庫(kù)的號(hào)段模式，每個(gè)業(yè)務(wù)定義起始值、步長(zhǎng)，一次拉取多個(gè)id號(hào)碼
• 基于Redis，通過(guò)incr命令實(shí)現(xiàn)ID的原子性自增。
• 雪花算法（Snowflake）
• 市面的一些開(kāi)源框架，如：百度（uid-generator），美團(tuán)（Leaf）， 滴滴（Tinyid）等

分表后可能遇到的哪些問(wèn)題？

答案：分表后，與單表的最大區(qū)別是有分表鍵sharding_key，用來(lái)路由具體的物理表，以電商為例，有買(mǎi)家和賣(mài)家兩個(gè)維度，以buyer_id路由，無(wú)法滿(mǎn)足賣(mài)家的需求，反之同樣道理。如何解決？

• 分買(mǎi)家?guī)旌唾u(mài)家?guī)?，將買(mǎi)家?guī)熳鰹閷?xiě)庫(kù)，保存完整的數(shù)據(jù)關(guān)系。同時(shí)將數(shù)據(jù)異構(gòu)同步一份到賣(mài)家?guī)?，賣(mài)家?guī)炜梢灾淮鎯?chǔ)seller_id，order_id，buyer_id 等幾個(gè)簡(jiǎn)單關(guān)系字段即可，以seller_id作為分表鍵
• 多線程掃描，分段查找，然后再聚合結(jié)果
• 另外也可以存到ES中，支持多維度復(fù)雜搜索