前言
Github:https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql
概述
通过 SHOW [session|global] STATUS LIKE 'Com_%'; 查看各种 sql 的执行频率。
默认使用 session 级别,针对的是当前会话,而如果指定 global,统计的结果是从数据库上次启动至今的范围。
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_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 1 |
| 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 |
.......太长了,有140个
上面查看session级别看到的value看不出效果,我们再看看global级别:
mysql> show global status like 'Com_%';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| Com_admin_commands | 913 |
| 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 | 423 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 13217 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 29016 |
| Com_create_db | 1 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 242 |
| Com_create_trigger | 3 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 514 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 224 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 13 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 762923 |
| Com_insert_select | 686 |
| Com_install_plugin | 0 |
| Com_kill | 8 |
| Com_load | 0 |
| Com_lock_tables | 489 |
| 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 | 200 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 157199708 |
| Com_set_option | 306160345 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 18 |
| Com_show_collations | 76501796 |
| Com_show_contributors | 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 | 12620 |
| Com_show_create_trigger | 171 |
| Com_show_databases | 374 |
| 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 | 13882 |
| Com_show_function_status | 177 |
| Com_show_grants | 0 |
| Com_show_keys | 304 |
| Com_show_master_status | 1 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 177 |
| 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 | 1536 |
| Com_show_storage_engines | 40 |
| Com_show_table_status | 10891 |
| Com_show_tables | 95471121 |
| Com_show_triggers | 10456 |
| Com_show_variables | 76503375 |
| 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 | 489 |
| Com_update | 524185 |
| Com_update_multi | 364 |
| 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 |
+---------------------------+-----------+
140 rows in set
除了增删改查外,还包括存储过程,事务提交,回滚,表锁等情况的分析。
从对应的 value 值,可以知道每种语句执行的次数,一般情况下,最关心的语句如下:
Com_insert:执行insert的操作次数,插入一次,累加一次,对于批量插入,也只能算一次
Com_delete:执行delete的操作次数
Com_update:执行update的操作次数
Com_select:执行select的操作次数,查询一次,累加一次
我们通过这些参数,能够快速的了解到平台对数据库应用以什么为主,在时间有限的优化条件下,
可以更合理的处理工作计划问题。对于更新操作,特别注意的是,如果进行了更新,事务回滚了,
也会累加一次,通过 Com_commit,Com_rollback 参数能分析出提交和回滚的状态,如果发现回滚
次数太多,这个时候一般是我们的程序编写有问题,导致程序操作数据库时,总是出现事务的回滚,
或许要从程序入手,寻找问题原因和解决办法。