`

Mysql的查询缓存区的优化

阅读更多
什么是MySQL查询高速缓冲?
         查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询,服务器从查询缓存中重新得到查询结果,而不再需要解析和执行查询。
缓存的效果如何?
          如果执行的所有查询是简单的(如从只有一行数据的表中选取一行),但查询是不同的,查询不能被缓存,查询缓存激活率是13%。这可以看作是最坏的情形。在实际应用中,查询要复杂得多,因此,查询缓存使用率一般会很低。
          从只有一行的表中查找一行数据时,使用查询缓存比不使用速度快238%。这可以看作查询使用缓存时速度提高最小的情况
注意:
        查询缓存不返回旧的数据。当表更改后,查询缓存值的相关条目被清空。
        如果你有许多mysqld服务器更新相同的MyISAM表,在这种情况下查询缓存不起作用。
        服务器启动时要禁用查询缓存,设置query_cache_size系统变量为0。禁用方式是通过在configure中使用--without-query-cache选项,可以从服务器中彻底去除查询缓存能力。
查询高速缓冲工作原理是怎样的?
         当查询解析之前先进行比较操作,SELECT * FROM table_name 和 Select * From table_name被认为是不同的两个操作,也就意味着查询操作必须是逐字节相同的操作语句串才能够使用高速缓冲;同样的查询字符串有可能认为是不同的,如:使用不同的数据库、不同的协议版本或者不同的默认字符集的查询,所以高速缓冲将建立不同的查询缓冲。
         当一个表被更改,那么使用那个表的所有缓冲查询将不再有效,并从缓冲区中移出。可能被哪些语句更改呢?它包括:INSERT UPDATE DELETE TRUNCATE ALTER TABLE DROP TABLE 和 DROP DATABASE。
         SELECT SQL_CALC_FOUND_ROWS * ......和SELECT FOUND_ROWS() type类型的查询使用查询缓存。即使因创建的行数也被保存在缓冲区内,前面的查询从缓存中提取,FOUND_ROWS()也返回正确的值。
         哪些函数将不被缓存?

               引用自定义函数、变量、Mysql系统数据库中的表。
           SELECT ... IN SHARE MODE
           SELECT ... FOR UPDATE
           SELECT ... INTO OUTFILE...
           SELECT ... INTO DUMPFILE ...
           SELECT * FROM ... WHERE autoincrement_col IS NULL
          使用TEMPORARY 表
          不使用任何表
          用户有某个表的列集权限。
如何设置查询高速缓冲SELECT 选项?
        SQL_CACHE:如果query_cache_type系统变量的值是ON或DEMAND,查询结果被缓存。
        SQL_NO_CACHE:查询结果不被缓存。
如何设置查询高速缓冲配置?
        通过have_query_cache服务器系统变量指示查询缓存是否可用。
        SHOW VARIABLES LIKE 'have_query_cache';
       所有查询缓存系统变量名以query_cache_ 开头。为了设置查询缓存大小,设置query_cache_size系统变量。设置为0表示禁用查询缓存。 默认缓存大小设置为0;也就是禁用查询缓存。当设置query_cache_size变量为非零值时,应记住查询缓存至少大约需要40KB来分配其数据结构。(具体大小取决于系统结构)。如果你把该值设置的太小,将会得到一个警告。
        SET GLOBAL query_cache_size = 40000;
        SHOW WARNINGS\G
         如果查询缓存大小设置为大于0,query_cache_type变量影响其工作方式。这个变量可以设置为下面的值:
           0或OFF将阻止缓存或查询缓存结果。
           1或ON将允许缓存,以SELECT SQL_NO_CACHE开始的查询语句除外。
           2或DEMAND,仅对以SELECT SQL_CACHE开始的那些查询语句启用缓存。
         当一个查询结果(返回给客户端的数据)从查询缓冲中提取期间,它在查询缓存中排序。因此,数据通常不在大的数据块中处理。查询缓存根据数据排序要求分配数据块,因此,当一个数据块用完后分配一个新的数据块。因为内存分配操作是昂贵的(费时的),所以通过query_cache_min_res_unit系统变量给查询缓存分配最小值。当查询执行时,最新的结果数据块根据实际数据大小来确定,因此可以释放不使用的内存。根据你的服务器执行查询的类型,你会发现调整query_cache_min_res_unit变量的值是有用的:
         设置query_cache_type变量的GLOBAL值将决定更改后所有连接客户端的缓存行为。具体客户端可以通过设置query_cache_type变量的会话值控制它们本身连接的缓存行为。例如,一个客户可以禁用自己的查询缓存,方法如下:
mysql> SET SESSION query_cache_type = OFF;
         要控制可以被缓存的具体查询结果的最大值,应设置query_cache_limit变量。 默认值是1MB。
          
当一个查询结果(返回给客户端的数据)从查询缓冲中提取期间,它在查询缓存中排序。因此,数据通常不在大的数据块中处理。查询缓存根据数据排序要求分配数据块,因此,当一个数据块用完后分配一个新的数据块。因为内存分配操作是昂贵的(费时的),所以通过query_cache_min_res_unit系统变量给查询缓存分配最小值。当查询执行时,最新的结果数据块根据实际数据大小来确定,因此可以释放不使用的内存。根据你的服务器执行查询的类型,你会发现调整query_cache_min_res_unit变量的值是有用的:
         query_cache_min_res_unit默认值是4KB。这应该适合大部分情况。
         如果你有大量返回小结果数据的查询,默认数据块大小可能会导致内存碎片,显示为大量空闲内存块。由于缺少内存,内存碎片会强制查询缓存从缓存内存中修整(删除)查询。这时,你应该减少query_cache_min_res_unit变量的值。空闲块和由于修整而移出的查询的数量通过Qcache_free_blocks和Qcache_lowmem_prunes变量的值给出。
·          如果大量查询返回大结果(检查 Qcache_total_blocks和Qcache_queries_in_cache状态变量),你可以通过增加query_cache_min_res_unit变量的值来提高性能。但是,注意不要使它变得太大(参见前面的条目)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/linuxin/archive/2008/07/17/2665015.aspx

  查询缓存区(query cache)是Mysql 4.0版本后引入的一项功能,本意是提高SQL查询性能,即把某些SQL查询命令的结果存放在内存里,当其他用户再次执行一条完全相同的查询命令时,Mysql将会把缓存在内存里的查询结果直接发送给他,而不用再对数据库表进行查询。
   但是并不是说查询缓存区就只有优点,而没有缺点,我举个例子,一个项目里如果是update, delete ,insert等数据库操作比select操作更多,那我想觉得这时候就不适合开启查询缓存区的功能,为什么呢,原因很简单,查询缓存区缓存的是查询的结果,如果数据库表内容经常的改变,那很多查询结果多必须从查询缓存区里删除掉,特别是内存较小的系统,这样的话,只会加剧数据库的负担。
  基于这一点,用不用开启查询缓存区的功能,查阅不少文档后,总结几条如下:

1. 就是查询缓存区适合select 操作较多,而insert update delete操作较少的情况,并且重复的SQL查询越多,效果越好。

2. 就是SQL查询语句命令必须严格的相同,包括字母大小写,空格,举个例子
有这么两行查询语句,


select username,email from  members order by username limit 10;



Select username,email from  members order by username limit 10;


咋看怎么长的都一样,而且查询的结果也是一样的,是的,没错,除了“select”和“Select”一个字母大小写的不同外,其他的都一样,但是在Mysql内部,SQL分析器却会把这两条长的一样的语句当作两条不同的语句,mysql的确很傻很天真,嘿嘿,
这里还要注意的是mysql对数据库和数据表是区分大小写的,而对数据列是不区分大小写的。

3.就是select 命令里不能含有一些特殊的函数,比如CUDATE(),NOW(),有这些函数在里面,查询缓存区几乎不起作用,只会给系统带来额外开销。


查询缓存区的启用
   如果 query_cache_size=0,那么当前没有开启查询缓存区
在my.cnf的[mysqld]配置段
开启查询缓存区有几个相关项,如
query_cache_size =128M
query_cache_type = 1  (有三个值,分别是0,1,2)
qurey_cache_limit=128K;
意思是给启用查询缓存区并给查询缓存区分配128M的内存空间, 而且允许查询的结果数据必须不大于128KB大小
query_cache_type为1,表示在SQL语句里加上了SQL_NO_CACHE关键字的,强制不缓存查询结果。
query_cache_type为0,表示关闭SQL语句里的SQL_CACHE和SQL_NO_CACHE功能,也就是禁用查询缓存功能。
query_cache_type为2,表示在SQL语句里,只有包含着SQL_CACHE的查询才会进入查询缓存区。

下篇我将介绍下查询缓存区的优化方法。
查看查询缓存区的相关变量:
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              | 33554432 |
| query_cache_type              | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+----------+
6 rows in set (0.00 sec)
have_query_cache  
是否支持查询缓存区 “YES”表是支持查询缓存区
query_cache_limit  可缓存的Select查询结果的最大值 1048576 byte /1024 = 1024kB 即最大可缓存的select查询结果必须小于1024KB
query_cache_min_res_unit 每次给query cache结果分配内存的大小 默认是 4096 byte 也即 4kB
在这里,我延伸三点重要的东西
1.当查询进行的时候,Mysql把查询结果保存在qurey cache中,但是有时候要保存的结果比较大,超过了query_cache_min_res_unit的值 ,这时候mysql将一边检索结果,一边进行慢慢保存结果,所以,有时候并不是
把所有结果全部得到后再进行一次性保存,而是每次分配一块query_cache_min_res_unit 大小的内存空间保存结果集,
使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,
mysql要进行多次内存分配的操作,而我们应该知道,频繁操作内存都是要耗费时间的。
2. 内存碎片的产生。当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重复利用。比如,第一次分配4KB,只用 了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的1KB+2KB=3KB,不足以做个一个内存单元分配, 这时候,内存碎片便产生了。
3.内存块的概念,先看下这个:
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value     |
+-------------------------+----------+|
|Qcache_free_blocks        | 5096     |
| Qcache_free_memory       | 18964096 |
| Qcache_hits              | 12192192 |
| Qcache_inserts           | 3560370  |
| Qcache_lowmem_prunes     | 17326    |
| Qcache_not_cached        | 303599   |
| Qcache_queries_in_cache  | 10201    |
| Qcache_total_blocks      | 25937    |
+-------------------------+----------+
8 rows in set (0.00 sec)
Qcache_total_blocks  表示所有的块
Qcache_free_blocks  表示未使用的块 这个值比较大,那意味着,内存碎片比较多,用flush query cache清理后,
为被使用的块其值应该为1或0 ,因为这时候所有的内存都做为一个连续的快在一起了:
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name            | Value     |
+-------------------------+----------+
| Qcache_free_blocks       | 1         |
| Qcache_free_memory       | 18539240 |
| Qcache_hits              | 12192502 |
| Qcache_inserts           | 3560515   |
| Qcache_lowmem_prunes     | 17326     |
| Qcache_not_cached        | 303607    |
| Qcache_queries_in_cache | 10318     |
| Qcache_total_blocks      | 21081     |
+-------------------------+----------+
8 rows in set (0.00 sec)
其他几个状态变量的意义:
Qcache_free_memory    表示查询缓存区现在还有多少的可用内存
Qcache_hits        表示查询缓存区的命中个数,也就是直接从查询缓存区作出响应处理的查询个数
Qcache_inserts    表示查询缓存区此前总过缓存过多少条查询命令的结果
Qcache_lowmem_prunes   表示查询缓存区已满而从其中溢出和删除的查询结果的个数
Qcache_not_cached    表示没有进入查询缓存区的查询命令个数
Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果
优化提示:
如果Qcache_lowmem_prunes 值比较大,表示查询缓存区大小设置太小,需要增大。
如果Qcache_free_blocks 较多,表示内存碎片较多,需要清理,flush query cache
根据我看的 《High Performance MySQL》中所述,关于query_cache_min_res_unit大小的调优
,书中给出了一个计算公式,可以供调优设置参考:
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
还要注意一点的是,FLUSH QUERY CACHE 命令可以用来整理查询缓存区的碎片,改善内存使用状况,
但不会清理查询缓存区的内容,这个要和RESET QUERY CACHE相区别,不要混淆,后者才是清除查询缓存区中
的所有的内容。
分享到:
评论

相关推荐

    MySQL优化之缓存优化(续)

    MySQL缓存优化是数据库性能提升的关键一环,尤其在高并发和大数据量的场景下,合理利用缓存能显著减少磁盘I/O操作,提高查询速度。本文将深入探讨MySQL中的各种缓存机制及其优化策略。 首先,我们关注的是查询缓存...

    mysql性能优化-慢查询分析、优化索引和配置.doc

    ### MySQL性能优化详解 #### 一、优化概述 MySQL作为一种广泛使用的开源关系型数据库管理系统,在很多应用场景中都可能遇到性能瓶颈的问题。常见的性能瓶颈主要分为两大类:**CPU瓶颈**和**I/O瓶颈**。 - **CPU...

    mysql5.6性能优化

    - **内存配置**:合理设置缓存和缓冲区大小,比如`innodb_buffer_pool_size`等参数。 - **磁盘I/O优化**:通过RAID配置、SSD使用等方式提高磁盘读写速度。 - **网络配置**:优化网络带宽、延迟等问题,确保数据传输...

    MySQL性能优化 SQL优化方法技巧

    ### MySQL性能优化与SQL优化方法技巧 #### 一、引言 MySQL作为一款广泛使用的开源关系型数据库管理系统,其性能优化对于确保应用系统的稳定性和高效运行至关重要。通过对MySQL的优化,不仅能提升系统的响应速度,还...

    mysql优化.pdf

    除了服务器硬件的性能瓶颈,我们还可以通过使用索引、EXPLAIN分析查询和调整MySQL配置来优化数据库性能。 在查询与索引优化分析部分,需要使用特定的工具来分析数据库性能。常见的分析工具有慢查询日志、EXPLAIN...

    2G内存的MYSQL数据库服务器优化

    ### 2G内存的MySQL数据库服务器优化 在IT行业中,对于资源有限的环境进行数据库优化是一项挑战性工作,尤其是在仅有2GB内存的情况下对MySQL数据库服务器进行优化。这种优化旨在提高性能的同时确保系统的稳定运行。 ...

    mysql最经典优化配置

    MySQL的优化主要包括两大部分:服务器硬件优化和MySQL自身配置(my.cnf)的优化。 首先,我们来看看服务器硬件对MySQL性能的影响: 1. **磁盘I/O**:磁盘的寻道能力是决定MySQL性能的重要因素。高速SCSI硬盘(如7200...

    mysql配置和优化

    - **内存分配**:合理配置MySQL的缓存和缓冲区大小,如`key_buffer_size`、`innodb_buffer_pool_size`等,可以显著提高数据库性能。 - **查询优化**:通过对SQL查询进行优化,减少不必要的数据加载和计算,提高查询...

    windows平台mysql优化配置

    ### Windows平台MySQL优化配置 在Windows平台上对MySQL进行优化配置是一项重要的任务,它不仅能够提升数据库系统的性能,还能确保数据处理的效率与安全性。本文将详细介绍如何针对Windows平台下的MySQL服务器进行...

    基于MySql的查询优化技术研究 (1).pdf

    因此,根据数据库的规模和访问模式,适当增大数据缓存区的大小可以提高查询效率。而日志缓存区则涉及事务处理,优化其大小可以减少磁盘写入次数,降低延迟。 其次,MySQL的查询重用技术是提高性能的有效手段。查询...

    MySQL数据库服务器优化详细

    通过精细地配置系统参数、合理利用内存资源、优化查询和临时表处理机制,可以显著提升数据库的响应速度和稳定性。同时,定期监控和分析服务器状态,及时调整策略,是保持MySQL高效运行的关键。希望本文能为正在寻求...

    【荐】mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    ### MySQL服务性能优化——my.cnf/my.ini配置详解 #### 背景介绍 本文档旨在详细介绍一套已在生产环境中经过验证、适用于16GB内存环境下的MySQL服务性能优化方案。通过对my.cnf/my.ini配置文件的各项关键参数进行...

    MySQL调优讨论:原理 优化 技巧

    优化查询意味着减少数据读取量,避免全表扫描,合理利用索引,以及减少JOIN操作的复杂性。 2. **索引优化**:索引是提高查询速度的关键,正确创建和使用索引能显著提升查询性能。应根据查询模式选择合适的索引类型...

    mysql服务性能优化my_cnf配置说明详解16G内存[参考].pdf

    如果还没有被缓存,但是在 MySQL 表缓冲区中还有空间,那么这个表就被打开并放入表缓存中。如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放。通过查看数据库运行峰值时间的状态值 ...

    Mysql性能优化教程.doc

    - 正确理解影响结果集对于优化查询至关重要。 - **获取影响结果集**: - 通过EXPLAIN语句分析查询计划。 - 利用EXPLAIN的结果判断哪些部分可能导致较高的查询开销。 - **解读影响结果集**: - 分析查询计划中的...

    redis集群, tomcat优化以及 MySQL5.6优化

    以上是关于 Redis 集群、Tomcat 优化及 MySQL 5.6 优化的核心知识点和优化策略,这些内容对于提升系统的稳定性和性能具有重要作用。在实际应用中,需要根据具体环境和业务需求进行针对性的调整。

    mysql 高性能优化

    书中会讨论如何调整缓冲池大小、查询缓存、排序区等内存参数,以最大化内存资源的利用率,同时避免内存溢出问题。 五、分区与分片 对于大数据量的表,分区和分片技术可以提高查询效率。分区通过将大表逻辑上分成多...

    高效MySQL查询加速指南:索引策略、查询优化、性能调优,助力数据库管理员和开发者突破性能瓶颈

    #### 一、优化查询语句 1. **选择合适的索引**: - **理解索引的作用**:索引是数据库表中一列或多列的排序机制,能够加快数据检索速度。为经常出现在WHERE子句中的列创建索引,可以显著提高查询速度。 - **避免...

    mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    优化MySQL服务性能不仅涉及上述参数,还需要关注其他如`key_buffer_size`、`query_cache_size`、`innodb_buffer_pool_size`等参数,以及合适的索引策略、查询优化等。在实际操作中,应根据具体业务场景和硬件条件...

Global site tag (gtag.js) - Google Analytics