Mysql查询优化（一）：SQL语句优化

查询速度的优化有大致有三个考虑方向，一是硬件层面的优化，二是数据库“配置”优化，三是SQL语句优化。

大部分搞数据分析的其实只有查询访问的权限，甚至建临时表的权限都没有，那这时候就专注在SQL本身的优化就行了。SQL层面的优化主要是围绕两个核心理念：①访问更少的数据 ②返回更少的数据

一、慎用否定查询

常用的否定查询：!=，<>， not in，not exist，is null，is not null  这些查询都可能导致使用不到索引

二、or和union的使用

or中的列都有索引的情况下，下列几种方式查询速度基本一致。

--n为带索引的字段
select * from table where n = 100 or n = 500  --方法1
select * from table where n in (100,500) --方法2

select * from table where n = 100
union all
select * from table where n = 500 --方法3

or中的列有一列没有索引的话，整个查询都不会使用到索引。多表查询的情况下，如果or条件关联的字段不在同一个表内，即使两个字段都含索引，也不会使用到索引。（在Innodb引擎下or无法使用组合索引）

--n为带索引的字段，m为带索引的字段，a为不带索引的字段
select * from table where n = 100 or m = 500  --方法1，使用到索引
select * from table where n = 100 or a = 200  --方法1，没使用到索引

select * from table where n = 100
union all
select * from table where a = 200 --方法3，改进后，速度由于方法2

---------------------------------------------------------------------------
--多表关联
select * from table t1,table t2 where t1.id=t2.id and (t1.c='123' or t1.c='1234'); --使用索引
select * from table t1,table t2 where t1.id=t2.id and (t1.c='123' or t2.c='1234'); --不使用索引

三、where子句的使用

• 避免使用like ‘%a%’：where num like ‘%a%’使用前置通配符的情况下，无法使用到索引。like 'a%' 可以使用到索引。全文索引fulltext可以尝试一下，或是Elasticsearch。
• 避免在where子句中对字段进行函数操作：where num/2 = 100 或是 where substr(name,1,3) 等都不能无法使用到对应字段内的索引。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。特殊情况下的隐式转换，比如1和‘1’.
• 避免使用参数：where num = @num，会导致全表扫描。

 

四、SQL执行顺序

SQL语句的基本执行顺序：

 from > on>where > group by > having > order by > select >limit 

• oracle的where子句解析顺序自右向左
• mysql的where子句解析顺序自左向右

基于这两者的特性，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的合适位置。

在group by 后面增加 order by null 就可以防止排序。

五、explain分析