DBA常用SQL和命令整理备查

新建表后校验

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.tables 
WHERE table_name = 'my_table_name';

修改表结构后校验

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'my_table_name' 
AND COLUMN_NAME IN ('my_column_name1', 'my_column_name2');

添加索引后校验

SELECT s.table_schema, s.table_name, s.index_name, s.column_name 
FROM information_schema.STATISTICS s 
WHERE index_name = 'idx_myindex';

查看表创建时间

-- 所有表
SELECT table_name, create_time 
FROM information_schema.TABLES;

-- 指定表
SELECT table_name, create_time 
FROM information_schema.TABLES 
WHERE table_name = 'table_name';

查看大于10000000行的表

SELECT table_schema, table_name, table_rows 
FROM information_schema.TABLES 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
AND table_rows > 10000000 
ORDER BY table_rows DESC;

KILL数据库链接

杀掉空闲时间大于2000s的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE command = 'Sleep' AND time > 2000;

杀掉处于某状态的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE state LIKE 'Creating sort index';

杀掉某个用户的链接

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.`processlist` 
WHERE user = 'root';

拼接创建数据库语句（排除系统库）

SELECT CONCAT(
        'CREATE DATABASE ',
        '`',
        schema_name,
        '`',
        ' DEFAULT CHARACTER SET ',
        default_character_set_name,
        ';'
    ) AS CreateDatabaseQuery
FROM information_schema.schemata
WHERE schema_name NOT IN (
        'information_schema',
        'performance_schema',
        'mysql',
        'sys'
    );

拼接创建用户语句（排除系统用户）

SELECT CONCAT(
        'CREATE USER \'',
        user,
        '\'@\'',
        host,
        '\' IDENTIFIED WITH \'mysql_native_password\' AS \'',
        authentication_string,
        '\' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;'
    )
FROM mysql.`user`
WHERE `User` NOT IN ('root', 'mysql.session', 'mysql.sys');

用于批量修改密码

SELECT CONCAT(
        'CREATE USER ',
        user,
        '_v1@',
        '`',
        host,
        '`',
        ' IDENTIFIED BY ',
        "'",
        RIGHT(TO_BASE64(authentication_string), 12),
        "';"
    )
FROM mysql.user
WHERE host NOT IN ('localhost', '127.0.0.1')
AND user NOT LIKE '%_v1'
ORDER BY user;

重命名库名

SELECT CONCAT(
        "CREATE TABLE ", TABLE_SCHEMA, "_bak.", TABLE_NAME, " LIKE ", TABLE_SCHEMA, ".", TABLE_NAME, ";"
    ) 
FROM information_schema.columns 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
GROUP BY TABLE_SCHEMA, 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;

查看单个库占用空间大小

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_db';

查看单个表占用空间大小

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_db'
AND table_name = 'tbname';

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

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;

查看某个库下所有表的碎片情况

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS,
    CONCAT(ROUND(t.DATA_LENGTH / 1024 / 1024, 2), ' MB') AS size,
    t.INDEX_LENGTH,
    CONCAT(ROUND(t.DATA_FREE / 1024 / 1024, 2), ' MB') AS datafree
FROM information_schema.`TABLES` t
WHERE t.TABLE_SCHEMA = 'callcenter'
ORDER BY datafree DESC;

收缩表，减少碎片

ALTER TABLE tb_name ENGINE = InnoDB;
OPTIMIZE TABLE tb_name;

查找某一个库无主键表

SELECT 
    table_schema,
    table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'test_db'
AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (constraint_name, table_schema, table_name)
    WHERE t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'test_db'
);

查找除系统库外无主键表

SELECT 
    t1.table_schema,
    t1.table_name
FROM information_schema.`TABLES` t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');