MySQL有一個很有意思的索引型別，叫做字首索引，它可以給某個文字欄位的前面部分單獨做索引，從而降低索引的大小。
其實，Oracle也有類似的實現，對於文字，它可以通過substr的函式索引，實現同樣甚至更多的功能。另外，經過探索，我們發現，原來數字和時間欄位，在Oracle也可以實現類似的功能。
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;;複製程式碼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
);複製程式碼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的字首索引；
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複製程式碼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));;複製程式碼
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;;複製程式碼
alter table test_prefix_ind add key(long_str(5));複製程式碼看看大小，僅僅258k(10320-10064),遠低於最早建立的8992k。
從前面的做法中，我們可以發現，字首索引本質上就是把欄位的前N位作為索引，這個看起來，很像Oracle的函式索引。類似於:
Create index index_name on table_name(substr(column_name,1,<length>) );複製程式碼對於Oracle的函式索引，我們一個比較深的印象就是，where條件必須和函式索引裡的表示式一致，才能利用上函式索引。但既然MySQL可以用字首索引，作為老前輩的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;複製程式碼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);複製程式碼有興趣的，可以做個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);複製程式碼
但Oracle僅止於此嗎？我們在來試試看另一個SQL, 這次，我們在條件上也使用substr,但是長度不為5。
select * from test_substr
where substr(object_name,1,<N>)=:a;複製程式碼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)的語句。
僅僅就這樣嗎？除了字元型別之外，數字型別和時間型別是否也支援？
Create index test_trunc_date_inx on test_substr(trunc(created));複製程式碼
create index test_trunc_number on TEST_SUBSTR(trunc(object_id));複製程式碼
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，這代表資料庫根本就沒去訪問表。