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

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

以下もご興味があるかもしれません:
  • MySQL の低速クエリの最適化: 理論と実践からの制限の利点
  • MySQLスタンドアロンデータベースの最適化のいくつかの実践
  • 収集する価値のある 21 の MySQL 最適化プラクティスの詳細な要約

<<:  Javascript Echarts 空気質マップ効果の詳細な説明

>>:  Dockerイメージの作成Dockerfileとコミット操作

推薦する

JavaScriptは双方向リンクリストプロセス分析を実装します

目次1. 二重連結リストとは何か2. 双方向リンクリストのカプセル化3. 双方向リンクリストの一般的...

MySQL トランザクション分離レベルとロックメカニズムの問題に関する深い理解

概要データベースは通常、複数のトランザクションを同時に実行します。複数のトランザクションが、同じデー...

MySQLカバーインデックスの利点

一般的な提案は、WHERE 条件のインデックスを作成することですが、これは実際には一方的です。インデ...

Vueモバイル端末は左スライド編集と削除の全プロセスを実現します

序文プロジェクトのニーズに応じて、Vue-touch を使用して、vue モバイル端末の左スワイプ編...

固定サイドバーを実現するためのJavaScript

固定サイドバーを実装するにはJavaScriptを使用します。参考までに、具体的な内容は次のとおりで...

コンポーネントベースのフロントエンド開発プロセスの詳細な説明

背景<br />フロントエンドを担当する学生は、ページが多すぎると煩雑になるため、開発プ...

W3C 検証に合格するにはどうすればいいですか?

W3C では、さまざまなタグの規定を設定するだけでなく、Web ページの作成者が実際に W3C 規...

MySQL マルチテーブルクエリの具体例

1. SELECT句を使用して複数のテーブルをクエリするSELECT フィールド名 FROM tab...

VUE でタブページを切り替える 4 つの方法

目次1. 静的実装方法: 2. 第2のシミュレーション動的方法3. 3番目の動的データ方式4. 動的...

Docker イメージを削除できません エラー: そのようなイメージはありません: xxxxxx 解決策

序文docker イメージを削除できません。docker docker imagesを確認すると、イ...

Dockerfile をベースに Zabbix 監視システムのコード例を作成する

forループを使用してZabbixイメージをコンテナにインポートします。 n を `ls *.tar...

XHTML 入門チュートリアル: シンプルな Web ページの作成

1 分で最初の Web ページを作成します。簡単な Web ページを作ってみましょう。ぜひフォローし...

Vue.js パフォーマンス最適化 N 個のヒント (収集する価値あり)

目次機能コンポーネント子コンポーネントの分割ローカル変数v-show によるDOMの再利用キープアラ...

JavaScript が Taobao の虫眼鏡効果を模倣

この記事では、淘宝虫眼鏡効果を実現するためのJavaScriptの具体的なコードを参考までに紹介しま...

Windows 10 での MySQL 8.0 のダウンロードとインストール構成のグラフィック チュートリアル

この記事では、MySQL 8.0のダウンロードとインストールについてご紹介します。具体的な内容は以下...