ホームページ > 記事 > バックエンド開発 > インデックスをカバーする MySQL インデックスの最適化

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

小云云オリジナル: 2018-03-22 09:29:531715ブラウズ

最近、古いビジネスコードを扱うときに問題が発生しました。この記事では主に 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 インデックスの最適化

関連記事