简要介绍mysql系统_简要总结mysql几个系统库

前两天因为去南昌出了个短差，有几天没有更新了，有时间就动起来。今天就总结一下mysql数据库的几个系统库。

首先说说sys库，mysql5.6开始推出了sys系统库，提供了一些直接访问系统库如performance_schema的一些示图，简化了一些查询监控操作，大部分数据来自performance_schema(简称PS), 因此我们只要好好研究sys系统示图即可mysql系统，对于performance_schema即用sys替代。少量数据来自information_schema(简称IS)。下面就简要从几个系统库用示例的方式介绍一下常用示例场景：

SYS

首先看看sys示库下面有哪些表

SYS示图

mysql> select database();

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

| database() |

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

| sys|

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

1 row in set (0.00 sec)

mysql>

mysql> show tables;

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

| Tables_in_sys |

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

| host_summary |

| host_summary_by_file_io |

| host_summary_by_file_io_type |

| host_summary_by_stages |

| host_summary_by_statement_latency |

| host_summary_by_statement_type |

| innodb_buffer_stats_by_schema |

| innodb_buffer_stats_by_table |

| innodb_lock_waits |

| io_by_thread_by_latency |

| io_global_by_file_by_bytes |

| io_global_by_file_by_latency |

| io_global_by_wait_by_bytes |

| io_global_by_wait_by_latency |

| latest_file_io |

| memory_by_host_by_current_bytes |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes |

| memory_global_by_current_bytes |

| memory_global_total |

| metrics |

| processlist |

| ps_check_lost_instrumentation |

| schema_auto_increment_columns |

| schema_index_statistics |

| schema_object_overview |

| schema_redundant_indexes |

| schema_table_lock_waits |

| schema_table_statistics |

| schema_table_statistics_with_buffer |

| schema_tables_with_full_table_scans |

| schema_unused_indexes |

| session |

| session_ssl_status |

| statement_analysis |

| statements_with_errors_or_warnings |

| statements_with_full_table_scans |

|statements_with_runtimes_in_95th_percentile|

| statements_with_sorting |

| statements_with_temp_tables |

| sys_config |

| user_summary |

| user_summary_by_file_io |

| user_summary_by_file_io_type |

| user_summary_by_stages |

| user_summary_by_statement_latency |

| user_summary_by_statement_type |

| version |

| wait_classes_global_by_avg_latency |

| wait_classes_global_by_latency |

| waits_by_host_by_latency |

| waits_by_user_by_latency |

| waits_global_by_latency |

| x$host_summary |

| x$host_summary_by_file_io |

| x$host_summary_by_file_io_type |

| x$host_summary_by_stages |

| x$host_summary_by_statement_latency |

| x$host_summary_by_statement_type |

| x$innodb_buffer_stats_by_schema |

| x$innodb_buffer_stats_by_table |

| x$innodb_lock_waits |

| x$io_by_thread_by_latency |

| x$io_global_by_file_by_bytes |

| x$io_global_by_file_by_latency |

| x$io_global_by_wait_by_bytes |

| x$io_global_by_wait_by_latency |

| x$latest_file_io |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes |

| x$memory_global_total |

| x$processlist |

|x$ps_digest_95th_percentile_by_avg_us|

| x$ps_digest_avg_latency_distribution |

| x$ps_schema_table_statistics_io |

| x$schema_flattened_keys |

| x$schema_index_statistics |

| x$schema_table_lock_waits |

| x$schema_table_statistics |

| x$schema_table_statistics_with_buffer |

|x$schema_tables_with_full_table_scans|

| x$session |

| x$statement_analysis |

| x$statements_with_errors_or_warnings |

| x$statements_with_full_table_scans |

|x$statements_with_runtimes_in_95th_percentile |

| x$statements_with_sorting |

| x$statements_with_temp_tables |

| x$user_summary |

| x$user_summary_by_file_io |

| x$user_summary_by_file_io_type |

| x$user_summary_by_stages |

| x$user_summary_by_statement_latency |

| x$user_summary_by_statement_type |

| x$wait_classes_global_by_avg_latency |

| x$wait_classes_global_by_latency |

| x$waits_by_host_by_latency |

| x$waits_by_user_by_latency |

| x$waits_global_by_latency |

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

我们可以看到有带X$的，也有不带x$的，带X$的主要是没加工过的没有被处理的，而不带X$的一般经过了加工处理，查询起来更加直观。

下面列举一些常用查询示例

查询版本相关信息

mysql> select * from sys.version;

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

| sys_version | mysql_version |

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

| 1.5.1| 5.7.22-log |

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

1 row in set (0.00 sec)

会话相关

查询当前正在执行的语句进度信息

select * fromsession where conn_id != connection_id() and trx_state ='ACTIVE';

用户与连接：

查看每个客户端IP过来的连接消耗资源情况。

查看每个用户资源消耗情况

查看当前有多少个连接连进来：

select host,current_connections,statementsfrom host_summary\G;

MySQL内部有多个线程在运行，线程类型及数量。

select user, count(*) from processlistgroup user;

当前正在执行的sql会话信息：

select conn_id, user, current_statement,last_statement from session\G;

文件IO相关统计

根据file_io统计

mysql> select * from host_summary_by_file_io;

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

| host| ios | io_latency |

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

| background | 2365 | 330.04 ms |

| localhost| 915 | 64.59 ms |

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

IO请求前三名的文件：

select * from io_global_by_file_by_bytes order bytotal desc limit 3\G;

事务与锁相关：

select * from innodb_lock_waits\G

select * from schema_table_lock_waits\G;

INNODB引擎相关:

查询innodb_buffer_pool中的热数据

select * from innodb_buffer_stats_by_schema;

select * from innodb_buffer_stats_by_table limit3;

pages是指在buffer pool中的page数量；pages_old指在LRU 列表中处于后37%位置的page。

当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。

查询每张表在内存中消耗情况：

select * from innodb_buffer_stats_by_table limit3;

总共分配了多少内存

select * from memory_global_total\G

查询每个连接分配了多少内存

selectb.user,

current_count_used,

current_allocated,

current_avg_alloc,

current_max_alloc,

total_allocated,

current_statement

frommemory_by_thread_by_current_bytes a,sessionb

wherea.thread_id=b.thd_id G;

索引相关:

冗余索引和没有用过的索引

select * from schema_redundant_indexes;

select * from schema_unused_indexes;

查询表自增字段监控相关

select * fromschema_auto_increment_columns;

查看使用了全表扫描、文件排序、临时表的语句：

mysql> show tables like 'statements%';

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

| Tables_in_sys (statements%) |

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

| statements_with_errors_or_warnings |

| statements_with_full_table_scans |

|statements_with_runtimes_in_95th_percentile |

| statements_with_sorting |

| statements_with_temp_tables |

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

索引使用情况：

select * from schema_index_statistics wheretable_name='employees'\G

Informatino_schema

而对于IS里面基本存储的是一些基本信息如数据库表、示图、触发器等信息，还有包括字符集、文件信息、分区参数等一些元数据，基本上mysql很信息在这个IS库里都能查到.

查询IS下面有哪些表。

mysql> show tables;

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

| Tables_in_information_schema |

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

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS |

| COLUMN_PRIVILEGES |

| ENGINES |

| EVENTS |

| FILES |

| GLOBAL_STATUS |

| GLOBAL_VARIABLES |

| KEY_COLUMN_USAGE |

| OPTIMIZER_TRACE |

| PARAMETERS |

| PARTITIONS |

| PLUGINS |

| PROCESSLIST |

| PROFILING |

| REFERENTIAL_CONSTRAINTS |

| ROUTINES |

| SCHEMATA |

| SCHEMA_PRIVILEGES |

| SESSION_STATUS |

| SESSION_VARIABLES |

| STATISTICS |

| TABLES |

| TABLESPACES |

| TABLE_CONSTRAINTS |

| TABLE_PRIVILEGES |

| TRIGGERS |

| USER_PRIVILEGES |

| VIEWS |

| INNODB_LOCKS |

| INNODB_TRX |

| INNODB_SYS_DATAFILES |

| INNODB_FT_CONFIG |

| INNODB_SYS_VIRTUAL |

| INNODB_CMP |

| INNODB_FT_BEING_DELETED |

| INNODB_CMP_RESET |

| INNODB_CMP_PER_INDEX |

| INNODB_CMPMEM_RESET |

| INNODB_FT_DELETED |

| INNODB_BUFFER_PAGE_LRU |

| INNODB_LOCK_WAITS |

| INNODB_TEMP_TABLE_INFO |

| INNODB_SYS_INDEXES |

| INNODB_SYS_TABLES |

| INNODB_SYS_FIELDS |

| INNODB_CMP_PER_INDEX_RESET |

| INNODB_BUFFER_PAGE |

| INNODB_FT_DEFAULT_STOPWORD |

| INNODB_FT_INDEX_TABLE |

| INNODB_FT_INDEX_CACHE |

| INNODB_SYS_TABLESPACES |

| INNODB_METRICS |

| INNODB_SYS_FOREIGN_COLS |

| INNODB_CMPMEM |

| INNODB_BUFFER_POOL_STATS |

| INNODB_SYS_COLUMNS |

| INNODB_SYS_FOREIGN |

| INNODB_SYS_TABLESTATS |

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

61 rows in set (0.00 sec)

这里我们对它做一个简单介绍：

前三张表：

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY

是和字符集相关的

COLUMNS 相当于oracle里面的dba_columns即表和列的对应关系，即哪些表有哪些列。

FILES 相当于oracle里面的dba_data_files记录的是数据文件和表空间的对应关系。

PROCESSLIST 记录的是线程信息，相当于show processlist的输出

TABLES 相当于oracle里面的dba_tables，存储表的相关信息

VIEWS 相当于oracle里面的dba_views，存储示图相关信息

而接下来的三个表则非常重要，

innodb_trx 打印innodb内核中的当前活跃(ACTIVE)事务

innodb_locks 打印当前状态产生的innodb锁仅在有锁等待时打印

innodb_lock_waits 打印当前状态产生的innodb锁等待仅在有锁等待时打印

通常我们查询哪些事务或者锁等待时必须查这三个表。

USER_PRIVILEGES 与权限相关

MYSQL

mysql> use mysql

Reading table information for completion of tableand column names

You can turn off this feature to get a quickerstartup with -A

Database changed

mysql> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

Mysql里面我经常用到的就是下面三个

User用户相关的

Innodb_table_stats和innodb_index_stats统计信息相关的

由于本人是oracle出身的，慢慢地感觉到mysql越来越像oracle了从之前的hint，到现在的sys系统库，说不定过一段时间都有mysql awr报告供我们分析了，还有就是hash_join，希望mysql也赶紧支持吧。另外mysql没有一个牛逼的解析缓存，每次都是硬解析，所以能在程序中解决的问题就不要放到数据库中来，数据库就是个存放数据的仓库，这也许就是它的设计思路，所以不要放动则几十上百行的代码放进mysql里面来，这个真的不适合我。

洗洗睡觉了….

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!