MySQL における 101 のデバッグおよび最適化スキルの共有

データベース 駆動の アプリケーションが増えるにつれ、人々は MySQL を限界まで押し上げています。ここでは、MySQL インストールをチューニングおよび最適化するための 101 のヒントを紹介します。一部のヒントは特定の設置環境に固有のものですが、考え方は一般的なものです。 MySQL のチューニングと最適化のスキルをさらに習得できるように、それらをいくつかのカテゴリに分けました

MySQL は強力なオープンソース データベースです。データベース駆動型アプリケーションが増えるにつれ、人々は MySQL を限界まで押し上げるようになりました。ここでは、MySQL インストールをチューニングおよび最適化するための 101 のヒントを紹介します。一部のヒントは特定の設置環境に固有のものですが、考え方は一般的なものです。 MySQL のチューニングと最適化のテクニックをさらに習得できるように、それらをいくつかのカテゴリに分類しました。

MySQL サーバーのハードウェアとオペレーティング システムの調整:

1. InnoDB ファイル全体をメモリにロードするのに十分な物理メモリを用意します。メモリ内のファイルへのアクセスは、ハード ディスク上のファイルにアクセスするよりもはるかに高速です。
2. スワップの使用は絶対に避けてください。スワップはハードドライブから読み取られるため、非常に時間がかかります。
3. バッテリー駆動の RAM を使用します (注: RAM はランダム アクセス メモリです)。
4. 高度な RAID (注: 安価なディスクの冗長 アレイ) を使用します - できれば RAID10 以上。
5. RAID5 (注: ストレージ パフォーマンス、データ セキュリティ、ストレージ コストのバランスをとるストレージ ソリューション) を避ける – データベースの整合性を確保するには代償を払う必要があります。
6. オペレーティング システムとデータ パーティションを論理的だけでなく物理的に分離します。オペレーティング システムの読み取りおよび書き込み操作はデータベースのパフォーマンスに影響します。
7. MySQL の一時領域とレプリケーション ログとデータを別のパーティションに配置します。データベースのバックグラウンドでディスクの読み取りと書き込みを行うと、データベースのパフォーマンスに影響します。
8. ディスク容量が増えると速度も速くなります。
9. より優れた、より高速なディスク。
10. SATA (注: SATA、シリアル ハード ドライブ) の代わりに SAS (注: シリアル接続 SCSI、シリアル接続 SCSI) を使用します。
11. 特に RAID 構成では、小型のハードドライブは大型のハードドライブよりも高速です。
12. バッテリーバックアップの高速キャッシュRAIDコントローラーを使用します。
13. ソフトウェア ディスク アレイの使用を避けてください。
14. データ パーティションにはソリッド ステート IO カード (ディスク ドライブではない) の使用を検討します。これらのカードは、ほぼすべての量のデータに対して 2 GB/秒の書き込み速度をサポートできます。
15. Linux でスワッピー値を 0 に設定する – データベース サーバーにファイルをキャッシュする理由がありません。これはサーバーまたはデスクトップにとって利点です。
16. 可能であれば、noatime と nodirtime を使用してファイル システムをマウントします。アクセスされたデータベース ファイルの変更時刻を更新する必要はありません。
17. XFS ファイル システムを使用します。このファイル システムは、ext3 よりも高速で小さく、多くのロギング オプションを備えていますが、ext3 には MySQL での二重バッファリングの問題があることがわかっています。
18. XFS ファイル システムのログとバッファー変数を調整して、最高のパフォーマンス基準を実現します。
19. Linux システムでは、NOOP または DEADLINE IO スケジュール スケジューラを使用します – NOOP および DEADLINE スケジュール スケジューラと比較して、CFQ および ANTICIPATORY スケジュール スケジューラは非常に低速です。
20. 64 ビット オペレーティング システムを使用する – MySQL の場合、メモリのサポートと使用量が増加します。
21. サーバー上の未使用のインストール パッケージとデーモンを削除します。これにより、リソースの使用量が削減されます。
22. MySQL を使用するホストと MySQL ホストを hosts ファイルに配置します。DNS ルックアップは必要ありません。
23. MySQL プロセスを強制終了しないでください。データベースとバックアップを実行しているプログラムが損傷します。
24. サーバーを MySQL に提供する – バックグラウンド プロセスやその他のサービスにより、データベースが占有する CPU 時間を短縮できます。

MySQL 構成:

25. 書き込み時には、二重バッファリングを避けるために innodb_flush_method=O_DIRECT を使用します。
26. O_DIRECT および EXT3 ファイルシステムの使用は避けてください。書き込むものはすべてシリアル化されます。
27. InnoDB ファイル全体をメモリにロードするのに十分な innodb_buffer_pool_size を割り当てます。これにより、ディスクからの読み取りが少なくなります。
28. innodb_log_file_size パラメータを大きすぎないように設定すると、より高速になり、より多くのディスク領域を確保できます。通常は、より多くのログを破棄することが適切であり、データベースのクラッシュ後にデータベースを復元する時間を短縮できます。
29. innodb_thread_concurrency パラメーターと thread_concurrency パラメーターを混合しないでください。これら 2 つの値は互換性がありません。
30. max_connections パラメーターに非常に小さな数値を割り当てます。接続が多すぎると RAM が消費され、MySQL サービスがロックされます。
31. 接続を開く際の速度低下を防ぐために、thread_cache を比較的高い値 (16 程度) に保ちます。
32. Skip-name-resolve パラメーターを使用する – DNS ルックアップを削除します。
33. クエリが繰り返され、データが頻繁に変更されない場合は、クエリ キャッシュを使用できます。ただし、データが頻繁に変更される場合、クエリ キャッシュを使用すると後戻りしてしまいます。
34. temp_table_size の値を大きくして、ディスクへの書き込みを防止します
35. max_heap_table_size の値を大きくしすぎないでください。そうしないと、メモリがすぐに使い果たされてしまいます。
37. サイズを決定します。通常、key_read_requests は key_reads の値より大きくなければなりません。そうしないと、key_buffer を効率的に使用できなくなります
38。ただし、デフォルト値の If (1) を維持したい場合は、次に、データの整合性を確保する必要があり、レプリケーションに遅延が生じないことも確認する必要があります。
39. 通常の運用に影響を与えずに構成をテストし、頻繁に再起動するためのテスト環境が必要です。

MySQL スキーマの最適化:

40. データベースを整理します。

41. 古いデータのアーカイブ – 返すか、
検索クエリを実行するために冗長な行を削除します。 42. データにインデックスを付けます
43. インデックス、比較、クエリを多用しないでください スペースを節約し、ディスク読み取りを削減します 45。 latin1 より効率的ではありません。
46. 冗長なデータを最小限に抑えます。
48. データ型に注意してください。実際のデータはできるだけ小さいものを使用してください。 50. 他のデータがクエリで頻繁に使用されるが、BLOB / TEXT データは使用されない場合は、他のデータとは別に、テーブルを頻繁にチェックして最適化します。 52. InnoDB テーブルの最適化を頻繁に書き直す。
53. 列を追加するときにインデックスを削除してから、インデックスを元に戻す方が速い場合がある。
55. さまざまなニーズに応じて、アーカイブ ストレージを使用する。ログ テーブルまたは監査テーブル用のエンジン - これは書き込みの効率が高くなります 56. セッション データは MySQL ではなくキャッシュ (memcache) に保存されます – キャッシュにより、値の自動入力が可能になり、時空間データの作成が防止されます。 MySQL への読み取りと書き込みが困難です 57. 可変長の文字列
を格納する場合は、CHAR の代わりに VARCHAR を使用します – VARCHAR は固定長ではないため、スペースを節約できます (UTF8 はこれの影響を受けません)。スキーマを段階的に変更します。小さな変更が大きな影響を与える可能性があります。
60.
設定ファイルの値をランダムに変更しないでください。悲惨な影響を及ぼします
61。MySQL 設定に関しては、少ない方が良い場合があります。

63. スロークエリログを使用してスロークエリを検出します。
64. 実行プランを使用して、クエリが正常に実行されているかどうかを確認します。
65. クエリが最適に実行されているかどうかを必ずテストしてください – パフォーマンスは時間の経過とともに常に変化します。
66. テーブル全体で count(*) を使用しないでください。テーブル全体がロックされる可能性があります。
67. 後続の同様のクエリでクエリ キャッシュを使用できるように、クエリの一貫性を保ちます。
68. 適切な状況では DISTINCT の代わりに GROUP BY を使用します。
69. WHERE、GROUP BY、ORDER BY 句でインデックス付き列を使用します。
70. インデックスはシンプルにして、複数のインデックスに同じ列を含めないでください。
71. MySQL は間違ったインデックスを使用する場合があります。この場合は USE INDEX を使用します。
72. SQL_MODE=STRICT の使用の問題を確認します。
73. レコード数が 5 未満のインデックス フィールドの場合、UNION での LIMIT の使用は OR ではありません。更新前の SELECT を回避するには、INSERT ON DUPLICATE KEY または INSERT IGNORE を使用して実装しないでください。
75. MAX を使用せず、インデックス フィールドと ORDER BY 句を使用します。
76. ORDER BY RAND() の使用は避けてください。
77. LIMIT M、N は場合によってはクエリを遅くする可能性があるため、慎重に使用してください。
78. WHERE 句でサブクエリの代わりに UNION を使用します。
79. 更新の場合は、共有モードを使用して排他ロックを防ぎます。
80. MySQL を再起動する前に、データがメモリ内にあり、クエリが高速であることを確認するためにデータベースをウォームアップすることを忘れないでください。
81. DROP TABLE、CREATE TABLE DELETE FROM を使用して、テーブルからすべてのデータを削除します。
82. 最小化されたデータで必要なデータをクエリする場合、* を使用すると時間がかかります。
83. オーバーヘッドを減らすために、複数の接続ではなく永続的な接続を検討してください。
84. サーバーの負荷を含むクエリのベンチマークでは、1 つの単純なクエリが他のクエリに影響を与える場合があります。
85. サーバーの負荷が増加した場合は、SHOW PROCESS
LIST を使用して、遅くて問題のあるクエリを表示します。 86. 開発環境で生成された画像データに対する疑わしいクエリをすべてテストします。

MySQL バックアップ プロセス:

87. セカンダリ レプリケーション サーバーからのバックアップ。

88. データの依存関係や外部キー
制約の不一致を避けるために、バックアップ中にレプリケーションを停止します。 89. MySQL を完全に停止し、データベース ファイルからバックアップを作成します。
90. バックアップに MySQL ダンプを使用する場合は、バイナリ ログ ファイルもバックアップしてください。レプリケーションが中断されないように注意してください。
91. LVM スナップショットを信頼しないでください。データの不整合が生じ、将来問題が発生する可能性があります。
92. 単一テーブルのリカバリを容易にするために、データが他のテーブルから分離されている場合は、テーブル単位でデータをエクスポートします。
93. mysqldump を使用する場合は –opt を使用してください。
94. バックアップする前にテーブルを確認して最適化します。
95. インポートを高速化するには、インポート中に外部キー制約を一時的に無効にします。
96. インポートを高速化するために、インポート中に一意性の検出が一時的に無効になります。
97. データ サイズの増加をより適切に監視するために、各バックアップ後にデータベース、テーブル、インデックスのサイズを計算します。
98. 自動スケジュール スクリプトを介してレプリケーション インスタンスのエラーと遅延を監視します。
99. 定期的にバックアップを実行します。
100. バックアップを定期的にテストします。
最後の 101: MySQL モニタリングの実行: Monitis が世界初の無料オンデマンド MySQL モニタリングを発表します。

以上がMySQL における 101 のデバッグおよび最適化スキルの共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。