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的普及性实际上可以让你发现总有一个厂商会提供准确的解决方案，而这个方案会满足你的需要和需求。

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

我们学到了什么？思考问题的时候从表的角度来思考问

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

你可以简单地认为适合的就是好，不适合就是不好。

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？