MySQL インデックスの失敗を引き起こす一般的な書き込み方法の概要

序文

最近、古いプロジェクトから残ったいくつかの SQL 最適化の問題に対処するのに忙しくしています。元のテーブル設計とフィールド設計の問題により、ビジネスが成長するにつれて、大量の遅い SQL が発生し、MySQL の CPU リソースが急増しました。これに基づいて、比較的実用的で簡単に習得して使用できるこれらの経験を簡単に共有したいと思います。

今回は、インデックスが無効にならないようにする方法について簡単に説明します。

話を進める前に、まずは最近の経験に基づいたインデックスに関する私の見解を共有させてください。すべてのテーブルにインデックスを付ける必要はないと思います。ビジネス データによっては、ボリュームが大きく、データのクエリが少しストレスになる場合があります。この場合、最も簡単で迅速な方法は、適切なインデックスを作成することです。ただし、ビジネスによっては、テーブルにそれほど多くのデータが含まれていない場合や、テーブルがあまり頻繁に使用されない場合は、インデックスを作成する必要はありません。たとえば、一部のテーブルには 2 年間で約 10 個のデータしか含まれていない場合があり、インデックスの有無によるパフォーマンスはほぼ同じです。

インデックス作成は、ビジネスを最適化するための手段にすぎません。インデックス作成のためだけにインデックスを作成すべきではありません。

以下は、このテストで使用したテーブル構造といくつかのテストデータです。

テーブル `user` を作成します (
 `id` int(5) 符号なし NOT NULL AUTO_INCREMENT,
 `create_time` 日時 NOT NULL、
 `name` varchar(5) NOT NULL,
 `age` tinyint(2) 符号なしゼロフィル NOT NULL,
 `sex` char(1) NOT NULL,
 `mobile` char(12) NOT NULL デフォルト ''
 `address` char(120) デフォルト NULL,
 `height` varchar(10) デフォルト NULL,
 主キー (`id`)、
 キー `idx_createtime` (`create_time`) BTREE 使用、
 キー `idx_name_age_sex` (`name`,`sex`,`age`) BTREE 使用、
 キー `idx_ height` (`height`) BTREE 使用、
 キー `idx_address` (`address`) BTREE 使用、
 キー `idx_age` (`age`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=261 デフォルト CHARSET=utf8;

: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :

単一インデックス

1. != または <> を使用するとインデックスが失敗します

SELECT * FROM `user` WHERE `name` != 'Bingfeng';

name フィールドにインデックスを作成しましたが、!= または <> を使用するとインデックスが失敗し、テーブル全体のスキャンが実行されます。そのため、データ量が多い場合は注意して使用してください。

SQL を分析すると、タイプが ALL であり、10 行のデータがスキャンされ、完全なテーブルスキャンが実行されていることがわかります。 <>でも同じ結果になります。

2. 不一致な型によるインデックス障害

これについて話す前に、テーブル フィールドを設計するときには、フィールド タイプの一貫性を必ず維持する必要があることを述べておかなければなりません。これはどういう意味でしょうか。たとえば、ユーザー テーブルの id が int で自動増分である場合、ユーザーのアカウント テーブルの user_id フィールドも int 型である必要があります。varchar、char などとして記述しないでください。

SELECT * FROM `user` WHERE height= 175;

このSQLは注意深く読む必要があります。高さテーブルのフィールド型はvarcharですが、クエリ時に数値型を使用しました。途中で暗黙的な型変換があるため、インデックスが無効になり、テーブル全体のスキャンが実行されます。

これで、フィールドを設計するときに型の一貫性を維持する必要があると述べた理由がお分かりいただけたと思います。一貫性を確保しないと、複数テーブルの結合クエリを実行するときに、int と varchar は必然的にインデックスを使用できなくなります (例: 1 = '1')。

何千万ものデータが含まれており、変更できないこのようなテーブルに遭遇すると、やはり苦痛を感じるかもしれません。

若い人たちよ、忘れないで、忘れないで。

3. 関数によるインデックス障害

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

インデックス フィールドでインデックスが使用されている場合、残念ながら、実際にはインデックスは使用されません。

4. 演算子によるインデックスの失敗

SELECT * FROM `user` WHERE age - 1 = 20;

列に対して (+, -, *, /, !) を実行すると、インデックスは使用されません。

5. ORによるインデックス障害

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

OR は特定の状況でインデックスを作成します。すべての OR がインデックスを無効にするわけではありません。OR が同じフィールドを接続する場合、インデックスは無効になりません。それ以外の場合は、インデックスは無効になります。

6. あいまい検索によるインデックスの失敗

SELECT * FROM `user` WHERE `name` LIKE '%冰';

これは皆さんも理解していると思います。プレフィックスに対してあいまい検索を実行すると、インデックスは使用されません。

7. NOT INとNOT EXISTSはインデックスエラーを引き起こす

SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = '冰峰')

SELECT * FROM `user` WHERE `name` NOT IN ('Bingfeng');

これら 2 つの使用法でもインデックスが無効になります。ただし、NOT IN は依然としてインデックスを使用します。IN はインデックスをまったく使用しないと誤解しないでください。以前も誤解したことがありました（恥ずかしいですね…）。

8. IS NULLはインデックスを使用しませんが、IS NOT NULLはインデックスを使用します。

SELECT * FROM `user` WHERE address IS NULL 

インデックスに従わないでください。

SELECT * FROM `user` WHERE address IS NOT NULL; 

インデックスを歩きます。

このような状況を踏まえると、フィールドを設計する際に、必ずしも NULL である必要がない場合は、デフォルト値として空の文字列を指定することが推奨されます。これにより、後続のトラブルの多くを解決できます (深い経験 <経験=教訓>)。

マッチインデックス

1. 左端一致原則

EXPLAIN SELECT * FROM `user` WHERE sex = '男';

EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';

テストする前に、他の単一列インデックスを削除します。

左端一致原則とは何ですか? これは、一致するインデックスの場合、インデックスの順序が左から右に比較されることを意味します。たとえば、2 番目のクエリ ステートメントでは、インデックスを介して名前が検索され、次に年齢が検索されます。年齢が結果条件にない場合、後続の性別はインデックスを介して検索されません。

知らせ：

SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = '冰峰';

おそらく、一部の職人は、最初は私のことを誤解しているかもしれません。私たちのインデックスの順序は明らかに名前、性別、年齢ですが、あなたの現在のクエリの順序は性別、年齢、名前です。これは明らかにインデックスに従っていません。自分でテストしたことがなく、そのような未熟な考えを持っているのであれば、あなたは私と同じように若すぎます。実際には順序とは何の関係もありません。なぜなら、MySQL の基盤となるレイヤーが最適化に役立つからです。SQL を最適化して、最も効率的な方法で実行します。ですから、誤解しないでください。

2. != を使用すると、後続のインデックスはすべて無効になります。

SELECT * FROM `user` WHERE sex = '男' AND `name` != '冰峰' AND age = 22;

名前フィールドで != を使用しました。名前フィールドは左端のフィールドであるため、左端一致の原則に従って、名前フィールドがインデックス化されていない場合は、次のフィールドもインデックス化されません。

インデックスのマッチングがインデックスの無効化につながる状況は、現在 2 つしかありません。実際、インデックスのマッチングで重要なのは、いかに効率的なインデックスを確立するかだと考えています。フィールドを使用する場合は、別のインデックスを作成してグローバルに使用する、などと言ってはなりません。これは可能ですが、インデックスの効率性の要件を満たしていません。したがって、上級のブリックレイヤーになるためには、効率的なインデックスを作成する方法を引き続き学習する必要があります。

要約する

これで、MySQL インデックスの失敗を引き起こす一般的な書き込み方法についての記事は終了です。MySQL インデックスの失敗を引き起こす一般的な書き込み方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。