効果的な MySQL SQL ステートメントの最適化 -- インデックス

1 2 つのインデックスの結合結合

ALTER TABLE album ADD INDEX name_release (name,first_released);
EXPLAIN SELECT a.name, ar.name,
a.first_released
  FROM album a
 INNER JOIN artist ar USING (artist_id)
 WHERE a.name = 'Greatest Hits'
 ORDER BY a.first_released;
mysql> EXPLAIN SELECT a.name, ar.name,
    -> a.first_released
    ->   FROM album a
    ->  INNER JOIN artist ar USING (artist_id)
    ->  WHERE a.name = 'Greatest Hits'
    ->  ORDER BY a.first_released;
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | a     | ref    | name_release,name_2,name_part2 | name_release | 257     | const             |  659 | Using where |
|  1 | SIMPLE      | ar    | eq_ref | PRIMARY                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ALTER TABLE album ADD INDEX name_release (name,first_released);

MySQL は WHERE、ORDER BY、GROUP BY カラムでインデックスを使用できますが、通常、MySQL はテーブル上の 1 つのインデックスのみを選択します。
MySQL 5.0 以降、オプティマイザーは個々の例外で複数のインデックスを使用することがありますが、以前のバージョンで使用するとクエリの実行が遅くなります。

2 2 つのインデックスの結合
最初のタイプ: 最も一般的なインデックスの結合操作は、ユーザーが非常に高いカーディナリティで 2 つのインデックスに対して OR 操作を実行すると、この種のインデックスが生成されます。マージ操作が発生します。
次の例を見てください:

 SET @@session.optimizer_switch='index_merge_intersection=on';
 
 EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
 OR founded = 1942\G
 
mysql>  EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->  OR founded = 1942;
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.01 sec)

補足: Union(name,founded); を使用すると、コレクションを取得するために Union のジョイント インデックス モードが採用されます。

注意
optimizer_switch は MySQL 5.1 システムで初めて導入されました。これらの追加オプションは、この変数を有効または無効にすることで制御できます。詳細については、リンク http://www.php.cn/ を参照してください。

2 インデックスのマージの 2 番目のタイプは、以下に示すように、少数の一意の値を持つ 2 つのインデックスを交差させることです。したがって、値を走査するには 2 つのインデックスのうち最も効率的なインデックスを選択するだけで済みます

3 3 番目のタイプのインデックス結合操作は 2 つのインデックスの結合に似ていますが、最初に Sorted が必要です。

SET @@session.optimizer_switch='index_merge_intersection=on';
EXPLAIN SELECT artist_id, name
 FROM artist
  WHERE type = 'Band'
 AND founded = 1942;
 
 mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->   WHERE type = 'Band'
    ->  AND founded = 1942;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

インデックスの結合について詳しくは、リンク http://www.php.cn/ をご覧ください。

4 複数のインデックスをマージするケース

これらの例を作成するプロセス中に、これまでのクライアント クエリには現れなかった新しい状況が発見されました。インデックスのマージの 3 つの例を次に示します。

EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
  OR (founded BETWEEN 1942 AND 1950);
  mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->   OR (founded BETWEEN 1942 AND 1950);
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                       |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
1 row in set (0.00 sec)

ヒント

オプティマイザにインデックス列をマージさせるよりも複数列インデックスの方が効率的かどうかを常に評価する必要があります。複数の単一列インデックスと複数の複数列インデックスではどちらの方が有利ですか?この質問
は、特定のアプリケーションのクエリ タイプとクエリ容量と組み合わせてのみ回答できます。さまざまなクエリ条件の下で、カーディナリティの高い列の単一列インデックスをマージすると、高い柔軟性が得られます。データベース書き込み操作のパフォーマンス参照要素も、データを取得するための最適なデータ アクセス パスに影響します。

5 より良い MySQL インデックスを作成します

主に 2 つの特別なインデックスを使用します
インデックスを使用することで、クエリの実行時間を秒単位からミリ秒単位に短縮できます。アプリケーションのパフォーマンスを飛躍的に向上させます。 インデックスを適切に調整することは、最適化、特に高スループットのアプリケーションにとって非常に重要です。実行時間の改善がわずか数ミリ秒であっても、1 秒あたり 1000 回実行されるクエリにとっては非常に意味のあるパフォーマンスの向上です。たとえば、本来は実行に 20 ミリ秒かかるクエリを 1 秒あたり 1,000 回実行する場合、実行時間を 4 ミリ秒短縮することは、SQL ステートメントを最適化するために重要です。第 4 章で紹介した方法を使用して複数列インデックスを作成し、この基盤に基づいてより適切なカバレッジ インデックスを作成します。

● カバーインデックスを作成します

ALTER TABLE Artist
DROP INDEXfounded, ADD INDEXfounded_name (founded,name);
InnoDB では、主キーの値が非主キーの対応する各レコードに追加されますIndex なので、非主キー インデックスで主キーを指定する必要はありません。 この重要な機能は、InnoDB エンジン内のすべての非主キー インデックスに暗黙的な主キー カラムがあることを意味します。また、MyISAM ストレージ エンジンから変換されたテーブルの場合、主キーは通常、InnoDB テーブル インデックスの最後の要素として追加されます。 QEP が [追加] 列に [インデックスの使用] を表示する場合、基になるテーブル データにアクセスするときにインデックスが使用されることを意味するものではなく、このインデックスのみがクエリのすべての要件を満たしていることを意味します。この種のインデックスは、大規模なクエリまたは頻繁に実行されるクエリのパフォーマンスを大幅に向上させることができます。これは、カバー インデックスと呼ばれます。カバリング インデックスは、クエリ内の特定のテーブルで使用されるすべての列をカバーするという事実からその名前が付けられています。カバーインデックスを作成するには、指定されたテーブルの WHERE ステートメント、ORDER BY ステートメント、GROUP BY ステートメント (存在する場合)、および SELECT ステートメント内のすべての列がこのインデックスに含まれている必要があります。

[コメント]: データ容量が増加すると、特にメモリとディスクの最大容量を超えると、大きな列のインデックスを作成すると、
システム全体のパフォーマンスに影響を与える可能性があります。カバーインデックスは、多くの短い長さの主キー制約と外部キー制約を使用する大規模な正規化スキーマにとって理想的な最適化です。


● ローカル列のインデックスを作成します

ALTER TABLE artist
 DROP INDEX name,
  ADD INDEX name_part(name(20));

这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销，而这又意味着能更快地访问到需
要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低
基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name 列不会对执行过
的SQL 语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。

EXPLAIN SELECT artist_id,name,founded
 FROM artist
 WHERE name LIKE 'Queen%';
 mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name          | name | 257     | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在这个示例中，Extra后面没有出现Using Index，所以在索引中记录全名并没有带来额外的益处。
而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索
引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较
是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录，而SELECT count(*) FROM artist;有577983条记录，按照普遍的情况，可以走索引，难道是name(20)的20定义的太长了？

ALTER TABLE artist
 DROP INDEX name_part,
  ADD INDEX name_part2(name(10));

  mysql> ALTER TABLE artist
    ->  DROP INDEX name_part,
    ->   ADD INDEX name_part2(name(10));
Query OK, 0 rows affected (3.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12      | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看结果，再用name(5) 试试看。
mysql> ALTER TABLE artist
    ->  DROP INDEX name_part2,
    ->   ADD INDEX name_part3(name(5));
Query OK, 0 rows affected (3.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part3    | name_part3 | 7       | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看来局部索引对like的效果不是很明显的，可能跟数据分布范围有关，也许这93条数据全部打散在各个数据库块中，
所以导致解析器认为不能简单地通过数次index就能遍历出数据，故而Extra栏里面就没有出现Using Index的提示。

 
总结：在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来
巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000 条查询减少几毫秒
将会显著改善性能，并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。

 

 以上就是Effective MySQL之SQL语句最优化--索引 的内容，更多相关内容请关注PHP中文网（www.php.cn）！