原因

  • 想通过show global status来完成对MySQL关键指标的采集,但是不知道这个命令频繁执行是否会造成性能压力,网上也没有搜到相关的资料。mysql_exporter工具也是使用的这个命令。

结论

取出值的时候可能对心痛有影响

思路

查看5.7源码

vim mysql-server-mysql-5.7.21/sql/mysqld.cc
/// 从6801行开始定义了输出的性能指标结构体 /// 结构体定义可以简单理解为 /* struct st_mysql_show_var { const char *name; char *value; enum enum_mysql_show_type type; }; */ /* Variables shown by SHOW STATUS in alphabetical order */ SHOW_VAR status_vars[]= { {"Aborted_clients", (char*) &aborted_threads, SHOW_LONG, SHOW_SCOPE_GLOBAL}, #ifndef EMBEDDED_LIBRARY {"Aborted_connects", (char*) &show_aborted_connects, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif #ifdef HAVE_REPLICATION #ifndef DBUG_OFF {"Ongoing_anonymous_gtid_violating_transaction_count",(char*) &show_ongoing_anonymous_gtid_violating_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif//!DBUG_OFF {"Ongoing_anonymous_transaction_count",(char*) &show_ongoing_anonymous_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #ifndef DBUG_OFF {"Ongoing_automatic_gtid_violating_transaction_count",(char*) &show_ongoing_automatic_gtid_violating_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif//!DBUG_OFF #endif//HAVE_REPLICATION {"Binlog_cache_disk_use", (char*) &binlog_cache_disk_use, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Binlog_cache_use", (char*) &binlog_cache_use, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Binlog_stmt_cache_disk_use",(char*) &binlog_stmt_cache_disk_use, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Binlog_stmt_cache_use", (char*) &binlog_stmt_cache_use, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Bytes_received", (char*) offsetof(STATUS_VAR, bytes_received), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Bytes_sent", (char*) offsetof(STATUS_VAR, bytes_sent), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Com", (char*) com_status_vars, SHOW_ARRAY, SHOW_SCOPE_ALL}, {"Com_stmt_reprepare", (char*) offsetof(STATUS_VAR, com_stmt_reprepare), SHOW_LONG_STATUS, SHOW_SCOPE_ALL}, {"Compression", (char*) &show_net_compression, SHOW_FUNC, SHOW_SCOPE_SESSION}, {"Connections", (char*) &show_thread_id_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #ifndef EMBEDDED_LIBRARY {"Connection_errors_accept", (char*) &show_connection_errors_accept, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Connection_errors_internal", (char*) &connection_errors_internal, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Connection_errors_max_connections", (char*) &show_connection_errors_max_connection, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Connection_errors_peer_address", (char*) &connection_errors_peer_addr, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Connection_errors_select", (char*) &show_connection_errors_select, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Connection_errors_tcpwrap", (char*) &show_connection_errors_tcpwrap, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif {"Created_tmp_disk_tables", (char*) offsetof(STATUS_VAR, created_tmp_disk_tables), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Created_tmp_files", (char*) &my_tmp_file_created, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Created_tmp_tables", (char*) offsetof(STATUS_VAR, created_tmp_tables), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Delayed_errors", (char*) &delayed_insert_errors, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Delayed_insert_threads", (char*) &delayed_insert_threads, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Delayed_writes", (char*) &delayed_insert_writes, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Flush_commands", (char*) &refresh_version, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Handler_commit", (char*) offsetof(STATUS_VAR, ha_commit_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_delete", (char*) offsetof(STATUS_VAR, ha_delete_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_discover", (char*) offsetof(STATUS_VAR, ha_discover_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_external_lock", (char*) offsetof(STATUS_VAR, ha_external_lock_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_mrr_init", (char*) offsetof(STATUS_VAR, ha_multi_range_read_init_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_prepare", (char*) offsetof(STATUS_VAR, ha_prepare_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_first", (char*) offsetof(STATUS_VAR, ha_read_first_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_key", (char*) offsetof(STATUS_VAR, ha_read_key_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_last", (char*) offsetof(STATUS_VAR, ha_read_last_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_next", (char*) offsetof(STATUS_VAR, ha_read_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_prev", (char*) offsetof(STATUS_VAR, ha_read_prev_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_rnd", (char*) offsetof(STATUS_VAR, ha_read_rnd_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_rnd_next", (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_rollback", (char*) offsetof(STATUS_VAR, ha_rollback_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_savepoint", (char*) offsetof(STATUS_VAR, ha_savepoint_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_savepoint_rollback",(char*) offsetof(STATUS_VAR, ha_savepoint_rollback_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_update", (char*) offsetof(STATUS_VAR, ha_update_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_write", (char*) offsetof(STATUS_VAR, ha_write_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Key_blocks_not_flushed", (char*) offsetof(KEY_CACHE, global_blocks_changed), SHOW_KEY_CACHE_LONG, SHOW_SCOPE_GLOBAL}, {"Key_blocks_unused", (char*) offsetof(KEY_CACHE, blocks_unused), SHOW_KEY_CACHE_LONG, SHOW_SCOPE_GLOBAL}, {"Key_blocks_used", (char*) offsetof(KEY_CACHE, blocks_used), SHOW_KEY_CACHE_LONG, SHOW_SCOPE_GLOBAL}, {"Key_read_requests", (char*) offsetof(KEY_CACHE, global_cache_r_requests), SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL}, {"Key_reads", (char*) offsetof(KEY_CACHE, global_cache_read), SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL}, {"Key_write_requests", (char*) offsetof(KEY_CACHE, global_cache_w_requests), SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL}, {"Key_writes", (char*) offsetof(KEY_CACHE, global_cache_write), SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL}, {"Last_query_cost", (char*) offsetof(STATUS_VAR, last_query_cost), SHOW_DOUBLE_STATUS, SHOW_SCOPE_SESSION}, {"Last_query_partial_plans", (char*) offsetof(STATUS_VAR, last_query_partial_plans),SHOW_LONGLONG_STATUS, SHOW_SCOPE_SESSION}, #ifndef EMBEDDED_LIBRARY {"Locked_connects", (char*) &locked_account_connection_count, SHOW_LONG, SHOW_SCOPE_GLOBAL}, #endif {"Max_execution_time_exceeded", (char*) offsetof(STATUS_VAR, max_execution_time_exceeded), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Max_execution_time_set", (char*) offsetof(STATUS_VAR, max_execution_time_set), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Max_execution_time_set_failed", (char*) offsetof(STATUS_VAR, max_execution_time_set_failed), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Max_used_connections", (char*) &Connection_handler_manager::max_used_connections, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Max_used_connections_time",(char*) &show_max_used_connections_time, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Not_flushed_delayed_rows", (char*) &delayed_rows_in_use, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Open_files", (char*) &my_file_opened, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Open_streams", (char*) &my_stream_opened, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Open_table_definitions", (char*) &show_table_definitions, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Open_tables", (char*) &show_open_tables, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Opened_files", (char*) &my_file_total_opened, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Opened_tables", (char*) offsetof(STATUS_VAR, opened_tables), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Opened_table_definitions", (char*) offsetof(STATUS_VAR, opened_shares), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Prepared_stmt_count", (char*) &show_prepared_stmt_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Qcache_free_blocks", (char*) &query_cache.free_memory_blocks, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Qcache_free_memory", (char*) &query_cache.free_memory, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Qcache_hits", (char*) &query_cache.hits, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Qcache_inserts", (char*) &query_cache.inserts, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Qcache_lowmem_prunes", (char*) &query_cache.lowmem_prunes, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Qcache_not_cached", (char*) &query_cache.refused, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Qcache_queries_in_cache", (char*) &query_cache.queries_in_cache, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Qcache_total_blocks", (char*) &query_cache.total_blocks, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Queries", (char*) &show_queries, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Questions", (char*) offsetof(STATUS_VAR, questions), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Select_full_join", (char*) offsetof(STATUS_VAR, select_full_join_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Select_full_range_join", (char*) offsetof(STATUS_VAR, select_full_range_join_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Select_range", (char*) offsetof(STATUS_VAR, select_range_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Select_range_check", (char*) offsetof(STATUS_VAR, select_range_check_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Select_scan", (char*) offsetof(STATUS_VAR, select_scan_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Slave_open_temp_tables", (char*) &show_slave_open_temp_tables, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #ifdef HAVE_REPLICATION {"Slave_retried_transactions",(char*) &show_slave_retried_trans, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Slave_heartbeat_period", (char*) &show_heartbeat_period, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Slave_received_heartbeats",(char*) &show_slave_received_heartbeats, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Slave_last_heartbeat", (char*) &show_slave_last_heartbeat, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #ifndef DBUG_OFF {"Slave_rows_last_search_algorithm_used",(char*) &show_slave_rows_last_search_algorithm_used, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif {"Slave_running", (char*) &show_slave_running, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif #ifndef EMBEDDED_LIBRARY {"Slow_launch_threads", (char*) &Per_thread_connection_handler::slow_launch_threads, SHOW_LONG, SHOW_SCOPE_ALL}, #endif {"Slow_queries", (char*) offsetof(STATUS_VAR, long_query_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Sort_merge_passes", (char*) offsetof(STATUS_VAR, filesort_merge_passes), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Sort_range", (char*) offsetof(STATUS_VAR, filesort_range_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Sort_rows", (char*) offsetof(STATUS_VAR, filesort_rows), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Sort_scan", (char*) offsetof(STATUS_VAR, filesort_scan_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, #ifdef HAVE_OPENSSL #ifndef EMBEDDED_LIBRARY {"Ssl_accept_renegotiates", (char*) &show_ssl_ctx_sess_accept_renegotiate, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_accepts", (char*) &show_ssl_ctx_sess_accept, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_callback_cache_hits", (char*) &show_ssl_ctx_sess_cb_hits, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_cipher", (char*) &show_ssl_get_cipher, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_cipher_list", (char*) &show_ssl_get_cipher_list, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_client_connects", (char*) &show_ssl_ctx_sess_connect, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_connect_renegotiates", (char*) &show_ssl_ctx_sess_connect_renegotiate, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_ctx_verify_depth", (char*) &show_ssl_ctx_get_verify_depth, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_ctx_verify_mode", (char*) &show_ssl_ctx_get_verify_mode, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_default_timeout", (char*) &show_ssl_get_default_timeout, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_finished_accepts", (char*) &show_ssl_ctx_sess_accept_good, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_finished_connects", (char*) &show_ssl_ctx_sess_connect_good, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_hits", (char*) &show_ssl_ctx_sess_hits, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_misses", (char*) &show_ssl_ctx_sess_misses, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_mode", (char*) &show_ssl_ctx_get_session_cache_mode, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_overflows", (char*) &show_ssl_ctx_sess_cache_full, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_size", (char*) &show_ssl_ctx_sess_get_cache_size, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_session_cache_timeouts", (char*) &show_ssl_ctx_sess_timeouts, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_sessions_reused", (char*) &show_ssl_session_reused, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_used_session_cache_entries",(char*) &show_ssl_ctx_sess_number, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Ssl_verify_depth", (char*) &show_ssl_get_verify_depth, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_verify_mode", (char*) &show_ssl_get_verify_mode, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_server_not_before", (char*) &show_ssl_get_server_not_before, SHOW_FUNC, SHOW_SCOPE_ALL}, {"Ssl_server_not_after", (char*) &show_ssl_get_server_not_after, SHOW_FUNC, SHOW_SCOPE_ALL}, #ifndef HAVE_YASSL {"Rsa_public_key", (char*) &show_rsa_public_key, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif #endif #endif /* HAVE_OPENSSL */ {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Table_open_cache_hits", (char*) offsetof(STATUS_VAR, table_open_cache_hits), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Table_open_cache_misses", (char*) offsetof(STATUS_VAR, table_open_cache_misses), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Table_open_cache_overflows",(char*) offsetof(STATUS_VAR, table_open_cache_overflows), SHOW_LONGLONG_STATUS,SHOW_SCOPE_ALL}, {"Tc_log_max_pages_used", (char*) &tc_log_max_pages_used, SHOW_LONG, SHOW_SCOPE_GLOBAL}, {"Tc_log_page_size", (char*) &tc_log_page_size, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, {"Tc_log_page_waits", (char*) &tc_log_page_waits, SHOW_LONG, SHOW_SCOPE_GLOBAL}, #ifndef EMBEDDED_LIBRARY {"Threads_cached", (char*) &Per_thread_connection_handler::blocked_pthread_count, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL}, #endif {"Threads_connected", (char*) &Connection_handler_manager::connection_count, SHOW_INT, SHOW_SCOPE_GLOBAL}, {"Threads_created", (char*) &show_num_thread_created, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Threads_running", (char*) &show_num_thread_running, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, {"Uptime", (char*) &show_starttime, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #ifdef ENABLED_PROFILING {"Uptime_since_flush_status",(char*) &show_flushstatustime, SHOW_FUNC, SHOW_SCOPE_GLOBAL}, #endif {NullS, NullS, SHOW_LONG, SHOW_SCOPE_ALL} };
  • 这里可以看出的是,其值是一个指向内存位置的指针,其数据来源在6800行以上的部分。有些是直接变量值传过来
  • 在mysqld.h文件中定义了若干个需要加锁的系统变量
/* Server mutex locks and condition variables. */ extern mysql_mutex_t LOCK_item_func_sleep, LOCK_status, LOCK_uuid_generator, LOCK_crypt, LOCK_timezone, LOCK_slave_list, LOCK_manager, LOCK_global_system_variables, LOCK_user_conn, LOCK_log_throttle_qni, LOCK_prepared_stmt_count, LOCK_error_messages, LOCK_sql_slave_skip_counter, LOCK_slave_net_timeout, LOCK_offline_mode, LOCK_default_password_lifetime; #ifdef HAVE_OPENSSL extern char* des_key_file; extern mysql_mutex_t LOCK_des_key_file; #endif extern mysql_mutex_t LOCK_server_started; extern mysql_cond_t COND_server_started; extern mysql_mutex_t LOCK_reset_gtid_table; extern mysql_mutex_t LOCK_compress_gtid_table; extern mysql_cond_t COND_compress_gtid_table; extern mysql_rwlock_t LOCK_sys_init_connect, LOCK_sys_init_slave; extern mysql_rwlock_t LOCK_system_variables_hash; extern mysql_cond_t COND_manager; extern int32 thread_running; extern mysql_mutex_t LOCK_keyring_operations; extern char *opt_ssl_ca, *opt_ssl_capath, *opt_ssl_cert, *opt_ssl_cipher, *opt_ssl_key, *opt_ssl_crl, *opt_ssl_crlpath, *opt_tls_version; extern char *opt_disabled_storage_engines; 

下面供参考

vim mysql-server-mysql-5.7.21/sql/sql_show_status.cc
/** Build a replacement query for SHOW STATUS. When the parser accepts the following syntax: SHOW GLOBAL STATUS the parsed tree built for this query is in fact: SELECT * FROM (SELECT VARIABLE_NAME as Variable_name, VARIABLE_VALUE as Value FROM performance_schema.global_status) global_status Likewise, the query: SHOW GLOBAL STATUS LIKE "<value>" is built as: SELECT * FROM (SELECT VARIABLE_NAME as Variable_name, VARIABLE_VALUE as Value FROM performance_schema.global_status) global_status WHERE Variable_name LIKE "<value>" Likewise, the query: SHOW GLOBAL STATUS where <where_clause> is built as: SELECT * FROM (SELECT VARIABLE_NAME as Variable_name, VARIABLE_VALUE as Value FROM performance_schema.global_status) global_status WHERE <where_clause> **/

此处,代码说明执行show slave status命令,在内部会通过转化为查询p_s.global_status表的SQL形式。

vim mysql-server-mysql-5.7.21/sql/sql_show.cc
/*第2537行开始是status信息函数的实现*/ /***************************************************************************** Status functions *****************************************************************************/ int add_status_vars (const SHOW_VAR *list) {.....} void init_status_vars() {.............} void reset_status_vars(){.......} bool get_status_var(THD *thd, SHOW_VAR *list, const char * name, char * const value, enum_var_type var_type, size_t *length) {......} /// 这里的注释表明show 命令一次获取status+vars信息,然后通过下面这个命令清掉vars信息 void remove_status_vars(SHOW_VAR *list) {.....} const char* get_one_variable(THD *thd, const SHOW_VAR *variable, enum_var_type value_type, SHOW_TYPE show_type, system_status_var *status_var, const CHARSET_INFO **charset, char *buff, size_t *length) {...........} /** @brief Returns the value of a system or a status variable. @param running_thd [in] The handle of the current THD. @param target_thd [in] The handle of the remote THD. @param variable [in] Details of the variable. @param value_type [in] Variable type. @param show_type [in] Variable show type. @param charset [out] Character set of the value. @param buff [in,out] Buffer to store the value. (Needs to have enough memory to hold the value of variable.) @param length [out] Length of the value. @return Pointer to the value buffer. */ const char* get_one_variable_ext(THD *running_thd, THD *target_thd, const SHOW_VAR *variable, enum_var_type value_type, SHOW_TYPE show_type, system_status_var *status_var, const CHARSET_INFO **charset, char *buff, size_t *length) {....................}

查看5.5源码

5.5.62版本中并没有sql_show_status.cc文件,show global status应该是使用sql_show.cc来实现。
天色已晚,明日再看

总结

  • 虽然没有看出如何查出对应的status值,单是对于global 信息来说,有些信息是需要对各个线程的指标进行相加的到的、
  • 5.7可以使用查询表的方式查询指标信息,但是必须要一次查完,对多个的指标的分批查询会造成监控数据的不一致性,影响分析
  • 关键在于对status_vars对象的查找,分析这个变量名,可以找出`show global status``命令的来龙去脉

参考链接