MySQL千万数据count()优化：索引选择与性能测试

PDF文件

87KB | 更新于2024-08-30 | 176 浏览量 | 4 评论 | 举报收藏

立即下载

"本文探讨了MySQL中的count()函数在选择索引和进行大数据量查询时的优化策略，通过实例分析了不同情况下的性能表现。" 在MySQL数据库中，count()函数常用于统计表中的行数，对于千万级乃至更大规模的数据表，高效的count()查询尤为重要。以下是对文章内容的详细解析：一、前言文章作者在处理千万级数据表的count(*)查询时遇到了性能问题，从而引发对优化count(*)方法的研究。网上关于count(*)选择索引的常见观点有两种：一是基于索引长度，二是基于索引基数。文章接下来将通过实验验证这两个观点。二、测试索引长度和索引基数对count(*)查询的影响 1. 总数据量1100W+的表在未优化时，count(*)查询耗时9秒。 2. 查看默认使用的索引，以了解当前查询的执行路径。 3. 分析表的所有索引信息，包括索引长度和基数。 4. 强制使用基数较小的country字段作为索引，观察查询速度变化。 5. 结果表明，使用基数小的索引能显著提高count(*)查询速度。三、两千万的大表count(*)优化 1. 对于一个用户表(user)，首先展示默认count(*)的执行时间和效率。 2. 检查表的现有索引，以确定优化方向。 3. 实际测试不同索引下的count(*)查询性能，找到最佳选择。四、索引基数一致的情况下，选择使用哪个索引 1. 当多个索引的基数相同，系统会考虑索引长度。 2. 分析对应索引的长度，以理解数据库可能的选取依据。 3. 通过实际测试，揭示在基数相同情况下如何优化count(*)查询。五、为什么count(*)不用主键，或count(主键)速度并不快 1. 主键通常为唯一索引，其基数为表的行数，但索引长度可能较大，不利于count()操作。 2. 即使辅助索引和主键索引长度相等，count(*)也可能不会优先选择主键索引，因为系统会权衡基数和长度。六、总结 1. 结论：count(*)的选择可能基于索引长度和基数，优化策略应结合实际情况调整。 2. 其他建议：在无where条件的全表统计时，可考虑其他快速统计方法，如预先计算并存储总数。优化MySQL中的count(*)查询需要综合考虑索引的长度和基数，以及查询语句的上下文。在特定情况下，选择基数小的索引或优化查询条件可以显著提升查询性能。对于大规模数据表，定期更新统计数据或预计算总数也是提高效率的有效手段。

mysql的的count()函数如何选择索引，千万级表的函数如何选择索引，千万级表的count()查询查询

优化实例优化实例

文章目录文章目录一、前言1、网上的主要两种说法2、不贴出mysql版本的测试都是耍流氓~3、mysql的count(*)和count(1)二、测试索

引长度和索引基数对count(*)查询的影响1、总数据量1100W+ 表的速度2、默认使用的索引3、查看该表所有索引信息4、强制

选择基数最小的country字段5、强制使用基数最小索引的聚合查询速度三、两千万的大表count(*) 优化1、user表的数量级以

及默认count(*)的速度2、查看表索引3、实际使用的索引四、索引基数一致的情况下，会选择使用哪个索引呢1、索引基数一

致2、查看对应的索引长度3、实际的优化手段五、为什么count(*)不用主键，或者count(主键)速度并不快1、部分解释2、有人

可能会觉得，如果辅助索引长度和主键索引长度一样，那么count(*) 会不会使用PRIMARY索引呢？六、总结1、结论2、其他

（1）这些sql都是建立在没有where条件的基础上。（2）如果只是要手动统计一个大表有多少数据，可以采用另一种方式：

一、前言一、前言

博主今天在对一个千万级表进行count(*)查询的时候，发现速度有点慢，达到了9s，这对于程序来说是不可承受的，因此萌

生了优化count(*)查询的想法，这里记录一下。

1、网上的主要两种说法、网上的主要两种说法

（1）count(*) 函数会选择索引长度最短的字段

ps:索引长度指的是执行计划explain里面的key_len长度。

（2）count(*)函数会选择索引基数最小的字段

PS：索引基数其实就是说该字段在表中对应的不重复的记录值。

查询方式：

select count(distinct xxx) from xxx;

咱们这里主要就来讨论测试下这两种说法，并且找到适合千万级数据库查询的优化方式。

2、不贴出、不贴出mysql版本的测试都是耍流氓版本的测试都是耍流氓~

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

| 11793920 |

+----------+

1 row in set (9.10 sec)

3、、mysql的的count(*)和和count(1)

有很多文章都在说count(*)和count(1）的区别，还有文章说count(1)比count(*)速度快之类的，这里郑重说一下，对于目前的

的mysql(>=5.6)对于count(1)和count(*)，MySQL的优化是完全一样的，根本不存在谁更快，而且count(*)是SQL92定义的标准统计行数

的语法,使用使用count(*)准没错准没错。（PS：更早的版本可能会有一些区别）

二、测试索引长度和索引基数对二、测试索引长度和索引基数对count(*)查询的影响查询的影响

1、总数据量、总数据量1100W+ 表的速度表的速度

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

| 11793920 |

+----------+

1 row in set (9.10 sec)

查看count(*)速度并不是很理想

2、默认使用的索引、默认使用的索引

mysql> explain select count(*) from test;

+------+-------------+-------------+-------+---------------+-------------+---------+------+----------+-------------+

+------+-------------+-------------+-------+---------------+-------------+---------+------+----------+-------------+

+------+-------------+-------------+-------+---------------+-------------+---------+------+----------+-------------+

这里可以看到选用的索引长度很短，是个int类型的时间戳。那么不同索引字段的基数如何呢？

3、查看该表所有索引信息、查看该表所有索引信息

mysql> show index from test;

+-------------+------------+---------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Index_comment |

+-------------+------------+---------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

|test | 0 | PRIMARY | 1 | id | A | 10795387 | NULL | NULL | | BTREE | | |