计算 MySQL 表行的成本是多少？

news/2025/2/9 1:44:43/

当计算表中的所有行时，将使用什么索引？好吧，MySQL文档文档对此提供了一个直接的答案，引用：

InnoDB 通过遍历最小的可用二级索引来处理 SELECT COUNT（*） 语句除非索引或优化器提示指示优化器使用
不同的索引。如果不存在二级索引，则 InnoDB 通过扫描聚集索引来处理 SELECT COUNT（*） 语句。

通过一个简单的 sysbench 表示例，首先让我们检查一下查询计划：

mysql > explain select count(*) from sbtest1;
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL | 986400 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

正如预期的那样，查询优化器选择了二级索引并打算对其执行扫描。运行查询将相应地增加处理程序，并在慢速日志中报告相同的情况：

mysql > flush status;
Query OK, 0 rows affected (0.00 mysql > select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.17 sec)mysql > show status like 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1       |
| Handler_read_last     | 0       |
| Handler_read_next     | 1000000 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 0       |
+-----------------------+---------+
7 rows in set (0.01 sec)

第一个惊喜是，在上述执行后，慢日志没有报告任何rows_examined：

# Time: 2025-02-07T10:14:28.548037Z
# User@Host: msandbox[msandbox] @ localhost []  Id:     9
# Schema: db1  Last_errno: 0  Killed: 0
# Query_time: 0.173279  Lock_time: 0.000006  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0  Bytes_sent: 62
use db1;
SET timestamp=1736696128;
select count(*) from sbtest1;

如果没有可用的辅助密钥怎么办？第二个示例表仅定义了主键：

mysql > desc sbtest2;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int       | NO   | PRI | NULL    | auto_increment |
| k     | int       | NO   |     | 0       |                |
| c     | char(120) | NO   |     |         |                |
| pad   | char(60)  | NO   |     |         |                |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)mysql > explain select count(*) from sbtest2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sbtest2 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 986400 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

第二个惊喜来了：处理程序不会改变！

mysql > flush status;
Query OK, 0 rows affected (0.00 sec)mysql > select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.04 sec)mysql > show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

在另一个表中，相同的查询成本是否会有所不同，这次没有定义任何索引

mysql > desc sbtest3;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int       | NO   |     | 0       |       |
| k     | int       | NO   |     | 0       |       |
| c     | char(120) | NO   |     |         |       |
| pad   | char(60)  | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql > explain select count(*) from sbtest3;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | sbtest3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 985734 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql > select count(*) from sbtest3;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.05 sec)mysql > show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

除了观察执行时间（与具有主键的表相当）之外，很难判断，因为状态处理程序既不会递增，也不会从慢速日志中Rows_examined信息。此外，全局 InnoDB 计数器（如 Innodb_rows_read）以及性能架构表统计信息在使用聚集索引（显式或隐式）进行计数时也不会更改！

mysql > select id from sbtest1 limit 1; select id from sbtest2 limit 1; select id from sbtest3 limit 1;
+--------+
| id     |
+--------+
| 731065 |
+--------+
1 row in set (0.00 sec)+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)mysql > select table_schema,table_name,rows_fetched from sys.schema_table_statistics where table_schema="db1";
+--------------+------------+--------------+
| table_schema | table_name | rows_fetched |
+--------------+------------+--------------+
| db1          | sbtest1    |            1 |
| db1          | sbtest2    |            1 |
| db1          | sbtest3    |            1 |
+--------------+------------+--------------+
3 rows in set (0.01 sec)mysql > select count(*) from sbtest1; select count(*) from sbtest2; select count(*) from sbtest3;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.21 sec)+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)mysql > select table_schema,table_name,rows_fetched from sys.schema_table_statistics where table_schema="db1";
+--------------+------------+--------------+
| table_schema | table_name | rows_fetched |
+--------------+------------+--------------+
| db1          | sbtest1    |      1000001 |
| db1          | sbtest2    |            1 |
| db1          | sbtest3    |            1 |
+--------------+------------+--------------+
3 rows in set (0.01 sec)

当不涉及辅助键时，在上面的运行中显然会被忽略！那么我们如何检查在 count 查询执行期间实际读取了什么索引，以及必须获取多少数据呢？我只能想到一种方法 – 在新重新启动的空闲服务器上，我们可以检查 Buffer Pool 内容（前提innodb_buffer_pool_load_at_startup = OFF）

建议只在空闲时间进行检查！！！！！

mysql > select TABLE_NAME,INDEX_NAME,count(*) from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where TABLE_NAME like "`db1`.%" GROUP BY TABLE_NAME,INDEX_NAME;
Empty set (0.19 sec)mysql > select count(*) from sbtest1; select count(*) from sbtest2; select count(*) from sbtest3;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.22 sec)+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.20 sec)+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.87 sec)mysql > select TABLE_NAME,INDEX_NAME,count(*) from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where TABLE_NAME like "`db1`.%" GROUP BY TABLE_NAME,INDEX_NAME;
+-----------------+-----------------+----------+
| TABLE_NAME      | INDEX_NAME      | count(*) |
+-----------------+-----------------+----------+
| `db1`.`sbtest1` | k_1             |      833 |
| `db1`.`sbtest2` | PRIMARY         |    13713 |
| `db1`.`sbtest3` | GEN_CLUST_INDEX |    14302 |
+-----------------+-----------------+----------+
3 rows in set (0.26 sec)

根据上述结果，我们可以看到加载了多少个索引页面来执行计数，其中小型二级索引胜出，因为它不包含数据。在这种情况下，它并没有直接转化为执行时间，具体原因等我明白了再给大家开篇贴来说


http://www.ppmy.cn/news/1570473.html

相关文章

QUIC 与 UDP 关系

QUIC 与 UDP 关系

QUIC协议是建立在UDP之上的，这意味着QUIC的数据包实际上是通过UDP传输的。QUIC的设计使其能够利用UDP的特性，同时在其上实现更复杂的功能。以下是QUIC如何体现出其基于UDP的特性，以及QUIC头部字段的详细介绍。 QUIC与UDP的关系 UDP封装：QUIC数据包被封装在UDP数据包中进行…
阅读更多...
【IoCDI】_Spring的基本扫描机制

【IoCDI】_Spring的基本扫描机制

目录 1. 创建测试项目 2. 改变启动类所属包 3. 使用ComponentScan 4. Spring基本扫描机制 程序通过注解告诉Spring希望哪些bean被管理，但在仅使用Bean时已经发现，Spring需要根据五大类注解才能进一步扫描方法注解。 由此可见，Spring对注…
阅读更多...
ChatGPT提问技巧：行业热门应用提示词案例--咨询法律知识

ChatGPT提问技巧：行业热门应用提示词案例--咨询法律知识

ChatGPT除了可以协助办公，写作文案和生成短视频脚本外，和还可以做为一个法律工具，当用户面临一些法律知识盲点时，可以向ChatGPT咨询获得解答。赋予ChatGPT专家的身份，用户能够得到较为满意的解答。 1.咨询法律知识 举…
阅读更多...
Redis企业开发实战(三)——点评项目之优惠券秒杀

Redis企业开发实战(三)——点评项目之优惠券秒杀

目录 一、全局唯一ID (一)概述 (二)全局ID生成器 (三)全局唯一ID生成策略 1. UUID (Universally Unique Identifier) 2. 雪花算法（Snowflake） 3. 数据库自增 4. Redis INCR/INCRBY 5.总结 (四)Redis实现全局唯一ID 1.工具类 2.测试类 3…
阅读更多...
预训练语言模型（笔记）

预训练语言模型（笔记）

笔记来源：Transformer、GPT、BERT，预训练语言模型的前世今生（目录） - B站-水论文的程序猿 - 博客园 预训练语言模型的发展并不是一蹴而就的，而是伴随着诸如词嵌入、序列到序列模型及 Attention 的发展而产生的。 一、…
阅读更多...
7.PPT：“中国梦”学习实践活动【20】

7.PPT：“中国梦”学习实践活动【20】

目录 NO1234​ NO5678​ NO9\10\11 NO1234 考生文件夹下创建一个名为“PPT.pptx”的新演示文稿Word素材文档的文字：复制/挪动→“PPT.pptx”的新演示文稿（蓝色、黑色、红色） 视图→幻灯片母版→重命名：“中国梦母版1”→背景样…
阅读更多...
【Elasticsearch】Global 聚合

【Elasticsearch】Global 聚合

7. 总结全局聚合是 Elasticsearch 中一个非常强大的工具，它允许你在执行特定查询的同时，对整个数据集进行统计分析。通过合理使用全局聚合，可以实现多维度的数据分析，帮助你更好地理解数据的整体情况和特定条件下的差异。希望这些…
阅读更多...
Facebook矩阵营销：多维度布局，精准打击

Facebook矩阵营销：多维度布局，精准打击

随着社交媒体的迅猛发展，企业和品牌在数字营销中的竞争愈发激烈。Facebook，作为全球最大的社交平台之一，已成为了品牌推广的关键阵地之一。然而，仅仅依靠单一的Facebook页面进行营销已经无法满足品牌发展的需求，如何通…
阅读更多...
最新文章

Copyright @ 2022~2023