1. 数据表优化步骤
- 开启慢日志查询日志,设置阙值。。。收集执行超过阙值的sql语句
- Explain+sql 分析
- ShowProfile
- 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