【数据库】查询优化总结

一、合理使用索引

使用原则:

1、经常在查询中作为条件被使用的列,应为其建立索引。

2、频繁进行排序或分组group by 或 order by 操作的列,应为其建立索引。

3、一个列值域很大时,应为其建立索引。

4、如果待排序的列有多个,应在这些列上建立符合索引。

5、参与了连接操作的属性

6、在某一范围内频繁搜索的属性和按照排序顺序平凡检索的属性

7、在where子句中包含的一个关系的多个属性,可以考虑在这些属性上建立多属性索引。

此外,如果数据库文件需要频繁执行精确匹配查询(如等值查询),可考虑建立散列索引。而B+数等有序索引更适合范围查询。


二、避免或简化排序

在运行Order by或 Group by的SQL语句时,会涉及排序的操作,应当简化或避免对大型表进行重复的排序,因为排序磁盘的开销是很大的。降低数据库性能,而且磁盘排序会消耗临时表空间中的资源。

当能够利用索引自动以适当次序产生输出时,优化器就可以避免不必要的排序操作。以下是一些影响因素:

1、由于现有索引的不足,导致排序时索引中不包含一个或几个待排序的列。

2、group by 或order by子句中列的次序与索引的次序不一样。

为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,当相对于效率的提高还是值得的)。如果排序不可避免,那么应带试图简化它,如缩小排序列的范围等。


三、消除对大型表行数据的顺序存取

在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。避免这种情况的主要方法就是对连接的列进行索引。还可以使用并集来避免顺序存取。尽管在所有的检查裂伤都有索引,但某些形式的where子句强迫优化器使用顺序索引。


四、避免相关子查询

一个列属性同时在主查询和子查询中同时出现,那么很可能当住查询中的列值改变之后,子查询必须重新查询一次。查询嵌套次数越多,效率越低,因此应当尽量避免子查询。

如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。


五、避免困难的增则表达式

避免含MATCHES和LIKE关键字的增则表达式等。


六、使用临时表加速查询

把一个表的自己进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。临时表中的行比主表中的行要少,而且顺序就是所要求的顺序,减少了磁盘的I/O操作,所以查询工作量可以得到大幅减少。


七、用排序来取代非顺序磁盘存取

非顺序磁盘存取是最慢的操作。但是在写SQL语句时往往忽略了这一点,是的在写应用程序时很容易写出要求存取大量非顺序页的查询,导致效率的降低。有些时候,可以使用以数据库排序功能为基础的SQL来替代非顺序的存取,以改进查询效率。


八、不充分的连接操作

左(右)连接消耗的资源非常之多,因为它们包含与NULL(不存在)数据匹配的数据,其代价可能非常高。左(右)连接比内连接消耗资源更多,所以如果可以重新编写查询,使得该查询不使用左(右)连接,则会得到非常可观的回报。


九、存储过程

平时每次向数据库发送的SQL脚本,都需要先编译后执行。这样当使用相同的语句时效率就会低很多,而存储过程则不需要编译就能直接执行,因此速度可能更快。所以对于平凡使用的SQL语句建议使用存储过程。另外,要注意存储过程中尽量使用SQ自带的返回参数,而非自定义的返回参数,减少不必要的参数,避免数据冗余。


十、不要随意使用游标

游标会占用较多的系统资源,尤其是对于大规模并发量的情况下,很容易使得系统资源耗尽而崩溃。所以不要随意使用游标,而且游标使用完成后应及时关闭和销毁,以释放资源。


十一、事务处理

为了保证同时操作多个表而保证数据库的一致性,往往会用到事务。但是一旦将多个处理放入事务当中,系统的处理速度会有所降低,所以应当在保证书屋一致性的前提下,将频繁操作的多个可分割的处理过程放入到多个存储过程当中,这样会大大提高系统的响应速度。








【数据库】查询优化总结

上一篇:sqlalchemy


下一篇:SQL求职总结