MySQL 内部处处皆缓存，等什么时候看了MySQL的源码，再来详细的分析缓存的是如何利用的。这部分主要将各种显式的缓存优化：
- 查询缓存优化
- 结果集缓存
- 排序缓存
- join 连接缓存
- 表缓存Cache 与表结构定义缓存Cache
- 表扫描缓存buffer
- MyISAM索引缓存buffer
- 日志缓存
- 预读机制
- 延迟表与临时表
1、查询缓存优化
查询缓存不仅将查询语句结构缓存起来，还将查询结果缓存起来。一段时间内，如果是同样的SQL,则直接从缓存中读取结果，提高查找数据的效率。但当缓存中的数据与硬盘中的数据不一致时，缓存就会失效。
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 是否支持查询缓存。
query_cache_limit 如果某条select语句的结果集大小超过了querycachelimit的值时，这个结果集将不会被添加到查询缓存。
query_cache_min_res_unit 查询缓存是以块来申请内存空间的，每次申请的块大小为设定值。4K 是非常一个合理的值，不必修改。
query_cache_size 查询缓存的大小。
query_cache_type 查询缓存的类型，值有 0（OFF）、1（ON）、2（DEMOND）。OFF表示查询缓存是关闭的。ON 表示查询总是先到查询缓存中去查找，除非在select 语句中包含sql_no_cache选项。 DEMOND 表示不适用缓存，除非在select 语句中包含sql_cache选项。
query_cache_wlock_invalidate 该参数用于设置行级排它锁与查询缓存之间的关系，默认为为0（OFF）,表示施加行级排它所的同时，该表的所有查询缓存依然有效。如果设置为1（ON）,表示事假行级排它锁的同时，该表的所有查询缓存失效。
查看查询缓存的命中率
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 | +-------------------------+---------+
查看当前缓存的状态信息：
Qcache_free_blocks
表示查询缓存中处以重现状态的内存块数（碎片数量）。如果Qcache_free_blocks 的值较大，则意味着查询缓存中碎片比较多，表明查询结果集较小，此时可以减小query_cache_min_res_unit的值。使用flush query cache 会对缓存中的若干个碎片进行整理，从而得到一个比较大的空闲块。缓存碎片率 = Qcache_free_blocks/ Qcache_total_blocks * 100%
Qcache_free_memory
表示当前MySQL服务实例的查询缓存还有多少可用内存。
Qcache_hits
表示使用查询缓存的次数，该值会依次增加。如果Qcache_hits比较大，则说明查询缓存使用的非常频繁，此时需要增加查询缓存。
Qcache_inserts
表示查询缓存中此前总共缓存过多少条select 语句的结果集。
Qcache_lowmen_prunes
表示因为查询缓存已满而溢出，导致MySQL删除的查询结果个数。如果该值比较大，则表明查询缓存过小。
Qcache_not_cached
表示没有进入查询缓存的select个数
Qcache_queryies_in_cache
表示查询缓存中缓存这多少条select语句的结果集
Qcache_total_blocks
查询缓存的总个数
缓存命中率的计算方式： 查询缓存的命中率 = Qcache_hits / Com_select * 100%
其中Com_select为当前MySQL实例执行select 语句的个数。一般情况下Com_select = Qcache_insert + Qcache_not_cached。而 Qcache_not_cached中包含有数据频繁变化而导致查询缓存失效的select语句，因此命中率一般来说较低。如果抛开失效的因素，查询缓存的命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) 如果使用这种公式计算出查询缓存的命中率比较高的话，这就意味着大部分select语句都命中了查询缓存。
通过如下命令查看当前系统一共执行了多少条select语句
mysql> show status like 'Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+
2、结果集缓存
结果集缓存是会话缓存，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_division_limit | 100 | +--------------------------+---------+
key_buffer_size
设置索引缓存的大小，默认是8M。建议提升。
key_cache_block_size
指定每个索引缓存的区块大小，建议设置为4K,即4096
key_cache_division_limit
为了有效的使用缓存。默认情况下MySQL降缓存划分为两个索引缓存区，温区（warm area） 以及热区（hot area）。key_cache_division_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优化感兴趣的博友可以关注我的博客，以便看到后续的分享。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。
一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。
另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。
稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！
昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？