[MYSQL] 查询最大值 使用order by x desc limit 1 还是使用max ?

导读

我们需要查询某个字段最大值的时候, 可以使用max()函数, 也可以使用order by column desc limit1 来实现.

那么问题来了, 选择哪一种呢?

当然是我全都要测试完了再看

准备数据

建表

create table db1.t20240605(name varchar(200), id int);


插入数据

import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3314,
user='root',
password='123456',
)

for i in range(10000):
cursor = conn.cursor()
sql = 'insert into db1.t20240605 values'
for j in range(100):
sql += f"('ddcw',{i*10000+j}),"
sql = sql[:-1]
cursor.execute(sql)
conn.commit()


测试

本次测试环境是8.0.28

无索引测试

MAX函数

select max(id) from db1.t20240605;
explain select max(id) from db1.t20240605;


ORDER BY LIMIT

select id from db1.t20240605 order by id desc limit 1;
explain select id from db1.t20240605 order by id desc limit 1;


没索引, 都一样慢

升序索引测试

添加升序索引

alter table db1.t20240605 add index idx_id(id asc);


MAX函数

Extra提示: Select tables optimized away

that at most one row should be returned
that to produce this row, a deterministic set of rows must be read

When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution.
满足上述两个条件就会有这个优化提示信息. 即不会读取任何表. 所以这里耗费时间是0

ORDER BY LIMIT

Extra提示:Backward index scan; Using index
即反向索引扫描, 由于是最大值, 即’第一条’数据就是我们要的值. 所以也很快.

降序索引测试

删除之前的索引, 并添加降序索引

alter table db1.t20240605 drop index idx_id;
alter table db1.t20240605 add index idx_id(id desc);


MAX函数

索引扫描, 速度慢下来了. 理论上比全表扫描还慢. 但我这里数据量较少, 不太明显.

ORDER BY LIMIT

order by肯定还是快的, 而且还不需要反向扫描了.

总结

没得索引的时候, 都是全表扫描, 都慢, 升序索引的时候, 速度都差不多, 降序索引对max无效, 所以是order by更快一点.

对象	MAX时间(s)	ORDER BY时间(s)
无索引	0.3	0.3
升序索引	0	0
降序索引	0.3	0

3局2胜, order by胜. 胜之不武 不过一般也不会使用降序索引…