索引
基本概念：索引是在存盤引擎層實作的，而不是在服務器層實作的，所以不同存盤引擎具有不同的索引型別和實作，
資料結構
- Tree 指的是 Balance Tree，也就是平衡樹，平衡樹是一顆查找樹，并且所有葉子節點位于同一層，
- B+ Tree 是基于 B Tree 和葉子節點順序訪問指標進行實作，它具有 B Tree 的平衡性，并且通過順序訪問指標來提高區間查詢的性能，
- 在 B+ Tree 中，一個節點中的 key 從左到右非遞減排列，如果某個指標的左右相鄰 key 分別是 keyi 和 keyi+1，且不為 null，則該指標指向節點的所有 key 大于等于 keyi 且小于等于 keyi+1，
基本原理
- 進行查找操作時，首先在根節點進行二分查找，找到一個 key 所在的指標，然后遞回地在指標所指向的節點進行查找，直到查找到葉子節點，然后在葉子節點上進行二分查找，找出 key 所對應的 data，
- 插入洗掉操作會破壞平衡樹的平衡性，因此在進行插入洗掉操作之后，需要對樹進行分裂、合并、旋轉等操作來維護平衡性，
紅黑樹
紅黑樹等平衡樹也可以用來實作索引，但是檔案系統及資料庫系統普遍采用 B+ Tree 作為索引結構，這是因為使用 B+ 樹訪問磁盤資料有更高的性能，
- B+ 樹有更低的樹高：平衡樹的樹高 O(h)=O(logdN)，其中 d 為每個節點的出度，紅黑樹的出度為 2，而 B+ Tree 的出度一般都非常大，所以紅黑樹的樹高 h 很明顯比 B+ Tree 大非常多，
- 磁盤訪問原理：作業系統一般將記憶體和磁盤分割成固定大小的塊，每一塊稱為一頁，記憶體與磁盤以頁為單位交換資料，資料庫系統將索引的一個節點的大小設定為頁的大小，使得一次 I/O 就能完全載入一個節點，
如果資料不在同一個磁盤塊上，那么通常需要移動制動手臂進行尋道，而制動手臂因為其物理結構導致了移動效率低下，從而增加磁盤資料讀取時間，B+ 樹相對于紅黑樹有更低的樹高，進行尋道的次數與樹高成正比，在同一個磁盤塊上進行訪問只需要很短的磁盤旋轉時間，所以 B+ 樹更適合磁盤資料的讀取， - 磁盤預讀特性：為了減少磁盤 I/O 操作，磁盤往往不是嚴格按需讀取，而是每次都會預讀，預讀程序中，磁盤進行順序讀取，順序讀取不需要進行磁盤尋道，并且只需要很短的磁盤旋轉時間，速度會非常快，并且可以利用預讀特性，相鄰的節點也能夠被預先載入，
索引分類
B+樹索引
- 是大多數 MySQL 存盤引擎的默認索引型別，
- 因為不再需要進行全表掃描，只需要對樹進行搜索即可，所以查找速度快很多，
- 因為 B+ Tree 的有序性，所以除了用于查找，還可以用于排序和分組，
- 可以指定多個列作為索引列，多個索引列共同組成鍵，
- 適用于全鍵值、鍵值范圍和鍵前綴查找，其中鍵前綴查找只適用于最左前綴查找，如果不是按照索引列的順序進行查找，則無法使用索引，
- InnoDB 的 B+Tree 索引分為聚簇索引（主索引）和非聚簇索引（輔助索引），
- 唯一索引：主鍵就是一種特殊的唯一索引，唯一索引允許null值，但主鍵列不允許為null值，一張表最多建議一個主鍵
哈希索引
- 哈希索引能以O（1）時間進行查找，但是失去了有序性特點InnoDB
- 存盤引擎有一個特殊的功能叫“自適應哈希索引”，當某個索引值被使用的非常頻繁時，會在 B+Tree 索引之上再創建一個哈希索引，這樣就讓 B+Tree 索引具有哈希索引的一些優點，比如快速的哈希查找，
全文索引
- MyISAM 存盤引擎支持全文索引，用于查找文本中的關鍵詞，而不是直接比較是否相等，
- 查找條件使用 MATCH(clo1,clo2..) AGAINST(value)，而不是普通的 WHERE，全文索引使用倒排索引實作，它記錄著關鍵詞到其所在檔案的映射，
- InnoDB 存盤引擎在 MySQL 5.6.4 版本中也開始支持全文索引，
FULLTEXT (clo1,clo2)只有建立的全文索引的列可以進行全文索引
索引優化
獨立的列
進行查詢時，索引列不能是運算式的一部分，也不能是函式的引數，否則無法使用索引
多列索引
在需要使用多個列作為條件進行查詢時，使用多列索引比使用多個單列索引性能更好，
列如下面的陳述句中可以將actor_id和film_id設定多列索引
SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;
索引列的順序
索引的選擇性是指：不重復的索引值和記錄總數的比值，最大值為 1，此時每個記錄都有唯一的索引與其對應，選擇性越高，每個記錄的區分度越高，查詢效率也越高，
讓選擇性最強的索引列放在最前面
輸入陳述句：
SELECT COUNT(DISTINCT addr_id)/COUNT(*) AS addr_id_selectivity,
COUNT(DISTINCT user_id)/COUNT(*) AS user_id_selectivity,
COUNT(*)
FROM user_address;
結果：
addr_id_selectivity：1.0000
user_id_selectivity：0.1250
count(*)：64
從上述結果分析：主鍵是唯一的，user_id會有重復，所以寫sql陳述句時，主鍵應該放在最前面
這樣可以逐個分析對應的索引
前綴索引
- 對于 BLOB、TEXT 和 VARCHAR 型別的列，必須使用前綴索引，只索引開始的部分字符，
- 前綴長度的選取需要根據索引選擇性來確定，某些列，一般是字串型別，很長，全部作為索引大大增加存盤空間，索引也需要維護，對于長字串，又想作為索引列，一個可取的辦法就是取前一部分（前綴），代表一整列作為索引串，
- 如何確保這個前綴能代表或大致代表這一列？所以mysql中有個概念是索引的選擇性，是指索引中不重復的值的數目（也稱基數）與整個表該列記錄總數（#T）的比值，比如一個串列（1,2,2,3），總數是4，不重復值數目為3，選擇性為3/4，因此選擇性范圍是[1/#T, 1]，這個值越大，表示列中不重復值越多，越適合作為前綴索引，唯一索引（UNIQUE KEY）的選擇性是1，
MySQL性能優化神器Explain使用分析
- Explain 用來分析 SELECT 查詢陳述句，開發人員可以通過分析 Explain 結果來優化查詢陳述句，
- select_type : 查詢型別，有簡單查詢、聯合查詢、子查詢等
- key : 使用的索引
- rows : 掃描的行數
總結
- 資料庫只做兩件事情：存盤資料、檢索資料，而索引是在你存盤的資料之外，額外保存一些路標（一般是B+樹），以減少檢索資料的時間，所以索引是主資料衍生的附加結構，
- 一張表可以建立任意多個索引，每個索引可以是任意多個欄位的組合，索引可能會提高查詢速度（如果查詢時使用了索引），但一定會減慢寫入速度，因為每次寫入時都需要更新索引，所以索引只應該加在經常需要搜索的列上，不要加在寫多讀少的列上，
- 多使用explain對陳述句進行索引分析，任何理論都是建立在實際的分析基礎上，explain陳述句關注那幾個關鍵詞即可，盲目的加索引并不是好的，有時候索引反而成為拖慢我們系統運行速度的源頭.優化索引其實就兩件事，第一找到區別度大的列，第二看生產的查詢場景是否有用到這個列，加上索參考explain分析，看是否會比不加上索引性能提升更快
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/237883.html
標籤：其他
上一篇：2.pandas常用讀取
下一篇：3.DataFrame的增刪改查