mysql常用查询信息

1. 统计当前实例中业务相关的库和表的信息（排除掉mysql sys information_schema
   performance_schema）
   库名 表个数 表名列表

mysql> select table_schema,group_concat(table_name),count(*) from
information_schema.tables where table_schema not in
('sys','mysql','information_schema','performance_schema') group by table_schema;

2. 统计当前实例每个数据库的数据总量（排除掉mysql sys information_schema
   performance_schema）

select table_schema,sum(table_rows * avg_row_length + index_length)/1024/1024 as
total_mb
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
group by table_schema;

3. 统计当前实例非innodb的表（排除掉mysql sys information_schema performance_schema）

select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';
alter table world.aaaaa engine=innodb;

4. 查询有碎片的表信息

select table_schema,table_name ,data_free
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
and data_free >0;

5. 查询出本地外的连接信息

 select * from information_schema.processlist where host !='locakhost';

6. 拼接SQL
   a. 查询当前系统中所有非INNODB的表。

select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';

b. 将这些非INNODB的表替换为INNODB
8.3.4 TRIGGERS、views、ROUTINES、EVENTS应用
8.3.5 columns
8.3.6 processlist应用

mysql> select concat("alter table ",table_schema,".",table_name,"
engine=innodb;") from information_schema.tables where table_schema not
in ('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB' into outfile '/tmp/alter.sql';
source /tmp/alter.sql