SQL优化之--索引

《SQL优化之--索引》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、ORACLESQL优化——索引使用一：为什么引入索引索引的目的其实很简单，就是避免顺序的逐行的扫描，避免大量的磁盘输入输出，提高系统的效率。二：试验索引的作用表现一条SQL语句的好坏，大多数的情况下，我们一般观察该语句的执行效率，即得到结果所需的时间。但基于ORACLE的缓存、数据库的访问人数和硬件网络的繁忙与否等外界条件的影响。得到的结果并不令人满意。我在此采用的是利用TOAD的EXPLAINPLAN的cost来分析索引对SQL产生的影响。（1）.plan_table表（2）.ALTERSESSIONSETOPT

2、IMIZER_MODE=first_rows1前提条件：表：TEST_STU（30万条数据）STUIDSTUNAMECOURSENAME1404004NicholasChinese1404005CherryChemistryStuid：学生号Stuname：学生姓名Coursename：选修课程名TEST_COURSECOURSEIDCOURSENAMETEACHERID031Chinese54001032English54005Courseid：课程号Coursename：课程名Teacherid：代课教师编号T

3、EST_TEATEACHERIDTEACHERNAMESEX54001guorongM54002jingtaoMTeacherid：教师编号Teachername：教师名称Sex：性别2有无索引的条件下的运行情况2.1查询所有索引SELECTINDEX_NAME,TABLE_NAME,UNIQUENESSFROMUSER_INDEXES2.2清除索引DROPINDEXINDEX_NAME//适用于仅和本表有关联的索引ALTERTABLETABLE_NAMEDROPPRIMARYKEYCASCADE//用于删除主键（

4、主键也是一种索引）ALTERTABLETABLE_NAMEDROPUNIQUE(COL_NAME)//用于删除唯一索引ALTERTABLETEST_BALLCLUBDROPCONSTRAINTINDEX_NAME//用于删除外键引起的索引2.3分析SQL语句SQL>SELECT*FROMTEST_STU2WHERESTUNAME=’huping’EXPLAIN一下上面的语句,所消耗的cost为：115，115创建索引：CREATEINDEXSTUID_INDEXONTEST_STU(STUNAME)EXPLAIN一

5、下上面的语句，所消耗的cost为：2，2，1删除索引：DROPINDEXSTUID_INDEX效果是明显的，因为索引避免了全局扫面所花的不必要的时间和开资。SQL>SELECT*2FROMTEST_STUstu,3TEST_COURSEcor,4TEST_TEAtea5WHEREstu.coursename=cor.coursename6ANDcor.teacherid=tea.teacherid7ANDtea.teacherid=’54006’对ORDERBY的影响更大，例如，在最后加上“ORDERBYstuna

6、me”同样对GROPUBY也一样成立。COST:126,126,4,2,2,2,115在从分析表中，可以看到，COST主要消耗在了全局扫面TEST_STU表上了，所以，在TEST_STU的COURSENAME上添加一个索引CREATEINDEXstu_coursename_indexONTEST_STU(COURSENAME)DROPINDEXstu_coursename_indexSQL>SELECT*2FROMTEST_STU3WHEREstunamelike‘J%’CREATEINDEXstu_stuname

7、_indexONTEST_STU(STUNAME)DROPINDEXstu_stuname_index查看分析以上结果：对与具有insert和delete的SQL语句例如，TEST_STU的COURSENAME是TEST_COURSE的外键，而如果要删除TEST_COURSE的一条记录，那么，此时便会要对TEST_STU表进行扫描。查找与要删除行的相关有约束的行。进行级联删除或是其他处理。同样对于向TEST_STU表进行插入的时候，也要对TEST_COURSE的表进行扫描，这样如果没有索引的话，扫描就会采取顺序的全

8、表扫面，时间必然就会很长。从以上所得的结果，不难看出，索引从很大成度上减少了系统的成本。但索引也有其局限性，即他不是对所有的SQL语句都使用的。三：索引使用的限制3.1IN、OR子句常会使用工作表，使索引失效。例子如下:如果在fields1和fields2上同时建立了索引，fields1为主索引CREATEINDEXstu_stuname_indexONTE