Carry の Blog
Carry の Blog
2024-07-27
目录

查询与内存使用分析

# 查询与内存使用分析

在MySQL数据库性能调优中，理解和分析查询的内存使用情况是至关重要的。通过深入分析查询的内存消耗，可以识别性能瓶颈，优化查询效率，提高系统整体性能。

# 1. 内存使用基础概念

# 1.1 MySQL内存结构

MySQL的内存管理主要包括以下几个方面：

-- MySQL内存主要组成部分：
-- 1. 连接内存（Thread Stack）
-- 2. 查询缓冲区（Query Buffers）
-- 3. 临时表内存（Temporary Tables）
-- 4. 缓冲池（Buffer Pool）
-- 5. 日志缓冲区（Log Buffers）

-- 查看内存相关配置
SHOW VARIABLES LIKE 'thread_stack';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
1
2
3
4
5
6
7
8
9
10
11
12
13

# 1.2 内存分配机制

-- MySQL内存分配机制：
-- 1. 连接级别的内存分配
-- 2. 查询级别的内存分配
-- 3. 临时表内存分配
-- 4. 系统级内存管理

-- 查看内存使用状态
SHOW STATUS LIKE 'Memory%';
SHOW STATUS LIKE 'Created_tmp%';
1
2
3
4
5
6
7
8
9

# 2. 查询内存使用分析

# 2.1 查询执行过程中的内存消耗

-- 查询执行过程中的内存消耗：
-- 1. 解析阶段：词法分析、语法分析
-- 2. 优化阶段：查询优化、执行计划生成
-- 3. 执行阶段：数据读取、处理、返回结果

-- 使用EXPLAIN分析查询内存使用
EXPLAIN SELECT * FROM large_table WHERE condition;
EXPLAIN FORMAT=JSON SELECT * FROM large_table WHERE condition;
1
2
3
4
5
6
7
8

# 2.2 内存使用监控

-- 监控查询内存使用情况
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME LIKE '%Memory%'
ORDER BY VARIABLE_NAME;

-- 关键内存指标：
-- Created_tmp_disk_tables: 创建的磁盘临时表数量
-- Created_tmp_tables: 创建的内存临时表数量
-- Max_used_connections: 最大连接数
-- Threads_connected: 当前连接数
1
2
3
4
5
6
7
8
9
10
11
12
13

# 2.3 临时表内存使用

-- 临时表内存使用分析
-- 1. 内存临时表
-- 2. 磁盘临时表

-- 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp%';

-- 临时表配置
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'internal_tmp_disk_storage_engine';
1
2
3
4
5
6
7
8
9
10
11

# 3. 查询性能与内存关系

# 3.1 大查询的内存消耗

-- 大查询内存消耗分析
-- 1. 大结果集查询
-- 2. 复杂JOIN查询
-- 3. 大量数据排序
-- 4. 复杂聚合查询

-- 分析大查询的内存使用
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE TIME > 30 AND COMMAND != 'Sleep';

-- 优化大查询
-- 1. 添加LIMIT限制
-- 2. 分批处理
-- 3. 使用索引优化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 3.2 排序操作的内存消耗

-- 排序操作内存消耗
-- 1. 内存排序
-- 2. 外部排序

-- 查看排序相关状态
SHOW STATUS LIKE 'Sort%';

-- 排序优化示例
-- 优化前：需要外部排序
SELECT * FROM large_table ORDER BY sort_column;

-- 优化后：使用索引避免排序
CREATE INDEX idx_sort_column ON large_table(sort_column);
SELECT * FROM large_table ORDER BY sort_column;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 3.3 JOIN操作内存消耗

-- JOIN操作内存消耗分析
-- 1. Nested Loop Join
-- 2. Hash Join（MySQL 8.0+）
-- 3. Index Join

-- 查看JOIN相关状态
SHOW STATUS LIKE '%Join%';

-- JOIN优化示例
-- 优化前：无索引JOIN
SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 优化后：有索引JOIN
CREATE INDEX idx_orders_user_id ON orders(user_id);
SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 4. 内存使用诊断工具

# 4.1 Performance Schema分析

-- 使用Performance Schema分析内存使用
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name 
WHERE EVENT_NAME LIKE '%memory%'
ORDER BY SUM_TIMER_WAIT DESC;

-- 查看内存分配详情
SELECT 
    OBJECT_INSTANCE_BEGIN,
    COUNT_ALLOC,
    COUNT_FREE,
    SUM_ALLOCATED
FROM performance_schema.memory_summary_by_account_by_event_name 
WHERE EVENT_NAME LIKE '%memory%'
ORDER BY SUM_ALLOCATED DESC
LIMIT 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 4.2 查询执行计划分析

-- 使用EXPLAIN分析内存使用
EXPLAIN FORMAT=JSON 
SELECT u.name, o.order_date, p.product_name
FROM users u 
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.status = 'active'
AND o.order_date >= '2024-01-01';

-- 分析结果中的内存使用信息
-- 1. Using temporary: 需要创建临时表
-- 2. Using filesort: 需要文件排序
-- 3. Extra: 额外信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 4.3 慢查询日志分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 分析慢查询的内存使用
-- 1. 查看查询执行时间
-- 2. 分析查询复杂度
-- 3. 识别内存密集型查询
1
2
3
4
5
6
7
8
9
10
11

# 5. 内存优化策略

# 5.1 临时表优化

-- 临时表内存优化
-- 1. 调整临时表大小限制
SET GLOBAL tmp_table_size = 256*1024*1024;     -- 256MB
SET GLOBAL max_heap_table_size = 256*1024*1024; -- 256MB

-- 2. 优化临时表使用
-- 避免不必要的临时表创建

-- 检查临时表使用情况
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME LIKE 'Created_tmp%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 5.2 排序优化

-- 排序内存优化
-- 1. 创建合适的索引
CREATE INDEX idx_sort_field ON table_name(sort_field);

-- 2. 调整排序缓冲区
SET GLOBAL sort_buffer_size = 256*1024*1024;    -- 256MB

-- 3. 优化排序查询
-- 避免不必要的排序操作
1
2
3
4
5
6
7
8
9

# 5.3 JOIN优化

-- JOIN内存优化
-- 1. 创建合适的索引
CREATE INDEX idx_join_field ON table_name(join_field);

-- 2. 优化JOIN顺序
-- 让小表驱动大表

-- 3. 使用合适的JOIN类型
-- 根据数据特点选择JOIN算法
1
2
3
4
5
6
7
8
9

# 6. 内存使用监控脚本

# 6.1 实时内存监控

-- 创建内存监控存储过程
DELIMITER //
CREATE PROCEDURE monitor_memory_usage()
BEGIN
    DECLARE tmp_tables BIGINT;
    DECLARE tmp_disk_tables BIGINT;
    DECLARE sort_rows BIGINT;
    DECLARE sort_merge_passes BIGINT;
    
    SELECT VARIABLE_VALUE INTO tmp_tables
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Created_tmp_tables';
    
    SELECT VARIABLE_VALUE INTO tmp_disk_tables
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Created_tmp_disk_tables';
    
    SELECT VARIABLE_VALUE INTO sort_rows
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Sort_rows';
    
    SELECT VARIABLE_VALUE INTO sort_merge_passes
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Sort_merge_passes';
    
    -- 输出监控结果
    SELECT 
        'Memory Usage Monitoring' as check_type,
        tmp_tables as tmp_tables_created,
        tmp_disk_tables as tmp_disk_tables_created,
        sort_rows as sort_rows_processed,
        sort_merge_passes as sort_merge_passes;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 6.2 内存使用报告

-- 创建内存使用报告存储过程
DELIMITER //
CREATE PROCEDURE generate_memory_report()
BEGIN
    SELECT 
        'Memory Usage Report' as report_title,
        NOW() as report_time,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') as tmp_tables,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') as tmp_disk_tables,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Sort_rows') as sort_rows,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Sort_merge_passes') as sort_merge_passes,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') as connections,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') as buffer_pool_reads;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 7. 内存使用优化案例

# 7.1 大查询优化案例

-- 大查询内存优化示例
-- 优化前：全表扫描+大结果集
SELECT * FROM large_table WHERE condition;

-- 优化后：添加索引+限制结果集
CREATE INDEX idx_condition ON large_table(condition);
SELECT * FROM large_table WHERE condition LIMIT 1000;

-- 进一步优化：分批处理
SELECT * FROM large_table WHERE condition LIMIT 1000 OFFSET 0;
SELECT * FROM large_table WHERE condition LIMIT 1000 OFFSET 1000;
1
2
3
4
5
6
7
8
9
10
11

# 7.2 复杂JOIN优化案例

-- 复杂JOIN内存优化示例
-- 优化前：无索引JOIN
SELECT u.name, o.order_date, p.product_name
FROM users u 
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 优化后：添加索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 优化后的查询
SELECT u.name, o.order_date, p.product_name
FROM users u 
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 7.3 排序优化案例

-- 排序内存优化示例
-- 优化前：需要文件排序
SELECT * FROM large_table ORDER BY sort_column;

-- 优化后：添加索引
CREATE INDEX idx_sort_column ON large_table(sort_column);

-- 优化后的查询
SELECT * FROM large_table ORDER BY sort_column;
1
2
3
4
5
6
7
8
9

# 8. 内存使用最佳实践

# 8.1 配置优化建议

-- 内存配置优化建议：
-- 1. 根据服务器内存合理分配各组件内存
-- 2. 临时表内存设置要适度
-- 3. 排序缓冲区设置要足够但不过大

-- 推荐配置示例（基于8GB内存服务器）
SET GLOBAL tmp_table_size = 256*1024*1024;        -- 256MB
SET GLOBAL max_heap_table_size = 256*1024*1024;    -- 256MB
SET GLOBAL sort_buffer_size = 256*1024*1024;       -- 256MB
SET GLOBAL innodb_buffer_pool_size = 4G;           -- 4GB
SET GLOBAL innodb_log_buffer_size = 64*1024*1024;  -- 64MB
1
2
3
4
5
6
7
8
9
10
11

# 8.2 查询优化建议

-- 查询优化建议：
-- 1. 使用索引避免全表扫描
-- 2. 限制结果集大小
-- 3. 避免不必要的复杂查询
-- 4. 合理使用临时表
-- 5. 优化JOIN顺序

-- 优化查询示例
-- 优化前：复杂查询
SELECT u.name, o.order_date, p.product_name, COUNT(*) as item_count
FROM users u 
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.status = 'active'
GROUP BY u.id, o.id, p.id;

-- 优化后：分步处理
-- 第一步：获取用户信息
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id FROM users WHERE status = 'active';

-- 第二步：获取订单信息
CREATE TEMPORARY TABLE temp_orders AS
SELECT o.id, o.user_id, o.order_date 
FROM orders o 
JOIN temp_active_users u ON o.user_id = u.id;

-- 第三步：获取产品信息
SELECT u.name, o.order_date, p.product_name, COUNT(*) as item_count
FROM temp_active_users u 
JOIN temp_orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY u.id, o.id, p.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 8.3 监控和维护

-- 创建内存使用监控事件
CREATE EVENT memory_monitor
ON SCHEDULE EVERY 10 MINUTE
DO
BEGIN
    INSERT INTO memory_usage_log (
        timestamp,
        tmp_tables,
        tmp_disk_tables,
        sort_rows,
        connections
    ) VALUES (
        NOW(),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables'),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Sort_rows'),
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected')
    );
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 9. 总结

查询与内存使用分析是MySQL性能优化的重要组成部分。通过深入理解MySQL的内存管理机制，合理配置内存参数，优化查询语句，可以有效提升数据库性能。

关键优化要点包括：

  1. 理解内存结构：掌握MySQL内存分配机制
  2. 监控内存使用：建立完善的监控体系
  3. 优化查询语句：避免内存密集型操作
  4. 合理配置参数：根据系统资源调整内存设置
  5. 持续优化改进：定期分析和优化内存使用

通过系统性的内存使用分析和优化，可以显著提升MySQL数据库的查询性能和系统稳定性，为业务提供更好的服务体验。

#MySQL#查询分析#内存使用#性能优化
上次更新: 3/4/2026

← Join原理与选择→

最近更新
01
表空间管理与回收
03-04
02
MySQL抖动刷脏页
03-04
03
count函数详解
03-04
更多文章>
Theme by Vdoing