提升查询速度

作者：Laiznse Auzres

1、尽量少用负向条件查询

负向条件查询都是不能使用索引的，当表中的数据达到一定量级时，这个查询的效率会急剧的下降

2、在IN后面值得列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断次数

3、大表、热表要加上nolock

4、尽量避免在WHERE字句中使用逆向运算操作符，例如<>，否则引擎将放弃使用索引二进行全表扫描

5、对查询进行优化，应尽量避免全表扫描，首先考虑在WHERE及ORDER涉及的列上建立索引

6、尽量避免在WHERE字句中使用OR来连接条件，否则引擎将放弃使用索引而进行全表扫描

7、尽量不要使用前置百分号，可以吧LIKE '%%'放到临时表，再进行复杂表的关联

8、尽量避免在WHERE字句中对字段进行表达式操作

9、尽量避免在WHERE字句中对字段进行函数操作、这将导致引擎放弃使用索引而进行全表扫描

10、不要再WHERE字句中的=的左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引

11、使用EXISTS代替IN是好的选择，外表数据比内表数据大，用IN；反之用EXISTS。NOT IN 避免使用，可用NOT EXISTS代替

12、字段IS NOT NULL 改写为字段<>’’；

13、部分UPDATE、SELECT语句写得很复杂（经常嵌套多级子查询），可以考虑适当拆成几部，先生成一些临时数据表，再进行关联操作；

14、尽量避免在WHERE字句中使用OR来作为连接条件。尽可能将OR改成UNION ALL;

15、不要使用 SELECT * FROM 表，用具体的字段列表代替""，不要返回用不到的任何字段。使用，在SQL解析过程中，会将*一次转换成所有的列名，这个工作是通过查询系统数据字典完成的，这就意味着将耗费更多的时间

16、避免频繁创建和删除临时表，以减少系统表资源的消耗

17、在新建临时表时，如果一次性插入数据量很大，使用SELECT INTO代替CREATE TABLE，避免造成大量的LOG，以提高速度；如果数据量不大，为降低系统表的资源，应先CREATE TABLE,然后INSERT。

18、如果使用到了临时表，在存储过程的最后务必将所有的临时表显示删除，先TRUNCATE,然后DROP TABLE，这样可以避免系统表的长时间锁定；

19、尽量避免使用游标，如果游标操作的数据大于10000行，就应考虑改写。

20、在所有的报表存储过程开始处设置 SET NOCOUNT ON，无需在执行存储过程中的每个语句在客户端发送doneinproc的信息；

21、尽量避免向客户端反馈大数据量，若数据量过大，应该考虑需求是否合理；

22、尽量避免事务操作；

23、跨链接数据库取数据，尽量避免直接关联，尽量先取到临时表，然后再做关联

24、注意表之间连接的数据类型，尽量避免不同类型数据之间的连接，注意存储过程中参数和数据类型的关系

25、注意WHERE子句的写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小

26、如果使用了游标，就要尽量避免在游标循环中在进行表连接的操作

27、数据查询报表的WHERE条件后面尽量避免使用连续多个OR进行条件限制，可将通过OR条件过滤的结果集先行插入临时表，然后关联临时表进行检索

28、建议在业务高峰时段，尽量避免大批量数据查询，或者在报表中添加条件控制，设计主要业务表查询时，如在高峰时段，查询时提示业务高峰时段不能进行大批量数据查询

29、尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index_name来强制指定索引

30、在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用

转载QQ群友分享，感谢

1表8000万左右，2表100万左右的数据，效果0.5秒以内查出来你要的数据，并分页，注意，不是查一条

正常查询：select 字段 from a as a1 left join

select 字段 from a as a1 left join b as b1 on a1.id=b1.id and a1.op=b1.op limit 100000,20

这是个例子，如果正常这个查询大概耗时几秒钟，如果是limit 5000000,20，耗时可能大概20多秒，那么你给我优化一下，有思路吗

解答：
select * from tb a join (select id from tb limit 5000000, 100) b on a.id = b.id and a.op=b.op where 如果有条件写这里 order by a.id desc;

这个是优化后的，你把两个字段改一模一样的，然后模拟500万数据，看看，后一个查询是上一个的50倍以上

思路是先把2表的子集取出来，再和1表联合起来