非常慢的查詢

問題

下面是我在 MySQL Workbench 中整理的一個查詢，我認為它相對簡單。但是當執行它時，在更大的數據集（7000+ 行）上，我發現它需要 200 多秒，使用 1GB 的 RAM，並且從 MySQL 伺服器以 10+ MB/s 的速率下載數據。整個表中沒有 100KB 的數據。很明顯，我在這里以某種方式創造了某種可怕的循環。但我沒有看到它。

我在做什麼

實際上，此查詢所做的是從每一行中獲取指定的列，轉義單引號和雙引號，然後將它們一起連接成 JSON 格式，完全符合 3rd 方 API 的需要。完成這部分查詢後，我選擇最後一行並將其返回。（這裡看不到，它將這些數據插入到“記憶體”表中，以便從 API 快速訪問）

我的查詢

set @RowNum := 0;
set @JSON := '';
set @RowTotal := (SELECT count(*) FROM(SELECT * FROM `Job1111`.CivilTrackerDetails GROUP BY BidID) tb);


SELECT (@RowNum := @RowNum + 1) AS ID,   
(@JSON := CONCAT(@JSON, CONCAT(
IF(@RowNum = 1, '[', ''),
'["',
ctd.BidID,
'","', 
REPLACE(REPLACE(ctd.Description, '"', '\\"'),"'", "\\'"), 
'","', 
REPLACE(REPLACE(ctd.FoundationDescription, '"', '\\"'),"'", "\\'"), 
'","', 
REPLACE(REPLACE(ctd.EngDrawingNumber, '"', '\\"'),"'", "\\'"), 
'","', 
REPLACE(REPLACE(ctd.DetailDrawingNumber, '"', '\\"'),"'", "\\'"),  
'","', 
ctd.TakeOffQuantity, 
'"]',
IF(@RowNum = @RowTotal, ']', ',')))) AS JsonData

FROM `Job1111`.CivilTrackerDetails  ctd

題

你能明白為什麼循環如此糟糕嗎？你知道完成這項任務的更好方法嗎？最好在給定的 MySQL 內與 MySQL 外安排數據的方法內。

解釋沒有顯示什麼……只是從全表掃描中派生的 ctd 。（意料之中）。

我使用的是 5.6.3 版。我經常想知道 5.7 中應該提供的一些 JSON 兼容性是否可以幫助解決這種情況。不過，我還沒有研究 5.7 中的所有功能。我也會對此感興趣。

最終，這個問題的解決方案將在儲存過程中實現。

在我看來，您只需要使用GROUP_CONCAT 聚合函式：

SET @JSON :=
 CONCAT(
   '[',
   (
     SELECT
       GROUP_CONCAT(
         CONCAT(
           '["', ctd.BidID,
           '","', REPLACE(REPLACE(ctd.Description,           '"', '\\"'),"'", "\\'"), 
           '","', REPLACE(REPLACE(ctd.FoundationDescription, '"', '\\"'),"'", "\\'"), 
           '","', REPLACE(REPLACE(ctd.EngDrawingNumber,      '"', '\\"'),"'", "\\'"), 
           '","', REPLACE(REPLACE(ctd.DetailDrawingNumber,   '"', '\\"'),"'", "\\'"),  
           '","', ctd.TakeOffQuantity, 
           '"]'
         )
         ORDER BY <some-column>
       )
     FROM
       `Job1111`.CivilTrackerDetails AS ctd
   ),
   ']'
 )
;

正如Rick James所指出的，將 GROUP_CONCAT 應用於這樣的整個表可能會導致超出預設group_concat_max_len值，即 MySQL5.7 中的 1024。因此，請確保您已將其設置得足夠大以容納生成的字元串，例如，在字元串分配之前使用 SET 語句將其設置為會話方式，如下所示：

SET group_concat_max_len = 20480;  -- for example
SET @JSON := ...

引用自：https://dba.stackexchange.com/questions/158205