下载  Example Databases :http://dev.mysql.com/doc/index-other.html  sakila-db.zip

mysql> show status like "Com_select"; show status like "Com_insert"; show status like "Com_update"; show status like "Com_delete";

mysql> 

mysql> show status like "Innodb_rows_%";

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

| Variable_name        | Value |

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

| Innodb_rows_deleted  | 0     |

| Innodb_rows_inserted | 47280 |

| Innodb_rows_read     | 62    |

| Innodb_rows_updated  | 0     |

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

4 rows in set (0.00 sec)

mysql> 

mysql> show status like "Connections";show status like "Uptime"; show status like "Slow_%";

mysql> explain select * from  customer where customer_id=130;     


mysql> select @@have_profiling;

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

| @@have_profiling |

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

| YES              |

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

1 row in set, 1 warning (0.00 sec)

mysql> 

mysql> select @@profiling;

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

| @@profiling |

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

|           0 |

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

1 row in set, 1 warning (0.00 sec)


mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> select @@profiling;

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

| @@profiling |

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

|           1 |

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

1 row in set, 1 warning (0.00 sec)


mysql> 

mysql> select count(*) from payment;

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

| count(*) |

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

|    16049 |

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

1 row in set (0.00 sec)


mysql> show profiles;

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

| Query_ID | Duration   | Query                        |

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

|        1 | 0.00022100 | select @@profiling           |

|        2 | 0.00073525 | show tables                  |

|        3 | 0.00203800 | select count(*) from payment |

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

3 rows in set, 1 warning (0.00 sec)


mysql> show profile for query 3;

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

| Status               | Duration |

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

| starting             | 0.000032 |

| checking permissions | 0.000003 |

| Opening tables       | 0.000011 |

| init                 | 0.000007 |

| System lock          | 0.000005 |

| optimizing           | 0.000007 |

| statistics           | 0.000007 |

| preparing            | 0.000008 |

| executing            | 0.000001 |

| Sending data         | 0.001884 |

| end                  | 0.000003 |

| query end            | 0.000003 |

| closing tables       | 0.000005 |

| freeing items        | 0.000056 |

| cleaning up          | 0.000008 |

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

15 rows in set, 1 warning (0.00 sec)


mysql> 


mysql> show profile all  for query 3;

mysql> show profile cpu  for query 3;  


mysql> create table payment_myisam like payment;

Query OK, 0 rows affected (0.43 sec)


mysql> alter table payment_myisam engine=myisam;

Query OK, 0 rows affected (0.41 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into payment_myisam select * from payment; 

Query OK, 16049 rows affected, 1 warning (0.05 sec)

Records: 16049  Duplicates: 0  Warnings: 1


mysql> 

mysql> select count(*) from payment_myisam;

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

| count(*) |

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

|    16049 |

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

1 row in set (0.00 sec)


mysql> show profiles;

mysql> show profile for query 14;

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

| Status               | Duration |

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

| starting             | 0.000083 |

| checking permissions | 0.000011 |

| Opening tables       | 0.000028 |

| init                 | 0.000021 |

| System lock          | 0.000017 |

| optimizing           | 0.000012 |

| executing            | 0.000016 |

| end                  | 0.000006 |

| query end            | 0.000005 |

| closing tables       | 0.000065 |

| freeing items        | 0.000021 |

| cleaning up          | 0.000043 |

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

12 rows in set, 1 warning (0.00 sec)



mysql> show profile  source for query 14;

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

| Status               | Duration | Source_function       | Source_file      | Source_line |

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

| starting             | 0.000083 | NULL                  | NULL             |        NULL |

| checking permissions | 0.000011 | check_access          | sql_parse.cc     |        5296 |

| Opening tables       | 0.000028 | open_tables           | sql_base.cc      |        5094 |

| init                 | 0.000021 | mysql_prepare_select  | sql_select.cc    |        1051 |

| System lock          | 0.000017 | mysql_lock_tables     | lock.cc          |         304 |

| optimizing           | 0.000012 | optimize              | sql_optimizer.cc |         138 |

| executing            | 0.000016 | exec                  | sql_executor.cc  |         110 |

| end                  | 0.000006 | mysql_execute_select  | sql_select.cc    |        1106 |

| query end            | 0.000005 | mysql_execute_command | sql_parse.cc     |        4995 |

| closing tables       | 0.000065 | mysql_execute_command | sql_parse.cc     |        5043 |

| freeing items        | 0.000021 | mysql_parse           | sql_parse.cc     |        6432 |

| cleaning up          | 0.000043 | dispatch_command      | sql_parse.cc     |        1777 |

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

12 rows in set, 1 warning (0.00 sec)


mysql>