MySQL 최적화를 위한 캐시 최적화에 대한 자세한 설명(2)
- 黄舟원래의
- 2017-03-16 14:22:371545검색
이 글은 MySQL 최적화 시리즈 캐시 최적화의 첫 번째 글에 이어, 캐시 최적화의 모든 측면을 더 자세히 소개합니다.
MySQL 내부에는 곳곳에 캐시가 존재합니다. MySQL의 소스코드를 읽으면서 캐시가 어떻게 사용되는지 자세히 분석해보겠습니다. 이 부분에는 주로 다양한 명시적 캐시 최적화가 포함됩니다.
쿼리캐시 최적화
결과 세트 캐시
캐시 정렬
연결 캐시 연결
테이블 캐시 캐시 및 테이블 구조 정의 캐시
테이블 스캔 캐시 버퍼
MyISAM인덱스캐시 버퍼
-
로그 캐시
미리 읽기 메커니즘
지연 테이블 및 임시 테이블
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 쿼리 캐시를 사용하면 캐시의 여러 조각을 조각 모음하여 상대적으로 큰 여유 블록을 얻을 수 있습니다. 캐시 조각화율 = 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
쿼리 캐시에 입력되지 않은 선택 항목 수를 나타냅니다.
Qcache_queryies_in_cache
쿼리 캐시를 나타냅니다.
Qcache_total_blocks
총 쿼리 캐시 수
에 캐시된 select 문 결과 세트 수를 계산하는 방법 rate: 쿼리 캐시 적중률 = Qcache_hits / Com_select * 100%
여기서 Com_select는 현재 MySQL 인스턴스에서 실행되는 select 문 수입니다. 일반적으로 Com_select = Qcache_insert + Qcache_not_cached. Qcache_not_cached에는 데이터를 자주 변경하고 쿼리 캐시 실패를 유발하는 선택 문이 포함되어 있어 일반적으로 적중률이 낮습니다. 실패 요인을 제쳐두면 쿼리 캐시의 적중률 = 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_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优化感兴趣的博友可以关注我的博客，以便看到后续的分享。
위 내용은 MySQL 최적화를 위한 캐시 최적화에 대한 자세한 설명(2)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!