Mysql必读MySQL优化之缓存优化（续）

《Mysql必读MySQL优化之缓存优化（续）》要点：
本文介绍了Mysql必读MySQL优化之缓存优化（续），希望对您有用。如果有疑问，可以联系我们。

MySQL 内部处处皆缓存,等什么时候看了MySQL的源码,再来详细的分析缓存的是如何利用的.这部分主要将各种显式的缓存优化：MYSQL实例

1. 查询缓存优化
2. 结果集缓存
3. 排序缓存
4. join 连接缓存
5. 表缓存Cache 与表结构定义缓存Cache
6. 表扫描缓存buffer
7. MyISAM索引缓存buffer
8. 日志缓存
9. 预读机制
10. 延迟表与临时表
    

1、查询缓存优化MYSQL实例

查询缓存不仅将查询语句结构缓存起来,还将查询结果缓存起来.一段时间内,如果是同样的SQL,则直接从缓存中读取结果,提高查找数据的效率.但当缓存中的数据与硬盘中的数据不一致时,缓存就会失效.MYSQL实例

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 是否支持查询缓存.MYSQL实例

query_cache_limit 如果某条select语句的结果集大小超过了querycachelimit的值时,这个结果集将不会被添加到查询缓存.MYSQL实例

query_cache_min_res_unit 查询缓存是以块来申请内存空间的,每次申请的块大小为设定值.4K 是非常一个合理的值,不必修改.MYSQL实例

query_cache_size 查询缓存的大小.MYSQL实例

query_cache_type 查询缓存的类型,值有 0(OFF)、1(ON)、2(DEMOND).OFF表示查询缓存是关闭的.ON 表示查询总是先到查询缓存中去查找,除非在select 语句中包含sql_no_cache选项. DEMOND 表示不适用缓存,除非在select 语句中包含sql_cache选项.MYSQL实例

query_cache_wlock_invalidate 该参数用于设置行级排它锁与查询缓存之间的关系,默认为为0(OFF),表示施加行级排它所的同时,该表的所有查询缓存依然有效.如果设置为1(ON),表示事假行级排它锁的同时,该表的所有查询缓存失效.MYSQL实例

查看查询缓存的命中率MYSQL实例

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    |
+-------------------------+---------+

查看当前缓存的状态信息：MYSQL实例

Qcache_free_blocksMYSQL实例

表示查询缓存中处以重现状态的内存块数(碎片数量).如果Qcache_free_blocks 的值较大,则意味着查询缓存中碎片比较多,表明查询结果集较小,此时可以减小query_cache_min_res_unit的值.使用flush query cache 会对缓存中的若干个碎片进行整理,从而得到一个比较大的空闲块.缓存碎片率 = Qcache_free_blocks/ Qcache_total_blocks * 100%MYSQL实例

Qcache_free_memoryMYSQL实例

表示当前MySQL服务实例的查询缓存还有多少可用内存.MYSQL实例

Qcache_hitsMYSQL实例

表示使用查询缓存的次数,该值会依次增加.如果Qcache_hits比较大,则说明查询缓存使用的非常频繁,此时需要增加查询缓存.MYSQL实例

Qcache_insertsMYSQL实例

表示查询缓存中此前总共缓存过多少条select 语句的结果集.MYSQL实例

Qcache_lowmen_prunesMYSQL实例

表示因为查询缓存已满而溢出,导致MySQL删除的查询结果个数.如果该值比较大,则表明查询缓存过小.MYSQL实例

Qcache_not_cachedMYSQL实例

表示没有进入查询缓存的select个数MYSQL实例

Qcache_queryies_in_cacheMYSQL实例

表示查询缓存中缓存这多少条select语句的结果集MYSQL实例

Qcache_total_blocksMYSQL实例

查询缓存的总个数MYSQL实例

缓存命中率的计算方式： 查询缓存的命中率 = Qcache_hits / Com_select * 100%MYSQL实例

其中Com_select为当前MySQL实例执行select 语句的个数.一般情况下Com_select = Qcache_insert + Qcache_not_cached.而 Qcache_not_cached中包含有数据频繁变化而导致查询缓存失效的select语句,因此命中率一般来说较低.如果抛开失效的因素,查询缓存的命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) 如果使用这种公式计算出查询缓存的命中率比较高的话,这就意味着大部分select语句都命中了查询缓存.MYSQL实例

通过如下命令查看当前系统一共执行了多少条select语句MYSQL实例

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

2、结果集缓存MYSQL实例

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

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

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

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

3、排序缓存MYSQL实例

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

1、普通排序缓存MYSQL实例

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

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

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

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_dataMYSQL实例

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

max_sort_lengthMYSQL实例

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

Sort_merge_passesMYSQL实例

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

Sort_rangeMYSQL实例

使用范围排序的次数MYSQL实例

Sort_rowsMYSQL实例

已经排序的记录行数MYSQL实例

Sort_scanMYSQL实例

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

2、MyISAM排序缓存MYSQL实例

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

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

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 排序缓存MYSQL实例

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

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

4、join 连接缓存MYSQL实例

join缓存是会话缓存,如果两张表相连,但是却无法使用索引(这时使用join连接缓存的前提),MySQL将为每张表分配join 连接缓存.MYSQL实例

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

join_buffer_size 定义了连接缓存的大小,如上图,默认为256;MYSQL实例

5、表缓存Cache 与表结构定义缓存CacheMYSQL实例

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

表结构并不经常变化,当对表进行访问的时候,除了将该表植入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
MYSQL实例

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

table_definition_cacheMYSQL实例

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

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

open_files_limitMYSQL实例

打开文件的上限MYSQL实例

innodb_open_filesMYSQL实例

如果InnoDB 表使用的是独立表空间文件(ibd),该参数设定同一时间能够打开的文件数量.MYSQL实例

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

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、表扫描缓存bufferMYSQL实例

表扫描分为顺序扫描(Sequential Scan)以及随机扫描(Random Scan) 两种方式MYSQL实例

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

随机扫描MYSQL实例

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

7、MyISAM索引缓存bufferMYSQL实例

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

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_sizeMYSQL实例

设置索引缓存的大小,默认是8M.建议提升.MYSQL实例

key_cache_block_sizeMYSQL实例

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

key_cache_division_limitMYSQL实例

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

key_cahe_age_thresholdMYSQL实例

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

查看当前MySQL服务实例索引读以及索引写的状态值：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、日志缓存MYSQL实例

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

1、二进制日志缓存MYSQL实例

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.首先将操作写入二进制日志,当操作成功之后,将二进制日志写入硬盘.MYSQL实例

2、InnoDB重做日志缓存MYSQL实例

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

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实例

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

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

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

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

9、预读机制MYSQL实例

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

1、InnoDB预读机制MYSQL实例

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

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

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

2、索引缓存预加载MYSQL实例

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

10、MyISAM表延迟插入MYSQL实例

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  |
+----------------------------+-------+

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

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

delyed_insert_limitMYSQL实例

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

delayed_insert_timeoutMYSQL实例

在超时范围内,如果delayed 队列里没有数据,延迟插入线程将关掉.MYSQL实例

delayed_queue_sizeMYSQL实例

延迟插入的队列长度,超出将阻塞,直到有足够的空间.MYSQL实例

max_delayed_threadsMYSQL实例

延迟插入的线程数.MYSQL实例

MyISAM表的批量延迟插入MYSQL实例

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

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

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

MyISAM表的索引延迟更新MYSQL实例

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

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

InnoDB延迟更新MYSQL实例

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!