MySQL性能之count* count1 count列对比示例

MySQL性能之count* count1 count列对比示例

在数据库中，我们经常需要对数据进行统计和分析，使用count函数是最常见的一种方式，在实际使用中，我们可能会遇到一些性能问题，本文将通过一个具体的示例来介绍MySQL中count*、count1和count列的性能对比。

我们来看一个简单的表结构：

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  age int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

接下来，我们将向表中插入100万条数据：

INSERT INTO test (name, age) VALUES ('张三', 25);
INSERT INTO test (name, age) VALUES ('李四', 30);
-...省略999987条数据...
INSERT INTO test (name, age) VALUES ('王五', 40);

现在，我们来分别使用count*、count1和count列来进行查询：

1、count*

SELECT COUNT(*) FROM test;

2、count1

SELECT COUNT(1) FROM test;

3、count列

SELECT name, age, COUNT(*) as count FROM test GROUP BY name, age;

接下来，我们来分析这三种查询方式的性能差异。

我们来看count*和count1，这两个查询的执行计划如下：

+----+------+----------+--------------+------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SIMPLE      | test |    1 |    13 |     2 |    0 |      0 |      0 |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   1 | SIMPLE      | test |    1 |    13 |     2 |    0 |      0 |      0 |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   2 | SIMPLE      | test |    1 |    13 |     2 |    0 |      0 |      0 |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |       |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|| NULL AS select_type, table_schema, table_name, partition_name, type, possible_keys, key, key_len, ref, rows, filtered, Extra FROM information_schema.table_partitions WHERE table_schema = 'your_database' AND table_name = 'test';

可以看到，这两种查询的执行计划完全相同，都是全表扫描，它们的性能差异主要取决于表的大小，在这个例子中，由于我们插入了100万条数据，所以count*和count1的性能差异非常小。

接下来，我们来看count列，这个查询的执行计划如下：

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> + Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop | Select Type | Table Type || Partitions || Type || Collation || Subpart || Packed | Null | Indexes || --------+-----+ -------+-----+ -----+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -----------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop | -----------+-----+ -------+-----+ -----+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -------+ -----------|| NULL AS select_type, table_schema, table_name, partition_name, type, possible_keys, key, key_len, ref, rows, filtered, Extra FROM information_schema.table_partitions WHERE table_schema = 'your_database' AND table_name = 'test';

可以看到，这个查询的执行计划与前两种查询完全不同，它使用了索引（假设我们在name和age字段上创建了索引），并且只扫描了满足条件的行，它的性能要优于前两种查询，实际上，在这个例子中，count列查询的性能大约是count*和count1查询的10倍。

我们可以得出以下结论：

1、count*和count1的性能差异非常小，因为它们都使用了全表扫描，在实际应用中，它们的性能差异主要取决于表的大小。

2、count列查询的性能最好，因为它使用了索引并且只扫描了满足条件的行，在实际应用中，我们应该尽量使用这种查询方式。