sql调优的23种方案

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

2.应尽量避免在where子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

3.应该尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from table where num is null，可以在num上设置默认值0以确保表中num列没有null值，然后这样查询：select id from table where num = 0

4.在where子句中使用or作为连接条件时，如果or前的列和后面的列中有一个没有索引，那么涉及的索引都不会被用到。

5.下面的查询也将导致全表扫描：select id from t where name like ‘%abc%’，对于 like ‘…%’ (不以 % 开头)，则可以应用到 column 上的index。

6.下面的查询也将导致全表扫描：select id from t where n6.in 和 not in会导致全表扫描（in会走索引,但是当in的取值范围较大时会导致索引失效,走全表扫描），如：select id from t where num in(1,2,3,4…28…)。对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3，但是between…and…也可能让索引失效：当sql语句查询出来的数据量超过总数据量一定的比例( 30%左右)会使索引失效。

7.如果在 where 子句中使用形参变量，也会导致全表扫描。如下面语句将进行全表扫描：select id from t where num=@num可以改为查询使用强制索引：select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

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

10.不要在 where 子句中的“=”【左边】进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是【复合索引】，那么必须使用到该索引中的【第一个字段】作为条件时才能保证系统使用该索引，否则该索引将不会被使用。并且应【尽可能】的让字段顺序与索引顺序相一致。（字段顺序也可以不与索引顺序一致，但是一定要包含【第一个字段】）

13.很多时候用 exists 代替 in 是一个好的选择：

select * from a where id in(select id from b)

用下面的语句替换：

select * from a where exists(select 1 from b where id=a.id)

对于这样的sql查询同一个库，结果是一样的，但是对于不同情况查询速度差别较大，使用in时是先执行子查询再执行主查询，而使用exists时是先执行主查询再执行子查询。

对于主表数据较多时，使用in查询速度比exist更快；反之从表数据较多时，使用exists查询速度更快；都会走索引。

如果使用的是not in与not exists，请直接使用not exists，因为not in会进行全表扫描不走索引，而not exists会走索引。

14.索引并不是越多越好，索引固然可以提高 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常用到的列上建的索引是否必要

15.尽量使用数值型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数值型而言只需要比较一次就够了。

16.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

18.尽量用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。

MySQL表变量简介

​ MySQL表变量是MySQL数据库中一种特殊的变量类型，可以在查询中使用它们来模拟临时表。表变量是一种内存中的数据结构，可以存储和操作数据。在某些情况下，使用表变量可以提高查询性能并简化查询逻辑。

创建表变量

下面的代码示例演示了如何创建一个名为my_var的表变量，包含两个列id和name。

DECLARE my_var TABLE (
  id INT,
  name VARCHAR(255)
);

插入数据
一旦创建了表变量，我们可以使用INSERT INTO语句向其中插入数据。下面的代码示例演示了向my_var表变量中插入两行数据的方法。

INSERT INTO my_var (id, name) VALUES (1, ‘Alice’);
INSERT INTO my_var (id, name) VALUES (2, ‘Bob’);

表变量的优势
1.由于表变量是在内存中创建的，在查询中减少了对硬盘的I/O操作，因此对表变量的读写操作速度更快。

2.使用表变量还可以减少临时表的创建和销毁次数，从而减少了系统资源的消耗。

3.另一个优势是表变量可以提高查询的可读性和可重用性，简化查询逻辑：通过将计算和过滤操作应用于表变量，我们可以将复杂的查询逻辑分解为更易于理解和维护的部分。

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

20.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表（数据字典）的较长时间锁定。

注意！

drop table的时候数据库至少会做两件事：
1、删除表所占有的空间
2、在数据字典中删除表的信息
如果直接drop table，那么这两个步骤要保证一致，在同一个事务中完成，特别是数据量很大的表，这个过程会占用比较长的时间。如果先truncat table后drop table，
1、可以降低操作失败的风险
2、可以降低数据字典锁占用的时间，降低系统开销

21.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

**游标(cursor)**是一个存储在服务器上的数据库查询的结果集。游标可以在存储过程和自定义函数中使用，申明游标、打开游标、使用游标、关闭游标.

游标的作用就是对查询数据库所返回的结果集进行遍历，以便进行相应的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；正是游标把作为面向数据集的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够结合使用。

MySQL游标有下面这些属性：

• 游标是只读的，也就是不能更新它；
• 游标是单向的，也就是只能在一个方向上从开始往后进行遍历，不能从后往前，也不能跳过某些记录随意进退；
• 避免在已经打开游标的表上更新数据。

22.在所有存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF ，这样就无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

23.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。