MySQLのインデックス選択と最適化の詳細な説明

// データページが 16KB、データ行が 1KB、インデックスポインタが 6 バイト、インデックスフィールドが bigint 型 (8 バイト) であると仮定します。

// インデックスの数 K = 16*1024/(8+6) = 1170

// 単一のリーフノード内のレコード数 N = 16/1 = 16

// レイヤー 3 B+ レコード番号 V = K*K*N = 21902400

-- 現在のインデックスベースを表示します。mysql> show index from test;
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+
| テスト | 0 | プライマリ | 1 | id | A | 100256 | NULL | NULL | | BTREE | | |
| テスト | 1 | index_a | 1 | a | A | 98199 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

mysql> '%innodb_stats_persistent%' のような変数を表示します。
+--------------------------------------+--------------+
| 変数名 | 値 |
+--------------------------------------+--------------+
-- 統計の更新を自動的にトリガーするかどうか。変更されたデータが 10% を超えると、統計が再計算されます | innodb_stats_auto_recalc | ON |
-- 統計を再計算するときに削除マークの付いたレコードを考慮するかどうかを制御します | innodb_stats_include_delete_marked | OFF |
-- NULL 値の統計メソッド。変数が nulls_equal に設定されている場合、すべての NULL 値は同じと見なされます | innodb_stats_method | nulls_equal | 
-- メタデータを操作するときに統計の更新をトリガーするかどうか | innodb_stats_on_metadata | OFF |
--統計が永続的に保存されるかどうか | innodb_stats_persistent | ON |
-- innodb_stats_persistent=on、永続統計サンプリングのサンプル ページ数 | innodb_stats_persistent_sample_pages | 20 |
-- 非推奨、innodb_stats_transient_sample_pages に置き換えられました | innodb_stats_sample_pages | 8 |
-- 一時サンプル ページ番号 | innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+--------------+

-- インデックス情報を再計算します。mysql> analyze table t;

-- テーブルを作成する mysql> CREATE TABLE `t` (
`id` int(11) NULLではない、
`a` int(11) デフォルト NULL,
`b` int(11) デフォルト NULL,
主キー (`id`)、
キー `a` (`a`),
キー `b` (`b`)
)ENGINE=InnoDB;

-- テストデータの保存手順を定義します。mysql> delimiter;
CREATE PROCEDURE idata() を作成する
始める

i INT を宣言します。
i = 1 に設定します。
(i <= 100000) の場合
 tに挿入
価値観
 (私、私、私) ;
i = i + 1 を設定します。
終わり
その間 ;
終わり;
区切り文字 ;

-- ストアド プロシージャを実行し、テスト データを挿入します。mysql> CALL idata ();

-- フィールド a のインデックスを使用して実行プランを表示します。mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+
| 1 | SIMPLE | t | 範囲 | a | a | 5 | NULL | 10000 | インデックス条件を使用 |
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+

-- フィールド b はソートする必要があるため、インデックス b はより多くの行をスキャンする必要がありますが、順序付けされます。スキャンする行数とソートを考慮して、オプティマイザはコストが小さいと判断し、インデックス b を選択します。mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+
| 1 | SIMPLE | t | range | a,b | b | 5 | NULL | 50128 | インデックス条件の使用; where の使用 |
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+

-- 解決策 1: force index を使用して、インデックス a を強制的に使用し、オプティマイザの誤った選択を修正します。これは推奨されません (普遍的ではなく、インデックス名が変更された場合はステートメントを変更する必要があります)。
mysql> explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 999 | インデックス条件の使用; where の使用; filesort の使用 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+

-- 解決策 2: MySQL が目的のインデックスを使用し、b、a でソートするように誘導します。オプティマイザは、a のソートのコストを考慮する必要があります。mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b、a limit 1;
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a,b | a | 5 | NULL | 999 | インデックス条件の使用; where の使用; filesort の使用 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+

-- 解決策3: シナリオによっては、より適切なインデックスを作成してオプティマイザに選択させるか、誤って使用されているインデックスALTER TABLE `t`を削除することができます。
インデックス「a」を削除します。
インデックス `b` を削除します。
インデックス `ab` (`a`,`b`) を追加します。

-- テーブル t のフィールド xxx の選択インデックス: select count(distinct xxx)/count(id) from t;

-- ID の値を確認するだけで済み、ID の値は既に k インデックス ツリーに含まれているため、テーブルに戻らずにクエリ結果を直接提供できます。select ID from T where k between 3 and 5

-- フィールド V を追加します。V は各クエリで返される必要があります。k と v を結合インデックスにすることを検討できます。select ID,V from T where k between 3 and 5

-- id、name、age の 3 つの列に結合インデックスを作成します。 -- 左端プレフィックスの原則を満たし、name と age の両方にインデックスが付けられます。select * from T where name='xxx' and age=12

-- Mysql は自動的に名前と年齢の順序を最適化して調整します。名前と年齢の両方がインデックス化されます。select * from T where age=12 and name='xxx'

-- name が左端のプレフィックスの原則を満たす場合、インデックスが使用されます。MySQL 5.6 では、インデックス条件プッシュダウン最適化が導入されました。つまり、age=12 を満たさないレコードは、まずインデックスでフィルタリングされ、その後テーブルに返されます。select * from T where name like 'xxx%' and age=12

-- 左端プレフィックスの原則を満たしていないため、インデックスは使用されません。select * from T where name like '%xxx%' and age=12

-- 左端のプレフィックス原則を満たし、name はインデックスを使用します。select * from T where name='xxx'

-- 左端プレフィックスの原則を満たしていないため、インデックスは使用されません。select * from T where age=12

mysql> テーブル SUser( を作成
ID bigint 符号なし主キー、
名前varchar(64)、  
電子メールvarchar(64)、
...
)エンジン=innodb;

-- 次のクエリ シナリオmysql> select name from SUser where email='xxx';

-- ソリューション 1: フルテキスト インデックス。返されるテーブルの数は、条件を満たすデータの量によって決まります。mysql> alter table SUser add index index1(email);

-- 解決策 2: プレフィックス インデックス。返されるテーブルの数は、プレフィックスの一致結果によって決まります。mysql> alter table SUser add index index2(email(6));

-- 許容可能な識別損失率を事前に設定し、条件を満たす最小のプレフィックス長を選択します。select count(distinct left(email,n))/count(distinct email) from SUser;

-- クエリ中の文字列反転クエリ mysql> select field_list from t where id_card = river('input_id_card_string');

-- ID カード検証コードを格納する整数フィールドを作成し、このフィールドにインデックスを作成します。mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

-- ハッシュ フィールドを使用してインデックスで検索し、元のフィールドの精度を使用してフィルタリングします。mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

-- プレフィックス インデックスを使用する場合、クエリ パフォーマンスを最適化するためにカバー インデックスは必要ありません。select id,email from SUser where email='xxx';