测试表的字段id为主键，max(id) 和count(*)的效果在不删除记录的情况下应该是一样的，一下是不同sql写法之间的效率对比

1. select count(*) from udata_1;   

   
+----------+
| count(*) |
+----------+
| 115651 |
+----------+

   然后该sql语句得到
   
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | udata_1 | index | NULL | PRIMARY | 4 | NULL | 115806 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+

   从这里可以看到 rows为115806 ，几乎将表遍历得到的数据

2. select count(id) from udata_1;

   
+-----------+
| count(id) |
+-----------+
| 115651 |
+-----------+
1 row in set (0.03 sec)

   explain select count(id) from udata_1
   
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | udata_1 | index | NULL | PRIMARY | 4 | NULL | 115806 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+

   可见在效率上结果上并没有任何差异

3. explain  select max(id) from udata_1 where id > 0; 因为id是从1开始自增的，所以和之前的sql应该没有任何差异

   
+----------+
| count(*) |
+----------+
| 115651 |
+----------+
1 row in set (0.05 sec)

   结果上的确没有任何差异

   explain select count(*) from udata_1 where id > 0;
   
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE | udata_1 | range | PRIMARY | PRIMARY | 4 | NULL | 57903 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+

   这个结果有点意外，因为id是主键，应该是因为这个，从而可以判定之前的count(*)的时候，并没有根据主键来进行优化，而这里因为where 的原因，启动了主键查找，一定程度提供了效率。

4. select max(id) from udata_1;

   
+---------+
| max(id) |
+---------+
| 115651 |
+---------+
1 row in set (0.00 sec)

   和count(*) 一样的结果，虽然只有10w+的测试数据，时间上还是可以看出优越的。

   explain select max(id) from udata_1;
   
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

   这个结果也有点意外，根据b+树的特点，以为需要logN级别的时间，而不是常量级别，也许是mysql针对max做了特定的优化

由以上对比可以看出，在count(*)和max 之间，效率上的差异是很明显的