溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL索引知識點分析”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL索引知識點分析”文章能幫助大家解決問題。
索引在關系型數據庫中，是一種單獨的、物理的對數據庫表中的一列或者多列值進行排序的一種存儲結構，它是某個表中一列或者若干列值的集合，還有指向表中物理標識這些值的數據頁的邏輯指針清單。
索引的作用相當于圖書的目錄，可以根據目錄重點頁碼快速找到所需要的內容，數據庫使用索引以找到特定值，然后順著指針找到包含該值的行，這樣可以是對應于表的SQL語句執行得更快，可快速訪問數據庫表中的特定信息。
在InnoDB里面，索引類型有三種，普通索引、唯一索引（主鍵索引是特殊的非空的唯一索引）、全文索引。
普通（Normal）：也叫非唯一索引，是普通索引，沒有任何限制。唯一（Unique）：唯一索引要求鍵值不能重復（可以為空），主鍵索引其實是一種特殊的唯一索引，不過他還多了一個限制條件，要求鍵值不能為空。主鍵索引用 primary key 創建。全文（Fulltext）：針對比較大的數據，比如我們存放是文章，課文，郵件，等等，有可能一個字段就需要幾kb，如果要解決like查詢在全文匹配的時候效率低下的問題，可以創建全文索引。只有文本類型的字段才可以創建全文索引，比如char、varchar、text。MyISAM和InnoDB都支持全文索引。
一句話總結：
索引能夠提高數據檢索的效率，降低數據庫的IO成本。
提出問題：我們用空間換時間，但是他的數據結構、查詢的IO成本、以及是如何存儲數據的呢?
我們以一個 Page 的視角去看我們的B+樹演進過程。
頁是InnoDB管理存儲空間的基本單位，InnoDB將數據庫中的數據都是存儲在頁這個基本存儲單位?的；頁也是內存和磁盤交互的基本單位，數據庫從磁盤中讀取若?個頁??的數據到內存，也將內存中若?個頁??的數據刷新到磁盤中。
?個頁的內存??為16KB。
假設我們要執行這個SQL，得到了10條記錄：
SELECT * FROM INNODB_USER LIMIT 0 , 10;
假如一條記錄的數據大小是4K，那么我們一個Page頁能存多少條數據呢？
16K 除以 4K 得到 4條記錄,對吧。
Page里面的每一條數據都有一個關鍵的屬性叫做record_type
0 普通用戶記錄 1 目錄的索引記錄 2 最小 3 最大
畫個圖示例一下頁里面數據是怎么放的：
這個是我們的Page頁，每個Page頁都會存放數據，按照主鍵有序存放數據
我們知道數據的存儲是順序IO的，方便存放，可是存放方便那查詢是不是就不方便了，如果查的是最后一個是不是要遍歷整個頁的數據？
假如我們要查一條數據要怎么查?怎么才能快速查到數據？
如果我們Page頁中的數據是有連接方式的，想想我們學過的數據結構，哪種結構查詢快？
如果我們Page頁中的數據是有連接方式的，就能夠解決啊！沒錯，就是鏈表
Page頁中的數據是怎么連接的(數據在同一個頁中)：
MySQL把頁中的數據通過單向鏈表連接起來，如果是根據主鍵去查詢，使用二分法定位會非常快，如果是根據非主鍵索引去查，只能從最小的一個個開始遍歷單向鏈表。
多個Page頁是怎么建立連接（數據在不同的頁中）：
MySQL把不同的頁通過雙向向鏈表建立鏈接，這樣我們就可以通過上一頁找到下一頁，通過下一頁找到一頁，由于我們現在不能快速定位到數據的所在頁，我們只能從第一個頁沿著雙向鏈表一直往下找，在每個頁中再按照在同一頁的方式去查找指定的記錄，這個也是全表掃描嘛。
當Page頁越來越多，查詢會出現什么問題、怎么解決怎么優化？
當我們鏈表記錄變多，由于不能直接定位，我們出現了查詢緩慢問題，深入思考，所謂的查詢緩慢，其實就是下面兩個問題：
查詢時間的復雜度0（N）
讀寫磁盤的IO次數過多
我們想一下，平時看書時，想找某一頁的資料，怎么做的？
查目錄對不對？目錄是個啥？不就是索引嘛！
百度上隨便找個目錄，貼個圖：
我們發現，這個目錄里面有兩個很重要的信息：
內容簡介（章節標題）
所在的頁碼
我們這個我們參考一個圖書的目錄的思想來達到我們快速查詢數據的目的：
給數據加一個目錄，查數據，我們先根據目錄頁找到數據在哪個頁的哪個地方，提升查詢性能。
可是，
建目錄是不是要有規律？比如字典的目錄就是根據字母順序建立的，你想到了什么？沒錯就是主鍵，Mysql里自增的主鍵剛好符合我們的要求，有規律，內容還少，而且不可重復，真是完美的目錄，我們將每一頁的主鍵按規律存儲一下，添加一個指針指向數據的位置，查詢時直接根據主鍵大小，用二分法快速找到目錄，然后找到數據。
但是我們要給每一個數據頁都建目錄嗎？好像還必須如此，不給每一個頁建數據，你怎么定位到頁里的數據？難道全頁掃描嗎？
但是給每一個頁都建目錄，隨著目錄頁也出現多個，我們一個個目錄也去遍歷查詢性能也會下降。
我們可不可以給目錄建一個目錄？
于是，我們可以通過為目錄頁也建立一次目錄，向上抽取一層根結點，這樣就更加便于我們進行查詢了。
這棵樹，因為是根據主鍵存儲的，所以我們把它稱之為主鍵索引樹，因為主鍵索引樹里存儲了我們的表里的所有數據，那么在MySQL中 索引即數據，數據即索引也是這個原因了。
這就是MysqlB+樹主鍵索引樹的數據結構，怎么樣，是不是比你直接死記硬背得到的知識印象更深刻
我們找到了提升查詢性能的辦法，那么，當Page頁出現增加、修改、刪除，都會遇到什么問題？
如果是有序增加，新增一條數據怎么辦？
頁寫滿了，那么是不是得開啟一個新頁！
并且頁的數據必須滿足一個條件：下一個數據頁中用戶記錄的主鍵值必須大于上一個頁中用戶記錄的主鍵值
因為是有序增加，我們直接在頁的雙向鏈表末端增加一個頁即可。
那如果是無序增加，新增一條數據怎么辦？
開啟一個新頁，并且找到數據的位置。
把舊數據移動到新頁，把新的數據放到有序的位置上。
葉子結點數據一直平移。
觸發葉子結點數據Page頁的分裂與合并觸發上層葉結點和根結點的再次分裂與合并。
這叫什么，“牽一發而動全身”,也叫做頁分裂！！
總結：Page頁出現增加、修改、刪除遇到的問題：
我們可以說，當無序增加、更新主鍵ID、刪除索引頁的更新操作時候，會有大量的樹結點調整，觸發子葉結點Page頁和上層葉結點和根節點頁的分頁與合并，造成大量磁盤碎片，損耗數據庫的性能，也就是解釋了我們為什么不要在頻繁更新修改的列上建索引，或者是不要去更新主鍵。
讓我們總結一下：
聚集索引（聚簇索引）:
主鍵索引樹也叫聚集索引或者是聚簇索引，在InnoDB中一張表只有一個聚集索引樹，如果一張表創建了主鍵索引，那么這個主鍵索引就是聚集索引，我們是根據聚集索引樹的鍵值，決定數據行的物理存儲順序，我們的聚集索引會對表中的所有列進行排序存儲，索引即數據，數據即索引，指的就是我們的主鍵索引樹啦。
為什么主鍵ID最好是趨勢遞增的？
你剛剛看完啊，不會沒記住吧，有序遞增，下一個數據頁中用戶記錄的主鍵值必須大于上一個頁中用戶的主鍵值，假如我是趨勢遞增，存入的數據肯定是在最末尾鏈表或者新增一個鏈表，就不會觸發頁的分裂與合并，導致添加的速度變慢。
三層B+數能存多少數據？
考察點：Page頁的大小，B+樹的定義
1GB = 1024 M， 1mb = 1024k，1k= 1024 bytes
答：
已知：索引邏輯單元 16bytes 字節，16KB=16* 1024*1024，肯定比一千萬多，在InnoDB中B+樹的深度為3層就能滿足千萬級別的數據存儲。
mysql 大字段為什么要拆分？
一個Page頁可存放16K的數據，大字段占用大量的存儲空間，意味著一個Page頁可存儲的數據條數變少，那么就需要更多的頁來存儲，需要更多的Page，意味著樹的深度會變高。那么磁盤IO的次數會增加，性能下降，查詢更慢。大字段不管是否被使用都會存放在索引上，占據大量內存空間壓縮Page數據條數。
為什么用B+樹？
B+樹的底層是多路平衡查找樹，對于每一次的查詢的都是從根節點觸發，到子葉結點才存放數據，根節點和非葉子結點都是存放的索引指針，查找葉子結點互，可以根據鍵值數據查詢。掃庫、掃表能力更強排序能力更強查詢效率和查詢性能穩定存儲能力更強、三層B+樹就能存儲千萬級別的數據。
剛才看的是根據主鍵得來的索引，我們如果不查主鍵，或者說表里壓根就沒有主鍵，怎么辦？我們還可以根據幾個字段來創建聯合索引(組合索引聚合索引。。哎呀名字而已怎么叫都行)。
根據主鍵得到的索引樹叫主鍵索引樹，根據別的字段得到的索引樹叫二級索引樹。
通過下面的SQL 可以建立一個組合索引
ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');其實，看似建立了1個索引，但是你使用 age 查詢 age，user_name 查詢 age，user_name，phone 都能生效
您也可以認為建立了三個這樣的索引：
ALTER TABLE INNODB__USER ADD INDEX
SECOND_INDEX_AGE__USERNAME_PHONE('age');
ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name');
ALTER TABLE `INNODB_USER`ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');首先需要知道參與排序的字段類型是否有有序？
如果是有序字段，就按照有序字段排序比如（int） 1 2 3 4。
如果是無序字段，按照這個列的字符集的排序規則來排序，這點不去深入，知道就好。
我現在有一個組合索引（A-B-C）他會按照你建立字段的順序來進行排序：
如果A相同按照B排序，如果B相同按照C排序，如果ABC全部相同，會按照聚集索引進行排序。
我們的Page會根據組合索引的字段建立順序來存儲數據，年齡 用戶名 手機號。
它的數據結構其實是一樣的
還是上面那個索引，年齡用戶名手機號，age,username,phone
那么可以看到我們第一個字段是AGE，如果需要這個索引生效，是不是在查詢的時候需要先使用Age查詢，然后如果還需要user_name，就使用user_name。
只使用了user_name 能使用到索引嗎？
其實是不行的，因為我是先使用age進行排序的，你必須先命中age，再命中user_name，再命中phone，這個其實
就是我們所說的最左匹配原則。
最左其實就是因為我們是按照組合索引的順序來存儲的。大家常說的"索引橋"也是這個原因。命中組合索引必須是像過橋一樣，必須現在從第一塊木板走到第二塊木板再走到第三塊木板。
二級索引樹有三個重要的概念，分別是回表、覆蓋索引、索引下推。.
回表就是：我們查詢的數據不在二級索引樹中需要拿到ID去主鍵索引樹找的過程。
覆蓋索引就是：我們需要查詢的數據都在二級索引樹中，直接返回這種情況就叫做覆蓋索引。
索引下推（index condition pushdown ）簡稱ICP：在Mysql5.6以后的版本上推出，用于優化回表查詢；
為什么離散度低的列不走索引？
離散度是什么概念？相同的數據越多離散度越低，相同的數據越少離散度就越高。
請問都是相同的數據，怎么排序？沒辦法排序啊？
在B+Tree 里面重復值太多，MySQL的優化器發現走索引跟使用全表掃描差不了多少的時候，就算建立了索引也不會走。走不走索引，是MySQL的優化器去決定的。
索引是不是越多越好？
空間上：用空間換時間，索引是需要占用磁盤空間的。
時間上：命中索引，加快我們的查詢效率，如果是更新刪除，會導致頁的分裂與合并，影響插入和更新語句的響應時間，反而延緩性能。
如果是頻繁需要更新的列，不建議建立索引，因為頻繁觸發頁的分裂與合并。
也叫作組合索引（復合索引），二級索引樹存儲的是我們創建索引時候的保存了列名順序來存儲的，它只保存了創建二級索引列名的部分數據，二級索引樹是為了輔助我們查詢，提高查詢效率誕生的，二級索引樹里有三個動作：回表、覆蓋索引、索引下推。其中，性能最高的是覆蓋索引。
網上找了一張區別圖
關于“MySQL索引知識點分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
