MySQL数据库：SQL语句优化

• 数据库最常用的优化方式有：SQL语句和索引、数据库表结构、系统配置、硬件。
• 优化效果：SQL语句和索引 < 数据库表结构 < 系统配置 < 硬件，成本也是递增的。

优化方法

设计符合范式的数据库。

1. 设计符合范式的数据库。
2. 选择合适的存储引擎。
3. SQL语句优化；
4. 索引优化：高分离字段建立索引。
5. SQL表结构、字段优化。
6. 数据库参数优化：IO参数、CPU参数。
7. 延迟加载、设置缓存与缓存参数优化。
8. 分库分表：垂直切分与水平切分。
9. 分区：将表的数据按照特定的规则放在不同的分区，提高磁盘的IO效率，提高数据库的性能。
10. 主从复制与读写分离：三个主要线程与bin-log文件、relay_log文件，主数据库负责写操作，从数据库负责读操作。
11. 负载均衡。
12. 数据库集群。
13. 硬件。

sql语句优化

原则：

• 避免全表扫描，尽量用索引。

1. 写出统一的SQL语句：

       虽然只是大小写不同，但是查询分析器认为是两句不同的SQL语句，就会进行两次解析，生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

select * from dual
select * From dual

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

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

4. 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。 如：

select id from t where num is null

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

select id from t where num=0

5. 避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

6. 前导模糊查询将导致全表扫描：

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

使用索引的情况下：

select id from t where name like 'c%'

7. not in 也要慎用
       会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了，尽量使用exists代替in。

8. 如果在 where 子句中使用参数，也会导致全表扫描。
       因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

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

（1）select id from t where num/2=100
应改为：select id from t where num=100*2

（2）select id from t where substring(name,1,3)=’abc’ –name以abc开头的id
应改为：select id from t where name like 'abc%'

（3）select id from t where datediff(day,createdate,'2005-11-30')=0 –'2005-11-30'生成的id
应改为：select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

8. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

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

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

11. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。一个表的索引数较好不要超过6个。

12. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

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

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

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

16、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

17、尽量使用表变量来代替临时表。

18、考虑使用“临时表”暂存中间结果。临时表并不是不可使用，适当地使用它们可以使某些查询更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表，后面的查询就在tempdb中查询了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是，对于一次性事件，较好使用导出表。

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

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

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

22、尽量避免使用游标，因为游标的效率较差。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

23、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

24、尽量避免向客户端返回大数据量。

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

26、用where子句替换Having子句：

避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序，如果能通过where子句限制记录的数目，就可以减少这方面的开销。on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。

27、使用Truncate替代delete：

当需要删除全表的记录时使用Truncate替代delete。在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。

28、使用表的别名:

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

29、使用union all 替换 union：

当SQL语句需要union两个查询结果集合时，这两个结果集合会以union all的方式被合并，然后再输出最终结果前进行排序。如果用union all替代料union，这样排序就不是不要了，效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

30、用where替代order by：

ORDER BY 子句只在两种严格的条件下使用索引：
（1）ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序；
（2）ORDER BY中所有的列必须定义为非空；

低效: (索引不被使用) 
SELECT DEPT_CODE FROM  DEPT  ORDER BY  DEPT_TYPE 
高效: (使用索引) 
SELECT DEPT_CODE FROM  DEPT  WHERE  DEPT_TYPE > 0

31、避免索引列的类型转换：

假设EMP_TYPE是一个字符类型的索引列. 
  SELECT …  FROM EMP  WHERE EMP_TYPE = 123 
  这个语句被转换为: 
  SELECT …  FROM EMP  WHERE EMP_TYPE=‘123’ 
  因为内部发生的类型转换, 这个索引将不会被用到! 
  为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来.
  注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型。

32、优化Group by：

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。

低效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP by JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'
GROUP by JOB

33、避免使用耗费资源的操作：

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

34、在运行代码中，尽量使用PreparedStatement来查询，不要用Statement。