MySQL使用使用profile查询性能的操作教程查询性能的操作教程
MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。
查看profile是否开启
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF | --开启SQL语句剖析功能
| profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling
+------------------------+-------+
2 rows in set (0.00 sec)
基于会话级别开启
mysql> set profiling = 1; --关闭则用set profiling = off
Query OK, 0 rows affected (0.00 sec)
mysql> select distinct d.account,a.server_id from tab_appserver_user a
-> inner join tab_department_parent b on a.key_id = b.parent_id
-> inner join tab_department_member c on b.department_id = c.department_id and c.state=1
-> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1
-> where a.type=1
-> union
-> select distinct b.account,a.server_id from tab_appserver_user a
-> inner join tab_user_info b on a.key_id = b.user_id and b.state=1
-> where a.type=0;
查看是否设置生效：
select @@profiling;
默认是0，设置成功是1
运行SQL语句：
mysql> select * FROM hx_line WHERE id = '1455023';
查看profiles
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00036150 | select * FROM hx_line WHERE id = '1455023' |
+----------+------------+---------------------------------------------+
查看具体某条的profile
mysql> show profile FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000013 |
| Waiting for query cache lock | 0.000014 |
| checking query cache for query | 0.000038 |
| checking permissions | 0.000006 |
| Opening tables | 0.000013 |
| System lock | 0.000009 |
资源评论
weixin_38612095
- 粉丝: 10
- 资源: 921
最新资源
- Node-Red语音识别节点microphone
- YOLOv6-main.zip
- 【Unity 3D 模型资源包】Stylized Viking Hut 快速创建维京风格环境或建筑
- 鸿蒙HarmonyOS端云一体化开发实践视频.zip
- 5号任浩宇，创新创业作业.docx
- 【Unity对话和任务管理插件】Dialogue and Quests 灵活的对话系统，轻松创建对话
- k8s命令详细教程大大是的
- 基于Java的运动赛事管理系统
- 【Unity 资源管理插件】Asset Inventory 2 高效组织、搜索、管理各种资源，提高工作效率
- 【 Unity网格优化插件】MeshFusion Pro: Ultimate Optimization Tool 优化 3D 模
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
