mysql中select求最大值_关于取表中id最大值+1的select语句，哪种效率更高？

需求：取stock表中id最大值+1，作为下一个id值。

特殊情况：考虑到表中会没有值，max(id)会返回空，因此需要用case when进行判断。

实现一：select (case max(id) is null when true then 0 else max(id)+1 end) from stock

实现二：select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock

效率分析：

实现一相对于实现二不取数量，在索引的帮助下也能快速取值，因此效率应该比实现二高。

分析之验证：

(MySQL数据库)

stock表中没有数据时：

mysql> explain select (case max(id) is null when true then 0 else max(id)+1 end) fromstock;+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+

1 row in set (0.00sec)

mysql> explain select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) fromstock;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+

| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row |

| 2 | SUBQUERY | stock | index | NULL | PRIMARY | 4 | NULL | 3749 | Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+

2 rows in set (0.05 sec)

stock表中有数据时：

mysql> select count(*) fromstock;+----------+

| count(*) |

+----------+

| 3768 |

+----------+

1 row in set (0.00sec)

mysql> explain select (case max(id) is null when true then 0 else max(id)+1 end) fromstock;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

| 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 |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

1 row in set (0.00sec)

mysql> desc select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) fromstock;+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+

| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

| 2 | SUBQUERY | stock | index | NULL | PRIMARY | 4 | NULL | 3696 | Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+

2 rows in set (0.00 sec)

分析之验证：

oracle数据库

MySQL版的SQL不能直接在oracle里使用，因此需要改写成：

方案一：select nvl(max(id)+1,0) from stock;

方案二：select (case count(*) when 0 then 0 else max(id)+1 end) from stock

然后我模拟做了3744条记录，跑解释计划确实方案二慢.

方案一的执行计划：

SQL> select nvl(max(id)+1,0) fromstock;

已用时间:00: 00: 00.00执行计划----------------------------------------------------------

Plan hash value: 1547204082

-------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

| 2 | INDEX FULL SCAN (MIN/MAX)| SYS_C0011050 | 1 | 13 | 2 (0)|

00:00:01 |

-------------------------------------------------------------------------------------------

Note-----

- dynamic sampling used for this statement (level=2)

方案二的执行计划：

SQL> select (case count(*) when 0 then 0 else max(id)+1 end) fromstock;

已用时间:00: 00: 00.00执行计划----------------------------------------------------------

Plan hash value: 916654

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:0

0:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

| 2 | INDEX FAST FULL SCAN| SYS_C0011050 | 3744 | 48672 | 5 (0)| 00:0

0:01 |

--------------------------------------------------------------------------------------

Note-----

- dynamic sampling used for this statement (level=2)

--2020年5月2日--