MySQL查看数据库性能常用命令和实战教学

黄聪波

MySQL查看数据库性能常用命令

1. # 列出MySQL服务器运行各种状态值
2. show global status;
3. # 查询MySQL服务器配置信息语句
4. show variables;
5. # 慢查询
6. show variables like '%slow%';
7. # MySQL服务器最大连接数
8. show variables like 'max_connections'; 
9. # 服务器响应的最大连接数
10. show global status like 'Max_used_connections';
11. # 查看试图连接到MySQL(不管是否连接成功)的连接数
12. show status like 'connections';
13. # 创建临时表
14. show global status like 'created_tmp%'; 
15. # MySQL服务器对临时表的配置
16. show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
17. # 打开表的数量
18. show global status like 'open%tables%'; 
19. # table高速缓存的数量
20. show variables like 'table_open_cache'; 
21. # 查看MySQL服务器的线程信息
22. show global status like 'Thread%';
23. # 查看当前运行的sql
24. SELECT * FROM `information_schema`.`PROCESSLIST` WHERE `info` IS NOT NULL and TIME > 0
25. # 当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
26. show variables like 'thread_cache_size'; 
27. # 查询缓存
28. show global status like 'qcache%'; 
29. # 查询缓存适用于特定的场景，建议充分测试后，再考虑开启，避免引起性能下降或引入其他问题
30. show variables like 'query_cache%'; 
31. # 排序使用情况
32. show global status like 'sort%'; 
33. # 文件打开数
34. show global status like 'open_files'; 
35. # 表锁情况
36. show global status like 'table_locks%'; 
37. # 表扫描情况
38. show global status like 'handler_read%'; 
39. # 服务器完成的查询请求
40. show global status like 'com_select'; 
41. # 查询当前MySQL本次启动后的运行统计时间
42. show status like 'uptime';
43. # 查看本次MySQL启动后执行的select语句的次数
44. show status like 'com_select';
45. # 查看本次MySQL启动后执行insert语句的次数
46. show global status like 'com_insert';
47. # 查看本次MySQL启动后执行update语句的次数
48. show global status like 'com_update';
49. # 查看本次MySQL启动后执行delete语句的次数
50. show global status like 'com_delete';
51. # 查看立即获得的表的锁的次数
52. show status like 'table_locks_immediate';
53. # 查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制
54. show status like 'table_locks_waited';
55. # 查看查询时间超过long_query_time秒的查询的个数
56. show status like 'slow_queries';
57. # 通过mysql自带profiling(性能分析)工具可以诊断某个sql各个执行阶段消耗的时间，每个执行阶段在cpu disk io等方面的消耗情况。
58. show variables like '%profiling%';
59. #
60. show profiles
61. show profile for query 2;
62. show profile cpu, block io for query 2;

复制代码

实战

查询服务器状态和配置

1. # 列出MySQL服务器运行各种状态值
2. show global status;

复制代码

• 所有数据一目了然，参数有点多，分成另一篇文章来介绍[https://www.cnblogs.com/LoveBB/p/17194556.html]。
  

慢查询

1. # 慢查询
2. show variables like '%slow%';

复制代码

• 配置中关闭了记录慢查询，打开之后，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内。打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
  

连接数

MySQL: ERROR 1040: Too many connections
一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小： 　

1. # MySQL服务器最大连接数
2. show variables like 'max_connections'; 

复制代码

• 这台MySQL服务器最大连接数是5275，然后查询一下服务器响应的最大连接数：
  

1. # 服务器响应的最大连接数
2. show global status like 'Max_used_connections';

复制代码

• MySQL服务器过去的最大连接数是62，没有达到服务器连接数上限5275，应该没有出现1040错误，比较理想的设置是
  

1. Max_used_connections / max_connections * 100% ≈ 85%

复制代码

• 最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。这台服务器设置连接上限有些偏高。
  

临时表

1. # 创建临时表
2. show global status like 'created_tmp%'; 

复制代码

• 每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加。
  
• Created_tmp_files：表示MySQL服务创建的临时文件文件数，比较理想的配置是：
  

1. Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

复制代码

1. # 打开表的数量
2. show global status like 'open%tables%'; 

复制代码

• 比较合适的设置：
  

1. # table高速缓存的数量
2. show variables like 'table_open_cache'; 

复制代码

• Table_locks_immediate：表示立即释放表锁数，Table_locks_waited：表示需要等待的表锁数。
  
• 如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。示例中的服务器Table_locks_immediate / Table_locks_waited = 235，MyISAM就足够了。
  

表扫描情况

1. Open_tables / Opened_tables * 100% >= 85%
2. Open_tables / table_cache * 100% <= 95%

复制代码

1. # 查看MySQL服务器的线程信息
2. show global status like 'Thread%'; 

复制代码

• 计算表扫描率：
  　　表扫描率 = Handler_read_rnd_next / Com_select
  　　如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。
  
• 上面服务器中表扫描率=20411
  

出处：https://www.cnblogs.com/LoveBB/本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。
来源:https://www.cnblogs.com/LoveBB/p/17195702.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！