通过force index了解的MySQL查询的性能优化

骑猪看日落 2022-08-24 04:09 266阅读 0赞

查询是数据库技术中最常用的操作。查询操作的过程比较简单，首先从客户端发出查询的SQL语句，数据库服务端在接收到由客户端发来的SQL语句后， 执行这条SQL语句，然后将查询到的结果返回给客户端。虽然过程很简单，但不同的查询方式和数据库设置，对查询的性能将会有很在的影响。  
  
因此，本文就在MySQL中常用的查询优化技术进行讨论。讨论的内容如：通过查询缓冲提高查询速度；MySQL对查询的自动优化；基于索引的排序；不可达查询的检测和使用各种查询选择来提高性能。  
  
一、 通过查询缓冲提高查询速度  
  
一般我们使用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 TABLE1  
2.   
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后，才使用查询缓冲。  
  
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)，  
16.   
17.INDEX (NAME)，  
18.   
19.INDEX (SALE\_DATE)  
20.   
21.);  
假设这个表中保存了数百万条数据，而我们要查询商品号为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语句:  
  
1.SELECT \* FROM SALES WHERE NAME = “name” ORDER BY SALE\_DATE DESC;  
在以上的SQL的WHERE子句中已经使用了NAME字段上的索引，因此，在对SALE\_DATE进行排序时将不再使用索引。为了解决这个问题，我们可以对SALES表建立复合索引:  
  
1.ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE\_DATE)  
这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时， 要确保WHERE子句中没有排序字段， 在上例中就是不能用SALE\_DATE进行查询，否则虽然排序快了，但是SALE\_DATE字段上没有单独的索引，因此查询又会慢下来。  
  
四、 不可达查询的检测  
  
在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value < 100 AND value > 200。我们永远无法找到一个既小于100又大于200的数。  
  
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:  
  
1.SELECT \* FROM SALES WHERE NAME = “name1” AND NAME = “name2”  
以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行 SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用 EXPLAIN进行测试:  
  
1.EXPLAIN SELECT \* FROM SALES WHERE NAME = “name1”  
上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:  
  
1.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并不一定是谁连接谁:  
  
1.SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …  
如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT\_JOIN关键字，如下列的SQL语句:  
  
1.SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT\_JOIN TABLE2 WHERE …  
由上面的SQL语句可知，通过STRAIGHT\_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT\_JOIN来确定连接顺序。  
  
2. 干预索引使用，提高性能  
  
在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。  
  
限制使用索引的范围  
  
有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。  
  
1.SELECT \* FROM TABLE1 USE INDEX (FIELD1, FIELD2) …  
从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。  
  
限制不使用索引的范围  
  
如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。  
  
1.SELECT \* FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …  
在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。  
  
强迫使用某一个索引  
  
上面的两个例子都是给MySQL提供一个选择，也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。  
  
1.SELECT \* FROM TABLE1 FORCE INDEX (FIELD1) …  
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。  
  
3. 使用临时表提供查询性能  
  
当我们查询的结果集中的数据比较多时，可以通过SQL\_BUFFER\_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。  
  
1.SELECT SQL\_BUFFER\_RESULT \* FROM TABLE1 WHERE …  
和SQL\_BUFFER\_RESULT.选项类似的还有SQL\_BIG\_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。  
  
1.SELECT SQL\_BUFFER\_RESULT FIELD1, COUNT(\*) FROM TABLE1 GROUP BY FIELD1  
六、 结论  
  
在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。