SQL优化

优化工具

自己总结转载的两个工具：
explain
show profiles

几个类型的优化

1、优化COUNT
count主要有这三种形式：

• count(*)
• count(1)
• count(列名)

count(*)	count(1)	count(列名)
统计行数，不会忽略列值为NULL	统计行数，不会忽略列值为NULL	统计某一列的有效值，会忽略列值为NULL。

执行效率上：count（主键）的执行效率是最优的。所以，如果列名是主键的话，count(列名)会比count(1)快 。但是，从规范化的角度来说，会选择count(*)。

2、优化LIMIT和OFFSET
比如，如果我们写 limit 1000000,10。这个句子最后只要10个结果，但是却要搜索100W行数据，这个是不合理的（取出整个结果集然后扔掉一些额外的数据这个做法，不合理）
一些想法：能不能建立联合索引
举个例子：下面的查询，使用explain后，type为all，想办法提升type

select * from user limit 10000000,10

修改为：这个时候type变成ref。

SELECT  * FROM USER a JOIN (SELECT    id   FROM   USER  LIMIT 10000000,10) b ON a.id = b.id

为什么这样可以优化？因为前一句没有使用到索引，而后边的一句，使用了索引id，很快的找到了需要的10行数据的id，然后将其连接，将需要的列信息选择出来。

3、优化GROUP BY
GROUP BY 与临时表的关系 :

• 如果GROUP BY 的列没有索引,产生临时表.
• 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
• 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
• 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
• 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
• 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
  总的来说就是：group by 或者是order by的列没有索引可能会影响效率。

可行的方法：

• 在group by、order by上尝试建立索引
• 在分组之前，提前将条件选择操作上推

4、优化DISTINCT
转自：分析MySQL中优化distinct的技巧
比如说原始的查询语句是：

select count(distinct nick) from user_access_xx_xx;

就算我们在nick上边建立索引，count起来依旧很慢，因为count要经过很多虽然已经使用索引排好序，但是重复的Nick。如何解决：使用loose index scan，即count的时候，略过那些重复的部分。
优化后的SQL为：

select count(*) from ( select distinct(nick) from user_access)t ;

值的注意的是：mysql把distinct优化为group by，它首先利用索引来分组，然后扫描索引。

select count(*) 
from user_access_xx_xx
group by nick;

一些优化的方法

参考博客
我觉得，方法可以分成三类：

发挥索引的作用

1、善于利用force index，将type的类型从all变成更高级。

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

使用explain时，type有

• ALL ：全表扫描
• INDEX ：在索引上进行扫描
• RANGE ：在部分索引（确定范围的索引）进行扫描
• REF ：匹配某个单独值
• EQ_REF ：使用的索引，都是唯一索引
• CONST,SYSTEM ：根据主键或者唯一索引进行的查询
• NULL

比如：这个查询
转载来自：利用 force index优化sql语句性能

select customer,count(1) c
from upv_**
where created between "2015-07-06" and "2015-07-07"
group by customer 
having c > 20
order by c desc

在日期上没有索引，explain查询后type是index。优化以后，在日期上边建立一个index，则type变成了 range
优化后：

select customer,count(1) c
from upv_** force index(idx_created)
where created between "2015-07-06" and "2015-07-07"
group by customer 
having c > 15
order by c desc 

有时候，单个index不能满足要求，需要考虑复合index

2、少用（*），多用索引
少用 count ( * )等等

避免避免索引失效的情况

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
比如说：where num<>1000改变成
where num<1000 or num>1000

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3、很多时候用 exists 代替 in，not exists 代替not in 是一个好的选择
NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
exits例子

将选择条件下推

尽早过滤和选择，每次操作都以减少数据量为目标
1、尽早的执行选择语句。
where中的选择条件，将能起过滤作用大的条件，放在前边
一个非常好的例子，注意分析思路
MYSQL一次千万级连表查询优化（一）

2、用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤（即将选择下推，尽早做选择）

3、选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

其他

1、 删除重复记录

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) 
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO); 

索引失效

摘自
1、or
如果条件中有or，会使索引失效。要想索引不失效，则要将所有的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、对于复合索引，如果不使用前列，后续列也将无法使用
相关说法
下面这些说法还没有验证（TODO）
（
即index(name,phone,address)，如果只使用 where phone=123456 and address="abcd"不会用索引。但是，name，name+phone，name+phone+address会用索引。如果写了呢(phone,name,address`)？mysql查询优化器会最终以这种顺序进行查询执行。

）
（index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。）
举个例子：（摘自）
为什么 b<10 and c <10,没有用到索引？而 a<10 and c <10用到了？
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

3、like的模糊查询以%开头，索引失效
4、索引的数据类型转换
如果索引name是字符串类型的，那么，如果在写的时候写成 where name=123是无法使用索引的，要写成where name=“123”。
5、where 子句里对索引列上有数学运算，用不上索引

where id =id+1
select id from t where substring(name,1,3)='abc'  # name以abc开头的id
#改成
select id from t where name like 'abc%'

6、where 子句里对有索引列使用函数，用不上索引
where ABS(id)=1
7、如果MySQL预计使用全表扫描要比使用索引快，则不使用索引