MySQL中的SQL的常见优化策略

1 避免全表扫描

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

 

2 避免判断null值

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

    select id from t where num is null

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

    select id from t where num=0

 

3 避免不等值判断

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

 

4 避免使用or逻辑

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

    select id from t where num = 10 or num = 20

    可以这样查询：(虽然union 效率也挺低的，但是比or效率高)

    select id from t where num = 10

    union all

    select id from t where num = 20

 

5 慎用in和not in逻辑

    in和not in也要慎用，否则会导致全表扫描，如：

    select id from t1 where t1.num in(select id from t2 where id > 10)

    此时外层查询会全表扫描，不使用索引。可以修改为：

    select t1.id from t1, (select id from t2 where t2.id > 10) t2 where t1.id = t2.id

    此时索引被使用，可以明显提升查询效率。

 

6 注意模糊查询

    下面的查询也将导致全表查询

    select id from t where name like ‘%abc%’

    模糊查询如果有必要条件时，可以使用select id from t where name like ‘abc%’来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr等）。

 

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子句“=”左边注意点

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

 

10 组合索引使用

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

 

11 不要定义无异议的查询

    不要写一些没意义的查询，如需要生成一个空表结构：

    select col1,col2 into t1 from t where 1=0

    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

    create table t(...)

 

12 exists

    很多时候用exists代替in是一个好的选择：

    select num from a where num in(select num from b)

    用下面的语句替换：1为真，0为假

    select num from a where exists(select 1 from b where num=a.num)

 

13 索引也可能失效

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

   

14 表格字段类型选择

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

    尽可能的使用varchar代替char，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。（定长使用char（如：密码加密）,不定长使用varchar）

 

15 查询语法中的字段

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