MySQL性能优化

MySQL性能优化

对于性能优化的思路

• 首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
• 其次使用explain命令查看有问题的SQL的执行计划
• 最后可以使用show profiles 查看有问题的SQL的性能使用情况

下面针对着三个方面一次展开讲解。

慢查询

如果不是指定需要优化某条SQL语句，则需要使用慢查询日值来查找出查询时间比较长的SQL语句。

慢查询的基本配置

slow_query_log 启动停止慢查询日志
slow_query_log_file 指定慢查询日值的存储路径及文件
long_query_time 指定记录慢查询日值SQL执行时间的阈值(单位：秒，默认10s)
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方

配置了慢查询后，它会记录符合条件的SQL,包括

• 查询语句
• 数据修改的语句
• 已经回滚的SQL

慢查询的开启

MySQL的慢查询日志功能，默认是关闭的，需要手动开启。

临时开启慢查询功能

--查看是否开启慢查询功能
show variables like '%slow_query_log%'
--开启慢查询与设置超时参数
set global slow_query_log=ON;
set global long_query_time=1;

永久开启慢查询功能

永久开启慢查询功能，需要修改/etc/my.cnf配置文件，重启MySQL,这种永久生效

[mysqld]
slow_query_log =ON
slow_query_log_file =/var/log/mysql/slow.log
long_query_time =1

执行计划

执行计划关注的点

id:id越大越先执行
type:
key:实际上使用的索引
extra:重要的信息

根据执行计划我们可以看到索引利用的情况，可以考虑是否添加索引，是否语句有问题导致索引失效。

show profile

1. 查看是否已经开启show profile

   show variables like 'profiling';
   

2. 开启show profile

   set profiling = ON;
   

3. 运行几条sql,以便于show profile的日志分析

4. show profiles;

5. 诊断SQL

   用于单独分析某条sql,查看某条sql的生命周期以及各占用多少时间。

   show profile all for query id;
   #id 为show profiles 查出来的某条记录的id
   #参数说明
   ALL --显示所有的开销信息
   BLOCK IO--显示块IO相关开销
   CONTEXT SWITCHES--上下文切换开销
   CPU --显示CPU相关开销信息
   IPC --显示发送和接受相关开销信息
   MEMORY --显示内存相关开销信息
   PAGE FAULTS --显示页面错误相关开销信息
   SOURCE --显示和Source_function,Source_file,Source_line相关开销的信息
   SWAPS --显示交换次数相关开销信息
   

6. 日常开发需要注意的结论

   如果show profile … for query id;出现如下四个，则必须优化这条sql

   converting HEAP to MyISAM:查询结果台打，内存不够用都往磁盘上搬了
   Creating tmp table :创建临时表（拷贝数据到临时表，用完在删除）
   Copying to tmp table on disk:把内存中临时表复制到磁盘，危险！！
   locked
   

总结

以上的三个方法都是针对SQL语句进行的优化，有的时候优化SQL语句不能达到效果，这个时候我们要考虑从表结构，硬件(要区分是IO密集型应用，还是CPU密集型应用)，架构上读写分离，表分区，表的划分，垂直分表，水平分表，等综合考虑。