記錄數增加時網站速度太慢？

我開發了一個名為Referral Program的 Web 應用程序，它使用 PHP、MySQL 開發。使用者可以加入網站，當他們介紹新成員時，他/她會獲得佣金。當使用者數量較少時，系統執行平穩。但是使用者數量超過 50,000 時，系統就會變慢。

問題是使用者進入系統就像二叉樹，當他/她進入系統時，佣金被分配到頂層（根節點或管理員）。每次註冊都需要進行樹遍歷。這需要更多時間。系統中的每個使用者註冊必須給予五種（或更多）類型的佣金，當系統中有100,000個使用者時將有500,000條記錄。然後系統變得太慢。並且將有 600-1000 名使用者線上。他們什麼也做不了，因為速度慢。

最後我們將 500,000 條記錄合併為 100,000 條（我們只保留總和），然後就可以了。他們可以使用該應用程序。我們將 InnoDB 更改為 MyISAM，它更快但不支持事務。但我們應該保留原始記錄。怎麼做？

已編輯 - 1

我在這裡列出了伺服器功能。

• 作業系統：CentOS
• 記憶體：25 GB
• 硬碟：1500 GB
• 目前數據庫大小：800 MB
• MySQL 版本：5.1（或以上）
• PHP : 5.3

MySQL錯誤“嘗試獲取鎖時發現死鎖；嘗試重啟交易”

我們正在執行一個 cron 作業（每天），它需要更多的時間來完成，因為複雜的計算是在龐大的數據庫中執行的。當使用者註冊或登錄系統時，會顯示上述錯誤。我只是在這里和這里處理相同的錯誤，但我沒有得到確切的解決方案。我們正在做很多 SELECT / UPDATE / INSERT

EDITED - 2 這是我使用的數據庫結構，還有其他表，但我認為現在並不重要。

我使用的功能在這裡

public function getAllUplineId($id,$i,$position,$profile_field_id){
$select = "SELECT father_id
           FROM 161_ft_individual
           WHERE id=$id ";
$result = db_query($select);    
while($node = db_fetch_object($result))
   {
   $user_id = $node->father_id;   
   if($user_id > 0)
       {
       $amount = 1;        
       $this->update_profile_value($user_id,$profile_field_id,$amount);
       $i=$i+1;         
       $this->getAllUplineId($user_id,$i,$position,$profile_field_id);
       }

   }
}

public function update_profile_value($user_id,$profile_field_id,$amount){
$query = "UPDATE 161_ft_individual SET total_leg=total_leg+$amount
           WHERE id='$user_id' LIMIT 1";
$result = db_query($query);    
return $result;
}

getAllUplineId() 函式從註冊點獲取並更新所有上線使用者。超過 50,000 的使用者數量（在表中161_ft_individual）變得太慢了，沒有人可以做任何事情。該表161_leg_amount有 500,000 條記錄。

提前致謝。

您遇到的基本問題是一切都是遞歸的，並且您正試圖鎖定 InnoDB 中的數據頁面，從而創建大量MVCC資訊。在某些情況下，您可能已經嘗試重複鎖定同一個 InnoDB 頁面。這就解釋了僵局。我已經在同一問題的三個不同文章中與某人深入評論了這種情況：

• 無法破譯 innodb 狀態日誌中的死鎖
• 這兩個查詢如果按順序執行會導致死鎖嗎？
• 偶爾慢查詢的原因？

你需要一個解決方法。您可能想嘗試以下算法：

對於這個例子

• 編號 = 12
• 金額 = 22.75

Step 01) 製作一張表來收集所有要更新的father_ids

DROP TABLE IF EXISTS fathers_to_update;
CREATE TABLE fathers_to_update
(
   father_id INT NOT NULL,
   PRIMARY KEY (father_id)
);

步驟 02) 製作一個將成為 FIFO 隊列的表。將 12 的值放入隊列：

DROP TABLE IF EXISTS fathers_to_queue;
CREATE TABLE fathers_to_queue
(
   father_id INT,
   id INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id)
);
INSERT INTO fathers_to_queue (father_id) VALUES (12);

步驟 03) 獲取計數fathers_to_queue

SELECT COUNT(1) FROM fathers_to_queue;

如果計數為0，則轉到步驟07）

步驟 04) 刪除前面的數字fathers_to_queue

SELECT MIN(id),father_id INTO @frontndx,@dad FROM fathers_to_queue;
DELETE FROM fathers_to_queue WHERE id = @frontndx;

Step 05) 從161_ft_individual中收集所有id為@dad的father_ids到隊列中

INSERT INTO fathers_to_update SELECT father_id FROM 161_ft_individual WHERE id = @dad;
INSERT INTO fathers_to_queue (father_id) SELECT father_id FROM 161_ft_individual WHERE id = @dad;

步驟 06) 返回步驟 03

步驟 07) 對您收集的 161_ft_individual 中的所有記錄執行一次更新fathers_to_update

UPDATE fathers_to_update A
LEFT JOIN 161_ft_individual B
USING (father_id)
SET B.total_leg=B.total_leg+@amount;

而已。我基本上執行了一個 preoder 樹遍歷，將層次結構備份到所有記錄，直到父親 ID 為 NULL，這使得步驟 03 停止收集父親 ID。

試一試 ！！！

更新 2011-12-16 12:18 EDT

早在 2011 年 10 月 24 日，我實際上用 MySQL 儲存過程語言編寫了一個算法來執行前序樹遍歷：查找分層欄位的最高級別：with vs without CTEs。希望能幫助到你 ！！！

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