MariaDB:如何在with语句中使用"INSERT ... SELECT“

Question

注意:这涉及到ColumnStore。

在工作中，我们有一个很大的SQL语句，它需要太多的内存来执行prod。我目前正在致力于减少查询消耗的大小。我尝试过使用不同的方法，但由于某些原因，除了WITH ... AS (...)之外，到目前为止还没有解决这个问题的方法。但是，我需要将其与INSERT INTO ...结合使用。

这是我正在尝试运行的代码

TRUNCATE db1.myTable;

INSERT INTO db1.myTable(`all`, `needed`, `columns`)
(WITH everything AS (
  SELECT all, needed, columns
   FROM db1.mainTable T1
   JOIN db1.secondTable T2
     ON (T1.someCol = T2.someCol)
   JOIN db2.thirdTable T3
     ON (T1.anotherCol = T3.anotherCol)
   LEFT JOIN db1.fourthTable T4
     ON (T4.anotherCol = T1.anotherCol)
   WHERE T2.yetAnotherCol >= (some_SELECT_subquery)
     AND T1.valid = 1
) SELECT * FROM everything);

EXPLAIN (WITH everything AS ...返回

+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY     | <derived2>            | ALL  | NULL          | NULL | NULL    | NULL | 16000000000000 |                                                 |
|    2 | PRIMARY     | T1                    | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where with pushed condition               |
|    2 | PRIMARY     | T2                    | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|    2 | PRIMARY     | T3                    | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
|    2 | PRIMARY     | T4                    | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where                                     |
|    3 | SUBQUERY    | some_SELECT_subquery  | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where with pushed condition               |
+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
5 rows in set (0,21 sec)

如果我只使用WITH-statement，我可以让它工作。也就是说，我不使用INSERT INTO。完全没有问题，这样查询速度会更快。我还做了快速测试，试图将查询分成几个WITH，但我放弃了，因为我认为我搞乱了语法。我对SQL不太在行，对JOIN(初级开发人员)更是如此。

当我将WITH-statement与INSER INTO ...组合在一起时，MariaDB以ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') SELECT * FROM everything)' at line 1作为响应。我还尝试在... valid = 1后面添加分号，合并最后两行，将... AS后面的左括号放在新的一行上，以及我能想到的其他一些可能与语法相关的问题。不走运。

我现在的想法是你不能把INSERT INTO ... SELECT ...和WITH ...结合起来。至少在开头没有WITH，而SELECT应该在那里。这是我可以从docs收集到的信息。

所以，简而言之，我的问题是:我能把INSERT INTO ... SELECT和WITH-statement结合起来吗？如果不是，我能用另一种技术实现类似的效果吗？

是否有其他方法可以提高查询的内存利用率？我不想弄乱MariaDB或Docker的配置选项，但如果这是唯一的可能性，我会考虑的。

Answer 1

你试过这个吗？

 TRUNCATE db1.myTable;


WITH everything AS (
  SELECT all, needed, columns
   FROM db1.mainTable T1
   JOIN db1.secondTable T2
     ON (T1.someCol = T2.someCol)
   JOIN db2.thirdTable T3
     ON (T1.anotherCol = T3.anotherCol)
   LEFT JOIN db1.fourthTable T4
     ON (T4.anotherCol = T1.anotherCol)
   WHERE T2.yetAnotherCol >= (some_SELECT_subquery)
     AND T1.valid = 1
) INSERT INTO db1.myTable SELECT * FROM everything;

Answer 2

虽然我没有找到我最初问题的答案，但我们决定通过减少在子查询中收集的数据量来解决这个问题。我没有在最初的问题中透露这一点，因为我在发布问题时并没有意识到这是一个解决方案。我们只需从Python脚本调用SQL，在该脚本中我们可以循环遍历要获取的周数。

WHERE T2.ID >= (SELECT ID - {week_number} FROM db1.secondTable WHERE NOW() BETWEEN monday AND sunday) AND T1.valid = 1);