MYSQL查询优化(Ⅰ)

时间:2024-01-06 13:08:08

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

  一般我们使用SQL语句进行查询时,数据库服务器每次在收到客户端 发来SQL后,都会执行这条SQL语句。但当在一定间隔内(如1分钟内),接到完全一样的SQL语句,也同样执行它。虽然这样可以保证数据的实时性,但在 大多数时候,数据并不要求完全的实时,也就是说可以有一定的延时。如果是这样的话,在短时间内执行完全一样的SQL就有些得不偿失。
  幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

1、我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲:

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

SELECT * FROM TABLE1
SELECT * FROM TABLE1

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

2、临时关闭查询缓冲方法:

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

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

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

3、临时开启查询缓冲方法:

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

SELECT SQL_CALHE * FROM TABLE1

二、MySQL对查询的自动优化

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

CREATETABLE SALES
(
   ID INT(10) UNSIGNED NOTNULL AUTO_INCREMENT,
   NAME VARCHAR(100) NOTNULL,
   PRICE FLOATNOTNULL,
   SALE_COUNT INTNOTNULL,
   SALE_DATE DATE NOTNULL,
 PRIMARYKEY(ID),
 INDEX (NAME),
  INDEX (SALE_DATE)
)

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

SELECT AVG(PRICE) FROM SALES
WHERE ID=1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

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

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

三、 基于索引的排序

  MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询
大约15,000条记录,但由于MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了,这将大
大降低查询的速度。我们可以看看如下的SQL语句:

SELECT*FROM SALES WHERE NAME = 'name' ORDERBY SALE_DATE DESC;

在以上的SQL的WHERE子句中已经使用了NAME字段上的索引,因此,在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题,我们可以对SALES表建立复合索引:

ALTERTABLE SALES DROPINDEX NAME, ADDINDEX (NAME, SALE_DATE)

这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意,在使用这个方法时,要确保WHERE子句中没有排序字段,在上例中就是不能用SALE_DATE进行查询,否则虽然排序快了,但是SALE_DATE字段上没有单独的索引,因此查询又会慢下来。

SELECT*FROM SALES WHERE NAME = 'name1' AND NAME = 'name2'

以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显,这是一个不可达的查询,WHERE条件一定是假。MySQL在执行SQL

语句之前,会先分析WHERE条件是否是不可达的查询,如果是,就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用EXPLAIN
进行测试:

EXPLAIN SELECT*FROM SALES WHERE NAME = ’name1'

上面的查询是一个正常的查询,我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

EXPLAIN SELECT*FROM SALES WHERE NAME = ’name1' AND NAME = 'name2'

我们可以看到,table项是空,这说明MySQL并没有对SALES表进行操作。

四、 使用各种查询选择来提高性能

  SELECT语句除了正常的使用外,MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分,我将介绍几个常用的查询选项。

1、STRAIGHT_JOIN:强制连接顺序

当我们将两个或多个表连接起来进行查询时,我们并不用关心MySQL先连哪个表,后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估,最后得出的一个连接顺序决定的。如下列的SQL语句中,TABLE1和TABLE2并不一定是谁连接谁:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …

如果开发人员需要人为地干预连接的顺序,就得使用STRAIGHT_JOIN关键字,如下列的SQL语句:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

  由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

2、干预索引使用,提高性能

  在上面已经提到了索引的使用。一般情况下,在查询时MySQL将自己决定是否使用索引,使用哪一个索引。
  但在一些特殊情况下,我们希望MySQL只使用一个或几个索引,或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。

(1)限制使用索引的范围:

  有时我们在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置。

SELECT*FROM TABLE1 USEINDEX (FIELD1, FIELD2) …

  从以上SQL语句可以看出,无论在TABLE1中已经建立了多少个索引,MySQL在选择索引时,只考虑在FIELD1和FIELD2上建立的索引。

(2)限制不使用索引的范围:

  如果我们要考虑的索引很多,而不被使用的索引又很少时,可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引,而使用IGNORE INDEX是选择不被考虑的索引。

SELECT*FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中,TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

(3)强迫使用某一个索引:

  上面的两个例子都是给MySQL提供一个选择,也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

SELECT*FROM TABLE1 FORCE INDEX (FIELD1) …

  以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。

3. 使用临时表提供查询性能

当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

  SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

和SQL_BUFFER_RESULT选项类似的还有SQL_BIG_RESULT,这个选项一般用于分组或DISTINCT关键字,这个选项通知MySQL,如果有必要,就将查询结果放到临时表中,甚至在临时表中进行排序。

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUPBY FIELD1

转载:http://do-found.blog.163.com/blog/static/2837184200842810115882/