51黑料不打烊

Transcript

Today鈥檚 topic is the query cache. What鈥檚 the query cache? The query cache is some mapping between a query syntax and output. MySQL is going to add both of them into a table in the cache. The first time, MySQL is going to execute the query and store the output the subsequent times instead of executing the query. MySQL is just going to grab the resource set from the mapping table or from the cache, making the query very fast to return its output because it doesn鈥檛 execute the query, it just grabs the resource set from the mapping table or from the cache. Here鈥檚 a situation where we have 800 queries waiting for the query cache log to be acquired. This is a common issue, a common pain point. It鈥檚 very important to understand what happens in the background. Let鈥檚 basically dive into the internals to better understand what happened. Let鈥檚 set profiling to one so that we have more information, more details about the queries we鈥檙e going to execute. I have this test table and then let鈥檚 do it just a simple, let鈥檚 say it takes half a second.

You can see the queries in the profile output or profile stable. Now let鈥檚 execute the query in exactly the same syntax. Notice that this time around, around it takes a millisecond. Let鈥檚 see what鈥檚 in the profile.

We have the two queries. Let鈥檚 dig into each of them. If we do show profile or query one, you notice that my SQL is going to check. The first time it doesn鈥檛 know if the query cache is in the mapping table or the cache. It鈥檚 going to check and then it鈥檚 going to wait for the query cache. Remember those processes were waiting. Every time you do a query, it鈥檚 not just a select, it could be an update. Even though they only select output into the query cache, but every time you do a select, it鈥檚 going to basically check if the output exists already. If it doesn鈥檛, it鈥檚 going to execute the query. Otherwise, it鈥檚 just going to grab the output and return it to the client instead of executing the query.

Now, if we change the, let鈥檚 say we change the, we can just add a space.

It鈥檚 going to execute the query. That鈥檚 why the syntax is very sensitive and it鈥檚 very rudimentary. The query cache in my SQL because adding a space is treated as a new query. If you do show profiles, see it鈥檚 going to be a new query. If you do, it鈥檚 a third query.

It鈥檚 going to do the same thing, all same thing, checking and then waiting for query cache. Remember the second query took only a bit of second. Let鈥檚 check the internals. You can see that, for example, this is sending cached result to the client because it was cached so it did not execute it. Whereas the first time it stored it into the query cache. Now, let鈥檚 try to update the table. For the first query, we know the data is stored so it鈥檚 taking a millisecond. Let鈥檚 update the table. Now, let鈥檚 run the select again. MySQL is going to execute the query. What happened is that the update invalidated the output. Because every time you change the data into a table, MySQL is going to invalidate all of its outputs from the query cache. That means all of the queries in the query cache will have to be executed the next times because the output invalid. It鈥檚 not recommended to have a very big query cache.

Let鈥檚 show you here. We have 256 megabytes. That鈥檚 why you should keep it small because although if it鈥檚 big, we may end up storing more data into the query cache. But the flip side is when you update the table, it鈥檚 going to invalidate all of those outputs into the query cache. That鈥檚 basically the takeaway. The takeaway is to keep it as small as possible, typically 8 megabytes, 16 megabytes. Thank you for listening.