MySQL の開発仕様についての私の意見-mysql チュートリアル-php.cn

MySQL の仕様のほとんどはインターネットでも見つけることができます。ここで共有したいのは、Lao Ye が個人的により重要だと考えている、または見落とされやすく、混乱しやすい箇所です。

1. デフォルトで InnoDB エンジンを使用する

[Old Ye の観点] 何度も呼び出されていますが、InnoDB は MySQL アプリケーションシナリオのほぼ 99% に適しており、 MySQL 5.7 は InnoDB に変更されました。では、これ以上 MyISAM に固執する理由は何でしょうか?

さらに、頻繁に読み書きされる InnoDB テーブルは、自動インクリメント/シーケンシャル特性を持つ整数を明示的な主キーとして使用する必要があります。

【参考】: [MySQL FAQ] シリーズ - InnoDB テーブルの主キーとして自動インクリメントカラムを使用することが推奨される理由。

2. 文字セットとしてutf-8を選択します

【Lao Yeの観点】ディスク容量を節約したい場合は、latin1を選択することをお勧めします。いわゆる「汎用性」のために通常は UTF-8 を選択することが推奨されますが、実際には、ユーザーが送信した UTF-8 データを latin1 文字セットで保存することもできます。

latin1 を使用して utf-8 データを保存するときに遭遇する可能性がある問題は、中国語ベースの取得がある場合、それが 100% 正確ではない可能性があることです (Lao Ye は個人的に通常の中国語の完全な取得をテストしただけですが、それは正確ではありませんでした)まったく問題ありません。つまり、一般的な中国語の比較は問題ありません）。

latin1 文字セットを使用して utf-8 データを保存する方法は、Web 側 (ユーザー側) の文字セットは utf-8 であり、バックエンドプログラムも処理に utf-8 を使用しますが、character_set_client、 Character_set_connection、character_set_results、character_set_database、character_set_server これらはすべて latin1 であり、データテーブルとフィールドの文字セットも latin1 です。または、データテーブルで latin1 を使用する場合は、接続するたびに SET NAMES LATIN1 を実行するだけです。

【参考】: MySQL 文字セットについての簡単な説明。

3. InnoDBテーブルの行レコードの物理長は8KBを超えない

【Old Yeの観点】 InnoDBのデフォルトのデータページはB+Treeの特性に基づいて16KBである必要があります。少なくとも 2 つのレコードを保存します。したがって、実際のストレージ長が 8KB を超える場合 (特に TEXT/BLOB 列)、大きな列 (大きな列) では、ORACLE の「行マイグレーション」と同様の「ページオーバーフローストレージ」が発生します。

したがって、大きな列 (特に TEXT/BLOB 型) を使用し、頻繁に読み取りと書き込みを行う必要がある場合は、これらの列をサブテーブルに分割し、メインテーブルと一緒に格納しないことが最善です。あまり頻繁ではない場合は、メインテーブルに保持することを検討してください。

もちろん、innodb_page_size オプションを 8KB に変更した場合、行レコードの物理長は 4KB を超えないようにすることが推奨されます。

【参考】: 【MySQL最適化事例】シリーズ - InnoDBテーブルのBLOBカラムのストレージ効率を最適化します。

4. パーティションテーブルを使用するかどうか

【Old Yeの意見】パーティションテーブルを使用することでパフォーマンスや運用保守の利便性が明らかに向上する場合は、やはりパーティションテーブルを使用することをお勧めします。

たとえば、Lao Ye は zabbix のデータベースで TokuDB エンジンを使用し、時間ディメンションに基づいたパーティションテーブルを使用します。これの利点は、Zabbix の日常的なアプリケーションに影響を与えないことです。また、管理者は、対応するパーティションを削除するだけでよく、非常に遅い実行を必要とせずに、定期的に過去のデータを削除するのに便利です。全体的なパフォーマンスに影響を与える DELETE。

【参考】: ZabbixデータベースをTokuDBに移行します。

5. ストアドプロシージャとトリガーを使用するかどうか

【Lao Yeの意見】一部の適切なシナリオでは、ストアドプロシージャとトリガーを使用してもまったく問題ありません。

以前はゲームのビジネスロジックの処理を完了するためにストレージを使用していましたが、パフォーマンスは問題なく、要件が変更された後はストアドプロシージャを変更するだけで済み、変更コストは非常に低くなります。また、頻繁に更新されるテーブルを維持するためにトリガーも使用します。このテーブルへのすべての変更は、別のテーブルの一部のフィールドを同期的に更新します (マテリアライズドビューの偽装実装と同様)。パフォーマンスの問題はありません。

MySQL のストアドプロシージャとトリガーを災難とみなさないでください。問題が発生した場合でも、最適化するのに遅すぎることはありません。さらに、MySQL にはマテリアライズドビューがないため、ビューの使用は最小限に抑えてください。

6. 適切なタイプを選択してください

【Old Yeの視点】一般的な提案に加えて、他にもいくつかのポイントがあります:

6.1. IPV4アドレスを保存するにはINT UNSIGNEDを使用し、INET_ATON()を使用します。変換には INET_NTOA() を使用して保存する必要はありません。

6.2. 列挙型は ENUM を使用できます。ENUM の内部ストレージ機構は TINYINT または SMALLINT (CHAR/VARCHAR ではありません) を使用します。列挙型データの格納には CHAR/VARCHAR を使用しないでください。

6.3. 以前に広まった「常識的な誤った情報」に対応して、DATETIME の代わりに TIMESTAMP を使用することが推奨されます。実際、5.6 以降では、日付と時刻を保存するために DATETIME を優先することが推奨されます。これは、その利用可能な範囲が TIMESTAMP よりも大きく、物理ストレージが TIMESTAMP よりも 1 バイト多いだけであるため、全体的なパフォーマンスの損失は大きくありません。。

6.4. NULL でなければならない場合を除き、すべてのフィールド定義にデフォルトで NOT NULL 制約が追加されます (ただし、0 で表すことができる NULL 値をデータベースに保存する必要があるシナリオは思いつきません)。。このフィールドに対して COUNT() 統計を実行すると、統計結果がより正確になります (NULL 値を持つものは COUNT でカウントされません)。また、WHERE カラム IS NULL 検索を実行すると、結果を迅速に返すこともできます。

6.5. 特にテーブルに大きな TEXT/BLOB 列がある場合は、直接 SELECT * を実行してすべてのフィールドを読み取らないようにしてください。これらの列を読み取る必要はないかもしれませんが、私が怠け者で SELECT * を書いたために、メモリバッファプールがこれらの「ジャンク」データによって洗い流され、本当にバッファリングする必要があるホットデータが洗い流されてしまいました。

8. インデックスについて

【Old Yeの意見】一般的な提案に加えて、いくつかの重要なポイントがあります：

8.1. 長さが20を超える文字列の場合は、接頭辞インデックスを作成するのが最善です。完全な列インデックス (例: ALTER TABLE t1 ADD INDEX(user(20))) ではなく、インデックス使用率を効果的に向上させることができますが、欠点は、この列を並べ替えるときにプレフィックスインデックスが使用されないことです。プレフィックスインデックスの長さはフィールドの統計に基づいて決定でき、通常は平均の長さよりわずかに長くなります。

8.2. pt-duplicate-key-checker ツールを定期的に使用して、重複したインデックスをチェックして削除します。たとえば、インデックス idx1(a, b) がインデックス idx2(a) をすでにカバーしている場合、idx2 インデックスを削除できます。

8.3. 複数フィールドの結合インデックスがある場合、WHEREのフィルタ条件のフィールド順序はインデックスと一致している必要はありませんが、ソートやグループ化がある場合は一致している必要があります。

例えば、結合インデックス idx1(a, b, c) がある場合、次の SQL はインデックスを完全に使用できます:

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到，WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

ログイン後にコピー

しかし、次の SQL はインデックスの一部しか使用できません:

SELECT ... WHERE b = ? AND a = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a = ? AND c = ?;   -- 只能用到 (a) 部分
SELECT ... WHERE a = ? AND b IN (?, ?);    -- 只能用到 (a, b) 部分
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?;   -- 只能用到 (a) 部分，注意BETWEEN和IN的区别
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?;    -- 只能用到 (a, b) 部分

ログイン後にコピー

以下いくつかの SQLはこのインデックスをまったく使用しません:

SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;

ログイン後にコピー

上記の例からわかるように、これまで WHERE 条件フィールドの順序に重点が置かれてきたのは、インデックスを使用する前にインデックスの順序と一致している必要があります。使用されるのは「常識を誤解させる」」です。厳密に遵守する必要はありません。

さらに、クエリオプティマイザーによって指定されたインデックスまたは実行プランが最適でない場合があります。最適なインデックスを手動で指定するか、セッションレベルの optimizer_switch オプションを変更して、悪い結果を引き起こす特定の機能 (インデックスなど) をオフにすることができます。マージは通常は良いことですが、インデックスのマージを使用すると悪化する場合もあります。この場合、インデックスの 1 つを強制的に指定するか、インデックスのマージ機能を一時的に無効にする必要があります。

9. その他

9.1. インデックスに基づく条件付きフィルタリングであっても、スキャンする必要があるデータの総量が 30% を超えるとオプティマイザが認識した場合 (ORACLE、MySQL では 20% であるようです)現在は 30% ですが、将来的には調整される可能性があります)、実行計画が完全テーブルスキャンに直接変更され、インデックスは使用されなくなります。

9.2. 複数のテーブルを結合する場合、フィルタリング能力が最も高いテーブル (必ずしもデータ量が最小である必要はありませんが、WHERE 条件を追加した後のフィルタ能力が最も高いテーブル) を駆動テーブルとして選択する必要があります。さらに、JOIN の後に並べ替えがある場合は、ドライバーテーブルのインデックスを使用して並べ替えを完了できるように、並べ替えフィールドがドライバーテーブルに属している必要があります。

9.3. ほとんどの場合、ソートインデックスは通常より高いため、実行プランに「Using filesort」が表示されている場合は、最初にソートインデックスを作成します。

9.4. pt-query-digestを使用してスロークエリログを定期的に分析し、Box Anemometerと組み合わせてスロークエリログ分析と最適化システムを構築します。

【参考】: [MySQL FAQ] シリーズ - EXPLAIN 結果のどの情報が懸念すべきか。

注: 特に指定がない限り、上記の仕様推奨事項は MySQL 5.6 以前のバージョンに適用されます。バージョン 5.7 以降ではいくつかの変更がある可能性があるため、個々の仕様の推奨事項をそれに応じて調整する必要があります。

上記は MySQL 開発仕様に関する私の意見です。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。