mysql运维语句

1、检查表空间状态，包括行数、数据大小以及索引大小

SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    engine AS 'Engine',
    table_rows AS 'Rows',
    round(data_length/1024/1024,2) AS 'Data Length (MB)',
    round(index_length/1024/1024,2) AS 'Index Length (MB)'
FROM 
    information_schema.tables
WHERE 
    engine = 'InnoDB';

2、检查碎片化程度较高的表，DATA_FREE 字段，它显示了表末尾未使用的空间大小。

SELECT 
    TABLE_SCHEMA AS 'Database',
    TABLE_NAME AS 'Table',
    CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), ' MB') AS 'Data Length',
    CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), ' MB') AS 'Index Length',
    CONCAT(ROUND(DATA_FREE / 1024 / 1024, 2), ' MB') AS 'Free Space',
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS 'Frag Ratio (%)'
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
    AND DATA_FREE > 0
ORDER BY 
    (DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) DESC;

对于 InnoDB 存储引擎的表，可以使用 SHOW TABLE STATUS 命令来查看表的状态信息，包括碎片信息（Data_free 字段）。

SHOW TABLE STATUS FROM your_database_name WHERE Name = 'your_table_name';

一旦确定了哪些表有较高的碎片程度，可以通过执行 OPTIMIZE TABLE 来整理这些表。这个命令会重建表以减少碎片，并回收未使用的空间。 

OPTIMIZE TABLE your_table_name;

 3、显示 InnoDB 状态，包括缓冲池、事务、锁等信息

SHOW ENGINE INNODB STATUS;

4、查看 MySQL 的变量设置

SHOW GLOBAL VARIABLES LIKE '%innodb%';

5、查看当前正在运行的线程，这条命令显示了当前连接到 MySQL 服务器的所有线程及其执行的查询。这对于识别长时间运行的查询或锁问题很有用。

SHOW FULL PROCESSLIST;

6、查看 MySQL 服务器的二进制日志状态

SHOW MASTER STATUS;

7、连接相关

（1）统计当前连接数

SELECT COUNT(*) FROM information_schema.PROCESSLIST;

（2）查看最大允许连接数

SHOW VARIABLES LIKE 'max_connections';

（3）监控连接相关状态变量

SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
  • Connections: 自服务器启动以来收到的连接请求次数。
  • Threads_connected: 当前打开的连接数量。
  • Aborted_connects: 尝试连接到服务器失败的次数。

8、检查运行时间较长的sql

这段查询尝试结合语句历史记录和阶段当前事件，以识别出执行时间较长的查询。

SELECT 
    t1.THREAD_ID, 
    t1.SQL_TEXT, 
    t2.EVENT_ID, 
    t2.EVENT_NAME, 
    t2.TIMER_WAIT,
    t2.WORK_COMPLETED,
    t2.WORK_ESTIMATED
FROM 
    performance_schema.events_statements_history AS t1
JOIN 
    performance_schema.events_stages_current AS t2 ON t1.THREAD_ID = t2.THREAD_ID
WHERE 
    t1.SQL_TEXT IS NOT NULL
ORDER BY 
    t2.TIMER_WAIT DESC;

查找正在执行语句的耗时 

SELECT 
    t.thread_id, 
    t.processlist_user, 
    t.processlist_host, 
    s.sql_text, 
    s.timer_wait
FROM 
    performance_schema.threads AS t
JOIN 
    performance_schema.events_statements_current AS s ON t.thread_id = s.thread_id
WHERE 
    s.sql_text IS NOT NULL
ORDER BY 
    s.timer_wait DESC;

慢查询日志参数

SHOW VARIABLES LIKE 'slow_query_log%';

9、查询等待事件

（1）查询等待事件

SELECT * FROM performance_schema.events_waits_current;

（2）聚合等待事件

SELECT event_name, count_star, sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE sum_timer_wait > 0
ORDER BY sum_timer_wait DESC;

（3）在查询性能模式表之前，确保相关消费者已经开启，否则可能无法收集到任何数据。可以通过以下命令来检查和设置消费者状态：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%wait%';

10、检查表空间的I/O情况

SELECT 
    FILE_NAME AS file_name,
    COUNT_READ AS count_read,
    SUM_TIMER_READ AS sum_timer_read,
    COUNT_WRITE AS count_write,
    SUM_TIMER_WRITE AS sum_timer_write,
    COUNT_MISC AS count_misc,
    SUM_TIMER_MISC AS sum_timer_misc
FROM 
    performance_schema.file_summary_by_instance
ORDER BY 
    (SUM_TIMER_READ + SUM_TIMER_WRITE + SUM_TIMER_MISC) DESC;

确保相关的 Performance Schema 消费者和仪器已经启用，以便收集所需的 I/O 相关信息： 

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%file%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/io/file/%';

11、事务相关

启用相关度量

SET GLOBAL innodb_monitor_enable = all;

可以查询与事务和锁有关的指标：

SELECT * FROM information_schema.INNODB_METRICS 
WHERE subsystem IN ('transaction', 'lock') 
ORDER BY name;

查看当前活跃事务 

SELECT * FROM information_schema.INNODB_TRX;

 12、检查未使用的索引

首先，确保相关的消费者已经启用：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_waits_%';

然后，执行以下查询来查找未使用的索引： 

SELECT 
    OBJECT_SCHEMA AS schema_name,
    OBJECT_NAME AS table_name,
    INDEX_NAME AS index_name
FROM 
    performance_schema.table_io_waits_summary_by_index_usage
WHERE 
    INDEX_NAME IS NOT NULL
AND 
    COUNT_STAR = 0;

13、计算缓冲池命中率

SET GLOBAL innodb_monitor_enable = all;
SELECT 
    (1 - (buffer_pool_reads / buffer_pool_read_requests)) * 100 AS buffer_pool_hit_rate
FROM (
    SELECT 
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') AS buffer_pool_reads,
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') AS buffer_pool_read_requests
) AS status;

14、检查日志缓冲区

查看当前的日志缓冲区大小

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

 Innodb_log_waits：这是一个状态变量，指示因日志缓冲区空间不足而必须等待刷新到磁盘的次数。如果这个数值持续增长，可能意味着日志缓冲区太小了。如果 Innodb_log_waits 增长迅速，考虑增加 innodb_log_buffer_size 的值。

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

Innodb_os_log_written：显示自服务器启动以来写入到日志文件的字节数。虽然这不是直接关于日志缓冲区的信息，但它可以帮助你了解日志活动的总体水平。

 

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

15、检查排序区

MySQL 提供了几个与排序操作密切相关的系统变量，这些变量控制着排序操作的行为以及分配给排序操作的资源量：

  1. sort_buffer_size：每个需要进行排序的线程会分配这么大小的缓冲区。如果排序操作较多，适当增加这个值可能会提高性能。

  2. read_rnd_buffer_size：这个变量控制读取排序后的行时使用的缓冲区大小。增加这个值可以减少文件排序操作的次数。

  3. max_length_for_sort_data：MySQL 使用内存中的排序算法还是文件排序算法取决于这个变量。如果排序行的数据总长度超过了这个值，MySQL 将使用较慢的文件排序方法。

检查当前设置

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'read_rnd_buffer_size';
SHOW VARIABLES LIKE 'max_length_for_sort_data';

为了了解排序操作对性能的影响程度，可以通过查询 SHOW GLOBAL STATUS 来获取关于排序操作的统计信息：

SHOW GLOBAL STATUS LIKE 'Sort%';

 

注意事项

  • 性能模式可能会对数据库性能产生一定的影响，尤其是在高负载环境下。因此，在生产环境中使用时需要谨慎。
  • 对于非常频繁的 I/O 操作，可能需要定期监控并清理 Performance Schema 数据，避免其占用过多内存。

posted on 2025-06-30 21:23  sw-lab  阅读(13)  评论(0)    收藏  举报  来源

导航