| Bug #119480 | Full table scans on primary keys, MySQL 8.0 performance degradation than MySQL 5.7. | ||
|---|---|---|---|
| Submitted: | 1 Dec 9:38 | Modified: | 3 Dec 9:42 |
| Reporter: | haizhen xue | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[1 Dec 14:12]
Øystein Grøvlen
Hi, and thank you for your report.
I see a smaller regression (9%) between 5.7 and 8.0, but there is no significant difference between 5.7 and 8.4 or 9.5:
mysql [localhost:5736] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (2.98 sec)
mysql [localhost:8044] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (3.25 sec)
mysql [localhost:8407] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (3.00 sec)
mysql [localhost:9500] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (3.008 sec)
In my case, all data was in memory. What is your setting for innodb_buffer_pool_size? It would also be useful to see the result of the following query for both versions:
select * from mysql.innodb_index_stats where table_name = 'leads_discuss';
[2 Dec 6:42]
haizhen xue
between 5.7 and 8.0, it has a regression (12.4%) when all data was in memory. There is a question: why is the value of Innodb_buffer_pool_read_requests much lower in MySQL 8.0 compared to MySQL 5.7? Before and after executing SQL statements, the increase in the value of Innodb_buffer_pool_read_requests in MySQL 8.0 is much smaller compared to MySQL 5.7. 8.0.44: mysql> select * from mysql.innodb_index_stats where table_name = 'leads_discuss'; +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | leads_discuss | PRIMARY | 2025-12-02 10:59:23 | n_diff_pfx01 | 9252244 | 20 | id | | test | leads_discuss | PRIMARY | 2025-12-02 10:59:23 | n_leaf_pages | 98063 | NULL | Number of leaf pages in the index | | test | leads_discuss | PRIMARY | 2025-12-02 10:59:23 | size | 98176 | NULL | Number of pages in the index | | test | leads_discuss | idx_discussType | 2025-12-02 10:59:23 | n_diff_pfx01 | 4 | 6 | discuss_type | | test | leads_discuss | idx_discussType | 2025-12-02 10:59:23 | n_diff_pfx02 | 9566356 | 20 | discuss_type,id | | test | leads_discuss | idx_discussType | 2025-12-02 10:59:23 | n_leaf_pages | 7966 | NULL | Number of leaf pages in the index | | test | leads_discuss | idx_discussType | 2025-12-02 10:59:23 | size | 8175 | NULL | Number of pages in the index | | test | leads_discuss | idx_leadsId | 2025-12-02 10:59:23 | n_diff_pfx01 | 103845 | 20 | leads_id | | test | leads_discuss | idx_leadsId | 2025-12-02 10:59:23 | n_diff_pfx02 | 8975237 | 20 | leads_id,id | | test | leads_discuss | idx_leadsId | 2025-12-02 10:59:23 | n_leaf_pages | 11752 | NULL | Number of leaf pages in the index | | test | leads_discuss | idx_leadsId | 2025-12-02 10:59:23 | size | 13489 | NULL | Number of pages in the index | +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 11 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 1264917 | | Innodb_buffer_pool_read_ahead_evicted | 3798 | | Innodb_buffer_pool_read_requests | 104072442 | | Innodb_buffer_pool_reads | 73861 | +---------------------------------------+-----------+ 5 rows in set (0.01 sec) mysql> SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000; +---------------+ | max(leads_id) | +---------------+ | 100000 | +---------------+ 1 row in set (3.70 sec) mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 1264917 | | Innodb_buffer_pool_read_ahead_evicted | 3798 | | Innodb_buffer_pool_read_requests | 104201092 | | Innodb_buffer_pool_reads | 73861 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) 5.7.44: mysql> select * from mysql.innodb_index_stats where table_name = 'leads_discuss'; +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | leads_discuss | PRIMARY | 2025-12-02 14:24:46 | n_diff_pfx01 | 9964773 | 20 | id | | test | leads_discuss | PRIMARY | 2025-12-02 14:24:46 | n_leaf_pages | 103155 | NULL | Number of leaf pages in the index | | test | leads_discuss | PRIMARY | 2025-12-02 14:24:46 | size | 103360 | NULL | Number of pages in the index | | test | leads_discuss | idx_discussType | 2025-12-02 14:24:46 | n_diff_pfx01 | 4 | 6 | discuss_type | | test | leads_discuss | idx_discussType | 2025-12-02 14:24:46 | n_diff_pfx02 | 10062452 | 20 | discuss_type,id | | test | leads_discuss | idx_discussType | 2025-12-02 14:24:46 | n_leaf_pages | 8377 | NULL | Number of leaf pages in the index | | test | leads_discuss | idx_discussType | 2025-12-02 14:24:46 | size | 8558 | NULL | Number of pages in the index | | test | leads_discuss | idx_leadsId | 2025-12-02 14:24:46 | n_diff_pfx01 | 104706 | 20 | leads_id | | test | leads_discuss | idx_leadsId | 2025-12-02 14:24:46 | n_diff_pfx02 | 9265788 | 20 | leads_id,id | | test | leads_discuss | idx_leadsId | 2025-12-02 14:24:46 | n_leaf_pages | 13290 | NULL | Number of leaf pages in the index | | test | leads_discuss | idx_leadsId | 2025-12-02 14:24:46 | size | 15217 | NULL | Number of pages in the index | +---------------+---------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 11 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 1066953 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 126336696 | | Innodb_buffer_pool_reads | 52346 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000; +---------------+ | max(leads_id) | +---------------+ | 100000 | +---------------+ 1 row in set (3.29 sec) mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 1066953 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 127606222 | | Innodb_buffer_pool_reads | 52346 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec)
[3 Dec 9:42]
Øystein Grøvlen
If I set innodb_buffer_pool_size to 1GB (previously it was 128 GB), 8.4 and 9.5 are even slower than 8.0:
mysql [localhost:5736] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (4.16 sec)
mysql [localhost:8044] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (4.58 sec)
mysql [localhost:8407] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (5.28 sec)
mysql [localhost:9500] {msandbox} (bug119480) > SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;
+---------------+
| max(leads_id) |
+---------------+
| 100000 |
+---------------+
1 row in set (5.214 sec)
Description: table: CREATE TABLE `leads_discuss` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '??ID', `discuss_uuid` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??UUID', `leads_id` int NOT NULL COMMENT '????ID', `discuss_type` int NOT NULL COMMENT '????', `discuss_classify` int DEFAULT NULL COMMENT '????', `start_time` datetime DEFAULT NULL COMMENT '????', `end_time` datetime DEFAULT NULL COMMENT '????', `next_time` datetime DEFAULT NULL COMMENT '????', `content` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `is_test_drive` int DEFAULT NULL COMMENT '?????0-? 1-?', `address` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `remarks` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `customer_name` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '????', `customer_phone` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '?????', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '????', PRIMARY KEY (`id`), KEY `idx_leadsId` (`leads_id`) USING BTREE, KEY `idx_discussType` (`discuss_type`) ) ENGINE=InnoDB AUTO_INCREMENT=10464481 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='???????'; MySQL 8.0: mysql> SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000; +---------------+ | max(leads_id) | +---------------+ | 100000 | +---------------+ 1 row in set (5.97 sec) MySQL 5.7: mysql> SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000; +---------------+ | max(leads_id) | +---------------+ | 100000 | +---------------+ 1 row in set (4.60 sec) How to repeat: 1. create table: CREATE TABLE `leads_discuss` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '??ID', `discuss_uuid` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??UUID', `leads_id` int NOT NULL COMMENT '????ID', `discuss_type` int NOT NULL COMMENT '????', `discuss_classify` int DEFAULT NULL COMMENT '????', `start_time` datetime DEFAULT NULL COMMENT '????', `end_time` datetime DEFAULT NULL COMMENT '????', `next_time` datetime DEFAULT NULL COMMENT '????', `content` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `is_test_drive` int DEFAULT NULL COMMENT '?????0-? 1-?', `address` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `remarks` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '??', `customer_name` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '????', `customer_phone` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '?????', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '????', PRIMARY KEY (`id`), KEY `idx_leadsId` (`leads_id`) USING BTREE, KEY `idx_discussType` (`discuss_type`) ) ENGINE=InnoDB AUTO_INCREMENT=10464481 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='???????'; 2. insert data: DELIMITER $$ CREATE PROCEDURE InsertLeadsDiscussDataOptimized() BEGIN DECLARE i INT DEFAULT 0; DECLARE batch_size INT DEFAULT 500; -- 减小批次大小 DECLARE total_batches INT DEFAULT CEIL(10459981 / 500); DECLARE current_batch INT DEFAULT 0; WHILE current_batch < total_batches DO START TRANSACTION; INSERT INTO leads_discuss ( discuss_uuid, leads_id, discuss_type, discuss_classify, start_time, end_time, next_time, content, is_test_drive, address, remarks, customer_name, customer_phone, create_time ) VALUES -- 动态生成500条数据 ( UUID(), FLOOR(1 + RAND() * 100000), FLOOR(1 + RAND() * 5), CASE WHEN RAND() > 0.3 THEN FLOOR(1 + RAND() * 3) ELSE NULL END, NOW() - INTERVAL FLOOR(RAND() * 365) DAY, CASE WHEN RAND() > 0.2 THEN NOW() - INTERVAL FLOOR(RAND() * 300) DAY ELSE NULL END, CASE WHEN RAND() > 0.3 THEN NOW() + INTERVAL FLOOR(RAND() * 30) DAY ELSE NULL END, CONCAT('讨论内容_', current_batch * batch_size + 1), CASE WHEN RAND() > 0.2 THEN FLOOR(RAND() * 2) ELSE NULL END, CONCAT('地址', FLOOR(1 + RAND() * 1000)), CASE WHEN RAND() > 0.4 THEN CONCAT('备注', current_batch * batch_size + 1) ELSE '' END, CONCAT('客户', FLOOR(1 + RAND() * 5000)), CONCAT('138', LPAD(FLOOR(RAND() * 100000000), 8, '0')), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); -- 使用存储过程循环插入剩余499条 SET @j = 1; WHILE @j < batch_size DO INSERT INTO leads_discuss ( discuss_uuid, leads_id, discuss_type, discuss_classify, start_time, end_time, next_time, content, is_test_drive, address, remarks, customer_name, customer_phone, create_time ) SELECT UUID(), FLOOR(1 + RAND() * 100000), FLOOR(1 + RAND() * 5), CASE WHEN RAND() > 0.3 THEN FLOOR(1 + RAND() * 3) ELSE NULL END, NOW() - INTERVAL FLOOR(RAND() * 365) DAY, CASE WHEN RAND() > 0.2 THEN NOW() - INTERVAL FLOOR(RAND() * 300) DAY ELSE NULL END, CASE WHEN RAND() > 0.3 THEN NOW() + INTERVAL FLOOR(RAND() * 30) DAY ELSE NULL END, CONCAT('讨论内容_', current_batch * batch_size + @j + 1), CASE WHEN RAND() > 0.2 THEN FLOOR(RAND() * 2) ELSE NULL END, CONCAT('地址', FLOOR(1 + RAND() * 1000)), CASE WHEN RAND() > 0.4 THEN CONCAT('备注', current_batch * batch_size + @j + 1) ELSE '' END, CONCAT('客户', FLOOR(1 + RAND() * 5000)), CONCAT('139', LPAD(FLOOR(RAND() * 100000000), 8, '0')), NOW() - INTERVAL FLOOR(RAND() * 365) DAY; SET @j = @j + 1; END WHILE; COMMIT; SET current_batch = current_batch + 1; SET i = i + batch_size; -- 每1万条输出一次进度 IF current_batch % 20 = 0 THEN SELECT CONCAT('进度: ', i, '/10459981 (', ROUND(i/10459981*100, 2), '%)') AS progress; END IF; END WHILE; SELECT CONCAT('数据插入完成! 总共插入: ', i, ' 条记录') AS completion_message; END$$ DELIMITER ; call InsertLeadsDiscussDataOptimized(); 3. execute sql: SELECT max(leads_id) FROM leads_discuss l FORCE INDEX (PRIMARY) WHERE l.id < 100000000;