MySQL query cache has been provided since version 4.1, but it is worth studying it today. Under the default configuration, this function of MySQL is not enabled. You may find that the value of have_query_cache is yes through show variables like '%query_cache%'; for MYSQL beginners It is easy to think that if this parameter is YES, it means that QueryCache is turned on. This is actually wrong. This parameter indicates whether the current version of MYSQL supports Query Cache. In fact, whether to enable the query cache depends on the value of another parameter: query_cache_size. The value is 0, which means the query cache is disabled, and the default configuration is 0.
Find the following content in the
configuration file
my.ini or my.cnf of MYSQL: # Query cache is used to cache SELECT results and later return them
# without actually executing the same query once again. Having the query
# cache enabled may result in significant speed improvements , if your
# have a lot of identical queries and rarely changing tables. See the
# “Qcache_lowmem_prunes” status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0
The above information is the default configuration, and its
comment
means that the query cache of MYSQL is used to cache select query results, and When the same query request is received next time, the actual query processing will no longer be performed and the results will be returned directly. Such a query cache can increase the speed of the query and optimize the query performance. The prerequisite is that you have a large number of the same or similar queries. , and rarely change the data in the table, otherwise there is no need to use this function. You can check whether the current value meets your current system load through the value of the Qcache_lowmem_prunes variable. Note: If the table you query is updated frequently and rarely has the same query, it is best not to use the query cache. Specific configuration method:
1. Set query_cache_size to a specific size. The specific size depends on the actual situation of the query, but it is best to set it to a multiple of 1024, with a reference value of 32M.
2. Add a line: query_cache_type=1
The query_cache_type parameter is used to control the type of cache. Note that this value cannot be set casually and must be set to a number. The optional items and descriptions are as follows: If it is set to 0, then it can be said that your cache is useless at all, which is equivalent to being disabled. But in this case, does the system need to allocate the size set by query_cache_size? This question needs to be tested?
If set to 1, all results will be cached unless your select statement uses SQL_NO_CACHE to disable query caching.
If set to 2, only the queries that need to be cached through SQL_CACHE in the select statement will be cached.
OK, some of the files after configuration are as follows:
query_cache_size=128M
query_cache_type=1
Save the file, restart the MYSQL service, and then query as follows Verify whether it is really turned on:
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 | 134217728 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +——————————+———–+ 6 rows in set (0.00 sec)
Mainly depends on whether the values of query_cache_size and query_cache_type are consistent with what we set:
The value of query_cache_size here is 134217728, we The setting is 128M, which is actually the same, but the units are different. You can convert it yourself: 134217728 = 128*1024*1024.
query_cache_type is set to 1 and displayed as ON. This has been mentioned before.
In short, seeing the above display indicates that the settings are correct, but whether the query can be cached in the actual query still needs to be tested manually. We can test it through the show status like '%Qcache%'; statement. Now we have enabled the query cache function. Before executing the query, let’s first look at the values of the relevant parameters:
mysql> show status like ‘%Qcache%'; +————————-+———–+ | Variable_name | Value | +————————-+———–+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134208800 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +————————-+———–+ 8 rows in set (0.00 sec)
这里顺便解释下这个几个参数的作用:
Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks,如果该值显示较大,则说明查询缓存中的内存碎片过多了,可能在一定的时间进行整理。
Qcache_free_memory:查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用,是多了,还是不够用,DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大,缓存效果越理想。
Qcache_inserts: 表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中。这样的情况的次数,次数越多,表示查询缓存应用到的比较少,效果也就不理想。当然系统刚启动后,查询缓存是空的,这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值,用户可以适当的调整缓存大小。
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量。
下边我们测试下:
比如执行如下查询语句
mysql> select * from user where id = 2; +—-+——-+ | id | name | +—-+——-+ | 2 | test2 | +—-+——-+ 1 row in set (0.02 sec)
然后执行show status like ‘%Qcache%',看看有什么变化:
mysql> show status like ‘%Qcache%'; +————————-+———–+ | Variable_name | Value | +————————-+———–+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134207264 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +————————-+———–+ 8 rows in set (0.00 sec)
对比前面的参数值,我们发现Qcache_inserts变化了。Qcache_hits没有变,下边我们在执行同样的查询
select * from user where id = 2,按照前面的理论分析:Qcache_hits应该等于1,而Qcache_inserts应该值不变(其他参数的值变化暂时不关注,读者可以自行测试),再次执行:
show status like ‘%Qcache%',看看有什么变化:
mysql> show status like ‘%Qcache%'; +————————-+———–+ | Variable_name | Value | +————————-+———–+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134207264 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +————————-+———–+ 8 rows in set (0.00 sec)
OK,果然跟我们分析的完全一致。
The above is the detailed content of Analyze MySQL cache startup method and parameters (query_cache_size). For more information, please follow other related articles on the PHP Chinese website!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

在mysql中,是否需要commit取决于存储引擎:1、若是不支持事务的存储引擎,如myisam,则不需要使用commit;2、若是支持事务的存储引擎,如innodb,则需要知道事务是否自动提交,因此需要使用commit。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Dreamweaver Mac version
Visual web development tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Chinese version
Chinese version, very easy to use

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
