溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了MySQL中怎么實現高性能索引，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。
索引是提高MySQL查詢性能最有效的手段，我們常說的MySQL性能調優基本都是對索引的優化。所以這是每個開發需要掌握并會應用的知識點。
索引是一種數據結構，它也是存儲在磁盤的一個文件。上一篇我們學習MySQL的邏輯架構的時候了解了InnoDB和MyISM存儲引擎，InnoDB存儲引擎索引和數據是同一個文件，MyISAM索引和數據是兩個獨立的文件。
在MySQL中，索引是在存儲引擎層實現的而不是Server層實現的，所以不同的存儲引擎的索引的工作方式是不一樣的。我們對索引的分析應該是建立在存儲引擎的基礎上的，InnoDB是MySQL默認的存儲引擎。
索引的優點：
索引大大減少了服務器需要掃描的數據量。
索引可以幫助服務器避免排序和臨時表。
索引可以隨機I/O變為順序I/O。
索引的缺點：
索引是數據結構，它占用了額外的磁盤空間。
當表數據量比較大時，維護索引的代價比較大。
每個存儲引擎的數據結構和算法都是存在區別，我們先看下MySQL本身支持的索引類型。
一般我們說的索引結構就是指B-Tree索引，MySQL大部分的存儲引擎都支持這種索引，但是不同的存儲引擎以不同的方式使用B-Tree索引，性能也各有不同。InnoDB使用的是B+Tree，按照原有的數據格式進行存儲，根據主鍵引用被索引的行。
B-Tree所有的值都是按順序存儲的，并且每一個葉子到根的距離相同。下圖是B-Tree的抽象圖：
B-Tree能夠加快訪問數據的速度。
存儲引擎不需要全表掃描來獲取所需要的數據，它是從索引的根節點開始搜索。根節點的槽中存放指向子節點的指針，搜索引擎根據這些指針向下層查找。通過比較節點頁的值和要查找的值可以找到合適的指針進入到下層節點。最終引擎要么找到對應的值，要么該記錄不存在。
B-Tree的索引如果多個列，索引值的排序是按照建表時定義的索引順序，所以索引的順序是比較重要的。
B-Tree是N叉樹，N的大小取決于數據塊的大小。
以InnoDB的一個整數字段索引為例，N大概為1200，當樹高是4的時候，就可以存1200的3次方的數據，大概為17億。一個擁有10億的表上一個整數字段的索引，查找一個值最多訪問3次磁盤。其實在應用時，如果第二層被提前加載到內存中，那么磁盤的訪問次數就更少了。
哈希索引是基于哈希表實現的，只有精確匹配所有列的查詢才有效。
對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼(hash code)，哈希碼是一個比較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在hash表中保存指向每個數據行的指針。
創建表test_hash，它的存儲引擎為memory，索引為full_name，索引類型為hash。
CREATE TABLE `test_hash` ( `full_name` varchar(255) DEFAULT NULL, `short_name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `idx` (`full_name`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
表中的數據如下：
mysql> select * from test_hash; +-------------------+------------+------+ | full_name | short_name | age | +-------------------+------------+------+ | Dwayne Johnson | Johnson | NULL | | Taylor Swift | Taylor | NULL | | Leonardo DiCaprio | Leonardo | NULL | | Vin Diesel | Diesel | NULL | | Kobe Bryant | Kobe | NULL | +-------------------+------------+------+ 5 rows in set (0.00 sec)
那么哈希索引的數據結構可能是：
當我們執行查詢語句：
mysql> select short_name from test_hash where full_name = 'Dwayne Johnson';
這個sql語句的執行流程：
1)根據where條件 'Dwayne Johnson'計算出哈希碼，那么得到的哈希碼為1234。
2)MySQL在索引中查找到1234，并根據這個值找到了對應的行記錄指針。
3)根據指針地址找到對應的行，最后比較這個行中的full_name列是否為'Dwayne Johnson'。
那現在有個問題，哈希碼沖突的時候怎么辦呢?學過HashMap的小伙伴此時肯定靈機一動：哈希碼沖突的時候使用鏈表。對的，當鍵值的哈希碼沖突的時候，MySQL也是使用的鏈表結構。如果是鏈表結構，在查找的時候就需要遍歷每個鏈表指針指向的行記錄做匹配，所以哈希沖突比較大的時候查找的效率是比較低的。
從上面的示例我們可以看出，哈希索引的結構中只存儲了哈希值，它的結構是比較緊湊的，對于精確查詢的效率是比較快的。
但是哈希索引還是有些限制的：
哈希索引中存儲的是鍵值的哈希值，它不是按照索引列的順序的，所以它不無法用于排序。
哈希索引不支持部分索引匹配查找，因為哈希索引始終是索引列的全部內容。如果我們索引有兩個列(A，B)，查詢的時候只想使用A列，這個時候是無法應用索引的。
哈希索引只支持等值查詢，比如=、in等，它不支持任何范圍查詢。
當哈希沖突的時候，存儲引擎必須要遍歷鏈表中的所有行指針，逐行比較，直到找到所有符合條件的行，如果哈希沖突比較多的時候，索引維護的代價比較高。
在MySQL中，目前只有memory引擎顯式支持哈希索引。
我們前面提到，InnoDB的索引結構是B+Tee，它是以主鍵引用被索引的行。所以在InnoDB中，表都是根據主鍵順序以索引的形式存放的，每一個索引在InnoDB里面對應一棵B+樹。
B+Tree是我們前面提到的B-Tree的擴展，B-Tree的每一個節點都包含了數據項，這樣每一塊磁盤存儲的索引值就會比較少，樹的高度就會變大，查詢的磁盤I/O次數就會增加。
那B+Tree是怎么樣的數據結構呢?下圖是B+Tree的抽象圖：
B+Tree與B-Tree的區別：
B+Tree的非葉子節點不保存數據信息，只保存索引值和指向下一層節點的指針。
B+Tree的葉子節點保存了數據
B+Tree的葉子節點是順序排列的，并且葉子相鄰節點之間有指針的互相引用
B+Tree能夠更好地配合磁盤的讀寫特性，減少單次查詢的磁盤訪問次數。
InnoDB的索引類型分為主鍵索引和非主鍵索引。
創建表user，它的存儲引擎為InnoDB，id為主鍵，name為普通索引。
CREATE TABLE `user` ( `id` int(10) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` varchar(1) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表中的數據如下:
mysql> select * from user; +----+-------+------+------+---------+------------+ | id | name | age | sex | comment | date | +----+-------+------+------+---------+------------+ | 1 | Alen | 20 | 1 | NULL | 2021-02-16 | | 2 | Alex | 21 | 1 | NULL | 2021-02-16 | | 3 | Saria | 16 | 0 | NULL | 2021-02-16 | | 4 | Semyt | 18 | 0 | NULL | 2021-02-16 | | 5 | Summy | 17 | 1 | NULL | 2021-02-16 | | 6 | Tom | 19 | 0 | NULL | 2021-02-16 | +----+-------+------+------+---------+------------+ 6 rows in set (0.00 sec)
主鍵索引也稱為聚簇索引，它的葉子節點都包含了主鍵值、事務ID、用于事務和MVCC的回滾指針以及所有剩余的列。
mysql> select * from user where id = 1;
主鍵索引只需要搜索ID這棵B+Tree就可以拿到符合條件的行記錄。
InnoDB是通過主鍵索引聚集數據，如果表中沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。這也是勾勾為每個表都創建主鍵的原因。
聚簇索引的優點：
把相關的數據保存在一起，減少了磁盤I/O;
聚簇索引將數據和索引保存在同一顆BTree上，數據訪問更快;
聚簇索引的缺點：
如果數據都在內存中，聚簇索引的查詢性能就沒有那么好的優勢了。
插入的速度嚴重依賴于插入順序。盡量保證主鍵索引是有序的。
更新聚簇索引列的代價更高。
在插入行或者更新主鍵的時候導致需要移動行的時候可能導致頁分裂的問題。當插入到一個已滿的頁中，存儲引擎會將該頁分裂為兩頁來容納數據，頁分裂會導致占用更多的磁盤空間。
非主鍵索引也稱為非聚簇索引，在InnoDB中又被稱為二級索引。非主鍵索引的葉子節點內容是主鍵的值。
mysql> select * from user where name = 'Alen';
非主鍵索引查詢時，首先根據name普通查詢搜索name索引樹，找到id為1，再根據id=1到ID索引樹查詢一次才能獲取到符合條件的行記錄。
我們把先搜索普通索引樹得到主鍵，再搜索主鍵索引樹的過程稱為回表。
普通索引的查詢比主鍵索引多檢索了一棵B+Tree，在實際應用場景下如果能用到主鍵索引盡量選擇主鍵索引。
在創建索引的時候還有其他的原則，我們接下來繼續學習高性能的索引策略。
小伙伴們在學習索引策略的時候可以利用上一篇文章的explian關鍵字查詢執行計劃。
索引的分類有多種，我們可以按照索引字段的個數將索引分為單列索引和聯合索引。
單列索引：一個索引只包含一個列，一個表中可以多個單列索引。
聯合索引：一個索引包含多個列。
我們還可以將索引分為普通索引、唯一索引和主鍵索引。
普通索引：基本的索引類型，常用來提高查詢效率，對數據沒有限制。允許在索引列中插入空值和重復值。
唯一索引：索引列中的值必須是唯一的，允許存在空值。
主鍵索引：不允許空值的特殊的唯一索引。
索引有這么多分類，我們在創建索引的時候如何選擇呢?
索引的三星系統：
一星：索引相關的記錄放到一起。
二星：索引中的數據順序和查找列中的順序一致。
三星：索引的列包含了查詢中需要的全部列。
正確的創建和使用索引是實現高性能查詢的基礎。索引的選擇沒有絕對的要求，主要是根據自己的業務需求，但是有些原則我們在創建索引的時候可以作為參考。
索引列的區分度越高則查詢效率越高。
將頻繁搜索的列加入索引，可以提高搜索效率。
索引不只提高了查詢效率，也可以參與排序和分組，經常用來排序和分組的字段也需考慮加入索引。
創建索引時，應將區分度高的字段排在前面。即需要注意索引字段的順序。
索引列不能參與任何運算。
避免創建重復索引，即在同一個列上按照相同的順序創建相同類型的索引。
對于從未使用的索引，應盡量刪除。
對于blob、text或者長varchar類型的列，必須要使用前綴索引，取最夠長的前綴來保證較高的區分度。
普通索引和唯一索引在查詢效率上差別并不大，因為引擎是按照頁讀取數據。對于唯一索引在查詢的時候只要找到就不再繼續比較了，因為索引已經保證了唯一性。而對于普通索引則在找到滿足條件的記錄后還需要繼續查找直到找到不滿足條件的第一條記錄，但是對于按照頁讀取數據的引擎來說，多一次的判斷對性能的影響較小。普通索引和唯一索引的選擇除了保證業務的準確性之外，其他更多的考慮更新數據時對性能的影響。
”獨立的列“是指索引不能是表達式的一部分，也不能是函數的參數。
例如，如下sql語句，在查詢時索引字段name參與了函數運算，會導致索引失效，全表掃描。
mysql> select * from user where CONCAT(name,'n') = 'Alen';
添加索引age字段，如果我們在查詢的時候對age字段進行了運算也會導致索引失效：
mysql> select * from user where age + 1 = 21;
我們平時開發中要養成簡化where條件的習慣，始終使用單獨的索引列。
如果我們把按照普通索引查詢的sql語句修改如下:
mysql> select name from user where name like 'Al%';
這時只需要查詢普通索引樹即可得到要查詢的列，因為要查詢的列已經在索引樹了，而不需要再回表查詢。
這種索引字段覆蓋了我們需要查詢的結果字段的場景我們稱為覆蓋索引。
覆蓋索引可以減少回表，減少索引樹的搜索次數，顯著提高查詢性能，所以覆蓋索引是一個比較好的優化策略。
在實際開發中，可以按照業務需要把一些常用的檢索字段添加到索引中，利用覆蓋索引提高查詢效率，但是有些場景下不能為了使用覆蓋索引而過多的維護索引，畢竟索引的維護成本也是很高的。
這個時候我們還需要思考一個問題，在業務場景中我們的查詢是多樣化的，不能為了使用索引而為每一種場景都設計一個索引吧?
這個時候我們就要利用B+Tree樹索引結構的另外一個特性最左前綴。
最左前綴可以是聯合索引的最左的幾個字段，也可以是字符串索引的最左的幾個字符。
創建聯合索引(name,age)，順序一致。
此時執行sql語句：
mysql> select * from user where name = 'Alen';
雖然是聯合索引，但是name字段排在第一位，也是可以命中索引的。
mysql> select * from user where name like 'Al%';
如果使用name索引字段的最左N個字符串，也是可以命中索引的。但是如果我們使用%Al是不能命中索引的。
如果我們使用如下的sql查詢語句：
mysql> select * from user where age = '16'；
雖然age也是聯合索引的字段，但是他的順序在name之后，直接使用age查詢無法命中索引。所以創建聯合索引時一定要考慮索引字段的順序。
索引維護時有一個原則：如果能通過調整索引順序，可以少維護一個索引，那么就需要優先調整順序而不是增加索引。
MySQL可以利用同一個索引進行排序和掃描行，但是只有當索引的列順序和order by子句的順序完全一致，并且列的排序方向都一致(正序或者倒序)時，MySQL才能使用對結果進行排序。
order by子句和查詢類型限制是一樣的，也需要滿足”最左前綴“的原則，否則MySQL無法利用索引排序。
當我們的查詢語句不滿足最左前綴的時候會如何呢?
比如我們查詢名字第一個字為A，年齡為20，并且性別為1(男)的人員信息，sql語句如下：
mysql> select * from user where name like 'A%' and age = 20 and sex = 1 ;
按照我們前面學習的最左前綴原則，按照’A‘先搜索到第一個滿足條件的主鍵1，然后回表查詢判斷其他的兩個條件是否滿足。
MySQL5.6之后引入了索引下推的優化，即會按照索引中包含的字段優先過濾，減少回表的次數。
我們上述的sql語句在MySQL5.6之前會回表2次分別對比主鍵1和2兩條的數據的其他條件是否滿足，但是引入索引下推的優化之后age = 20這個條件不滿的會直接過濾掉，只需要對主鍵1回表一次就可以獲取到結果。
上述內容就是MySQL中怎么實現高性能索引，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
