MySQL8.0でSELECT COUNT(*)が低速になる動作は8.0.37で解消されていた！

投稿日

2024/6/13

タグ

MySQL

Aurora

執筆者

鬼海雄太

MySQL8.0ではSELECT COUNT(*)が低速になる

MySQL8.0.37で低速なのが解消された！？

[30 Apr 16:31] MySQL Verification Team

Thank you for the feedback and test case.
Degradation observed by you could be because the select count(*) must be using the clustered index instead of secondary index.
We have fixed this problem in 8.0.37 throughBug #112767 SELECT COUNT(*) degraded performance on 8.0 compared to 5.7 .  Please give it a try with 8.0.37 and report a new bug if you still face the problem.
Plain Text

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) References: This issue is a regression of: Bug #12978.

動作検証

DROP SCHEMA IF EXISTS example;
CREATE SCHEMA example;
USE example;

DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(
    id     INT AUTO_INCREMENT,
    col_idx VARCHAR(28),
    col_data  LONGTEXT,
    PRIMARY KEY (id),
    KEY `idx` (`col_idx`)
) CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

DROP PROCEDURE IF EXISTS fill_data;
DELIMITER //

CREATE PROCEDURE fill_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000
        DO
            INSERT INTO table1 (col_idx, col_data)
            VALUES (REPEAT('a', 28),
                    REPEAT('a', 5000));
            SET i = i + 1;
        END WHILE;
END
//
DELIMITER ;
SET AUTOCOMMIT = true;
call fill_data();
SQL

$ mysql -uroot -e "SELECT VERSION();"
+------------+
| VERSION()  |
+------------+
| 5.7.41-log |
+------------+

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)
SQL

$ mysql -uroot -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.0.28    |
+-----------+

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.82 sec)
SQL

$ mysql -uroot -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.0.36    |
+-----------+

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.90 sec)
SQL

$ mysql -uroot -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.0.37    |
+-----------+

mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)
SQL

mysql> select count(*) from entry;
+----------+
| count(*) |
+----------+
|  1473385 |
+----------+
1 row in set (38.94 sec)
SQL

$ mysql -uroot -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.0.37    |
+-----------+

mysql> select count(*) from entry;
+----------+
| count(*) |
+----------+
|  1473385 |
+----------+
1 row in set (0.63 sec)
SQL

LTS MySQL8.4ではどうなの？

[16 Feb 22:07] Philip Olson

Posted by developer:

Fixed as of the upcoming MySQL Server 8.0.37 and 8.4.0 releases, and here's the proposed changelog entry from the documentation team:

A SELECT COUNT(*) query that used a secondary index for scanning would
perform much slower than the same query did in MySQL 5.7.

Thank you for the bug report.
Plain Text

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)

mysql -uroot -e "SELECT VERSION();"
+-----------+
| VERSION() |
+-----------+
| 8.4.0     |
+-----------+

mysql> SELECT COUNT(*) FROM table1;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.03 sec)
SQL

終わりに