Home | 简体中文 | 繁体中文 | 杂文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品与服务 | 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%';
				
				

comments powered by Disqus