本文實(shí)例講述了mysql建立高效的索引。分享給大家供大家參考,具體如下:
如何建立理想的索引?
- 查詢頻繁度
- 區(qū)分度
- 索引長(zhǎng)度
- 覆蓋字段
區(qū)分度
假設(shè)100萬(wàn)用戶,性別基本上男/女各為50W, 區(qū)分度就低。
長(zhǎng)度小
索引長(zhǎng)度直接影響索引文件的大小,影響增刪改的速度,并間接影響查詢速度(占用內(nèi)存多).
區(qū)分度高,長(zhǎng)度小
問(wèn)題:如果讓區(qū)分度高,而長(zhǎng)度?。?/p>
答:可以針對(duì)列中的值,從左往右截取部分,來(lái)建索引
(1)截的越短, 重復(fù)度越高,區(qū)分度越小, 索引效果越不好
(2)截的越長(zhǎng), 重復(fù)度越低,區(qū)分度越高, 索引效果越好,但帶來(lái)的影響也越大–增刪改變慢,并間影響查詢速度.
所以, 我們要在 區(qū)分度 + 長(zhǎng)度 兩者上,取得一個(gè)平衡。慣用手法:截取不同長(zhǎng)度,并測(cè)試其區(qū)分度。
假設(shè)我們有一張表:英語(yǔ)4級(jí)的單詞表,里面有13324條記錄,我們?cè)趺唇oname字段加索引呢?
如果計(jì)算區(qū)分度?
截取單詞第1位的不重復(fù)數(shù):
select count(distinct left(name,1)) from dict
總的數(shù)量:
select count(*) from dict
區(qū)分度:不重復(fù)數(shù)/總的數(shù)量,sql語(yǔ)句如下:
select (select count(distinct left(name,1)) from dict) / (select count(*) from dict) as rate;
然后按照這樣的步驟把其他長(zhǎng)度所對(duì)應(yīng)的區(qū)分度給找出來(lái),看一個(gè)這個(gè)圖表,可以知道當(dāng)長(zhǎng)度為11的時(shí)候重復(fù)度僅僅為1%,我們可以考慮建立11位長(zhǎng)的索引
alter table dict add index name name(11);
左前綴不好區(qū)分的情況
對(duì)于左前綴不易區(qū)分的列 ,建立索引的技巧
如url列
http://www.baidu.com
http://www.web-bc.cn
列的前11個(gè)字符都是一樣的,不易區(qū)分, 可以用如下2個(gè)辦法來(lái)解決
(1)把列內(nèi)容倒過(guò)來(lái)存儲(chǔ),并建立索引
moc.udiab.www//:ptth
nc.cb-bew.www//://ptth
這樣左前綴區(qū)分度大
(2)偽hash索引效果
同時(shí)存url和url_hash列
#建表
create table t10 (
id int primary key,
url char(60) not null default ''
);
#插入數(shù)據(jù)
insert into t10 values
(1,'http://www.baidu.com'),
(2,'http://www.sina.com'),
(3,'http://www.sohu.com.cn'),
(4,'http://www.onlinedown.net'),
(5,'http://www.gov.cn');
#修改表結(jié)構(gòu),添加urlcrc列
alter table t10 add urlcrc int unsigned not null;
在存儲(chǔ)的時(shí)候,將url對(duì)應(yīng)的crc32碼一同插入到數(shù)據(jù)庫(kù)中,然后按照urlcrc字段建立索引,然后查找的時(shí)候,我們?cè)跇I(yè)務(wù)層中將對(duì)應(yīng)的url轉(zhuǎn)換為crc32進(jìn)行查找,就可以利用上索引了。
因?yàn)閏rc的結(jié)果是32位int無(wú)符號(hào)數(shù),因此當(dāng)數(shù)據(jù)超過(guò)40億,也會(huì)有重復(fù),但這是值得的.(索引長(zhǎng)度為int4個(gè)字節(jié))
多列索引
多列索引的考慮因素—列的查詢頻率 , 列的區(qū)分度, 注意一定要結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景
以ecshop商城為例, goods表中的cat_id,brand_id,做多列索引,從區(qū)分度看,brand_id區(qū)分度更高, 但從 商城的實(shí)際業(yè)務(wù)業(yè)務(wù)看, 顧客一般先選大分類->小分類->品牌,最終選擇建立2個(gè)索引:
(1)index(cat_id,brand_id)
(2)index(cat_id,shop_price)
甚至可以再加 (3)index(cat_id,brand_id,shop_price),3個(gè)冗余索引
但(3)中的前2列和(1)中的前2列一樣,所以可以再去掉(1),建立2個(gè)索引
index(cat_id,price)
和 index(cat_id,brand_id,shop_price);
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
您可能感興趣的文章:- mysql重復(fù)索引與冗余索引實(shí)例分析
- mysql索引覆蓋實(shí)例分析
- mysql索引對(duì)排序的影響實(shí)例分析
- MySQL中NULL對(duì)索引的影響深入講解
- 詳解MySQL索引原理以及優(yōu)化
- 通過(guò)唯一索引S鎖與X鎖來(lái)了解MySQL死鎖套路
- mysql 添加索引 mysql 如何創(chuàng)建索引
- MySQL索引類型總結(jié)和使用技巧以及注意事項(xiàng)
- MySQL查看、創(chuàng)建和刪除索引的方法
- MySQL 創(chuàng)建索引(Create Index)的方法和語(yǔ)法結(jié)構(gòu)及例子
- mysql聚簇索引的頁(yè)分裂原理實(shí)例分析