MySQL常用巡检SQL(4)

9、查看有哪些trigger

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

10、查看有哪些event

SELECT EVENT_SCHEMA,EVENT_NAME,STATUS,INTERVAL_VALUE,INTERVAL_FIELD,CREATED,LAST_ALTERED 
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

11、查看有哪些视图

--统计每个数据库视图总量
SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ;

--查看每个库具体有哪些视图

SELECT  TABLE_SCHEMA , TABLE_NAME,DEFINER
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ,TABLE_NAME,DEFINER;

12、查询分区表信息

--查看分区表的最大分区、每个分区表的总大小

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', -1), ')', 1)) AS PARTITION_KEY,
    TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', 1), ')', -1)) AS PARTITION_FUNC,
    MAX(
        IFNULL(
            CASE
                WHEN p.PARTITION_DESCRIPTION IS NULL THEN NULL
                WHEN p.PARTITION_DESCRIPTION = 'MAXVALUE' THEN 'MAXVALUE'
                WHEN LENGTH(p.PARTITION_DESCRIPTION) = 6 THEN FROM_DAYS(p.PARTITION_DESCRIPTION)
                WHEN LENGTH(p.PARTITION_DESCRIPTION) = 10 THEN FROM_UNIXTIME(p.PARTITION_DESCRIPTION, '%Y-%m-%d')
                ELSE NULL
            END,
            p.PARTITION_DESCRIPTION
        )
    ) AS MAX_PARTITION_DESCRIPTION,
    c.COLUMN_TYPE AS PARTITION_KEY_TYPE,
    PARTITION_METHOD,
    COUNT(p.PARTITION_NAME) AS PARTITION_COUNT,
    SUM(p.TABLE_ROWS) AS TABLE_TOTAL_ROWS,
    CONCAT(ROUND(SUM(p.DATA_LENGTH) / (1024 * 1024), 2),'M') AS DATA_LENGTH_MB,
    CONCAT(ROUND(SUM(p.INDEX_LENGTH) / (1024 * 1024), 2),'M') AS INDEX_LENGTH_MB,
    CONCAT(ROUND(ROUND(SUM(p.DATA_LENGTH + p.INDEX_LENGTH)) / (1024 * 1024),2),'M') AS TOTAL_SIZE_MB
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.PARTITIONS p 
ON t.TABLE_SCHEMA = p.TABLE_SCHEMA AND t.TABLE_NAME = p.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS c 
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 
AND c.COLUMN_NAME = TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', -1), ')', 1))
WHERE t.TABLE_SCHEMA NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
GROUP BY
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    PARTITION_KEY,
    PARTITION_FUNC,
    PARTITION_KEY_TYPE,
    PARTITION_METHOD
ORDER BY TOTAL_SIZE_MB DESC;
    
    
    
--查看某个分区表的信息,table_schema和table_name 

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', -1), ')', 1)) AS PARTITION_KEY,
    TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', 1), ')', -1)) AS PARTITION_FUNC,
    IFNULL(
        CASE
            WHEN p.PARTITION_DESCRIPTION IS NULL THEN NULL
            WHEN p.PARTITION_DESCRIPTION = 'MAXVALUE' THEN 'MAXVALUE'
            WHEN LENGTH(p.PARTITION_DESCRIPTION) = 6 THEN FROM_DAYS(p.PARTITION_DESCRIPTION)
            WHEN LENGTH(p.PARTITION_DESCRIPTION) = 10 THEN FROM_UNIXTIME(p.PARTITION_DESCRIPTION, '%Y-%m-%d')
            ELSE NULL
        END,
        p.PARTITION_DESCRIPTION
    ) AS PARTITION_DESC,
    c.COLUMN_TYPE AS PARTITION_KEY_TYPE,
    p.PARTITION_METHOD,
    p.TABLE_ROWS AS TABLE_ROWS,
    CONCAT(ROUND(p.DATA_LENGTH / (1024 * 1024), 2), 'M') AS DATA_LENGTH_MB,
    CONCAT(ROUND(p.INDEX_LENGTH / (1024 * 1024), 2), 'M') AS INDEX_LENGTH_MB,
    CONCAT(ROUND((p.DATA_LENGTH + p.INDEX_LENGTH) / (1024 * 1024), 2), 'M') AS TOTAL_SIZE_MB
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.PARTITIONS p 
ON t.TABLE_SCHEMA = p.TABLE_SCHEMA AND t.TABLE_NAME = p.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS c 
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 
AND c.COLUMN_NAME = TRIM('`' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(p.PARTITION_EXPRESSION, '(', -1), ')', 1))
WHERE t.TABLE_SCHEMA = 'mydb'
    AND t.TABLE_NAME = 't2_pt'
GROUP BY
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    PARTITION_KEY,
    PARTITION_FUNC,
    PARTITION_DESC,
    PARTITION_KEY_TYPE,
    PARTITION_METHOD,
    TABLE_ROWS,
    DATA_LENGTH_MB,
    INDEX_LENGTH_MB,
    TOTAL_SIZE_MB
ORDER BY PARTITION_DESC ASC;