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!

MySQLhandlesconcurrencyusingamixofrow-levelandtable-levellocking,primarilythroughInnoDB'srow-levellocking.ComparedtootherRDBMS,MySQL'sapproachisefficientformanyusecasesbutmayfacechallengeswithdeadlocksandlacksadvancedfeatureslikePostgreSQL'sSerializa

MySQLhandlestransactionseffectivelyusingtheInnoDBengine,supportingACIDpropertiessimilartoPostgreSQLandOracle.1)MySQLusesREPEATABLEREADasthedefaultisolationlevel,whichcanbeadjustedtoREADCOMMITTEDforhigh-trafficscenarios.2)Itoptimizesperformancewithabu

MySQLisbetterforspeedandsimplicity,suitableforwebapplications;PostgreSQLexcelsincomplexdatascenarioswithrobustfeatures.MySQLisidealforquickprojectsandread-heavytasks,whilePostgreSQLispreferredforapplicationsrequiringstrictdataintegrityandadvancedSQLf

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.


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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 Chinese version
Chinese version, very easy to use

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
