mysql profile 查谒_MYSQL查询优化：profile功能

mysql> usetestDatabasechanged

mysql> set profiling=1;

Query OK,0 rows affected (0.00sec)

mysql>show tables;+----------------+

| Tables_in_test |

+----------------+

| bag_item |

| bag_user |

| score |

| t |

+----------------+

4 rows in set (0.03sec)

mysql> select count(*) fromt;+----------+

| count(*) |

+----------+

| 2097152 |

+----------+

1 row in set (0.74sec)

mysql>show profiles;+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

+----------+------------+------------------------+

2 rows in set (0.00sec)

mysql> show profile for query 2;+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| (initialization) | 0.000004 |

| checking query cache for query | 0.000044 |

| Opening tables | 0.000012 |

| System lock | 0.000017 |

| Table lock | 0.00003 |

| init | 0.000013 |

| optimizing | 0.000008 |

| statistics | 0.000013 |

| preparing | 0.000011 |

| executing | 0.000006 |

| Sending data | 0.747313 |

| end | 0.000014 |

| query end | 0.000006 |

| storing result in query cache | 0.000006 |

| freeing items | 0.000012 |

| closing tables | 0.000009 |

| logging slow query | 0.000183 |

+--------------------------------+----------+

17 rows in set (0.00sec)

mysql> show profile block io,cpu for query 2;+--------------------------------+----------+----------+------------+--------------+---------------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+--------------------------------+----------+----------+------------+--------------+---------------+

| (initialization) | 0.000004 | 0 | 0 | 0 | 0 |

| checking query cache for query | 0.000044 | 0 | 0 | 0 | 0 |

| Opening tables | 0.000012 | 0 | 0 | 0 | 0 |

| System lock | 0.000017 | 0 | 0 | 0 | 0 |

| Table lock | 0.00003 | 0 | 0 | 0 | 0 |

| init | 0.000013 | 0 | 0 | 0 | 0 |

| optimizing | 0.000008 | 0 | 0 | 0 | 0 |

| statistics | 0.000013 | 0 | 0 | 0 | 0 |

| preparing | 0.000011 | 0 | 0 | 0 | 0 |

| executing | 0.000006 | 0 | 0 | 0 | 0 |

| Sending data | 0.747313 | 0.746887 | 0 | 0 | 0 |

| end | 0.000014 | 0 | 0 | 0 | 0 |

| query end | 0.000006 | 0 | 0 | 0 | 0 |

| storing result in query cache | 0.000006 | 0 | 0 | 0 | 0 |

| freeing items | 0.000012 | 0 | 0 | 0 | 0 |

| closing tables | 0.000009 | 0 | 0 | 0 | 0 |

| logging slow query | 0.000183 | 0 | 0 | 0 | 0 |

+--------------------------------+----------+----------+------------+--------------+---------------+

17 rows in set (0.00sec)

mysql> insert into t(username) select username fromt;

Query OK,2097152 rows affected (34.17sec)

Records:2097152 Duplicates: 0 Warnings: 0mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

+----------+-------------+------------------------------------------------+

4 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps for query 4;

mysql> select count(*) fromt;+----------+

| count(*) |

+----------+

| 4194304 |

+----------+

1 row in set (1.51sec)

mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

| 5 | 1.50563800 | select count(*) from t |

+----------+-------------+------------------------------------------------+

5 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 5;

……

mysql> update t set username = 'waill';

Query OK,4194304 rows affected (44.82sec)

Rows matched:4194304 Changed: 4194304 Warnings: 0mysql>show profiles;+----------+-------------+------------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+------------------------------------------------+

| 1 | 0.02717000 | show tables |

| 2 | 0.74770100 | select count(*) from t |

| 3 | 0.00004200 | show prifile for query 2 |

| 4 | 34.30410100 | insert into t(username) select username from t |

| 5 | 1.50563800 | select count(*) from t |

| 6 | 44.82054700 | update t set username = 'waill' |

+----------+-------------+------------------------------------------------+

6 rows in set (0.00sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;