开发者社区> 技术小能手> 正文

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

简介:
+关注继续查看

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的前缀索引;

5191bc643f543324db32581afa0b5567c14a0af5

看看大小: 8992k

0f50d03db66feb32ceba80cc478dc16b929ba800

尝试在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.

5ddc3d1e1f9a779ce3b6d496c578e7de0f0e834c

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

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

fcb60da8d86f7de9acf48cb7aea0e81783870396

看看几个表的前缀长度和大小。前缀长度显著降低了索引的大小。

8f8aa9edd198219e42db5c3422aa35a521162984

看看查询是否能正常进行:

184ad0e01ec08b4d86b625dbb72d3ded00ff757f

可以使用上索引。

前缀索引长度的选择

对于一个可能挺长的栏位,怎么判断合适的前缀索引呢?

简单做法:

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

44c529cbd56f92d6c97b0c8d6dd96e5e40bebe80

对于这个表,由于数据是随机的,所以,前5位已经足够好。

我们创建一个前缀长度为5的前缀索引。

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

看看大小,仅仅258k(10320-10064),远低于最早创建的8992k

c01f71dce86a9a91bc5012451a9bd28a3ec5eeff

测试一下性能,有前缀索引时:

29050a558b3023b0abf0d3671b024b29c4f03fab

删除索引后,性能差距很明显:

f4298e7bd2799bde6c8d725f4ec2a3421ec3df1b

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

看看执行计划:

ca4af3e20da82cfff9b8e2b6593a4f8d46f3d0f9

神奇的事情发生了,的确走了索引,Oracle也支持前缀索引~~

我们可以看到,找谓词中,增加了一个原来语句中没有的东西:

47274310272193667855e7f110a459966db76a10

换成绑定变量看看:

2c512d1487ca7cc095183cc81d4eea9304fe4f4c

可以看到,谓词中变成了:

083c2b39807b12230ec028a5e27b5d1e243c4a46

为什么多了这个东西?因为,从逻辑上来说:

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

311f2ed90b53239ddf124fde6e2b66115a3cee87

大小分别是7M256K.

但Oracle仅止于此吗?我们在来试试看另一个SQL, 这次,我们在条件上也使用substr,但是长度不为5。

603b53cb0ab37247950ea7a2cb6ba586e00dfaa7

果然还是可以的。因为逻辑上来说

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

看看执行计划:

e963270c93485619c4283d22099d9730a28898ed

没问题,还是可以的。

创建数字类型上的trunc函数索引:

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

b204c297a755c992c92d60974e163c3bdf90fee4

实际上,问题的关键在于等价与优化器的内部改写。

我们最后再看另一个例子。

建另一个表,相当栏位长度最长为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';

655236aad640716e53139f62c12fbb67dd2e21cd

神奇的事情再次发生,autotrace中db block gets/consistent gets都为0,这代表数据库根本就没去访问表。

原因很简单,‘DBA_TABLES’这个值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。这个,在10053里也找不到,但的确存在。


原文发布时间为:2018-01-10
本文作者:罗海雄 
本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”微信公众号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL和Oracle的添加字段的处理差别
昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处理的。没法在已有的字段1,字段2中间添加一个字段3。
908 0
Mysql、SqlServer和Oracle 添加修改删除字段
MySql:添加单列:ALTER TABLE 表名 ADD 列名 数据类型添加多列:ALTER TABLE 表名 ADD 列名1 数据类型1,Add 列名2 数据类型2修改单列数据类型:ALTER TABLE 表名 CHANGE COLUMN 列名 数据类型同时修改多列数据类型:ALTE...
682 0
ORACLE MYSQL中join 字段类型不同索引失效的情况
关于JOIN使用不同类型的字段类型,数据库可能进行隐士转换,MYSQL ORACLE都是如此, 下面使用一个列子来看看,脚本如下: mysql: drop table testjoin1; drop table testjoin2; create tab...
1162 0
MYSQL多表更新删除以及和ORACLE的对比
MYSQL多表更新操作, 一般来说这类操作可以用IN和NOT IN,至少在ORACLE中可以这样做,ORACLE对IN和NOT IN的优化程度很高 一般使用SEMI JOIN和ANTI JOIN进行半连接,抛弃不需要的行,但是MYSQL在这方面性能还是有一些问题, 所以建议使用连接的方式如下,对于联合UPDATE ORACLE可以使用MERGE来代替比如 update a,b set a.name=b.name where a.id=b.id; ORACLE是没有这种语法的,一句话MYSQL的连接查询在ORACLE中都可以有相应的代替。
841 0
ORACLE SEQUENCE和MYSQL 自增字段对比
ORACLE 序列:不同于MYSQL的自增字段ORACLE序列是单独的一个object,单独进行维护,可以查看 SQL> select * from dba_sequences a where a.
1135 0
ORACLE 透明网关访问 MYSQL 表
环境 LINUX 64 BIT ORACLE 11.2.0.4      LINUX 64 BIT MYSQL 5.7.14 1、 MYSQL建立一个用户用于ORACLE DBLINK 建立这里忽略掉 2、 11gR2 默认安装了透明网关检查一下 [ora...
1438 0
ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别
ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理 我们清楚ORACLE中的b+索引是对键值的NULL进行存储的,以致于我们 IS NULL这种肯定是用不到索引的, 当然这提及的ORACLE表为堆表,索引为单列B+树索引,(有一种优化方式为建立...
953 0
mysql中实现行号,oracle中的rowid
mysql中实现行号需要用到MYSQL的变量,因为MySql木有rownumber。   MYSQL中变量定义可以用 set @var=0 或 set @var:=0   可以用=或:=都可以,但是如果变量用在其他语句中,如:select那么必须用:=,因为=会被当作一个比较符号   1 select @rownum:=@rownum+1 as rownum, b.* from (select @rownum:=0) a, b   这个很有用哦,比如要取到排名好多的。
1356 0
+关注
技术小能手
云栖运营小编~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
Oracle 数据库笔记
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载