sql优化的常用方法

首先建表要注意的：

1. 考虑在where和order by涉及的列上建立索引，避免全表扫描。
2. 当索引列有大量重复数据时，sql查询可能不会使用索引，所以建表时要考虑适合的索引。例如，性别sex
3. 某些列应设置默认值，避免where中null值判断，否则引擎会放弃使用索引查询而使用全表扫描。
   例： select id from student where sex is null
   可以给sex列设置默认值0，上面的语句可以优化为：
   select id from student where sex = 0
4. 索引不能太多，因为索引太过会影响insert和update的效率。一般不要超过6个。
5. 建表时不要使用不兼容的数据类型。例如：float、char等

写sql要注意的：

1. where中尽量避免使用不等号(!= <>)，否则引擎会放弃使用索引查询而使用全表扫描。
2. where中尽量避免or的使用，可以用union all来代替，否则引擎会放弃使用索引查询而使用全表扫描。
   例： select id from student where sex = 1 or sex = 2
   优化：
   select id from student where sex = 1 union all select id from student where sex = 2
3. where中like的使用，因为数值前有%会导致索引失效,尽量在数值前不要用%，如果要优化则考虑全文搜多。
   例：select id from student where name = ‘%小%’
   优化：
   select id from student where name = ‘李小%’
4. where中不要在’='左边进行运算(函数、算数、表达式等)，这会导致系统无法正确使用索引。
   例:select name from student where id/2 = 100
   优化:
   select name from student where id = 1002
   select id from student where substring(name,1,3) = ‘abc’
   优化：
   select id from student where name = ‘abc%’
5. in 和 not in 会导致全表扫描，所有要少用，如果是连续的数字用between代替，如果不是数据量小用in，数据量大用exists(走索引)。
   a. in是先子查询后主查询。
   b. exists是先执行主查询，再对主查询结果集的每一行执行一次子查询，用子查询的结果确定主查询的结果集(如果主查询查出100行数据则总共执行101次sql)
   exists的写法:select name from student where exists(select id from a where s_id=student.id)
   例：select name from student where id in (888,889,900)
   优化：
   select name from student where id between 888 and 900;
6. where中使用参数，也会导致全表扫描，可以强制优化器使用某个索引。
   因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划选择推迟到运行时; 它必须在编译时进行选择。
   然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
   例:select name from student where code = @code
   优化:
   select name from student with(index(索引名)) where code = @code
7. 使用组合索引时，要使用其中的第一个索引，否则索引无法使用。
8. 尽量用表变量来代替临时表，但如果表变量包含大量数据则索引使用非常有限(只有主键索引)。
9. 避免频繁创建和删除临时表，以减少系统表资源消耗。
10. 当需要重复引用大型表或常用表中的某个数据集时，适当使用临时表会更有效。
11. 可以用distinct就别使用group by。
12. 要注意select into会锁表，导致其他用户无法访问。
13. 能使用union all就别使用union，因为union all不执行select distinct函数，可以减少资源浪费。
14. 充分使用连接条件。
15. 不要用使用‘*’，不要返回无用字段。
16. 尽量避免大事务操作，提高系统并发能力。
17. 尽量避免更新聚集索引数据列，它的顺序是表记录的物理存储顺序，会消耗大量资源。