teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

1

回答を追記

2016/09/06 06:35

投稿

KiyoshiMotoki
KiyoshiMotoki

スコア4791

answer CHANGED
@@ -10,4 +10,49 @@
10
10
 
11
11
  とのことですが、FORCE INDEX 構文なら効くかもしれません(これも確実ではありませんが)。
12
12
  [https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html](https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html)
13
- > USE INDEX (index_list) と同様の機能を持つが、テーブルスキャンが非常に負荷が大きいと見なされる点が追加された FORCE INDEX を使用することもできます。つまり、テーブルスキャンは、指定されたインデックスのいずれかを使用してテーブル内の行を検索する方法がない場合にのみ使用されます。
13
+ > USE INDEX (index_list) と同様の機能を持つが、テーブルスキャンが非常に負荷が大きいと見なされる点が追加された FORCE INDEX を使用することもできます。つまり、テーブルスキャンは、指定されたインデックスのいずれかを使用してテーブル内の行を検索する方法がない場合にのみ使用されます。
14
+
15
+ # 追記
16
+
17
+ > ANALYZE TABLEやOPTIMIZE TABLEは1億レコードの場合、処理が終わらないため、
18
+ 実行しておりません。
19
+ その代り、定期的(2月に1回程度)に、別テーブルへのデータ移行&プログラム修正(参照先修正)を2日かけて行っています。
20
+
21
+ となると、相当な程度、統計情報が狂っている可能性があります。
22
+
23
+ > 更新頻度は毎秒100件くらいのDELETE / INSERTを24時間実施。
24
+
25
+ ということは 2ヶ月で約5億レコードの DELETE / INSERT が発生するため、
26
+ データ移行をする頃には、前回のデータ移行時からレコードが丸ごと入れ替わっていてもおかしくないからです。
27
+
28
+ > check table tb_test_data fast quick;
29
+ にて、テーブルの状態を確認していますが、
30
+ statusは「Table is already up to date」の状態で、問題ない認識です。
31
+
32
+ とのことですが、
33
+ 「(統計情報が)問題ない認識」
34
+ という意味なら、これは誤りです。
35
+
36
+ FAST オプションを指定すると、統計情報の更新は行なわないためです。
37
+ [https://dev.mysql.com/doc/refman/5.6/ja/check-table.html](https://dev.mysql.com/doc/refman/5.6/ja/check-table.html)
38
+ > FAST 正しく閉じられていないテーブルのみを検査します。
39
+
40
+ ---
41
+ 可能であれば、ストレージエンジンを InnoDB に変更することも検討してみてください。
42
+
43
+ InnoDB であれば統計情報は自動的に更新されます(※1)し、
44
+ ```sql
45
+ ALTER TABLE tb_test_data ENGINE InnoDB;
46
+ ```
47
+ という SQL で OPTIMIZE TABLE と同じことを、テーブルロック無しに実現できる(※2)からです。
48
+
49
+ ※1 [http://nippondanji.blogspot.jp/2010/09/innodb.html](http://nippondanji.blogspot.jp/2010/09/innodb.html)
50
+ > InnoDBの場合、ANALYZE TABLEは不要である。なぜなら、InnoDBが自発的に統計情報を更新するからだ。
51
+
52
+ ※2 同上
53
+ > InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。
54
+
55
+ ※2 [https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html](https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html)
56
+ > 並列 DML ではあるが、テーブルコピーが引き続き必要
57
+ (中略)
58
+ 「null」 ALTER TABLE ... ENGINE=INNODB ステートメントを使用したテーブルの再構築