性能优化的思路
- 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句
- 其次使用explain命令去查询由问题的SQL的执行计划(脑补链接:点我直达1,点我直达2)
- 最后可以使用show profile[s] 查看由问题的SQL的性能使用情况
- 优化SQL语句
介绍
数据库查询快慢是影响项目性能的一大因素,对于数据库,我们除了要优化SQL,更重要的是得先找到需要优化的SQL语句。
MySQL数据库有一个“慢查询日志”功能,用来记录查询时间超过某个设定值的SQL,这将极大程度帮助我们快速定位到问题所在,以便对症下药。
至于查询时间的多少才算慢,每个项目、业务都有不同的要求。
比如传统企业的软件允许查询时间高于某个值,但是把这个标准方在互联网项目或者访问量大的网站上,估计就是一个Bug,甚至可能升级为一个功能缺陷。
MySQL的慢查询日志功能,默认是关闭的,需要手动开启。
开启慢查询功能
查看是否开启慢查询功能
参数说明:
- slow_query_log:是否开启慢查询,on为开启,off为关闭;
- log-slow-queries:旧版(5.6以下版本)MySQL数据库慢查询存储路径,可以不设置该参数,系统则会给一个缺省的文件:host_name-slow.log
- long_query_time:慢查询阀值,当查询时间多于设置的阀值时,记录日志,单位为秒。
临时开启满查询功能
在MySQL执行SQL语句设置,但是如果重启MySQL的话会失效。
set global slow_query_log=on; set global long_query_time=1;
永久性开启慢查询
修改:/etc/my.cnf,添加以下内容,然后重启MySQL服务
[mysqld] lower_case_table_names=1 slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/chenyanbindeMacBook-Pro-slow.log long_query_time=1
查看满查询启动状态
演示慢查询
为了演示方便,我们让sql睡眠3秒!
格式说明:
- 第一行,SQL查询执行的具体时间
- 第二行,执行SQL查询的连接信息,用户和连接IP
- 第三行,记录了一些我们比较有用的信息,
- Query_timme,这条SQL执行的时间,越长则越慢
- Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
- Rows_sent,查询返回的行数
- Rows_examined,查询检查的行数,越长就越浪费时间
- 第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间。
- 第五行,执行的SQL语句记录信息
分析满查询日志
MySQL自带的mysqldumpslow
参数说明:
- -s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
- -t, 是top n的意思,即为返回前面多少条的数据;
- -g, 后边可以写一个正则匹配模式,大小写不敏感的;
MySQL性能fenix语句show profile(重要)
介绍
- Query Profiler是MySQL自带的一种query诊断分析工具,通过它可以分析出一条SQL语句性能瓶颈在什么地方。
- 通常使用explain,以及slow query log都无法做到精确分析,但是Query profiler却可以定位出一条SQL执行的各种资源消耗情况,比如CPU、IO等,以及该SQL执行所耗费的时间等。不过该工具只有在MySQL5.0.37以上版本中才有实现
- 默认的情况下,MySQL的该功能没有打开,需要自己手动打开
语句使用
- show profile和show profiles语句可以展示当前会话(退出session后,profiling重置为0)中执行语句的资源使用情况。
- show profiles:以列表形式显示最近发送到服务器上执行的语句的资源使用情况,显示的记录数由变量:profiling_history_size控制,默认15条
- show profile:只是最近一条语句执行的消息资源占用信息,默认实现Status和Duration两列
开启Profile功能
- Profile功能由MySQL会话变量:profiling控制,默认是OFF关闭状态。
- 查看是否开启了Profile功能
select @@profiling; show variables like '%profil%';
打开profiling功能
set profiling=1;
show profile用法
SHOW PROFILE [type [, type] …… ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS }