MySQL information_schema 系统库介绍

# 查看某个库中的表信息
SELECT
    table_name,
    table_type,
  ENGINE 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'db5' 
ORDER BY
    table_name
# 查看整个实例占用空间
SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
    concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
    information_schema.TABLES;
# 查看各个库占用空间
SELECT
    TABLE_SCHEMA,
    concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
    concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size 
FROM
    information_schema.TABLES 
GROUP BY
    TABLE_SCHEMA 
ORDER BY
    data_length DESC;
# 查看某个表占用空间
SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
    concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'test' 
    AND table_name = 'test_tb'
# 查看所有线程信息
SELECT
    * 
FROM
    information_schema.PROCESSLIST
# 查看非睡眠线程信息
SELECT
    * 
FROM
    information_schema.PROCESSLIST 
WHERE
    command != 'sleep'
# 查看某个用户发起的线程信息
SELECT
    * 
FROM
    information_schema.PROCESSLIST 
WHERE
    USER = 'testuser'
# 查看某个字符集支持的所有排序规则
SELECT
    COLLATION_NAME,
    CHARACTER_SET_NAME,
    IS_DEFAULT
FROM
    `information_schema`.`COLLATIONS` 
WHERE
    `CHARACTER_SET_NAME` = 'utf8' 
# 查看某个表的分区信息（如果有）
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME 
FROM
    `information_schema`.`PARTITIONS` 
WHERE
    `TABLE_SCHEMA` = 'test' 
    AND `TABLE_NAME` = 'tbname'
# 查看某个表的索引信息
SELECT
    * 
FROM
    `information_schema`.`STATISTICS` 
WHERE
    `TABLE_SCHEMA` = 'test' 
    AND `TABLE_NAME` = 'tbname' 
# 查看 innodb 事务相关信息
SELECT
    * 
FROM
    information_schema.INNODB_TRX