MySQL データベース開発の 36 の原則 (要約)

序文

これらの原則は実際の戦闘から要約されています。

あらゆる原則の背後には血なまぐさい教訓がある

これらの原則は主にデータベース開発者向けです。開発プロセス中は必ず注意してください。

I. 基本原則

1. データベース内で計算を行わないようにする

諺にもあるように、「つま先で考えさせるな、それは脳の仕事だ」

データベース開発者として、私たちはデータベースが得意とする以下の機能をもっと活用できるようにすべきです。

• データベースで計算をしないようにしてください
• 複雑な計算はプログラム端末のCPUに移される
• MYSQLをできるだけシンプルに使う

例：

MySQL では、md5()、Order by Rand() などの計算関数を使用しないようにしてください。

2. 1つのテーブル内のデータ量を制御する

1 つのテーブル内のデータ量が多すぎると、データ クエリの効率に影響し、深刻な場合にはデータベース全体が停止してしまうことは誰もが知っています。

一般的に、1 年間の単一テーブルの推定データ量に基づくと、次のようになります。

• 純粋なINTは1000Wを超えない
• CHARは500万を超えない

同時に、単一のテーブルが過負荷にならないように、適切なテーブル パーティション分割を行う必要があります。一般的なテーブル パーティション分割戦略は次のとおりです。

• テーブルを USERID で分割する (テーブルを ID 間隔で分割する): ユーザー数が多く、ユーザー特性が明らかな金融業界で広く使用されています。
• テーブルを日付別（日別、週別、月別）に分割します。ユーザーインターネット記録テーブル、ユーザー SMS テーブル、通話記録テーブルなど、通信業界で広く使用されています。
• エリア別テーブル（州、市、地区テーブル）
• 他の

パーティション テーブルは主に次のシナリオに適用されます。

① テーブルが非常に大きく、メモリに格納できない、またはテーブルの最後にホットデータのみがあり、残りは履歴データです。

② パーティションテーブル内のデータの保守が容易になり、独立したパーティションに対して独立した操作を実行できます。

③ パーティションテーブルのデータを異なるマシンに分散できるため、リソースを効率的に使用できます。

④ パーティションテーブルを使用すると、特定の特殊なボトルネックを回避できます。

⑤ 独立したパーティションをバックアップ・復元できます。

ただし、パーティション テーブルを使用する場合にはいくつかの制限もあります。 使用時には次の点に注意してください。

① テーブルには最大 1024 個のパーティションを設定できます。

② バージョン5.1ではパーティションテーブル式は整数である必要があり、バージョン5.5では列パーティションを使用できます。

③ パーティションフィールドに主キー列と一意のインデックス列がある場合は、主キー列と一意の列の両方を含める必要があります。

④ パーティションテーブルでは外部キー制約は使用できません。

⑤ 既存のテーブルの構造を変更する必要があります。

⑥ すべてのパーティションは同じストレージエンジンを使用する必要があります。

⑦ パーティション関数で使用できる関数や式にはいくつかの制限があります。

⑧ 一部のストレージエンジンはパーティション分割をサポートしていません。

⑨ MyISAM パーティションテーブルの場合、インデックスをキャッシュにロードすることはできません。

⑩ MyISAM テーブルの場合、パーティションテーブルを使用するときは、より多くのファイル記述子を開く必要があります。

3. テーブルフィールドの数を制御する

1 つのテーブル内のフィールドの数は多すぎないようにしてください。ビジネス シナリオに応じて最適化および調整し、テーブル フィールドの数が少なく正確になるように調整してください。これには次の利点があります。

• IO効率
• 完全なテーブルトラバーサル
• 迅速なテーブル修理
• 同時実行性の向上
• alter tableはより高速です

では、1 つのテーブルに適切なフィールド数はいくつでしょうか?

評価は、サイズが 1G、データが 500 万行ある単一のテーブルに基づいています。

• 1Gのファイルを連続して読み取るにはN秒かかります
• 1行あたり200バイト以内
• 1つのテーブルには純粋なINTフィールドが50個を超えない
• 1 つのテーブルに 20 個を超える CHAR(10) フィールドを含めることはできません。

==> 1つのテーブル内のフィールド数を20〜50に制限することをお勧めします。

4. パラダイムと冗長性のバランスをとる

データベースのテーブル構造の設計にもバランスが必要です。これまで、3 つの主要なパラダイムに厳密に従う必要があるとよく言われてきましたが、まずはパラダイムとは何かについてお話ししましょう。

第 1 正規形: 単一のフィールドをこれ以上分割することはできません。ユニークさ。

第 2 正規形: 主キーの一部にのみ依存する非主属性はありません。不完全な依存関係を排除します。

3 番目のパラダイム: 推移的な依存関係を排除します。

パラダイムと冗長性を一文で要約すると次のようになります。

冗長性は、ストレージをパフォーマンスと交換します。

パラダイムは、パフォーマンスとストレージを交換することです。

したがって、実際の仕事では冗長性の方が一般的に人気があります。

モデルを設計する際、これら 2 つの側面間の具体的なトレードオフは、まず企業が提供するコンピューティング能力とストレージ リソースに基づいて決定する必要があります。

第二に、一般に、インターネット業界ではデータ ウェアハウスは Kimball モデルに従って実装され、モデリングもタスク主導型であるため、冗長性とパラダイムのトレードオフがタスク要件を満たします。

たとえば、指標データセットは午前 8 時前に処理する必要がありますが、計算時間ウィンドウは非常に小さいです。指標の計算時間をできるだけ短縮するには、計算プロセス中に複数のテーブルの関連付けをできるだけ減らす必要があり、モデルを設計するときにはより多くの冗長性が必要です。

5. 3Bを拒否する

データベースの同時実行性は都市交通のように非線形に増加します

このため、高同時実行性によるデータベース麻痺を防ぐために、データベースを開発する際には、高同時実行性におけるボトルネックに注意を払う必要があります。

ここでの 3B の拒否は以下を指します:

• Big SQL: 削減するために
• B IG 取引
• ビッグバッチ

2. フィールドクラスの原則

1. 数値フィールドタイプを有効活用する

数値には 3 つの種類があります。

• 整数: TINYINT(1バイト)、TINYINT(1バイト)、SMALLINT(2バイト)、MEDIUMINT(3バイト)、INT(4バイト)、BIGINT(8バイト)
• 浮動小数点型: FLOAT (4B)、DOUBLE (8B)
• 10進数(M,D)

一般的な例をいくつか挙げます。

1) INT(1) 対 INT(11)

INT(1)とINT(11)の違いが分からない人が多いと思います。皆さんもとても興味があると思います。実は、1と11は表示の長さの違いに過ぎません。つまり、int(x)のxの値が何であっても、格納されている数値の値の範囲はintデータ型自体の値の範囲であり、xはデータ表示の長さに過ぎません。

2) BIGINT 自動インクリメント

ご存知のとおり、signed int でサポートされる最大値は約 22 億であり、これは私たちのニーズや単一の MySQL テーブルがサポートできるパフォーマンスの限界をはるかに超えています。 OLTP アプリケーションの場合、単一テーブルのサイズは通常数千万に維持され、上限の 22 億に達することはありません。予約量を増やしたい場合は、主キーを上限が 42 億の unsigned int に変更すれば十分です。

bigint を使用すると、より多くのディスク領域とメモリ領域が占有されます。結局のところ、メモリ領域は限られています。非効率的な占有は、より多くのデータのスワップインとスワップアウトにつながり、IO 圧力が増加してパフォーマンスに悪影響を及ぼします。

したがって、自動インクリメント主キーには int unsigned 型を使用することをお勧めしますが、bigint を使用することはお勧めしません。

3) 10進数(N,0)

DECIMAL データ型を使用する場合、小数点以下の桁数は一般的に 0 になりません。小数点以下の桁数を 0 に設定する場合は、INT 型を使用することをお勧めします。

2. 文字を数字に変換する

数値インデックスと文字列インデックスには、さらに多くの利点があります。

• より効率的
• より高速なクエリ
• より小さなフットプリント

例: CHAR(15)の代わりにunsigned INTを使用してIPを保存する

符号なし整数

INET_ATON() と INET_NTOA() を使用して、IP 文字列と値間の変換を行うことができます。

3. 最初にENUMまたはSETを使用する

一部の列挙データについては、最初に ENUM または SET を使用することをお勧めします。このようなシナリオは次のような場合に適しています。

1) 文字列型

2) 可能な値は既知であり有限である

ストレージ：

1) ENUMは1バイトを占め、数値演算に変換される

2) SETはノードに依存し、最大8バイトを占有します。

3) 比較するときは一重引用符が必要です（数値の場合でも）

例：

`sex` enum('F','M') COMMENT '性別';

`c1` enum('0','1','2','3') COMMENT '監査';

4. NULLフィールドの使用を避ける

データベース テーブル フィールドを設計するときに、「NOT NULL DEFAULT」を追加しようとするのはなぜでしょうか。ここでは、NULL フィールドを使用することの欠点について説明する必要があります。

クエリの最適化が難しい

NULL列とインデックスには追加のスペースが必要です

NULL を含む複合インデックスは無効です

例：

1) `a` char(32) デフォルト NULL [非推奨]

2) `b` int(10) NOT NULL [非推奨]

3) `c` int(10) NOT NULL DEFAULT 0 [推奨]

5. TEXT/BLOBの使用を減らして分割する

TEXT型の処理性能はVARCHAR型に比べてはるかに低い

• ハードディスク一時テーブルの強制生成
• スペースを無駄にする
• VARCHAR(65535) ==> 64K (UTF-8 に注意)

TEXT/BLOBデータ型は使用しないでください

ビジネス上必要な場合は、別のテーブルに分割することをお勧めします。

例：

テーブルt1を作成します（
  id INT NOT NULL AUTO_INCREMENT、
  データ TEXT NOT NULL、
  主キー(id)
)ENGINE=InnoDB;

6. 画像をデータベースに保存しない

最初の写真:

すべての画像をデータベースに保存すると、データベースのサイズが増加し、読み書き速度が低下することがわかります。

写真をデータベースに保存することの欠点:

1. データベースの読み取り/書き込み速度はファイルシステムの処理速度に追いつくことは決してない
2. データベースのバックアップは膨大になり、時間がかかるようになっている
3. ファイルにアクセスするには、アプリケーション層とデータベース層を通過する必要があります。

★推奨解決策: 画像パスをデータベースに保存する

年、月、日に基づいてパスを生成します。年、月、日、または年と月のいずれに従ってパスを生成するかは、ニーズによって異なります (必ずしも日付に基づいているわけではありません)。

重要なのは、なぜ複数のフォルダに分散する必要があるかを理解することです。これは次の原則で説明できます。

オペレーティング システムでは、1 つのディレクトリ内のファイル数に制限があります。ファイル数が大量にある場合。ディレクトリからファイルを取得する速度はどんどん遅くなります。そのため、速度を維持するためには、一定のルールに従って複数のディレクトリに分散させる必要があります。

イメージはディスク パスに分散されます。データベースフィールドには「images/2012/09/25/1343287394783.jpg」のようなものが保存されます。

アップロードされた元の画像ファイル名は、タイムスタンプに従って生成され、たとえば 1343287394783.jpg のように名前が変更されて保存されます。これは、複数の人が同じディレクトリに画像をアップロードした場合に発生する可能性があるファイル名の重複を回避するためです。

画像に名前を付ける際にどのようなルールを使用するかは関係ありません。画像の名前が一意である限りです。

たとえば、Web サイトに多数の同時訪問者がいる場合、ディレクトリ生成は可能な限り詳細に行う必要があります。たとえば、時間単位の精度であれば、各時間をフォルダーにすることができます。同時に、0.001 秒の間に、2 人のユーザーが同時に写真をアップロードしています (その場合、写真は同じ時間別フォルダーに保存されるため)。タイムスタンプは秒単位まで正確だからです。イメージ名の一意性を保証し、上書きを回避するために、タイムスタンプの後にミリ秒とマイクロ秒を追加できます。要約すると、同時アクセス量が多いほど、正確であればあるほど良いです。

話題外:

1) 保存されたディスク パスが「/images/2012/09/25/1343287394783.jpg」(先頭にスラッシュ) ではなく「images/2012/09/25/1343287394783.jpg」なのはなぜですか?

ページに画像を表示するために画像パスを取得する必要がある場合、それが相対パスであれば、「./」+「images/2012/09/25/1343287394783.jpg」を使用して組み立てることができます。

別のドメイン名が必要な場合 (たとえば、CDN アクセラレーションを行う場合)、img1.xxx.com や img2.xxx.com などのドメイン名を使用できます。

「http://img1.xxx.com/」+「images/2012/09/25/1343287394783.jpg」を直接アセンブルします

2) 保存されたディスク パスが「http://www.xxx.com/images/2012/09/25/1343287394783.jpg」ではなく「images/2012/09/25/1343287394783.jpg」になっているのはなぜですか。

これには実際に CDN の知識が関係しますが、ここでは CDN に関する具体的な知識については詳しく説明しません。簡単に言うと、

CDN サービス: 静的コンテンツに非常に適しています。そのため、商品画像については、訪問数が増えても、CDN サービスをレンタルするときに画像をサーバーにアップロードするだけで済みます。

例: 北京から長沙のサーバーにアクセスすると、距離が遠すぎます。北京のクラウド サービスに商品写真を完全に配置できます (現在 Web サイトに提供されているクラウド ストレージは、実際には Web サイトへの転送とローカル アクセスを提供する CDN だと思います)。こうすることで、北京のユーザーがウェブサイトにアクセスすると、実際に近くの場所から写真を入手できるようになります。長距離伝送は必要ありません。

写真を読み込むにはドメイン名 img.xxx.com を使用します。このドメイン名は北京のクラウド サービスに解決されます。

練習: データベースには「images/2012/09/25/1343287394783.jpg」が保存されます。

画像は実際には Web サーバーに保存されません。北京のCDNサーバーにアップロードします。

データベースから直接「img.xxx.com/」+「images/2012/09/25/1343287394783.jpg」を取得しました

たとえば、複数ある場合は、img1.xx.com と img2.xx.com という名前を付けます。

とにかく、あなたがしたいことを何でもしてください。したがって、ドメイン名を直接保存する場合。非常に面倒になります。移行のトラブル。

3. インデックスの原則

1. インデックスを慎重かつ合理的に追加する

• インデックスを追加するとクエリが改善されます
• インデックスを追加すると更新が遅くなります
• インデックスの数が増えれば増えるほど良いわけではない
• 省略可能なインデックスの追加は避ける（データ密度とデータ分布を総合的に評価し、フィールド数の20%以下が望ましい）
• コアSQLと組み合わせてインデックスをカバーすることを検討する

例: 「性別」列にインデックスを作成しないでください

理論的な記事では、値の重複率が高いフィールドはインデックス作成に適していないと説明されています。性別フィールドには2つの値しかないとは言わないでください。ネットユーザーが個人的にテストしたところ、フィールドはピンインの最初の文字を値として使用し、合計26の可能性があります。インデックスを追加した後、数百万のデータ量で、インデックスを使用した場合の速度は、インデックスを使用しない場合よりも遅くなります。

性別はインデックス作成に適さないのはなぜですか?インデックスにアクセスするには追加の IO オーバーヘッドを支払う必要があるため、インデックスから取得できるのはアドレスのみです。実際にデータにアクセスする場合は、テーブルに対して IO を実行する必要があります。 100 万行のテーブルからいくつかのデータ ポイントを取得する場合、インデックスを使用してそれらをすばやく見つけ、インデックスにアクセスすることは、IO オーバーヘッドに見合う価値があります。しかし、性別フィールドなどの 100 万行のデータから 50 万行のデータを取得する場合は、インデックスに 50 万回アクセスしてから、テーブルに 50 万回アクセスする必要があります。合計コストは、テーブルを 1 回直接スキャンする場合よりも少なくなりません。

2. 文字フィールドにはプレフィックスインデックスが必要です

差別：

一文字識別: 26

4文字の識別: 26*26*26*26 = 456,976

5文字の識別: 26*26*26*26*26 = 11,881,376

6文字の識別: 26*26*26*26*26*26 = 308,915,776

文字フィールドにはプレフィックス インデックスが必要です。例:

`pinyin` varchar(100) デフォルト NULL コメント '地区ピンイン', 
キー `idx_pinyin` (`pinyin`(8))、 
) エンジン=InnoDB

3. インデックス列に対して操作を実行しない

理由は2つあります。

1) インデックスは使用されません。

2) テーブル全体のスキャンが行われます

例：

悪いサンプル:

テーブルから*を選択 
WHERE to_days(現在の日付) – to_days(日付列) <= 10

良いサンプル:

テーブルから*を選択 
ここで、date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);

4. 自動増分列またはグローバルIDをINNODB主キーとして使用する

• 主キーにクラスター化インデックスを作成する
• セカンダリインデックスはプライマリキーの値を保存します
• 主キーは更新または変更しないでください
• 値を昇順に挿入する
• 文字列を主キーとして使用しないでください
• クラスター化インデックスの分割
• 代理主キーとして、ビジネスから独立した AUTO_INCREMENT 列またはグローバル ID ジェネレーターを使用することをお勧めします。
• 主キーを指定しない場合、InnoDB は代わりに一意で null 以外の値のインデックスを使用します。

5. 外部キーをできるだけ避ける

1. オンライン OLTP システムでは外部キーを使用しないようにしています。
2. 外部キーは開発の労力を節約します
3. 追加費用が発生します
4. 行ごとの操作
5. 他のテーブルに「アクセス」できる、つまりロックできる
6. 同時実行性が高い場合、デッドロックが発生しやすくなります。

プログラムでは制約を保証することが推奨される

たとえば、元のテーブル作成ステートメントは次のようになります。

テーブル `user` を作成します (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー',
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT 'ユーザー名',
 主キー (`user_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;
 
テーブル「order」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー',
 `total_price` 小数点(10,2) NOT NULL デフォルト '0.00', 
 `user_id` int(11) NOT NULL デフォルト '0',
 主キー (`id`)、 
 キー `for_indx_user_id` (`user_id`)、 
 制約 `for_indx_user_id` 外部キー (`user_id`) 参照 `user` (`user_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

外部キー制約なし:

テーブル `user` を作成します (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー', 
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT 'ユーザー名',
 主キー (`user_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;
 
テーブル「order」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー', 
 `total_price` 小数点(10,2) NOT NULL デフォルト '0.00', 
 `user_id` int(11) NOT NULL デフォルト '0',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

外部キー制約が適用されなくなった後、クエリを高速化するために、通常は外部キー制約を確立しないフィールドにインデックスを追加します。

テーブル「order」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー', 
 `total_price` 小数点(10,2) NOT NULL デフォルト '0.00', 
 `user_id` int(11) NOT NULL デフォルト '0',
 主キー (`id`)、キー `idx_user_id` (`user_id`)、
)ENGINE=InnoDB デフォルト文字セット=utf8;

実際の開発では、外部キー制約は設定されないのが一般的です。

4. SQLクラスの原則

1. SQL文をできるだけシンプルにする

開発プロセスでは、SQL ステートメントをシンプルに保つように努めます。大きな SQL と複数のシンプルな SQL ステートメントを比較してみましょう。

1. 伝統的なデザインのアイデア
2. バグMySQLではありません
3. SQL ステートメントは 1 つの CPU 上でのみ実行できます。
4. 5000 QPS 以上の高同時実行性において、1 秒の SQL ステートメントはどういう意味ですか?
5. 大きなSQLはデータベース全体をブロックするかもしれない

大きなSQL文を拒否し、複数の単純なSQL文に分割する

1. 単純なSQLキャッシュヒット率は高い
2. テーブルロック時間（特にMyISAM）を短縮
3. 複数のCPUを使用する

2. トランザクション（接続）を短くする

1. トランザクション/接続の使用原則: 開いて使用し、使用後に閉じる
2. ロック リソースの使用を削減するために、トランザクションに関連しないすべての操作はトランザクションの外部に配置されます。
3. 一貫性を損なうことなく、長いトランザクションの代わりに複数の短いトランザクションを使用する

例：

1) 投稿時に写真のアップロードを待つ

2) 睡眠接続の数が多い

3. SP/TRIG/FUNCの使用をできるだけ避ける

オンライン OLTP システムでは、次のことが必要です。

• ストアドプロシージャの使用は最小限に抑える
• トリガーをできるだけ少なくする
• 結果を処理するためのMySQL関数の使用を減らす

上記のタスクはすべてクライアントプログラムに任せる

4. SELECT * を使わないようにする

SELECT * を使用すると、CPU、メモリ、IO、ネットワーク帯域幅がさらに消費されます。

クエリ ステートメントを記述するときは、SELECT * を使用せず、必要なデータ列のみを取得するようにしてください。

• より安全な設計: テーブル変更の影響を軽減
• カバーインデックスを使用する可能性を提供する
• SELECT/JOINは、特にTEXT/BLOBがある場合に、一時的なハードディスクテーブルの生成を削減します。

例：

推奨されません:

SELECT * FROM タグ
id = 999148 の場合

推薦する：

タグからキーワードを選択
id = 999148 の場合

5. ORをIN()に書き換える

同じフィールドの場合は、またはを()のように書き換えます。

OR効率: O(n)

IN効率: O(Log n)

nが大きい場合、ORははるかに遅くなります

IN の数を制御することに注意してください。n は 200 未満にすることをお勧めします。

例：

推奨されません:

opp から * を選択 WHERE phone='12347856' または phone='42242233'

推薦する：

opp WHERE 電話番号が ('12347856' , '42242233') であるものから * を選択

6. ORをUNIONに書き換える

異なるフィールドの場合は、「or」を「union」に変更します

1. 異なるフィールドでの「or」クエリを減らす
2. マージインデックスは往々にして非常に愚かである
3. 十分自信がある場合は、次のように設定します: global optimizer_switch='index_merge=off';

例：

推奨されません:

oppから*を選択 
ここで、電話番号は '010-88886666' です。 
または 
携帯電話='13800138000';

推薦する：

oppから*を選択 
ここで、電話番号は '010-88886666' です。 
連合 
oppから*を選択 
ここで、 cellPhone='13800138000';

7. 否定クエリや%プレフィックスのあいまいクエリを避ける

実際の開発では、ネガティブクエリを避けるようにする必要があります。ネガティブクエリとは何でしょうか? 主なものは次のとおりです。

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE など。

同時に、%プレフィックスのファジークエリも避けなければなりません。これはB+ツリーを使用し、インデックスが使用されず、完全なテーブルスキャンにつながるため、パフォーマンスと効率が想像できます。

例：

8. COUNTを減らす(*)

開発では COUNT(*) をよく使用しますが、この使用法によって多くのリソースが浪費されることはわかりません。COUNT(*) はリソースのオーバーヘッドが大きいため、できるだけ使用しないようにする必要があります。

統計をカウントする場合は、以下をお勧めします。

1. リアルタイム統計: memcache、双方向更新を使用し、早朝にベンチマークを実行します
2. 非リアルタイム統計: 別の統計テーブルを使用して定期的に再計算するようにしてください

COUNT(*) を他のいくつかの COUNT と比較してみましょう。

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '会社ID',
`sale_id` int(10) 符号なし デフォルト NULL, 

結論は：

COUNT(*)=COUNT(1)

カウント(0)=カウント(1)

カウント(1)=カウント(100)

COUNT(*)!=COUNT(列)

9. 効率的なページングを制限する

従来のページング:

テーブル制限 10000,10 から * を選択します。

LIMIT原則:

1. 制限 10000,10
2. オフセットが大きいほど遅くなります

推奨ページング:

テーブルから * を選択し、WHERE id>=23423、limit 11; 
#10+1 (1ページあたり10項目)
テーブルから * を選択 WHERE id>=23434 制限 11;

ページング方法2:

テーブルから * を選択します WHERE id >= ( テーブルから id を選択 制限 10000,1 ) 制限 10;

ページング方法 3:

SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;

ページング方法 4:

#まずプログラムを使用してIDを取得します:
テーブル制限 10000,10 から ID を選択;
# in を使用して、ID に対応するレコードを取得します。Select * from table WHERE id in (123,456…) ;

具体的なニーズは、実際のシナリオに応じてインデックスを分析し、再編成することです。

例：

10. UNIONの代わりにUNION ALLを使用する

結果を重複排除する必要がなく、複数のテーブルをまとめてクエリして表示するだけの場合は、UNION には重複排除のオーバーヘッドがあるため、UNION ALL を使用します。

例：

MySQL>SELECT * FROM detail20091128 UNION ALL 
SELECT * FROM detail20110427 UNION ALL 
SELECT * FROM detail20110426 UNION ALL 
SELECT * FROM detail20110425 UNION ALL 
SELECT * FROM detail20110424 UNION ALL 
* を detail20110423 から選択します。

11. 高い同時実行性を確保するために接続を分解する

高同時実行DBでは2つ以上のテーブルに対してJOINを実行することは推奨されません。

結合を適切に分解すると、高い同時実行性が保証されます。

• 大量の初期データをキャッシュする機能
• 複数のMyISAMテーブルの使用
• 大きなテーブルの小さなIDのIN()
• 結合は同じテーブルを複数回参照します

例：

元のSQL:

MySQL> タグから*を選択 
JOIN タグ投稿 
tag_post.tag_id=tag.id で 
JOIN投稿 
tag_post.post_id=post.id で 
WHERE tag.tag='中古おもちゃ';

SQL を分解すると:

MySQL> Select * from tag WHERE tag='中古おもちゃ'; 
MySQL> tag_post から * を選択 WHERE tag_id=1321; 
MySQL> post.id が (123,456,314,141) である post から * を選択

12. GROUP BYはソートを解除する

GROUP BYを使用してグループ化と自動ソートを実現する

並べ替えは不要: NULL で並べ替え

特定の並べ替え: DESC/ASC によるグループ化

例：

13. 同じデータ型の列値の比較

原則: 数字は数字、文字は文字

数値列と文字型の比較: 比較のために倍精度に変換します

文字列と数値型の比較: 文字列全体が数値に変換され、インデックスクエリは使用されません。

例：

フィールド: `remark` varchar(50) NOT NULL COMMENT '備考、デフォルトでは空'、

MySQL>ギフトから `id`, `gift_code` を選択 
ここで、`deal_id` = 640 かつ remark=115127; 
セット内1列（0.14秒）
 
 
MySQL>pool_gift から `id`, `gift_code` を選択 
ここで、`deal_id` = 640 かつ remark='115127'; 
セット内の1行（0.005秒）

14. データを読み込む

高速バッチデータインポート:

1. バッチ ロードは単一行のロードよりも高速で、毎回キャッシュを更新する必要はありません。
2. インデックスなしでの読み込みはインデックスありでの読み込みよりも高速です
3. 値、値、値を挿入するインデックスの更新を減らす
4. データのロードは挿入よりも約20倍高速です

INSERT ... SELECT は使用しないようにしてください。理由の 1 つは遅延が発生すること、もう 1 つは同期エラーが発生する可能性があることです。

15. 大規模なバッチ更新を分割する

• 大規模なバッチ更新は、ピーク時間を避けるために早朝に実行する必要があります。
• 早朝は制限なし
• 1 日のデフォルトのレートは 1 秒あたり 100 メッセージです (特別な条件については後で説明します)

例：

投稿を更新します。set tag=1 WHERE id in (1,2,3); 
睡眠 0.01; 
投稿を更新します。set tag=1 WHERE id in (4,5,6); 
睡眠 0.01;
…

16. すべてのSQLを知る

DBA またはデータベース開発者であれば、データベースのすべての SQL に精通している必要があります。一般的なコマンドには次のものがあります。

• プロフィールを表示
• MYSQLsla
• MySQL ダンプが遅い
• 説明する
• スローログを表示
• プロセスリストを表示
• QUERY_RESPONSE_TIME を表示 (Percona)

5. 合意の原則

1. オンラインとオフラインの分離

ワイヤレスデータベース操作権限を確保するためのデータベースエコシステムを構築する

原則: オンラインはオンラインにつながり、オフラインはオフラインにつながる

1. 生産データ用のプロライブラリ
2. プレプロダクション環境ではプレライブラリを使用する
3. テストライブラリ
4. 開発ライブラリ

2. DBAの確認なしでサブクエリを禁止する

1. ほとんどの場合、最適化が不十分です
2. IN id を使用した特別な WHERE サブクエリ
3. 一般的にはJOINを使って書き換えることができる。

例：

MySQL> table1 から * を選択します。 where id in (table2 から id を選択します); 
MySQL> insert into table1 (select * from table2); // レプリケーション異常が発生する可能性があります

3. プログラムを明示的にロックしない

1. 外部ロックはデータベースを制御できません
2. ハイバーストは大惨事
3. デバッグとトラブルシューティングが非常に困難

同時控除などの一貫性の問題については、トランザクションを使用して処理し、コミットする前に 2 回目の競合チェックを実行します。

4. 統一文字セットはUTF8です

5. 統一された命名規則

1) ライブラリ名とテーブル名はすべて小文字です

2) デフォルトのインデックス名は「idx_field name」です。

3) 図書館名には略語を使用する（できれば2文字から7文字）

データ共有 ==> ds

4) 命名に予約語を使用しない

データベース開発者は、上記の落とし穴をすべて念頭に置くことをお勧めします。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援して頂ければ幸いです。