Home > MySQLチューニング > InnoDB vs MyISAM パフォーマンス 副問い合わせ

InnoDB vs MyISAMシリーズでブログ風のテーブル設計にして、
副問い合わせのパフォーマンスを試してみました。

測定環境についてはこちらを参照して下さい。

・データ件数は100万件
・こちらの構造のテーブルをInnoDBとMyISAMで作成して処理速度を比較
・entryとentry2は記事を格納するテーブルで100万件登録されています。
・entry_commentとentry_comment2はコメントを格納するテーブルで、こちらも100万件登録されています。
・PRIMARY KEYであるidには1～1000000の値がセットされています。
・1記事に対して、1コメントが存在しています。・以下の構造のテーブルをInnoDBとMyISAMで作成して処理速度を比較

以下SQL中の「テーブル名 entryとentry_comment」にはMyISAMなら entry2とentry_comment2 がセットされます。

副問い合わせのパフォーマンス比較用のＳＱＬ

PLAIN TEXT
SQL:
  1. SELECT  e.id, e.STATUS,
  2. e.category_id,
  3. e.user_type,
  4. e.name,
  5. e.favorites,
  6. e.comments,
  7. e.trackbacks,
  8. e.favorite_permission,
  9. e.comment_permission,
  10. e.comment_captcha_permission,
  11. e.trackback_permission,
  12. e.edited,
  13. e.image,
  14. e.caption,
  15. e.user_name,
  16. e.created,
  17. e.modified,
  18. e.removed,
  19. e.title,
  20. e.body,
  21. s.max_created AS max_created
  22. FROM entry e, (SELECT c.entry_id,
  23. max(c.created) AS max_created
  24. FROM entry_comment c
  25. WHERE c.user_name = %s
  26. AND c.removed IS NULL
  27. GROUP BY c.entry_id) s
  28. WHERE e.id = s.entry_id
  29. AND e.removed IS NULL
  30. AND ( e.STATUS = 1 OR  e.STATUS = 2)
  31. ORDER BY s.max_created

※%sはランダムに変化します。

InnoDBの実行計画(explainの結果）

PLAIN TEXT
SQL:
  1. *************************** 1. row ***************************
  2. id: 1
  3. select_type: PRIMARY
  4. TABLE: <derived2>
  5. type: system
  6. possible_keys: NULL
  7. KEY: NULL
  8. key_len: NULL
  9. ref: NULL
  10. rows: 1
  11. Extra:
  12. *************************** 2. row ***************************
  13. id: 1
  14. select_type: PRIMARY
  15. TABLE: e
  16. type: const
  17. possible_keys: PRIMARY
  18. KEY: PRIMARY
  19. key_len: 4
  20. ref: const
  21. rows: 1
  22. Extra:
  23. *************************** 3. row ***************************
  24. id: 2
  25. select_type: DERIVED
  26. TABLE: c
  27. type: ref
  28. possible_keys: entry_comment_idx2,entry_comment_idx3,entry_comment_idx4
  29. KEY: entry_comment_idx2
  30. key_len: 204
  31. ref:
  32. rows: 1
  33. Extra: USING WHERE; USING TEMPORARY; USING filesort</derived2>

MyISAMの実行計画(explainの結果）

PLAIN TEXT
SQL:
  1. *************************** 1. row ***************************
  2. id: 1
  3. select_type: PRIMARY
  4. TABLE: <derived2>
  5. type: system
  6. possible_keys: NULL
  7. KEY: NULL
  8. key_len: NULL
  9. ref: NULL
  10. rows: 0
  11. Extra: const row NOT found
  12. *************************** 2. row ***************************
  13. id: 1
  14. select_type: PRIMARY
  15. TABLE: e
  16. type: eq_ref
  17. possible_keys: PRIMARY
  18. KEY: PRIMARY
  19. key_len: 4
  20. ref: const
  21. rows: 1
  22. Extra: USING WHERE
  23. *************************** 3. row ***************************
  24. id: 2
  25. select_type: DERIVED
  26. TABLE: c
  27. type: ref
  28. possible_keys: entry_comment_idx2,entry_comment_idx3,entry_comment_idx4
  29. KEY: entry_comment_idx2
  30. key_len: 204
  31. ref:
  32. rows: 1
  33. Extra: USING WHERE; USING TEMPORARY; USING filesort</derived2>

2.rowの表現が食い違っています。
InnoDBは　type: const　ですが、MyISAMは　type: eq_ref　となっています。
ただ、両方ともに　key: PRIMARY　で、　ref: const　なので問題ないようです。

計測結果

スレッド数 MyISAM InnoDB

副問い合わせ


2～8同時スレッドのピーク時性能で15～20%程度、InnoDBのほうが高速のようです。
同時スレッド数に対するスケーラビリィティは、これまで見てきた通り、MyISAMが優秀で、
おそらく128同時スレッド数あたりでは、優劣が逆転すると思います。

次回はInnerJoinを試してみます。