需求：取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) from stock;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
| 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.00 sec) mysql> explain select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+
| 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(*) from stock;
+----------+
| count(*) |
+----------+
| 3768 |
+----------+
1 row in set (0.00 sec) mysql> explain select (case max(id) is null when true then 0 else max(id)+1 end) from stock;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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.00 sec) mysql> desc select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock;
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
| 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) from stock;
已用时间: 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) from stock;
已用时间: 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日--