information_schema模式表介绍processlist_MySQL

information_schema模式表介绍processlist

在mysql里，我们一般通过show （full）processlist查看当前连接情况，处理各种数据库问题。现在在information_schema模式下，5.5以后增加了processlist表，里面存储了当前连接信息，跟show (full) processlist存储的一样。通过processlist 我们可以统计分析一些信息，而不依赖于shell命令（如 grep、awk等）

如 查询当前ip的连接信息

mysql> select substring_index(host,':',1),count(*) from processlist group by substring_index(host,':',1);

同时，还增加了对innodb存储引擎性能信息等相关表，如下以innodb开头的表

mysql> select database();

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

| database() |

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

| information_schema |

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

1 row in set (0.00 sec)

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 |

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

| INNODB_TRX |

| INNODB_BUFFER_POOL_STATS |

| INNODB_LOCK_WAITS |

| INNODB_CMPMEM |

| INNODB_CMP |

| INNODB_LOCKS |

| INNODB_CMPMEM_RESET |

| INNODB_CMP_RESET |

| INNODB_BUFFER_PAGE_LRU |

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

40 rows in set (0.01 sec)