热门标签
热门文章
- 1【ElasticSearch】中文分词器_es中文分词器
- 2鸿蒙-webview的使用和JS交互（附源码）_华为webview学习
- 3arm64交叉编译环境的搭建_arm交叉编译环境搭建
- 4uniapp之小程序设置二维码，扫码点餐（已通过测试上线）_uniapp 扫码点餐
- 5Linux操作系统（三）：详解Linux及2023年gcc/g++ 多版本间切换_linux 内核版本 gcc
- 6基于javaweb+mysql的springboot小区物业管理系统(java+springboot+html+layui+mysql)_java物业管理系统
- 7滑动窗口题目(难题)
- 8java中文分词工具_【分词】从why到how的中文分词详解，从算法原理到开源工具...
- 9微信小程序自动化测试框架 minitest_mini test
- 10机器学习ML笔记_deoldify测试集
当前位置: article > 正文
MySQL学习笔记之SQL语句执行过程查看_浏览器sql语句怎么查看
作者：我家自动化 | 2024-03-21 04:42:58
赞
踩
浏览器sql语句怎么查看
参数使能
以select语句为例，首先打开profile参数：
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 1
- 2
然后执行两边下面的语句：
mysql> select * from employees;
- 1
查看最近一条SQL执行过程
可以通过show profile语句查看最近一条SQL的执行过程：
mysql> show profile; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000039 | | checking permissions | 0.000004 | | Opening tables | 0.000012 | | init | 0.000014 | | System lock | 0.000006 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000188 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000008 | | freeing items | 0.000104 | | cleaning up | 0.000013 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
可以看到一条SQL语句要经历上述15步。如果开启了SQL缓存且命中缓存的话，步骤会减少，但SQL缓存要求两条SQL必须完全一样才能命中，任何字符的更改(包括注释、空格等)都会导致缓存没命中，因此MySQL的缓存相当鸡肋，命中率很低。
查看profiling打开开后，所有SQL语句执行耗时
可以通过show profiles开启profiling后所有SQL语句的耗时：
mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------+
| 1 | 0.00070175 | select * from employees |
| 2 | 0.00041950 | select * from employees |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
查看某一条SQL的执行过程
可以通过show profile for query Query_ID查看：
mysql> show profile for query 1; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000036 | | checking permissions | 0.000004 | | Opening tables | 0.000012 | | init | 0.000013 | | System lock | 0.000006 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000355 | | executing | 0.000006 | | Sending data | 0.000169 | | end | 0.000003 | | query end | 0.000005 | | closing tables | 0.000006 | | freeing items | 0.000070 | | cleaning up | 0.000007 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec) mysql> show profile for query 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000039 | | checking permissions | 0.000004 | | Opening tables | 0.000012 | | init | 0.000014 | | System lock | 0.000006 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000188 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000008 | | freeing items | 0.000104 | | cleaning up | 0.000013 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
指定要查看的性能选项
mysql> show profile CPU, block io;
+---------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+---------------+----------+----------+------------+--------------+---------------+
| starting | 0.000066 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000058 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
+---------------+----------+----------+------------+--------------+---------------+
3 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查看所有性能选项
mysql> show profile all for query 1\G *************************** 1. row *************************** Status: starting Duration: 0.000036 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: NULL Source_file: NULL Source_line: NULL *************************** 2. row *************************** Status: checking permissions Duration: 0.000004 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: check_access Source_file: sql_authorization.cc Source_line: 802 *************************** 3. row *************************** Status: Opening tables Duration: 0.000012 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: open_tables Source_file: sql_base.cc Source_line: 5714 *************************** 4. row *************************** Status: init Duration: 0.000013 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: handle_query Source_file: sql_select.cc Source_line: 121 *************************** 5. row *************************** Status: System lock Duration: 0.000006 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_lock_tables Source_file: lock.cc Source_line: 323 *************************** 6. row *************************** Status: optimizing Duration: 0.000002 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::optimize Source_file: sql_optimizer.cc Source_line: 151 *************************** 7. row *************************** Status: statistics Duration: 0.000008 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::optimize Source_file: sql_optimizer.cc Source_line: 367 *************************** 8. row *************************** Status: preparing Duration: 0.000355 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::optimize Source_file: sql_optimizer.cc Source_line: 475 *************************** 9. row *************************** Status: executing Duration: 0.000006 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::exec Source_file: sql_executor.cc Source_line: 119 *************************** 10. row *************************** Status: Sending data Duration: 0.000169 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::exec Source_file: sql_executor.cc Source_line: 195 *************************** 11. row *************************** Status: end Duration: 0.000003 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: handle_query Source_file: sql_select.cc Source_line: 199 *************************** 12. row *************************** Status: query end Duration: 0.000005 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 4946 *************************** 13. row *************************** Status: closing tables Duration: 0.000006 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 4998 *************************** 14. row *************************** Status: freeing items Duration: 0.000070 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_parse Source_file: sql_parse.cc Source_line: 5610 *************************** 15. row *************************** Status: cleaning up Duration: 0.000007 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: dispatch_command Source_file: sql_parse.cc Source_line: 1924 15 rows in set, 1 warning (0.00 sec) 举其中的一行为例： *************************** 10. row *************************** Status: Sending data Duration: 0.000169 CPU_user: 0.000000 CPU_system: 0.000000 Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: JOIN::exec Source_file: sql_executor.cc Source_line: 195
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
每一行数据就显示该步骤下的各个阶段的耗时，甚至源文件等信息。
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/278529?site
推荐阅读
相关标签
