Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the meaning of the query caching mechanism of MySQL", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what the query caching mechanism of MySQL means.

The query must be identical (byte by byte) to be considered the same. In addition, the same query string may be thought to be different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

The following sql query cache is considered different:

SELECT*FROMtbl_nameSelect*fromtbl_name

Query cache related parameters

Mysql > SHOWVARIABLESLIKE'%query_cache%' +-- +-- + | Variable_name | Value | +-- +-+ | have_query_cache | YES |-- query whether the cache is available | query_cache_limit | 1048576 |-- the maximum value that can be cached for specific query results | query _ cache_min_res_unit | 4096 | query_cache_size | 599040 |-query cache size | query_cache_type | ON |-- block or support query cache | query_cache_wlock_invalidate | OFF | +-- +-- +

What does MySQL query caching mechanism mean?

Here is an example of a simple MySQL query caching mechanism:

[mysql@csdba1850~] $mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\ g.YourMySQLconnectionidis3Serverversion:5.0.45-communityMySQLCommunityEdition (GPL) Type'help;'or'\ html forhelp.Type'\ c'toclearthebuffer.mysql > setglobalquery_cache_size=600000;-- set cache memory QueryOK,0rowsaffected (0.00sec) mysql > setsessionquery_cache_type=ON;-- enable query cache QueryOK,0rowsaffected (0.00sec) mysql > usetestReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql > showtables +-+ | Tables_in_test | +-+ | animals | | person | +-+ 5rowsinset (0.00sec) mysql > selectcount (*) fromanimals +-+ | count (*) | +-+ | 6 | +-+ 1rowinset (0.00sec)-- Qcache_hits represents the cumulative number of hits of the sql query in the cache, which is a cumulative value. Mysql > SHOWSTATUSLIKE'Qcache_hits';+-+-+ | Variable_name | Value | +-+-+ | Qcache_hits | 0 |-0 times +-+-+ 8rowsinset (0.00sec) mysql > selectcount (*) fromanimals +-+ | count (*) | +-+ | 6 | +-+ 1rowinset (0.00sec) mysql > SHOWSTATUSLIKE'Qcache%' +-+-+ | Variable_name | Value | +-+-+ | Qcache_hits | 1 |-- indicates that sql gets the result directly in the cache. There is no need to parse +-+-+ 8rowsinset (0.00sec) mysql > selectcount (*) fromanimals. +-+ | count (*) | +-+ | 6 | +-+ 1rowinset (0.00sec) mysql > selectcount (*) fromanimals;+-+ | count (*) | +-+ | 6 | +-+ 1rowinset (0.00sec) mysql > SHOWSTATUSLIKE'Qcache_hits' +-+-+ | Variable_name | Value | +-+-+ | Qcache_hits | 3 |-- the sql above also fetches the result directly from the cache +-+-+ 1rowinset (0.00sec) mysql > insertintoanimalsselect9,'testsds' -- after inserting data, all sql caches associated with this table will be cleared QueryOK,1rowaffected (0.00sec) Records:1Duplicates:0Warnings:0mysql > selectcount (*) fromanimals;+-+ | count (*) | +-+ | 7 | +-+ 1rowinset (0.00sec) mysql > SHOWSTATUSLIKE'Qcache_hits' +-+-+ | Variable_name | Value | +-+-+ | Qcache_hits | 3 |-- or equal to 3, indicating that the previous sql is not obtained directly from the cache +-+-+ 1rowinset (0.00sec) mysql > selectcount (*) fromanimals +-+ | count (*) | +-+ | 7 | +-+ 1rowinset (0.00sec) mysql > SHOWSTATUSLIKE'Qcache_hits';+-+-+ | Variable_name | Value | +-+-+ | Qcache_hits | 4 | +-- + 1rowinset (0.00sec)

At this point, I believe you have a deeper understanding of "what the query caching mechanism of MySQL means". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.