MySQL データベース クエリの最適化の簡単な分析
- WBOYオリジナル
- 2016-06-16 08:41:13997ブラウズ
クエリの最適化では、データベース アプリケーション (MySQL など) はツールの操作と使用を意味します。クエリの最適化は、インデックスの使用、EXPLAIN を使用したクエリの分析、MySQL の内部構成の調整によって実現できます。
#1: インデックスを使用する
MySQL ではデータベース テーブルにインデックスを付けることができるため、最初にテーブル全体をスキャンしなくてもレコードをすぐに見つけることができるため、クエリが大幅に高速化されます。各テーブルには最大 16 個のインデックスを設定できます。さらに、MySQL は複数列インデックスと全文検索もサポートしています。
テーブルへのインデックスの追加は、CREATE INDEX コマンドを呼び出してインデックスのフィールドを指定するだけで簡単です。リスト A は例を示しています:
リスト Amysql> CREATE INDEX idx_username ON users(username);<br>
Query OK, 1 row affected (0.15 sec)<br>
Records: 1 Duplicates: 0 Warnings: 0mysql> CREATE INDEX idx_username ON users(username);
クエリは OK、1 行が影響を受けました (0.15 秒)
レコード: 1 重複: 0 警告: 0 ここでは、WHERE 句または HAVING 句でこのフィールドを参照する SELECT クエリ ステートメントが、インデックスを追加しない場合よりも高速に実行されるように、 users テーブルの username フィールドにインデックスが付けられています。 SHOW INDEX コマンドを使用して、インデックスが作成されたことを確認できます (リスト B)。 mysql> SHOW INDEX FROM users;<br>
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |<br>
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| users | 1 | idx_username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | |<br>
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
1 row in set (0.00 sec)
リスト B
mysql> ユーザーからのインデックスを表示;
-------------+-------------+----------+---------- -- --+----------+----------+------+---------------+----- -- --+
| 非固有のインデックス | インデックスの種類 |
-------------+-------------+----------+---------- -- --+----------+----------+------+---------------+----- -- --+
| ユーザー名 | 1 | NULL |
-------------+-------------+----------+----------- -- --+----------+----------+------+---------------+----- -- --+
1 行 (0.00 秒) インデックスは両刃の剣のようなものであることに注意してください。テーブル内のすべてのフィールドのインデックス作成は通常は不要であり、データがテーブルに挿入または変更されるたびに MySQL がインデックスを再作成するという余分な作業を実行する必要があるため、速度が低下する可能性があります。一方、テーブル内のすべてのフィールドにインデックスを作成しないこともあまり良い考えではありません。レコードの挿入速度は向上しますが、クエリ操作が遅くなるからです。これには、バランス ポイントを見つける必要があります。たとえば、インデックス システムを設計するときは、テーブルの主な機能 (データの修復と編集) を考慮するのが賢明な選択です。
<br>
#2: クエリのパフォーマンスを最適化する<br>
<br>
クエリのパフォーマンスを分析するときは、EXPLAIN キーワードを考慮することも役立ちます。 EXPLAIN キーワードは通常、SELECT クエリ ステートメントの前に配置され、MySQL がクエリ操作を実行する方法と、結果セットを正常に返すために MySQL が実行する必要がある行数を記述するために使用されます。以下の簡単な例は、このプロセスを示しています (リスト C): <br>
<br>
リスト C<br>mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';<br>
+-----+---------------+-----------+----------+--------------- ---+--------+--------+----------+------+----------- --+
| 選択可能なキー |
+-----+---------------+-----------+----------+--------------- ---+----------+----------+----------+------+---------- - --+
| 1 | プライマリ | インデックス 1 |
| 1 | ヌル | ヌル |
+-----+---------------+-----------+----------+--------------- ---+----------+----------+----------+------+---------- - --+セット内の 2 行 (0.00 秒)ここでのクエリは 2 つのテーブルの接続に基づいています。 EXPLAIN キーワードは、MySQL がこれら 2 つのテーブルの結合をどのように処理するかを記述します。現在の設計では、MySQL が country テーブルの 1 つのレコードと city テーブルの 4019 レコード全体を処理する必要があることは明らかです。これは、他の最適化手法を使用してクエリ方法を改善できることを意味します。たとえば、次のインデックスを city テーブル (リスト D) に追加します:
リスト Dmysql> CREATE INDEX idx_ccode ON city(countrycode);<code class="prettyprint linenums lang-php">mysql> CREATE INDEX idx_ccode ON city(countrycode);<br>
Query OK, 4079 rows affected (0.15 sec)<br>
Records: 4079 Duplicates: 0 Warnings: 0
クエリは OK、4079 行が影響を受けました (0.15 秒)
レコード: 4079 重複: 0 警告: 0
クエリに EXPLAIN キーワードを再利用すると、大幅な改善が見られます (リスト E):mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';<br>
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+<br>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br>
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+<br>
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |<br>
| 1 | SIMPLE | city | ref | idx_ccode | idx_ccode | 3 | const | 333 | Using where |<br>
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+<br>
2 rows in set (0.01 sec)
リスト Emysql>EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';<br>
+-----+---------------+-----------+----------+--------------- ---+-----------+--------+------+------+--------- ----+<br>
| 選択可能なキー | <br>
+-----+---------------+-----------+----------+--------------- ---+-----------+--------+------+------+--------- ----+<br>
| 1 | プライマリ | インデックス 1 |
| 1 | 都市 | idx_ccode 3 |
+-----+---------------+-----------+----------+--------------- ---+-----------+--------+------+------+--------- ----+<br>
セット内の 2 行 (0.01 秒)<br> この例では、MySQL は結果セットを生成するために city テーブル内の 333 レコードをスキャンするだけで済み、スキャンされたレコードの数はほぼ 90% 削減されました。当然のことながら、データベース リソースのクエリ速度はより速く、より効率的になります。 <br>
<br>
#3: 内部変数を調整する<br>
<br>
MySQL は非常にオープンであるため、パフォーマンスと安定性を向上させるためにデフォルト設定を簡単にさらに調整できます。最適化する必要がある主な変数は次のとおりです: <br>
<p class="da_word">
インデックス バッファ長の変更 (key_buffer) 一般に、この変数はインデックス テーブルの処理 (読み取り/書き込み操作) 時に使用されるバッファの長さを制御します。 MySQL マニュアルには、インデックス テーブルの最適なパフォーマンスを確保するためにこの変数を継続的に増やすことができると記載されており、この変数の値としてシステム メモリの 25% を使用することが推奨されています。これは MySQL の非常に重要な構成変数の 1 つであり、システム パフォーマンスの最適化と向上に興味がある場合は、まず key_buffer_size 変数の値を変更します。 </p>