mysql优化前情提要

前情提要

如果mysql的性能或者语句执行时间没有达到你的预期，首先你得知道到底是哪条sql语句出了问题，不能把项目里面的sql全部手动走一次再返回来分析吧，这样从人力和时间上都不允许呦，所以，mysql自带了一些功能可以帮助我们定位问题！！！

慢查询

mysql开启慢查询日志：slow_query_log=ONlong_query_time=3 #查询时间多久以上slow_query_log_file=/var/lib/mysql/slow-log.log
查看mysql慢查询开启情况：show variables like '%slow_query%';
查看mysql慢查询时间阈值：show variables like 'long_query_time%';
注：慢查询日志包括被锁阻塞的时间--->Lock_time:~ 

慢查询日志分析工具：

mysqldumpslow工具（mysql自带）：

例如：得到按照时间排序的前10条里面含有左连接的查询语句：
mysqldumpslow -s t -t 10 -g “left join” /var/log/mysql/slow.log（慢查询日志路径）

	-s：是表示按照何种方式排序 c:访问计数 l：锁定时间 r:返回记录 t:查询时间 al：平均锁定时间ar：平均返回记录数 at：平均查询时间-t：是top n的意思，即为返回前面多少条的数据-g：后边可以写一个正则匹配模式，大小写不敏感的

percona-toolkit工具（就比较专业了）：

	下载安装：wget https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gztar -xf percona-toolkit-3.0.11_x86_64.tar.gzcd percona-toolkit-3.0.11perl Makefile.PLmakemake install
一般到这里就结束了，如果出现了错误，请继续Can't locate ExtUtils/MakeMaker.pm in @INC 错误的解决方式:yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMakerCan't locate Time/HiRes.pm in @INC 错误的解决方式:yum install -y perl-Time-HiResCan't locate Digest/MD5.pm in @INC 错误的解决方式:yum install perl-Digest-MD5.x86_64

使用pt-query-digest查看慢查询日志

语句：pt-query-digest /var/lib/mysql/localhost-slow.logpt-query-digest语法及重要选项pt-query-digest [OPTIONS] [FILES] [DSN]--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析--limit  限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。--host mysql服务器地址--user mysql用户名--password mysql用户密码--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。--until 截止时间，配合—since可以分析一段时间内的慢查询。

分析查询语句：

查看当前session是否开启profiling控制，默认是0 关闭状态select @@profiling;
开启profile功能set profiling=1; --1是开启、0是关闭
查看最近执行的sql语句以及执行时间show profiles;
查看最近一条的sql语句的执行详情（查询出来的字段，如果有一个值特别大，就按值分析）show profile；
定位一条sql语句的执行详情：show profile for query 序号;（此序号来源于show profiles;的Query_ID ）