查看Profile是否开启
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set
# 如关闭,则开启
mysql> set profiling = on;
Query OK, 0 rows affected
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set
分析Profile
# 首先执行几条SQL
# 查看Profiles
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 9.75E-5 | like |
| 2 | 0.00042825 | show variables like 'profiling' |
| 3 | 0.08572125 | select * from a |
| 4 | 1.8947365 | select * from do |
| 5 | 0.0015365 | select * from t_wechat_customer_msg |
| 6 | 8.02308925 | select * from do group by rand() |
+----------+------------+-------------------------------------+
6 rows in set
# 分析耗时较少的select查询
mysql> show profile cpu,block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 4.2E-5 | 0 | 0 | NULL | NULL |
| checking permissions | 5E-6 | 0 | 0 | NULL | NULL |
| Opening tables | 0.001203 | 0 | 0 | NULL | NULL |
| System lock | 9E-6 | 0 | 0 | NULL | NULL |
| init | 1.2E-5 | 0 | 0 | NULL | NULL |
| optimizing | 5E-6 | 0 | 0 | NULL | NULL |
| statistics | 6E-6 | 0 | 0 | NULL | NULL |
| preparing | 6E-6 | 0 | 0 | NULL | NULL |
| executing | 1E-6 | 0 | 0 | NULL | NULL |
| Sending data | 4.4E-5 | 0 | 0 | NULL | NULL |
| end | 3E-6 | 0 | 0 | NULL | NULL |
| query end | 3E-6 | 0 | 0 | NULL | NULL |
| closing tables | 5E-6 | 0 | 0 | NULL | NULL |
| freeing items | 0.00019 | 0 | 0 | NULL | NULL |
| logging slow query | 2E-6 | 0 | 0 | NULL | NULL |
| cleaning up | 1E-6 | 0 | 0 | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set
# 分析耗时较多的select查询
mysql> show profile cpu,block io for query 6;
+------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+------------------------------+----------+----------+------------+--------------+---------------+
| starting | 6.6E-5 | 0 | 0 | NULL | NULL |
| checking permissions | 8E-6 | 0 | 0 | NULL | NULL |
| Opening tables | 0.001002 | 0 | 0 | NULL | NULL |
| System lock | 1.2E-5 | 0 | 0 | NULL | NULL |
| init | 1.4E-5 | 0 | 0 | NULL | NULL |
| optimizing | 4E-6 | 0 | 0 | NULL | NULL |
| statistics | 7E-6 | 0 | 0 | NULL | NULL |
| preparing | 8E-6 | 0 | 0 | NULL | NULL |
| Creating tmp table | 5.8E-5 | 0 | 0 | NULL | NULL |
| executing | 3E-6 | 0 | 0 | NULL | NULL |
| Copying to tmp table | 0.324864 | 0.327602 | 0 | NULL | NULL |
| converting HEAP to MyISAM | 0.860471 | 0.858006 | 0 | NULL | NULL |
| Copying to tmp table on disk | 4.042798 | 4.009226 | 0.0156 | NULL | NULL |
| Sorting result | 0.698543 | 0.452403 | 0.156001 | NULL | NULL |
| Sending data | 1.887769 | 0.436803 | 1.450809 | NULL | NULL |
| end | 8E-6 | 0 | 0 | NULL | NULL |
| removing tmp table | 0.207213 | 0 | 0.0156 | NULL | NULL |
| end | 1.6E-5 | 0 | 0 | NULL | NULL |
| query end | 3E-6 | 0 | 0 | NULL | NULL |
| closing tables | 7E-6 | 0 | 0 | NULL | NULL |
| freeing items | 0.000215 | 0 | 0 | NULL | NULL |
| logging slow query | 2E-6 | 0 | 0 | NULL | NULL |
| cleaning up | 2E-6 | 0 | 0 | NULL | NULL |
+------------------------------+----------+----------+------------+--------------+---------------+
23 rows in set
几种拖慢速度的Status
# converting HEAP to MyISAM
查询结果太大,内存不够使用,要使用磁盘
# Copying to tmp table
创建临时表
1:创建临时表--Creating tmp table
2:拷贝数据到临时表--Copying to tmp table
3:用完后删除临时表--removing tmp table
# Copying to tmp table on disk
把内存中的临时表复制到磁盘,会严重拖慢SQL执行速度
# locked
锁表
profile几种参数类型
profile参数
