Home Database Mysql Tutorial mysql query cache用法与性能详细介绍

mysql query cache用法与性能详细介绍

Jun 07, 2016 pm 05:52 PM
cache performance

前用naviacte for mysql 用sql语句查询时发现 第一次查询比较慢,在用同样的sql查询时会比较快,之后查资料,才发现mysql也有缓存机制;意思是将select查询的结果集和sql语句映射到内存缓存起来。当有存在缓存数据的时候,服务器马上返回服务器的结果,跳过解

mysql Query Cache 默认为打开。从某种程度可以提高查询的效果,但是未必是最优的解决方案,如果有的大量的修改和查询时,由于修改造成的cache失效,会给服务器造成很大的开销,可以通过query_cache_type【0(OFF)1(ON)2(DEMAND)】来控制缓存的开关.

需要注意的是mysql query cache 是对大小写敏感的,因为Query Cache 在内存中是以 HASH 结构来进行映射,HASH 算法基础就是组成 SQL 语句的字符,所以 任何sql语句的改变重新cache,这也是项目开发中要建立sql语句书写规范的原因吧

1. 何时cache
a) mysql query cache内容为 select 的结果集, cache 使用完整的 sql 字符串做 key, 并区分大小写,空格等。即两个sql必须完全一致才会导致cache命中。
b) prepared statement永远不会cache到结果,即使参数完全一样。据说在 5.1 之后会得到改善。
c) where条件中如包含了某些函数永远不会被cache, 比如current_date, now等。
d) date 之类的函数如果返回是以小时或天级别的,最好先算出来再传进去。
select * from foo where date1=current_date -- 不会被 cache
select * from foo where date1='2008-12-30' -- 被cache, 正确的做法
e) 太大的result set不会被cache (

2. 何时invalidate
a) 一旦表数据进行任何一行的修改,基于该表相关cache立即全部失效。
b) 为什么不做聪明一点判断修改的是否cache的内容?因为分析cache内容太复杂,服务器需要追求最大的性能。

3. 性能
a) cache 未必所有场合总是会改善性能
当有大量的查询和大量的修改时,cache机制可能会造成性能下降。因为每次修改会导致系统去做cache失效操作,造成不小开销。
另外系统cache的访问由一个单一的全局锁来控制,这时候大量>的查询将被阻塞,直至锁释放。所以不要简单认为设置cache必定会带来性能提升。
b) 大result set不会被cache的开销
太大的result set不会被cache, 但mysql预先不知道result set的长度,所以只能等到reset set在cache添加到临界值 query_cache_limit 之后才会简单的把这个cache 丢弃。这并不是一个高效的操作。如果mysql status中Qcache_not_cached太大的话, 则可对潜在的大结果集的sql显式添加 SQL_NO_CACHE 的控制。
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

4. 内存池使用
mysql query cache 使用内存池技术,自己管理内存释放和分配,而不是通过操作系统。内存池使用的基本单位是变长的block, 一个result set的cache通过链表把这些block串起来。因为存放result set的时候并不知道这个resultset最终有多大。block最短长度为 query_cache_min_res_unit, resultset 的最后一个block会执行trim操作。


Query Cache 在提高数据库性能方面具有非常重要的作用。

其设定也非常简单,仅需要在配置文件写入两行: query_cache_type 和 query_cache _size,而且 MySQL 的 query cache 非常快!而且一旦命中,就直接发送给客户端,节约大量的 CPU 时间。

当然,非 SELECT 语句对缓冲是有影响的,它们可能使缓冲中的数据过期。一个 UPDATE 语句引起的部分表修改,将导致对该表所有的缓冲数据失效,这是 MySQL 为了平衡性能而没有采取的措施。因为,如果每次 UPDATE 需要检查修改的数据,然后撤出部分缓冲将导致代码的复杂度增加。

 query_cache_type 0 代表不使用缓冲, 1 代表使用缓冲,2 代表根据需要使用。

设置 1 代表缓冲永远有效,如果不需要缓冲,就需要使用如下语句:

 代码如下 复制代码

SELECT SQL_NO_CACHE * FROM my_table WHERE ...

如果设置为 2 ,需要开启缓冲,可以用如下语句:

 代码如下 复制代码

SELECT SQL_CACHE * FROM my_table WHERE ...

用 SHOW STATUS 可以查看缓冲的情况:

 代码如下 复制代码

mysql> show status like 'Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 8 |
| Qcache_inserts | 545875 |
| Qcache_hits | 83951 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2343256 |
| Qcache_free_memory | 33508248 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 18 |
+-------------------------+----------+
8 rows in set (0.00 sec)

如果需要计算命中率,需要知道服务器执行了多少 SELECT 语句:

 代码如下 复制代码

mysql> show status like 'Com_sel%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 2889628 |
+---------------+---------+
1 row in set (0.01 sec)

在本例中, MySQL 命中了 2,889,628 条查询中的 83,951 条,而且 INSERT 语句只有 545,875 条。因此,它们两者的和和280万的总查询相比有很大差距,因此,我们知道本例使用的缓冲类型是 2 。


而在类型是 1 的例子中, Qcache_hits 的数值会远远大于 Com_select

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

The difference between vivox100s and x100: performance comparison and function analysis The difference between vivox100s and x100: performance comparison and function analysis Mar 23, 2024 pm 10:27 PM

Both vivox100s and x100 mobile phones are representative models in vivo's mobile phone product line. They respectively represent vivo's high-end technology level in different time periods. Therefore, the two mobile phones have certain differences in design, performance and functions. This article will conduct a detailed comparison between these two mobile phones in terms of performance comparison and function analysis to help consumers better choose the mobile phone that suits them. First, let’s look at the performance comparison between vivox100s and x100. vivox100s is equipped with the latest

How to show hidden performance overlays in Windows 11 How to show hidden performance overlays in Windows 11 Mar 24, 2024 am 09:40 AM

In this tutorial, we will help you reveal hidden performance overlays in Windows 11. Using Windows 11's Performance Overlay feature, you'll be able to monitor your system resources in real time. You can view real-time CPU usage, disk usage, GPU usage, RAM usage, etc. on your computer screen. This is convenient when you are playing games or using large graphics programs (such as video editors) and need to check how much system performance is affected when using a specific program. While there are some excellent free software available for monitoring system performance, and some built-in tools like Resource Monitor can be used to check system performance, the performance overlay feature also has its advantages. For example, you don't need to leave the program or app you're currently using or

Comparing the performance of Win11 and Win10 systems, which one is better? Comparing the performance of Win11 and Win10 systems, which one is better? Mar 27, 2024 pm 05:09 PM

The Windows operating system has always been one of the most widely used operating systems on personal computers, and Windows 10 has long been Microsoft's flagship operating system until recently when Microsoft launched the new Windows 11 system. With the launch of Windows 11 system, people have become interested in the performance differences between Windows 10 and Windows 11 systems. Which one is better between the two? First, let’s take a look at W

Windows 10 vs. Windows 11 performance comparison: Which one is better? Windows 10 vs. Windows 11 performance comparison: Which one is better? Mar 28, 2024 am 09:00 AM

Windows 10 vs. Windows 11 performance comparison: Which one is better? With the continuous development and advancement of technology, operating systems are constantly updated and upgraded. As one of the world's largest operating system developers, Microsoft's Windows series of operating systems have always attracted much attention from users. In 2021, Microsoft released the Windows 11 operating system, which triggered widespread discussion and attention. So, what is the difference in performance between Windows 10 and Windows 11? Which

Kirin 8000 processor competes with Snapdragon series: Who can be king? Kirin 8000 processor competes with Snapdragon series: Who can be king? Mar 25, 2024 am 09:03 AM

In the era of mobile Internet, smartphones have become an indispensable part of people's daily lives. The performance of smartphones often directly determines the quality of user experience. As the "brain" of a smartphone, the performance of the processor is particularly important. In the market, the Qualcomm Snapdragon series has always been a representative of strong performance, stability and reliability, and recently Huawei has also launched its own Kirin 8000 processor, which is said to have excellent performance. For ordinary users, how to choose a mobile phone with strong performance has become a key issue. Today we will

The local running performance of the Embedding service exceeds that of OpenAI Text-Embedding-Ada-002, which is so convenient! The local running performance of the Embedding service exceeds that of OpenAI Text-Embedding-Ada-002, which is so convenient! Apr 15, 2024 am 09:01 AM

Ollama is a super practical tool that allows you to easily run open source models such as Llama2, Mistral, and Gemma locally. In this article, I will introduce how to use Ollama to vectorize text. If you have not installed Ollama locally, you can read this article. In this article we will use the nomic-embed-text[2] model. It is a text encoder that outperforms OpenAI text-embedding-ada-002 and text-embedding-3-small on short context and long context tasks. Start the nomic-embed-text service when you have successfully installed o

Comparison of PHP and Go languages: big performance difference Comparison of PHP and Go languages: big performance difference Mar 26, 2024 am 10:48 AM

PHP and Go are two commonly used programming languages, and they have different characteristics and advantages. Among them, performance difference is an issue that everyone is generally concerned about. This article will compare PHP and Go languages ​​from a performance perspective, and demonstrate their performance differences through specific code examples. First, let us briefly introduce the basic features of PHP and Go language. PHP is a scripting language originally designed for web development. It is easy to learn and use and is widely used in the field of web development. The Go language is a compiled language developed by Google.

Performance comparison of different Java frameworks Performance comparison of different Java frameworks Jun 05, 2024 pm 07:14 PM

Performance comparison of different Java frameworks: REST API request processing: Vert.x is the best, with a request rate of 2 times SpringBoot and 3 times Dropwizard. Database query: SpringBoot's HibernateORM is better than Vert.x and Dropwizard's ORM. Caching operations: Vert.x's Hazelcast client is superior to SpringBoot and Dropwizard's caching mechanisms. Suitable framework: Choose according to application requirements. Vert.x is suitable for high-performance web services, SpringBoot is suitable for data-intensive applications, and Dropwizard is suitable for microservice architecture.

See all articles