SRGについて技術記事採用情報
SRGについて技術記事採用情報
HOME/技術記事/MySQL8.0で低速になったSELECT COUNTを高速化する

MySQL8.0で低速になったSELECT COUNTを高速化する

メディア統括本部 サービスリライアビリティグループ（SRG）の鬼海雄太（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
本記事では、MySQL8.0で遅くなったSELECT COUNTをバージョンアップなしで高速化する対応法についてまとめています。
なにかの役に立てば幸いです。
 

MySQL8.0のSELECT COUNT低速化問題

MySQL8.0ではSELECT COUNTが非常に低速になる場合があり、
MySQL5.7と比べて数十倍遅くなるケースも出てきています。
 
MySQL 5.7.41でのSELECT COUNT結果
 
MySQL8.0.28でのSELECT COUNT結果
この問題がMySQL8.0.37以降(MySQL8.4 , MySQL9.0含む)では解消されました。
しかし、Aurora MySQL Version3を使っている場合など、まだ最新のMySQLにアップグレードすることができないという環境は多いと思います。
 

SELECT COUNTの低速化の根本原因

改めてMySQL8.0.37のリリースノートを見てみます。
InnoDB:
MySQL no longer ignores the optimizer hint to use a secondary index scan, which instead forced a clustered (parallel) index scan. In addition, added the ability to provide an index hint that forces use of a clustered index. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353)
📕
InnoDB: MySQLは、セカンダリインデックススキャンを使用するというオプティマイザーヒントを無視しなくなりました。これにより、代わりにクラスタ化（並列）インデックススキャンが強制されていました。(バグ#100597、バグ#112767、バグ#31791868、バグ#35952353)
 

バグ番号：100597

これはSELECT COUNTにおいて、ヒント句をつけてもセカンダリインデックスが使用されずにクラスタ化インデックス（PKのインデックス）が利用されてしまうというバグでした。
EXPLAINの結果ではセカンダリインデックスが使われるように見えるが、実際は使われておらず、WHERE句を追加したときのみ使用されたという報告がされていました。
 

バグ番号：112767

並列読み取りスレッドのページを事前に読み込む機能が、クラスタ化インデックスにおいて必要以上にページを読み込んでしまい、過剰な読み取りI/Oを引き起こしてSELECT COUNTのパフォーマンスが悪化しているということがわかりました。
 
SELECT COUNTでクラスタ化インデックス(PKインデックス)の並列スキャンを利用させないようにすれば、SELECT COUNT低速化を回避可能であると言えそうです。
  • ヒント句(USE INDEXやFORCE INDEX)でセカンダリインデックスを指定必須
  • WHERE句での条件必須
 

高速化の検証

 

検証テーブル(100万レコード)

MySQL5.7のCOUNT結果

でした。これを基準とします。
 

MySQL8.0のCOUNT結果

もかかりました。20倍程度遅くなっています。
 
EXPLAINしてみると、Keyは「」になっています。
 
インデックスの統計情報とバッファプールの使用状況を確認してみます。
バグレポートにあった通り実際は「」は利用されておらず、が利用されていることがわかります。
上記のクエリは、mysql.innodb_index_statsとinformation_schema.innodb_buffer_pageの内容を確認しています。
  • mysql.innodb_index_stats
    • InnnoDBインデックスの統計情報
  • information_schema.innodb_buffer_page
    • バッファプールのページ情報
指定テーブルのインデックスごとにバッファプール内のページ数を集計することで、インデックスが実際に利用されてバッファプール内にデータが載っているかを確認しています。
 
念の為、FORCE INDEXでを強制してみても低速なままで、バッファプールにもしか記録されていません。
 

MySQL8.0のCOUNTにWHERE句でid > 0の条件追加

WHERE句条件をつけて実行してみると、若干ですが早くなりました。
 
EXPLAINの結果も見てみます。Keyはになりました。
 
もちろんインデックス統計情報とバッファプールに変化はありません。

MySQL8.0のCOUNTにWHERE句でid > 0の条件追加 + FORCE INDEXで「idx」を指定

FORCE INDEXを追加すると、0.85秒まで早くなりました。
 
EXPLAINを確認してみると、Keyに「」が利用されていて、Extraに「」が増えています。
 
インデックスの統計情報とバッファプールの使用状況を確認してみます。
ここではじめて「」が利用されたことがわかりました。
 
はのインデックスです。WHEREにid > 0を指定していることで、スキップスキャンが使われています。
この部分をさらに改善してみます。
 

【解決】MySQL8.0のCOUNTに適切なWHERE句条件 + FORCE INDEXで適切なインデックスを指定

このサンプルテーブルの例では、以下の2パターンが考えられます。
  • WHERE句の条件をではなく、に変更する。（フルインデックススキャン） で最速となりました。MySQL5.7の0.37秒にだいぶ近づきました。
     
    • WHERE句は変えずに新規インデックスの(id,col_idx)を用意して、そちらを使うようにする（レンジスキャン） となりました。今回のデータの例ではより単純なフルインデックススキャンのほうが高速だったということです。

      終わりに

      遅くなってしまったSELECT COUNTも、なんとかすれば救える可能性があることがわかりました。
      MySQL8.0を最新までバージョンアップをすることができれば解決できるのですが、
      Aurora MySQLなどを含め様々な理由で最新のMySQL8.0を利用できない人も多いかと思います。
      そんな方に今回の情報が届けば良いなと思います！
      SRG では一緒に働く仲間を募集しています。 ご興味ありましたらぜひこちらからご連絡ください。
       
      このエントリーをはてなブックマークに追加
      ▲