建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
MySQL有一個(gè)很有意思的索引類(lèi)型，叫做前綴索引，它可以給某個(gè)文本字段的前面部分單獨(dú)做索引，從而降低索引的大小。
其實(shí)，Oracle也有類(lèi)似的實(shí)現(xiàn)，對(duì)于文本，它可以通過(guò)substr的函數(shù)索引，實(shí)現(xiàn)同樣甚至更多的功能。另外，經(jīng)過(guò)探索，我們發(fā)現(xiàn)，原來(lái)數(shù)字和時(shí)間字段，在Oracle也可以實(shí)現(xiàn)類(lèi)似的功能。
MySQL的前綴索引
MySQL的前綴索引指的是對(duì)指定的欄位的前面幾位建立的索引。
- Altertable Table_Name add key(column_name(prefix_len));
或者
- Createindex index_name on Table_Name(column_name(prefix_len));
建立前綴索引后，可以直接當(dāng)做普通索引進(jìn)行過(guò)濾。
- Select ..from table_name where column_name=’…’;
前綴索引的***的好處是降低索引的大小。另外，由于InnoDB單列索引長(zhǎng)度不能超過(guò)767bytes，如果是text或者blob字段，直接建立索引可能會(huì)報(bào)錯(cuò)，而前綴索引可以繞過(guò)這一限制。
做個(gè)測(cè)試看一下。
- delimiter;;
- dropFUNCTION if exists random_str;;
- CREATEFUNCTION random_str(n int) RETURNS varchar(30000)
- begin
- declarereturn_str varchar(30000) default "";
- declare iint default 0;
- whilelength(return_str) < n do
- setreturn_str=concat(return_str,md5(rand()));
- endwhile;
- returnsubstring(return_str,1,n);
- end;;
首先，創(chuàng)建一個(gè)生成超過(guò)1000長(zhǎng)度的隨機(jī)字符串的函數(shù)。
創(chuàng)建測(cè)試表
- CREATETABLE TEST_PREFIX_IND (
- ID INT(10) PRIMARY KEY AUTO_INCREMENT,
- NORMAL_STR VARCHAR(20) ,
- LONG_STR VARCHAR(1000),
- TEXT_STR TEXT,
- BLOB_STR BLOB
- );
插入10000行記錄：
- drop procedure if exists init_test_prefix_ind;;
- createprocedure init_test_prefix_ind(n int)
- begin
- declare iint default 0;
- while i< n do
- insertinto test_prefix_ind(NORMAL_STR,long_str, TEXT_STR,BLOB_STR)
- values(random_str(20),random_str(rand()*1000+1),random_str(rand()*1000+1),random_str(rand()*300+1));
- seti=i+1;
- endwhile;
- end;;
- callinit_test_prefix_ind(10000);;
嘗試在類(lèi)型為varchar(1000)的LONG_STR創(chuàng)建索引
- altertable test_prefix_ind add key(LONG_STR);;
成功了，但是Sub_part顯示為767,表示系統(tǒng)自動(dòng)創(chuàng)建了前綴長(zhǎng)度為767的前綴索引；
看看大小: 8992k
嘗試在TEXT和BLOB的欄位上直接創(chuàng)建索引
- mysql> alter table test_prefix_ind add key(text_str);
- ERROR 1170 (42000): BLOB/TEXT column 'text_str' used in key specification without a key length
- mysql> alter table test_prefix_ind add key(blob_str);;
- ERROR 1170 (42000): BLOB/TEXT column 'blob_str' used in key specification without a key length
在TEXT和BLOB欄位上建立索引，必須指定前綴長(zhǎng)度。
- alter table test_prefix_ind add key(text_str(30));;
看看大小，528k(9520-8992), 遠(yuǎn)遠(yuǎn)小于LONG_STR的8992k.
- alter table test_prefix_ind add key(blob_str(30));;
看看大小，544k(10064-9520)。
看看幾個(gè)表的前綴長(zhǎng)度和大小。前綴長(zhǎng)度顯著降低了索引的大小。
看看查詢(xún)是否能正常進(jìn)行:
可以使用上索引。
前綴索引長(zhǎng)度的選擇
對(duì)于一個(gè)可能挺長(zhǎng)的欄位，怎么判斷合適的前綴索引呢？
簡(jiǎn)單做法:
- Select count(distinct substr(long_str,1,5))/count(*) from test_prefix_ind;
炫一點(diǎn)的寫(xiě)法，通過(guò)一些小技巧，可以在同一個(gè)SQL里遍歷多個(gè)值，同時(shí)查看多個(gè)值的選擇度。
- select R,count(distinct substr(long_str,1,R))/count(*)
- from
- (SELECT @rownum:=ceil(@rownum*1.4) AS R
- FROM (SELECT @rownum:=1) r,test_prefix_ind limit 1,10
- ) R,test_prefix_ind T
- group by R;;
對(duì)于這個(gè)表，由于數(shù)據(jù)是隨機(jī)的，所以，前5位已經(jīng)足夠好。
我們創(chuàng)建一個(gè)前綴長(zhǎng)度為5的前綴索引。
- alter table test_prefix_ind add key(long_str(5));
看看大小，僅僅258k(10320-10064),遠(yuǎn)低于最早創(chuàng)建的8992k。
測(cè)試一下性能，有前綴索引時(shí)：
刪除索引后，性能差距很明顯：
Oracle的類(lèi)似實(shí)現(xiàn)
從前面的做法中，我們可以發(fā)現(xiàn)，前綴索引本質(zhì)上就是把欄位的前N位作為索引，這個(gè)看起來(lái)，很像Oracle的函數(shù)索引。類(lèi)似于:
- Create index index_name on table_name(substr(column_name,1,
) );
對(duì)于Oracle的函數(shù)索引，我們一個(gè)比較深的印象就是，where條件必須和函數(shù)索引里的表達(dá)式一致，才能利用上函數(shù)索引。但既然MySQL可以用前綴索引，作為老前輩的Oracle, 似乎應(yīng)該也能實(shí)現(xiàn)才對(duì)。
我們來(lái)看看，在Oracle里面，是否能夠?qū)崿F(xiàn)同樣的功能。
創(chuàng)建表格：
- Create table test_substr as
- select object_id,object_name||dbms_random.string('x',dbms_random.value(1,1000) as object_name,created from all_objects ,
- (select * from dual connect by level < 100)
- where rownum < 10000;
創(chuàng)建substr的函數(shù)索引：
- Create index test_substr_inx on test_substr(substr(object_name,1,5));
看看執(zhí)行計(jì)劃：
神奇的事情發(fā)生了，的確走了索引，Oracle也支持前綴索引~~
我們可以看到，找謂詞中，增加了一個(gè)原來(lái)語(yǔ)句中沒(méi)有的東西:
換成綁定變量看看：
可以看到，謂詞中變成了：
為什么多了這個(gè)東西？因?yàn)椋瑥倪壿嬌蟻?lái)說(shuō)：
- select * from test_substr where object_name=:a;
和
- select * from test_substr where object_name=:a and substr(object_name,1,5)=substr(:a,1,5);
是***等價(jià)的。Oracle相當(dāng)于自動(dòng)做了語(yǔ)義上的優(yōu)化。
有興趣的，可以做個(gè)10053。Oracle內(nèi)部實(shí)際進(jìn)行執(zhí)行計(jì)劃解析的，就是這樣一個(gè)SQL。
- SELECT * FROM TEST_SUBSTR WHERE OBJECT_NAME=:A AND SUBSTR(OBJECT_NAME,1,5)=SUBSTR(:A,1,5);
看看如果創(chuàng)建普通索引，空間占用是多少。
- Create index test_substr_inx2 on test_substr(object_name);
大小分別是7M和256K.
但Oracle僅止于此嗎？我們?cè)趤?lái)試試看另一個(gè)SQL, 這次，我們?cè)跅l件上也使用substr,但是長(zhǎng)度不為5。
果然還是可以的。因?yàn)檫壿嬌蟻?lái)說(shuō)
- select * from test_substr where substr(object_name,1,
)=:a;
在N>=5的時(shí)候，
- select * from test_substr where substr(object_name,1,
)=:a and substr(object_name,1,5)=substr(:a,1,5);
還是等價(jià)的。所以?xún)?yōu)化器還是可以繼續(xù)加一個(gè)謂詞。
當(dāng)然，如果把WHERE條件中substr換成小于5的值,就不再能用得上索引。因?yàn)闊o(wú)法直接換為等價(jià)的、又帶有substr(object_name,1,5)的語(yǔ)句。
Oracle時(shí)間、數(shù)字上的前綴索引
僅僅就這樣嗎？除了字符類(lèi)型之外，數(shù)字類(lèi)型和時(shí)間類(lèi)型是否也支持？
我們?cè)倏纯础?/p>
在剛才的表的基礎(chǔ)上，創(chuàng)建時(shí)間類(lèi)型上的trunc函數(shù)索引。
- select * from test_substr
- where substr(object_name,1,
)=:a and substr(object_name,1,5)=substr(:a,1,5);
看看執(zhí)行計(jì)劃:
沒(méi)問(wèn)題，還是可以的。
創(chuàng)建數(shù)字類(lèi)型上的trunc函數(shù)索引:
- create index test_trunc_number on TEST_SUBSTR(trunc(object_id));
實(shí)際上，問(wèn)題的關(guān)鍵在于等價(jià)與優(yōu)化器的內(nèi)部改寫(xiě)。
我們***再看另一個(gè)例子。
建另一個(gè)表，相當(dāng)欄位長(zhǎng)度最長(zhǎng)為5。
- create table test_scale (object_name varchar2(5));
- insert into test_scale select substr(object_name,1,5) from all_objects;
- create index test_scale_str_inx in test_scale(object_name);
來(lái)看看這個(gè)語(yǔ)句的執(zhí)行效果
- select * from test_scale where object_name = 'DBA_TABLES';
神奇的事情再次發(fā)生，autotrace中db block gets/consistent gets都為0，這代表數(shù)據(jù)庫(kù)根本就沒(méi)去訪(fǎng)問(wèn)表。
原因很簡(jiǎn)單，‘DBA_TABLES’這個(gè)值長(zhǎng)度大于5, 超出了表定義中的varchar2(5)了。object_name = ‘DBA_TABLES’就等價(jià)于恒否的條件了。這個(gè)，在10053里也找不到，但的確存在。
文章名稱(chēng)：MySQL的前綴索引及Oracle的類(lèi)似實(shí)現(xiàn)
網(wǎng)址分享：http://www.5511xx.com/article/cdodoip.html
