索引怎么選擇mysql mysql索引原理

MySQL前綴索引

前綴索引顧名思義，定義字符串的一部分當(dāng)做索引，而不是把整個字符串當(dāng)做索引。默認(rèn)地，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會包含整個字符串。

為杜爾伯特等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及杜爾伯特網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、杜爾伯特網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

假設(shè)一張表有 id,name,email 2個字段

1.創(chuàng)建email列的普通索引應(yīng)該是： alter table T add index idx_email1( email )

2.前綴索引的創(chuàng)建規(guī)則為： alter table table T add index idx_email2( email(6) )

當(dāng)然第一索引包含是的整個字符串，第二個是該字段前6個字節(jié)（注意是字節(jié)）

對于這2中索引，B+樹怎么存儲呢？

INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163點(diǎn)抗 '), ('劍圣','lisi1998883@163點(diǎn)抗 '), ('露娜','zhangssxyz@163點(diǎn)抗 '), ('李白','zhangsy1998@163點(diǎn)抗 '), ('韓信','zhaq5481993@163點(diǎn)抗 '), ('百里玄策','hhaq5481993@163點(diǎn)抗 ');

【誰還不是個野王啊】

普通索引存儲為：

是的你沒看錯，前綴索引那顆樹上的存儲的是email的前6位字節(jié)，也就是你創(chuàng)建前綴索引時指定的前綴字節(jié)長度。2種樹相比，前綴索引存儲了更少的數(shù)據(jù)，那么他所耗費(fèi)的空間也就相比較少，這正是他的一個優(yōu)點(diǎn)。同樣的也就相對的增加了掃描行數(shù)。

什么增加了掃描行數(shù)？？？？這是為什么呢？

那么小朋友咱們一起來看下吧。

假設(shè)SQL如此這般： select id,name,email from T where email = 'zhangsh1234@163點(diǎn)抗 '

那么這2個SQL，應(yīng)該怎么操作呢。

idx_email1:

2.到主鍵上查到主鍵為ID1的，判斷email值是否正確【為什么判斷呢，其實(shí)我理解是為了二次判斷保證數(shù)據(jù)一致性吧，比較官方的解釋尚未找到】，正確放入結(jié)果集

3.取 idx_email1 索引樹上剛剛查到的位置的下一條記錄，如此往復(fù)。

循環(huán)過程中，需要回主鍵取1次數(shù)據(jù)，所以系統(tǒng)可以認(rèn)為只掃描了一行【1次是數(shù)第一棵樹數(shù)出來的】

idx_email2:

1.從索引數(shù)上找到滿足索引值為 'zhangs'的該記錄，取得 ID1的值

2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值是’ zhangsh1234@xxx點(diǎn)抗 ’，這行記錄放入結(jié)果集【不是要的值，丟棄，進(jìn)行下一步】

3.取 idx_email2 上剛剛查到的位置的下一條記錄,重復(fù)以上步驟

在這個過程中，要回主鍵索引取 3 次數(shù)據(jù)，也就是掃描了 3 行。通過這個對比，你很容易就可以發(fā)現(xiàn)，使用前綴索引后，可能會導(dǎo)致查詢語句讀數(shù)據(jù)的次數(shù)變多。

但是，對于這個查詢語句來說，如果你定義的 idx_email2 不是 email(6) 而是 email(8），也就是說取 email 字段的前 8 個字節(jié)來構(gòu)建索引的話，即滿足前綴’zhangsh’的記錄只有一個，也能夠直接查到 ID1，只掃描一行就結(jié)束了。也就是說使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。

那么問題來了，到底定義多長才算是合理呢？

一般的定義原則是 count(distinct(columnName))/count(*) ,當(dāng)前綴索引【count(distinct(columnName(length)))，length是你想要創(chuàng)建列的前綴字節(jié)長度】越接近此值越好，當(dāng)有多個前綴字節(jié)都一樣且都等于這個值時怎么選擇呢，當(dāng)然是字節(jié)越少越好了哈，字節(jié)越少越省空間。索引選取的越長，占用的磁盤空間就越大，相同的數(shù)據(jù)頁能放下的索引值就越少，搜索的效率也就會越低。

count(distinct(columnName(length))) 翻譯到SQL 為： count(dictinct(left(colunmName, length)))

前面我們說了使用前綴索引可能會增加掃描行數(shù)，這會影響到性能。其實(shí)，前綴索引的影響不止如此，我們再看一下另外一個場景。

來呀，上SQL： select id,email from T where email='zhangsh1234@163點(diǎn)抗 '

如果按照email全字段索引，那么此SQL 是不需要回表的【為什么不需要回表？兄嘚，這個相當(dāng)于覆蓋索引了哈】

那么如果按照前綴索引是否需要回表呢？答案是的。

因?yàn)楫?dāng)判斷前6個字節(jié)相等后，需要拿到id 回表拿到email的全部內(nèi)容進(jìn)行比較，如果不相同，丟棄這行，否則加入結(jié)果集。

那么有人會問了，我把長度放大點(diǎn)，包含所有字節(jié)不就好了嗎？

那么此時會有如下問題。

1.當(dāng)你此時的長度是囊括了全字段，但是系統(tǒng)是不知道的，他還是需要回表再次判斷的，去確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?/p>

2.此時長度是夠了，那么能肯定因?yàn)闃I(yè)務(wù)日后不會增加長度嗎？

3.盡可能的加長長度，還不如直接建立全字段索引呢

綜上，使用前綴索引就用不上覆蓋索引對查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素。

前面說到的是，可以根據(jù)字段前面幾個字節(jié)進(jìn)行查詢的，那么對于身份證這種，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身份證號前 6 位一般會是相同的。

或許你會說，多弄幾個字節(jié)不就好嗎？那么請問下自己為什么使用前綴索引呢，不就是為了節(jié)省空間嗎？

那么這么做合適嗎？不合適對嗎？乖~，快去反省下吧

那么采用前綴索引顯示是不行的，那么如果用前綴索引怎么辦呢，聰明的你應(yīng)該已經(jīng)猜到了，采用倒敘存儲，然后建立前綴索引。

放到SQL 中就應(yīng)該是這樣的： select field_list from t where id_card = reverse('id_card_string');

當(dāng)然了，這種邏輯建議放到業(yè)務(wù)邏輯中實(shí)現(xiàn)，而不是放到SQL 中。

按照上述第4節(jié)的內(nèi)容，有人或許會有另一個想法，還倒敘建立前綴索引復(fù)雜不，hash索引或者h(yuǎn)ash字段不香嗎？

有人會問了，為什么要在創(chuàng)建一個值來存儲hash值呢，如果不存儲你知道原值是什么嗎？同時hash算法是有一定重復(fù)可能的（hash值碰撞）

【可以了解下partition算法哦:[ 】。如果重復(fù)了，不存儲原值，你是無法判斷出正確數(shù)據(jù)的。

注：【hash字段不代表hash索引，hash索引原理正在快馬加鞭】，簡單說下hash索引，hash索引不需要創(chuàng)建一個值來存儲hash值，而是有hasn表來存儲【hash值碰撞時，由一個鏈表來搞定了】，存儲的內(nèi)容為 hash值和每行的行指針。

說回來啊，跑題了

查詢時： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不過有個問題相信你也想到了，不管是hash存儲值還是hash索引都是不支持范圍查詢的。

來總結(jié)下這2個優(yōu)缺點(diǎn)吧

1.從占用空間來看呢，倒敘索引不需要額外開辟存儲空間，而hash字段需要額外的一個字段，所以從這點(diǎn)上看倒敘索引更勝一籌，NO!并不準(zhǔn)確,如果前綴長度過長，那么這2個情況額外的空間也就相差無幾了

3.從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩(wěn)定一些。因?yàn)?crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認(rèn)為每次查詢的平均掃描行數(shù)接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數(shù)

1.全字段完整索引比較占空間，但是而走覆蓋索引

2.前綴索引，節(jié)省空間，但會增加掃描次數(shù) 并且不能使用覆蓋索引【每次都需回表校驗(yàn)】

3.倒序存儲，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不夠的問題?！镜箶⒎椒ń⒎诺綐I(yè)務(wù)邏輯中】

4.hash字段索引，相比前綴索引性能較為穩(wěn)定，但是有額外的存儲空間和計(jì)算消耗，同時也不支持范圍查詢

mysql 索引怎么使用

CREATE

[UNIQUE]

INDEX

(字段

[ASC|DESC]);

UNIQUE

--確保所有的索引列中的值都是可以區(qū)分的。

[ASC|DESC]

--在列上按指定排序創(chuàng)建索引。

(創(chuàng)建索引的準(zhǔn)則：

1.如果表里有幾百行記錄則可以對其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。

2.不要試圖對表創(chuàng)建兩個或三個以上的索引。

3.為頻繁使用的行創(chuàng)建索引。

)

示例

create

index

i_1

emp(empno

asc);

Mysql索引

建立索引，要使用離散度（選擇度）更高的字段。

我們先來看一個重要的屬性列的離散度，

count(distinct(column_name)) : count(*) -- 列的全部不同值個數(shù)：所有數(shù)據(jù)行行數(shù)

數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡單來說，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。

當(dāng)字段值比較長的時候，建立索引會消耗很多的空間，搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個就叫前綴索引。

創(chuàng)建一張商戶表，因?yàn)榈刂纷侄伪容^長，在地址字段上建立前綴索引

create table shop(address varchar(120) not null);

alter table shop add key(address(12));? // 截取12個字符作為前綴索引是最優(yōu)的嗎？

問題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲空間的目的，截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會降低。怎么計(jì)算不同的長度的選擇性呢？

先看一下字段在全部數(shù)據(jù)中的選擇度計(jì)算公式：

select count(distinct address) / count(*) from shop;

select count(distinct left(address, n)) / count(*) as subn from shop;

count(distinct left(address,n)) / count(*) 的結(jié)果是會隨著 n 的變大而變大。舉個例子，現(xiàn)在有兩個address（東大街長興小區(qū)，東大街福樂小區(qū)），那么 distinct(address，2) distinct(address，3）

==所以，截取的長度越長就會越接近字段在全部數(shù)據(jù)中的選擇度

==所以，我們要權(quán)衡索引大小和查詢速度。

舉個例子，通過不同長度去計(jì)算，與全表的選擇性對比：

SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度

COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個字符的選擇度

COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?

COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,

COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個字符的選擇度

COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,

COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,

COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,

COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15

FROM shop;

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| sub? ? | sub5? | sub7? | sub9? | sub10? | sub11? | sub12? | sub13? | sub15? |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

可以看到在截取 11 個字段時 sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了，而且長度也相較后面更短一些，綜合考慮比較合適。

ALTER TABLE shop ADD KEY (address(11));

1.索引的個數(shù)不要過多（浪費(fèi)空間，更新變慢）

2.在用于 where 判斷 order 排序和 join 的（on）字段上創(chuàng)建索引

3.區(qū)分度低的字段，例如性別，不要建索引（離散度太低，導(dǎo)致掃描行數(shù)過多）

4.更新頻繁的值，不要作為主鍵或者索引（頁分裂）

5.不建議用無序的值作為索引，例如身份證、UUID（在索引比較時需要轉(zhuǎn)為ASCII，并且插入時可能造成頁分裂）

6.若在多個字段都要創(chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引

7.聯(lián)合索引把散列性高（區(qū)分度高）的值放在前面

mysql創(chuàng)建索引的原則

1．選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學(xué)生表中學(xué)號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

2．為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引

經(jīng)常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作會浪費(fèi)很多時間。如果為其建立索引，可以有效地避免排序操作。

3．為常作為查詢條件的字段建立索引

如果某個字段經(jīng)常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

4．限制索引的數(shù)目

索引的數(shù)目不是越多越好。每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。修改表時，對索引的重構(gòu)和更新很麻煩。越多的索引，會使更新表變得很浪費(fèi)時間。

5．盡量使用數(shù)據(jù)量少的索引

如果索引的值很長，那么查詢的速度會受到影響。例如，對一個CHAR(100)類型的字段進(jìn)行全文檢索需要的時間肯定要比對CHAR(10)類型的字段需要的時間要多。

6．盡量使用前綴來索引

如果索引字段的值很長，最好使用值的前綴來索引。例如，TEXT和BLOG類型的字段，進(jìn)行全文檢索會很浪費(fèi)時間。如果只檢索字段的前面的若干個字符，這樣可以提高檢索速度。

7．刪除不再使用或者很少使用的索引

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

8 . 最左前綴匹配原則，非常重要的原則。

mysql會一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“ 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

9 .=和in可以亂序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

。

10 . 盡量選擇區(qū)分度高的列作為索引。

區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗(yàn)值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

11 .索引列不能參與計(jì)算，保持列“干凈”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

12 .盡量的擴(kuò)展索引，不要新建索引。

比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可

注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準(zhǔn)則，但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實(shí)踐。根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷，選擇最合適的索引方式。

當(dāng)前文章：索引怎么選擇mysql mysql索引原理
網(wǎng)站鏈接：http://m.jiaotiyi.com/article/ddeohgg.html

網(wǎng)站建設(shè)知識

索引怎么選擇mysql mysql索引原理

MySQL前綴索引

mysql 索引怎么使用

Mysql索引

mysql創(chuàng)建索引的原則

其他資訊