mysql的查询缓存_MySQL

bitsCN.com

mysql的查询缓存

 

    查询是数据库技术中最常用的操作。查询操作的过程比较简单，首先从客户端发出查询的SQL语句，数据库服务端在接收到由客户端发来的 SQL语句后， 执行这条SQL语句，然后将查询到的结果返回给客户端。虽然过程很简单，但不同的查询方式和数据库设置，对查询的性能将会有很在的影响。

     因此，本文就在MySQL中常用的查询优化技术进行讨论。讨论的内容如：通过查询缓冲提高查询速度；MySQL对查询的自动优化；基于索引的排序；不可达查询的检测和使用各种查询选择来提高性能。

 

     显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。

 

 

通过查询缓冲提高查询速度

     一般我们使用SQL语句进行查询时，数据库服务器每次在收到客户端发来SQL后，都会执行这条SQL语句。但当在一定间隔内(如1分钟内)，接到完 全一样的SQL语句，也同样执行它。虽然这样可以保证数据的实时性，但在大多数时候，数据并不要求完全的实时，也就是说可以有一定的延时。如果是这样的 话，在短时间内执行完全一样的SQL就有些得不偿失。

      幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

      我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单，只需要将query_cache_type设为1即可。在设 置了这个属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过 期，那么就直接取查询结果返回给客户端。但在写SQL语句时注意，MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句:

1.    SELECT * from TABLE12.3.    SELECT * FROM TABLE1

 

     上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。

      虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或者并不经常使用(可能一天就执行一两次)。这样就需要把 缓冲关了。当然，这可以通过设置query_cache_type的值来关闭查询缓冲，但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法:

（1） SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

以上的SQL语句由于使用了SQL_NO_CACHE，因此，不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

我们还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

 

（2） SELECT SQL_CALHE * FROM TABLE1

 

 

 

MySQL 检查缓存命中的规则

 

（1）在检查缓存的时候，MySQL 不会对语句进行解析、正则化或者参数化，它精确地使用客户端传来的查询语句和其他数据。只要字符大小写、空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询

 

（2）查询缓存不会存储有不确定结果的查询。因此，任何一个包含不确定函数（比如NOW()或CURRENT_DATE()）的查询不会被缓存。同样地，CURRENT_USER()或CONNECTION_ID()这些由不同用户执行，将会产生不同的结果的查询也不会被缓存。事实上，查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、mysql 数据库中的表或者任何一个有列级权限的表的查询

 

（3）查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。

 

MySQL对查询的自动优化

      索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:

1.    CREATE TABLE SALES2.3.    (4.5.    ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,6.7.    NAME VARCHAR(100) NOT NULL,8.9.    PRICE FLOAT NOT NULL,10.11.    SALE_COUNT INT NOT NULL,12.13.    SALE_DATE DATE NOT NULL,14.15.    PRIMARY KEY(ID)，16.17.    INDEX (NAME)，18.19.    INDEX (SALE_DATE)20.21.    );

 

     假设这个表中保存了数百万条数据，而我们要查询商品号为1000的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:

SELECT AVG(PRICE) FROM SALESWHERE ID = 1000 AND SALE_DATE BETWEEN ’2004-01-01′ AND ’2005-12-31′;

 

      如果这种商品的数量非常多，差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使 用索引，就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例)，速度会变慢，这样还不如对整个表进行扫描。因 此，MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。

      对于MySQL来说，上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了，这个比例是MySQL的开发人员根据他们的经验得出的。然而，实际的比例值会根据所使用的数据库引擎不同而不同。

 

开启查询缓存的开销

 

（1）读取查询在开始之前必须要检查缓存。

 

（2）如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销。写入数据的查询也会有额外的开销，因为它必须使缓存中相关的数据表失效。（如果缓存中的数据，被删除或跟新了，数据库怎么判断这个缓存的数据不能用了，是脏数据？）

 

     这些开销相对来说较小，所以查询缓存还是很有好处的。但是，稍后你会看到，额外的开销有可能也会增加。从缓存中受益最多的查询可能是需要很多资源来产生结果，但是不需要很多空间来保存的类型。所以用于存储、返回和失效的代价都较小。聚集查询，比如从大表中利用COUNT()产生较小的结果，就符合这个范畴。

 

bitsCN.com