仓酷云

标题: MYSQL编程：利用索引的误区之三：基于函数的索引 [打印本页]

作者: 莫相离 时间: 2015-1-16 22:31
标题: MYSQL编程：利用索引的误区之三：基于函数的索引
要想在MySQL与其他数据库之间进行一个面面俱到的功能设置对比，并不是一件容易的MySQL学习教程。随着新软件版本的发布或一些补丁的推出。函数|索引利用索引的误区之三：基于函数的索引
利用基于函数的索引（BFI,BasedFunctionIndex）：

从Oracle8i入手下手，可使用基于函数的索引来进步查询功能，

利用基于函数的索引，必要几个前提：

1，用户必要有createindex大概createanyindex权限

2，用户必要有queryrewrite大概globalqueryrewirte权限

3，设置体系参数query_rewrite_enabled=TRUE

和query_rewrite_integrity=enforced

4，设置体系参数：COMPATIBLE=8.1.0.0.0大概更高

5，创立了BFI后，必要对表举行剖析

请看上面的例子：

起首，在没有创建函数索引的情形下，我们看到查询没有如我们想一想一样利用单列（dname）索引：

SQL>setautotracetraceonly

SQL>select*fromdeptwheresubstr(dname,1,5)=aaa;

未选定行

已用工夫:00:00:00.00

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(FULL)OFDEPT

Statistics

----------------------------------------------------------

134recursivecalls

0dbblockgets

20consistentgets

0physicalreads

0redosize

323bytessentviaSQL*Nettoclient

372bytesreceivedviaSQL*Netfromclient

1SQL*Netroundtripsto/fromclient

2sorts(memory)

0sorts(disk)

0rowsprocessed

上面间接创建基于函数的索引，看看是不是查询是不是可使用我们创建的索引

SQL>createindexdept_id5ondept(substr(dname,1,5));

createindexdept_id5ondept(substr(dname,1,5))

*

ERROR位于第1行:

ORA-01031:权限不敷

已用工夫:00:00:00.00

SQL>setautotraceoff

SQL>colusernameformata10

SQL>colprivilegeformata20

SQL>selectusername,privilegefromuser_sys_privs;

USERNAMEPRIVILEGE

------------------------------

DEMOUNLIMITEDTABLESPACE

PUBLICSELECTANYTABLE

已用工夫:00:00:00.00

SQL>selectusername,granted_rolefromuser_role_privs;

USERNAMEGRANTED_ROLE

----------------------------------------

DEMOCONNECT

DEMORESOURCE

PUBLICPLUSTRACE

已用工夫:00:00:00.01

我们看到，固然用户有connect和resource脚色，可是仍旧没有创建函数索引的权限。

我们利用sysdba身份上岸，给demo用户授createanyindex和globalqueryrewrite权限：

SQL>connlunar/lunar@test1assysdba

已毗连。

SQL>grantcreateanyindextodemo;

受权乐成。

已用工夫:00:00:00.00

SQL>grantglobalqueryrewritetodemo;

受权乐成。

已用工夫:00:00:00.00

SQL>conndemo/demo@test1

已毗连。

SQL>selectusername,privilegefromuser_sys_privs;

USERNAMEPRIVILEGE

------------------------------

DEMOCREATEANYINDEX

DEMOGLOBALQUERYREWRITE

DEMOUNLIMITEDTABLESPACE

PUBLICSELECTANYTABLE

已用工夫:00:00:00.00

SQL>selectusername,granted_rolefromuser_role_privs;

USERNAMEGRANTED_ROLE

----------------------------------------

DEMOCONNECT

DEMORESOURCE

PUBLICPLUSTRACE

已用工夫:00:00:00.00

再修正体系参数，将query_rewrite_enabled设置为true，这个参数是静态参数，设置后能够有吹糠见米的效果：

SQL>conn/@test1assysdba

已毗连。

SQL>showparameterquery

NAMETYPEVALUE

-----------------------------------------------------------------------------

query_rewrite_enabledstringFALSE

query_rewrite_integritystringenforced

SQL>altersystemsetquery_rewrite_enabled=true;

体系已变动。

已用工夫:00:00:00.00

SQL>showparameterquery

NAMETYPEVALUE

-----------------------------------------------------------------------------

query_rewrite_enabledstringTRUE

query_rewrite_integritystringenforced

好了，再利用demo用户上岸，创立函数索引

SQL>conndemo/demo@test1

已毗连。

SQL>createindexdept_id5ondept(substr(dname,1,5));

索引已创立。

已用工夫:00:00:00.00

SQL>selectindex_type,index_namefromuser_indexeswheretable_name=DEPT;

INDEX_TYPEINDEX_NAME

---------------------------------------------------------

FUNCTION-BASEDNORMALDEPT_ID5

已用工夫:00:00:00.00

可见已创立乐成了。

上面，我们看看查询是不是会利用我们创立的函数索引：

SQL>setautotracetraceonly

SQL>select*fromdeptwheresubstr(dname,1,5)=aaa;

未选定行

已用工夫:00:00:00.00

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(FULL)OFDEPT

Statistics

----------------------------------------------------------

29recursivecalls

0dbblockgets

10consistentgets

0physicalreads

0redosize

323bytessentviaSQL*Nettoclient

372bytesreceivedviaSQL*Netfromclient

1SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

0rowsprocessed

在我们剖析表以后，我们看到，查询如我们所但愿的那样，利用了索引。

SQL>analyzetabledeptcomputestatistics

2fortable

3forallindexes

4forallindexedcolumns;

表已剖析。

已用工夫:00:00:00.02

SQL>select*fromdeptwheresubstr(dname,1,5)=aaa;

未选定行

已用工夫:00:00:00.02

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=23)

10TABLEACCESS(BYINDEXROWID)OFDEPT(Cost=2Card=1Byt

es=23)

21INDEX(RANGESCAN)OFDEPT_ID5(NON-UNIQUE)(Cost=1Ca

rd=1)

Statistics

----------------------------------------------------------

0recursivecalls

0dbblockgets

1consistentgets

0physicalreads

0redosize

323bytessentviaSQL*Nettoclient

372bytesreceivedviaSQL*Netfromclient

1SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

0rowsprocessed

SQL>

经由过程一切的statistics，我们能够分明的看到，得当的利用索引会是功能进步几倍乃至更多。
MySQL的低成本来自于其简单性吗?它的普及性是由于其低成本吗?其实，在MySQL的最“好”与最“不好”的功能之间没有明显的分界线，但它们组合在一起就形成了一副让我们欣赏的作品。

作者: 老尸 时间: 2015-1-19 16:10
入门没那么困难，精通没那么容易

作者: 兰色精灵 时间: 2015-1-26 10:10
varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。

作者: 深爱那片海 时间: 2015-2-4 14:14
比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。

作者: 飘飘悠悠 时间: 2015-2-10 01:56
两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

作者: 飘灵儿 时间: 2015-2-28 15:11
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 若相依 时间: 2015-3-10 00:46
从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

作者: 小魔女 时间: 2015-3-17 03:33
对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

作者: 小女巫 时间: 2015-3-23 18:13
总感觉自己还是不会SQL

欢迎光临仓酷云 (http://ckuyun.com/)