简要介绍mysql系统_简要总结mysql几个系统库
发布时间：2022-11-16 12:43:56 所属栏目：MySql教程 来源：
导读： 前两天因为去南昌出了个短差，有几天没有更新了，有时间就动起来。今天就总结一下mysql数据库的几个系统库。
首先说说sys库，mysql5.6开始推出了sys系统库，提供了一些直接访问系统库如performance_sche
首先说说sys库，mysql5.6开始推出了sys系统库，提供了一些直接访问系统库如performance_sche
|
前两天因为去南昌出了个短差，有几天没有更新了，有时间就动起来。今天就总结一下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里面来，这个真的不适合我。 洗洗睡觉了…. （编辑：PHP编程网 - 钦州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330484号