MySQL的首碼索引及Oracle的類似實現，mysqloracle

MySQL的首碼索引及Oracle的類似實現，mysqloracle

MySQL有一個很有意思的索引類型，叫做首碼索引，它可以給某個文字欄位的前面部分單獨做索引，從而降低索引的大小。

其實，Oracle也有類似的實現，對於文本，它可以通過substr的函數索引，實現同樣甚至更多的功能。另外，經過探索，我們發現，原來數字和時間欄位，在Oracle也可以實作類別似的功能。

MySQL的首碼索引

MySQL的首碼索引指的是對指定的欄位的前面幾位建立的索引。

Altertable Table_Name add key(column_name(prefix_len));

或者

Createindex index_name on Table_Name(column_name(prefix_len));

建立首碼索引後，可以直接當做普通索引進行過濾。

Select ..from table_name where column_name=’…’;

首碼索引的最大的好處是降低索引的大小。另外，由於InnoDB單列索引長度不能超過767bytes，如果是text或者blob欄位，直接建立索引可能會報錯，而首碼索引可以繞過這一限制。

 

做個測試看一下。

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;;

首先，建立一個產生超過1000長度的隨機字串的函數。

建立測試表

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);;

嘗試在類型為varchar(1000)的LONG_STR建立索引

altertable test_prefix_ind add key(LONG_STR);;

成功了，但是Sub_part顯示為767,表示系統自動建立了前置長度為767的首碼索引；

看看大小: 8992k

嘗試在TEXT和BLOB的欄位上直接建立索引

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欄位上建立索引，必須指定前置長度。

alter table test_prefix_ind add key(text_str(30));;

看看大小，528k(9520-8992), 遠遠小於LONG_STR的8992k.

alter table test_prefix_ind add key(blob_str(30));;

看看大小，544k(10064-9520)。

看看幾個表的前置長度和大小。前置長度顯著降低了索引的大小。

看看查詢是否能正常進行:

可以使用上索引。

首碼索引長度的選擇

對於一個可能挺長的欄位，怎麼判斷合適的首碼索引呢？

簡單做法:

Select count(distinct substr(long_str,1,5))/count(*) from test_prefix_ind;

炫一點的寫法，通過一些小技巧，可以在同一個SQL裡遍曆多個值，同時查看多個值的選擇度。

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;;

對於這個表，由於資料是隨機的，所以，前5位已經足夠好。

我們建立一個前置長度為5的首碼索引。

alter table test_prefix_ind add key(long_str(5));

看看大小，僅僅258k(10320-10064),遠低於最早建立的8992k。

測試一下效能，有首碼索引時：

刪除索引後，效能差距很明顯：

Oracle的類似實現

從前面的做法中，我們可以發現，首碼索引本質上就是把欄位的前N位作為索引，這個看起來，很像Oracle的函數索引。類似於:

Create index index_name on table_name(substr(column_name,1,<length>) );

對於Oracle的函數索引，我們一個比較深的印象就是，where條件必須和函數索引裡的運算式一致，才能利用上函數索引。但既然MySQL可以用首碼索引，作為老前輩的Oracle, 似乎應該也能實現才對。

我們來看看，在Oracle裡面，是否能夠實現同樣的功能。

建立表格：

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;

建立substr的函數索引：

Create index test_substr_inx on test_substr(substr(object_name,1,5));

看看執行計畫：

神奇的事情發生了，的確走了索引，Oracle也支援首碼索引~~

我們可以看到，找謂詞中，增加了一個原來語句中沒有的東西:

換成綁定變數看看：

可以看到，謂詞中變成了：

為什麼多了這個東西？因為，從邏輯上來說：

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);

是百分百等價的。Oracle相當於自動做了語義上的最佳化。

有興趣的，可以做個10053。Oracle內部實際進行執行計畫解析的，就是這樣一個SQL。

SELECT * FROM TEST_SUBSTR WHERE OBJECT_NAME=:A AND SUBSTR(OBJECT_NAME,1,5)=SUBSTR(:A,1,5);

看看如果建立普通索引，空間佔用是多少。

Create index test_substr_inx2 on test_substr(object_name);

大小分別是7M和256K.

但Oracle僅止於此嗎？我們在來試試看另一個SQL, 這次，我們在條件上也使用substr,但是長度不為5。

果然還是可以的。因為邏輯上來說

select * from test_substr

 where substr(object_name,1,<N>)=:a;

在N>=5的時候，

select * from test_substr

where substr(object_name,1,<N>)=:a and substr(object_name,1,5)=substr(:a,1,5);

還是等價的。所以最佳化器還是可以繼續加一個謂詞。

當然，如果把WHERE條件中substr換成小於5的值,就不再能用得上索引。因為無法直接換為等價的、又帶有substr(object_name,1,5)的語句。

Oracle時間、數字上的首碼索引

僅僅就這樣嗎？除了字元類型之外，數字類型和時間類型是否也支援？

我們再看看。

在剛才的表的基礎上，建立時間類型上的trunc函數索引。

Create index test_trunc_date_inx on test_substr(trunc(created));

看看執行計畫:

沒問題，還是可以的。

建立數字類型上的trunc函數索引:

create index test_trunc_number on TEST_SUBSTR(trunc(object_id));

實際上，問題的關鍵在於等價與最佳化器的內部改寫。

我們最後再看另一個例子。

建另一個表，相當欄位長度最長為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);

來看看這個語句的執行效果

select * from  test_scale where object_name = 'DBA_TABLES';

神奇的事情再次發生，autotrace中db block gets/consistent gets都為0，這代表資料庫根本就沒去訪問表。

原因很簡單，‘DBA_TABLES’這個值長度大於5, 超出了表定義中的varchar2(5)了。object_name = ‘DBA_TABLES’就等價於恒否的條件了。這個，在10053裡也找不到，但的確存在。

關注本公眾號，回複：prelection，你可以找到本文的相關視頻文檔。

相關閱讀：

深入剖析-Oracle索引分支塊的結構

巧用複合索引，有效降低系統IO

MySQL SQL最佳化之覆蓋索引

遠端資料庫的表超過20個索引的影響

資源下載

關注公眾號：資料和雲（OraNews）回複關鍵字擷取

‘2017DTC’，2017DTC大會PPT

‘DBALIFE’，“DBA的一天”海報

‘DBA04’，DBA手記4經典篇章電子書

‘RACV1’, RAC系列課程視頻及ppt

‘122ARCH’，Oracle 12.2體繫結構圖

‘2017OOW’，Oracle OpenWorld資料

‘PRELECTION’，大講堂講師課程資料