MySQL有一个非常有趣的索引类型叫前缀索引，它可以单独索引一个文本字段的前面部分，从而减少索引的大小。其实Oracle也有类似的实现。对于text，可以通过substr的函数索引实现相同甚至更多的功能。另外，经过摸索，我们发现原来的数字和时间字段在Oracle中也可以实现类似的功能。MySQL的前缀索引MySQL的前缀索引是指建立在指定字段前几位的索引。AltertableTable_Nameaddkey(column_name(prefix_len));或Createindexindex_nameonTable_Name(column_name(prefix_len));前缀索引建立后，可以直接作为普通索引进行过滤。选择..fromtable_namewherecolumn_name='...';前缀索引的最大优点是减少了索引的大小。另外，由于InnoDB单列索引的长度不能超过767字节，如果是text或者blob字段，直接建索引可能会报错，前缀索引可以绕过这个限制。做一个测试看看。定界符；；dropFUNCTIONifexistsrandom_str；；CREATEFUNCTIONrandom_str(nint)RETURNSvarchar(30000)begindeclarereturn_strvarchar(30000)default"";declareiintdefault0;whilelength(return_str)altertabletest_prefix_indaddkey(text_str);ERROR1170(42000):BLOB/TEXTcolumn'text_str'usedinkeyspecificationwithoutkeylengthmysql>altertabletest_prefix_indaddkey(blob_str);;ERROR1170(4200):ERROR1170(4200)TEXTcolumn'blob_str'usedinkeyspecificationwithoutkeylength要在TEXT和BLOB列上建立索引，必须指定前缀长度。altertabletest_prefix_indaddkey(text_str(30));;看大小，528k(9520-8992)，比LONG_STR的8992k小很多.altertabletest_prefix_indaddkey(blob_str(30));;看大小，544k(10064-9520).看几个表的前缀长度和大小。前缀长度显着减小了索引的大小。查看查询是否正常：可以使用上索引。前缀索引长度的选择对于一个可能比较长的字段，如何判断合适的前缀索引呢？简单方法：Selectcount(distinctsubstr(long_str,1,5))/count(*)fromtest_prefix_ind;有点眼花缭乱的写法，通过一些技巧，可以在同一条SQL中遍历多个值，同时检查多个值的选择性。selectR,count(distinctsubstr(long_str,1,R))/count(*)from(SELECT@rownum:=ceil(@rownum*1.4)ASRFROM(SELECT@rownum:=1)r,test_prefix_indlimit1,10)R,test_prefix_indTgroupbyR;;对于这张表，由于数据是随机的，所以前5位数字就足够了。我们创建一个前缀长度为5的前缀索引。altertabletest_prefix_indaddkey(long_str(5));看大小，只有258k（10320-10064），比最早创建的8992k低很多。测试性能。有前缀索引时：删除索引后，性能差距很明显：Oracle的类似实现从前面的实践我们可以发现，前缀索引本质上是使用字段的前N位作为索引。这看起来很像Oracle的功能索引。类似于：Createindexindex_nameontable_name(substr(column_name,1,));对于Oracle的函数索引，我们有一个很深的印象就是where条件必须和函数索引中的表达式一致才能使用函数索引。不过MySQL既然可以使用前缀索引，作为老牌的Oracle，看来应该也能实现。让我们看看在Oracle中是否可以实现同样的功能。创建表：Createtabletest_substrassselectobject_id,object_name||dbms_random.string('x',dbms_random.value(1,1000)asobject_name,createdfromall_objects,(select*fromdualconnectbylevel<100)whererownum<10000;创建substr函数索引：Createsubjsubjsubstr_inxonstr_str_test(,1,5));看执行计划：神奇的事情发生了，索引确实没有了，Oracle也支持前缀索引~~我们可以看到在predicate中，新增了一个原来语句中没有的东西补充：change看一下绑定变量：可以看到predicate变成了：这个东西怎么多了？因为，从逻辑上讲：select*fromtest_substrwhereobject_name=:a;和select*fromtest_substrwhereobject_name=:aandsubstr(object_name,1,5)=substr(:a,1,5);是绝对等价的。Oracle相当于自动优化了语义。有兴趣的可以做个10053，Oracle内部实际的执行计划分析就是这样一条SQL。SELECT*FROMTEST_SUBSTRWHEREOBJECT_NAME=:AANDSUBSTR(OBJECT_NAME,1,5)=SUBSTR(:A,1,5);如果创建普通索引，看看占用多少空间。Createindextest_substr_inx2ontest_substr(object_name);大小为7M和256K。但是甲骨文就此止步了吗？再试试SQL，这次条件里也用了substr，但是长度不是5，果然还是可以的。因为从逻辑上讲select*fromtest_substrwheresubstr(object_name,1,)=:a;当N>=5时，select*fromtest_substrwheresubstr(object_name,1,)=:aandsubstr(object_name,1,5)=substr(:a,1,5);仍然是等价的。所以优化器仍然可以继续添加谓词。当然，如果将WHERE条件中的s??ubstr替换为小于5的值，就不能再使用索引了。因为它不能直接替换为具有substr(object_name,1,5)的等效语句。Oracle时间和数字上的前缀索引就是这样的吗？除了字符类型，是否也支持数值类型和时间类型？让我们再看看。基于刚才的表，在时间类型上创建一个trunc函数索引。select*fromtest_substrwheresubstr(object_name,1,)=:aandsubstr(object_name,1,5)=substr(:a,1,5);看执行计划：没问题，还是可以的。在数字类型上创建一个trunc函数索引：createindextest_trunc_numberonTEST_SUBSTR(trunc(object_id));其实问题的关键在于对等和优化器的内部重写。让我们看另一个例子。再创建一个最大字段长度为5的表createtabletest_scale(object_namevarchar2(5));insertintotest_scaleselectsubstr(object_name,1,5)fromall_objects;createindextest_scale_str_inxintest_scale(object_name);看这条语句的执行效果select*fromtest_scalewhereobject_name='DBA_TABLES';神奇的事情又发生了，autotracesdbb/consistentgets是0，也就是说数据库根本不访问表。原因很简单，'DBA_TABLES'的长度大于5，超过了表定义中的varchar2(5)。object_name='DBA_TABLES'相当于常量条件。这个在10053中是找不到的，但是确实存在。
