MySQL中min和max查询优化

MySQL max() 函数的需扫描where条件过滤后的所有行：

在测试环境中重现：

测试版本：Server version:        5.1.58-log MySQL Community Server (GPL)

testtable表中的索引

mysql> show index from testtable; +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | testtable |          0 | PRIMARY    |            1 | id          | A        |          2 |    NULL | NULL  |      | BTREE      |        | | testtable |          1 | key_number |            1 | number      | A        |          2 |    NULL | NULL  | YES  | BTREE      |        | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

对比的sql为：

 select sql_no_cache  max(id) from testtable where number=98;  select sql_no_cache id from testtable where number=98 order by id desc limit 1;

查看执行计划：

mysql> explain select sql_no_cache  max(id) from testtable where number=98; +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    | +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ |  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index | +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)

mysql> explain select sql_no_cache id from testtable where number=98 order by id desc limit 1; +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    | +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ |  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index | +----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) 执行计划显示完全一样。

其中，number为98 对应的记录有4行：

mysql> select count(*) from testtable where number=98;

+----------+ | count(*) | +----------+ |        4 | +----------+ 1 row in set (0.00 sec)

执行前查看innodb_rows_read

#innodb_rows_read  从InnoDB表读取的行数

mysql> show global status like 'innodb_rows_read'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | Innodb_rows_read | 1022  | +------------------+-------+ 1 row in set (0.00 sec)

执行sql1 mysql> select sql_no_cache  max(id) from testtable where number=98; +---------+ | max(id) | +---------+ |      13 | +---------+

1 row in set (0.00 sec)

执行后查看innodb_rows_read，发现innodb_rows_read增加了4，即number为98 对应的记录有4行 mysql> show global status like 'innodb_rows_read'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | Innodb_rows_read | 1026  | +------------------+-------+ 1 row in set (0.00 sec)

执行sql2 mysql> select sql_no_cache id from testtable where number=98 order by id desc limit 1; +----+ | id | +----+ | 13 | +----+ 1 row in set (0.00 sec)

执行后查看innodb_rows_read，发现innodb_rows_read增加了1 mysql> show global status like 'innodb_rows_read'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | Innodb_rows_read | 1027  | +------------------+-------+ 1 row in set (0.00 sec)

测试得出：

 select sql_no_cache  max(id) from testtable where number=98;

需要读取 number=98 的所有行，才能得到最大的id

 select sql_no_cache id from testtable where number=98 order by id desc limit 1;

由于id是主键，number是第二索引，只需扫描1行即可得到最大的id

请慎用max()函数，特别是频繁执行的sql，若需用到可转化为测试中的  order by id desc limit 1

因为往往min()或者max()函数往往会造成全表扫描