Mysql常用SQL

一、查看所有数据库容量大小

select
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

二、查看所有数据库各表容量大小

方式一

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc limit 10;

方式二

select
    concat(table_schema,'.',table_name,':',truncate(data_length/1024/1024/1024, 2),'GB')
from information_schema.tables
order by data_length desc limit 5;

三、查看指定数据库容量大小

select
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

四、查看指定数据库各表容量大小

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

五、慢日志分析

pt-query-digest --since='2d' --report-format=profile /data/mysql/logs/slow.log
# 报告格式选项
--report-format rusage,date,hostname,files,header,profile,query_report,prepared

六、没有主键的表

查询无主键表信息

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND TABLE_NAME NOT IN
    (SELECT TABLE_NAME
     FROM information_schema.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
       );

生成删除无主键表的 SQL

SELECT  concat('drop table ',TABLE_SCHEMA, '.', TABLE_NAME,';') as droptable
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND TABLE_NAME NOT IN
    (SELECT TABLE_NAME
     FROM information_schema.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
       );

查询无主键表的数据库和表名

SELECT  TABLE_SCHEMA,TABLE_NAME,concat(TABLE_SCHEMA, '.', TABLE_NAME) as dbtable
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND TABLE_NAME NOT IN
    (SELECT TABLE_NAME
     FROM information_schema.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
       );

七、查看 MySQL 表最近 1 次访问时间

指定数据库

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'apps'
order by update_time;

所有数据库取前 20 条

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
order by update_time desc limit 20;

八、查看从库

方式一

show slave hosts;

方式二

select substring_index(host, ':', 1) as slave_hostname
from
    information_schema.processlist
where
    command IN ('Binlog Dump', 'Binlog Dump GTID')
group by 1;

方式三

show slave status\G