mysql select max(id) 与 select count(*) 性能对比

测试表的字段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 之间,效率上的差异是很明显的

Leave a comment

Your email address will not be published.

*