mysql常用脚本

那些你一定会用到的 MySQL 查询脚本，本文脚本基于 MySQL 5.7+ / 8.0 测试。

可用性

检查连接数使用率

• 若mysql实例的最大连接数设置过低，会导致应用程序异常，而过高则会造成服务器资源不足
• 根据服务器的配置来设置，通常，连接数使用率高于 80% 则需要开始关注，若高于 90% 则需要立即优化

SELECT 
  max_conn.variable_value AS max_connections,
  max_user_conn.variable_value AS max_user_connections,
  conn.variable_value AS current_connections,
  ROUND(conn.variable_value / max_conn.variable_value * 100, 4) AS connection_usage_percent
FROM 
  (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'max_connections') AS max_conn,
  (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'max_user_connections') AS max_user_conn,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Threads_connected') AS conn;

检查连接失败率

• MySQL连接失败率是一个重要的系统健康指标，通常用于监控数据库性能和可用性。通常，连接失败率 >5% 则建议立即处理
• 检查方法：连接失败率 = (失败的连接数 / 总的连接尝试数) * 100%

SELECT 
  Aborted_connects AS failure_conn_count, Connections AS success_conn_count,
  ROUND((Aborted_connects * 100.0 / (Connections + Aborted_connects)), 4) AS connection_failure_percent
FROM (
  SELECT 
    MAX(CASE WHEN variable_name = 'Aborted_connects' THEN variable_value END) AS Aborted_connects,
    MAX(CASE WHEN variable_name = 'Connections' THEN variable_value END) AS Connections
  FROM performance_schema.global_status WHERE variable_name IN ('Aborted_connects', 'Connections')
) AS stats;

检查自增id使用率

• 整数类型的字段（比如 tinyint, smallint, mediumint, int, bigint）能够保存的数值是有限的，超出最大值的数据无法写入表中，数值类型溢出会导致程序报错

SELECT 
  t.table_schema,
  t.table_name,
  c.column_type,
  t.auto_increment,
  POW(2, CASE c.DATA_TYPE 
    WHEN 'tinyint' THEN 7
    WHEN 'smallint' THEN 15
    WHEN 'mediumint' THEN 23
    WHEN 'int' THEN 31
    WHEN 'bigint' THEN 63
  END) AS MAX_VALUE,
  ROUND((t.auto_increment / POW(2, CASE c.DATA_TYPE
    WHEN 'tinyint' THEN 7
    WHEN 'smallint' THEN 15
    WHEN 'mediumint' THEN 23
    WHEN 'int' THEN 31
    WHEN 'bigint' THEN 63
  END)) * 100,4) AS USAGE_PERCENT 
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.EXTRA LIKE '%auto_increment%'
AND (t.auto_increment / POW(2, CASE c.DATA_TYPE
  WHEN 'tinyint' THEN 7
  WHEN 'smallint' THEN 15
  WHEN 'mediumint' THEN 23
  WHEN 'int' THEN 31
  WHEN 'bigint' THEN 63
END)) > 0.8;

检查 MGR 状态

• 检查组复制状态

#.检查MGR节点信息
SELECT 
  m.CHANNEL_NAME,
  m.MEMBER_HOST,
  m.MEMBER_PORT,
  m.MEMBER_STATE,
  CASE 
    WHEN m.MEMBER_ID=(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member') THEN 'PRIMARY'
    ELSE 'SECONDARY'
  END AS MEMBER_ROLE
FROM performance_schema.replication_group_members m
ORDER BY m.MEMBER_HOST;

#.检查事务队列
SELECT 
  member_id,
  COUNT_TRANSACTIONS_IN_QUEUE AS tx_queue,
  COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS remote_queue,
  COUNT_TRANSACTIONS_CHECKED AS checked_tx
FROM performance_schema.replication_group_member_stats;

安全性

检查具有super权限的账号

• 建议遵循“最小权限原则”回收超配权限，核心业务账号仅授予必要的DML/DDL权限，运维账号严格限制操作范围
• 查询除root之外、是否存在超配的SUPER权限、ALL PRIVILEGES权限的账号

SELECT user,host,Super_priv,Grant_priv FROM mysql.user 
WHERE account_locked='N' and ((Super_priv='Y' and user not in('root','mysql.session')) or host='%');

性能篇

InnoDB缓冲池命中率

• 高命中率意味着更多数据从内存读取，减少磁盘I/O，若缓存命中率过低则会导致系统响应缓慢，直接影响查询性能。通常，低于 95% 则需要开始关注，低于 70% 则性能已明显下降，需要立即优化
• InnoDB缓冲池命中率 = 1 - (物理读 / 逻辑读)

SELECT ROUND(
  (1 - (
    SELECT variable_value 
    FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_reads'
  ) / (
    SELECT variable_value 
    FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_read_requests'
  )) * 100, 4
) AS hit_ratio;

查看 buffer pool 使用率

#.查看buffer_pool的使用率
SELECT
  ROUND(pages_total * 16 / 1024, 2) AS total_mb,
  ROUND(pages_data * 16 / 1024, 2) AS used_mb,
  ROUND((pages_data / pages_total) * 100, 2) AS used_pct
FROM ( 
  SELECT
    MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_data' THEN variable_value END) AS pages_data,
    MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_total' THEN variable_value END) AS pages_total
  FROM performance_schema.global_status
  WHERE variable_name IN ('Innodb_buffer_pool_pages_data','Innodb_buffer_pool_pages_total')
) t;

关注 top 20 大库

• 单实例的数据库控制在20个以内、大小500G以内

select ist.table_schema
,round(sum(ist.data_length+ist.index_length)/1024/1024,2) as total_size_mb
,round(sum(ist.data_length)/1024/1024,2) as data_size_mb
,round(sum(ist.index_length)/1024/1024,2) as index_size_mb
,iss.default_character_set_name
,iss.default_collation_name
from information_schema.tables as ist
join information_schema.schemata as iss on ist.table_schema = iss.SCHEMA_NAME
where ist.table_schema not in ('information_schema','mysql','performance_schema','sys','percona_schema') 
group by ist.table_schema,iss.default_character_set_name,iss.default_collation_name
order by sum(ist.data_length+ist.index_length) desc limit 200;

关注 top 20 大表

• 大表在做全表扫描时非常耗费性能，执行DDL操作更是灾难，大于 100G 的表都应该评估一下，为什么这么大？是否可以迁到TiDB？是否可以归档或分库分表？
• 单表行数控制在1000w行以内、大小30G以内，针对单表超过1000w的大表，建议对历史数据进行清理或归档(改动小)、分表(推荐方案)

select table_schema,table_name,table_rows
,round(sum(data_length+index_length)/1024/1024,2) as total_size_mb
,round(sum(data_length)/1024/1024,2) as data_size_mb
,round(sum(index_length)/1024/1024,2) as index_size_mb 
from information_schema.tables 
where table_schema not in ('information_schema','mysql','performance_schema','sys','percona_schema') 
group by table_schema,table_name,table_rows
order by round(sum(data_length+index_length)/1024/1024,2) desc limit 200;

行数为 0 的空表

• 检查行数为零的、疑似不在使用的空表

select table_schema,table_name,table_rows
,round(sum(data_length+index_length)/1024/1024,2) as total_size_mb
,round(sum(data_length)/1024/1024,2) as data_size_mb
,round(sum(index_length)/1024/1024,2) as index_size_mb 
from information_schema.tables 
where table_schema not in ('information_schema','mysql','performance_schema','sys','percona_schema') 
and table_rows = 0 
group by table_schema,table_name,table_rows
order by table_schema,table_name limit 200;

没有主键的表

• MySQL 的玩法就是需要有主键，最好是业务无关的 int signed 自增主键
• 每张表必须要有主键，强制自增id为bigint(20)，且为主键，业务不要使用

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name NOT IN (SELECT DISTINCT table_name FROM information_schema.columns WHERE column_key = "PRI")
AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');

检查可用空间最多的表

• 删除数据之后，mysql的数据表不会自动变小，此时需要找出可用空间最多的几张表，然后执行 optimize 操作

select 
  table_schema,
  table_name,
  table_rows,
  round(sum(data_length+index_length)/1024/1024,2) as total_size_mb,
  round(sum(data_free)/1024/1024,2) as free_size_mb,
  concat('optimize table ',table_name,';') as optimize_table
from information_schema.tables 
where table_schema not in ('information_schema','mysql','performance_schema','sys','percona_schema') 
group by table_schema,table_name 
order by data_free desc 
limit 200;