开发者社区> 问答> 正文

MySQL的前缀索引及Oracle的类似实现

驻云科技 2018-01-31 12:08:56 3421
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的前缀索引;

神奇的事情再次发生,autotrace中db block gets/consistent gets都为0,这代表数据库根本就没去访问表。
原因很简单,‘DBA_TABLES’这个值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。这个,在10053里也找不到,但的确存在。


更多IT咨询请下载 prof.wangAPP IT人的云计算智慧助手。
Oracle 关系型数据库 MySQL 数据库 云计算 索引
分享到
取消 提交回答
全部回答(2)
添加回答
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程