データベースクエリのパフォーマンスを最適化するにはどうすればよいですか? (詳しい説明)

#データベース クエリのパフォーマンスを最適化するにはどうすればよいですか? (詳細な説明)

クエリは、データベース テクノロジで最も一般的に使用される操作です。クエリの動作の流れは比較的単純で、まずクライアントからクエリSQLを発行し、クライアントから送信されたSQLを受信したデータベースサーバがSQLを実行し、クエリ結果をクライアントに返します。プロセスは非常に単純ですが、クエリ方法とデータベース設定の違いはクエリのパフォーマンスに大きな影響を与えます。

したがって、この記事では、MySQL で一般的に使用されるクエリ最適化手法について説明します。議論される内容には、クエリ バッファリングによるクエリ速度の向上、MySQL のクエリの自動最適化、インデックス ベースの並べ替え、到達不能なクエリの検出、およびパフォーマンスを向上させるためのさまざまなクエリ オプションの使用が含まれます。

1. クエリ バッファリングによるクエリ速度の向上
一般に、SQL ステートメントを使用してクエリを実行する場合、データベース サーバーはクライアントから SQL を受け取るたびにこのステートメントを実行します。 。ただし、まったく同じ SQL ステートメントを一定の間隔内 (たとえば 1 分以内) に受信した場合は、同じように実行されます。これによりデータのリアルタイム性が保証されますが、ほとんどの場合、データは完全なリアルタイム性を必要としないため、ある程度の遅延が発生する可能性があります。この場合、まったく同じ SQL を短期間に実行してもメリットはありません。
幸いなことに、MySQL にはクエリ バッファリング機能が備わっています (クエリ バッファリングは MySQL 4.0.1 以降でのみ使用できます)。クエリ キャッシュにより、クエリのパフォーマンスをある程度向上させることができます。

MySQL インストール ディレクトリの my.ini ファイルを通じてクエリ バッファを設定できます。設定も非常に簡単で、query_cache_type を 1 に設定するだけです。この属性を設定した後、SELECT ステートメントを実行する前に、MySQL は同じ SELECT ステートメントが以前に実行されたかどうかをバッファ内でチェックします。そうであれば、実行結果の有効期限が切れていない場合、クエリ結果はクライアントに直接返されます。ただし、SQL ステートメントを記述する場合、MySQL のクエリ バッファでは大文字と小文字が区別されることに注意してください。次の 2 つの SELECT ステートメントは次のとおりです。

SELECT * from TABLE1
SELECT * FROM TABLE1

上記 2 つの SQL ステートメントは、クエリ バッファリングのためのまったく異なる SELECT です。さらに、クエリ バッファはスペースを自動的に処理しないため、SQL ステートメントを作成するときは、スペース、特に SQL の先頭と末尾のスペースの使用を減らすように努める必要があります (クエリ キャッシュが最初と最後のスペースを自動的にインターセプトしないため)。始まりと終わり）。

クエリ バッファを設定しないとパフォーマンスが低下する場合がありますが、リアルタイムでデータをクエリする必要がある SQL ステートメントや、頻繁に使用されない (おそらく 1 日に 1 回か 2 回実行される) SQL ステートメントがいくつかあります。これにはバッファリングをオフにする必要があります。もちろん、query_cache_type の値を設定することでクエリ キャッシュをオフにすることもできますが、これによりクエリ キャッシュは永続的にオフになります。 MySQL 5.0 には、クエリ バッファを一時的にオフにするメソッドが用意されています:

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

上記の SQL ステートメントは SQL_NO_CACHE を使用するため、この SQL ステートメントが実行されたかどうかに関係なく、サーバーはバッファ内を検索しません。そして毎回それを実行します。
my.ini の query_cache_type を 2 に設定して、SQL_CACHE が使用された後にのみクエリ キャッシュが使用されるようにすることもできます。

SELECT SQL_CALHE * FROM TABLE1

2. MySQL のクエリの自動最適化

インデックスはデータベースにとって非常に重要です。インデックスを使用すると、クエリ時のパフォーマンスを向上させることができます。ただし、インデックスを使用するとパフォーマンスが低下する場合があります。次の SALES テーブルを見てみましょう:

CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
PRICE FLOAT NOT NULL,
SALE_COUNT INT NOT NULL,
SALE_DATE DATE NOT NULL,
PRIMARY KEY(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 開発者が経験に基づいて求めています)。ただし、実際の比率の値は、使用するデータベース エンジンによって異なります。

3. インデックスベースの並べ替え

MySQL の弱点の 1 つは、その並べ替えです。 MySQL は 1 秒間に約 15,000 レコードをクエリできますが、MySQL はクエリ時に最大 1 つのインデックスしか使用できません。したがって、WHERE 条件がすでにインデックスを占有している場合、そのインデックスは並べ替えに使用されず、クエリの速度が大幅に低下します。次の SQL ステートメントを見てみましょう:

SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

NAME フィールドのインデックスは、上記の SQL の WHERE 句で使用されています。したがって、このインデックスは、SALE_DATE を並べ替えるときに使用されなくなります。 。この問題を解決するには、SALES テーブルに複合インデックスを作成します:

ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)

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

四、 不可达查询的检测

在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value  200。我们永远无法找到一个既小于100又大于200的数。
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:

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的控制索引的一些查询选项。

限制使用索引的范围：

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

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

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

限制不使用索引的范围

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

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

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

强迫使用某一个索引

上面的两个例子都是给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 GROUP BY FIELD1

六、 结论
在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。 

推荐教程：《MySQL教程》

以上がデータベースクエリのパフォーマンスを最適化するにはどうすればよいですか? (詳しい説明)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。