关于取表中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) 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日--