Mysql查詢執行速度非常慢

我在我的 CodeIgniter 模型中使用了一個查詢來獲取特定日期之間的產品串列計數。當我的表中的專案較少時，這可以正常作業，但是我的表中有超過 100,000 個條目，并且僅獲得 2 天的輸出大約需要 3-4 分鐘。間隔天數越長，花費的時間就越多。

這是查詢：（Dbfiddle：https ://dbfiddle.uk/ ? rdbms = mysql_8.0 & fiddle = e7a99f08ecd217cbeb09fe6676cfe645 ）

with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n 1, day   interval 1 day from D
      where day   interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
         l.*,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;

基本上，此查詢所做的是 status_from 來自日期在所選日期范圍期間和之后的日志，并從日期落在用戶選擇的起始日期范圍之前的串列中獲取 added_date 并計算它。一旦它檢索到這些記錄，它就會檢查表中狀態保存的變數并執行 asum(case when else 0)以獲取總數。

我認為查詢很慢的一個原因是因為它必須計算查詢本身中狀態的總和，所以也許在 php 端執行計數部分會更快？如果是這樣，那么我如何為它創建一個陳述句來迭代我的視圖類中的計數。

當前視圖類：

<?php
            foreach($data_total as $row ){
               $draft = $row->draft ? $row->draft : 0;
               $publish = $row->publish ? $row->publish : 0;
               $action = $row->action ? $row->action : 0;
               $sold = $row->sold ? $row->sold : 0;
               $let = $row->let ? $row->let : 0;                              
          ?>
              <tr>
                    <td><?= $row->day?></td>
                    <td><?= $draft ?></td>
                    <td><?= $publish ?></td>
                    <td><?= $action ?></td>
                    <td><?= $sold ?></td>
                    <td><?= $let ?></td>
              </tr>
          <?php }  ?>

或者如果可能的話，是否有任何方法可以更快地獲得此查詢的相同輸出。

uj5u.com熱心網友回復：

這是更快嗎？如果您更頻繁地呼叫查詢，您可以考慮保存ROW_NUMBER到logs表

with calendar as (
with recursive cal (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n 1, day   interval 1 day from cal
    where day   interval 1 day < '2021-10-15'
    )select * from cal
), loggs as (
    select
         ROW_NUMBER() OVER (partition by refno order by logtime) as RN
        ,status_from as logstat
        ,refno
        ,logtime
    from logs
),X as (
  select cal.day,
         l.*,
         logs.logstat,
         RN,
         min(RN) over (partition by l.refno, cal.day) as RN_MIN
    from listings l
    join calendar as cal on l.added_date <= cal.day
    left join loggs as logs on logs.refno = l.refno and logs.logtime >= cal.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    where ifnull(RN, 0) = ifnull(RN_min, 0)
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;

uj5u.com熱心網友回復：

如果您的最終輸出將在網站上，資料快照通常比過去活動的實時提要更好。過去，我使用存盤程序每天使用 Past Activites 更新表，然后使用視圖選擇與 Current_Activities 結合的 Past_Activities 以減少查看器的加載時間。

標籤：mysql sql 查询优化 分组最大值

上一篇：如何撰寫遞回SQL查詢

下一篇：SQL：在超過1500萬行的查詢中結合WHERE、ORDER和LIMIT