英文:
How to increase select performance on a 15 million row MyISAM table converted to InnoDB
问题
以下是翻译好的内容：
MySQL版本 8.0.32-0ubuntu0.20.04.2
我试图提高SELECT性能，而不是规避进行SELECT。
CREATE TABLE big_table (
pk INT AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(255),
field2 VARCHAR(255),
field3 mediumtext,
field4 BIGINT,
KEY idx_field4 (field4)
) ENGINE=MyISAM CHARSET=utf8mb3;
插入1500万行。
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1行结果（0.57秒）
ALTER TABLE big_table ENGINE=INNODB;
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1行结果（10.23秒）
设置innodb_buffer_pool_size=8G（之前为128Mb）（重新启动MySQL）
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1行结果（1分18.67秒）
英文:
MySQL Version 8.0.32-0ubuntu0.20.04.2
I'm trying to improve SELECT performance, not circumvent doing a SELECT.
CREATE TABLE big_table (
pk INT AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(255),
field2 VARCHAR(255),
field3 mediumtext,
field4 BIGINT,
KEY idx_field4 (field4)
) ENGINE=MyISAM CHARSET=utf8mb3;
Insert 15 million rows.
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1 row in set (0.57 sec)
ALTER TABLE big_table ENGINE=INNODB;
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1 row in set (10.23 sec)
**Set innodb_buffer_pool_size=8G (Was 128Mb) (Restarted MySQL) **
SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk) |
+---------------+
| 15911974 |
+---------------+
1 row in set (1 min 18.67 sec)
答案1
得分: 1
以下是已翻译的内容：
原文：
It turns out that SELECT COUNT(PK) or SELECT COUNT(*) on a MyISAM table with no where clause is a very specific situation because it does not count each row and so is very fast. The same query on InnoDB is slow because it DOES count each row.
翻译：
事实证明，在没有 where 子句的情况下，在 MyISAM 表上执行 SELECT COUNT(PK) 或 SELECT COUNT(*) 是一个非常特殊的情况，因为它不会计算每一行，所以非常快速。在 InnoDB 上执行相同查询会很慢，因为它会计算每一行。
英文:
It turns out that SELECT COUNT(PK) or SELECT COUNT(*) on a MyISAM table with no where clause is a very specific situation because it does not count each row and so is very fast. The same query on InnoDB is slow because it DOES count each row.
However as soon as a where clause comes into play on an indexed field, the performance of InnoDB appears to outshine MyIsam. Seeing as this is 99% of use cases.....
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论