如何為MySQL創(chuàng)建高性能索引

今天小編給大家分享一下如何為MySQL創(chuàng)建高性能索引的相關(guān)知識(shí)點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識(shí)，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

1 索引基礎(chǔ)

1.1 索引作用

在MySQL中，查找數(shù)據(jù)時(shí)先在索引中找到對(duì)應(yīng)的值，然后根據(jù)匹配的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行，假如要運(yùn)行下面查詢語句：

SELECT	* FROM  USER  WHERE uid = 5;

如果在uid在建有索引，則MySQL將使用該索引先找到uid為5的行，也就是說MySQL先在索引上按值進(jìn)行查找，然后返回所有包含該值的數(shù)據(jù)行。

1.2 MySQL索引常用數(shù)據(jù)結(jié)構(gòu)

MySQL索引是在存儲(chǔ)引擎層面實(shí)現(xiàn)的，不是在服務(wù)器實(shí)現(xiàn)的。所以，沒有統(tǒng)一的索引標(biāo)準(zhǔn)：不同存儲(chǔ)引擎的索引工作方式不一樣。

1.2.1 B-Tree

大多數(shù)的MySQL引擎都支持這種索引B-Tree，即時(shí)多個(gè)存儲(chǔ)引擎支持同一種類型的索引，其底層實(shí)現(xiàn)也可能不同。比如InnoDB使用的是B+Tree。

存儲(chǔ)引擎以不同的方式實(shí)現(xiàn)B-Tree，性能也各有不同，各有優(yōu)勢(shì)。如，MyISAM使用前綴壓縮技術(shù)是的索引更小，當(dāng)InnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)，MyISAMy索引通過數(shù)據(jù)的物理位置引用被索引的行，而InnoDB根據(jù)組件應(yīng)用被索引的行。

B-Tree所有值都是順序存儲(chǔ)的，并且每一個(gè)葉子頁到根的距離相同。如下圖大致反應(yīng)了InnoDB索引是如何工作的，MyISAM使用的結(jié)構(gòu)有所不同。但基本實(shí)現(xiàn)是類似的。

實(shí)例圖說明：

每個(gè)節(jié)點(diǎn)占用一個(gè)磁盤塊，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹根節(jié)點(diǎn)的指針，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址。兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹的數(shù)據(jù)的范圍域。以根節(jié)點(diǎn)為例，關(guān)鍵字為 16 和 34，P1 指針指向的子樹的數(shù)據(jù)范圍為小于 16，P2 指針指向的子樹的數(shù)據(jù)范圍為 16~34，P3 指針指向的子樹的數(shù)據(jù)范圍為大于 34。查找關(guān)鍵字過程：

• 根據(jù)根節(jié)點(diǎn)找到磁盤塊 1，讀入內(nèi)存?！敬疟P I/O 操作第 1 次】

• 比較關(guān)鍵字 28 在區(qū)間（16,34），找到磁盤塊 1 的指針 P2。

• 根據(jù) P2 指針找到磁盤塊 3，讀入內(nèi)存。【磁盤 I/O 操作第 2 次】

• 比較關(guān)鍵字 28 在區(qū)間（25,31），找到磁盤塊 3 的指針 P2。

• 根據(jù) P2 指針找到磁盤塊 8，讀入內(nèi)存?！敬疟P I/O 操作第 3 次】

• 在磁盤塊 8 中的關(guān)鍵字列表中找到關(guān)鍵字 28。

缺點(diǎn)：

• 每個(gè)節(jié)點(diǎn)都有key，同時(shí)也包含data，而每個(gè)頁存儲(chǔ)空間是有限的，如果data比較大的話會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)存儲(chǔ)的key數(shù)量變??；

• 當(dāng)存儲(chǔ)的數(shù)據(jù)量很大的時(shí)候會(huì)導(dǎo)致深度較大，增大查詢時(shí)磁盤io次數(shù)，進(jìn)而影響查詢性能。

1.2.2 B+Tree索引

B+樹是對(duì)B樹的變種。與B樹區(qū)別：B+樹只在葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，非葉子節(jié)點(diǎn)只存儲(chǔ)key值及指針。

在B+樹上有兩個(gè)指針，一個(gè)指向根葉子節(jié)點(diǎn)，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)，因此可以對(duì)B+樹進(jìn)行兩種查找運(yùn)算：一種是對(duì)于組件的范圍查找，另一種是從根節(jié)點(diǎn)開始，進(jìn)行隨機(jī)查找。

B*樹與B+數(shù)類似，區(qū)別在于B*數(shù)非葉子節(jié)點(diǎn)之間也有鏈?zhǔn)江h(huán)結(jié)構(gòu)。

1.2.3 Hash索引

哈希索引基于哈希表實(shí)現(xiàn)，只有精準(zhǔn)匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼（hash code）,哈希碼是一個(gè)較小的值，并且不同鍵值的行計(jì)算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)在哈希表中保存指向每個(gè)數(shù)據(jù)行的指針。

在MySQL中只有Memory默認(rèn)索引類型就是使用的哈希索引，memory也支持B-Tree索引。同時(shí)，Memory引擎支持非唯一哈希索引，如果多個(gè)列的哈希值相同，索引會(huì)以鏈表的方式存放多個(gè)指針相同一個(gè)哈希條目中。類似HashMap。

優(yōu)點(diǎn)：
索引自身只需要存儲(chǔ)對(duì)應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，哈希所以查找的速度非?？臁?br/>缺點(diǎn)：

• 利用hash存儲(chǔ)的話需要將所有的數(shù)據(jù)文件添加到內(nèi)存，比較耗費(fèi)內(nèi)存空間；

• 哈希索引數(shù)據(jù)并不是按順序存儲(chǔ)的，所以無法用于排序；

• 如果所有的查詢都是等值查詢，那么hash確實(shí)很快，但是在企業(yè)或者實(shí)際工作環(huán)境中范圍查找的數(shù)據(jù)更多，而不是等值查詢，因此hash就不太適合了；

• 如果哈希沖突很多的話，索引維護(hù)操作的代價(jià)也會(huì)很高，這也是HashMap后期通過增加紅黑樹解決Hash沖突的問題；

2 高性能索引策略

2.1 聚簇索引與非聚簇索引

聚簇索引

不是單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式，在InnoDB存儲(chǔ)引擎中聚簇索引實(shí)際在同一個(gè)結(jié)構(gòu)中保存了鍵值和數(shù)據(jù)行。當(dāng)表中有聚簇索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁中。因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在不同的地方，所以一個(gè)表中只能有一個(gè)聚簇索引（索引覆蓋可以模擬出多個(gè)聚簇索引的情況）。

聚簇索引優(yōu)點(diǎn)：

可以把相關(guān)數(shù)據(jù)保存在一起;數(shù)據(jù)訪問更快，因?yàn)樗饕蛿?shù)據(jù)保存在同一個(gè)樹中;使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點(diǎn)中的主鍵值;

缺點(diǎn)：

聚簇?cái)?shù)據(jù)最大限度地提高了IO密集型應(yīng)用的性能，如果數(shù)據(jù)全部在內(nèi)存，那么聚簇索引就沒有什么優(yōu)勢(shì)；插入速度嚴(yán)重依賴于插入順序，按照主鍵的順序插入是最快的方式；更新聚簇索引列的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制將每個(gè)被更新的行移動(dòng)到新的位置；基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨頁分裂的問題；聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí)候；

非聚簇索引

數(shù)據(jù)文件跟索引文件分開存放

2.2 前綴索引

有時(shí)候需要索引很長(zhǎng)的字符串，這會(huì)讓索引變的大且慢，通常情況下可以使用某個(gè)列開始的部分字符串，這樣大大的節(jié)約索引空間，從而提高索引效率，但這會(huì)降低索引的選擇性，索引的選擇性是指：不重復(fù)的索引值（也稱為基數(shù)cardinality）和數(shù)據(jù)表記錄總數(shù)的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因?yàn)檫x擇性更高的索引可以讓mysql在查找的時(shí)候過濾掉更多的行。

一般情況下某個(gè)列前綴的選擇性也是足夠高的，足以滿足查詢的性能，但是對(duì)應(yīng)BLOB,TEXT,VARCHAR類型的列，必須要使用前綴索引，因?yàn)閙ysql不允許索引這些列的完整長(zhǎng)度，使用該方法的訣竅在于要選擇足夠長(zhǎng)的前綴以保證較高的選擇性，通過又不能太長(zhǎng)。

舉例

表結(jié)構(gòu)及數(shù)據(jù)MySQL官網(wǎng)或GItHub下載。

city Table Columns

--計(jì)算完整列的選擇性
select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到當(dāng)前綴長(zhǎng)度到達(dá)7之后，再增加前綴長(zhǎng)度，選擇性提升的幅度已經(jīng)很小了。由此最佳創(chuàng)建前綴索引長(zhǎng)度為7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表時(shí)設(shè)置的主鍵索引，如果沒有設(shè)置MySQL自動(dòng)將第一個(gè)非空唯一值作為索引，如果還是沒有InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為索引（oracle數(shù)據(jù)庫row-id顯式展示，可以用于分頁）；普通索引就是給普通列創(chuàng)建的索引。普通列索引在葉子節(jié)點(diǎn)中存儲(chǔ)的并不是整行數(shù)據(jù)而是主鍵，當(dāng)按普通索引查找時(shí)會(huì)先在B+樹中查找該列的主鍵，然后根據(jù)主鍵所在的B+樹中查找改行數(shù)據(jù)，這就是回表。

2.4 覆蓋索引

覆蓋索引在InnoDB中特別有用。MySQL中可以使用索引直接獲取列的數(shù)據(jù)，如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么就沒必要再回表查詢了，如果一個(gè)索引包含（覆蓋）所有需要查詢的字段的值，那么該索引就是覆蓋索引。簡(jiǎn)單的說：不回表直接通過一次索引查找到列的數(shù)據(jù)就叫覆蓋索引。

表信息

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

舉例

--將uid設(shè)置成主鍵索引后通過下面的SQL查詢 在explain的Extra列可以看到“Using index”
explain select uid from t_user where uid = 1;

覆蓋索引在組合索引中用的比較多，舉例

explain select age,uname from t_user where age = 10 ;

當(dāng)不建立組合索引時(shí)，會(huì)進(jìn)行回表查詢

設(shè)置組合索引后再次查詢

create index index_user on t_user(age,uname);

2.5 索引匹配方式

2.5.1 最左匹配

在使用組合索引中，比如設(shè)置(age,name)為組合索引，單獨(dú)使用組合索引中最左列是可以匹配索引的，如果不使用最左列則不走索引。例如下面SQL

--走索引
explain select * from t_user where age=10 and uname='zhang';

下面的SQL不走索引

explain select * from t_user where  uname='zhang';

2.5.2 匹配列前綴

可以匹配某一列的值的開頭部分，比如like 'abc%'。

2.5.3 匹配范圍值

可以查找某一個(gè)范圍的數(shù)據(jù)。

explain select * from t_user where age>18;

2.5.4 精確匹配某一列并范圍匹配另外一列

可以查詢第一列的全部和第二列的部分

explain select * from t_user where age=18 and uname like 'zhang%';

2.5.5 只訪問索引的查詢

查詢的時(shí)候只需要訪問索引，不需要訪問數(shù)據(jù)行，本質(zhì)上就是覆蓋索引。

explain select age,uname,update_time from t_user 
            where age=18 and uname= 'zhang' and update_time='123';

3 索引優(yōu)化最佳實(shí)踐

1. 當(dāng)使用索引列進(jìn)行查詢的時(shí)候盡量不要使用表達(dá)式，把計(jì)算放到業(yè)務(wù)層而不是數(shù)據(jù)庫層。

--推薦
select uid,age,uname from t_user where uid=1;

--不推薦
select uid,age,uname from t_user where uid+9=10;

2. 盡量使用主鍵查詢，而不是其他索引，因?yàn)橹麈I查詢不會(huì)觸發(fā)回表查詢

3. 使用前綴索引參考2.2 前綴索引
4. 使用索引掃描排序mysql有兩種方式可以生成有序的結(jié)果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序。
掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄就得回表查詢一次對(duì)應(yīng)的行，這基本都是隨機(jī)IO，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描慢。
mysql可以使用同一個(gè)索引即滿足排序，又用于查找行，如果可能的話，設(shè)計(jì)索引時(shí)應(yīng)該盡可能地同時(shí)滿足這兩種任務(wù)。
只有當(dāng)索引的列順序和order by子句的順序完全一致，并且所有列的排序方式都一樣時(shí)，mysql才能夠使用索引來對(duì)結(jié)果進(jìn)行排序，如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)orderby子句引用的字段全部為第一張表時(shí)，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的，需要滿足索引的最左前綴的要求，否則，mysql都需要執(zhí)行順序操作，而無法利用索引排序。
舉例表結(jié)構(gòu)及數(shù)據(jù)MySQL官網(wǎng)或GItHub下載。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental表在rental_date,inventory_id,customer_id上有rental_date的索引。使用rental_date索引為下面的查詢做排序

--該查詢?yōu)樗饕牡谝涣刑峁┝顺Ａ織l件，而使用第二列進(jìn)行排序，將兩個(gè)列組合在一起，就形成了索引的最左前綴
explain select rental_id,staff_id from rental 
where rental_date='2005-05-25' order by inventory_id desc

--下面的查詢不會(huì)利用索引
explain select rental_id,staff_id from rental 
where rental_date>'2005-05-25' order by rental_date,inventory_id

5. union all,in,or都能夠使用索引，但是推薦使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范圍列可以用到索引范圍條件是：<、<=、>、>=、between。范圍列可以用到索引，但是范圍列后面的列無法用到索引，索引最多用于一個(gè)范圍列。

7. 更新十分頻繁，數(shù)據(jù)區(qū)分度不高的字段上不宜建立索引

• 更新會(huì)變更B+樹，更新頻繁的字段建議索引會(huì)大大降低數(shù)據(jù)庫性能；

• 類似于性別這類區(qū)分不大的屬性，建立索引是沒有意義的，不能有效的過濾數(shù)據(jù)；

• 一般區(qū)分度在80%以上的時(shí)候就可以建立索引，區(qū)分度可以使用 count(distinct(列名))/count(*) 來計(jì)算；

8. 創(chuàng)建索引的列，不允許為null，可能會(huì)得到不符合預(yù)期的結(jié)果

9.當(dāng)需要進(jìn)行表連接的時(shí)候，最好不要超過三張表，如果需要join的字段，數(shù)據(jù)類型必須一致

10. 能使用limit的時(shí)候盡量使用limit

11. 單表索引建議控制在5個(gè)以內(nèi)

12. 單索引字段數(shù)不允許超過5個(gè)（組合索引）

13. 創(chuàng)建索引的時(shí)候應(yīng)該避免以下錯(cuò)誤概念

• 索引越多越好

• 過早優(yōu)化，在不了解系統(tǒng)的情況下進(jìn)行優(yōu)化

4 索引監(jiān)控

show status like 'Handler_read%';

以上就是“如何為MySQL創(chuàng)建高性能索引”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會(huì)為大家更新不同的知識(shí)，如果還想學(xué)習(xí)更多的知識(shí)，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。