MySQL Advanced Learning: 効率的かつ適切なインデックスを作成する方法の詳細な説明

この記事は MySQL の高度な学習です。より適切なインデックスを作成する方法について詳しく説明します。お役に立てば幸いです。

ライブラリにデータが増えたときのインデックス作成の重要性がわかりません。ましてや、ライブラリにデータが増えたときの適切なインデックスもわかりません。インデックスの重要性。この記事では、効率的かつ適切なインデックスを作成する方法を紹介します。 [関連する推奨事項: mysql ビデオ チュートリアル ]

1. クエリにインデックス列を使用する場合は、式を使用しないようにし、データベース レイヤーではなくビジネス レイヤーに計算を配置するようにしてください。

以下に示すように、2 つの SQL の結果は同じですが、2 つの SQL の実行計画は異なります。型のインデックスの効率は、const where 条件のactor_id 4 の効率よりもはるかに低くなります。式実行計画と型表現の意味に影響を与えます詳細な説明については

explain を参照してください

2. 他のクエリの代わりに主キー クエリを使用してみてください。主キー クエリによってテーブルが返されるクエリは発生しません。

基本的にすべてのテーブルには主キーがあるため、通常の開発では、使用できる場合はインデックスを使用し、使用できる場合は主キー インデックスを使用します。

3. 接頭辞インデックスを使用する

多くの場合、インデックスは実際には文字列であり、必然的に長い文字列が表示されるため、インデックスが多くのスペースを占有し、効率が低下します。特に、BLOB、テキスト、VARCHAR などの長い列の場合に適しています。このときの対処方法は、フィールドの全値をインデックスとして使用するのではなく、前半部分のみを取得することです（選択されたプレフィックスインデックスの選択性が列全体に近い）。これによりインデックス領域が大幅に削減され、効率が向上しますが、インデックスの選択性が低下するという欠点があります。

インデックス選択性: データ テーブル レコードの総数 (#T) に対する一意のインデックス値の比率 (1/#T ～ 1)。インデックスの選択性が高くなると、データが高度に区別され、より多くの行をフィルターで除外できるため、クエリ効率が高くなります。ユニークインデックスの選択性は 1 であり、そのパフォーマンスは最高です。

たとえば、会社の従業員テーブルの電子メール フィールドでは、会社の電子メール サフィックスは xxxx@qq.com のように同じです。電子メールをインデックスとして使用する唯一の効果的な方法は、xxxx の部分です。@qq.com が同じであるため、インデックスとしては意味がありません。明らかに、xxxx のみがインデックスとして使用され、その選択性は値全体と同じです。ただし、xxxx をインデックスとして使用すると、明らかにインデックスのスペースが削減されます。

以下では例として従業員テーブルを使用します (テーブルの構造とデータについては記事の最後を参照)

電子メール フィールドを例としてインデックスを作成します。

このデータのメールアドレスは実際には携帯電話番号です

@qq.com これを例に挙げると、実は最初の11桁とその後の桁はすべて同じです。次の SQL を使用して、これらのデータの選択性の計算を確認します (最初の 10、11、12 をそれぞれ取得します)。

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

上の図から、上位 10、上位 11、および上位 12 の選択性がそれぞれ 0.14、1.0、1.0 であり、インデックスの選択性が次のとおりであることがわかります。 11 番目の位置が最高の 1 なので、すべてをインデックスとして使用する必要がなく、インデックスのスペースが増加します。

-- 创建前缀索引
alter table employee add key(email(11));

count を使用して統計の頻度を計算することもできます (出現回数が少ないほど、繰り返し率が低くなり、選択性が高くなります)

-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;

4. インデックス スキャンを使用して並べ替えます。

並べ替えや並べ替えを使用する必要があることがよくありますが、並べ替えはパフォーマンスにさらに影響します。データ量が大きい場合は、データをメモリにロードして並べ替えます。メモリに保存され、複数のバッチでのみ処理できます。ただし、インデックス自体は順序付けされているため、インデックスを直接使用して並べ替えを完了する方が簡単です。

インデックスのスキャン自体は、1 つのインデックス レコードから次のレコードに移動するだけなので高速ですが、インデックスがクエリに必要なすべての列をカバーできない場合は、毎回インデックスをスキャンする必要があります。レコードがテーブルに返され、対応する行が 1 回クエリされます。これは基本的にランダム IO です。したがって、インデックス順でのデータの読み取りは、通常、連続した全テーブル スキャンよりも遅くなります。

Mysql は、ソートと行の検索に同じインデックスを使用できます。可能であれば、そのようなインデックスの作成を検討してください。

インデックス列の順序が order by 句の順序と完全に一致し、すべての列のソート方向 (後方または前方) が同じ場合にのみ、mysql はインデックスを使用できます。結果を並べ替えます。クエリを複数のテーブルに関連付ける必要がある場合、インデックス ソートは、order by 句のフィールドがすべて最初のテーブルのものである場合にのみ使用できます。クエリによる順序付けは、結合されたインデックスの左端のプレフィックスを満たす必要もあります。そうでない場合、インデックスの並べ替えは使用できません。

実は、開発中に注意すべき主な点が 2 つあります。

• where 条件のフィールドと order by のフィールドは、インデックスを結合して、左端のプレフィックスを満たすことができます。
• order by のフィールドの順序は一貫している必要があります。Desc と asc は存在できません。

5. Union all、in、またはインデックスを使用できますが、in を使用することをお勧めします

上記のように、union all は次のようになります。そして、and または or は 1 回だけ実行されます。同時に、 or と in の実行計画は同じであることがわかります。\

ただし、それらの実行時間を調べています。以下に示すように、詳細な時間を表示するには set profiling=1 を使用し、特定の時間を表示するには show profiles を使用します。次の図は、 or の時間が 0.00612000 で、 in の時間が 0.00022800 であることを示しています。ギャップは依然として非常に大きいです (テスト テーブル データには 200 行しかありません)

union all: クエリ 2 つの段階に分かれています。実際には、ユニオンもあります。日常の開発でユニオンを使用する必要がある場合は、ユニオンには個別の重複排除という追加のステップがあるため、ユニオン オールを使用することをお勧めします。したがって、union all を使用してみてください。

6. 範囲列ではインデックス

範囲条件を使用できます:>,>=,<,<=,between

範囲列インデックスは使用できますが、範囲列に続く列ではインデックスを使用できません (インデックスは最大 1 つの範囲列に使用できます)

たとえば、クエリ条件が where の場合、結合インデックス年齢名age>18 および name="Ji" の後の名前はインデックス作成に使用されません。

面接で「ある指標に従うことができるということですか？」と聞かれたことがありますが、これまで気にしたこともうまく答えたこともありませんでした。今回は自分で実験してみました。結論としては記事の最後。

7. 強制型変換はテーブル全体をスキャンします

従業員テーブルに定義しましたmobileフィールドは varchar 型でインデックスが付けられています。

#結果を見てください: 2 つのタイプは異なり、文字列のみがインデックスを使用します。

条件の値の型がテーブルで定義されている型と一致しない場合、mysql は型変換を強制しますが、結果にはインデックスが付けられません。インデックスの開発中に、次のことを行う必要があります。独自に定義した型に従って、対応する型を入力します。

8. データの区別性は高くなく、頻繁に更新されるフィールドはインデックス付けには適していません。

インデックス列の更新により変更されますB ツリーは頻繁に更新されるため、データベースのパフォーマンスが大幅に低下します。
性別 (男性と女性のみ、または不明) と同様に、データを効果的にフィルタリングすることはできません。
通常、区別が 80% を超えた場合にインデックスを作成できます。区別の場合は、count(distinct(列名))/count(*)

9 となります。インデックス列 Null は許可されず、予期しない結果が得られる可能性があります

つまり、インデックス付けされたフィールドはできる限り空にすべきではありません。予期しない問題が発生する可能性がありますが、実際の作業では、そのようなことは考えられません。空にはならないので、実際のビジネスに応じて処理し、このような状況を回避するようにしてください。

10. テーブル接続が必要な場合、テーブルの数が 3 つを超えないようにするのが最善です。

テーブル接続は実際には複数のテーブルの循環ネストされたマッチングであり、パフォーマンスに影響し、結合が必要です。クエリの効率を向上させるには、フィールドのデータ型に一貫性がある必要があります。テーブル接続の原理については後ほど特集記事を書きましょう。

11. 可能な場合は制限を使用するようにしてください。

limit の機能は単なるページングではなく、その本質的な機能は出力を制限することです。

limit は、実際にはクエリ データを 1 つずつ走査します。

limit 1 の制限を追加するために 1 つのデータだけが必要な場合、インデックス ポインタは、条件を満たすデータを見つけた後に停止します。はい、そのままリターンしてください。制限がない場合は判定が続行されます。

ただし、10,000 項目の後にページングして 5 つの項目を取得する場合 (

limit 10000,10005)、10,000 項目を走査して 5 つの項目を取得することになり、非常に非効率になります。ヒント: 主キーがシーケンシャルの場合は、主キーを介してページング データを直接取得できます。

12. 単一テーブルのインデックスを 5 以内に制御するようにしてください

インデックスの構築/維持にはコストがかかり、スペースも消費します。インデックスは多ければ多いほど良いため、インデックスは合理的に使用する必要があります。

13. 単一の結合インデックス内のフィールド数は 5 を超えてはなりません

フィールドの数が増えると、インデックスが大きくなり、占有するストレージ領域も増えます。

インデックスは多ければ多いほど良いため、テーブルの構築を開始するときにインデックスを設計する必要はありません。時期尚早に最適化すると効率的なインデックスにはなりません。ビジネスを理解し、以下に基づいて行う必要があります。関連するインデックスを構築する前に、ビジネス SQL を統計的に評価する必要があります。これにより、考慮事項がより包括的になり、確立されたインデックスがより効果的かつ効率的になります。

上記はインデックスの最適化に対応する細かい詳細です。スムーズな SQL を書くのに役立つことを願っています。

補足

---

インデックスするかどうかの問題について

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

以上がMySQL Advanced Learning: 効率的かつ適切なインデックスを作成する方法の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。