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

  • 时间:
  • 浏览:2
  • 来源:uu快3下载网站_uu快3开户二维码

从前面的做法中,大家都可以 发现,前缀索引本质上而是把栏位的前N位作为索引,你是什么 看起来,很像Oracle的函数索引。类式:

尝试在TEXT和BLOB的栏位上直接创建索引

人太好,Oracle都在类式的实现,对于文本,它都可以 通过substr的函数索引,实现同样甚至更多的功能。另外,经过探索,大家发现,从前数字和时间字段,在Oracle都可以 能 实现类式的功能。

select * from test_substr

建从前表,相当栏位长度最长为5。

当然,可能性把WHERE条件中substr加进小于5的值,就不再能用得上索引。可能性无法直接换为等价的、又含有substr(object_name,1,5)的一句话。

ID INT(10) PRIMARY KEY AUTO_INCREMENT,

建立前缀索引后,都可以 直接当做普通索引进行过滤。

delimiter;;

from

炫何如让 的写法,通过何如让 小技巧,都可以 在同一另一三个小 SQL里遍历多个值,一起去查想看 个值的确定度。

where substr(object_name,1,<N>)=:a and substr(object_name,1,5)=substr(:a,1,5);

在N>=5的而是,

callinit_test_prefix_ind(1000);;

文章来源于网络

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

插入1000行记录:

前缀索引的最大的好处是降低索引的大小。另外,可能性InnoDB单列索引长度都可以 了超过767bytes,可能性是text可能性blob字段,直接建立索引可能性会报错,而前缀索引都可以 绕过你是什么 限制。

TEXT_STR TEXT,

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

ERROR 1170 (4100): BLOB/TEXT column ‘blob_str’ used in key specification without a key length

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

大家再看看。

Create index test_substr_inx2 on test_substr(object_name);

简单做法:

有兴趣的,都可以 做个10053。Oracle实物实际进行执行计划解析的,而是从前一另一三个小 SQL。

endwhile;

begin

Select ..from table_name where column_name=’…’;

MySQL有一另一三个小 很有意思的索引类型,叫做前缀索引,它都可以 给某个文本字段的前面每段单独做索引,从而降低索引的大小。

ERROR 1170 (4100): BLOB/TEXT column ‘text_str’ used in key specification without a key length

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

FROM (SELECT @rownum:=1) r,test_prefix_ind limit 1,10

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

insertinto test_prefix_ind(NORMAL_STR,long_str,  TEXT_STR,BLOB_STR)

createprocedure init_test_prefix_ind(n int)

declare iint default 0;

dropFUNCTION if exists random_str;;

还是等价的。何如让 优化器还是都可以 继续加一另一三个小 谓词。

大家都可以 想看 ,找谓词中,增加了一另一三个小 从前一句话中都可以 了 的东西:

尝试在类型为varchar(100)的LONG_STR创建索引

setreturn_str=concat(return_str,md5(rand()));

declare iint default 0;

仅仅就从前吗?除了字符类型之外,数字类型和时间类型否是也支持?

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

begin

select * from test_substr

但Oracle仅止于此吗?大家在来试试看从前SQL, 这次,大家在条件上也使用substr,何如让 长度不为5。

where substr(object_name,1,<N>)=:a;

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

看看执行计划:

看看执行计划:

Create table test_substr as

大家最后再看从前例子。

(select * from dual connect by level < 100)

CREATETABLE TEST_PREFIX_IND (

LONG_STR VARCHAR(100),

mysql> alter table test_prefix_ind add key(text_str);

首先,创建一另一三个小 生成超过100长度的随机字符串的函数。

加进绑定变量看看:

SELECT * FROM TEST_SUBSTR WHERE OBJECT_NAME=:A AND SUBSTR(OBJECT_NAME,1,5)=SUBSTR(:A,1,5);

returnsubstring(return_str,1,n);

没现象,还是都可以 的。

大家创建一另一三个小 前缀长度为5的前缀索引。

来看看你是什么 一句话的执行效果

select R,count(distinct substr(long_str,1,R))/count(*)

为有哪些多了你是什么 东西?可能性,从逻辑上来说:

declarereturn_str varchar(1000) default “”;

Create index test_substr_inx on test_substr(substr(object_name,1,5));

人太好 还是都可以 的。可能性逻辑上来说

创建substr的函数索引:

Altertable Table_Name add key(column_name(prefix_len));

Select count(distinct substr(long_str,1,5))/count(*) from test_prefix_ind;

);

select * from  test_scale where object_name = ‘DBA_TABLES’;

因为很简单,‘DBA_TABLES’你是什么 值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。你是什么 ,在10053里也找都可以 了,但的确占据 。

mysql> alter table test_prefix_ind add key(blob_str);;

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

创建表格:

group by R;;

看看大小: 8992k

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

whilelength(return_str) < n do

where rownum < 1000;

create index test_scale_str_inx in test_scale(object_name);

BLOB_STR BLOB

(SELECT @rownum:=ceil(@rownum*1.4) AS  R

create table test_scale (object_name varchar2(5));

实际上,现象的关键在于等价与优化器的实物改写。

insert into test_scale select substr(object_name,1,5) from all_objects;

看看大小,528k(9520-8992), 远远小于LONG_STR的8992k.

大小分别是7M256K.

endwhile;

NORMAL_STR VARCHAR(20) ,

对于一另一三个小 可能性挺长的栏位,为什判断大慨的前缀索引呢?

alter table test_prefix_ind add key(text_str(100));;

做个测试看一下。

在刚才的表的基础上,创建时间类型上的trunc函数索引。

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

是百分百等价的。Oracle大慨自动做了语义上的优化。

while i< n do

altertable test_prefix_ind add key(LONG_STR);;

Createindex index_name on Table_Name(column_name(prefix_len));

select object_id,object_name||dbms_random.string(‘x’,dbms_random.value(1,100) as object_name,created from all_objects ,

成功了,何如让 Sub_part显示为767,表示系统自动创建了前缀长度为767的前缀索引;

seti=i+1;

创建测试表

end;;

对于Oracle的函数索引,大家一另一三个小 比较深的印象而是,where条件须要和函数索引里的表达式一致,都可以 利用上函数索引。但既然MySQL都可以 用前缀索引,作为老前辈的Oracle, 似乎应该都可以 实现才对。

) R,test_prefix_ind T

看看可能性创建普通索引,空间占用是几块。

drop  procedure if exists init_test_prefix_ind;;

都可以 使用上索引。

select * from test_

MySQL的前缀索引指的是对指定的栏位的前面几位建立的索引。

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

大家来看看,在Oracle上边,否是都可以 实现同样的功能。

CREATEFUNCTION random_str(n int) RETURNS varchar(1000)

对于你是什么 表,可能性数据是随机的,何如让 ,前5位可能性足够好。

Create index test_trunc_date_inx on test_substr(trunc(created));

都可以 想看 ,谓词中变成了:

Create index index_name on table_name(substr(column_name,1,<length>) );

可能性

在TEXT和BLOB栏位上建立索引,须要指定前缀长度。

end;;

values(random_str(20),random_str(rand()*100+1),random_str(rand()*100+1),random_str(rand()*100+1));

select * from test_substr where object_name=:a and substr(object_name,1,5)=substr(:a,1,5);

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