MySQL 内存使用率分析语句大全

一、全局内存使用概览

1.1 查看内存总体分配

SHOW VARIABLES 
WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'key_buffer_size',
    'query_cache_size',
    'tmp_table_size',
    'max_heap_table_size',
    'innodb_log_buffer_size',
    'sort_buffer_size',
    'read_buffer_size',
    'read_rnd_buffer_size',
    'join_buffer_size',
    'thread_stack'
);

1.2 内存使用率计算

SELECT 
    ROUND(SUM(variable_value)/1024/1024,2) AS total_allocated_mb,
    ROUND(@@global.innodb_buffer_pool_size/1024/1024,2) AS innodb_buffer_pool_mb,
    ROUND(@@global.key_buffer_size/1024/1024,2) AS key_buffer_mb,
    ROUND((SUM(variable_value) - @@global.innodb_buffer_pool_size - @@global.key_buffer_size)/1024/1024,2) AS other_memory_mb
FROM performance_schema.global_variables
WHERE variable_name IN (
    'innodb_buffer_pool_size',
    'key_buffer_size',
    'query_cache_size',
    'tmp_table_size',
    'max_heap_table_size',
    'innodb_log_buffer_size',
    'sort_buffer_size',
    'read_buffer_size',
    'read_rnd_buffer_size',
    'join_buffer_size',
    'thread_stack'
);

二、InnoDB 缓冲池分析

2.1 缓冲池使用状态

SELECT 
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024,2) AS total_data_index_mb,
    ROUND(@@innodb_buffer_pool_size/1024/1024,2) AS buffer_pool_size_mb,
    ROUND((SUM(DATA_LENGTH + INDEX_LENGTH)/@@innodb_buffer_pool_size)*100,2) AS usage_percentage
FROM information_schema.TABLES
WHERE ENGINE='InnoDB';

2.2 缓冲池命中率

SELECT 
    (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 
    AS buffer_pool_hit_ratio;

三、会话级内存分析

3.1 当前连接内存使用

SELECT 
    thread_id,
    user,
    db,
    command,
    time,
    ROUND(mem.current_number_of_bytes_used/1024/1024,2) AS memory_used_mb,
    event_name
FROM performance_schema.threads t
JOIN performance_schema.memory_summary_by_thread_by_event_name mem ON t.thread_id = mem.thread_id
WHERE t.PROCESSLIST_ID = CONNECTION_ID();

3.2 内存消耗TOP会话

SELECT 
    t.processlist_id AS connection_id,
    t.processlist_user AS user,
    t.processlist_host AS host,
    t.processlist_db AS database,
    t.processlist_command AS command,
    ROUND(SUM(mem.current_number_of_bytes_used)/1024/1024,2) AS total_memory_mb,
    GROUP_CONCAT(DISTINCT mem.event_name) AS memory_events
FROM performance_schema.threads t
JOIN performance_schema.memory_summary_by_thread_by_event_name mem ON t.thread_id = mem.thread_id
WHERE t.processlist_user IS NOT NULL
GROUP BY connection_id, user, host, database, command
ORDER BY total_memory_mb DESC
LIMIT 10;

四、内存事件统计

4.1 按事件类型统计内存

SELECT 
    event_name,
    ROUND(SUM(current_number_of_bytes_used)/1024/1024,2) AS current_mb,
    ROUND(SUM(high_number_of_bytes_used)/1024/1024,2) AS high_water_mark_mb,
    COUNT_ALLOC,
    COUNT_FREE
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_number_of_bytes_used > 0
ORDER BY current_mb DESC
LIMIT 20;

4.2 InnoDB内存分配详情

SELECT 
    event_name,
    ROUND(current_number_of_bytes_used/1024/1024,2) AS current_mb,
    ROUND(high_number_of_bytes_used/1024/1024,2) AS high_water_mark_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name LIKE 'memory/innodb/%'
ORDER BY current_mb DESC;

五、临时内存使用分析

5.1 临时表内存使用

SELECT 
    ROUND(SUM(tmp_table_size)/1024/1024,2) AS configured_tmp_table_size_mb,
    ROUND(SUM(created_tmp_tables * avg_tmp_table_size)/1024/1024,2) AS estimated_used_mb,
    ROUND(SUM(created_tmp_disk_tables * avg_tmp_table_size)/1024/1024,2) AS estimated_disk_used_mb
FROM (
    SELECT 
        @@tmp_table_size AS tmp_table_size,
        variable_value AS created_tmp_tables,
        0 AS created_tmp_disk_tables,
        0 AS avg_tmp_table_size
    FROM performance_schema.global_status
    WHERE variable_name = 'Created_tmp_tables'
    
    UNION ALL
    
    SELECT 
        @@tmp_table_size,
        0,
        variable_value,
        0
    FROM performance_schema.global_status
    WHERE variable_name = 'Created_tmp_disk_tables'
    
    UNION ALL
    
    SELECT 
        @@tmp_table_size,
        0,
        0,
        (@@tmp_table_size * 0.3) -- 假设平均使用30%的配置大小
) AS tmp_usage;

5.2 排序缓冲区使用

SELECT 
    variable_value AS sort_operations,
    ROUND(variable_value * @@sort_buffer_size/1024/1024,2) AS estimated_memory_mb
FROM performance_schema.global_status
WHERE variable_name = 'Sort_merge_passes';

六、内存泄漏检测

6.1 内存增长趋势

SELECT 
    event_name,
    ROUND(current_number_of_bytes_used/1024/1024,2) AS current_mb,
    ROUND(current_number_of_bytes_used/alloc_count,2) AS avg_alloc_bytes
FROM performance_schema.memory_summary_global_by_event_name
WHERE alloc_count > 0 AND free_count < alloc_count
ORDER BY (current_number_of_bytes_used/alloc_count) DESC
LIMIT 10;

6.2 未释放内存统计

SELECT 
    event_name,
    alloc_count,
    free_count,
    (alloc_count - free_count) AS leak_count,
    ROUND(current_number_of_bytes_used/1024/1024,2) AS current_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE free_count < alloc_count
ORDER BY leak_count DESC
LIMIT 10;

七、内存配置优化建议

7.1 内存配置评估

SELECT 
    variable_name,
    ROUND(variable_value/1024/1024,2) AS current_value_mb,
    CASE 
        WHEN variable_name = 'innodb_buffer_pool_size' THEN 
            CONCAT('建议: ', ROUND((SELECT SUM(data_length + index_length) FROM information_schema.tables WHERE engine = 'InnoDB') * 1.2 /1024/1024,2), ' MB')
        WHEN variable_name = 'key_buffer_size' AND 
            (SELECT COUNT(*) FROM information_schema.tables WHERE engine = 'MyISAM') = 0 THEN 
            'MyISAM表不存在，可降低此值'
        WHEN variable_name LIKE '%buffer_size' AND 
            variable_value > 16777216 THEN 
            '可能过大，建议评估连接数'
        ELSE '当前值合理'
    END AS recommendation
FROM performance_schema.global_variables
WHERE variable_name IN (
    'innodb_buffer_pool_size',
    'key_buffer_size',
    'query_cache_size',
    'sort_buffer_size',
    'read_buffer_size',
    'read_rnd_buffer_size',
    'join_buffer_size',
    'tmp_table_size',
    'max_heap_table_size'
);

7.2 连接内存计算

SELECT 
    @@max_connections AS max_connections,
    ROUND((@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@join_buffer_size + @@tmp_table_size) / 1024 / 1024, 2) AS per_connection_mb,
    ROUND(@@max_connections * (@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@join_buffer_size + @@tmp_table_size) / 1024 / 1024, 2) AS total_potential_mb,
    ROUND(@@innodb_buffer_pool_size / 1024 / 1024, 2) AS innodb_buffer_pool_mb,
    CASE 
        WHEN (@@max_connections * (@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@join_buffer_size + @@tmp_table_size)) > @@innodb_buffer_pool_size THEN 
            CONCAT('警告: 连接内存(', 
                ROUND(@@max_connections * (@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@join_buffer_size + @@tmp_table_size) / 1024 / 1024, 2), 
                ' MB) 超过InnoDB缓冲池大小')
        ELSE '连接内存配置合理'
    END AS evaluation
FROM dual;

八、实时监控查询

8.1 内存使用趋势

SELECT 
    NOW() AS sample_time,
    ROUND((SELECT SUM(variable_value) FROM performance_schema.global_status WHERE variable_name LIKE 'Memory%used')/1024/1024,2) AS total_used_mb,
    ROUND(@@innodb_buffer_pool_size/1024/1024,2) - 
    ROUND((SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_free') * 
    @@innodb_page_size/1024/1024,2) AS innodb_used_mb,
    ROUND((SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Key_blocks_used') * 
    @@key_cache_block_size/1024/1024,2) AS key_buffer_used_mb
FROM dual;

8.2 内存压力指标

SELECT 
    variable_name,
    variable_value,
    CASE 
        WHEN variable_name LIKE '%wait%' THEN 
            CONCAT(ROUND(variable_value/(SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Uptime')*100,2), '%')
        ELSE variable_value
    END AS normalized_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_wait_free',
    'Table_open_cache_overflows',
    'Key_reads',
    'Key_read_requests',
    'Innodb_row_lock_waits'
);

关键分析技巧：

重点关注Innodb_buffer_pool_hit_ratio，应保持在95%以上
当Created_tmp_disk_tables与Created_tmp_tables比值过高时，需增加tmp_table_size
memory/innodb/%事件的内存使用应与缓冲池配置匹配
每个连接的内存消耗 = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size
内存泄漏检测关注alloc_count与free_count的差异