【数据库】查询优化总结

一、合理使用索引 使用原则： 1、经常在查询中作为条件被使用的列，应为其建立索引。 2、频繁进行排序或分组group by 或 order by 操作的列，应为其建立索引。 3、一个列域很大时，应为其建立索引。 4、如果待排序的列有多个，应在这些列上建立符合索引。 5

一、合理使用索引

使用原则：

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自带的返回参数，而非自定义的返回参数，减少不必要的参数，避免数据冗余。

十、不要随意使用游标

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

十一、事务处理

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