分享私藏的mysql性能診斷sql

概述

今天分享幾個mysql語句，用于日常mysql資料庫的維護和性能診斷！

01 死鎖檢查語句

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;           

02 查詢沒有PK定義的表

SELECT
    CONCAT('The B-Tree NOT UNIQUE index ', IDX_PREFIX, ' is prefix of the composed index ', IDX_COMPOSED) AS Message,
    CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS FULL_TABLE_NAME,
    IDX_PREFIX,
    C_IDX_PREFIX,
    IDX_COMPOSED,
    C_IDX_COMPOSED
FROM
    (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        INDEX_NAME AS 'IDX_PREFIX',
        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS C_IDX_PREFIX
    FROM
        information_schema.STATISTICS
    WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
    AND INDEX_TYPE='BTREE'
    AND NON_UNIQUE=1
    GROUP BY
        TABLE_SCHEMA,
        TABLE_NAME,
        INDEX_NAME
    ) AS idx1
    INNER JOIN
    (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        INDEX_NAME AS 'IDX_COMPOSED',
        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS C_IDX_COMPOSED
    FROM
        information_schema.STATISTICS
    WHERE INDEX_TYPE='BTREE' 
    GROUP BY
        TABLE_SCHEMA,
        TABLE_NAME,
        INDEX_NAME
    ) AS idx2
    USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE idx1.C_IDX_PREFIX != idx2.C_IDX_COMPOSED AND LOCATE(CONCAT(idx1.C_IDX_PREFIX, ','), idx2.C_IDX_COMPOSED) = 1;           

03 查詢DB的空間占用

select 
    sum(data_length)/1024/1024 as data_lenght_mb,
    sum(data_free)/1024/1024 as data_free_mb,
    sum(index_length)/1024/1024 as index_lenght_mb,
    (select (@@innodb_log_files_in_group * @@innodb_log_file_size)/1024/1024) as innodb_log_size_mb,
    (sum(data_length)+sum(data_free)+sum(index_length)+(select (@@innodb_log_files_in_group * @@innodb_log_file_size)))/1024/1024 as tot_mb
from 
    information_schema.tables;
           

04 查詢有自增主鍵的表

select
  t.table_catalog,
  t.table_schema,
  t.table_name,
  c.column_name,
  c.data_type,
  c.column_type,
  t.auto_increment,
(case c.data_type
   when 'tinyint' THEN 255
   when 'smallint' THEN 65535
   when 'mediumint' THEN 16777215
   when 'int' THEN 4294967295
   when 'bigint' THEN 18446744073709551615
end >> if(LOCATE('unsigned', c.column_type) > 0, 0, 1)) as max_value
from information_schema.tables t inner join information_schema.columns c
    on t.table_name = c.table_name and c.table_catalog = t.table_catalog and c.table_schema = t.table_schema
where c.extra = 'auto_increment' AND
t.table_schema NOT IN  ('mysql', 'information_schema', 'performance_schema','sys')
order by t.auto_increment desc;            

05 查詢沒有自增主鍵的表

select
  t.table_catalog,
  t.table_schema,
  t.table_name,
  c.column_name,
  c.data_type,
  c.column_type,
(case c.data_type
   when 'tinyint' THEN 255
   when 'smallint' THEN 65535
   when 'mediumint' THEN 16777215
   when 'int' THEN 4294967295
   when 'bigint' THEN 18446744073709551615
end >> if(LOCATE('unsigned', c.column_type) > 0, 0, 1)) as max_value
from
  information_schema.tables t inner join information_schema.columns c
    on t.table_name = c.table_name and c.table_catalog = t.table_catalog and c.table_schema = t.table_schema
where c.extra != 'auto_increment' AND
t.table_schema NOT IN  ('mysql', 'information_schema', 'performance_schema','sys') AND
c.data_type in ('tinyint', 'int', 'mediumint', 'bigint')
; 
            

06 查詢有PK定義的表

SELECT 
    t2.TABLE_SCHEMA,
    t2.TABLE_NAME,
    t2.ENGINE
FROM    
    information_schema.TABLES t2,
    (
    SELECT 
        t.TABLE_SCHEMA,
        t.TABLE_NAME
    FROM
        information_schema.COLUMNS c,
        information_schema.TABLES t
    WHERE t.TABLE_SCHEMA not in ('mysql','sys')
    AND c.TABLE_NAME=t.TABLE_NAME
    AND c.TABLE_SCHEMA=t.TABLE_SCHEMA
    AND c.COLUMN_KEY='PRI'
    GROUP BY
        t.TABLE_SCHEMA,
        t.TABLE_NAME
    ) pkt
WHERE t2.TABLE_SCHEMA not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')
AND (t2.TABLE_SCHEMA=pkt.TABLE_SCHEMA AND t2.TABLE_NAME=pkt.TABLE_NAME);           

07 查詢有PK表定義的列名資訊

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.ENGINE,
    c.COLUMN_NAME as PK
FROM
    information_schema.COLUMNS c,
    information_schema.TABLES t
WHERE t.TABLE_SCHEMA not in ('mysql','sys')
AND c.TABLE_NAME=t.TABLE_NAME
AND c.TABLE_SCHEMA=t.TABLE_SCHEMA
AND c.COLUMN_KEY='PRI'
;