阿里云
社区时光机
发表主题 回复主题
  • 309阅读
  • 2回复

[干货分享]MySQL的前缀索引及Oracle的类似实现

级别: 码农
发帖
112
云币
305
MySQL有一个很有意思的索引类型,叫做前缀索引,它可以给某个文本字段的前面部分单独做索引,从而降低索引的大小。 (Dy6I;S  
其实,Oracle也有类似的实现,对于文本,它可以通过substr的函数索引,实现同样甚至更多的功能。另外,经过探索,我们发现,原来数字和时间字段,在Oracle也可以实现类似的功能。 dRdI('  
?QDHEC62  
MySQL的前缀索引 0}'xoYv f  
prHM}n{0  
7U9*-9  
MySQL的前缀索引指的是对指定的栏位的前面几位建立的索引。
zBQV2.@  
Altertable Table_Name add key(column_name(prefix_len));
jW,b"[  
或者
Xkqq$A4  
Createindex index_name on Table_Name(column_name(prefix_len));
T*Dd% f  
建立前缀索引后,可以直接当做普通索引进行过滤。
eD|p1+76  
Select ..from table_name where column_name=’…’;
&z;bX-"E  
前缀索引的最大的好处是降低索引的大小。另外,由于InnoDB单列索引长度不能超过767bytes,如果是text或者blob字段,直接建立索引可能会报错,而前缀索引可以绕过这一限制。 x0KW\<k  
做个测试看一下。
Ulf'gD4e  
delimiter;; S/itK3  
dropFUNCTION if exists random_str;; H*m3i;"4p\  
CREATEFUNCTION random_str(n int) RETURNS varchar(30000) t@&U2JaL>W  
begin v)b_bU]Hx  
declarereturn_str varchar(30000) default “”; uPT2ga]  
declare iint default 0; xU1_L*tu '  
whilelength(return_str) < n do w)rd--9f  
setreturn_str=concat(return_str,md5(rand())); |p .o^  
endwhile; kF;N}O2?{  
returnsubstring(return_str,1,n); >1}@Q(n/}{  
end;;
xekU2u}WE  
首先,创建一个生成超过1000长度的随机字符串的函数。 CN{xh=2qY[  
创建测试表
O"M2*qiH  
CREATETABLE TEST_PREFIX_IND ( 2 rFjYx8D!  
ID INT(10) PRIMARY KEY AUTO_INCREMENT, u|mTF>L  
NORMAL_STR VARCHAR(20) , hCOCX_  
LONG_STR VARCHAR(1000), |JL?"cc  
TEXT_STR TEXT, UkTq0-N;2  
BLOB_STR BLOB ;!+-fn4C  
);
2il`'X  
插入10000行记录:
\{a!Z&df  
drop  procedure if exists init_test_prefix_ind;; \ xJ_ )r  
createprocedure init_test_prefix_ind(n int) [;Y,nSw  
begin Op'&c0l  
declare iint default 0; +n%d,Pz  
while i< n do .6=;{h4cpB  
insertinto test_prefix_ind(NORMAL_STR,long_str,  TEXT_STR,BLOB_STR) &7XsyDo6  
values(random_str(20),random_str(rand()*1000+1),random_str(rand()*1000+1),random_str(rand()*300+1)); *x &  
seti=i+1; 64L;np>  
endwhile; ~n~j2OE  
end;; 4J1_rMfh  
callinit_test_prefix_ind(10000);;
B1V+CP3t  
尝试在类型为varchar(1000)的LONG_STR创建索引
]$,3vYBf  
altertable test_prefix_ind add key(LONG_STR);;
O'Q,;s`uC  
成功了,但是Sub_part显示为767,表示系统动创建了前缀长度为767的前缀索引; 2lCgUe)N  
8og8;#mnyr  
神奇的事情再次发生,autotrace中db block gets/consistent gets都为0,这代表数据库根本就没去访问表。 <N:)Xf9`  
原因很简单,‘DBA_TABLES’这个值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。这个,在10053里也找不到,但的确存在。 M2$Hb_S{  
}BUm}.-{u,  
aX2N Qq>s  
更多IT咨询请下载 prof.wangAPP IT人的云计算智慧助手。 p'0X>>$  
[ 此帖被驻云科技在2018-01-31 12:09重新编辑 ]
级别: 架构狮
发帖
1246
云币
1807
只看该作者 沙发  发表于: 01-31
      
级别: 码农
发帖
112
云币
305
只看该作者 板凳  发表于: 02-01
  
发表主题 回复主题
« 返回列表上一主题下一主题

限100 字节
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
 
验证问题: 57 - 40 = ?
上一个 下一个