MySQL优化2-通过show status查看各种sql的执行频率

2019独角兽企业重金招聘Python工程师标准>>>

通过下面的命令我们可以查看各种sql的执行频率，'Com_xxx'代表每个xxx语句执行的次数。如下面的代码，下面一共有142行被选择。下面的参数对所有引擎的表操作都会进行累加。通常我们只关心下面几个统计参数。

1. Com_select,执行select的次数，一次查询只累加1
2. Com_insert执行insert的次数，对于批量插入的insert只累加1次
3. Com_update执行update的次数
4. Com_delete，执行delete的次数，
5. Com_commit事务的提交情况
6. Com_rollback事务的回滚情况

mysql> show status like 'com%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Com_admin_commands        | 0     |
| Com_assign_to_keycache    | 0     |
| Com_alter_db              | 0     |
| Com_alter_db_upgrade      | 0     |
| Com_alter_event           | 0     |
| Com_alter_function        | 0     |
| Com_alter_procedure       | 0     |
| Com_alter_server          | 0     |
| Com_alter_table           | 0     |
| Com_alter_tablespace      | 0     |
| Com_alter_user            | 0     |
| Com_analyze               | 0     |
| Com_begin                 | 0     |
| Com_binlog                | 0     |
| Com_call_procedure        | 0     |
| Com_change_db             | 0     |
| Com_change_master         | 0     |
| Com_check                 | 0     |
| Com_checksum              | 0     |
| Com_commit                | 0     |
| Com_create_db             | 0     |
| Com_create_event          | 0     |
| Com_create_function       | 0     |
| Com_create_index          | 0     |
| Com_create_procedure      | 0     |
| Com_create_server         | 0     |
| Com_create_table          | 0     |
| Com_create_trigger        | 0     |
| Com_create_udf            | 0     |
| Com_create_user           | 0     |
| Com_create_view           | 0     |
| Com_dealloc_sql           | 0     |
| Com_delete                | 0     |
| Com_delete_multi          | 0     |
| Com_do                    | 0     |
| Com_drop_db               | 0     |
| Com_drop_event            | 0     |
| Com_drop_function         | 0     |
| Com_drop_index            | 0     |
| Com_drop_procedure        | 0     |
| Com_drop_server           | 0     |
| Com_drop_table            | 0     |
| Com_drop_trigger          | 0     |
| Com_drop_user             | 0     |
| Com_drop_view             | 0     |
| Com_empty_query           | 0     |
| Com_execute_sql           | 0     |
| Com_flush                 | 0     |
| Com_get_diagnostics       | 0     |
| Com_grant                 | 0     |
| Com_ha_close              | 0     |
| Com_ha_open               | 0     |
| Com_ha_read               | 0     |
| Com_help                  | 0     |
| Com_insert                | 0     |
| Com_insert_select         | 0     |
| Com_install_plugin        | 0     |
| Com_kill                  | 0     |
| Com_load                  | 0     |
| Com_lock_tables           | 0     |
| Com_optimize              | 0     |
| Com_preload_keys          | 0     |
| Com_prepare_sql           | 0     |
| Com_purge                 | 0     |
| Com_purge_before_date     | 0     |
| Com_release_savepoint     | 0     |
| Com_rename_table          | 0     |
| Com_rename_user           | 0     |
| Com_repair                | 0     |
| Com_replace               | 0     |
| Com_replace_select        | 0     |
| Com_reset                 | 0     |
| Com_resignal              | 0     |
| Com_revoke                | 0     |
| Com_revoke_all            | 0     |
| Com_rollback              | 0     |
| Com_rollback_to_savepoint | 0     |
| Com_savepoint             | 0     |
| Com_select                | 1     |
| Com_set_option            | 0     |
| Com_signal                | 0     |
| Com_show_binlog_events    | 0     |
| Com_show_binlogs          | 0     |
| Com_show_charsets         | 0     |
| Com_show_collations       | 0     |
| Com_show_create_db        | 0     |
| Com_show_create_event     | 0     |
| Com_show_create_func      | 0     |
| Com_show_create_proc      | 0     |
| Com_show_create_table     | 0     |
| Com_show_create_trigger   | 0     |
| Com_show_databases        | 0     |
| Com_show_engine_logs      | 0     |
| Com_show_engine_mutex     | 0     |
| Com_show_engine_status    | 0     |
| Com_show_events           | 0     |
| Com_show_errors           | 0     |
| Com_show_fields           | 0     |
| Com_show_function_code    | 0     |
| Com_show_function_status  | 0     |
| Com_show_grants           | 0     |
| Com_show_keys             | 0     |
| Com_show_master_status    | 0     |
| Com_show_open_tables      | 0     |
| Com_show_plugins          | 0     |
| Com_show_privileges       | 0     |
| Com_show_procedure_code   | 0     |
| Com_show_procedure_status | 0     |
| Com_show_processlist      | 0     |
| Com_show_profile          | 0     |
| Com_show_profiles         | 0     |
| Com_show_relaylog_events  | 0     |
| Com_show_slave_hosts      | 0     |
| Com_show_slave_status     | 0     |
| Com_show_status           | 1     |
| Com_show_storage_engines  | 0     |
| Com_show_table_status     | 0     |
| Com_show_tables           | 0     |
| Com_show_triggers         | 0     |
| Com_show_variables        | 0     |
| Com_show_warnings         | 0     |
| Com_slave_start           | 0     |
| Com_slave_stop            | 0     |
| Com_stmt_close            | 0     |
| Com_stmt_execute          | 0     |
| Com_stmt_fetch            | 0     |
| Com_stmt_prepare          | 0     |
| Com_stmt_reprepare        | 0     |
| Com_stmt_reset            | 0     |
| Com_stmt_send_long_data   | 0     |
| Com_truncate              | 0     |
| Com_uninstall_plugin      | 0     |
| Com_unlock_tables         | 0     |
| Com_update                | 0     |
| Com_update_multi          | 0     |
| Com_xa_commit             | 0     |
| Com_xa_end                | 0     |
| Com_xa_prepare            | 0     |
| Com_xa_recover            | 0     |
| Com_xa_rollback           | 0     |
| Com_xa_start              | 0     |
| Compression               | OFF   |
+---------------------------+-------+
142 rows in set (0.00 sec)

  对于InnoDB我们还应该查询下面的参数

mysql> show status like 'Innodb_rows%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 1     |
| Innodb_rows_inserted | 49    |
| Innodb_rows_read     | 81    |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

1.  Innodb_rows_read:select 查询返回的行数
2. Innodb_rows_inserted:执行insert插入的行数
3. Innodb_rows_updated执行update更新的行数
4. Innodb_rows_deleted执行delete删除的行数

  同时我们还可以通过Connections了解试图连接MySQL的次数，Uptime服务器工作时间，Slow_queries慢查询的次数。

转载于:https://my.oschina.net/jettyWang/blog/795039