开发者必备的SQL性能优化基本功

在互联网应用，特别是“动态”网站的应用中，随着网站访问量的增加，我们总是会发现性能的瓶颈最先来自数据IO，而非CPU、内存、网络带宽……而数据IO瓶颈一般表现在数据库查询上。

当然我们可以通过使用诸如 Memcache 之类的集群缓存方案替数据库“挡”那么一下，也可以考虑使用 HBase 之类的NoSQL解决方案分散数据存取。但是最经济、最快速的办法莫过于SQL语句优化了——几乎零成本且立杆见影。优化一条糟糕的查询，能轻松的提高千百倍的执行效率，而获得的结果不会有任何不同。

说出来也许都知道，但未必都能记得做到。所以，总结一下：

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上分别建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则会导致索引不命中而进行全表扫描，如：select id from t where num is null，可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0。
• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则会导致索引不命中而进行全表扫描。
• 应尽量避免在 where 子句中使用 or 来连接条件，否则会导致索引不命中而进行全表扫描。如：select id from t where num1=10 or num2=20。可以改为此查询：(select id from t where num1=10) union all (select id from t where num2=20)。
• in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)，对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3。
• 此查询也将导致全表扫描：select id from t where name like '%abc%'。对于模糊查询若要提高效率，可以考虑全文检索解决方案。
• 如果在 where 子句中使用参数（一般在存储过程中参数运用较多），也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如此语句将进行全表扫描：select id from t where num=@num，可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num。
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致索引不命中而进行全表扫描。如：select id from t where num/2=100，应改为：select id from t where num=100*2。
  即不要在 where 子句中的“=”左边进行函数、算术或其他表达式运算。
• 应尽量避免在 where 子句中对字段进行函数操作，这将导致索引不命中而进行全表扫描。如：
  

select id from t where substring(name,1,3)='abc'——name以abc开头的id
select id from t where datediff(day,createdate,'2010-11-30')=0——“2010-11-30”生成的id
可以改为:
select id from t where name like 'abc%'
select id from t where createdate>='2010-11-30' and createdate<'2010-12-1'

• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。应尽可能的让字段顺序与索引顺序相一致。
• 很多时候用 exists 或 join 代替 in 是一个好的选择，例如：select name from employers where id in (select employer_id from employment_periods)，用下面的语句替换：select name from employers where exists (select 1 from employment_periods where employer_id=employers.id) 或 select distinct name from employers join employment_periods on employers.id=employment_periods.employer_id，这样优化的目的是使“建立索引-命中索引”具有可操作性。
• 不要使用 select * from t ，用具体的字段名称代替 * ，不要返回用不到的任何字段，这能节省数据查询的系统和网络开销。

关于索引的总结：

1. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，例如表中有字段sex，sex的值为male、female，几乎各占一半，那么即使在字段sex上建了索引也对查询效率起不了提升作用。
2. 索引并不是越多越好，索引固然可以提高相应的查询效率，但同时也降低了插入和更新的效率，因为 insert 或 update 时有可能需要重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则需考虑其必要性了。
3. 应尽可能的避免更新 clustered 索引（聚集索引，比普通索引速度更快）数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

关于表设计：

1. 尽量使用数字型字段，只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并增加存储开销。这是因为数据库引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要一次比较就够了。
2. 尽量使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

关于临时表：

1. 尽量使用表变量来代替临时表。
2. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
3. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
4. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table ，避免造成大量Log，以提高效率；如果数据量不大，为了缓和系统表的资源，应先 create table ，然后 insert 。
5. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

关于游标：

1. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
2. 使用基于游标的方法或临时表方法之前，应先寻找基于结果集的解决方案，基于结果集的方法通常更有效率。
3. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。
4. 如果开发时间允许，基于游标的方法和基于结果集的方法都可以尝试一下，看哪一种方法的效果更好。

最后，应当尽量避免大事务操作（即避免长时间锁表），提高系统并发能力。