21 の MySQL 標準化および最適化のベストプラクティス!

序文

良い習慣はすべて宝物です。この記事は、SQL の後悔の治療法、SQL パフォーマンスの最適化、SQL 標準のエレガンスという 3 つの方向に分かれています。SQL を書くための 21 の良い習慣とベストプラクティスを紹介します。

SQLを書いた後、実行プランを表示する方法を説明します（SQLパフォーマンスの最適化）

日常の開発で SQL を書くときは、次の良い習慣を身につけるようにしてください。SQL を書いた後、explain を使用して分析し、インデックスが使用されているかどうかに特に注意してください。

削除または更新文を操作するときは制限を追加する（SQL後悔薬）

削除または更新ステートメントを実行するときは、制限を追加してみてください。次の SQL を例に挙げます。

euser から、年齢が 30 を超える場合は 200 を削除します。

制限を追加すると、次のような利点があります。

1. 間違った SQL を書くコストを削減します。この SQL をコマンドラインで実行するときに、制限を追加しないと、実行中に誤ってすべてのデータを削除する可能性があります。間違ったデータを削除してしまったらどうなるでしょうか? 200 という制限があると、状況は異なります。誤ってデータを削除した場合でも、失われるのは 200 件のレコードのみであり、binlog を通じてすぐに復元できます。
2. SQL の効率が高くなる可能性があります。SQL 行に制限 1 を追加すると、最初の行がターゲットにヒットした場合に制限なしでテーブルをスキャンし続けます。
3. 長いトランザクションを避けます。削除を実行すると、age がインデックス化されている場合、MySQL は関連するすべての行に書き込みロックとギャップロックを追加します。実行に関連するすべての行がロックされます。削除の数が多いと、関連する業務に直接影響し、使用できなくなります。
4. データ量が多いと、CPU がいっぱいになりやすくなります。レコード数に制限を加えずに大量のデータを削除すると、CPU がいっぱいになりやすく、削除がどんどん遅くなります。

テーブルを設計するときは、すべてのテーブルとフィールドに対応するコメントを追加します（SQL 標準およびエレガント）

この良い習慣を身につける必要があります。データベーステーブルを設計するときは、すべてのテーブルとフィールドに対応するコメントを追加して、後でメンテナンスしやすくします。

良い例:

テーブル「アカウント」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キーID',
 `name` varchar(255) DEFAULT NULL COMMENT 'アカウント名',
 `balance` int(11) デフォルト NULL コメント 'Balance',
 `create_time` datetime NOT NULL COMMENT '作成時刻',
 `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
 主キー (`id`)、
 キー `idx_name` (`name`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='アカウントテーブル';

反例:

テーブル「アカウント」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) デフォルト NULL,
 `balance` int(11) デフォルト NULL,
 `create_time` 日時 NOT NULL 、
 `update_time` datetime は UPDATE CURRENT_TIMESTAMP では NULL ではありません。
 主キー (`id`)、
 キー `idx_name` (`name`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=1570068 デフォルト CHARSET=utf8;

SQL の記述形式では、キーワードのサイズを一定に保ち、インデントを使用します。 (SQL は洗練されており、標準化されています)

良い例:

SELECT stu.name, sum(stu.score) FROM Student stu WHERE stu.classNo = 'Class 1' GROUP BY stu.name

反例:

Student stu から stu.name、sum(stu.score) を選択し、stu.classNo = 'Class 1' で stu.name でグループ化します。

当然ですが、キーワードの大文字と小文字を統一し、インデント配置を使用すると、SQL がよりエレガントに見えます。

INSERT文は対応するフィールド名を示します（SQL標準およびエレガント）

反例:

Student の値に ('666','Amateur Grass','100') を挿入します。

良い例:

insert into Student(student_id,name,score) values ('666','女生草','100');

SQL 変更操作は、詳細な操作手順とロールバック計画を使用して、まずテスト環境で実行し、本番環境の前に確認する必要があります。 (SQL 後悔薬)

構文エラーのある状態で本番環境に導入されないように、まずテスト環境で SQL の変更をテストします。
SQL 操作を変更する場合、特に最初にテーブル構造を変更してから対応するデータを追加するなど、依存関係がある場合は、詳細な操作手順を明記する必要があります。
SQL 変更操作にはロールバックプランがあり、対応する SQL 変更は本番環境の前に確認されます。

データベーステーブルを設計するときは、主キー、create_time、update_time の 3 つのフィールドを追加します。 (SQL 標準のエレガンス)

反例:

テーブル「アカウント」を作成します（
 `name` varchar(255) DEFAULT NULL COMMENT 'アカウント名',
 `balance` int(11) デフォルト NULL コメント 'Balance',
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='アカウントテーブル';

良い例:

テーブル「アカウント」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キーID',
 `name` varchar(255) DEFAULT NULL COMMENT 'アカウント名',
 `balance` int(11) デフォルト NULL コメント 'Balance',
 `create_time` datetime NOT NULL COMMENT '作成時刻',
 `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
 主キー (`id`)、
 キー `idx_name` (`name`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='アカウントテーブル';

理由：

1. 主キーを追加する必要があります。主キーのないテーブルは無意味です。
2. 作成時間と更新時間については、追加することをお勧めします。詳細な監査と追跡記録が役立ちます。

アリババ開発マニュアルにもこの点について言及されており、図に示されている。

SQL 文を記述した後、where、order by、group by の後の列を確認し、複数のテーブルに関連する列にインデックスが付けられているかどうかを確認し、結合されたインデックスを優先します。 (SQLパフォーマンスの最適化)

反例:

良い例:

-- インデックスを追加します alter table user add index idx_address_age (address, age)

重要なデータを変更または削除する前に、まずバックアップしてください。まずバックアップしてください。まずバックアップしてください。（SQL 後悔薬）

データを変更または削除する場合は、SQL を実行する前に変更するデータをバックアップする必要があります。誤った操作をした場合、後で後悔する可能性があります。

where に続くフィールドのデータ型の暗黙的な変換に注意してください (SQL パフォーマンスの最適化)

反例:

//userid は varchar 文字列型です。select * from user where userid =123;

良い例:

userid = '123' のユーザーから * を選択します。

理由：

一重引用符が追加されていない場合、比較は文字列と数値の間で行われ、それらの型が一致しません。MySQL は暗黙的な型変換を実行し、比較する前にそれらを浮動小数点数に変換します。これにより、最終的にインデックスが無効になります。

すべての列を NOT NULL として定義するようにしてください (SQL 標準のエレガンス)

NOT NULL 列はより多くのスペースを節約しますが、NULL 列は NULL かどうかを判断するためのフラグとして追加のバイトを必要とします。
NULL 列は、NULL ポインター問題に注意する必要があります。NULL 列を計算して比較するときは、NULL ポインター問題に注意する必要があります。

SQLを修正・削除するには、まずWHEREを書いて確認し、確認後にdeleteやupdateを追加する（SQL後悔薬）
特に本番データの操作時に、変更や削除のSQLに遭遇した場合は、まずwhereクエリを追加し、OKを確認した後、更新または削除操作を実行してください。

select * の代わりに select <特定のフィールド> を使用するなど、不要なフィールドの戻り値を減らす (SQL パフォーマンスの最適化)

反例:

従業員から*を選択します。

良い例:

従業員からID、名前を選択します。

理由：

リソースを節約し、ネットワークのオーバーヘッドを削減します。
カバーリングインデックスを使用すると、テーブルの戻り値を減らし、クエリの効率を向上させることができます。

すべてのテーブルは Innodb ストレージエンジンを使用する必要があります (SQL 標準エレガンス)

Innodb は、トランザクション、行レベルのロック、優れた回復性、高同時実行性における優れたパフォーマンスをサポートします。したがって、特別な要件 (列ストレージ、ストレージスペースデータなど、Innodb が満たせない機能) がない限り、すべてのテーブルで Innodb ストレージエンジンを使用する必要があります。

データベースとテーブルの文字セットはUTF8（SQL標準エレガント）を使用するように統一されています。

UTF8エンコードを統一的に使用する

文字化けしたコードの問題を回避できる
これにより、異なる文字セットの比較と変換によって発生するインデックス無効化の問題を回避できます。

式を保存する場合は、utf8mb4 を検討できます。

char の代わりに varchar を使用するようにしてください。 (SQLパフォーマンスの最適化)

反例:

`deptName` char(100) デフォルト NULL コメント '部門名'

良い例:

`deptName` varchar(100) デフォルト NULL コメント '部門名'

理由：

まず、可変長フィールドは占有するストレージスペースが少なくなるため、ストレージスペースを節約できます。

フィールドの意味を変更したり、フィールドに追加のステータスを追加したりする場合は、フィールド注釈を適時に更新する必要があります。 (SQL は洗練されており、標準化されています)
この点は、Alibaba 開発マニュアルの Mysql 仕様です。フィールド、特に列挙状態を表すフィールドは、意味が変更されたり、後でメンテナンスしやすくするために状態が追加されたりした場合に、すぐに注釈を更新する必要があります。

SQL はデータを変更し、トランザクションの開始 + コミットの習慣を身につけます (SQL 後悔薬)

良い例:

begin;update account set balance =1000000where name ='女生草';commit;

反例:

アカウントを更新し、balance = 1000000 に設定し、name = 'Amateur Grass' に設定します。

インデックスの命名は標準化する必要があります。主キーのインデックス名は pk_field name、一意のインデックス名は uk_field name、共通インデックス名は idx_field name です。 (SQL は洗練されており、標準化されています)
注: pk_ は主キー、uk_ は一意キー、idx_ はインデックスの略語です。

WHERE句の列に対して関数変換や式計算は実行されません。

loginTime がインデックス化されていると想定します。

反例:

Date_ADD(loginTime,Interval 7 DAY) >=now() の場合、loginuser から userId、loginTime を選択します。

良い例:

loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY); の場合、 loginuser から userId、loginTime を選択することを説明します。

変更または更新するデータが多すぎる場合は、バッチで実行することを検討してください。

反例:

アカウント制限 100000 から削除します。

良い例:

それぞれ(200回){ アカウント制限500から削除;}

理由：

大規模なバッチ操作では、マスタースレーブ間の遅延が発生します。
この操作により、大規模なトランザクションとブロックが生成されます。
データ量が多すぎると、CPU がフルに利用されます。

上記の内容が読者のプログラミング学習に役立つことを願っています。

MySQL の標準化と最適化に関する 21 のベストプラクティスに関するこの記事は以上です。この記事はこれで終わりです。MySQL標準の最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。

以下もご興味があるかもしれません: