iT邦幫忙

0

codeIgniter4資料庫很慢

舜~ 2021-09-14 14:35:441647 瀏覽

各位前輩們好

我要匯入一個大約有2萬筆的json
用原本CI內建的資料庫處理每秒大約處理29筆資料
大概要10分鐘左右完成超慢,

因為每筆資料的結構一樣,
想改用預備查詢prepare來看看是否能加速
但改預查詢後發現,
每秒只能處理5筆資料 @@

請問我哪邊寫法有問題? 或有什麼可以加速的建議?
另外想外update的預查詢好像不能使用update()方法?
使用update()會變成true...

$pQueryExist = null;
$pQueryUpdate = null;
$pQueryInsert = null;

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的預查詢
    if($pQueryExist == null) {
        $pQueryExist = $db->prepare(function ($db) {
            return $db->table('vsms_scan_copy')->where(['result_id' => '?'], false)->selectCount('result_id', 'cnt')->get();
        });
    }

    if($pQueryInsert == null) {
        $paramOrder = array_merge($vul, ['result_id' => '?', 'updated_at' => '?', 'created_at' => '?']);
        $pQueryInsert = $db->prepare(function ($db) use($paramOrder) {
            $result = $db->table('vsms_scan_copy')->insert($paramOrder);
            return $result;
        });
    }

    if($pQueryUpdate == null) {
        $paramOrder = array_merge($vul, ['updated_at' => '']);
        $pQueryUpdate = $db->prepare(function ($db) use($paramOrder) {

            $result = $db->table('vsms_scan_copy')->where('result_id' , '?', false);
            foreach ($paramOrder as $key => $value) {
                $result->set($key, '?', false);
            }
            $sql = $result->getCompiledUpdate(false);

            // return $result->update(); // 莫名其妙會變成true...有人知道原因嗎?
            return (new Query($db))->setQuery($sql);
        });
    }


    $resultId = $vul['id'];

    // 儲存紀錄
    $exist = $pQueryExist->execute($resultId)->getRow()->cnt;
    if (!empty($exist)){
		// 原本的方法
        // $db->table('vsms_scan')->update(array_merge($vul, ['updated_at' => $vulDate]), ['result_id' => $resultId]); // 'updated_at' => $now

		// 預查詢
        // 產生參數(key名稱要拿掉,只要值的順序一致就好)
        $param = array_values(array_merge($vul, ['updated_at' => $vulDate]));
        array_push($param, $resultId); // result_id
        $pQueryUpdate->execute(...$param);
    }
    else {
        // 原本的方法
        // $db->table('vsms_scan')->insert(array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate])); // , 'updated_at' => $now, 'created_at' => $now

        // 預查詢
        $param = array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]);
        $pQueryInsert->execute(...array_values($param));
    }
}
$pQueryExist->close();
$pQueryUpdate->close();
$pQueryInsert->close();

目前的想法是,
假設慢是慢在CI封裝處理這些SQL語法,
那我事先產生好值是問號的SQL語法,
後續帶參數的方式處理
像這樣

$db->query(
  "SELECT count(1) AS cnt FROM vsms_scan_copy WHERE result_id=? AND scan_no=? AND status=?",
  [$resultId, $vul['scan_no'], $vul['status']]
)

20210916
結合 海綿寶寶 的建議
因為大部分的資料是更新,
所以直接更新,沒更新再新增~~
並且只在第一次的時候產生SQL語法,
之後就重複使用SQL,
只替換掉值(好像有個專有名詞,好像不是叫封裝? 不重要XD),
後來發現花費時間從原本的 10多分鐘 直接降到 3分半 !!!
果然主要慢在CI動態產生SQL語法~~

$pQueryUpdate = null;
$pQueryInsert = null;

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的SQL
    if (empty($pQueryUpdate)) {
        $param = array_merge($vul, ['updated_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryUpdate = $db->table('vsms_scan')
            ->where(['result_id' => ':result_id:'])
            ->set($param)
            ->getCompiledUpdate();
		// 去掉值的前後單引號
        $pQueryUpdate = str_replace("':", ':', str_replace(":'", ':', $pQueryUpdate));
    }

    if (empty($pQueryInsert)) {
        $param = array_merge($vul, ['result_id' => '', 'updated_at' => '', 'created_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryInsert = $db->table('vsms_scan')
            ->set($param)
            ->getCompiledInsert();
		// 去掉值的前後單引號
        $pQueryInsert = str_replace("':", ':', str_replace(":'", ':', $pQueryInsert));
    }


    $resultId = $vul['id'];

    // 直接更新,更新失敗就改新增
    if ( 
		!$db->query($pQueryUpdate, array_merge($vul, ['updated_at' => $vulDate, 'result_id' => $resultId]) ||
        $db->affectedRows() == 0
	){
         $db->query($pQueryInsert, array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]));
    }
}

換算下來CI動態產生一次SQL大約要7.5ms
這次的時間差大約7分半即450秒
一輪跑3次SQL (含沒列出的log)
(450 / 3) / 20000 = 0.0075秒 = 7.5ms
(大概是已關閉apache、php、mysql的快取,並開啟CI的開發模式的關係所以特別慢?)
果然方便就是有代價的XD

周末再來進行優化
依各位前輩的建議,
改用批次處理,
看速度還能提升多少


20210930

因為先判斷更新有無成功後才能知道要不要新增,
因此只有新增才有機會改批次處理

結果有點意外,反而變慢了40秒左右,總花費時間 04分15秒
實際新增資料大約1萬上下筆資料,
剩下的都是更新

$pQueryUpdate = null;
$insertParams = []; // 新增的參數

$jsonRows = json_decode(file_get_contents($file->getTempName()), true);

foreach ($jsonRows as $vul) {

    // 首次初始化各種情況的SQL
    if (empty($pQueryUpdate)) {
        $param = array_merge($vul, ['updated_at' => '']);
        foreach ($param as $key => $value) {
            $param[$key] = ":$key:";
        }

        $pQueryUpdate = $db->table('vsms_scan')
            ->where(['result_id' => ':result_id:'])
            ->set($param)
            ->getCompiledUpdate();
		// 去掉值的前後單引號
        $pQueryUpdate = str_replace("':", ':', str_replace(":'", ':', $pQueryUpdate));
    }


    $resultId = $vul['id'];

    // 直接更新,更新失敗就改新增
    if ( 
		!$db->query($pQueryUpdate, array_merge($vul, ['updated_at' => $vulDate, 'result_id' => $resultId]) ||
        !$db->affectedRows()
	){
         $insertParams[] = array_merge($vul, ['result_id' => $resultId, 'updated_at' => $vulDate, 'created_at' => $vulDate]);
    }
}

$db->table('vsms_scan')->insertBatch($insertParams);

最後默默的回到上個版本...

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
海綿寶寶
iT邦大神 1 級 ‧ 2021-09-14 17:04:29
最佳解答

2萬筆資料
程式固定會 select 2萬次(檢查是否存在)
假設存在 N 次
則會 insert 20000-N 次
update N 次

我提供兩個比較混的做法
有空可以試試看
1.不用檢查是否存在
2.直接 insert 2萬次,成功 20,000-N 次
3.失敗 N 次,改用 update

第二種也是一樣
1.不用檢查是否存在
2.直接 update 2萬次,成功 N 次
3.失敗 20,000-N 次,改用 insert

舜~ iT邦高手 1 級 ‧ 2021-09-15 01:32:13 檢舉

感謝前輩的建議,這是個好主意,看來我思維有點固化了

1

我印象中,不是有batch insert的機制嗎?
把處理好的資料用batch insert(或是update)可以大幅度縮減時間吧。

我只知道有個概念是這樣的:能讓資料庫處理的事,就不要用程式碼來處理。

舜~ iT邦高手 1 級 ‧ 2021-09-15 14:17:59 檢舉

要讓sql可以批次新增/更新,繞不過需要程式碼先行整理

所以你是讀一次,就存一次?
如果說,你不在乎轉一次的記憶體的量。
你可以全部資料都放進陣列或是批次放進陣列。
然後採一批一批的方式送出。
如果說你的伺服器還要同時對到其他使用者的流量。
你可以用其他的方式在背景慢慢轉換。
但我看起來,你似比較像是讀一筆就存一筆……
這個效率會非常的慢。
這裡可以找到inserBatch方法

舜~ iT邦高手 1 級 ‧ 2021-09-30 08:42:23 檢舉

結果用inserBatch沒想像中的快XD

2
chan15
iT邦新手 3 級 ‧ 2021-09-15 13:54:25
舜~ iT邦高手 1 級 ‧ 2021-09-15 15:51:36 檢舉

感謝建議,我周末嘗試看看

2
一級屠豬士
iT邦大師 1 級 ‧ 2021-09-15 14:56:57

MySQL 有一個 replace 指令,也可以參考 chan15大大建議的
INSERT ... ON DUPLICATE KEY UPDATE

https://dev.mysql.com/doc/refman/8.0/en/replace.html

再搭配先 insert 的一個臨時的table, 然後使用一道SQL 處理.

或者是使用 https://dev.mysql.com/doc/refman/8.0/en/load-data.html
LOAD DATA 時有 replace 可以選用.

舜~ iT邦高手 1 級 ‧ 2021-09-15 15:51:55 檢舉

感謝建議

1

在回圈內,只組合需要處理的values值。但先不做QUERY的動作。
並且直接使用唯一的KEY值處理。

陣列變數存存一定的量,一般我會抓1000筆。
才統一送一次 REPLACE INTO 處理。

一筆一筆跑當然會很慢。要學用多重資料處理的方式。

舜~ iT邦高手 1 級 ‧ 2021-09-16 22:05:50 檢舉

好的,感謝提醒~

我要發表回答

立即登入回答