一、缓冲池

缓冲池是主内存中的一个区域，InnoDB在访问表和索引数据时会在该区域进行缓存。缓冲池允许直接从内存访问频繁使用的数据，这加快了处理速度。在专用服务器上，通常会将高达80%的物理内存分配给缓冲池。
为了提高高容量读取操作的效率，缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表；很少使用的数据使用最近最少使用（LRU）算法的变体从高速缓存中老化。
了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。

二、innodb_buffer_pool_size

innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

innodb_buffer_pool_size 默认是128M，

        缓冲池的大小（以字节为单位），即InnoDB缓存表和索引数据的内存区域。默认值为134217728字节（128MB）。最大值取决于CPU架构；32位系统上的最大值为4294967295（2^32-1），64位系统上为18446744073709551615（2^64-1）。在32位系统上，CPU体系结构和操作系统可能会施加比所述最大值更低的实际最大大小。当缓冲池的大小大于1GB时，将innodb_buffer_pool_instances设置为大于1的值可以提高繁忙服务器上的可扩展性。

        较大的缓冲池需要较少的磁盘I/O才能多次访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为计算机物理内存大小的80%。配置缓冲池大小时，请注意以下潜在问题，并准备在必要时缩减缓冲池的大小。

对物理内存的竞争可能会导致操作系统中出现分页。
InnoDB为缓冲区和控制结构保留了额外的内存，因此分配的总空间比指定的缓冲池大小大大约10%。
缓冲池的地址空间必须是连续的，这在具有在特定地址加载DLL的Windows系统上可能是一个问题。
初始化缓冲池的时间与其大小大致成正比。在具有大型缓冲池的实例上，初始化时间可能很长。为了缩短初始化周期，可以在服务器关闭时保存缓冲池状态，并在服务器启动时恢复。

        当您增加或减少缓冲池大小时，操作是以块为单位执行的。区块大小由innodb_buffer_pool_Chunk_size变量定义，默认值为128 MB。

        缓冲池大小必须始终等于或等于innodb_Buffer_pool_chunk_size*innodb_Buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances或其倍数的值，则缓冲池大小将自动调整为等于innodd_buffer_pool_chunk_size*innodb_buffer_poor_instances或其多倍的值。

        innodb_buffer_pool_size可以动态设置，这允许您在不重新启动服务器的情况下调整缓冲池的大小。Innodb_buffer_pool_resize_status状态变量报告在线缓冲池大小调整操作的状态。有关更多信息，请参阅第15.8.3.1节“配置InnoDB缓冲池大小”。

        如果启用了innob_dedicated_server，则如果未显式定义innodb_buffer_pool_size值，则会自动配置该值。有关更多信息，请参阅第15.8.12节“启用专用MySQL服务器的自动配置”。

innodb_buffer_pool_chunk_size 默认是128M

innodb_buffer_pool_instances 默认是8（如果innodb_buffer_pool_size < 1GB，则是1）

2.1查看现有配置

/opt/mysql-8.0.32/bin/mysql -h 127.0.0.1 -u root -p

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728     |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)

2.2 简单优化

把innodb_buffer_pool_size设置为1G。

专用服务器可以设为内存70%以上，个人建议innodb_buffer_pool_size设置为系统内存的50%。

最好设置为：innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

否则，innodb_buffer_pool_size自动调整可能是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的两倍。

my.cnf

# innodb缓冲池大小
innodb_buffer_pool_size=1G
 
# innodb缓冲池块大小
innodb_buffer_pool_chunk_size=128M
 
# innodb缓冲池实例数
innodb_buffer_pool_instances=8

重启数据库

调整后：

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)

这些参数也支持在线调整，可考虑在业务低谷时调整。

2.3配置是否合适
2.3.1查询缓存命中率：

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0            |
| Innodb_buffer_pool_read_ahead         | 20294922     |
| Innodb_buffer_pool_read_ahead_evicted | 1240192      |
| Innodb_buffer_pool_read_requests      | 299216558100 |
| Innodb_buffer_pool_reads              | 1167281260   |
+---------------------------------------+--------------+

Innodb_buffer_pool_read_requests：逻辑读取请求的数量。
Innodb_buffer_pool_reads：InnoDB无法从缓冲池满足的逻辑读取数，必须直接从磁盘读取。
percent = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%
上述的 percent>=99%，则表示当前的buffer pool满足当前的需求。否则需要考虑增加 innodb_buffer_pool_size的值。

2.3.2缓存数据页占比：

mysql> show status like 'Innodb_buffer_pool_pages%';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data    | 7003     |
| Innodb_buffer_pool_pages_dirty   | 0        |
| Innodb_buffer_pool_pages_flushed | 19906085 |
| Innodb_buffer_pool_pages_free    | 1021     |
| Innodb_buffer_pool_pages_misc    | 167      |
| Innodb_buffer_pool_pages_total   | 8191     |
+----------------------------------+----------+

innodb_buffer_pool_pages_data：InnoDB缓冲池中包含数据的页数。这个数字包括脏页和干净页。（使用压缩表时，报告的Innodb_buffer_pool_pages_数据值可能大于）
percent = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
上述的 percent>=95% 则表示当前的innodb_buffer_pool_size满足当前的需求。否则可以考虑增加 innodb_buffer_pool_size的值。

2.4如何判断MySQL使用内存会不会过高

可能还有有一些担心，所有参数设置完毕后MySQL的占用会过高导致内存溢出，那么我们可以算一下他会不会太高。
通过下面的SQL语句：
SELECT ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size)/1024/1024)+((@@read_rnd_buffer_size+@@read_buffer_size+@@myisam_sort_buffer_size+@@sort_buffer_size+@@join_buffer_size)/1024/1024*@@max_connections);
最终单位为MB
若该值不超过系统可用内存，说明还好（理论）

2.5其他命令

mysql> show status like 'Innodb_buffer_pool%';
+-------------------------------------------+--------------------------------------------------+
| Variable_name                             | Value                                            |
+-------------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status            | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 230316 18:50:53 |
| Innodb_buffer_pool_resize_status          |                                                  |
| Innodb_buffer_pool_resize_status_code     | 0                                                |
| Innodb_buffer_pool_resize_status_progress | 0                                                |
| Innodb_buffer_pool_pages_data             | 11658                                            |
| Innodb_buffer_pool_bytes_data             | 191004672                                        |
| Innodb_buffer_pool_pages_dirty            | 0                                                |
| Innodb_buffer_pool_bytes_dirty            | 0                                                |
| Innodb_buffer_pool_pages_flushed          | 80730                                            |
| Innodb_buffer_pool_pages_free             | 53706                                            |
| Innodb_buffer_pool_pages_misc             | 172                                              |
| Innodb_buffer_pool_pages_total            | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd         | 0                                                |
| Innodb_buffer_pool_read_ahead             | 2529                                             |
| Innodb_buffer_pool_read_ahead_evicted     | 0                                                |
| Innodb_buffer_pool_read_requests          | 115191477                                        |
| Innodb_buffer_pool_reads                  | 6644                                             |
| Innodb_buffer_pool_wait_free              | 0                                                |
| Innodb_buffer_pool_write_requests         | 1598891                                          |
+-------------------------------------------+--------------------------------------------------+
20 rows in set (0.00 sec)

mysql> show engine innodb status \G
mysql> SHOW GLOBAL STATUS \G 太多了。

三、其他优化：

# 连接操作缓冲区，默认256K

join_buffer_size = 8M

# 排序操作缓冲区，默认256K
sort_buffer_size = 8M

# 顺序读取缓冲区，默认128K

read_buffer_size = 4M

# 随机读取缓冲区，默认128K
read_rnd_buffer_size = 8M

mysql> show variables like '%buffer_size%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608  |
| innodb_ddl_buffer_size  | 1048576  |
| innodb_log_buffer_size  | 16777216 |
| innodb_sort_buffer_size | 1048576  |
| join_buffer_size        | 262144   |
| key_buffer_size         | 8388608  |
| myisam_sort_buffer_size | 8388608  |
| preload_buffer_size     | 32768    |
| read_buffer_size        | 131072   |
| read_rnd_buffer_size    | 262144   |
| select_into_buffer_size | 131072   |
| sort_buffer_size        | 262144   |
+-------------------------+----------+
12 rows in set (0.01 sec)