nativcalmysql_mysql的查询缓存模式介绍

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

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

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

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

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

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

1.    SELECT * from TABLE1

2.

3.    SELECT * FROM TABLE1

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

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

1.    SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

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

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

1.    SELECT SQL_CALHE * FROM TABLE1

二、MySQL对查询的自动优化

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

1.    CREATE TABLE SALES

2.

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)&＃xff0c;

16.

17.    INDEX (NAME)&＃xff0c;

18.

19.    INDEX (SALE_DATE)

20.

21.    );

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

SELECT AVG(PRICE) FROM SALES

WHERE ID &＃61; 1000 AND SALE_DATE BETWEEN ’2004-01-01′ AND ’2005-12-31′;

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

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