本文共 3037 字，大约阅读时间需要 10 分钟。
数据库查询优化是一项复杂而重要的任务，直接关系到系统性能和用户体验。以下是一些实用的查询优化技巧，帮助你更高效地与数据库交互。
在优化查询时，应尽量避免全表扫描。首先，应在where及order by涉及的列上建立索引。索引能够显著提升查询效率，但其选择性至关重要。如果索引字段的值分布不均匀，索引对查询效率的帮助会大打折扣。例如，如果sex字段值接近于随机分布，索引对查询几乎没有帮助。
在where子句中进行null值判断会导致引擎放弃使用索引而进行全表扫描。例如，select id from t where num is null可以通过在num上设置默认值0，确保表中num列没有null值，然后改为select id from t where num=0来执行查询。这种方法不仅避免了全表扫描，还提高了查询效率。
在where子句中使用!=或<>操作符会导致引擎放弃使用索引而进行全表扫描。例如，select id from t where num != 10会使系统忽略可能的索引优化。为了避免这一问题，可以考虑使用not in或其他更高效的方式来表达条件。
在where子句中使用or连接条件也会导致索引失效，从而进行全表扫描。例如，select id from t where num=10 or num=20可以通过使用union all将其拆分为两个独立查询来优化：select id from t where num=10 union all select id from t where num=20。
对于连续的数值，使用between比in更高效。例如，select id from t where num in(1,2,3)可以改为select id from t where num between 1 and 3。这种方法不仅更高效，还更易于阅读和维护。
对于select id from t where name like ‘%李%’这样的查询，可以考虑使用全文检索技术来提高效率。全文检索能够更快地找到包含特定子字符串的记录，减少对索引的依赖。
在where子句中使用参数化查询会导致全表扫描，因为优化程序无法在编译时知道参数的具体值。例如，select id from t where num=@num会进行全表扫描。为了避免这一问题，可以使用with(index(索引名))强制使用索引。
在where子句中对字段进行表达式操作会导致索引失效。例如，select id from t where num/2=100可以改为select id from t where num=100*2。这种方法不仅避免了全表扫描，还提高了查询效率。
在where子句中对字段进行函数操作也会导致索引失效。例如，select id from t where substring(name,1,3)=’abc’可以通过使用like 'abc%'来优化为select id from t where name like ‘abc%’。
在where子句中对字段进行复杂表达式操作会导致索引失效。例如，select id from t where num=100*2应避免使用num/2=100这样的表达式。
如果使用复合索引，必须确保查询条件使用到索引中的第一个字段。例如，如果索引是(col1, col2),则where col1 = 5才能保证索引被使用。同时，字段顺序应与索引顺序保持一致，以确保查询效率。
不要执行不会返回任何结果的查询，如select col1,col2 from t where 1=0。这种查询会消耗系统资源，而不会返回任何数据。建议使用create table #t(...)来创建临时表。
在某些情况下，使用exists代替in更高效。例如，select num from a where num in(select num from b)可以改为select num from a where exists(select 1 from b where num=a.num)。
索引并不是越多越好。索引能够提高select效率，但会降低insert和update效率。因此，应根据具体情况决定索引数量，避免过多索引。
如果频繁更新clustered索引数据列，可能需要考虑不使用clustered索引。clustered索引的顺序决定了表记录的物理顺序，一旦该列值改变会导致表记录重新排序，耗费大量资源。
尽量使用数字型字段，避免将只含数值信息的字段设计为字符型。数字型字段在查询和连接时效率更高，也减少了存储开销。
尽可能使用varchar/nvarchar代替char/nchar，因为变长字段存储空间更小，且在查询时搜索效率更高。
避免使用select * from t，而是明确列出需要的字段。这样可以减少不必要的数据传输和处理开销。
使用表变量代替临时表，但要注意表变量索引有限（只有主键索引）。对于一次性事件，建议使用导出表。
避免频繁创建和删除临时表，以减少系统表资源消耗。临时表并不是不可使用，适当使用它们可以提高效率，但要注意数据量大小。
在存储程序和触发器开始处设置set nocount on，结束时设置set nocount off。避免向客户端返回大数据量，确保数据量与需求合理。
在使用游标或临时表之前，先尝试基于集的解决方案。基于集的方法通常效率更高，特别是在处理多个表时。
使用fast_forward游标处理小型数据集通常更高效。对于一次性事件，建议使用导出表而不是临时表。
在存储过程末尾，先truncate table再drop table，以避免系统表的长时间锁定。
游标效率较差，尤其是在处理超过1万行数据时，建议改用基于集的方法。
在使用游标或临时表之前，优先寻找基于集的解决方案。基于集的方法通常更高效。
对于小型数据集，使用fast_forward游标通常优于其他逐行处理方法。基于游标的方法在必须引用多个表时效率较高，但对于结果集中包含“合计”的场景，基于集的方法速度更快。
在存储程序和触发器开始处设置set nocount on，结束时设置set nocount off。无需向客户端发送done_in_proc消息。
避免频繁的大事务操作，以提高系统并发能力。
尽量避免向客户端返回大数据量，确保数据量与需求合理。
转载地址：http://imbfk.baihongyu.com/