Mysqlが遅い時の対処方法
仕事でMysqlを使用していますが、DBにパワーが無いのか遅い・・・
DBはその特性上、データを保存していきます。
そのため、データを貯めこむ一方でどんどん遅くなっていきます。
そんな時の対処方法を色々と書いてみます。
Mysqlの設定を変更
一番簡単なのがMysqlの設定を変更して、メモリ使用量等の上限を上げることです。これが出来たら一番楽・・・
以下のSQLを使用して現在のDBの設定を見ることが出来ます。
SHOW VARIABLES;
MySQLの設定は(/etc/my.cnf)を書き換える。
innodb_buffer_pool_size = 256MB256MBの部分は環境や使用できるメモリに合わせて変更してください。
- innodb_buffer_pool_size
-
InnoDBに対する各種操作の他、レコードデータなどのキャッシュ領域として利用され、
非同期I/Oスレッドやサーバスレッドがアクセスする。
InnoDBバッファプール上 データへのアクセスにはディスクI/Oが発生しないが、バッファプール内に収まり きらなくなったデータはディスク上に退避されることになる。
インデックスを貼る
インデックスがないと、MySQL がレコードを見つけるために、最初のレコードから開始し、テーブル全体を読み取るとが必要になります。テーブルが大きくなると、これにコストがかかります。ただし、全てのカラムにインデックスを貼るのは本末転倒。更に遅くなってしまいます。
よく使用する検索カラムにインデックスを貼るのを推奨します。
今回は新たにインデックスを貼る方法のみ記載します。
インデックス
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
部分インデックス
ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名(バイト数));フィールドの指定バイト数にのみインデックス貼る方法です。
ユニークインデックス
CREATE UNIQUE INDEX インデックス名 ON テーブル名(カラム名);インデックスの対象となるカラムに含まれる値に重複する値が含まれないように制限をかけたい場合に使用します。
複合インデックス
CREATE INDEX インデックス名 ON テーブル名 (カラム名1([数値])[, カラム名2([数値]), ...);複数のカラムに同時にインデックスを貼るのではなく、複数のカラムを1つのインデックスと扱いので注意してください。
指定された複数のカラムを検索対象にしないと使用されないようなので、使い所が難しそうです。
インデックス確認
SHOW INDEX FROM テーブル名
クエリの最適化
使用するクエリ自体の最適化を行ってください。検索条件にインデックスを使用したり、無駄なカラムの読み込みを省く、必要な行数のみフェッチするなどすると早くなります。
単純にMysqlのメモリの増量やテーブルにインデックスを貼るほうが簡単ですが、いつかは頭打ちになります。
出来ればクエリの最適化を行っても無理であればMysqlの設定変更やインデックスを貼ってください。
プログラム側での対処
MysqlはSELECTされた内容をメモリ上に展開をします。ですがメモリの上限を超えるデータ量を取得しようとした場合、ディスク上に退避をしてしまうため、とても遅くなります。
対処方法としてはSQLを分割することです。
長大なSQL文でいくつものテーブルにアクセスしてしまうとその分、メモリが圧迫され、一時データがディスク上に行ってしまいとても遅くなってしまいます。
1つの長大なSQL文で問い合わせを行うより、短く早いSQL文を連発した方が早い場合もあります。
例えば1回10秒掛かるSELECTより、0.1秒掛かるSELECTを10回行うほうが格段に早くなります。
プログラム側でもDBのことを考えてSQL文のことを考えてみてください。
