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 システムは指定されたファイルを見つけることができません

推薦する

アコーディオンセカンダリメニューを実装するためのjQueryプラグイン

この記事では、jQueryプラグインを使用してアコーディオンセカンダリメニューを作成します。具体的な...

JS 開発効率を上げる4つの超実践的なヒント

目次1. 短絡判定2. オプション連鎖演算子 (?) 3. ヌル合体演算子 (??) 4. 終了関数...

div の水平レイアウトを両側に揃える 3 つの方法

この記事では、主に、div の水平レイアウトの両側の配置を実装する 3 つの方法を紹介し、それらを共...

Linux で最も頻繁に使用されるターミナル コマンドのトップ 10 のリストを取得します。

私が最も頻繁に使用するコマンドは次の通りです:選択肢CDギットls ssh須藤数週間前、私はこの R...

挿入前にレコードが既に存在するかどうかを確認するには、SQL ステートメントを使用します。

目次SQL文を挿入する前にレコードが既に存在するかどうかを確認するSQL挿入時の判断の簡単なコレクシ...

MySQLチュートリアルDMLデータ操作言語の例の詳細な説明

目次1. データ操作言語 (DML) 2. データを追加する(挿入) 3. 既存のテーブルをコピーし...

Mac に Windows サービスを備えた仮想マシンをインストールする方法

1. 仮想マシンをダウンロードする公式ダウンロードウェブサイト: https://www.vmwar...

MySQL 5.7 における基本的な JSON 操作ガイド

序文プロジェクトのニーズにより、ストレージ フィールドは JSON 形式で保存されます。プロジェクト...

CSS で要素を垂直方向に中央揃えする 7 つの方法

【1】中央の要素の幅と高さを知る絶対値 + 負のマージンコードの実装 .wrapBox5{ 幅: 3...

CSS3 のフィルタプロパティの使用に関する詳細な説明

最近、イントラネットポータルを修正していたときに、フィルターを使用する必要がある箇所に遭遇しました。...

InnoDB の主な機能 - 挿入キャッシュ、2 度書き込み、適応ハッシュ インデックスの詳細

InnoDB ストレージ エンジンの主な機能には、挿入バッファ、二重書き込み、適応ハッシュインデック...

Zabbixのインストールと展開の詳細な説明

序文Zabbix は最も主流のオープンソース監視ソリューションの 1 つです。導入自体は難しくありま...

JavaScript 配列重複排除ソリューション

目次方法1: set: データ型ではなくデータ構造であり、メンバーは一意である方法2: オブジェクト...

Ubuntu 16.04 で PostgreSQL の起動を設定する方法

PostgreSQL はコンパイルされインストールされるため、起動時に起動するように設定する必要があ...

重複したMySQLレコードを現場でチェックし、処理する実践的な記録

目次序文分析するデータ合計繰り返し率どこにあるかと持っているかの違い要約する序文私はソフトウェアの導...