MySQL数据库统计信息

数据库表空间模式（on：独立表空间模式；off：系统表空间模式）。
```
SQL> show variables like 'innodb_file_per_table';
```

查看数据库、表、列字符集。

查看数据库及数据库的字符集。

SQL> show databases;
SQL> show create database {数据库名称}\G;

查看指定数据库下表及表的字符集。

SQL> show tables from {数据库名称};
SQL> show table status from {数据库名称} like {表名称};

查看表中所有列的字符集。

SQL> show full columns from {表名称};

统计数据库对象及个数。

SQL> select 'database' type,schema_name db,count(*) cnt from information_schema.SCHEMATA  group by db
     union all
     select 'table' type,table_schema db,count(*) cnt from information_schema.TABLES a 
            where table_type='BASE TABLE' group by table_schema
     union all 
     select 'events' type,event_schema db,count(*) cnt from information_schema.EVENTS b 
            group by event_schema
     union all 
     select 'triggers' type,trigger_schema db,count(*) cnt 
            from information_schema.TRIGGERS c group by trigger_schema
     union all 
     select 'procedure' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d 
            where routine_type='PROCEDURE' group by db
     union all 
     select 'function' type,routine_schema db,count(*) cnt from information_schema.ROUTINES e 
            where routine_type='FUNCTION' group by db
     union all 
     select 'views' type,table_schema db,count(*) cnt 
            from information_schema.views f group by table_schema
     union all
     select 'index' type,index_schema db,count(distinct index_name) cnt 
            from information_schema.STATISTICS g group by db 
     union all
     select 'partition table' type,table_schema db,count(*) cnt from information_schema.PARTITIONS p 
            where partition_name is not null group by db;

统计表数据量。

SQL> select table_name,table_rows from information_schema.tables 
     where TABLE_SCHEMA = '{数据库名称}' order by table_name desc;

查看所有用户下的表对象使用空间大小。

SQL> select table_schema,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from  
     information_schema.TABLES group by table_schema;

查看数据库表对象使用的列名称（关键字排查）。

SQL> select column_name from information_schema.columns c
     where table_schema='{数据库名称}' group by column_name order by column_name desc;

查看数据库表对象使用的数据类型。

SQL> select data_type, count(*) cnt from information_schema.COLUMNS c 
  where table_schema='{数据库名称}' group by data_type order by cnt desc;

列出所有的非系统数据所有表的主键信息和外键信息。

SQL> select
       o.constraint_schema 约束拥有者,
       o.constraint_name 约束名称,
       o.table_schema 表拥有者,
       o.table_name 表名,
       o.column_name 列名,
       o.referenced_table_schema 外键表拥有者,
       o.referenced_table_name 外键表名,
       o.referenced_column_name 外键表列名,
       o.update_rule 约束更新规则,
       o.delete_rule 约束删除规则,
       o.unique_constraint_name 唯一约束名称,
       t.constraint_type 约束类型
    from
    (
        select
          k.constraint_schema,
          k.constraint_name,
          k.table_schema,
          k.table_name,
          k.column_name,
          k.referenced_table_schema,
          k.referenced_table_name,
          k.referenced_column_name,
          r.update_rule,
          r.delete_rule,
          r.unique_constraint_name
        from
          information_schema.key_column_usage k
        left join information_schema.referential_constraints r on
          k.constraint_name = r.constraint_name
    ) as o
    inner join information_schema.table_constraints t on
       o.table_name = t.table_name
       and t.constraint_name = o.constraint_name
    where
       o.constraint_schema != 'mysql' and o.constraint_schema != 'sys';

MySQL数据库统计信息 ​

MySQL数据库统计信息