sql优化

参考博客：

SQL优化的几种方法

常见的SQL优化方法

sql优化的N种方法_持续更新

史上最全SQL优化方案

sql语句用大写

解析sql语句时，把小写的字母转换成大写的再执行

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

应尽量避免在where子句中进行以下操作，以下操作将导致引擎放弃索引而进行全表扫描。

• 对字段进行null判断，如：

  SELECT id FROM table WHERE num is null
  

  解决方法：

  方法1：在创建表时，使用默认值（DEFAULT）约束；例如：将字符串设置为空字符串，将数字设置为0。然后查询该特定值

  方法2：在添加记录时，将NULL转换为特定值，然后查询该特定值

• 使用!=或<>操作符；

• 使用or连接条件；

  SELECT id FROM table1 WHERE num=10 OR num=20
  

  解决方法：

  SELECT id FROM table1 WHERE num=10
  UNIT ALL
  SELECT id FROM table1 WHERE num=20
  

• 使用in或not in；

  SELECT id FROM table1 WHERE num in (1, 2, 3)
  

  解决方法：

  对于连续数值，能用between就不用in：

  SELECT id FROM table1 WHERE num BETWEEN 1 AND 3
  

  对于离散数值，可用union all：

  SELECT id FROM table1 WHERE num=1
  UNIT ALL
  SELECT id FROM table1 WHERE num=2
  UNIT ALL
  SELECT id FROM table1 WHERE num=3
  

• 使用like；

  SELECT id FROM table1 WHERE name like '%abc%'
  

  解决方法：

  使用全文搜索，MySQL有该索引

• 等号左侧使用算术运算；

  SELECT id FROM table1 WHERE num/2=100
  

  解决方案：

  SELECT id FROM table1 WHERE num=100*2
  

• 对字段进行函数运算等；

  SELECT id FROM table1 WHERE SUBSTRING(name,1,3)='abc'
  SELECT id FROM table1 WHERE DATADIFF(day,createdate,'2005-11-30')=0
  

  解决方案：

  SELECT id FROM table1 WHERE name like 'abc%'
  SELECT id FROM table1 createdate>='2005-11-30' and createdate<'2005-12-1'
  

不要用一些没有意义的查询，如生成一个空表

SELECT col1, col2 into #t FROM table1 WHERE 1=0

解决方法：

CREATE table #t(...)

使用exists替代in，用not exists替代not in。not in 是低效的，因为它对子查询中的表执行了一个全表遍历，他执行了一个内部的排序和合并。select num from a where exists（select 1 from b where num=a.num）

SELECT num FROM table1 WHERE num in (SELECT num FROM table2)

解决方法：

SELECT num FROM table1 a WHERE exists(SELECT 1 FROM table2 num=a.num )

对只含数值信息的字段尽量使用数值型代替字符型，否则会降低查询和连接性能

引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型而言只需要比较一次

尽可能使用varchar代替char，节约存储空间，提高效率

变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些

尽量用具体字段代替*进行查询

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

当索引中有大量重复数据时，索引是无效的

当进行update或insert操作时，索引的存在会降低该操作的效率

尽量避免频繁创建或删除临时表，减少系统资源消耗

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

在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免产生大量log，提高效率

如果使用到了临时表，在存储过程的最后务必将所有的临时表显示的删除，先truncate table ，然后drop table，避免系统表长时间锁定

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

对于小型数据集使用fast_forward游标要优于其他逐行处理方法，尤其是在必须引用几个表才能获取所需要的数据时

表名顺序。选择最有效率的表名顺序，from后面先跟大表，再跟小表，因为from子句中写在最后的表被优先处理，from后跟多个表的情况下，应该选择记录条数最少的表作为优先处理的表

where子句连接顺序。那些可以过滤最大数量记录的条件必须写在where子句的末尾

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

高效删除重复记录

最高效的删除重复记录方法delete from emp a where a.rowid>(select min(b.rowid) from emp b where a.emp_no=b.emp_no);

使用truncate替代delete

当用delete删除表中记录时，回滚段rollback用来被存放可以被恢复的信息，如果你不执行commit，oracle会将数据恢复到删除之前的状态；当运行truncate时，回滚段不再存放任何可被恢复的信息，当运行truncate时，数据不再被恢复，此时很少的资源被调用，执行时间也会很短

尽量多使用commit

随着commit的多次使用，系统资源被释放，性能会提高；

用where子句替换having子句

having只会在检索出所有记录之后才对结果集进行过滤。on、where、having这三个都是删选条件的子句，on最先执行，where次之，hiving最后；on先把不符合条件的记录过滤才进行统计，它可以减少中间运算要处理的数据，on的使用仅限于多表连接；where也是过滤数据后才进行sum；hiving是在计算之后才启作用

使用表的别名

当sql语句中连接多个表时，请使用表的别名并用别名前缀识别每个column，这样可以减少sql解析时间，避免歧义

用>=替代>

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效

高效：SELECT * FROM EMP WHERE DEPTNO >=4
低效：SELECT * FROM EMP WHERE DEPTNO >3
注：前者DBMS将直接跳到第一个DEPTNO等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPTNO大于3的记录。

用union all替换union

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。

当使用join（inner join或left join）操作时，就应该是小表在前，大表在后。把重复关联键少的表放在join前面做关联可以提高join的效率

注：如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表

尽量使用表变量来代替临时表

如果表变量包含大量数据，请注意索引非常有限（只有主键索引）

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

字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效

指定查询的索引

• use index(索引): 推荐使用指定的索引

  SELECT * FROM table1 USE INDEX(索引A)
  

• ignore index(索引) : 忽略掉这个索引

  SELECT * FROM table1 IGNORE INDEX(索引A)
  

• force index(索引): 强制使用该索引

  SELECT * FROM table1 FORCE INDEX(索引A)
  

insert 优化

• 需要插入多条数据的时候 使用批量插入(多次插入需要频繁的建立连接，浪费资源)

• 多次插入数据时,采用手动提交事务

  sql语句在执行的时候如下所示, 会自动开启事务和提交事务. 我们可以手动开启事务,然后执行多条sql后,在手动提交事务.减少资源浪费

  start transaction;   
  执行sql;
  commit;
  

count 优化

速度:count(*)>count(1)>count(字段)

count(字段):遍历整张表 会把每一行的字段值取出来,然后返回
count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加
count(*):inndb引擎,特意做了优化,不会取出值,直接服务层进行累加

创表时使用同一编码

explain关键字分析

explain是非常重要的关键字,要善于运用它. 通过explain我们可以获得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

使用方法：explain + sql语句。可根据结果进行解析sql的问题所在

EXPLAIN SELECT * FROM table1