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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • Mysql インデックスが失敗するいくつかの状況の分析
  • MySQL のインデックス障害の一般的なシナリオと回避方法
  • MySQL でデータベース インデックスが失敗する状況の詳細な分析
  • mysql はインデックスを無効にしますか?
  • MySQL インデックス障害の 5 つの状況の分析
  • MySQLのあいまいクエリインデックスの失敗の問題を解決するいくつかの方法
  • MySQL インデックスが失敗するいくつかの状況の概要
  • MySQLインデックスが失敗するいくつかの状況の詳細な分析
  • MySQLインデックスが失敗するいくつかの状況の分析
  • MySQL データベースのインデックスと障害シナリオの詳細な説明

<<:  WebpackはTypeScriptコードをパッケージ化するためのスキャフォールディングを構築します

>>:  docker compose idea CreateProcess error=2 システムは指定されたファイルを見つけることができません

推薦する

MySQL で不明なフィールド名を回避する方法

序文この記事では、DDCTF の 5 番目の質問、つまり不明なフィールド名をバイパスする手法を紹介し...

フロントエンドAIカットのコツ(体験談)

AI 画像の切り取りは PS と連携する必要があります。まず、スライスするレイヤーを選択し、それを...

Linux コマンドで .sql ファイルをエクスポートおよびインポートする方法

この記事では、Linux コマンドを使用して .sql ファイルをエクスポートおよびインポートする方...

MySQLデータ行と行オーバーフローのメカニズムの詳細な説明

1. 行の形式は何ですか? MySQL の行形式の設定は次のように表示されます。 実際、MySQL ...

MySQLデータベースでサポートされているストレージエンジンの比較

目次ストレージエンジンMySQL でサポートされているストレージ エンジン同時実行制御ロック粒子をロ...

シンプルな画像ドラッグ効果を実現する js

この記事では、簡単な画像ドラッグ効果を実現するためのjsの具体的なコードを参考までに紹介します。具体...

Vue3 (V) HTTPライブラリaxiosの統合の詳細

目次1. axiosをインストールする2. アクシオスの使用1.ホームページでaxiosを参照する2...

ウェブサイト製品設計の参考となるいくつかの原則

以下の分析は製品設計原則に関するものですが、そのほとんどはウェブサイト製品に基づいているため、ユーザ...

Linux環境にMySQLデータベースをインストールする詳細なチュートリアル

1. データベースをインストールする1) yum -y install mysql-server (...

nginxワーカープロセスループの実装

ワーカープロセスは、起動されると、まず自身の動作に必要な環境を初期化し、次に実行する必要があるイベン...

経験豊富な人が、プロフェッショナルで標準化されたMySQL起動スクリプトの開発方法を紹介します。

シェル スクリプト言語は、すべてのプログラミング言語の中で最も単純な言語であるため、資格のある Li...

今日は、珍しいけれど役に立つJSテクニックをいくつか紹介します

1. 戻るボタンhistory.back() を使用してブラウザの「戻る」ボタンを作成します。 &l...

MySQL 8の新機能ウィンドウ関数の役割

MySQL 8.0 の新機能は次のとおりです。 Unicode 9.0 をすぐに完全にサポートウィン...

MySQL 5.7.21 解凍バージョンのインストールと設定のグラフィックチュートリアル

この記事では、MySQL 5.7.21の解凍版をダウンロードしてインストールする詳細な手順を記録して...

Linux jdk のインストールと環境変数の設定チュートリアル (jdk-8u144-linux-x64.tar.gz)

最初にsudo suコマンドを使用して root アカウントに切り替えることをお勧めします。そうしな...