编写高性能SQL的注意事项

 

在数据库部分，对数据库应用性能改进来说，需要重点关注应用程序，在查询设计和索引策略等方面进行优化，甚至可以把数据库查询效率提高数百倍，在其他方面的优化努力，其效果就没有这么明显（见下图）。本文重点描述在应用程序中进行数据库查询时，在设计和使用索引、设计查询语句等方面的注意事项，以取得良好的数据库查询性能。

一、索引设计和使用策略

使用索引是数据库减少磁盘I/O最有效的方法。除了在表中数据量非常少和需要返回表中大部分行的情况，正确使用索引的查询性能比全表扫描要高得多，有时简单新建一个合适的索引，就能把查询性能提高几十倍，在查询语句出现性能问题时，通常第一时间都会检查是否正确设计和使用了索引。

但是，事物都是有两面性的，索引的建立和维护需要消耗额外的CPU时间、额外的内存和存储空间，在对数据库表进行insert、update、delete操作时，都需要同时维护该表的所有索引，如果索引设计不当，不但不会提高性能，反而会降低性能。

遵循以下原则，可以让我们充分利用索引带来的性能方面的提升。

序号	索引设计和使用原则	解释说明
1	提高索引的区别能力，在数据基数大、重复值少的列上建索引，只要有可能，就使用create unique Index语句定义主键和唯一键	区分度越高，查找越精确，效率越高，尽量避免在有很多重复值（包括空值）的列上建索引
2	尽量选择唯一、最小、不可Null、容易获得、不常变更的列作为主键
3	在where子句中最频繁用到的列上建索引
4	在有distinct、min、max、order by、group by操作的列和join连接列上建索引	可降低查找成本和排序成本
5	避免创建冗余的类似索引，如存在a，b，c三列上的索引，那么a，b两列上的索引就是冗余的	冗余索引没有作用，只会增加额外的开销
6	索引中列的排列顺序需要考虑列唯一性，列宽度，列数据类型和查询使用频度。具有唯一性、列宽度小的、整型的、查询中引用最多的列排在前面	唯一性提高区分度，列宽小减少空间占用，列数据类型影响排序效率
7	尽量避免在一个索引中使用多于5个的列
8	对一个表，不要创建太多索引。通常，对联机事务处理环境（OLTP），不超过3个；对联机分析处理（OLAP）只读查询环境，可超过5个；混合查询和OLTP环境，2~5个为宜
9	尽量使用完全索引操作，即查询的所有列都在索引列中，如果需要的字段与排序无关，但查询时经常用到，可在create index时使用include子句将该字段包含在索引页中，但不作为索引字段使用	完全索引操作只访问索引，不需要访问数据表，可大幅度降低查询时间
10	在外键上建索引可提高父表update和delete操作的效率
11	在需要批量处理一定范围的数据或读取事先排序好的数据时，可使用聚簇索引（Cluster Index）	批量处理操作时比较适用
12	不要在频繁更新的列、宽度较大的列上建立聚簇索引	所有非聚簇索引将以聚簇键作为定位器，这两种情况将导致非聚簇索引更新效率下降
13	不要在有大量并行顺序插入操作的情况下使用聚簇索引，或使用其他列上的索引打乱插入顺序，将插入操作分布到整个表	如果按聚簇键批量插入，插入操作集中在表的最后一个页面，将形成“热点”，I/O效率非常低
14	首先创建聚簇索引，再创建非聚簇索引，避免因建立聚簇索引导致所有非聚簇索引重建的情况
15	重建聚簇索引时使用Drop_Existing选项，避免重建聚簇索引导致所有非聚簇索引重建的情况	适用于SQL Server、MySQL
16	在对超大字段进行查找，或对大量数据使用like ‘%...%’进行模糊查询时，使用全文索引

 

二、SQL语句编写的注意事项

数据库系统中，索引、碎片、统计信息和锁等很多因素都会影响性能，对这些特性进行优化都可能提高查询语句的执行效率。但是，如果SQL语句编写不合理，就可能大幅度增加本可避免的开销。

以下是编写SQL语句需要注意的一些事项：

1. 建立并遵循SQL编码规范，SQL语句应简洁、易读、便于维护
2. 为提高查询性能，应限制操作的数据量，尽量在小结果集上操作，减少行数和列数

²  不要使用“select * from ……”返回所有列，只检索需要的列，可避免后续因表结构变化导致的不必要的程序修改，还可降低额外消耗的资源

²  不要检索已知的列，如select  cust_no, cust_name from CustInfo where cust_no = ‘10000050’

²  使用高选择性的where子句，让数据库返回必须的数据集，而不是返回大量数据给应用程序，再由应用程序进行筛选，返回大数据集的代价很高

1. 有效地使用索引：where子句引用的列决定了索引的使用，需要重点关注

²  Where子句中列的顺序与需使用的索引顺序保持一致，不是所有数据库的优化器都能对此顺序进行优化，保持良好编程习惯

²  使用可参数化的搜索条件，如=, >, >=, <, <=, between, in, is null以及like ‘<literal>%’

²  尽量不要使用非参数化的负向查询，这将导致无法使用索引，如<>, !=, !>, !<, not in, not like, not exists, not between, is not null, like ‘%<literal>’

²  不要在where子句中对字段进行运算，如where  amount / 2 > 100，即使amount字段有索引，也无法使用，改成where amount > 100 * 2就可使用amount列上的索引

²  不要在where子句中对字段使用函数，如where substring( Lastname, 1, 1) = ‘F’就无法使用Lastname列上的索引，而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname < ‘G’就可以

²  小心使用or操作，and操作中任何一个子句可使用索引都会提高查询性能，但是or条件中任何一个不能使用索引，都将导致查询性能下降，如where member_no = 1 or provider_no = 1，在member_no或provider_no任何一个字段上没有索引，都将导致表扫描或聚簇索引扫描

²  Between一般比in/or高效得多，如果能在between和in/or条件中选择，那么始终选择between条件，并用>=和<=条件组合替代between子句，因为不是所有数据库的优化器都能把between子句改写为>=和<=条件组合，如果不能改写将导致无法使用索引

1. 分页显示的处理：在有大量满足条件的结果集，但界面上需要分页展示的情况，最好的方法是限制数据库每次返回的记录数，如每页展示50条记录，可使用select …… fetch first 50 rows only
2. 避免不必要的资源开销：

²  尽量用相同数据类型进行比较，避免发生数据类型转换，如select ‘5678’+3

²  在有min、max、distinct、order by、group by操作的列上建索引，避免额外的排序开销

²  当需要验证是否有符合条件的记录时，使用exists，不要使用count(*)，前者在第一个匹配记录处返回，后者需要遍历所有匹配记录

²  如果参与union子句的select结果集互斥或者允许结果集中有重复记录，可使用union all代替union，可避免检测和删除重复记录的开销

²  尽量使用单条语句完成操作，如多条单笔insert语句可以合并为一条多笔insert语句，使用case子句等

²  当不需要更新数据时，使用select……for read only或fetch read only，可以提高查询性能

1. 降低事务开销

²  使用明确的事务控制语句来控制事务的开始和commit/rollback，将原子性扩展到整个事务，否则，每个语句都是一个原子操作，日志开销大

²  及时commit，如果有insert、update和delete操作没有commit，数据上的锁会一直保持，及时commit有利于尽快释放锁，可提高并发能力，尤其是在批量操作时，分段commit还有利于出错时的重新处理，可从最后一次commit操作继续处理，如果一直没有commit，就必须从头开始了

²  尽量保持短事务，尽量不要在事务中处理与数据库操作无关的工作，如群发邮件等

²  使用insert into……select …… from ……等块操作方式，可大幅度降低日志开销

²  根据业务逻辑使用最低级别的隔离级别，降低锁开销

1. 使用select……for update保护后续update语句中可能被修改的那些行，可有效避免最常见的死锁情况
2. 使用数据库的参照完整性约束，如Not Null约束和主/外键约束，它们不仅能保证数据完整性，还能帮助优化器生成更高效的执行计划
3. 调整join操作顺序以使性能最优，join操作是自顶向下的，如select a.col1, b.col2, c.col3 from a left join b on a.col4 = b.col5

Join c on a.col4 = c.col6

    操作顺序是a和b进行left join，其结果再与c进行join，如果a与c进行join的结果集比较小，则应调整上述语句顺序，先进行a与c的join操作，再与b进行left join，可提高查询性能

 

三、其他注意事项

除了有效使用索引，编写高效SQL语句外，在提高查询性能方面，从应用程序编写角度还有以下内容需要注意：

1. SQL语句是一种处理结果集（多行、多列）的高效语句，而游标（cursor）是每次处理一行的技术，效率要低得多，如非必要，不要使用游标
2. 如果使用游标，使用代价最低的游标，并尽快释放
3. 多使用动态SQL：静态SQL语句的执行计划是在应用程序编译时生成的，即使后续数据库数据发生了重大变化，原有执行计划变得非常低效，只要应用程序没有重新编译，数据库就不会调整、优化其执行计划。而动态SQL语句虽然需要在每次执行时编译生成执行计划，但每次都是根据最新统计数据生成的优化执行计划，可得到较好的查询性能
4. 使用存储过程：对于不需要在用户界面上展示，但需要与数据库进行多次、大量数据交互的情况，最好的选择就是使用存储过程，可以减少网络通讯次数，降低网络流量，而存储过程已经过预编译，其执行速度也很快
5. 在高并发环境下，使用数据库连接池，可大幅度降低建立连接和关闭连接的开销

 

实际上，要想得到一个高性能的数据库应用，不是一蹴而就、一劳永逸的，除了在数据库设计之初就要充分考虑业务特性和数据特性，做好物理设计（硬件选择，尤其是存储系统的选择和设计）和逻辑设计，编制高效的应用程序外，还需要与数据库管理员充分配合，在运行过程中，持续跟踪数据库状况，不定期对索引和数据碎片整理（ReOrg），对统计信息进行更新，对应用进行持续优化，才能让数据库应用系统一直保持良好的性能。