菜鸟谈——mysql之查询截取分析

1. 数据表优化步骤

1. 开启慢日志查询日志，设置阙值。。。收集执行超过阙值的sql语句
2. Explain+sql 分析
3. ShowProfile
4. Sql服务器的参数调优

2. 查询优化

2.1 永远小表驱动大表

2.2 Order by 关键字优化

2.3 Group by关键字优化

2.4 小总结

为排序使用索引

Mysql两种排序方式：文件排序（Using filesort）或扫描有序索引排序（Using index）

Mysql能为排序与查询使用相同的索引（索引是排好序的快速查找数据结构）

KEY a_b_c(a,b,c)

2.4.1 Order by *能使用索引最左前缀*

-ORDER BY a

-ORDER BY a,b

-ORDER BY a,b,c

-ORDER BY a DESC,b DESC,c DESC（同升同降）

2.4.2 如果WHERE**使用索引的最左前缀定义为常量，则order by能使用索引**

-WHERE a=const ORDER BY b,c

-WHERE a=const AND b=const ORDER BY c

-WHERE a=const ORDER BY b,c

-WHERE a=const AND b>const PRDER BY b,c

2.4.3 不能使用索引进行排序

-ORDER BY a ASC,b DESC,c DESC   排序不一致

-WHERE g=const ORDER BY b,c            丢失a索引

-WHERE a=const ORDER BY c                丢失b索引

-WHERE a=const ORDER BY a,d             d不是索引的一部分

-WHERE a in(…) ORDER BY b,c           对于排序来说，多个相等条件也是范围查询

3. 慢日志查寻

3.1 是什么

Mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阙值的语句，具体指运行时间超过long_query_time值得sql，则会被记录到慢日志中。

具体运行时间超过long_query_time值得sql，则会被记录到慢日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看那些sql超出了我们的最大忍耐时间值，比如一条sql执行时间超过5秒钟，我们就算慢sql，希望能收集超过5秒的sql，结合之前explain进行全面解析。

3.2 怎么玩

3.2.1 说明

默认情况下，mysql没有开启慢查询日志，需要我们手动设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

3.2.2 查看是否开启及如何开启

查询是否开启

show variables like '%slow_query_log%';

开启

set global slow_query_log = 1;

3.3.3 那么开启了慢查询日志，什么样的sql才会被记录到慢日志里面呢？

• 这个是由参数long_query_time控制，默认情况下long_query_time值为10秒

• 命令：SHOW VARIABLES LIKE ‘long_query_time%’;
  

• 可以使用命令修改，也可以在my.cnf参数里修改。

• 假如运行时间正好等于long_query_time的情况，并不会被记录下来。。

• 也就是说在mysql源码里是判断：大于long_query_time，而非大于等于

3.3.4 case热身

3.3.4.1 查询是否开启

Show variables like ‘%slow_query_log%’;

3.3.4.2 开启，并且设置阙值

Set global slow_query_log=1;

Set global long_query_time=3;

3.3.4.3 为什么设置后看不出变化？

1) 需要重新连接或新开一个会话才能看到修改值。SHOW VARIABLES LIKE’long_query_time%’;

2) show global variables like ‘long_query_time’;

3.3.4.4 记录慢sql，并用于分析

select sleep(4);

3.3.4.5 查询当前系统中有多少条慢查询记录

Show global status like ‘%slow_queries%’;

3.3.5 配置版

#windows系统my.ini配置文件
# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="2XQBTV8H6ML83VA.log"
slow-query-log=1
slow_query_log_file="2XQBTV8H6ML83VA-slow.log"
long_query_time=10

#linux系统my.cnf配置文件
#段：[mysqlId]的配置
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/xxxxxxx-slow.log;
long_query_time=3;
log_output=FILE;    

3.3 日志分析工具

mysqldumpslow

http://blog.csdn.net/yabingshi_tech/article/details/51220831

4. ShowProfile

4.1 是什么

是mysql用来分析当前会话中语句执行的资源消耗情况。。可以用于sql调优的测量

4.2 默认情况下，处于关闭状态，并且保持最近15次的运行结果

4.3 分析步骤

4.3.1 是否支持

4.3.2 开启（默认是关闭的）

4.3.3 运行sql

sleep(4);

4.3.4 查看结果

1) Show profiles;

2) 诊断sql，show profile cpu,block io for query上一步前面的问题 sql 数字号码；

如：show profile cpu,block io for query 7;

4.3.5 日常开发注意

ConvertHEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了

Creatingtmp table 创建临时表，拷贝数据到临时表，用完再删除

Copying totmp table on disk 把内存中临时表复制到磁盘，，，危险！！！！！

locked

5. 全局日志查询