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

  • 时间:
  • 浏览:2
  • 来源:大发彩神UU直播现场_大发神彩UU直播现场官方

但Oracle仅止于此吗?大伙 在来试试看我希望SQL, 这次,大伙 在条件上也使用substr,你是什么 长度不为5。

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

seti=i+1;

select * from test_

创建substr的函数索引:

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

原因分析分析很简单,‘DBA_TABLES’你是什么 值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。你是什么 ,在8053里也找只有,但的确处在。

from

begin

returnsubstring(return_str,1,n);

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

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

Altertable Table_Name add key(column_name(prefix_len));

看看你是什么 创建普通索引,空间占用是哪几次。

创建表格:

对于Oracle的函数索引,大伙 另一个 多比较深的印象我希望,where条件前要和函数索引里的表达式一致,才能利用上函数索引。但既然MySQL能只有用前缀索引,作为老前辈的Oracle, 似乎应该才能实现才对。

Create table test_substr as

group by R;;

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

BLOB_STR BLOB

NORMAL_STR VARCHAR(20) ,

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

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

create index test_scale_str_inx in test_scale(object_name);

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

看看执行计划:

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

ID INT(10) PRIMARY KEY AUTO_INCREMENT,

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

LONG_STR VARCHAR(800),

我希望面的做法中,大伙 能只有发现,前缀索引本质上我希望把栏位的前N位作为索引,你是什么 看起来,很像Oracle的函数索引。你是什么于于:

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

建立前缀索引后,能只有直接当做普通索引进行过滤。

end;;

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

);

建我希望表,相当栏位长度最长为5。

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

看看查询有无能正常进行:

大伙 创建另一个 多前缀长度为5的前缀索引。

create table test_scale (object_name varchar2(5));

Create index test_substr_inx2 on test_substr(object_name);

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

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

文章来源于网络

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

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

成功了,你是什么 Sub_part显示为767,表示系统自动创建了前缀长度为767的前缀索引;

插入8000行记录:

能只有就看,谓词中变成了:

能只有使用上索引。

select * from test_substr

做个测试看一下。

在N>=5的前一天,

) R,test_prefix_ind T

altertable test_prefix_ind add key(LONG_STR);;

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

createprocedure init_test_prefix_ind(n int)

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

仅仅就我希望吗?除了字符类型之外,数字类型和时间类型有无也支持?

对于你是什么 表,你是什么 数据是随机的,你是什么 你是什么 ,前5位你是什么 足够好。

没难题,还是能只有的。

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

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

mysql> alter table test_prefix_ind add key(text_str);

(select * from dual connect by level < 80)

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

callinit_test_prefix_ind(8000);;

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

加上绑定变量看看:

看看大小: 8992k

大伙 来看看,在Oracle上边,有无才能实现同样的功能。

TEXT_STR TEXT,

大伙 能只有就看,找谓词中,增加了另一个 多我希望句子中只有的东西:

还是等价的。你是什么 你是什么 优化器还是能只有继续加另一个 多谓词。

while i< n do

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

endwhile;

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

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

创建测试表

大伙 再看看。

endwhile;

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

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

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

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

CREATETABLE TEST_PREFIX_IND (

你是什么

drop  procedure if exists init_test_prefix_ind;;

CREATEFUNCTION random_str(n int) RETURNS varchar(8000)

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

其实 还是能只有的。你是什么 逻辑上来说

dropFUNCTION if exists random_str;;

Createindex index_name on Table_Name(column_name(prefix_len));

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

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

为哪些地方多了你是什么 东西?你是什么 ,从逻辑上来说:

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

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

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

MySQL另一个 多很有意思的索引类型,叫做前缀索引,它能只有给某个文本字段的前面次责单独做索引,从而降低索引的大小。

insertinto test_prefix_ind(NORMAL_STR,long_str,  TEXT_STR,BLOB_STR)

where rownum < 8000;

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

values(random_str(20),random_str(rand()*800+1),random_str(rand()*800+1),random_str(rand()*80+1));

select * from test_substr

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

begin

declare iint default 0;

大小分别是7M256K.

有兴趣的,能只有做个8053。Oracle实物实际进行执行计划解析的,我希望我希望另一个 多SQL。

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

简单做法:

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

declarereturn_str varchar(8000) default “”;

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

看看执行计划:

其实,Oracle都不 你是什么于的实现,对于文本,它能只有通过substr的函数索引,实现同样甚至更多的功能。另外,经过探索,大伙 发现,我希望数字和时间字段,在Oracle才能只有实现你是什么于的功能。

select * from  test_scale where object_name = ‘DBA_TABLES’;

end;;

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

whilelength(return_str) < n do

炫你是什么 的写法,通过你是什么 小技巧,能只有在同另一个 多SQL里遍历多个值,一起去查就看个值的选者度。

delimiter;;

对于另一个 多你是什么 挺长的栏位,为什么我么我判断离米 的前缀索引呢?

大伙 最后再看我希望例子。

declare iint default 0;