mysql 工具SQL

--查看当前用户的链接进程
show processlist;

/**
 * 查询表空间中的表大小，表记录数
 * data_length 数据大小
 * index_length 索引大小
 * stat_total_rows 总行数
 */
-- 表控件中所有表统计
SELECT
    concat(table_schema , '.' , table_name) table_name ,
    concat(
        round(data_length /(1024 * 1024) , 2) ,
        'M'
    ) stat_data_length ,
    concat(
        round(index_length /(1024 * 1024) , 2) ,
        'M'
    ) stat_index_length ,
    concat(
        round(
            round(data_length + index_length) /(1024 * 1024) ,
            2
        ) ,
        'M'
    ) stat_total_size ,
    format(TABLE_ROWS , 0) as stat_total_rows
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = 'im'
ORDER BY
    data_length DESC;
--表空间总量统计
SELECT
    TABLE_SCHEMA ,
    concat(
        round(sum(data_length) /(1024 * 1024) , 2) ,
        'M'
    ) stat_data_length ,
    concat(
        round(sum(index_length) /(1024 * 1024) , 2) ,
        'M'
    ) stat_index_length ,
    concat(
        round(
            round(sum(data_length) + sum(index_length)) /(1024 * 1024) ,
            2
        ) ,
        'M'
    ) stat_total_size ,
    format(sum(TABLE_ROWS) , 0) stat_total_rows
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = 'im'
ORDER BY
    data_length DESC;

--获取前一天日期
SELECT
    DATE_FORMAT(
        DATE_ADD(now(), INTERVAL - 1 DAY),
        '%Y%m%d'
    );

--查看innodb类型表状态，可以查看最后一条死锁记录详细日志
show engine innodb status \G;