用好SELECT索引 提高MySQL查询统计速度[MySQL防范]

数据库系统是管理信息系统的核心,基于数据库的联机事件处理（OLTP）以及联机解析处理(OLAP)是银行、企业、政府等部门最为重要的计算机利用之一.从大大都系统的利用实例来看,查询操作在各种数据库操作中所占据的比重最大,而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句.

举例来说,假如数据的量堆集到一定的程度,比方一个银行的账户数据库表信息堆集到上百万乃至上千万条记录,全表扫描一次常常需求数非常钟,乃至数小时.假如采取比全表扫描更好的查询战略,常常可以使查询时间降为几分钟,由此可见查询优化技术的重要性笔者在利用项目的实施中发现,很多程序员在操纵一些前端数据库开辟工具（如PowerBuilder、Delphi等）开辟数据库利用程序时,只注重用户界面的富丽,并不器重查询语句的效率问题,招致所开辟出来的利用系统效率低下,资源浪费严重.因此,若何计划高效公道的查询语句就显得非常重要.本文以利用实例为底子,结合数据库理论,介绍查询优化技术在实际系统中的应用.

解析问题

很多程序员认为查询优化是DBMS（数据库管理系统）的任务,与程序员所编写的SQL语句关系不大,这是错误的.一个好的查询筹划常常可以使程序性能提高数十倍.查询筹划是用户所提交的SQL语句的调集,查询筹划是经过优化处理之后所产生的语句调集.DBMS处理查询筹划的历程是这样的:在做完查询语句的词法、语法查抄之后,将语句提交给DBMS的查询优化器,优化器做完代数优化和存取途径的优化之后,由预编译模块对语句举行处理并生成查询筹划,然后在符合的时间提交给系统处理履行,最后将履行后果返回给用户.在实际的数据库产品(如Oracle、Sybase等)的高版本中都是采取基于代价的优化办法,这种优化能按照从系统字典表所得到的信息来预计差别的查询筹划的代价,然后挑选一个较优的筹划.固然目前的数据库产品在查询优化方面已经做得越来越好,但由用户提交的SQL语句是系统优化的底子,很难假想一个本来糟糕的查询筹划经过系统的优化之后会变得高效,因此用户所写语句的优劣至关重要.系统所做查询优化我们暂不谈论,下面重点阐明改进用户查询筹划的办理筹划.

办理问题

下面以关系数据库系统Informix为例,介绍改进用户查询筹划的办法.

1．公道利用索引

索引是数据库中重要的数据构造,它的根本目的就是为了提高查询效率.目前大大都的数据库产品都采取IBM最早提出的ISAM索引构造.索引的利用要恰到好处,其利用原则以下:

◆在常常举行衔接,但是没有指定为外键的列上成立索引,而不常常衔接的字段则由优化器自动生成索引.

◆在频繁举行排序或分组（即举行group by或order by操作）的列上成立索引.

◆在条件表达式中常常用到的差别值较多的列上成立检索,在差别值少的列上不要成立索引.比方在雇员表的“性别”列上只有“男”与“女”两个差别值,因此就无必要成立索引.假如成立索引不但不会提高查询效率,反而会严重降低更新速度.

◆假如待排序的列有多个,可以在这些列上成立复合索引（compound index）.

◆利用系统工具.如Informix数据库有一个tbcheck工具,可以在可疑的索引上举行查抄.在一些数据库服务器上,索引大概失效大概因为频繁操作而使得读取效率降低,假如一个利用索引的查询不明不白地慢下来,可以试着用tbcheck工具查抄索引的完好性,必要时举行修复.别的,当数据库表更新大量数据后,删除并重建索引可以提高查询速度.

2．避免或简化排序

该当简化或避免对大型表举行反复的排序.当可以操纵索引自动以得当的次序产生输出时,优化器就避免了排序的步骤.以下是一些影响因素:

◆索引中不包含一个或几个待排序的列;

◆group by或order by子句中列的次序与索引的次序不一样;

◆排序的列来自差别的表.

为了避免不必要的排序,就要精确地增建索引,公道地归并数据库表（固然有时大概影响表的标准化,但相关于效率的提高是值得的）.假如排序不可避免,那么该当试图简化它,如缩小排序的列的范围等.

3．消除对大型表行数据的次序存取

在嵌套查询中,对表的次序存取对查询效率大概产生致命的影响.比方采取次序存取战略,一个嵌套3层的查询,假如每层都查询1000行,那么这个查询就要查询10亿行数据.避免这种情形的主要办法就是对衔接的摆列行索引.比方,两个表:学生表（学号、姓名、年纪……）和选课表（学号、课程号、成就）.假如两个表要做衔接,就要在“学号”这个衔接字段上成立索引.

还可以利用并集来避免次序存取.固然在全部的查抄列上都有索引,但某些情势的where子句逼迫优化器利用次序存取.下面的查询将逼迫对orders表履行次序操作:

SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 

固然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是利用次序存取途径扫描整个表.因为这个语句要检索的是别离的行的调集,所以应当改成以下语句:

SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 
UNION 
SELECT ＊ FROM orders WHERE order_num=1008 

这样就可以操纵索引途径处理查询.

4．避免相关子查询

一个列的标签同时在主查询和where子句中的查询中呈现,那么极大概当主查询中的列值改变之后,子查询必须重新查询一次.查询嵌套层次越多,效率越低,因此该当尽大概避免子查询.假如子查询不可避免,那么要在子查询中过滤掉尽大概多的行.

5．避免艰难的正规表达式

MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式.但这种匹配分外耗费时间.比方:

SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _” 

即便在zipcode字段上成立了索引,在这种情形下也还是采取次序扫描的方法.假如把语句改成SELECT ＊ FROM customer WHERE zipcode >“98000”,在履行查询时就会操纵索引来查询,明显会大大提高速度.

别的,还要避免非开始的子串.比方语句:SELECT ＊ FROM customer WHERE zipcode[2,3]>“80”,在where子句中采取了非开始子串,因而这个语句也不会利用索引.

6．利用暂时表加快查询

把表的一个子集举行排序并成立暂时表,有时能加快查询.它有助于避免多重排序操作,并且在其他方面还能简化优化器的工作.比方:

SELECT cust.name,rcvbles.balance,……other columns 
FROM cust,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
AND cust.postcode>“98000” 
ORDER BY cust.name 

假如这个查询要被履行多次而不止一次,可以把全部未付款的客户找出来放在一个暂时文件中,并按客户的名字举行排序:

SELECT cust.name,rcvbles.balance,……other columns 
FROM cust,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
ORDER BY cust.name 
INTO TEMP cust_with_balance 

然后以下面的方法在暂时表中查询:

SELECT ＊ FROM cust_with_balance 
WHERE postcode>“98000” 

暂时表中的行要比主表中的行少,并且物理次序就是所要求的次序,削减了磁盘I/O,所以查询工作量可以得到大幅削减.

注意:暂时表成立后不会反映主表的改正.在主表中数据频繁改正的情形下,注意不要丧失数据.

7．用排序来代替非次序存取

非次序磁盘存取是最慢的操作,表目前磁盘存取臂的往复移动.SQL语句躲藏了这一情形,使得我们在写利用程序时很简单写出要求存取大量非次序页的查询.
有些时刻,用数据库的排序本领来替换非次序的存取能改良查询. 　　以上是“用好SELECT索引 提高MySQL查询统计速度[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：