关于MySQL里lsquo;show global statusrsquo;输出的Com_select、Queries、Questions、Qcache_hits、Qcache_inserts、Qcache_not
关于MySQL里‘show global status’输出的Com_select、Queries、Questions、Qcache_hits、Qcache_inserts、Qcache_not_cached这几个状态值一直有些迷惑，现通过实验来更加准确和深刻的了解之。
实验版本：5.5.39
先附上每个状态的官方解释
Com_select：The Com_xxx statement counter variables indicate the number of times each xxx statement hasbeen executed.However, if a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select.
select语句执行的次数，如果结果从查询缓存里得来，就增加Qcache_hits，而不增加Com_select
Queries：The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
server执行过的所有语句，计数包含存储存储程序(存储过程)里面的sql条数
Questions：The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
只包含由client发过来的sql语句数量（包括use语句，show语句等），不包括存储过程里的sql语句（一个存储过程作为一条语句）
Qcache_hits：The number of query cache hits.
查询缓存命中的次数
Qcache_inserts：The number of queries added to the query cache.
加入到查询缓存的次数
Qcache_not_cached：The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).
不可缓存的查询数量。不可缓存的（例如结果集大小超过query_cache_limit的）；或者由于query_cache_type设置（on 允许、off 不允许、demand 依赖明确指定的使用缓存）不能使用查询缓存的数量。
Qcache_queries_in_cache：The number of queries registered in the query cache
在查询缓存中‘注册’过的查询语句的条数
下面通过实验来观察个 状态值的增长情况：
重启数据库，清空查询缓存
执行一条查询语句，然后观察个状态值
Com_select | 1
Qcache_hits | 0
Qcache_inserts | 1
在执行上面执行的查询语句，个状态值
Com_select 不变
Qcache_hits 加1
Qcache_inserts 不变
明确指定不使用缓存（加上SQL_NO_CACHE），执行刚才的查询语句
Com_select 加1
Qcache_hits 不变
Qcache_not_cached 加1
在搞清楚了以上几个值的含义后，讨论一下计算‘查询缓存命中率’的公式，关于查询缓存命中率的计算方法，网上说法不一，这里说一下我的理解：
查询语句分为‘可缓存’和‘不可缓存’两种
可缓存的：若存在于query_cache中，则Com_select不变，，Qcache_hits加1
不存在于query_cache中，则Com_select加1，Qcache_inserts加1
不可缓存的：select加1，Qcache_not_cached加1
即 Com_select = Qcache_inserts+Qcache_not_cached
所以查询缓存的命中率可以计算如下：
Qcache_hits/（Qcache_hits+Com_select）
附:查询缓存变量含义：
Qcache_free_blocks
，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory
缓存中的空闲内存总量。
Qcache_hits
缓存命中次数。
Qcache_inserts
缓存失效次数(查询结果新增进QueryCache的数量)
Qcache_lowmem_prunes
Qcache_not_cached
不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句以及由于query_cache_type设置的不会被Cache的查询，或者结果集超过query_cache_limit变量设置的语句。
Qcache_queries_in_cache
当前在query_cache中‘注册’的select语句条数
Qcache_total_blocks
缓存中块的总量。本文永久更新链接地址：
MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AMMySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.
MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AMMySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AMMySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.
The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AMSQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AMThe basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
MySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AMThe main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
MySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AMThe steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.
MySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AMMySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.
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
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.
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
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.
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Notepad++7.3.1
Easy-to-use and free code editor
