MySQL中min和max查询优化

共计 3684 个字符，预计需要花费 10 分钟才能阅读完成。

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() 函数往往会造成全表扫描

：