MySQL的索引, 到底怎么創建?

前言

MySQL的數據結構

索引是一把雙刃劍

索引創建原則

如何給一個列挑選索引???

索引列的基數, 要盡量小

?索引列的類型盡量小

索引長字符串的前綴

不要對索引列進行計算操作或者函數計算.

不要老想著查詢, 想想插入該怎么辦?

避免索引冗余和重復

前言

今天在滴滴的2022后端開發的面試題里面看到了這個面試題:

但是下面網友發表的觀點實在讓人難以釋懷 ....?

這種:?

還有這種:?

????????他們這些回答, 都達到點子上了, 但是也會遺漏一些基本的概念, 和索引使用原則. 或者是答得不太嚴謹, 例如 : 唯一性低的, 就不能加索引了嗎???數據量大, 就不能加索引了嗎?? 還有三樓這位電子科技大學的老哥, 什么交覆蓋索引(聚簇索引)? 覆蓋索引后面打了一個小括號解釋, 覆蓋索引就是聚簇索引??

? ? ? ? 看到這里, 本樓主倍感傷心, 看到這個回復時間, 也才離我現在不到半年吧.? 本樓主接下來就會來解救大家.

? ? ? ? 首先我們來回顧一下MySQL的索引結構到底是怎么樣的.

MySQL的數據結構

首先你得了解什么是二叉樹吧, 這個概念希望你能自己去弄明白.

大家可以思考一下, mysql底層是通過磁盤 + 內存的形式存儲數據的.??

? ? ? ? 磁盤存儲數據文件我當然知道, 那內存怎么也會存儲數據? 別忘了, MySQL是具備查詢緩存功能的, 也就是創建tcp連接之后的查詢, 每次查詢都會先去MySQL的內存緩存中查詢是否存在對應的數據集, 如果有, 就直接返回, 減少了MySQL的運行壓力, 如果沒有, 那么就去innodb中查找.

如下, 這是我存儲在磁盤下的一張表:?

? ? ? ? ?此表是通過innodb的存儲引擎的形式存儲的. 我們知道, innodb存儲的表, 是通過兩個文件來存儲的, 如果你創建一個名為test的數據庫, sql為:?

create database test;

? ? ? ? 那么就會在你當前所使用的data目錄底下創建一個test子文件夾:

????????里面存放著這個test數據庫的所有數據, 然后我在這個test中創建表, 那么這個表的數據就會被存放在這個test文件夾中:?

例如我創建一個名為t_car的表, MySQL就會為我創建下面兩個文件:?

其中.frm是存儲的表結構, 一個表中有很多個列, 這個.frm文件就是存儲的每個列的數據類型是什么呀, 有啥約束條件和索引呀, 以及他們的字符集和比較規則等等信息, 是以二進制文件存儲的.
ibd為后綴的文件, 就是我們存儲實際數據的地方了, 也稱之為獨立表空間文件. 這個ibd文件比較特殊, 我們稍后講解.

除了獨立表空間之外, 我們在安裝MySQL之后, 系統會為我們維護一些數據, 例如當前MySQL系統的狀態之類的, 這些數據被存放在系統表空間之中.

? ? ? ? ?首先這個獨立表空間就是我們自己創建的表存放的文件, 既然一個表只存在兩個文件, ,frm文件存放存儲的表的結構, .ibd存放數據記錄, 那么索引存放在哪? 那到底什么是索引??

? ? ? ? 首先回答第一個, MySQL在設計的時候, 針對于innodb搜索引引擎來說, 是將表的結構和數據分開存儲在兩個文件之中, 既然.frm存放表的結構, MySQL中的ibd文件就是用來存放數據和索引結構的. 當然你也可以說, 數據是需要有組織的存放的, 不是一條一條的存放成鏈表這種簡單的形式.

? ? ? ? 我們可能是需要把這些數據維護成一種特殊的查詢數據結構, 才可以很快的進行查詢操作.

上圖中的每一個藍色或者淺藍色的就為一個數據頁或者索引頁, 索引頁只存放當前索引列和頁號, 數據頁則存放索引列和主鍵id.

我們都知道, MySQL存在聚簇索引和非聚簇索引兩大類, 那么什么是聚簇索引???

? ? ? ? innodb會自動為主鍵創建全列索引, 也就是以b+樹的形式創建索引, 葉子結點存放的是全部列的數據, 如果沒有主鍵, 那么MySQL就會為當前表中unique索引創建聚簇索引, 如果你都未指定, 那么MySQL會隱式的幫你維護一個主鍵并以此創建聚簇索引, 無論以哪種方式創建, 其都會為你以全列的形式進行排序. 也就是說, 我們通過聚簇索引, 就可以找到這種表中的任何信息.

那非聚簇索引呢??

? ? ? ? 非聚簇索引就是指的二級索引(沒有被用來創建聚簇索引的索引, 例如unique, index), 此時MySQL也會創建一個b+樹來進行維護, 只不過此時葉子結點存放的事主鍵+ 索引列的數據, 然后對索引列進行排序的一個數據結構.

? ? ? ? 上述兩個索引, 都是存放在ibd這個后綴的文件之中的.

????????如果是利用二級索引來查找, 那么就是根據二級索引列的b+樹結構, 快速找到對應的數據頁, 然后再數據頁中進行查找, 找到對應的數據記錄, 只不過現在的二級索引數據記錄只存放了索引列和對應數據的id, 如果你僅僅只是查找當前這個索引列和id這一個數據, 那么就會觸發回表操作, 但是如果你還存放別的數據, 是當前索引列中沒有的, 那么就會觸發回表, 到聚簇索引中根據主鍵id來查詢數據.

索引是一把雙刃劍

既然可以讓查詢變快, 那么我給每一列創建一個索引就完事了. 真的完事了嗎??

? ? ? ? 在空間上, 顯而易見, 你每給一個列創建索引, 就會在ibd后綴的文件中創建一個b+樹, b+樹的每一個數據頁和索引頁, 都會占用一定的磁盤空間,?

? ? ? ? 在時間上, 紅黑樹你總應該了解吧, 我每次刪除一個紅黑樹結點, 就不得不去旋轉, 以此來平衡樹的高度,?每次對表中的數據進行增、刪、改操作時，都需要去修改各個B+ 樹索引。而且我們講過， B+ 樹每層節點都是按照索引列的值從小到大的順序排序而組成了雙向鏈表。不論是葉子節點中的記錄，還是內節點中的記錄（也就是不論是用戶記錄還是目錄項記錄）都是按照索引列的值從小到大的順序而形成了一個單向鏈表。而增、刪、改操作可能會對節點和記錄的排序造成破壞，所以存儲引擎需要額外的時間進行一些記錄移位，頁面分裂、頁面回收啥的操作來維護好節點和記錄的排序。如果我們建了許多索引，每個索引對應的B+ 樹都要進行相關的維護操作，這還能不給性能拖后腿么？

說了這么多, 既然索引創建有這么多壞處, 那我到底該什么時候創建索引??

索引創建原則

? ? ? ? 我們創建一個索引, 除了考慮時間和空間上的差異, 還要了解, 索引是怎么生效的, 其實索引就是講對應的索引列進行排序, 然后將其分為很多個數據頁, 一個數據頁(16kb)可能存放很多個記錄, 然后通過類似于查字典的形式(你想想, 英語詞典里面的單詞, 是亂序的嘛, 肯定不是啊, 是通過首字母進行總體的排序, 基于第一個字母排序的原則, 然后根據第二個字母排序, 以此類推), 通過不斷的縮小數據所在的區間, 然后到了最小的數據頁級別的時候, 就采用二分查找等高效的查詢方式, 快速定位到數據記錄.

? ? ? ? 如果你在使用索引的時候, 沒有使用到原本索引列就是有序的這個原則, 那么索引就會實效.?

就拿我們的多列索引來舉例子, 下面有一個表為:?

CREATE TABLE `t_car` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',`car_num` varchar(255) DEFAULT NULL COMMENT '汽車編號',`brand` varchar(255) DEFAULT NULL,`produce_time` char(10) DEFAULT NULL,PRIMARY KEY (`id`),index idx_carnum_brand_pt(car_num, brand, produce_time)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1

這個表有四個字段:?

主鍵id
car_num : 汽車編號
brand : 汽車品牌
produce_time: 汽車生產時間

擁有兩個索引:?

一個是主鍵的聚簇索引
一個是關于car_num, brand, produce_time, 并依次為順序的多列索引.?

分析:?

? ? ? ? 上面所述的多列索引, 就是通過carnum先排序, 然后在carnum的有序的基礎上, 然后再通過brand排序, 然后在前面兩個都有序的情況下, 然后再通過produce_time來進行排序.

為什么說, 多列排序需要遵循最左前列或者是最左匹配原則??

其原因就是因為排序的問題, 我有下面幾個記錄:?

如果我要查找produce_time =?15:06:52的這個記錄, 那么請問我可以用到索引嗎? 當然不行, 因為你以上來就是摻雜后produce_time, 但是我們的produce_time不是有序排列的, 首先你的得確認car_num =?1481389857的記錄的位置, 然后才能根據brand來查找吧, 因為brand是有序的, 所以你也能很快的確認brand的位置. 然后再去在相同的brand中查找produce_time.?

如何給一個列挑選索引???

基于索引的數據結構和創建原則, 這里有如下幾點:?

索引列的基數, 要盡量小

基數:?

在數學和集合論中，基數用來表示集合中元素的數量。兩個能夠建立元素間一一對應的集合稱為互相對等集合，它們的基數相等。例如，一個包含三個元素的集合和另一個也包含三個元素的集合，無論這兩個集合中的元素是什么，它們的基數都是三
在數據庫中: 某一列的唯一鍵（distinct Keys）的數量叫作基數。例如，性別列只有男女之分，那么這一列的基數就是2。而主鍵列的基數等于表的總行數。選擇性是列的基數與表中總行數的比值，它可以衡量數據庫索引能夠幫助縮小對表中特定值的搜索范圍的程度。

所以說, 基數值越大, 一個索引列的重復性就越小, 那么此索引性能就越高?.?

有人肯定要問, 不是哥們, 這跟基數有什么關系啊. 不是排好序的嘛, 對沒錯, 正是因為排序, 才導致數據重復性過高, 不能通過索引列快速的接近真正的我們需要的記錄項. 我舉個例子

????????有一個多列索引為 index(sex, age, name), 然后我想通過sex和age和name來找一個人, 假設叫張三, 那么就是用這個索引, 首先索引是先根據sex來排序, 然后才是age進行排序, 然后再是name, 那么我要使用索引, 就需要首先進行sex字段的搜索, 但是你想一下, sex只有兩種可能, 那么基于sex的排序就只有男和女, 因此就相當于你要在男生中去找張三. 這樣效率肯定低.

????????假設你是100個班的班主任, 假設一個班50個人, 每個班平均男女25人你要在這一百個班中找到張三, 那么你喜歡現根據性別篩選出男女, 然后找到張三, 還是說先通過班級, 然后通過性別, 然后通過名字來找到張三? 無疑是后一種吧. 那么你就直接定位到張三所在的班, 然后通過性別, 篩選出25人, 然后通過這25人里面確認張三的名字即可.?

? ? ? ? 但是如果你篩選出男女, 那就是2500個人中找到張三. 這效率顯然是不一樣的.??

?索引列的類型盡量小

? ? ? ? 這個類型, 就是指的所謂的數據類型, 例如int, tinyint, bigint, 等, 還有字符串類型, 例如varchar, char類型等.?

? ? ? ? 盡量小的意思就是: 能使用tinyint, 就盡量不要使用int類型來作為索引列的類型, 能使用char(2), 就盡量不要使用char(10).

? ? ? ? 為什么?? 這是因為:?

數據類型越小, 在查詢的時候, 進行比較的操作就越快
數據類型越小, 索引建立的所占用的空間就更少. io成本也就越少.

我嘗試來解釋一下為什么這會比較操作更快以及為什么io成本就越少:?

首先對于整數來說, 在進行比較大小的時候, 是通過做減法, 然后進行標記來判斷大小的, 就要涉及到計算機組成原理了, 我們知道計算機組成原理中, 兩個int類型做減法, 可以看做是一個數加上另外一個數的相反數, 是需要先將減數轉換為補碼, 然后與被減數做相加操作. 然后根據得到的數, 來判斷大小, 因此來進行排序或者比較, 因此, 數位越低, 轉化為補碼, 進行減法操作的效率就越高.
如果是字符串, 在mysql中進行查找的時候, 是進行字符串的比較, 兩個字符串從第一個字符開始, 依次往后比較其字段順序. 你想像, 如果你的字符串越短, 是不是存儲的數據就更少, 兩個字符串長度為5的比較所消耗的cpu資源肯定是比兩個長度為10的字符串比較要少的.?
那么io成本呢? 我們知道,mysqlio的單位是數據頁, 也就是更短的數據類型, 一個數據頁可以存放更多的數據, 此時, 每次io的成本也就越小.

索引長字符串的前綴

? ? ? ? 字面意思就是給比較長的字符串加索引的時候, 如果你給他這一列所有的內容都加索引的花, 那么存儲的開銷就太大了. 一個字符串可能太長了, 一個數據頁都裝不下這種, 那么就可能會出現這幾種情況:?

占用大量的空間, B+樹索引中的記錄, 需要把該列的完整字符串存儲起來, 那么假設我一個字符串有幾mb或者是gb呢? 那么本來聚簇索引中就存在這個數據了, 我還要在非聚簇索引中存一份相同大小的數據, 那么是不是太浪費空間了.
其次我們上面提到, 字符串在索引中進行比較的時候, 是從第一個字符開始, 一個一個往后比較, 那么此時, 使用索引將會帶來更大的字符串比較的成本.?

????????其實我們也知道, 我們沒必要讓所有的字符都參與到索引中, 我們可以給這一個字符串的前綴建立索引.? 這樣, 雖然重復的概率提升了,? 雖然不能直接進行精準的找到想要的長字符串, 但是我們可以通過前綴索引, 找到符合前綴索引的id集合, 然后根據id到主鍵回表去找到對應的完整的長字符串即可.?

? ? ? ? 這種前綴索引, 比起直接索引長字符串, 有幾個好處:?

占用空間小, 我的索引列只存放部分字符串, 而不是所有的字符串
字符串的存放的長度變小了, 使用索引進行字符串比較的位數就少了, 效率更高了.

提一嘴, 如何使用建立前綴索引:?

CREATE TABLE person_info(name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

? ? ? ? name(10)

注意 :

? ? ? ? 我們知道前綴索引是基于索引列的字符串的前幾個字符進行排序的(前幾個取決于我們規定是前幾個字符). 此時如果你有如下語句:?

select * from person_info order by name limit 10;

? ? ? ? 那么會帶啦什么問題? 我們知道此時的name索引里面存儲的部分字符串, 此時, 如果我設置name(10), 也就是將name的前10個字符作為索引列. 此時, 如果我排序的前10個數據的前10個字符都是一樣的, 那么這10個字符后面剩余的字符該如何排序? 我們不知道呀, 因為只存放了前10個數據, 所以我們無法判斷, 哪個索引列對應的完全長字符串是排在前面的, 此時就不能使用到這個name索引了.?

? ? ? ? 此時的索引本身滿足表不了這個排序的需求, 此時想要排序, 就只能通過文檔排序, 也就是file sort, 通過磁盤的方式進行排序.

不要對索引列進行計算操作或者函數計算.

? ? ? ? ?下面有連個例子, 假設where中字段(key 不一定是int或者是字符串)都是非聚簇索引:?

where key * 2 < 4;
where lower(key) = 'example@example.com';

? ? ? ? 第一個例子是對key 乘以 2, 然后去和4進行比較, 第二個是將key轉化為小寫, 然后去和后面的郵箱字符串進行比較. 這樣做, 會讓存儲引擎會識別不了, 因為他們不知道key * 2 是否比當前的4小, 也不知道當前的字符串全部轉化為小寫之后, 是否就等于'example@example.com', 因此存儲引擎會對索引進行全列掃描, 然后每一條記錄都去做計算, 然后跟后面的內容進行比較.

? ? ? ? ?當然如果你對非索引列進行計算, 查詢優化器會自動幫你算出來, 或者說是化簡.

例如: where key < 4 / 2 -> where key < 2;

不要老想著查詢, 想想插入該怎么辦?

? ? ? ? 我們知道, 向一個表中插入數據是由聚簇索引來維護的, 聚簇索引是根據主鍵排序的, 此時, 如果你插入的主鍵不是依次遞增的話, 那么就會額外的消耗性能, 為什么這么說?

? ? ? ? 我們存儲數據的部分是數據頁, 一個數據頁是16kb, 可以存放的數據是有限的, 此時假設我現在擁有一個數據頁, 里面存放著主鍵為 1 , 3 , 4 , 5, 6, 8, 9的數據, 就這么幾條, 假設我現在此數據頁的空間已經吃滿了, 已經無法存放任何的記錄了, 那此時, 如果我要插入一個主鍵為7的數據, 該怎么辦啊. 數據頁又裝不滿, 這個時候, 就只能把這個數據頁給拆成兩份了, 這個拆分的過程就代表性能消耗啊.?

? ? ? ? 所以在創建主鍵的時候, 應該讓主鍵自增, 也就是auto_increment

避免索引冗余和重復

? ? ? ? 經過前面的探討, 我們已經知道, 索引并不是越多越好, 索引會占據額外的磁盤空間, 在插入的時候, 會消耗額外的性能資源, 所以我們應該避免重復的索引, 這個重復, 具體體現在什么地方??

? ? ? ? 例如:?

你擁有一個表, 其中又name字段, age字段和birth字段, 這三個字段, 其中你建立了一個多列隨你和單列索引. 單列索引以name為索引列, 多列索引以name, age, birth為索引列, 此時, 如果你不是用使用age和birth這兩個字段的話, 那么多列索引就是多余的. 因為不管是單列還是多列的name都是再起各自的b+樹中排好序的, 除非你要使用到age和birth.

這里總結的差不多了, 各位老哥還有什么要補充的?? 歡迎留言, 我會聽取您的留言并對本文做出改正!!　感謝！！