我有一个包含动态类别数据的表：
+----------+--------------+---------------+---------+
| category | string_value | integer_value | user_id |
+----------+--------------+---------------+---------+
| cat_1 | NULL | 1 | 1 |
| cat_1 | NULL | 3 | 2 |
| cat_2 | foo | NULL | 1 |
| cat_2 | bar | NULL | 2 |
+----------+--------------+---------------+---------+
我需要这个表的透视版本，我用它来声明：
select
user_id,
max(case when category == 'cat_1' then integer_value end) as 'cat_1',
max(case when category == 'cat_2' then string_value end) as 'cat_2',
from my_table
group by user_id
这将以以下格式创建结果：
+---------+-------+-------+
| user_id | cat_1 | cat_2 |
+---------+-------+-------+
| 1 | 1 | foo |
| 2 | 3 | bar |
+---------+-------+-------+
这个查询本身对于许多类别和表条目也表现良好(例如，对于8个类别和240k个条目，大约需要20ms(，但如果我将这个确切的查询封装在select * from <query>中，性能会显著下降(降至650ms(。
此外，按user_id排序不会显著影响性能，而按任何其他字段排序也会导致性能下降，即使存在相应字段和user_id的索引也是如此。我猜这种方法本身对于较大的表是不可行的？然而，我很好奇在添加select * from <query部分时，是什么原因导致了额外的执行时间。
背景：我试图使用此查询来存储动态用户数据，并且我希望防止在运行时更改表结构(即添加列(。欢迎任何其他选择。我使用的是MariaDB 10.5.5，我需要该解决方案也可以使用MySQL 5.7和SQL Server 2019。
执行计划：
无周围select * from:
+----+-------------+-----------+-------+---------------+------------+---------+-----+--------+---------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|----|-------------|-----------|-------|---------------|------------|---------|-----|--------|---------|----------|------------|-------|
| 1 | SIMPLE | user_data | index | | user_index | | 9 | 226067 | 1619.00 | 100.0 | 99.88 | |
+----+-------------+-----------+-------+---------------+------------+---------+-----+--------+---------+----------+------------+-------+
带周围select * from:
+----+-------------+------------+-------+---------------+------------+---------+-----+--------+-----------+----------+------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|----|-------------|------------|-------|---------------|------------|---------|-----|--------|-----------|----------|------------|-------|
| 1 | PRIMARY | <derived2> | ALL | | | | | 226067 | 200.00 | 100.0 | 100.0 | |
| 2 | DERIVED | user_data | index | | user_index | 9 | | 226067 | 242418.00 | 100.0 | 100.0 | |
+----+-------------+------------+-------+---------------+------------+---------+-----+--------+-----------+----------+------------+-------+
以下是我对正在发生的事情的猜测。
您在MariaDB用于聚合的基础表上有一个索引。这意味着不进行排序。只要读取索引，它就可以开始返回行。
这是一个非常好的功能。但是，当您刚运行查询时，您会看到第一行的时间。
使用派生表时，MariaDB必须生成所有行，然后才能返回其中的任何。因此，带有子查询的select *正在做更多的工作。
这就是为什么第二个版本比第一个版本慢的原因。我预计，在大多数机器上，返回数万行的查询需要20毫秒以上的时间。