インデックスをカバーする MySQL インデックスの最適化

最近、古いビジネス コードを扱うときに問題が発生しました。この記事では主に MySQL インデックスの最適化に関するインデックスについて説明します。

私は最近、古いビジネス コードを扱っているときにこの例に遭遇しました:

テーブル構造は次のとおりです:

CREATE TABLE `group_user` ( 
  `id` int(11) NOT NULL auto_increment, 
  `uid` int(11) NOT NULL, 
  `username` varchar(16) NOT NULL,
  `gid` int(11) NOT NULL, 
  `create_time` int(10) NOT NULL, 
  `update_time` int(10) NOT NULL, 
  PRIMARY KEY  (`id`), 
  KEY `idx_uid` (`uid`), 
  KEY `idx_gid` (`gid`) 
) ENGINE=InnoDB AUTO_INCREMENT=1530312 DEFAULT CHARSET=utf8

150w のデータ、そのようなステートメント:

SELECT SQL_NO_CACHE uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

実際には 2 秒かかった低速クエリ ログが多数あります。 Explain の結果は :

+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                          | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
|  1 | SIMPLE      | group_user | ref  | idx_gid       | idx_gid  | 4       | const | 6535 | Using where; Using filesort    | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+

Explain の結果から、クエリがインデックスを使用していることがわかりますが、それでもまだ遅いのはなぜでしょうか?

分析: まず、ステートメント ORDER BY は filesort ファイルの並べ替えを使用しており、クエリ効率が低いです。次に、クエリ フィールドがインデックス上になく、カバー インデックスが使用されていないため、クエリを実行する必要があります。最後に、データ分散の点では、gid は同じです。uid ハッシュは比較的均等であり、セカンダリ インデックスのみを使用する効果は平均的です (インデックスがわからない場合)。分類については、「MySQL インデックス分類の概要」をクリックしてください)。

解決策: uid フィールドのみがクエリされるため、ジョイント インデックスを追加すると、テーブル バッキングとファイルの並べ替えを回避し、カバー インデックスを使用してクエリ速度を向上させ、インデックスを使用して並べ替えを完了できます。

カバード インデックス: MySQL は、クエリに必要なデータを返すためにインデックスを使用するだけでよく、セカンダリ インデックスを通じて主キーを見つけてデータをクエリする必要はありません。

ALTER TABLE group_user ADD INDEX idx_gid_ctime_uid (gid, create_time, uid);

もう一度説明します:

EXPLAIN SELECT SQL_NO_CACHE uid FROM group_user USE INDEX(idx_gid_ctime_uid) WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | group_user | ref  | idx_gid_ctime_uid | idx_gid_ctime_uid | 4       | const | 6375 | Using where; Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+

追加情報にはすでに「インデックスの使用」が含まれています。これは、カバーインデックスが使用されていることを意味します (通常、SELECT * を実行する同僚が多数いますが、これは落とし穴です)。

なぜステートメントで使用するインデックスを手動で指定する必要があるのですか? idx_gid インデックスが削除されない限り、MySQL クエリ オプティマイザーが idx_gid インデックスを使用する可能性があるためです。

インデックスの最適化後、オンラインクエリは基本的に 0.001 秒もかかりません。

最後の質問: このテーブルが MyISAM エンジンを使用している場合、実際の状況はどうなりますか?

関連する推奨事項:

Mysql インデックス最適化方法

mysql インデックス最適化の使用方法

Mysql 最適化ストレージ エンジンとインデックス最適化についての深い理解

以上がインデックスをカバーする MySQL インデックスの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。