常见SQL优化的一些方法

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

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

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

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    

　　select id from t where num=10 or num=20    
　　可以这样查询：    
　　select id from t where num=10    
　　union all    
　　select id from t where num=20    

5.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    

6.下面的查询也将导致全表扫描：    
　　select id from t where name like '%abc%'    

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

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：    
　　select id from t where substring(name,1,3)='abc'--name以abc开头的id    
　　应改为:    
　　select id from t where name like 'abc%'    

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

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

11.不要写一些没有意义的查询，如需要生成一个空表结构：    
　　select col1,col2 into #t from t where 1=0    
　　这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    
　　create table #t(...)    

12.很多时候用 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)    

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

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

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

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

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

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

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。    

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

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

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

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

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

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

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

 

 

改变 SQL 执行计划
明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。
尽量少 join
MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

尽量少排序
排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。
　　对于MySQL来说，减少排序有多种办法，比如：
　　通过利用索引来排序的方式进行优化
　　减少参与排序的记录条数
　　非必要不对数据进行排序
 

尽量避免select *
Select * 一般都会造成全表扫描，显示所有列，select 需要的字段即可。
尽量用 join 代替子查询
虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。　　
尽量用 union all 代替 union：
union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。
禁用外键

----------------------------------------------

 

SQL优化的基本原则

 

避免大sql
一个SQL只能在一个cpu上运行
高并发环境中，大SQL容易影响性能问题
可能一个大SQL把数据库搞死
拆分SQL
保持事物的短小精悍
即开即用，用完即关
无关操作踢出事务，减少资源占用
保持一致性的前提下，拆分事务
避免大批量更新
避开高峰
白天限制速度
加sleep
避免类型转换
避免取过量数据，建议使用limit
避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作
避免OR
同一字段，推荐in
不同字段，推荐union
优先优化高并发的 SQL，而不是执行频率低某些“大”SQL
从全局出发优化，而不是片面调整　
尽可能对每一条运行在数据库中的SQL进行explain。

-------------------------------------------------------------

索引的设计原则

1.搜索的索引列，不一定是所要选择的列，换句话说，最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。
2.使用唯一索引，考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。
3.使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。
4.利用最左前缀。在创建一个n 列的索引时，实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5.不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍 过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是，则就不要费力去增加这个索引了，因为已经有了。


-------------------------------------------------

 

主键索引，主键上会自动增加一个主键索引
唯一索引，数据是唯一的时候加上唯一索引，也就是唯一约束，使用唯一索引查询效率要比普通索引和多列索引速度快
单列索引（普通索引），任何列上都可添加
多列索引（组合索引），如果where字句后面有多列，或者数据有重复的，这样需要加多列索引，尽量避免对组合索引的列进行更新，因为更新数据后索引也会重新维护。
注意：修改表结构会，索引会失效，需要重构索引