mysql 索引詳解：

在mysql 中，索引可以分為兩種類(lèi)型 hash索引和 btree索引。

 什么情況下可以用到B樹(shù)索引？

 1.全值匹配索引

 比如：

orderID="123” 

2.匹配最左前綴索引查詢(xún) 

比如：在userid 和 date字段上創(chuàng)建聯(lián)合索引。

那么如果輸入 userId作為條件，那么這個(gè)userid可以使用到索引，如果直接輸入 date作為條件，那么將不能使用到索引。 

3.匹配列前綴查詢(xún)

比如： order_sn like ‘134%' 這樣可以使用到索引。 

4.匹配范圍值查詢(xún)

createTime>'2015-01-09' and createTime<'2015-01-10'

5.精確匹配左前列并范圍匹配另外一列

比如：

userId=1 and createTime>'2016-9-18'

 6.只訪問(wèn)索引的查詢(xún)，稱(chēng)為覆蓋索引，及索引包括查詢(xún)列的數(shù)據(jù)。

 BTREE索引的限制

 1.如果不是按照索引的最左列開(kāi)始查找，則無(wú)法使用索引。

比如創(chuàng)建聯(lián)合索引：

orderId ，createTime 字段創(chuàng)建聯(lián)合索引，如果只是輸入 createTIme的條件，沒(méi)有orderid的條件，那么將不能使用此索引。

 2.使用索引時(shí)不能跳過(guò)索引的列。

三列：

日期，姓名，電話 組成列和索引，如果查詢(xún)時(shí) 只輸入 日期 和電話，那么只能使用日期作為索引進(jìn)行過(guò)濾。

 3.NOT IN 和 <> 操作無(wú)法使用索引。

 4.如果查詢(xún)中有某個(gè)列的范圍查詢(xún)，則其右邊所有列都無(wú)法使用索引。

 hash 索引的特點(diǎn)

 hash索引是基于hash表實(shí)現(xiàn)的，只有查詢(xún)條件精確匹配hash 索引中的所有列時(shí)，才能使用hash索引。只能是等值查詢(xún)。

 對(duì)于hash索引中的所有列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)hash碼，hash索引中存儲(chǔ)的就是hash碼。

 限制：

1.必須兩次讀取，先讀取hash 找到對(duì)應(yīng)的行，再讀取對(duì)應(yīng)的行數(shù)據(jù)。

2.hash索引無(wú)法用于排序。

3.只支持精確查找，不支持部分索引查找，也不支持范圍查找。

hash沖突：

hash 索引不能使用于選擇性很差的字段，而要用在比如 選擇性強(qiáng)的列上創(chuàng)建hash索引。

比如：不要在性別字段上創(chuàng)建hash索引。

 為什么要使用索引？ 

1.索引大大減少存儲(chǔ)引擎需要掃描的數(shù)據(jù)量。索引小于數(shù)據(jù)大小。

2.索引可以幫助我們進(jìn)行排序以避免使用臨時(shí)表。索引是有順序的。

3.索引可以把隨機(jī)的I/0 變?yōu)?順序的IO

 索引是不是越多越好？

1.索引會(huì)增加寫(xiě)操作的成本

2.太多的索引會(huì)增加查詢(xún)優(yōu)化器和選擇時(shí)間。

 建立索引的策略

1.索引列上不能使用表達(dá)式或函數(shù)

比如：

select * from product where to_days(out_date) –to_days(current_date)<=30，out_date為索引列。

改成：

select* from product where out_date<date_add(current_date,interval 30 day)

 2.索引大小不能超過(guò)一定值。

inodb 索引列 大小為 200個(gè)長(zhǎng)度。

 3.前綴和索引列的選擇性。

 create index idx_NAME  on table (account);

 4.聯(lián)合索引

如何選擇索引列的順序。

1.經(jīng)常會(huì)被索引的列。

2.選擇性高的里的列的優(yōu)先。

3.對(duì)小的列創(chuàng)建索引。

感謝閱讀此文，希望能幫助到大家，謝謝大家對(duì)本站的支持！

最新評(píng)論