MySQL8.0複合インデックスのスキャン範囲をスキップしてアクセスする方法

7139 ワード
MySQL mysql
MySQL8.0スキャン範囲をスキップしてアクセスできる複合インデックスがありますが、どういう意味ですか?簡単に言えば、複合インデックス(a,b,c)については、通常「where a=xx and b=xx」はインデックスに使用できるが、「where b=xx and c=xx」はインデックスには使用できない.しかしMySQL 8.0では、後者もインデックスに使用できます.
テスト表を作成しました.そのテストデータは104万で、以下の通りです.
root@localhost|test>select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

root@localhost|test>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dept` tinyint DEFAULT NULL comment '  ID,1~10',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL comment '  ',
  `create_time` datetime DEFAULT NULL comment '    ',
  PRIMARY KEY (`id`),
  KEY `idx_muti` (`create_time`,`name`,`dept`)
) ENGINE=InnoDB AUTO_INCREMENT=1441756 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

root@localhost|test>select count(1) from t;
+----------+
| count(1) |
+----------+
|  1048576 |
+----------+
1 row in set (0.11 sec)

このテーブルには複合インデックスidx_が表示されます.muti(create_time,name,dept)では、インデックスが使用できるかどうかをdeptフィールドで直接調べてみましょう.
root@localhost|test>explain select count(1) from t  where dept=5;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_muti      | idx_muti | 131     | NULL | 1045864 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost|test>select count(1) from t  where dept=5;        
+----------+
| count(1) |
+----------+
|   105271 |
+----------+
1 row in set (1.16 sec)

実行計画から複合インデックス(a,b,c)に対して、私たちはもう(a,b)を前提にcを使う必要はありません.本当に便利になりました.
ゆっくりして、これは本当に実行する時間が少し間違っていると感じて、私たちはインデックスを歩かないで見てみましょう
root@localhost|test>explain select count(1) from t ignore index(idx_muti) where dept=5;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1045864 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost|test>select count(1) from t ignore index(idx_muti) where dept=5;         
+----------+
| count(1) |
+----------+
|   105271 |
+----------+
1 row in set (0.70 sec)

フルテーブルスキャンの消費時間は、インデックスを使用するよりも少ないのはinnodbのキャッシュのためですか?
私たちはcnfファイルに「innodb_buffer_pool_load_at_startup=0;および「innodb_buffer_pool_dump_at_shutdown=0;2つのパラメータを再起動してMySQLを再起動し、上記の全テーブルスキャンのSQLを実行すると、インデックスを使用するよりも常に0.7秒程度実行されていることがわかります.
官档を开くと、官档の大体の意味は、(a,b)の复合インデックスに対して、「where b>xxx」のクエリ条件があれば、まずaフィールドの异なる値を取得し、それから异なる値によって「where a=xx and b>xxx」を构筑し、aの异なる値を构筑して実行することである.
次の斜体部分は官档から来ています.
 
 
スキャン範囲のアクセス方法をスキップ
次の状況を考慮してください.
CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

このクエリを実行するために、MySQLはインデックススキャンを選択してすべてのロー(インデックスには選択するすべてのカラムが含まれます)を取得し、文のf2 > 40条件WHEREを適用して最終結果セットを生成します.
範囲スキャンはフルインデックススキャンよりも有効ですが、f1は最初のインデックス列に条件がないため、この場合は使用できません.しかし、MySQLの8.0のように.13において、オプティマイザは、1つの値f1のための複数の範囲スキャンを実行することができ、1つのジャンプスキャンと呼ばれる方法を使用することは、分散インデックススキャンと同様である(セクション8.2.1.17、「GROUP BYの最適化」を参照).
  • は、第1のインデックス部分の異なる値f1(インデックスプレフィックス)の間でスキップされる.
  • f2 > 40は、残りのインデックス部分の条件について、異なるプレフィックス値ごとにサブレンジスキャンを実行する.

    • 前に表示されたデータセットの場合、アルゴリズムの実行方法は次のとおりです.
  • は、第1のキー部分の第1の異なる値(f1 = 1)を取得する.
  • は、第1および第2のキー部分(f1 = 1 AND f2 > 40)に従って範囲を構成する.
  • レンジスキャンを実行します.
  • 最初のキーの次の異なる値(f1 = 2)を取得します.
  • は、第1および第2のキー部分(f1 = 2 AND f2 > 40)に従って範囲を構成する.
  • レンジスキャンを実行します.

    • このポリシーを使用すると、MySQLは各コンストラクションの範囲に合わないローをスキップするため、アクセスするローの数を減らすことができます.このスキップ・スキャン・アクセス・メソッドは、次の場合に適用されます.
  • 表Tは、少なくとも1つの複合インデックスを有しており、そのキーの形式は([A_1,...,A_k,]B_1,...,B_m,C[,D_1,...,D_n])である.キーAおよびDは空である可能性があるが、BおよびCは空でなければならない.
  • クエリは1つのテーブルのみを参照します.
  • クエリは、GROUP BYまたはDISTINCTを使用しません.
  • クエリは、インデックス内のカラムのみを参照します.
  • A_1,...,A_上の述語kは、等しい述語であり、定数でなければならない.これは、IN()オペレータを含む.
  • クエリーは、統合クエリーである必要があります.すなわち、ANDのOR条件:(cond1(key_part1)OR cond2(key_part1))AND(cond1(key_part2)OR…)AND ...
  • Cには が です.
  • D の は されています.D の はC の と み わせて しなければならない.

    • プロファイルの から、このようなスキップスキャン アクセス は、 インデックス(a,b)に され、aのインデックス が さい(すなわち、aフィールドの の が じ、 えば 、 、タイプ、 など) 、インデックス が きい 、 テーブルスキャンは、このスキップスキャン アクセスよりも である がある.
    に, インデックス(a,b)において,aのインデックス が さい を する.
    にMySQLを します
    #       
root@localhost|test>drop index idx_muti on t;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#      ， dept        。
root@localhost|test>create index idx_muti on t(dept, create_time);
Query OK, 0 rows affected (4.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost|test>explain select count(1) from t where create_time>='2020-01-01' and create_timeselect count(1) from t where create_time>='2020-01-01' and create_timesystem systemctl restart mysql 

#           
root@localhost|test>explain select count(1) from t ignore index(idx_muti) where create_time>='2020-01-01' and create_timeselect count(1) from t ignore index(idx_muti) where create_time>='2020-01-01' and create_time

    インデックスの が していることがわかります.したがって、 しいプロパティを する は、 に してください.そうしないと、 になる があります.
     
    :https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan
    C言語——関数
    JWTの概要とRubyにおける使い方のメモ