mysql global status_【MySQL】show global status命令

原因

想通过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 ""

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 ""

Likewise, the query:

SHOW GLOBAL STATUS where

is built as:

SELECT * FROM

(SELECT VARIABLE_NAME as Variable_name, VARIABLE_VALUE as Value

FROM performance_schema.global_status) global_status

WHERE

**/

此处，代码说明执行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``命令的来龙去脉

参考链接