id是primary key的吧? innodb引擎的话order by limit 1效率会高些。 myisam下单纯select max(id)是很快的,最大id相当于存在一个缓存里,因为我explian语句发现Extra列显示的是Select tables optimized away,是和select count(id)一样的处理。 不过如果你除了max(id)还要select对应的其它字段,一条语句的效率可能就不如order by limit 1了, 当然你的语句可以变化为 select * from table where id = (select max(id) as id from table) 应该会非常快,是我基于explian的分析,你可以拿去测试下 自己多查看explian分析吧。
For explains on simple count queries (i.e. explain select count(*) from people) the extra section will read "Select tables optimized away." This is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select.
否则若 id 有重复时,前者结果不一定对
测试了一下,的确没区别在速度上
mysql_connect('127.0.0.1', 'root', 'root');
mysql_select_db('collect');$sql1 = 'select id from collect order by id desc limit 1';
$sql2 = 'select max(id) from collect';$t = microtime(true);
for ($i = 0; $i < 10000; $i++) {
mysql_query($sql1);
}
echo microtime(true) - $t;
赞成,,而且不同的方式建表,也是有区别的。当你是mysql如果是主键,myisam,用max更好,这是无需任何扫描的直接读结果的搜索,你可以Explain下贴出结果瞅瞅呢
1,"SIMPLE","collect","index",NULL,"PRIMARY","4",NULL,1,"Using index"
第二条sql
1,"SIMPLE",NULL,NULL,NULL,NULL,NULL,NULL,NULL,"Select tables optimized away"
innodb引擎的话order by limit 1效率会高些。
myisam下单纯select max(id)是很快的,最大id相当于存在一个缓存里,因为我explian语句发现Extra列显示的是Select tables optimized away,是和select count(id)一样的处理。
不过如果你除了max(id)还要select对应的其它字段,一条语句的效率可能就不如order by limit 1了,
当然你的语句可以变化为
select * from table where id = (select max(id) as id from table)
应该会非常快,是我基于explian的分析,你可以拿去测试下
自己多查看explian分析吧。