ホームページ > 記事 > データベース > 最も包括的な MySQL のデバッグと最適化のスキル

最も包括的な MySQL のデバッグと最適化のスキル

小云云オリジナル: 2018-01-17 10:35:111706ブラウズ

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. インデックス、比較、クエリを多用しないでください。スペースを節約し、UTF16 よりも低くなります。実行効率を高める。
46. トリガーは控えめに使用する。
47. 冗長なデータを最小限に抑える – 不要なデータを繰り返さない。
48. 拡張行の代わりにリンクされたテーブルを使用する。
49. データ型に注意する。
50. クエリで他のデータが頻繁に使用されるが、BLOB/TEXT データは頻繁に使用されない場合は、テーブルを頻繁にチェックして最適化します。 InnoDB テーブルの最適化を頻繁に書き換えます。
53. 列を追加するときにインデックスを削除してから再度追加すると、より高速になります。
54. 別のストレージエンジンを使用します。ログテーブルまたは監査テーブルの場合 - これは書き込みの効率が高くなります。
56. セッションデータは MySQL ではなくキャッシュ (memcache) に保存されます。キャッシュにより値の自動入力が可能になり、読み取りが難しい時空間データの作成が防止されます。
57. 可変長の文字列を保存する場合は、CHAR の代わりに VARCHAR を使用します。VARCHAR は固定長ではないため、スペースが節約されます (UTF8 はこの影響を受けません)。 – 小さな変更が大きな影響を与える可能性があります。
59. 運用環境の変更を反映するすべてのスキーマをテストします。
60. 構成ファイルの値をランダムに変更しないでください。悲惨な影響を与える可能性があります。
61. MySQL 設定では、少ない方が良い場合もあります。
62. 疑わしい場合は、汎用の 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 PROCESSLIST を使用して、遅くて問題のあるクエリを表示します。
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 のデバッグと最適化のスキル

関連記事