我使用一下方式查询,好像是用count(主建),所花的时间更长一些,这样不就更慢了吗,不知道有没有好的办法啊 mysql> select count(*) from customer; +----------+ | count(*) | +----------+ | 3955 | +----------+ 1 row in set (0.00 sec)mysql> select count(id) from customer; +-----------+ | count(id) | +-----------+ | 3955 | +-----------+ 1 row in set (0.02 sec)mysql> select count(code) from customer; +-------------+ | count(code) | +-------------+ | 3955 | +-------------+ 1 row in set (0.02 sec)
索引情况、主键情况,一般情况下,count(主建)速度>count(*)
可以啊,INSERT INTO TT(ZS) select count(id) from customer;
show index from customer 看一下, 我有一个想法,我把这个总数写入一个统计表,然后每次访问都访问这个统计表,这样就快了,不过就是麻烦了点,不知道这样值不值没有见过这种方法的。理论上你的 select (*) 的开销是很小的,mysql会直接根据表中的信息建议你提供以下信息。show create table customer; show index from customer;
测试了一下,显然没有你说的那种问题。 另外同时也证明 select count(*) 和 select count(id) 是一样,没有什么主键的速度会比* 快的说法。mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | col | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.06 sec)mysql> select SQL_NO_CACHE count(*) from t1; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.00 sec)mysql> select SQL_NO_CACHE count(id) from t1; +-----------+ | count(id) | +-----------+ | 100000 | +-----------+ 1 row in set (0.00 sec) mysql> explain select SQL_NO_CACHE count(*) from t1; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec)mysql> explain select SQL_NO_CACHE count(id) from t1; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec)mysql>
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 3955 |
+----------+
1 row in set (0.00 sec)mysql> select count(id) from customer;
+-----------+
| count(id) |
+-----------+
| 3955 |
+-----------+
1 row in set (0.02 sec)mysql> select count(code) from customer;
+-------------+
| count(code) |
+-------------+
| 3955 |
+-------------+
1 row in set (0.02 sec)
我有一个想法,我把这个总数写入一个统计表,然后每次访问都访问这个统计表,这样就快了,不过就是麻烦了点,不知道这样值不值没有见过这种方法的。理论上你的 select (*) 的开销是很小的,mysql会直接根据表中的信息建议你提供以下信息。show create table customer;
show index from customer;
另外同时也证明 select count(*) 和 select count(id) 是一样,没有什么主键的速度会比* 快的说法。mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| col | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.06 sec)mysql> select SQL_NO_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)mysql> select SQL_NO_CACHE count(id) from t1;
+-----------+
| count(id) |
+-----------+
| 100000 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select SQL_NO_CACHE count(*) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)mysql> explain select SQL_NO_CACHE count(id) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)mysql>