13. sql优化

sql优化

一、首先我们要搞清楚为什么要进行SQL优化

开发项目上线初期或者开发的项目级别小，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不明显，而随着时间的积累，项目的成熟，
业务数据量与日俱增，这时SQL的执行效率对程序的运行效率的影响逐渐增大，此时就必须进行SQL优化来提升系统的运行性能。

二、如何进行SQL优化

1. 优化表结构

1.1 尽量使用数字型字段

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

1.2 尽可能的使用 varchar 代替 char

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

1.3 对作为查询条件和order by的字段简历索引

建立索引后可以有效的避免全表扫描。

1.4 当索引列大量重复数据时，可以把索引删除掉

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

1.5 一个表中索引最多不要超过6个。

2. 通过优化查询

2.1 避免在 where 子句中对字段进行 null 值判断

where 子句中对字段进行 null 值判断，这样导致引擎放弃对该字段的索引查询，而变为全表扫描。

如何解决？我们可以给需要判断的字段设置默认值。

如：select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

2.2 应尽量避免在 where 子句中使用!=或<>操作符

使用该符号后，也会导致字段索引无效，进行全表扫描。

2.3 应尽量避免在 where 子句中使用 or 来连接条件

使用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

2.4 应尽量避免在 where中使用 in 和 not in

使用in 和 not in，也会导致放弃使用索引，而进行全表扫描。

建议：对于连续的数值，能用 between 不用 in 了

select id from t where num in(1,2,3) 改为下面的查询
select id from t where num between 1 and 3

2.5 应尽量避免使用like关键字作为查询条件

在like条件中使用的前后都模糊查询，会导致全表查询。如下面的例子

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

2.6 应尽量避免在where子句中对字段进行函数或表达式操作

对字段进行函数或表达式操作，会导致字段的索引失效。进行全表扫描。

查询条件name以abc开头的id

select id from t where substring(name,1,3)='abc'
应改为:
select id from t where name like 'abc%'

查询num一半为100的id。

select id from t where num/2=100
应改为:
select id from t where num=100*2

2.7 使用索引字段作为条件时，复合索引的使用

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

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

2.9 任何查询也不要出现select *

用具体的字段列表代替“*”，不要返回用不到的任何字段。

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

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

三、总结：

其实在我们日常开发中初级阶段，很少会进行SQL优化，而且由于数据量太少，我们感觉不到优化的效果。但是当数据量很大，且项目日增长数据很大时。必须要进行SQL优化。
大数据量的查询，一个索引的建立，让你的程序运行效率提升多个档次。所以，中高级开发工程师必须具备SQL优化的能力。上面的优化方案不是很全。但是一些基本的优化都已经给出了解决方案。如有问题，大家积极在留言区进行指正。