1. MySQL缓存体系

缓存层次结构：
+——————-+——————+——————+
| 缓存类型 | 位置 | 作用 |
+——————-+——————+——————+
| InnoDB缓冲池 | 内存 | 数据页缓存 |
| 键缓冲 | 内存 | MyISAM索引缓存 |
| 查询缓存 | 内存(已废弃) | 查询结果缓存 |
| 操作系统缓存 | 操作系统 | 文件系统缓存 |
+——————-+——————+——————+

查看缓存配置：
mysql> SHOW VARIABLES LIKE ‘%buffer%’;

输出示例：
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 134217728 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| read_buffer_size | 131072 |
+————————-+———–+

2. InnoDB缓冲池

查看缓冲池状态：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool%’;

输出示例：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_pages_data | 7000 |
| Innodb_buffer_pool_pages_dirty | 100 |
| Innodb_buffer_pool_pages_free | 1000 |
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_reads | 1000 |
+—————————————+————-+

查看缓冲池大小：
mysql> SELECT
@@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb,
@@innodb_buffer_pool_instances AS instances;

输出示例：
+—————-+———–+
| buffer_pool_gb | instances |
+—————-+———–+
| 0.1250 | 1 |
+—————-+———–+

3. 键缓冲（MyISAM）

查看键缓冲状态：
mysql> SHOW STATUS LIKE ‘Key%’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107 |
| Key_blocks_used | 10 |
| Key_read_requests | 1000 |
| Key_reads | 10 |
| Key_write_requests | 100 |
| Key_writes | 50 |
+————————+——-+

计算键缓冲命中率：
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_read_requests’) AS read_requests,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_reads’) AS disk_reads,
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_read_requests’)
) * 100, 2) AS hit_ratio;

输出示例：
+—————+————+———–+
| read_requests | disk_reads | hit_ratio |
+—————+————+———–+
| 1000 | 10 | 99.00 |
+—————+————+———–+

4. 查询缓存（MySQL 8.0已移除）

MySQL 8.0之前版本：
mysql> SHOW VARIABLES LIKE ‘query_cache%’;

输出示例（MySQL 5.7）：
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
+——————————+———-+

MySQL 8.4说明：
mysql> SHOW VARIABLES LIKE ‘query_cache%’;

输出示例：
Empty set (0.00 sec)

5. 缓存命中率计算

InnoDB缓冲池命中率：
mysql> SELECT
ROUND(100 – (
(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, 2) AS buffer_pool_hit_ratio;

输出示例：
+————————+
| buffer_pool_hit_ratio |
+————————+
| 99.90 |
+————————+

缓存命中率标准：
+——————-+————+————+
| 缓存类型 | 良好 | 需要优化 |
+——————-+————+————+
| InnoDB缓冲池 | > 99% | < 95% | | 键缓冲 | > 99% | < 90% | +-------------------+------------+------------+

1. InnoDB缓冲池

缓冲池结构：
– 数据页缓存
– 索引页缓存
– 插入缓冲
– 自适应哈希索引
– 锁信息

查看缓冲池配置：
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool%’;

输出示例：
+————————————-+—————-+
| Variable_name | Value |
+————————————-+—————-+
| innodb_buffer_pool_size | 134217728 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_load_at_startup | ON |
+————————————-+—————-+

查看缓冲池页类型：
mysql> SELECT
PAGE_TYPE,
COUNT(*) AS page_count,
ROUND(COUNT(*) * 100.0 / (SELECT SUM(NUMBER_RECORDS)
FROM information_schema.INNODB_BUFFER_POOL_STATS), 2) AS percentage
FROM information_schema.INNODB_BUFFER_PAGE
GROUP BY PAGE_TYPE
ORDER BY page_count DESC;

输出示例：
+——————-+————-+————+
| PAGE_TYPE | page_count | percentage |
+——————-+————-+————+
| INDEX | 5000 | 60.00 |
| DATA | 3000 | 36.00 |
| IBUF_INDEX | 200 | 2.40 |
| UNKNOWN | 100 | 1.20 |
+——————-+————-+————+

2. 键缓冲（MyISAM）

查看键缓冲配置：
mysql> SHOW VARIABLES LIKE ‘key_buffer%’;

输出示例：
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| key_buffer_size | 8388608 |
+—————–+———-+

设置键缓冲大小：
mysql> SET GLOBAL key_buffer_size = 64*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

查看键缓冲使用：
mysql> SHOW STATUS LIKE ‘Key%’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Key_blocks_used | 100 |
| Key_read_requests | 5000 |
| Key_reads | 50 |
+————————+——-+

3. 表缓存

查看表缓存配置：
mysql> SHOW VARIABLES LIKE ‘table_open%’;

输出示例：
+——————+——-+
| Variable_name | Value |
+——————+——-+
| table_open_cache | 4000 |
+——————+——-+

查看表缓存状态：
mysql> SHOW STATUS LIKE ‘Open%tables’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 500 |
| Opened_tables | 1000 |
+—————+——-+

计算表缓存命中率：
mysql> SELECT
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Opened_tables’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Open_tables’)
) * 100, 2) AS table_cache_hit_ratio;

输出示例：
+———————–+
| table_cache_hit_ratio |
+———————–+
| 50.00 |
+———————–+

4. 表定义缓存

查看表定义缓存：
mysql> SHOW VARIABLES LIKE ‘table_definition_cache’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 2000 |
+————————+——-+

查看表定义缓存状态：
mysql> SHOW STATUS LIKE ‘Open_table_definitions’;

输出示例：
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Open_table_definitions| 300 |
+———————–+——-+

5. 线程缓存

查看线程缓存：
mysql> SHOW VARIABLES LIKE ‘thread_cache_size’;

输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 100 |
+——————-+——-+

查看线程缓存状态：
mysql> SHOW STATUS LIKE ‘Threads%’;

输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 50 |
| Threads_connected | 20 |
| Threads_created | 100 |
+——————-+——-+

1. 缓存预热

特点：启动时加载热点数据

配置缓冲池预热：
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_dump%’;

输出示例：
+————————————-+——-+
| Variable_name | Value |
+————————————-+——-+
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_load_at_startup | ON |
+————————————-+——-+

手动导出缓冲池：
mysql> SET GLOBAL innodb_buffer_pool_dump_now = ON;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看导出状态：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_dump_status’;

输出示例：
+——————————–+——————-+
| Variable_name | Value |
+——————————–+——————-+
| Innodb_buffer_pool_dump_status | Dumping of buffer|
+——————————–+——————-+

手动加载缓冲池：
mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 缓存淘汰

特点：LRU算法淘汰旧数据

查看LRU配置：
mysql> SHOW VARIABLES LIKE ‘innodb_old_blocks%’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
+————————+——-+

查看缓冲池LRU状态：
mysql> SELECT
COUNT(*) AS total_pages,
SUM(CASE WHEN NOT YOUNG THEN 1 ELSE 0 END) AS old_pages,
SUM(CASE WHEN YOUNG THEN 1 ELSE 0 END) AS young_pages
FROM information_schema.INNODB_BUFFER_PAGE;

输出示例：
+————-+———–+————–+
| total_pages | old_pages | young_pages |
+————-+———–+————–+
| 8192 | 3000 | 5192 |
+————-+———–+————–+

3. 缓存刷新

特点：定期刷新脏页到磁盘

查看刷新配置：
mysql> SHOW VARIABLES LIKE ‘innodb_flush%’;

输出示例：
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 1 |
+——————————–+——-+

查看脏页状态：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_pages_dirty’;

输出示例：
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| Innodb_buffer_pool_pages_dirty | 100 |
+——————————–+——-+

手动刷新：
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 缓存分区

特点：减少锁竞争

查看缓冲池实例数：
mysql> SELECT @@innodb_buffer_pool_instances;

输出示例：
+——————————–+
| @@innodb_buffer_pool_instances |
+——————————–+
| 1 |
+——————————–+

设置缓冲池实例：
vim /etc/my.cnf

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

5. 自适应哈希索引

特点：自动优化热点查询

查看配置：
mysql> SHOW VARIABLES LIKE ‘innodb_adaptive_hash_index’;

输出示例：
+——————————+——-+
| Variable_name | Value |
+——————————+——-+
| innodb_adaptive_hash_index | ON |
+——————————+——-+

查看哈希索引状态：
mysql> SHOW STATUS LIKE ‘Innodb_adaptive_hash%’;

输出示例：
+————————————–+———-+
| Variable_name | Value |
+————————————–+———-+
| Innodb_adaptive_hash_index_pages | 1000 |
| Innodb_adaptive_hash_index_cells | 50000 |
+————————————–+———-+

1. 缓冲池大小设计

计算原则：
– 专用服务器：物理内存的70-80%
– 共享服务器：物理内存的50-60%
– 最小建议：1GB以上

查看系统内存：
free -g

输出示例：
total used free
Mem: 16 4 12
Swap: 8 0 8

设置缓冲池大小：
vim /etc/my.cnf

[mysqld]
innodb_buffer_pool_size = 12G

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

验证设置：
mysql> SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;

输出示例：
+——————+
| buffer_pool_gb |
+——————+
| 12.0000 |
+——————+

2. 缓冲池实例设计

设计原则：
– 缓冲池 > 1GB：建议设置多个实例
– 实例数：CPU核心数或缓冲池/1GB
– 最大实例数：64

查看CPU核心数：
nproc

输出示例：
8

设置实例数：
vim /etc/my.cnf

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

验证设置：
mysql> SELECT @@innodb_buffer_pool_instances;

输出示例：
+——————————–+
| @@innodb_buffer_pool_instances |
+——————————–+
| 8 |
+——————————–+

3. 表缓存设计

设计原则：
– 根据表数量设置
– 建议值：max_connections * 平均每连接打开表数

查看表数量：
mysql> SELECT COUNT(*) FROM information_schema.TABLES;

输出示例：
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+

设置表缓存：
vim /etc/my.cnf

[mysqld]
table_open_cache = 4000
table_definition_cache = 2000

验证设置：
mysql> SELECT @@table_open_cache, @@table_definition_cache;

输出示例：
+——————–+————————–+
| @@table_open_cache | @@table_definition_cache |
+——————–+————————–+
| 4000 | 2000 |
+——————–+————————–+

4. 线程缓存设计

设计原则：
– 根据连接数设置
– 建议值：max_connections / 3

查看最大连接数：
mysql> SELECT @@max_connections;

输出示例：
+——————-+
| @@max_connections |
+——————-+
| 500 |
+——————-+

设置线程缓存：
vim /etc/my.cnf

[mysqld]
thread_cache_size = 100

验证设置：
mysql> SELECT @@thread_cache_size;

输出示例：
+———————+
| @@thread_cache_size |
+———————+
| 100 |
+———————+

5. 排序和连接缓存设计

查看当前配置：
mysql> SHOW VARIABLES LIKE ‘%buffer_size’ WHERE Variable_name IN
(‘sort_buffer_size’, ‘join_buffer_size’, ‘read_buffer_size’, ‘read_rnd_buffer_size’);

输出示例：
+———————-+———-+
| Variable_name | Value |
+———————-+———-+
| join_buffer_size | 262144 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
+———————-+———-+

设置缓存大小：
vim /etc/my.cnf

[mysqld]
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

注意事项：
– 这些缓存是每个连接分配的
– 设置过大会消耗大量内存
– 建议保持默认或适度增加

1. 缓冲池命中率优化

查看当前命中率：
mysql> SELECT
ROUND(100 – (
(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, 2) AS hit_ratio;

输出示例：
+———–+
| hit_ratio |
+———–+
| 95.00 |
+———–+

优化建议：
– 命中率 < 99%：增加缓冲池大小 - 命中率 < 95%：需要紧急优化 增加缓冲池： mysql> SET GLOBAL innodb_buffer_pool_size = 16*1024*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 脏页刷新优化

查看脏页比例：
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’) AS dirty_pages,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’) AS total_pages,
ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’) * 100, 2) AS dirty_ratio;

输出示例：
+————-+————-+————-+
| dirty_pages | total_pages | dirty_ratio |
+————-+————-+————-+
| 1000 | 8192 | 12.21 |
+————-+————-+————-+

优化刷新参数：
vim /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

3. 表缓存优化

查看表缓存状态：
mysql> SHOW STATUS LIKE ‘Open%tables’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 3500 |
| Opened_tables | 50000 |
+—————+——-+

计算表缓存命中率：
mysql> SELECT
ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Open_tables’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Opened_tables’) * 100, 2) AS table_cache_efficiency;

输出示例：
+————————+
| table_cache_efficiency |
+————————+
| 7.00 |
+————————+

优化表缓存：
vim /etc/my.cnf

[mysqld]
table_open_cache = 8000

4. 线程缓存优化

查看线程缓存状态：
mysql> SHOW STATUS LIKE ‘Threads%’;

输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 50 |
| Threads_connected | 100 |
| Threads_created | 5000 |
+——————-+——-+

计算线程缓存命中率：
mysql> SELECT
ROUND((1 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_created’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Connections’)
)) * 100, 2) AS thread_cache_hit_ratio;

输出示例：
+————————+
| thread_cache_hit_ratio |
+————————+
| 90.00 |
+————————+

优化线程缓存：
vim /etc/my.cnf

[mysqld]
thread_cache_size = 200

5. 预热优化

配置预热：
vim /etc/my.cnf

[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25

手动预热关键表：
mysql> SELECT * FROM hot_table LIMIT 1000;

输出示例：
+—-+——+——-+
| id | name | value |
+—-+——+——-+
| 1 | A | 100 |
| 2 | B | 200 |
+—-+——+——-+

1. 缓冲池监控

创建监控视图：
mysql> CREATE VIEW v_buffer_pool_stats AS
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE ‘Innodb_buffer_pool%’;

查询监控数据：
mysql> SELECT * FROM v_buffer_pool_stats;

输出示例：
+—————————————+————-+
| VARIABLE_NAME | VARIABLE_VALUE|
+—————————————+————-+
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_pages_data | 7000 |
| Innodb_buffer_pool_pages_dirty | 100 |
| Innodb_buffer_pool_pages_free | 1000 |
+—————————————+————-+

2. 缓存命中率监控

创建命中率监控存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE monitor_cache_hit_ratio()
BEGIN
SELECT
‘Buffer Pool’ AS cache_type,
ROUND(100 – (
(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, 2) AS hit_ratio
UNION ALL
SELECT
‘Key Buffer’ AS cache_type,
ROUND(100 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_reads’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_read_requests’)
) * 100, 2) AS hit_ratio;
END //
mysql> DELIMITER ;

执行监控：
mysql> CALL monitor_cache_hit_ratio();

输出示例：
+————–+———–+
| cache_type | hit_ratio |
+————–+———–+
| Buffer Pool | 99.90 |
| Key Buffer | 99.00 |
+————–+———–+

3. 内存使用监控

查看内存使用：
mysql> SELECT
SUBSTRING_INDEX(event_name, ‘/’, 2) AS memory_area,
ROUND(SUM(current_alloc) / 1024 / 1024, 2) AS total_mb
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY memory_area
ORDER BY total_mb DESC;

输出示例：
+—————–+———-+
| memory_area | total_mb |
+—————–+———-+
| memory/innodb | 8192.00|
| memory/sql | 256.00|
| memory/performance| 128.00|
+—————–+———-+

4. 缓存等待监控

查看缓存等待事件：
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS total_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘%buffer%’
ORDER BY COUNT_STAR DESC
LIMIT 10;

输出示例：
+—————————+————+—————+
| EVENT_NAME | COUNT_STAR | total_seconds |
+—————————+————+—————+
| wait/io/buffer_pool | 10000 | 10.00 |
+—————————+————+—————+

5. 告警配置

创建告警存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_cache_health()
BEGIN
DECLARE buffer_hit DECIMAL(5,2);

SELECT ROUND(100 – (
(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, 2) INTO buffer_hit;

IF buffer_hit < 95 THEN SELECT CONCAT('Warning: Buffer pool hit ratio is ', buffer_hit, '%') AS alert; ELSE SELECT CONCAT('OK: Buffer pool hit ratio is ', buffer_hit, '%') AS status; END IF; END // mysql> DELIMITER ;

执行检查：
mysql> CALL check_cache_health();

输出示例：
+——————————————+
| status |
+——————————————+
| OK: Buffer pool hit ratio is 99.90% |
+——————————————+

1. 缓冲池配置规范

配置文件设置：
vim /etc/my.cnf

[mysqld]
# 缓冲池配置
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# 缓冲池预热
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25

# LRU配置
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000

# 刷新配置
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

验证配置：
mysql> SELECT
@@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb,
@@innodb_buffer_pool_instances AS instances;

输出示例：
+—————-+———–+
| buffer_pool_gb | instances |
+—————-+———–+
| 8.0000 | 8 |
+—————-+———–+

2. 表缓存配置规范

配置文件设置：
vim /etc/my.cnf

[mysqld]
table_open_cache = 4000
table_definition_cache = 2000
table_open_cache_instances = 16

验证配置：
mysql> SELECT
@@table_open_cache,
@@table_definition_cache,
@@table_open_cache_instances;

输出示例：
+——————–+————————–+————————–+
| @@table_open_cache | @@table_definition_cache | @@table_open_cache_instances |
+——————–+————————–+————————–+
| 4000 | 2000 | 16 |
+——————–+————————–+————————–+

3. 线程缓存配置规范

配置文件设置：
vim /etc/my.cnf

[mysqld]
thread_cache_size = 100
thread_handling = pool-of-threads

验证配置：
mysql> SELECT @@thread_cache_size, @@thread_handling;

输出示例：
+———————+——————-+
| @@thread_cache_size | @@thread_handling |
+———————+——————-+
| 100 | one-thread-per-connection |
+———————+——————-+

4. 排序连接缓存规范

配置文件设置：
vim /etc/my.cnf

[mysqld]
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

验证配置：
mysql> SELECT
@@sort_buffer_size / 1024 / 1024 AS sort_mb,
@@join_buffer_size / 1024 / 1024 AS join_mb;

输出示例：
+———+———+
| sort_mb | join_mb |
+———+———+
| 1.00 | 1.00 |
+———+———+

5. 监控配置规范

创建监控用户：
mysql> CREATE USER ‘monitor’@’localhost’ IDENTIFIED BY ‘monitor_password’;
mysql> GRANT SELECT ON performance_schema.* TO ‘monitor’@’localhost’;
mysql> FLUSH PRIVILEGES;

输出示例：
Query OK, 0 rows affected (0.01 sec)

创建监控脚本：
vim /opt/mysql/cache_monitor.sh

#!/bin/bash
mysql -u monitor -pmonitor_password -e “CALL monitor_cache_hit_ratio();”

执行监控：
chmod +x /opt/mysql/cache_monitor.sh
/opt/mysql/cache_monitor.sh

输出示例：
+————–+———–+
| cache_type | hit_ratio |
+————–+———–+
| Buffer Pool | 99.90 |
| Key Buffer | 99.00 |
+————–+———–+

1. OLTP场景

特点：高并发、小事务

推荐配置：
innodb_buffer_pool_size = 物理内存的70-80%
innodb_buffer_pool_instances = CPU核心数
innodb_flush_log_at_trx_commit = 1

验证效果：
mysql> SELECT
COUNT(*) AS transactions,
AVG(timer_wait) / 1000000000 AS avg_seconds
FROM performance_schema.events_transactions_summary_by_thread_by_event_name;

输出示例：
+————–+————-+
| transactions | avg_seconds |
+————–+————-+
| 10000 | 0.010 |
+————–+————-+

2. OLAP场景

特点：大查询、复杂分析

推荐配置：
innodb_buffer_pool_size = 物理内存的50-60%
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M

验证效果：
mysql> EXPLAIN SELECT * FROM large_table WHERE condition;

输出示例：
+—-+————-+————-+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————-+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | large_table | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+—-+————-+————-+——+—————+——+———+——+——–+————-+

3. 混合场景

特点：OLTP和OLAP混合

推荐配置：
innodb_buffer_pool_size = 物理内存的60%
sort_buffer_size = 2M
join_buffer_size = 2M

监控资源使用：
mysql> SELECT
SUBSTRING_INDEX(event_name, ‘/’, 2) AS area,
ROUND(SUM(current_alloc) / 1024 / 1024, 2) AS mb
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY area;

输出示例：
+—————–+———-+
| area | mb |
+—————–+———-+
| memory/innodb | 6144.00|
| memory/sql | 512.00|
+—————–+———-+

4. 读密集场景

特点：读多写少

推荐配置：
innodb_buffer_pool_size = 物理内存的80%
innodb_read_only = OFF
innodb_flush_method = O_DIRECT

查看读写比例：
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Com_select’) AS reads,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Com_insert’) +
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Com_update’) +
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Com_delete’) AS writes;

输出示例：
+——-+——–+
| reads | writes |
+——-+——–+
| 90000 | 10000 |
+——-+——–+

5. 写密集场景

特点：写多读少

推荐配置：
innodb_buffer_pool_size = 物理内存的60%
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2

查看写入性能：
mysql> SHOW STATUS LIKE ‘Innodb_row_insert%’;

输出示例：
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| Innodb_row_inserted | 100000|
| Innodb_row_insert_time | 1000 |
+—————————+——-+

1. 缓存状态检查

检查缓冲池状态：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
————–
BUFFER POOL AND MEMORY
————–
Total large memory allocated 8589934592
Dictionary memory allocated 1000000
Buffer pool size 524288
Free buffers 10000
Database pages 500000
Old database pages 180000
Modified db pages 1000

2. 缓存预热

手动预热关键表：
mysql> SELECT COUNT(*) FROM hot_table;

输出示例：
+———-+
| COUNT(*) |
+———-+
| 1000000 |
+———-+

预热索引：
mysql> SELECT * FROM hot_table FORCE INDEX (PRIMARY) LIMIT 1000;

输出示例：
+—-+——+——-+
| id | name | value |
+—-+——+——-+
| 1 | A | 100 |
+—-+——+——-+

3. 缓存清理

清理缓冲池：
mysql> SET GLOBAL innodb_buffer_pool_size = innodb_buffer_pool_size;

输出示例：
Query OK, 0 rows affected (0.00 sec)

清理表缓存：
mysql> FLUSH TABLES;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 缓存调优

动态调整缓冲池：
mysql> SET GLOBAL innodb_buffer_pool_size = 16*1024*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

查看调整进度：
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool_resize_status’;

输出示例：
+———————————-+—————————+
| Variable_name | Value |
+———————————-+—————————+
| Innodb_buffer_pool_resize_status | Completed resizing buffer |
+———————————-+—————————+

5. 问题排查

排查缓存问题：
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS total_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘%buffer%’
ORDER BY SUM_TIMER_WAIT DESC;

输出示例：
+—————————+————+—————+
| EVENT_NAME | COUNT_STAR | total_seconds |
+—————————+————+—————+
| wait/io/buffer_pool | 10000 | 10.00 |
+—————————+————+—————+

# 案例：缓冲池命中率优化

# 问题描述：
# 数据库响应慢，缓冲池命中率低

# 步骤1：分析当前状态
mysql> SELECT
ROUND(100 – (
(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, 2) AS hit_ratio;

# 输出示例：
# +———–+
# | hit_ratio |
# +———–+
# | 85.00 |
# +———–+

# 步骤2：查看当前配置
mysql> SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;

# 输出示例：
# +——————+
# | buffer_pool_gb |
# +——————+
# | 2.0000 |
# +——————+

# 步骤3：查看数据大小
mysql> SELECT
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.TABLES;

# 输出示例：
# +———-+
# | total_gb |
# +———-+
# | 15.00 |
# +———-+

# 步骤4：优化配置
vim /etc/my.cnf

[mysqld]
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8

# 步骤5：重启MySQL
systemctl restart mysqld

# 输出示例：
# MySQL service restarted.

# 步骤6：验证优化效果
mysql> SELECT
ROUND(100 – (
(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, 2) AS hit_ratio;

# 输出示例：
# +———–+
# | hit_ratio |
# +———–+
# | 99.50 |
# +———–+

# 性能对比：
# +——————+————+————+
# | 指标 | 优化前 | 优化后 |
# +——————+————+————+
# | 缓冲池大小 | 2GB | 16GB |
# | 命中率 | 85% | 99.5% |
# | 查询响应时间 | 500ms | 50ms |
# +——————+————+————+

# 案例：MySQL 8.0+替代方案

# 问题描述：
# MySQL 8.0移除了查询缓存，需要替代方案

# 步骤1：确认MySQL版本
mysql> SELECT VERSION();

# 输出示例：
# +———–+
# | VERSION() |
# +———–+
# | 8.4.0 |
# +———–+

# 步骤2：使用应用层缓存
# Redis缓存方案

# 安装Redis
yum install redis -y

# 输出示例：
# Installed: redis

# 启动Redis
systemctl start redis

# 输出示例：
# Redis service started.

# 步骤3：应用层缓存实现
# Python代码示例
import redis
import pymysql

redis_client = redis.Redis(host=’localhost’, port=6379, db=0)

def get_user(user_id):
cache_key = f’user:{user_id}’
cached = redis_client.get(cache_key)

if cached:
return cached

conn = pymysql.connect(host=’localhost’, user=’app’, password=’pwd’, db=’db’)
cursor = conn.cursor()
cursor.execute(‘SELECT * FROM users WHERE id = %s’, (user_id,))
result = cursor.fetchone()

redis_client.setex(cache_key, 3600, str(result))
return result

# 步骤4：验证缓存效果
result = get_user(1)
print(result)

# 输出示例：
# (1, ‘user1’, ‘user1@example.com’)

# 步骤5：监控缓存命中率
redis-cli info stats | grep keyspace

# 输出示例：
# keyspace_hits:10000
# keyspace_misses:1000

# 案例：MySQL + Redis缓存架构

# 问题描述：
# 高并发场景下数据库压力大

# 步骤1：架构设计
# 应用 -> Redis -> MySQL

# 步骤2：配置Redis
vim /etc/redis.conf

maxmemory 4gb
maxmemory-policy allkeys-lru

# 重启Redis
systemctl restart redis

# 输出示例：
# Redis service restarted.

# 步骤3：创建缓存表映射
mysql> CREATE TABLE cache_config (
table_name VARCHAR(100) PRIMARY KEY,
cache_ttl INT DEFAULT 3600,
cache_key_pattern VARCHAR(200)
);

# 输出示例：
# Query OK, 0 rows affected (0.02 sec)

# 步骤4：实现缓存逻辑
# Python代码
def query_with_cache(sql, params, ttl=3600):
cache_key = f’query:{hash(sql + str(params))}’
cached = redis_client.get(cache_key)

if cached:
return eval(cached)

result = execute_query(sql, params)
redis_client.setex(cache_key, ttl, str(result))
return result

# 步骤5：监控缓存效果
mysql> SHOW STATUS LIKE ‘Com_select’;

# 输出示例：
# +—————+——-+
# | Variable_name | Value |
# +—————+——-+
# | Com_select | 10000 |
# +—————+——-+

# Redis监控
redis-cli info stats

# 输出示例：
# keyspace_hits:90000
# keyspace_misses:10000

# 性能对比：
# +——————+————+————+
# | 指标 | 无缓存 | 有缓存 |
# +——————+————+————+
# | QPS | 1000 | 10000 |
# | 响应时间 | 100ms | 10ms |
# | 数据库负载 | 100% | 10% |
# +——————+————+————+

1. 缓冲池配置原则

– 设置为物理内存的70-80%
– 使用多个缓冲池实例
– 开启预热功能
– 监控命中率

2. 表缓存配置原则

– 根据表数量设置
– 监控Open_tables状态
– 定期检查缓存效率

3. 线程缓存配置原则

– 根据连接数设置
– 监控线程创建频率
– 提高缓存命中率

4. 外部缓存使用原则

– 读多写少场景适用
– 设置合理的TTL
– 实现缓存失效机制
– 监控缓存命中率

5. 监控维护原则

– 定期检查缓存命中率
– 监控内存使用
– 及时调整配置
– 做好容量规划

1. 内存限制

– 受物理内存限制
– 需要为操作系统预留内存
– 需要为其他缓存预留内存

2. 数据一致性限制

– 缓存数据可能过期
– 需要实现失效机制
– 写入需要同步更新

3. 功能限制

– 查询缓存已移除
– 部分参数需要重启
– 动态调整有限制

4. 性能限制

– 缓存预热需要时间
– 大数据量加载慢
– 刷新策略影响性能

5. 兼容性限制

– 不同版本配置不同
– 存储引擎差异
– 外部缓存集成复杂

1. 设计阶段检查

[ ] 是否评估了内存需求
[ ] 是否选择了合适的缓存类型
[ ] 是否设置了合理的参数
[ ] 是否规划了监控方案

2. 配置阶段检查

[ ] 缓冲池大小是否合理
[ ] 表缓存是否足够
[ ] 线程缓存是否配置
[ ] 预热功能是否开启

3. 运维阶段检查

[ ] 是否监控了缓存命中率
[ ] 是否设置了告警
[ ] 是否定期检查状态
[ ] 是否优化了配置

4. 性能阶段检查

[ ] 是否分析了性能瓶颈
[ ] 是否调整了缓存参数
[ ] 是否验证了优化效果
[ ] 是否记录了变更历史