java八股文面试[数据库]——InnoDB相关的参数优化

ゝ一纸荒年。 2024-03-08 08:25 136阅读 0赞

###### 1.说一下InnoDB内存相关的参数优化？ ######

Buffer Pool参数优化

###### 1.1 **缓冲池内存**大小配置 ######

一个大的日志缓冲区允许**大量的事务**在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置这个参数会大量的**减少磁盘I/O的次数**。 建议: 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的60% - 80%.

*  **查看缓冲池大小**
    
        mysql> show variables like '%innodb_buffer_pool_size%';
        +-------------------------+-----------+
        | Variable_name           | Value     |
        +-------------------------+-----------+
        | innodb_buffer_pool_size | 134217728 |
        +-------------------------+-----------+
        
        mysql> select 134217728 / 1024 / 1024;
        +-------------------------+
        | 134217728 / 1024 / 1024 |
        +-------------------------+
        |            128.00000000 |
        +-------------------------+
 *  **在线调整InnoDB缓冲池大小** **innodb\_buffer\_pool\_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小.**
    
        mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512
        Query OK, 0 rows affected (0.10 sec)
        
        mysql> show variables like '%innodb_buffer_pool_size%';
        +-------------------------+-----------+
        | Variable_name           | Value     |
        +-------------------------+-----------+
        | innodb_buffer_pool_size | 268435456 |
        +-------------------------+-----------+
    
    **监控在线调整缓冲池的进度**
    
        mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
        +----------------------------------+----------------------------------------------------------------------+
        | Variable_name                    | Value                                                        |
        +----------------------------------+----------------------------------------------------------------------+
        | Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
        +----------------------------------+----------------------------------------------------------------------+

###### **1.2 InnoDB 缓存性能评估** ######

当前配置的innodb\_buffer\_pool\_size是否合适，可以通过分析InnoDB缓冲池的**缓存命中率**来验证。

*  以下公式计算InnoDB buffer pool **命中率**:
    
        命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)* 100
        
        参数1: innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
        参数2: innodb_buffer_pool_read_requests：表示从内存中读取页的请求数。
    
        mysql> show status like 'innodb_buffer_pool_read%';
        +---------------------------------------+-------+
        | Variable_name                         | Value |
        +---------------------------------------+-------+
        | Innodb_buffer_pool_read_ahead_rnd     | 0     |
        | Innodb_buffer_pool_read_ahead         | 0     |
        | Innodb_buffer_pool_read_ahead_evicted | 0     |
        | Innodb_buffer_pool_read_requests      | 12701 |
        | Innodb_buffer_pool_reads              | 455   |
        +---------------------------------------+-------+
        
        -- 此值低于90%，则可以考虑增加innodb_buffer_pool_size。
        mysql> select 12701 / (455 + 12701) * 100 ;
        +-----------------------------+
        | 12701 / (455 + 12701) * 100 |
        +-----------------------------+
        |                     96.5415 |
        +-----------------------------+

###### **1.3 Page管理相关参数** ######

**查看Page页的大小(默认16KB),`innodb_page_size`**只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使用默认页面大小初始化实例。

mysql> show variables like '%innodb_page_size%'; 
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | innodb_page_size | 16384 |
    +------------------+-------+

**Page页管理状态相关参数**

mysql> show global status like '%innodb_buffer_pool_pages%';
    +----------------------------------+-------+
    | Variable_name                    | Value |
    +----------------------------------+-------+
    | Innodb_buffer_pool_pages_data    | 515   |
    | Innodb_buffer_pool_pages_dirty   | 0     |
    | Innodb_buffer_pool_pages_flushed | 334   |
    | Innodb_buffer_pool_pages_free    | 15868 |
    | Innodb_buffer_pool_pages_misc    | 0     |
    | Innodb_buffer_pool_pages_total   | 16383 |
    +----------------------------------+-------+

**pages\_data**: InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。

**pages\_dirty**: 显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。

**pages\_flushed**: 表示从InnoDB缓冲池中刷新脏页的请求数。

**pages\_free**: 显示InnoDB缓冲池中的空闲页面

**pages\_misc**: 缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据页的数目

**pages\_total**: 缓存池的页总数目。单位是page。

###### 2.InnoDB日志相关的参数优化了解过吗？ ######

###### **2.1.日志缓冲区相关参数配置** ######

日志缓冲区的大小。一般默认值**16MB是够用**的，但如果事务之中**含有blog/text等大字段**，这个缓冲区会被很快填满会引起额外的IO负载。配置更大的日志缓冲区,可以有效的提高MySQL的效率.

*  **innodb\_log\_buffer\_size 缓冲区大小**
    
        mysql> show variables like 'innodb_log_buffer_size';
        +------------------------+----------+
        | Variable_name          | Value    |
        +------------------------+----------+
        | innodb_log_buffer_size | 16777216 |
        +------------------------+----------+
 *  innodb\_log\_files\_in\_group **日志组文件个数** 日志组根据需要来创建。而日志组的成员则需要至少2个，实现循环写入并作为冗余策略**。**
    
        mysql> show variables like 'innodb_log_files_in_group';
        +---------------------------+-------+
        | Variable_name             | Value |
        +---------------------------+-------+
        | innodb_log_files_in_group | 2     |
        +---------------------------+-------+
 *  innodb\_**log\_file\_size** 日志文件大小 参数innodb\_log\_file\_size用于设定MySQL日志组中每个日志文件的大小(默认**48M**)。此参数是一个全局的静态参数，不能动态修改。 参数innodb\_log\_file\_size的最大值，二进制日志文件大小（innodb\_log\_file\_size \* innodb\_log\_files\_in\_group）**不能超过512GB**.所以**单个日志文件的大小不能超过256G**.
    
        mysql> show variables like 'innodb_log_file_size';
        +----------------------+----------+
        | Variable_name        | Value    |
        +----------------------+----------+
        | innodb_log_file_size | 50331648 |
        +----------------------+----------+

###### **2.2.日志文件参数优化** ######

首先我们先来看一下**日志文件大小**设置对性能的影响

*  设置过小
    
    1.  参数 `innodb_log_file_size`设置太小，就会导致MySQL的日志文件( **redo log）频繁切换**，频繁的触发数据库的**检查点（Checkpoint）**，导致刷新脏页到磁盘的次数增加。从而影响IO性能。
    2.  处理**大事务时**，将所有的日志文件写满了，事务内容还没有写完，这样就会导致日志**不能切换**.
 *  设置过大 参数 `innodb_log_file_size`如果设置太大，虽然可以提升IO性能，但是当MySQL由于**意外宕机时，二进制日志很大**，那么**恢复的时间必然很长**。而且这个恢复时间往往不可控，受多方面因素影响。

**优化建议:**

如何设置合适的日志文件大小 ?

*  根据实际生产场景的优化经验,一般是**计算一段时间内**生成的事务日志（redo log）的大小， 而MySQL的日志文件的大小最少应该承载**一个小时的业务日志量**(官网文档中有说明)。

想要估计一下InnoDB redo log的大小，需要抓取一段时间内Log SequenceNumber（日志顺序号）的数据,来计算一小时内产生的日志大小.

> **Log sequence number**
> 
> 自系统修改开始，就不断的生成redo日志。为了记录一共生成了多少日志，于是mysql设计了全局变量log sequence number，简称lsn，但不是从0开始，是**从8704字节开始**。

-- pager分页工具, 只获取 sequence的信息
    mysql> pager grep sequence;
    PAGER set to 'grep sequence'
    
    -- 查询状态,并倒计时一分钟
    mysql> show engine innodb status\G select sleep(60);
    Log sequence number 5399154
    1 row in set (0.00 sec)
    
    1 row in set (1 min 0.00 sec)
    
    -- 一分时间内所生成的数据量 5406150
    mysql> show engine innodb status\G;
    Log sequence number 5406150
    
    -- 关闭pager
    mysql> nopager;
    PAGER set to stdout

有了一分钟的日志量,据此推算**一小时内的日志量**

mysql> select (5406150 - 5399154) / 1024 as kb_per_min;
    +------------+
    | kb_per_min |
    +------------+
    |     6.8320 |
    +------------+
    
    mysql> select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
    +------------+
    | kb_per_min |
    +------------+
    |   409.9219 |
    +------------+

**太大的缓冲池**或非常**不正常的业务负载**可能会计算出非常大(或非常小)的日志大小。这也是公式不足之处，需要根据判断和经验。但这个计算方法是一个很好的参考标准。

###### 3.InnoDB IO线程相关参数优化了解过吗？ ######

数据库属于 **IO 密集型**的应用程序，其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是**微秒级别**，而从一块普通硬盘上读取一个IO是在**毫秒级别**。要优化数据库，IO操作是必须要优化的，尽可能将磁盘IO转化为内存IO。

**1) 参数: query\_cache\_size&have\_query\_cache** MySQL查询缓存会保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，**跳过了解析，优化和执行阶段**。 查询缓存会跟踪查询中涉及的每个表，如果这些表**发生变化**，那么和这个表相关的所有**缓存都将失效**。

1.  查看查询缓存是否开启

1.  开启缓存,在my.ini中添加下面一行参数

query_cache_size=128M
    query_cache_type=1
    
    query_cache_type:
    设置为0，OFF,缓存禁用
    设置为1，ON,缓存所有的结果
    设置为2，DENAND,只缓存在select语句中通过SQL_CACHE指定需要缓存的查询

1.  测试能否缓存查询

mysql> show status like '%Qcache%';
      +-------------------------+---------+
      | Variable_name           | Value   |
      +-------------------------+---------+
      | Qcache_free_blocks      | 1       |
      | Qcache_free_memory      | 1031832 |
      | Qcache_hits             | 0       |
      | Qcache_inserts          | 0       |
      | Qcache_lowmem_prunes    | 0       |
      | Qcache_not_cached       | 1       |
      | Qcache_queries_in_cache | 0       |
      | Qcache_total_blocks     | 1       |
      +-------------------------+---------+

*  **Qcache\_free\_blocks**:缓存中目前剩余的**blocks数量**（如果值较大，则查询缓存中的内存碎片过多）
 *  **Qcache\_free\_memory**:**空闲缓存**的内存大小
 *  **Qcache\_hits**:命中缓存次数
 *  **Qcache\_inserts**: 未命中然后进行正常查询
 *  **Qcache\_lowmem\_prunes**:查询因为内存不足而被移除出查询缓存记录
 *  **Qcache\_not\_cached**: 没有被缓存的查询数量
 *  **Qcache\_queries\_in\_cache**:当前缓存中缓存的查询数量
 *  **Qcache\_total\_blocks**:当前缓存的block数量

**优化建议**: Query Cache的使用需要多个参数配合，其中最为关键的是 **query\_cache\_size** 和 **query\_cache\_type** ，前者设置用于缓存 ResultSet 的**内存大小**，后者设置在何**场景下**使用 Query Cache。

MySQL数据库数据变化相对不多，query\_cache\_size **一般设置为256MB比较合适** ,也可以通过计算Query Cache的命中率来进行调整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

2) 参数: innodb\_**max\_dirty**\_pages\_pct 该参数是InnoDB 存储引擎用来控制buffer pool中**脏页**的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会**启动刷脏页**的操作。

-- innodb_max_dirty_pages_pct 参数可以动态调整，最小值为0， 最大值为99.99，默认值为 75。
    mysql> show variables like 'innodb_max_dirty_pages_pct';
    +----------------------------+-----------+
    | Variable_name              | Value     |
    +----------------------------+-----------+
    | innodb_max_dirty_pages_pct | 75.000000 |
    +----------------------------+-----------+

**优化建议**: 该参数**比例值越大**，从内存到磁盘的写入操作就会**相对减少**，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中.最大不建议超过90,一般重启恢复的数据在超过1GB的话,启动速度就会变慢.

**3) 参数: innodb\_old\_blocks\_pct&innodb\_old\_blocks\_time** `innodb_old_blocks_pct` 用来确定**LRU链**表中old sublist所占比例,默认占用**37%**

mysql> show variables like '%innodb_old_blocks_pct%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_old_blocks_pct | 37    |
    +-----------------------+-------+

`innodb_old_blocks_time` 用来控制old sublist中page的转移策略，新的page页在进入LRU链表中时，会先插入到old sublist的头部，然后page需要在old sublist中停留innodb\_old\_blocks\_time这么久后，下一次对该page的访问才会使其移动到new sublist的头部，默认值1秒.

mysql> show variables like '%innodb_old_blocks_time%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_old_blocks_time | 1000  |
    +------------------------+-------+

**优化建议**: 在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加innodb\_old\_blocks\_pct的值，并且减小innodb\_old\_blocks\_time的值。让数据页能够更快和更多的进入的热点数据区。

知识来源：马士兵教育