仓酷云
标题:
MYSQL网站制作之利用索引的误区之一：没有利用复合索引...
[打印本页]
作者:
因胸联盟
时间:
2015-1-16 22:31
标题:
MYSQL网站制作之利用索引的误区之一：没有利用复合索引...
表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。索引利用索引的误区之一：没有利用复合索引的前导列招致查询不利用索引
在oracle中，我们常常觉得创建了索引，sql查询的时分就会如我们所但愿的那样利用索引，现实上，oracle只会在必定前提下利用索引，这里我们总结数第一点：oracle会在前提中包括了前导列时利用索引，即查询前提中必需利用索引中的第一个列，请看上面的例子
SQL>select*fromtab;
TNAMETABTYPECLUSTERID
-----------------------------------------------
BONUSTABLE
DEPTTABLE
DUMMYTABLE
EMPTABLE
SALGRADETABLE
创建一个团结索引（注重复合索引的索引列按次）
SQL>createindexemp_id1onemp(empno,ename,deptno);
Indexcreated
创建一个单键索引
SQL>createindexemp_id2onemp(sal);
Indexcreated
SQL>selecttable_name,index_namefromuser_indexes
2wheretable_name=EMP;
TABLE_NAMEINDEX_NAME
------------------------------------------------------------
EMPEMP_ID1
EMPEMP_ID2
SQL>SELECT*FROMUSER_IND_COLUMNS
2/
INDEX_NAMETABLE_NAMECOLUMN_NAMECOLUMN_POSITIONCOLUMN_LENGTHCHAR_LENGTHDESCEND
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMP_ID1EMPEMPNO1220ASC
EMP_ID1EMPENAME21010ASC
EMP_ID1EMPDEPTNO3220ASC
EMP_ID2EMPSAL1220ASC
上面的查询因为没有利用到复合索引的前导列，以是没有利用索引
selectjob,empnofromempwhereename=RICH;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|*1|TABLEACCESSFULL|EMP||||
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("EMP"."ENAME"=RICH)
Note:rulebasedoptimization
14rowsselected
上面的查询也因为没有利用到复合索引的前导列，以是没有利用索引
selectjob,empnofromempwheredeptno=30;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|*1|TABLEACCESSFULL|EMP||||
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("EMP"."DEPTNO"=30)
Note:rulebasedoptimization
14rowsselected
上面的查询利用了复合索引中的前导列，以是查询走索引了
selectjob,empnofromempwhereempno=7777;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|1|TABLEACCESSBYINDEXROWID|EMP||||
|*2|INDEXRANGESCAN|EMP_ID1||||
---------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("EMP"."EMPNO"=7777)
Note:rulebasedoptimization
15rowsselected
上面的查询利用了复合索引中的第一列和第二列，以是查询走索引了
selectjob,empnofromempwhereempno=7777andename=RICH;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|1|TABLEACCESSBYINDEXROWID|EMP||||
|*2|INDEXRANGESCAN|EMP_ID1||||
---------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("EMP"."EMPNO"=7777AND"EMP"."ENAME"=RICH)
Note:rulebasedoptimization
15rowsselected
利用了复合索引的全体列，以是走索引了，别的因为选了了索引中没有包括的列（job），
以是举行索引全表扫描失掉满意前提的rowid后，还要到表中检索响应的行
selectjob,empnofromempwhereempno=7777andename=RICHanddeptno=30;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|1|TABLEACCESSBYINDEXROWID|EMP||||
|*2|INDEXRANGESCAN|EMP_ID1||||
---------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("EMP"."EMPNO"=7777AND"EMP"."ENAME"=RICHAND"EMP"."DEP
TNO"=30)
Note:rulebasedoptimization
16rowsselected
利用了复合索引的全体列，以是走索引了，并且因为一切选择的列都包括在索引中，以是仅仅举行了索引局限扫描
selectempnofromempwhereempno=7777andename=RICHanddeptno=30;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|*1|INDEXRANGESCAN|EMP_ID1||||
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("EMP"."EMPNO"=7777AND"EMP"."ENAME"=RICHAND"EM
P"."DEPTNO"=30)
Note:rulebasedoptimization
15rowsselected
如果你需要额外的功能的话，MySQL的普及性实际上可以让你发现总有一个厂商会提供准确的解决方案，而这个方案会满足你的需要和需求。
作者:
若天明
时间:
2015-1-19 16:09
这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？
作者:
第二个灵魂
时间:
2015-1-28 07:52
一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）
作者:
活着的死人
时间:
2015-2-5 19:41
你可以简单地认为适合的就是好，不适合就是不好。
作者:
透明
时间:
2015-2-13 06:51
分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。
作者:
莫相离
时间:
2015-3-3 18:09
我们学到了什么？思考问题的时候从表的角度来思考问
作者:
蒙在股里
时间:
2015-3-11 12:23
学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!
作者:
不帅
时间:
2015-3-18 13:20
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。
欢迎光临 仓酷云 (http://ckuyun.com/)
Powered by Discuz! X3.2