[[271557]]

概述

前面我們講了怎么去計(jì)算索引所占用的長度?那么換個(gè)方式想?索引又應(yīng)該設(shè)置多少長度比較合理呢?

區(qū)分度與索引長度的權(quán)衡

首先索引長度和區(qū)分度是相互矛盾的，

索引長度太短，那么區(qū)分度就很低，吧索引長度加長，區(qū)分度就高，但是索引也是要占內(nèi)存的，所以我們需要找到一個(gè)平衡點(diǎn);

那么這個(gè)平衡點(diǎn)怎么來定?

比如用戶表有個(gè)字段 username ，要給他加索引，問題是索引長度多少合適?

其實(shí)我們知道 百家姓里面有百多個(gè)姓 ，但是大多數(shù)人的姓 集中在前十多個(gè);如果我設(shè)置索引索引長度為1，占內(nèi)存少，但是區(qū)分度低，區(qū)分度低索引的效率越低。太長則占內(nèi)存;

首先 mysql的索引都是排好序的。如果區(qū)分度高排序越快，區(qū)分度越低，排序慢;

舉個(gè)例子： (張，張三，張三哥)，如果索引長度取1的話，那么每一行的索引都是 張 這個(gè)字，完全沒有區(qū)分度，你讓他怎么排序?結(jié)果這樣三行完全是隨機(jī)排的，因?yàn)樗饕家粯?如果長度取2，那么排序的時(shí)候至少前兩個(gè)是排對了的，如果取3，區(qū)分度達(dá)到100%，排序完全正確;

那是不是索引越長越好? 答案肯定是錯(cuò)的，比如 (張,李,王) 和 (張三啦啦啦，張三呵呵呵，張三呼呼呼);前者在內(nèi)存中排序占得空間少，排序也快，后者明顯更慢更占內(nèi)存。

總之：

索引長度越低，索引在內(nèi)存中占的長度越小，排序越快，然而區(qū)分度就越低。這樣不利于查找。

索引長度越長，區(qū)分度就高，雖然利于查找了，但是索引在內(nèi)存中占得空間就多了。

mysql創(chuàng)建索引的時(shí)候指定索引長度

大部分的索引前面一部分的長度就能夠有很好的區(qū)分度了。

通過減小索引長度，這樣能夠減小索引文件的大小，能夠加快數(shù)據(jù)的insert。

語法：

CREATE INDEX index_name ON table_name (column_name(length), clolumn_name(length)…)； 

如何確認(rèn)當(dāng)前字段設(shè)置一個(gè)合適的長度呢?

索引長度與區(qū)分度要做一個(gè)取舍;這個(gè)取舍不是沒有一個(gè)固定的量;需要根據(jù)數(shù)據(jù)庫里面的數(shù)據(jù)來判斷;比較常規(guī)的公式是：

select count(distinct left(password, 5))/count(*) from user 

其中password是要加索引的字段，5是索引長度，求出一個(gè)浮點(diǎn)數(shù)，這個(gè)浮點(diǎn)數(shù)是逐漸趨向1的，上面這個(gè)比值，也算是區(qū)分度，也可以算作索引長度測試值，多測試幾組，找出最合適的來，一般的區(qū)分值在0.1左右就差不多了。

網(wǎng)上找了個(gè)圖片來分析下;

這個(gè)地方觀察到，當(dāng)索引長度達(dá)到4的時(shí)候就已經(jīng)趨向1了，所以長度設(shè)為4是最佳的，在大點(diǎn)增加的索引效果已經(jīng)很小了，這個(gè)地方不是說必須接近1才行;總之要找一個(gè)平衡點(diǎn);

還有一些特殊的字段常規(guī)方法用起不太順暢，比如有一個(gè)url字段，絕大部分的url都是 http://www. 開頭的,這種情況下索引長度取取到11都是無效的，需要更長的索引，那么有沒有優(yōu)雅的方式來解決呢;

• 第一種方法： 可以將數(shù)據(jù)倒序存入數(shù)據(jù)庫;
• 第二種方法：對字符串進(jìn)行crc32哈希處理;

兩種方法都不錯(cuò)，當(dāng)然要配合客戶端程序完成;

簡單測試：

這個(gè)方法可能是優(yōu)化最后才考慮的點(diǎn)了，不建議太過深究，了解到這就行了。后面會(huì)分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~