Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

第 9 章 Optimization

目錄

9.1. Buffering and Caching
9.1.1. Query Cache SELECT Options
9.2. where 優化
9.3. SHOW PROFILE Syntax SQL性能分析器
9.4. PROCEDURE ANALYSE()

9.1. Buffering and Caching

查看緩存是否開啟

		
MySQL> select @@query_cache_type;
MySQL> show variables like 'query_cache_type';
		
		

開啟與關閉緩存

		
MySQL> set query_cache_type=on;
MySQL> set query_cache_type=off;
		
		

查看緩存狀態

show variables like 'have_query_cache';
		

查詢緩存的大小

		
MySQL> select @@global.query_cache_size;
MySQL> select @@query_cache_size;
		
		

查看最大緩存限制,如果集大於該數則不緩存。

		
MySQL> select @@global.query_cache_limit;
		
		

清除緩存/重置緩存

		
MySQL> flush tables;
MySQL> flush query cache;
MySQL> reset query cache;
		
		

查詢緩存性能

		
MySQL> show status like 'qcache%';

MySQL> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)

MySQL> show status like 'qcache_f%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16766728 |
+--------------------+----------+
2 rows in set (0.00 sec)
		
		

9.1.1. Query Cache SELECT Options

Two query cache-related options may be specified in SELECT statements:

SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.

SQL_NO_CACHE

The query result is not cached.

Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;


SELECT /*! SQL_NO_CACHE */ stuff FROM table

			

例 9.1. SQL_CACHE 測試

下面的例子中你將看到緩存變化

				
flush tables;
show status like 'qcache_q%';
select sql_cache * from members limit 5;
show status like 'qcache_q%';
select sql_cache * from members limit 10;
show status like 'qcache_q%';
				
				

顯示當前緩存中的信息數量:

				
MySQL> show status like 'qcache_q%';