Detailed explanation of cache optimization for MySQL optimization (2)

This article is the first article in the MySQL optimization series Cache Optimization continuation. It introduces all aspects of cache optimization in more detail. I hope you will like it

MySQL is cached everywhere inside. When I read the source code of MySQL, I will analyze in detail how the cache is used. This part mainly includes various explicit cache optimizations:

1. QueryCache optimization

2. Result set cache

3. Sort cache

4. join connection cache

5. Table cache Cache and table structure definition cache Cache

6. Table scan cache buffer

7. MyISAMIndexCache buffer

8. ##Log cache

9. Read-ahead mechanism

10. Delayed table and temporary table

    
    

1. Query Cache optimization

The query cache not only caches the query statement structure, but also caches the query results. Within a period of time, if it is the same SQL, the results will be read directly from the cache to improve the efficiency of data search. But when the data in the cache is inconsistent with the data on the hard disk, the cache will become invalid.

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name        | Value  |
+------------------------------+---------+
| have_query_cache       | YES   |
| query_cache_limit      | 1048576 |
| query_cache_min_res_unit   | 4096  |
| query_cache_size       | 1048576 |
| query_cache_type       | OFF   |
| query_cache_wlock_invalidate | OFF   |
+------------------------------+---------+

have_query_cache Whether to support query cache.

query_cache_limit If the result set size of a select statement exceeds the querycachelimit value, this result set will not be added to the query cache.

query_cache_min_res_unit The query cache applies for memory space in blocks, and the block size applied for each time is the set value. 4K is a very reasonable value and does not need to be modified.

query_cache_size Query cache size.

query_cache_type Query cache type, values ​​are 0 (OFF), 1 (ON), 2 (DEMOND). OFF indicates that the query cache is turned off. ON means that the query is always searched in the query cache first, unless the sql_no_cache option is included in the select statement. DEMOND indicates that caching is not applicable unless the sql_cache option is included in the select statement.

query_cache_wlock_invalidate This parameter is used to set the relationship between row-level exclusive locks and query cache. The default is 0 (OFF), which means that while row-level exclusive locks are applied, all query caches of the table remain efficient. If set to 1 (ON), it means that all query caches of the table will be invalidated when the row-level exclusive lock is set.

View the hit rate of the query cache

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

View the current cache

statusInformation:

Qcache_free_blocks

indicates the number of memory blocks (number of fragments) in the query cache that are in a recurring state. If the value of Qcache_free_blocks is larger, it means that there are more fragments in the query cache, indicating that the query result set is smaller. In this case, the value of query_cache_min_res_unit can be reduced. Using

flush query cache will defragment several fragments in the cache to obtain a relatively large free block. Cache fragmentation rate = Qcache_free_blocks/ Qcache_total_blocks * 100%

Qcache_free_memory

indicates how much available memory is left in the query cache of the current MySQL service instance.

Qcache_hits

Indicates the number of times the query cache is used, and the value will increase sequentially. If Qcache_hits is relatively large, it means that the query cache is used very frequently, and the query cache needs to be increased.

Qcache_inserts

Indicates how many result sets of select statements have been cached in the query cache.

Qcache_lowmen_prunes

indicates the number of query results that MySQL

deleted because the query cache was full and overflowed. If this value is large, the query cache is too small.

Qcache_not_cached

Indicates the number of selects that have not entered the query cache

Qcache_queryies_in_cache

Indicates the query cache How many select statement result sets are cached in

Qcache_total_blocks

The total number of query caches

The cache hit rate calculation method: Query cache Hit rate = Qcache_hits / Com_select * 100%

Where Com_select is the number of select statements executed by the current MySQL instance. Generally Com_select = Qcache_insert + Qcache_not_cached. Qcache_not_cached contains select statements that cause the query cache to become invalid due to frequent data changes, so the hit rate is generally low. If you put aside the failure factor, the hit rate of the query cache = Qcache_hits / (Qcache_hits + Qcache_inserts) If you use this formula to calculate the hit rate of the query cache is relatively high, this means that most of the select statements hit the query cache.

Use the following command to check how many select statements have been executed by the current system

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select  | 1   |
+---------------+-------+

2. Result set cache

结果集缓存是会话缓存，MySQL客户机成功连接服务器之后。MySQL服务器会为每个MySQL客户机保留结果集缓存。缓存MySQL客户机连接线程的连接信息以及缓存返回MySQL客户机的结果集信息,当MySQL客户机向服务器发送select 语句时，MySQL将select语句的执行结果暂存在结果集缓存中。结果集的缓存大小由 net_buffer_length 参数值定义:

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

如果结果集超过net_buffer_length设置的值，则自动扩充容量，但不超过：max_allowd_packet的阈限值：

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name   | Value  |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

3、排序缓存

MySQL 常用的有InnoDB 和MyISAM 两种数据存储引擎。因此在优化的时候，每种引擎都会采用适合自己引擎的优化方法。关于MySQL 与InnoDB 表结构文件和数据日志文件的不同，可以先看本人的博客MySQL 日志系统,以便对这些基础概念有足够的了解，接下来看引擎的优化的方法才能如鱼得水，不觉得枯燥。

1、普通排序缓存

排序缓存是会话缓存, 如果客户机向服务端发送的SQL语句中含有设计排序的order by 或者group by 子句。MySQL就会选择相应的排序算法，在普通排序索引上进行排序，提升排序速度。普通排序索引的大小由sort_buffer_size 参数定义，如果要提升排序的速度，首先应该添加合适的索引，此后则应该增大排序索引缓存sort_buffer_size.

mysql> select @@global.sort_buffer_size / 1024;
+----------------------------------+
| @@global.sort_buffer_size / 1024 |
+----------------------------------+
|             256.0000 |
+----------------------------------+
1 row in set (0.00 sec)

接下来我们来看下与排序缓存相关的参数有哪些：

mysql> show variables like '%sort%';
+--------------------------------+---------------------+
| Variable_name         | Value        |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF         |
| innodb_ft_sort_pll_degree   | 2          |
| innodb_sort_buffer_size    | 1048576       |
| max_length_for_sort_data    | 1024        |
| max_sort_length        | 1024        |
| myisam_max_sort_file_size   | 9223372036853727232 |
| myisam_sort_buffer_size    | 8388608       |
| sort_buffer_size        | 262144       |
+--------------------------------+---------------------+

mysql> show status like '%sort%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Sort_merge_passes | 0   |
| Sort_range    | 0   |
| Sort_rows     | 0   |
| Sort_scan     | 0   |
+-------------------+-------+

max_length_for_sort_data

默认大小为1024字节，对每一列的进行排序操作是，如果该列的值长度较长，通过增加该参数来提升MySQL性能。

max_sort_length

order by 或者 group by 的时候使用该列的前 max_sort_length字节进行排序，排序操作完成后，会将此次排序的信息记录到本次会话的状态里。

Sort_merge_passes

使用临时文件完成排序操作的次数。MySQL在进行排序操作时，首先尝试在普通排序缓存中完成排序。如果缓存空间不够用，MySQL将利用缓存进行多次排序。并把每次的排序结果存放到临时文件中，最后再把临时文件中的数据做一次排序。Sort_merge_passes值就是记录了使用文件进行排序的次数。由于文件排序要牵涉到读文件，打开文件句柄，然后关闭文件等操作。所以读取文件的系统消耗比较大，通过增大普通排序缓存sort_buffer_size来减少使用临时文件排序的次数，从而增加排序的性能。

Sort_range

使用范围排序的次数

Sort_rows

已经排序的记录行数

Sort_scan

通过全表扫描完成排序的次数

2、MyISAM排序缓存

当我们使用alter table 语句或者create index 语句创建MyISAM表的索引，或者导入一部分数据使用load data infile path，这些操作都会导致索引被重建，重建索引时需要对索引字段进行排序操作，为了加快重建索引的效率，MyISAM提供了排序缓存用于实现索引的排序工作，这些方法都是尽量是排序的工作在内存中完成。MyISAM排序缓存的大小由myisam_sort_buffer_size定义。索引重建之后，该缓存立马释放。

但是当排序的缓存超过myisam_sort_buffer_size的阈限时，此时就需要在临时文件中完成索引字段的排序工作，外存临时文件的大小由myisam_max_sort_file_size参数设定，索引重建后，临时文件立即删除。

mysql> select @@global.myisam_sort_buffer_size/1024;
+---------------------------------------+
| @@global.myisam_sort_buffer_size/1024 |
+---------------------------------------+
|               8192.0000 |
+---------------------------------------+

mysql> select @@global.myisam_max_sort_file_size /1024;
+------------------------------------------+
| @@global.myisam_max_sort_file_size /1024 |
+------------------------------------------+
|          9007199254739967.7734 |
+------------------------------------------+

3、InnoDB 排序缓存

和MyISAM引擎类似，当执行alter table 、create index 创建索引是，InnoDB提供了3个InnoDB排序缓存用于实现索引的排序，每个缓存的大小由innodb_sort_buffer_size定义。

mysql> select @@global.innodb_sort_buffer_size/1024;
+---------------------------------------+
| @@global.innodb_sort_buffer_size/1024 |
+---------------------------------------+
|               1024.0000 |
+---------------------------------------+

4、join 连接缓存

join缓存是会话缓存，如果两张表相连，但是却无法使用索引（这时使用join连接缓存的前提），MySQL将为每张表分配join 连接缓存。

mysql> select @@global.join_buffer_size/1024;
+--------------------------------+
| @@global.join_buffer_size/1024 |
+--------------------------------+
|            256.0000 |
+--------------------------------+

join_buffer_size 定义了连接缓存的大小，如上图，默认为256;

5、表缓存Cache 与表结构定义缓存Cache

MySQL 服务访问数据库中的表时，实际上MySQL是做的文件的读取操作。MySQL的数据都是存在硬盘上的一个个文件，这个和一些内存的型的数据库不同。当我们查询一张表，使用select 语句时，不考虑使用查询缓存，首先要操作系统打开该文件，产生该文件的描述符。操作系统将文件描述符交给MySQL,MySQL才能对数据库进行CURD的操作。打开文件、产生文件描述符都需要消耗系统资源，造成访问延时。MySQL将已经打开的文件，包括文件描述符缓存起来，以后再次访问该文件时，就无需打开该文件，提高了读取文件的效率。

表结构并不经常变化，当对表进行访问的时候，除了将该表植入MySQL的表缓存外，MySQL还将表结构放入了表结构定义缓存中，供下次使用。

mysql> show variables like 'table%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| table_definition_cache   | 1400 |
| table_open_cache      | 2000 |
| table_open_cache_instances | 1   |
+----------------------------+-------+

mysql> show variables like '%open%';
+----------------------------+----------+
| Variable_name       | Value  |
+----------------------------+----------+
| have_openssl        | DISABLED |
| innodb_open_files     | 2000   |
| open_files_limit      | 65535  |
| table_open_cache      | 2000   |
| table_open_cache_instances | 1    |
+----------------------------+----------+

table_open_cache

设定了可以缓存表以及视图的数量限制

table_definition_cache

设定了可以存储多少张frm 表结构

对于MySQL MyISAM引擎来说，表结构包含MYI 和MYD 以及表结构frm, 当访问MyISAM 引擎的时候，需要一次性打开两个文件（MYI 、MYD）,产生两个文件描述符。

open_files_limit

打开文件的上限

innodb_open_files

如果InnoDB 表使用的是独立表空间文件（ibd），该参数设定同一时间能够打开的文件数量。

以下是和打开表相关的状态值：

mysql> show status like 'Open%';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| Open_files        | 18  |
| Open_streams       | 0   |
| Open_table_definitions  | 70  |
| Open_tables       | 63  |
| Opened_files       | 125  |
| Opened_table_definitions | 0   |
| Opened_tables      | 0   |
+--------------------------+-------+

6、表扫描缓存buffer

表扫描分为顺序扫描（Sequential Scan）以及随机扫描（Random Scan） 两种方式

顺序扫描 当MyISAM表没有建索引时，查询速度将进行全表扫描，效率很低。为了提升全表扫描的速度，MySQL提供了顺序扫描缓存（read buffer）。此时MySQL按照存储数据的存储顺序因此读出全部的数据块，每次读取的数据块缓存在顺序扫描缓存中，当read buffer写满之后，将数据返还给上层调用者。

随机扫描

当表里有缓存，扫描表的时候，会将表的索引字段放进内存里先拍好序，然后按照已经拍好的顺序去硬盘中查找数据。

7、MyISAM索引缓存buffer

通过缓存MYI索引文件的内容，可以加快读取索引的速度以及索引的速度。索引缓存只对MyISAM表起作用，且被所有线程共享。查询语句或者更新索引通过索引访问表数据的时候，MySQL首先检查索引缓存中是否已经存在需要的索引信息，如果有通过缓存中的索引可以直接访问到索引对应的MYD文件。如果没有，则会读取MYI文件，并将相应的索引数据读取到缓存中。索引缓存对MyISAM表的访问性能起到了至关重要的作用。

mysql> show variables like 'key%';
+--------------------------+---------+
| Variable_name      | Value  |
+--------------------------+---------+
| key_buffer_size     | 8388608 (8M)| 
| key_cache_age_threshold | 300   |
| key_cache_block_size   | 1024  |
| key_cache_pision_limit | 100   |
+--------------------------+---------+

key_buffer_size

设置索引缓存的大小，默认是8M。建议提升。

key_cache_block_size

指定每个索引缓存的区块大小，建议设置为4K,即4096

key_cache_pision_limit

为了有效的使用缓存。默认情况下MySQL降缓存划分为两个索引缓存区，温区（warm area） 以及热区（hot area）。key_cache_pision_limit参数以百分比的形式向曾哥索引缓存划分为多个区域。当默认值是100的时候，表示索引缓存只有温区，将启用LRU算法淘汰索引缓存中的索引。

key_cahe_age_threshold

控制温区域热区中的索引何时升级何时降级。如果该值小于100，则有热区。移动算法大致类似与LRU算法。

查看当前MySQL服务实例索引读以及索引写的状态值：

mysql> show status like 'Key%';
+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0   |
| Key_blocks_unused   | 6698 |
| Key_blocks_used    | 0   |
| Key_read_requests   | 0   |
| Key_reads       | 0   |
| Key_write_requests   | 0   |
| Key_writes       | 0   |
+------------------------+-------+

8、日志缓存

日志缓存分为二进制日志缓存以及InnoDB重做日志缓存

1、二进制日志缓存

mysql> show variables like '%binlog%cache%';
+----------------------------+----------------------+
| Variable_name       | Value        |
+----------------------------+----------------------+
| binlog_cache_size     | 32768        |
| binlog_stmt_cache_size   | 32768        |
| max_binlog_cache_size   | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+

mysql> show status like '%binlog%cache%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| Binlog_cache_disk_use   | 0   |
| Binlog_cache_use      | 0   |
| Binlog_stmt_cache_disk_use | 0   |
| Binlog_stmt_cache_use   | 0   |
+----------------------------+-------+

Mysql 进行创建或者更新的数据的时候，会记录一条二进制日志。然而频繁的进行I/O操作将对MySQL造成较大的性能影响。因此MySQL开辟了一个二进制日志缓存binlog_cache_size。首先将操作写入二进制日志，当操作成功之后，将二进制日志写入硬盘。

2、InnoDB重做日志缓存

事务在commit前，会将产生的重做日志写入InnoDB重做日志缓存，然后InnoDB【择机】执行轮询策略，将缓存中的重做日志文件写入ib_logfile0 以及ib_logfile1重做日志中。

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+---------+
| Variable_name     | Value  |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+

InnoDB重做日志缓存可以确保事务提交前，事务运行期间产生的重做日志保存在InnoDB的日志缓存中，但并不写入重做日志文件中。写入时机由innodb_flush_log_at_trx_commit参数控制。

mysql> show variables like 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout  | 1   |
| innodb_flush_log_at_trx_commit | 1   |
+--------------------------------+-------+

0:当缓存中重做日志文件以每秒一次的频率写入硬盘缓存，并且同时会更新到硬盘。

1：在每次事务提交的时候，将缓存中重做日志写到重做日志文件，同时写入硬盘，默认是该行为。

2:事务提交的时候，写到缓存，但并不触发文件系统到硬盘的同步操作，但此外每秒一次同步硬盘。

9、预读机制

预读机制主要利用了前文MySQL优化:一 、缓存优化所描述的原理。即局部性特征，空间局部性，和时间局部性，这里不再赘述。

1、InnoDB预读机制

InnoDB采用预读机制，将“未来即将访问的数据”包括索引加载到预读缓存中，进而提升数据的读性能。InnoDB支持顺序预读（linear read ahead）与随机预读（random read ahead）两种方式。

数据块（page）是InnoDB硬盘管理的最小单位，一个区由64个连续的数据块构成，对于顺序预读而言，InnoDB首选将该数据所在数据块置入InnoDB缓存池中，可以预测这些数据块的后续块很快就会被访问，于是这些数据块以及前置的数据块会被置入内存中。根据innodb_read_ahead_threshold参数设定预读前后多少个数据块。

mysql> show variables like 'innodb_read_ahead%';
+-----------------------------+-------+
| Variable_name        | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56  |
+-----------------------------+-------+

2、索引缓存预加载

数据库管理员可以使用MySQL命令 load index into cache 预加载MyISAM表索引

10、MyISAM表延迟插入

mysql> show variables like '%delayed%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| delayed_insert_limit    | 100  |
| delayed_insert_timeout   | 300  |
| delayed_queue_size     | 1000 |
| max_delayed_threads    | 20  |
| max_insert_delayed_threads | 20  |
+----------------------------+-------+

看到这个延迟插入的功能，想起项目里一个有点类似的功能，启发了自己的思路。

使用方法为：insert delyed into table values(*);

delyed_insert_limit

默认值为100.当向MySQL表延迟插入100行记录后，检查该表是否有select语句在等待执行，如果有，暂停insert语句执行。

delayed_insert_timeout

在超时范围内，如果delayed 队列里没有数据，延迟插入线程将关掉。

delayed_queue_size

延迟插入的队列长度,超出将阻塞，直到有足够的空间。

max_delayed_threads

延迟插入的线程数。

MyISAM表的批量延迟插入

类似 insert into table values(1),values(2),values(n)。MyISAM将进行批量插入。先将插入的数据放入缓存。当缓存被写满或者提交完毕了，MySQL一次性的将缓存中的写入硬盘。通过批量插入可以大大缩减MySQL客户机与服务机的连接语法分析等消耗，使得效率比分开执行单个insert语句快的多。

mysql> select @@global.bulk_insert_buffer_size/(1024*1024);
+----------------------------------------------+
| @@global.bulk_insert_buffer_size/(1024*1024) |
+----------------------------------------------+
|                    8.0000 |
+----------------------------------------------+

默认批量插入的大小为8M。如果业务上有需要，可以设定的大一些，以提高批量插入的性能。

MyISAM表的索引延迟更新

索引可以加快数据检索，但是对于更新来说，不仅需要修改记录，可能还需要修改索引，因此索引会导致数据更新操作变慢，如果将MySQL的delay_key_write参数设置为1（ON）,可以弥补这一缺陷。开启后更新操作修改数据的时候先将数据的更新提交到硬盘，索引的更新全部在索引缓存里完成。在关闭表的时候，一起更新到硬盘，这样就可以使索引更新的更快。仅对MyISAM有效。

mysql> show variables like 'delay_key_write';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| delay_key_write | ON  |
+-----------------+-------+

InnoDB延迟更新

非聚簇索引的更新操作通常会带来随机I/O，降低InoDB的性能。当更新（insert, delete ，update=insert+delete）非聚簇索引的数据时，会先检查非聚簇索引页是否位于InnoDB缓存池中，如果是直接更新，否则先将“信息修改”记录在更新缓存中（change buffer）

这篇博客的内容比较多，总结提炼下来以备以后查看。对整个MySQL的优化先有个整体的框架，徐徐渐进慢慢进步。这些参数可以不用记忆，用到的时候到博客中查找或者百度即可。了解道，知道术，就可以完成优化的过程。知道原理比记忆枯燥的原理要简单的多。对MySQL优化感兴趣的博友可以关注我的博客，以便看到后续的分享。

The above is the detailed content of Detailed explanation of cache optimization for MySQL optimization (2). For more information, please follow other related articles on the PHP Chinese website!