在實(shí)際開(kāi)發(fā)中使用數(shù)據(jù)庫(kù)時(shí),難免會(huì)遇到一些大表數(shù)據(jù),對(duì)這些數(shù)據(jù)進(jìn)行查詢時(shí),有時(shí)候SQL會(huì)查詢得特別慢,這時(shí)候,有經(jīng)驗(yàn)的老師傅會(huì)告訴你,你看一下哪幾個(gè)字段查的多,加一個(gè)索引就好了。
創(chuàng)新互聯(lián)公司是一家專注于成都做網(wǎng)站、成都網(wǎng)站制作、成都外貿(mào)網(wǎng)站建設(shè)與策劃設(shè)計(jì),株洲網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:株洲等地區(qū)。株洲做網(wǎng)站價(jià)格咨詢:18980820575
那么,怎么合理地建立索引呢?這里分享一下我的一些經(jīng)驗(yàn),如有不妥之處,歡迎批評(píng)指正。
1、不要盲目建立索引 , 先分析再創(chuàng)建
索引雖然能大幅度提升我們的查詢性能,但也要知道,在你進(jìn)行增刪改時(shí),索引樹(shù)也要同樣地進(jìn)行維護(hù)。所以,索引不是越多越好,而是按需建立。最好是在一整塊模塊開(kāi)發(fā)完成后,分析一下,去針對(duì)大多數(shù)的查詢,建立聯(lián)合索引。
2、使用聯(lián)合索引盡量覆蓋多的條件
這是說(shuō)在一個(gè)慢sql里假如有五個(gè)where ,一個(gè) order by ,那么我們的聯(lián)合索引盡量覆蓋到這五個(gè)查詢條件,如果有必要,order by 也覆蓋上 。
3、小基數(shù)字段不需要索引
這個(gè)意思是,如果一張表里某個(gè)字段的值只有那么幾個(gè),那么你針對(duì)這個(gè)字段建立的索引其實(shí)沒(méi)什么意義,比如說(shuō),一個(gè)性別字段就兩種結(jié)果,你建了索引,排序也沒(méi)什么意思(也就是索引里把男女給分開(kāi)了)
所以說(shuō),索引盡量選擇基數(shù)大的數(shù)據(jù)去建立,能最大化地利用索引
4、長(zhǎng)字符串可以使用前綴索引
我們建立索引的字段盡量選擇字段類型較小的,比如一個(gè)varchar(20)和varchar(256)的,我們?cè)?0的上面建立的索引和在256上就有明顯的差距(字符串那么長(zhǎng)排序也不好排呀,唉)。
當(dāng)然,如果一定是要對(duì)varchar(256)建立索引,我們可以選擇里面的前20個(gè)字符放在索引樹(shù)里(這里的20不絕對(duì),選擇能盡量分辨數(shù)據(jù)的最小字符字段設(shè)計(jì)),類似這樣KEY index(name(20),age,job) ,索引只會(huì)對(duì)name的前20個(gè)字符進(jìn)行搜索,但前綴索引無(wú)法適用于order by 和 group by。
5、對(duì)排序字段設(shè)計(jì)索引的優(yōu)先級(jí)低
如果一個(gè)SQL里我們出現(xiàn)了范圍查找,后邊又跟著一個(gè)排序字段,那么我們優(yōu)先給范圍查找的字段設(shè)置索引,而不是優(yōu)先排序。
6、如果出現(xiàn)慢SQL,可以設(shè)計(jì)一個(gè)只針對(duì)該條SQL的聯(lián)合索引。
不過(guò)慢SQL的優(yōu)化,需要一步步去進(jìn)行分析,可以先用explain查看SQL語(yǔ)句的分析結(jié)果,再針對(duì)結(jié)果去做相應(yīng)的改進(jìn)。explain的東西我們下次再講。
PS:在 select 語(yǔ)句之前增加 explain 關(guān)鍵字,MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記,執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息,而不是 執(zhí)行這條SQL。
建立索引,要使用離散度(選擇度)更高的字段。
我們先來(lái)看一個(gè)重要的屬性列的 離散度,
count(distinct(column_name)) : count(*) -- 列的全部不同值個(gè)數(shù):所有數(shù)據(jù)行行數(shù)
數(shù)據(jù)行數(shù)相同的情況下,分子越大,列的離散度就越高。簡(jiǎn)單來(lái)說(shuō),如果列的重復(fù)值越多,離散度就越低,重復(fù)值越少,離散度就越高。
當(dāng)字段值比較長(zhǎng)的時(shí)候,建立索引會(huì)消耗很多的空間,搜索起來(lái)也會(huì)很慢。我們可以通過(guò)截取字段的前面一部分內(nèi)容建立索引,這個(gè)就叫前綴索引。
創(chuàng)建一張商戶表,因?yàn)榈刂纷侄伪容^長(zhǎng),在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));? // 截取12個(gè)字符作為前綴索引是最優(yōu)的嗎?
問(wèn)題是,截取多少呢?截取得多了,達(dá)不到節(jié)省索引存儲(chǔ)空間的目的,截取得少了,重復(fù)內(nèi)容太多,字段的散列度(選擇性)會(huì)降低。怎么計(jì)算不同的長(zhǎng)度的選擇性呢?
先看一下字段在全部數(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é)果是會(huì)隨著 n 的變大而變大。舉個(gè)例子,現(xiàn)在有兩個(gè)address(東大街長(zhǎng)興小區(qū),東大街福樂(lè)小區(qū)),那么 distinct(address,2) distinct(address,3)
==所以,截取的長(zhǎng)度越長(zhǎng)就會(huì)越接近字段在全部數(shù)據(jù)中的選擇度
==所以,我們要權(quán)衡索引大小和查詢速度。
舉個(gè)例子,通過(guò)不同長(zhǎng)度去計(jì)算,與全表的選擇性對(duì)比:
SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個(gè)字符的選擇度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個(gè)字符的選擇度
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 個(gè)字段時(shí) sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了,而且長(zhǎng)度也相較后面更短一些, 綜合考慮比較合適。
ALTER TABLE shop ADD KEY (address(11));
1.索引的個(gè)數(shù)不要過(guò)多(浪費(fèi)空間,更新變慢)
2.在用于 where 判斷 order 排序和 join 的(on)字段上創(chuàng)建索引
3.區(qū)分度低的字段,例如性別,不要建索引(離散度太低,導(dǎo)致掃描行數(shù)過(guò)多)
4.更新頻繁的值,不要作為主鍵或者索引(頁(yè)分裂)
5.不建議用無(wú)序的值作為索引,例如身份證、UUID(在索引比較時(shí)需要轉(zhuǎn)為ASCII,并且插入時(shí)可能造成頁(yè)分裂)
6.若在多個(gè)字段都要?jiǎng)?chuàng)建索引的情況下,聯(lián)合索引優(yōu)于單值索引
7.聯(lián)合索引把散列性高(區(qū)分度高)的值放在前面
1.選擇唯一性索引
唯一性索引的值是唯一的,可以更快速的通過(guò)該索引來(lái)確定某條記錄。例如,學(xué)生表中學(xué)號(hào)是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個(gè)學(xué)生的信息。如果使用姓名的話,可能存在同名現(xiàn)象,從而降低查詢速度。
2.為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引
經(jīng)常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作會(huì)浪費(fèi)很多時(shí)間。如果為其建立索引,可以有效地避免排序操作。
3.為常作為查詢條件的字段建立索引
如果某個(gè)字段經(jīng)常用來(lái)做查詢條件,那么該字段的查詢速度會(huì)影響整個(gè)表的查詢速度。因此,為這樣的字段建立索引,可以提高整個(gè)表的查詢速度。
4.限制索引的數(shù)目
索引的數(shù)目不是越多越好。每個(gè)索引都需要占用磁盤空間,索引越多,需要的磁盤空間就越大。修改表時(shí),對(duì)索引的重構(gòu)和更新很麻煩。越多的索引,會(huì)使更新表變得很浪費(fèi)時(shí)間。
5.盡量使用數(shù)據(jù)量少的索引
如果索引的值很長(zhǎng),那么查詢的速度會(huì)受到影響。例如,對(duì)一個(gè)CHAR(100)類型的字段進(jìn)行全文檢索需要的時(shí)間肯定要比對(duì)CHAR(10)類型的字段需要的時(shí)間要多。
6.盡量使用前綴來(lái)索引
如果索引字段的值很長(zhǎng),最好使用值的前綴來(lái)索引。例如,TEXT和BLOG類型的字段,進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間。如果只檢索字段的前面的若干個(gè)字符,這樣可以提高檢索速度。
7.刪除不再使用或者很少使用的索引
表中的數(shù)據(jù)被大量更新,或者數(shù)據(jù)的使用方式被改變后,原有的一些索引可能不再需要。數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)定期找出這些索引,將它們刪除,從而減少索引對(duì)更新操作的影響。
8 . 最左前綴匹配原則,非常重要的原則。
mysql會(huì)一直向右匹配直到遇到范圍查詢(、、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)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式
。
10 . 盡量選擇區(qū)分度高的列作為索引。
區(qū)分度的公式是count(distinct col)/count(*),表示字段不重復(fù)的比例,比例越大我們掃描的記錄數(shù)越少,唯一鍵的區(qū)分度是1,而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就 是0,那可能有人會(huì)問(wèn),這個(gè)比例有什么經(jīng)驗(yàn)值嗎?使用場(chǎng)景不同,這個(gè)值也很難確定,一般需要join的字段我們都要求是0.1以上,即平均1條掃描10條 記錄
11 .索引列不能參與計(jì)算,保持列“干凈”。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很簡(jiǎn)單,b+樹(shù)中存的都是數(shù)據(jù)表中的字段值,但進(jìn)行檢索時(shí),需要把所有元素都應(yīng)用函數(shù)才能比較,顯然成本 太大。所以語(yǔ)句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
12 .盡量的擴(kuò)展索引,不要新建索引。
比如表中已經(jīng)有a的索引,現(xiàn)在要加(a,b)的索引,那么只需要修改原來(lái)的索引即可
注意:選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準(zhǔn)則,但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實(shí)踐。根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷,選擇最合適的索引方式。