前言
週五的早高峰, 各地軟件園地鐵站裡中出現了不少穿著長袖加絨格子衫, 背雙肩電腦包的年輕碼農, 現在節氣正值 [ 小雪 ] , 11月的全國性突然降溫 , 讓經歷過996摧殘的猿們一出地鐵站就凍的打了個激靈 , 很慶幸的告訴大家距離放年假還剩不到 37 個工作日, 要買火車票的趕緊預約搶, 要租男朋友的趕緊聯繫我.
---
前幾天我, 張大胖 , Mason 下班約好一起去吃海X撈火鍋, 大家去了都圍到到一張小火鍋桌子上, 服務員把菜單遞給我們.這時張大胖拿著菜單就開口了, "今天不要點綠色的菜, 我最近手裡的股票全部綠油油的 太護眼了, 先來個 牛油麻辣鍋底! 來3份 草原牛肉片, 來1份牛肉丸子, 來3份牛楠, 剩下的你們點吧".我吸了口氣說 "你這點全是肉, 合著哥幾個今天陪你來長膘了" .Mason 看我倆要掐起來 趕緊接上: "要不來一份西蘭花, 一份花菜, 這樣看著火鍋湯色也不錯~".張大胖皺了下眉毛, 嘆了口氣 "我前段時間去面試, 被面試官連環追問, Mysql數據庫優化 一路追問到 B+樹索引底層 ,我恨不得當場GG, 今天你又點樹, 想想就頭痛 ~" .Mason 笑了笑說 "這個Mysql B+索引, 你每天都在用但是不知道它原理, 面試官估計心裡在犯嘀咕, 這個人其它方面都合格, 但是就怕 '新同事來了,數據庫變慢了' 正確的使用並理解數據庫索引就是最好的優化反之"張大胖聽的一頭霧水然後問 到底怎麼表述數據庫優化給面試官才能抱的Offer歸呢 ?Mason 喝了口水說道 "那今天我們就邊打邊爐邊聊聊數據庫優化, 我以前專門整理過數據庫優化相關的知識"我急忙打斷說 "要不先上菜吧, 中午就吃了碗熱乾麵, 我現在太餓了"
數據庫優化主要有3個方面
- SQL層面 使用 Join 替代子查詢 (子程序查詢結果會形成臨時表) 正確書寫SQL 使索引生效 SELECT語句務必指明字段名稱 當只需要一條數據的時候，使用limit 1 排序字段沒有用到索引，就儘量少排序 Ps: 平時寫SQL ，多用 EXPLAIN 檢查SQL質量 ...
https://zhuanlan.zhihu.com/p/58065348
- 表結構層面 設計表時重中之重就是考慮這張表, 大概有什麼操作要上什麼索引. 增加冗餘字段和中間表 (空間換時間) 確定字段正確存儲範圍 ....
https://www.zhihu.com/question/19719997/answer/154809252
- 物理架構層面 數據庫緩存配置 讀寫分離 當數據庫讀遠大於寫，查詢多的情況，就可以考慮主數據負責寫操作，從數據庫負責讀操作，一主多重，從而把數據讀寫分離，最後還可以結合redis等緩存來配合分擔數據的讀操作，大大的降低數據庫的壓力。 分庫分表《阿里巴巴Java開發手冊》提出單錶行數超過500萬行或者單表容量超過2GB，才推薦分庫分表。性能由綜合因素決定，拋開業務複雜度，影響程度依次是硬件配置、MySQL配置、數據表設計、索引優化。500萬這個值僅供參考，並非鐵律。 對大表進行歷史歸檔, 比如美團外賣訂單隻能看近一年的訂單...
https://zhuanlan.zhihu.com/p/88139202
MySQL B+ Tree 索引原理
先來看看 這兩種樹形數據結構模擬自增ID索引場景 Ps: 大家有空也可以玩玩 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
兩款經典樹形數據結構,
- 左 : 二叉查找樹， 右 : 平衡二叉樹 二叉查找樹概述 若任意節點的左子樹不空，則左子樹上所有結點的值均小於它的根結點的值； 若任意節點的右子樹不空，則右子樹上所有結點的值均大於它的根結點的值； 任意節點的左、右子樹也分別為二叉查找樹； 沒有鍵值相等的節點。 假設用來做索引: 單一子樹數據發生變化時無法進行左旋右旋平衡, 會導致二叉樹嚴重不平衡, 惡劣情況查詢複雜度由 O(log n) 跌為 O(n) 的鏈表.
- 平衡二叉樹概述 它必須是二叉查找樹 . 每個節點的左子樹和右子樹的高度差至多為1。 每次數據發生變換會進行 對應的 左旋 或 右旋 來平衡樹的結構. 有效保證了+ 查詢複雜度為 O(log n).
- 假設用來做索引: 1.在平衡二叉樹中, 一顆層級為 100 樹， 找到最末端的節點, 需要IO 100 次. IO效率很低., 2. 預加載關聯節點數據少, 每次加載IO只加載一個節點, 每次IO操作一頁 (4KB數據) 。
https://blog.csdn.net/qq_37934101/article/details/81160254
---
**綜上所述: 這兩種經典樹形數據結構都不是最理想的數據庫索引樹, 噹噹噹 !!!
大名鼎鼎的 B+樹就橫空出世了**
- B+ 樹定義 B+樹包含2種類型的結點：內部結點（也稱索引結點）和葉子結點。根結點本身即可以是內部結點，也可以是葉子結點。根結點的關鍵字個數最少可以只有1個。 B+樹與B樹最大的不同是內部結點不保存數據，只用於索引，所有數據（或者說記錄）都保存在葉子結點中。 m階B+樹表示了內部結點最多有m-1個關鍵字（或者說內部結點最多有m個子樹），階數m同時限制了葉子結點最多存儲m-1個記錄。 內部結點中的key都按照從小到大的順序排列，對於內部結點中的一個key，左樹中的所有key都小於它，右子樹中的key都大於等於它。葉子結點中的記錄也按照key的大小排列。 每個葉子結點都存有相鄰葉子結點的指針，葉子結點本身依關鍵字的大小自小而大順序鏈接。
https://www.cnblogs.com/nullzx/p/8729425.html
- B+ 樹的特點 非葉子節點不存儲 data, 只存儲索引 (冗餘) 可放更多的索引. 葉子節點包括所有索引字段. 葉子節點用指針有序連接, 提高區間訪問的性能. 常見的 B+樹 為三層, 每個節點磁盤存儲默認大小 16KB, 索引節點存有多個索引和多個指針(一個索引為 bigint類型約 8KB ,一個指針約 6 KB, 最多可存 1170個索引指針組合). 一次加載一個索引節點有效利於IO資源, 讀取任意葉子節點都只需 3次 IO操作.
- **B+ 樹在 MySQL 的應用
綜上所述: 矮胖的B + 樹 恰好彌補了 瘦高平衡二叉樹的兩點不足. 層級少, IO預加載數據多.**
那在MySQL數據庫中是如何使用 B+ Tree 構建自己的索引呢?
- InnoDB 索引方式 (部分內容來自官網)
InnoDB是一種兼顧了高可靠性和高性能的通用存儲引擎。在MySQL 5.7中，InnoDB是默認的MySQL存儲引擎。除非您配置了其他默認存儲引擎，否則發出CREATE TABLE不帶ENGINE= 子句的語句將創建一個InnoDB表。
InnoDB 的主要優勢
- 它的DML操作遵循 ACID模型，並 具有具有 提交，回滾和 崩潰恢復 功能的事務， 以保護用戶數據。有關更多信息，請參見 第14.2節“ InnoDB和ACID模型”。
- 行級鎖定和Oracle風格的一致讀取可提高多用戶併發性和性能。有關更多信息，請參見第14.7節“ InnoDB鎖定和事務模型”。
- InnoDB表格將您的數據排列在磁盤上以基於主鍵優化查詢 。每個 InnoDB表都有一個稱為聚集索引的主鍵索引，該索引 組織數據以最小化主鍵查找的I / O。有關更多信息，請參見第14.6.2.1節“聚集索引和二級索引”。
- 維護數據 完整性， InnoDB支持 FOREIGN KEY約束。使用外鍵檢查插入，更新和刪除操作，以確保它們不會導致不同表之間的不一致。有關更多信息，請參見 第13.1.18.6節“外鍵約束”。 在 test_innodb表中執行此SQL 會如何使用 InnoDB 索引 ?
select * from test_innodb where name = 'to%' ``
- InnoDB 的缺點 5.7 以前不支持 全文檢索, 最常用索引, 除了它 其他MySQL 索引都被官方稱作替代存儲引擎, 地位接近封神.
- 物理層面 (一張 test_innodb表默認有 2個存儲文件組成) test_innodb.frm : 表結構定義 testinnodb.ibd : 存放 testinnodb 表的數據和索引的文件
- 使用InnoDB表時的最佳做法 。 使用查詢頻率最高的一個或多個列為每個表 指定一個主鍵， 如果沒有明顯的主鍵，則指定一個 自動增量值。 使用聯接時，無論是基於多個表中相同的ID值，還是從多個表中提取數據。為了提高連接性能，請在連接列上定義 外鍵，並在每個表中使用相同的數據類型聲明這些列。添加外鍵可確保對引用的列進行索引，從而可以提高性能。外鍵還將刪除或更新傳播到所有受影響的表，並且如果父表中不存在相應的ID，則可以防止在子表中插入數據。 關閉自動提交。每秒提交數百次會限制性能（受存儲設備的寫入速度限制）。 分組組相關的DML 操作成 交易，通過包圍他們START TRANSACTION和 COMMIT報表。雖然你不想過於頻繁地提交，你也不想發出的巨大的批次 INSERT， UPDATE或 DELETE陳述，不提交運行小時。 不使用LOCK TABLES 語句。InnoDB可以一次處理多個會話，一次讀寫同一張表，而無需犧牲可靠性或高性能。要獲得對一組行的排他性寫訪問權限，請使用 SELECT ... FOR UPDATE語法僅鎖定要更新的行。 啟用該 innodbfileper_table選項或使用常規表空間將表的數據和索引放入單獨的文件中，而不是 系統表空間中。
innodbfileper_table 默認情況下啟用 該選項。 評估您的數據和訪問模式是否受益於InnoDB表或頁面 壓縮功能。您可以在InnoDB不犧牲讀/寫能力的情況下壓縮表。
- MyISAM 索引方式
創建的表佔用的空間很小。表級鎖定限制了讀/寫工作負載中的性能，因此它通常用於Web和數據倉庫配置中的只讀或只讀工作負載中。
MyISAM 的主要優勢
- 所有數據值均以低字節開頭存儲。這使數據機和操作系統獨立。二進制可移植性的唯一要求是機器使用二進制補碼帶符號整數和IEEE浮點格式。這些要求已在主流機器中廣泛使用。二進制兼容性可能不適用於有時具有特殊處理器的嵌入式系統。
先存儲低字節數據沒有明顯的速度損失；錶行中的字節通常是未對齊的，按順序讀取未對齊的字節所需的處理要多於反向的順序。而且，與其他代碼相比，服務器中獲取列值的代碼不是時間緊迫的。 - 所有數字鍵值都先存儲高字節，以實現更好的索引壓縮。
- 在支持大文件的文件系統和操作系統上，支持大文件。 在 test_myisam 表中執行此SQL 會如何使用 MyISAM 索引 ?
select * from test_myisam where id = 20 `` - MyISAM 的缺點 不支持事務, 僅支持 表級鎖,系統奔潰後，MyISAM恢復起來比較困難.
- 物理層面 (一張 test_myisam 表默認有 3個存儲文件組成) test_myisam.frm : 表結構定義 test_myisam.MYD : 表數據 (文件後綴名全稱 Mysql Data) test_myisam.MYI : 表索引 (文件後綴名全稱 Mysql Index , 各字段索引之間獨立)
- Innodb VS Myisam 本質上的區別
經典數據庫優化面試問題
- 為什麼索引結構默認使用B + Tree，而不是hash桶，二叉查找樹，平衡二叉樹？
hash：雖然可以快速定位，但是沒有順序，IO複雜度高。二叉樹：樹的高度不均勻，不能自平衡，查找效率跟數據有關（樹的高度），並且IO代價高。平衡二叉樹：樹的高度隨著數據量增加而增加，IO代價高。
B-Tree: 常見的 B+樹 為三層, 每個節點磁盤存儲默認大小 16KB, 索引節點存有多個索引和多個指針(一個索引為 bigint類型約 8KB ,一個指針約 6 KB, 最多可存 1170個索引指針組合). 一次加載一個索引(16KB)節點有效利於IO資源, 讀取當前索引任意葉子節點都只需 3次 IO操作.
- 下列這行SQL 不使用索引的原因 ?
select * from test_innodb where name = '%to%' ``
先問是不是再問為什麼, 我們先建一張表測試一波, 全模糊匹配是否使用索引 !
<code>-- auto-generated definition
create table test_fuzzytext_index
(
id int auto_increment primary key,
context text not null,
context_fulltext text not null,
context_index varchar(100) not null
);/<code>給 testfuzzytextindex 的 文本類型的一些字段 加上索引形成和不加索引的對照組 !
<code> -- 0. context 字段不添加索引
-- 1. context_index 字段 添加INDEX(普通索引)
ALTER TABLE test_fuzzytext_index ADD INDEX index_name (context_index);
-- 2. context_fulltext 字段 添加FULLTEXT(全文索引)
ALTER TABLE test_fuzzytext_index ADD FULLTEXT (context_fulltext);/<code>最終表結構
Explain - Type 指標
訪問類型，SQL查詢優化中一個重要指標，查詢性能從好到壞依次是
system > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL
一般來說，好的SQL查詢至少達到range級別，最好能達到ref。
- system：const連接類型的特例，表只有一行記錄（等於系統表，平時不會出現，可以忽略不計）
- const：表中有且只有一個匹配行時使用，對主鍵或唯一索引的查詢，效率最高，將主鍵置於WHERE列表中，MySQL就能將該查詢轉換為一個const
- eq_ref：唯一性索引或主鍵查找，對於每個索引鍵，表中只有一條記錄與之匹配
- ref：非唯一性索引查找，返回匹配某個單獨值的所有行（多行）
- ref_null：類似ref類型，附加對NULL值列的查詢
- index_merge：索引合併優化方法（MySQL 5.6以後）
- range：索引範圍掃描，常見於bettween、、in查詢，這種索引列上的範圍掃描比全索引掃描要好。只需要開始於某個點，結束於另一個點，不用掃描全部索引
- index：全表索引掃描，使用索引而非數據行掃描
- ALL：全表掃描
index與ALL區別：index類型只遍歷索引樹，索引文件通常比數據文件小（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取）。
Explain - Extra 指標
- distinct：優化distinct操作，在找到第一個匹配的元組後即停止找同樣值的動作
- Not exists：使用not exists優化查詢
- Using filesort：使用額外操作進行排序，而不是按照索引進行排序，通常出現在
- order by或group by查詢
- Using index：使用覆蓋索引進行查詢，效率高
- Using temporary：使用臨時表處理查詢，常用於排序、子查詢、分組查詢
- Using where：MySQL服務器層使用WHERE條件過濾數據
- select tables optimized away：直接通過索引獲得數據，不用訪問表
預先隨機插入一些文本數據到表中. 使用查詢語句.
<code>explain select * from test_fuzzytext_index tfi where tfi.context like '%索引%'/<code>
<code>explain select * from test_fuzzytext_index tfi where tfi.context like '索引%'/<code>
分析結果:
%索引% type = ALL Extra = Using where , 結果集 50+ 耗時平均 130ms
索引% **type = ALL Extra = Using where , 結果集 50+ 耗時平均 130ms
綜上所述: 沒有索引的字段默認全表掃描**
<code>explain select * from test_fuzzytext_index tfi where tfi.context_index like '%索引%'/<code>
<code>select * from test_fuzzytext_index tfi where tfi.context_index like 索引%';/<code>
分析結果:
%索引% type = ALL Extra = Using where , 結果集 50+ 耗時平均 130ms
索引% type = renge Extra = Using where , 結果集 50+ 耗時平均 125ms
綜上所述: %匹配詞% 全模糊 沒有使用索引, 右模糊type 為 renge ,
<code>select * from test_fuzzytext_index where MATCH(context_fulltext) AGAINST ('+索引' in boolean mode); /<code>
分析結果:
使用了全文索引後 type = fulltext Extra = Using where; Fthints: noranking, 結果集 50+ 耗時平均 59ms
綜上所述: 全文檢索type為 fulltext, 注意目前 MySQL 全文索引只支持根據空格分詞, 意思是 中文分詞要提前用空格分詞存入, 僅適合數據量小的場景. 隨著數據增加檢索速度也會和普通索引拉開差距.
<code>select * from test_fuzzytext_index where MATCH(context_fulltext) AGAINST ('+索引' in boolean mode); /<code>總結一下 %代表一個或多個字符的通配符, %關鍵字% 場景不使用索引, 只會進行全表掃描. 如果有搜索檢索文章內容的需求, 可以使用 fulltext 索引 滿足大多數 搜索場景. MySQL 5.7 支持.
那為什麼 %關鍵字% 不使用索引呢?
在where條件後對索引字段加了函數轉換或者運算邏輯（+、-、*、/、！、<>、%、like'%_'（%放在前面）、or、in (疑問、可能存在成本問題)、exist等）的處理，比如對時間戳字段進行日期格式化函數都會引起索引失效。
被優化器分析後, 發現走索引還不如不走索引, 效率更高.
- 為什麼常見的 B+ 樹是三層, 可以更多嗎?
B+ 樹 初始化時只有一層, 會隨著數據擴大進行樹的層級擴容, 那麼MySQL生產環境是怎麼樣的呢 ?
<code>索引節點: 一個索引為 bigint類型約 8 KB ,一個指針約 6 KB, 最多可存 1170個索引指針組合 數據節點: 大約 16KB , 索引節點不存儲數據 , Mysql 一頁 為 16 KB. MySQL B+ Tree 三層樹的最大行數為 : 1170 * 1170 * 16 = 21,902,400/<code>
+ Mysql 數據庫索引的常用種類有幾種,每種場景是什麼 ?主要有兩種, 一種是 InnoDB 索引, 一種是 替代索引方案.
InnoDB 是 Mysql 5.7 的默認索引, 支持現代數據庫理念的一切操作, 比如 事務, 行級鎖, 數據庫可恢復性好,默認順序索引等...
替代索引方案中, 在一定的特定場景可用, 比如 MyISAM 可以在大量查詢場景使用. CSV 可以用來做 數據分析, 使用場景有限.
- InnoDB索引方式 與 MyISAM索引方式 的不同點 ?
- 三個外鍵關聯表, 怎麼寫 SQL 會使用索引 ?
使用多列索引並根據最左匹配原則, 保證表結構設計階段主表與關聯表之間的關聯字段的數據類型、數據長度、字段的編碼格式以及字段的排序規則需要保持一致 .
在Mysql建立多列索引（聯合索引）有最左前綴的原則，即最左優先。
如果我們建立了一個2列的聯合索引(col1,col2),實際上已經建立了兩個聯合索引(col1)、(col1,col2）;
如果有一個3列索引(col1,col2,col3)，實際上已經建立了三個聯合索引(col1)、(col1,col2)、(col1,col2,col3)。
1、b+樹的數據項是複合的數據結構，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道第一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪裡查詢。
2、比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等於張三的數據都找到，然後再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。（這種情況無法用到聯合索引）
- 使用多列索引時,怎麼寫 SQL 會使用索引?
當一張表的查詢方式比較固定，這時候可以嘗試創建多列索引，查詢時應當遵從組合索引的規則，最左原則，查詢時使用最頻繁的一列放在最左邊，
<code>例：index(user_id,user_name,user_type)這是一個組合索引，當查詢時如果想走索引則
sql：select * from userInfo where user_id='001' and user_name='小張' and user_type='1'；
-- 這個時候是走了索引的，但是
select * from userInfo where user_name='小張' and user_type='1'；
-- 這時user_id沒有在where條件內將不走索引；
-- 此例，user_id字段必須出現在where後面，不然索引將不會生效。/<code>看完不妨問問自己如何回答這些問題, 也可寫上在評論區留言回答, 溫故知新 ..
關注我的公眾號 20K+
深入淺出分享 Java 乾貨 , 找回對代碼的 Passion , 助力月入 20K+
