您的位置: 首页 > IT文章 > mysql performance_schema 和information_schema.tables了解

mysql performance_schema 和information_schema.tables了解

分类: IT文章 • 2023-12-01 09:33:07

这个是关于mysql的系统表，性能表，核心表操作的一些介绍，深入算不上

我们一般很少去动 mysql information_schema 信息相关 performance_schema 性能相关库

因为版本不同你所看到的数据会有一些差异，这个使用5.6版本作为测试

废话一句，如果有很复杂的操作表结构sql语句要写，可以使用phpmyadmin来操作，预览语句获得SQL语句

show tables;

mysql表

mysql> show tables;
+--------------------------
| Tables_in_mysql
+--------------------------
| columns_priv
| db
| event
| func
| general_log
| help_category
| help_keyword
| help_relation
| help_topic
| innodb_index_stats
| innodb_table_stats
| ndb_binlog_index
| plugin
| proc
| procs_priv
| proxies_priv
| 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

information_schema表

+--------------------------------------
| 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_LOCK_WAITS
| INNODB_SYS_TABLESTATS
| INNODB_CMP
| INNODB_METRICS
| INNODB_CMP_RESET
| INNODB_CMP_PER_INDEX
| INNODB_CMPMEM_RESET
| INNODB_FT_DELETED
| INNODB_BUFFER_PAGE_LRU
| INNODB_SYS_FOREIGN
| INNODB_SYS_COLUMNS
| INNODB_SYS_INDEXES
| INNODB_FT_DEFAULT_STOPWORD
| INNODB_SYS_FIELDS
| INNODB_CMP_PER_INDEX_RESET
| INNODB_BUFFER_PAGE
| INNODB_CMPMEM
| INNODB_FT_INDEX_TABLE
| INNODB_FT_BEING_DELETED
| INNODB_SYS_TABLESPACES
| INNODB_FT_INDEX_CACHE
| INNODB_SYS_FOREIGN_COLS
| INNODB_SYS_TABLES
| INNODB_BUFFER_POOL_STATS
| INNODB_FT_CONFIG
+--------------------------------------

performance_schema表

+----------------------------------------------------
| Tables_in_performance_schema
+----------------------------------------------------
| accounts
| cond_instances
| events_stages_current
| events_stages_history
| events_stages_history_long
| events_stages_summary_by_account_by_event_name
| events_stages_summary_by_host_by_event_name
| events_stages_summary_by_thread_by_event_name
| events_stages_summary_by_user_by_event_name
| events_stages_summary_global_by_event_name
| events_statements_current
| events_statements_history
| events_statements_history_long
| events_statements_summary_by_account_by_event_name
| events_statements_summary_by_digest
| events_statements_summary_by_host_by_event_name
| events_statements_summary_by_thread_by_event_name
| events_statements_summary_by_user_by_event_name
| events_statements_summary_global_by_event_name
| events_waits_current
| events_waits_history
| events_waits_history_long
| events_waits_summary_by_account_by_event_name
| events_waits_summary_by_host_by_event_name
| events_waits_summary_by_instance
| events_waits_summary_by_thread_by_event_name
| events_waits_summary_by_user_by_event_name
| events_waits_summary_global_by_event_name
| file_instances
| file_summary_by_event_name
| file_summary_by_instance
| host_cache
| hosts
| mutex_instances
| objects_summary_global_by_type
| performance_timers
| rwlock_instances
| session_account_connect_attrs
| session_connect_attrs
| setup_actors
| setup_consumers
| setup_instruments
| setup_objects
| setup_timers
| socket_instances
| socket_summary_by_event_name
| socket_summary_by_instance
| table_io_waits_summary_by_index_usage
| table_io_waits_summary_by_table
| table_lock_waits_summary_by_table
| threads
| users
+----------------------------------------------------

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.tables WHERE table_name = "v1_const" and table_schema = "loan-10-14"

show columns from v1_const WHERE field = "const_id"

show tables like 'v1_customer%'

查询某个表的表情以什么开头

TABLE_NAME 表名

TABLE_SCHEMA 数据库名

SELECT * FROM information_schema.tables WHERE `TABLE_NAME` LIKE 'v1_third%' AND `TABLE_SCHEMA` ='loan-2017-01-16'