浅谈MySQL中SQL优化的常用方法

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

2、尽量避免在那里 where 在句子中使用！=或<>操作符，否则发动机将放弃使用索引进行全表面扫描。

3、尽量避免在那里 where 字段在子句中进行 null 值判断，否则会导致发动机放弃使用索引进行全表面扫描，如：

select id from t where num is null

默认值0可以设置在num上，以确保表中的num列没有null值，然后查询：

select id from t where num=0

4、尽量避免在那里 where 子句中使用 or 连接条件，否则会导致发动机放弃使用索引进行全表面扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10union allselect id from t where num=20

5、下面的查询也会导致全表扫描:

select id from t where name like '%abc%'

为提高效率，可考虑全文检索。

6、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

7、如果在 where 子句中使用的参数也会导致整个表面扫描。由于SQL只在运行过程中分析局部变量，但优化程序不能将访问计划的选择推迟到运行过程中；它必须在编译过程中进行选择。然而，如果在编译过程中建立了访问计划，变量值仍然未知，因此不能作为索引选择的输入项。以下句子将扫描整个表：

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'--以abc开始的idname

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、当使用索引字段作为条件时，如果索引是复合索引，则必须使用索引中的第一个字段作为条件，以确保系统使用索引，否则索引不会使用，并尽可能使字段顺序与索引顺序一致。

12、如果需要生成一个空表结构，不要写一些毫无意义的查询：

select col1，col2 into #t from t where 1=0

此类代码不会返回任何结果集，但会消耗系统资源的，应改为：

create table #t(...)

13、很多时候用 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)

14、并非所有索引都对查询有效。SQL根据表中的数据进行查询和优化。当索引列中有大量数据重复时，SQL查询可能不会使用索引，例如，表中有字段SEX，male、female几乎是一半，所以即使在sex上建索引也不能起到查询效率的作用。

15、索引越多越好。虽然索引可以改进相应的索引 select 但也降低了效率，但也降低了效率 insert 及 update 因为 insert 或 update 索引有可能被重建，因此如何构建索引需要仔细考虑，这取决于具体情况。一个表的索引数最好不要超过6个。如果太多，应该考虑是否有必要在一些不常用的列上建造索引。

16、尽量避免更新 clustered 因为索引数据列 clustered 索引数据列的顺序是表记录的物理存储顺序。一旦列值发生变化，整个表记录的顺序将被调整，这将消耗相当多的资源。如果应用系统需要频繁更新 clustered 需要考虑索引数据列是否应该构建索引 clustered 索引。

17、尽量使用数字字段。如果只包含值信息的字段尽量不设计为字符类型，这将降低查询和连接的性能，并增加存储成本。这是因为引擎在处理查询和连接时逐个比较字符串中的每个字符，而对于数字类型，只需要比较一次。

18、尽可能多地使用 varchar/nvarchar 代替 char/nchar ，首先，长字段存储空间小，可以节省存储空间。其次，对于查询，在相对较小的字段中搜索效率明显较高。

19、不要在任何地方使用 select * from t ，用特定的字段列表代替“*”，不要返回任何无法使用的字段。

20、尽量用表变量代替临时表。若表变量包含大量数据，请注意索引非常有限(只有主键索引)。

21、为了减少系统表资源的消耗，避免频繁创建和删除临时表。

22、临时表并非不可用。适当使用它们可以使一些例程更有效。例如，当需要在大型表或常用表中重复引用数据集时。然而，对于一次性事件，最好使用导出表。

23、新建临时表时，如果一次性插入大量数据，可以使用 select into 代替 create table，避免造成大量 log ，为了提高速度；如果数据量不大，为了缓解系统表的资源，应首先进行createte table，然后insert。

24、如果使用临时表，则必须在存储过程结束时删除所有临时表显式。首先 truncate table ，然后 drop table ，这样可以避免系统表长时间锁定。

25、尽量避免使用游标，因为游标效率差。如果游标操作的数据超过1000行，则应考虑重写。

26、在使用基于游标或临时表的方法之前，首先要找到基于集的解决方案来解决问题，基于集的方法通常更有效。

27、和临时表一样，游标也不是不可用的。使用小数据集 FAST_FORWARD 游标通常优于其他逐行处理方法，特别是在必须引用几个表才能获得所需的数据时。结果集中包括“合计”例程通常比使用游标快。如果开发时间允许，可以尝试基于游标和集合的方法，看看哪种方法效果更好。

28、设置在所有存储过程和触发器的开始处 SET NOCOUNT ON ，设置在结束时 SET NOCOUNT OFF 。在执行存储过程和触发器的每个句子后，无需将其发送到客户端 DONE_IN_PROC 消息。

29、尽量避免将大数据返回客户端。如果数据量过大，应考虑相应的需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。