慢查询总结

1.COUNT

count的作用 统计值的数量和统计行的数量

值是非空表达式（NOT NULL）

一个常见的错误就是在想统计行数的时候，在ount的括号中放入列名，如果想知道结果的行数，应该总是使用COUNT(*)，这可以清晰的说明意图，并且得到好的性能。

2.MYISAM

只有在没有WHERE条件的时候OUNT(*)才是最快的，在有条件过滤的时候并不非常快。

3.简单优化

可以利用MYISAM对COUNT（*）的优化对已经有索引的一小部分做统计。

SELET COUNT(*) FROM WORD.ITY WHERE ID>5;

优化为下面的语句

SELET (SELET COUNT(*) FROM ITY) - COUNT(*) FROM ITY WHERE ID<=5;

这样的explain只扫描6行数据

使用一个查询统计同一列中不同值的数量。

select sum(if(color='blue',1,0)) as blue,sum(if(color='red',1,0)) as red from items;

下面是一个等价查询

select count(color='blue' or null) as blue,count(color='red' or null) as red from items;

4.优化联接

1.确保on 或using使用的列上有索引。

通常只需要在联接中的第2个表上添加索引就可以。

2.确保group by或order by只引用一个表中的列。这样可以使用索引。

3.谨慎升级mysql

 5.优化子查询

 对于子查询，尽可能的使用联接。

 6.优化group by和distinct

1.主要方式：索引

2.优化group by的策略：临时表或文件排序分组。

SQL_SMALL_RESULT ： 强制使用临时表

SQL_BIG_RESULT :强制使用文件排序

通常对表的id进行分组会更加高效

可以使用SQL_MODE参数禁止SELET中使用在group by中出现的列

子查询创建的临时表不支持索引

所以要让子查询创建的临时表尽可能的小

3.使用ROLL UP 优化GROUP BY

WITH ROLLUP

最好的方式是将WITH ROLLUP 放在应用程序里。

注意: Rollup 与 order by 相互排拆

 7.优化limit和offset

LIMIT 和ORDER BY 一块使用。

如果没有索引，就使用文件排序。

8.优化SQL_AL_FOUND_ROWS

这个地方很重要

一个技巧：在含有limit的查询中添加SQL_AL_FOUND_ROWS，这样就可以知道没有limit的时候会返回多少行数据。服务器会预测将会发现多少行数据。

但是服务器并不能真正的做到，只是告诉服务器生成结果并丢掉结果中不需要的部分。而不是在得到需要的数据后就立即停止。这个选项代价很高。

一个非常好的设计：

如果每页有20条结果，那么应该查询limit 21行数据，只显示20条，如果结果中有21行，那么就会有下一页。

另一种方式：就是提取并缓存大量数据，比如1000行，然后从缓存中获取后续页面的数据。

可以让程序知道一共有多少数据，少于1000，程序知道有多少页，如果大于1000，可以显示找到的结果超过1000个。

这两种都比重复产生完整的结果效率高。

如果以上两种都不可以使用，可以使用覆盖索引，使用单独的count（*）会更好。

9.优化联合 union

mysql总是使用临时表来执行union，无法做更多的优化

重要的是，一定要使用union all，除非真的是需要服务器消除重复的行，

否则mysql会使用distint选项，来确保所有行数据的唯一性。

10.用户自定义变量

一些需要注意的问题：

会禁止缓存

不能用于文字常量和标识的地方（表名，列名，limit）

和连接有关，不能跨通信使用

如果使用连接池，会引起代码隔离

mysql 5.0大小写敏感

不能显示的声明类型，最好的方式给变量显示的一个初始值 0 、  0.0 、 ‘’

用户自定义变量的类型是动态的，赋值的时候才会变化。

优化器有时候会把变量优化掉

未定义的变量不会引起语法错误，很容易犯错