MySQLのEXPLAIN結果がどんどん変わっていく例

MySQLのEXPLAINが直感とは異なっていた事例 の続き。ふと実験中にEXPLAINを打っていたら、 rows が減っていることに気づいたのでさらに追試をした。

実験

MySQLのEXPLAINが直感とは異なっていた事例 の実験で、SELECT文にかかった秒数に加えて、EXPLAINの rows の値も測定する。MySQL 8.0で実験した。

結果

*1

mysql explain test rows - Google スプレッドシート

考察

• インデックスなしの場合、常に rows は50
• インデックスありの場合
  • done = TRUE な行数が14万を超えるまでは、 rows は一定
  • 14万を超えたあたりから、振動しつつも減少傾向になる
  • 全ての行が done = TRUE になったら rows は1になる
• (Cardinality低, Cardinality高) というインデックスを貼るとこういうことが起こりうるのだろうか

SHOW INDEXES を見る

mysql> SHOW INDEXES FROM tbl_with_index;
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table          | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tbl_with_index |          0 | PRIMARY     |            1 | id          | A         |      998568 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tbl_with_index |          1 | done_and_id |            1 | done        | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tbl_with_index |          1 | done_and_id |            2 | id          | A         |      998568 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

• 998568 / 2 = 499284 で、この値は done = TRUE な行数が14万を超えたあたりのEXPLAINの rows の値に一致する
  • done は BOOLEAN なので、EXPLAINすると最悪ケースでテーブルの行数のおよそ半分ぐらいをなめることになる、と予測されている？

*1:縦軸のスケール感をうまく調整したい