優化查詢有 COUNT 和 INSERT…ON DUPLICATE KEY UPDATE

我要改進兩個主要問題，一個問題更具概念性和學習數據庫，另一個問題更直接。

兩種情況都處理同一張表就問題而言，它有 2 個相關列，它們構成了表的主鍵。主鍵的每個元素都假設是另一個表的鍵。第一行是一個 int（這是一個項目），第二行是一個VARCHAR（這是一個使用者），限制為 200 個字元。

第一個問題是試圖找到使用者的交集。我正在尋找共享多個項目的使用者的交集，在範例中為 5

SELECT userid 
FROM   (SELECT b.userid, 
              b.showid 
       FROM   ratings AS A 
              JOIN ratings AS B 
                ON A.userid LIKE :user 
                   AND B.userid NOT LIKE :user 
                   AND A.showid LIKE B.showid) AS C 
GROUP  BY userid 
HAVING Count(*) > 4  

在快速執行時，這佔用了我程序執行時間的大約 2/3（出現兩次，一個嵌套在另一個查詢中，花費的時間比這個長一點）。我已經嘗試查看它，但我沒有看到任何明顯的方法來重組查詢以節省時間，但是在更多行上執行的查詢的執行時間不到 1 秒，而這需要 2 秒。

第二個問題更具概念性。如前所述，我有一個複合主鍵。我在其他地方發現，對於InnoDB表格，這會使插入佔用更多時間。這正是INSERT ... UPDATE ON DUPLICATE KEY我正在執行的查詢所發生的事情。減少這種減速的最佳方法是什麼。

這就是插入的樣子，它只有在節目被評分時才會執行

INSERT INTO shows 
           ( 
                       showid, 
                       showname, 
                       showimage 
           ) 
           VALUES 
           ( 
                       :id, 
                       :name, 
                       :image 
           ) 
on duplicate KEY 
UPDATE showimage=:image, 
      showname= :name;
INSERT INTO ratings 
           ( 
                       userid, 
                       showid, 
                       rating 
           ) 
           VALUES 
           ( 
                       :user, 
                       :show, 
                       :rating 
           ) 
on duplicate KEY 
UPDATE rating=:rating

節目

CREATE TABLE "shows" (
"ShowId" int(11) NOT NULL,
"ShowName" varchar(400) NOT NULL,
"ShowImage" varchar(400) NOT NULL,
"ShowAgreement" int(11) NOT NULL DEFAULT "0",
PRIMARY KEY ("ShowId")
) ENGINE=InnoDB DEFAULT CHARSET=latin1

收視率

CREATE TABLE "ratings" (
"UserId" varchar(200) NOT NULL,
"ShowId" int(11) NOT NULL,
"Rating" int(11) NOT NULL,
PRIMARY KEY ("UserId","ShowId"),
KEY "ShowId" ("ShowId"),
CONSTRAINT "ratings_ibfk_1" FOREIGN KEY ("ShowId") REFERENCES "shows" ("ShowId") ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

對於單個測試案例，對於 160 個條目，INSERT 比 UPDATE 需要額外的 8 秒。

• 我是否應該簡單地執行查詢程式碼側的條件部分並刪除主鍵。
• 我應該刪除主鍵並用唯一約束替換它嗎？
• 我應該為 UserId 創建一個新表，為 userId 創建一個VARCHAR->INT 身份，並為所有現有表提供INTid 的版本而不是CHAR?
• 我應該做一些完全沒有想到的事情嗎？

我猜測您的查詢應該做什麼。我假設您正在尋找與某個使用者共享 5 個或更多項目的所有使用者：

SELECT a.userid 
FROM ratings AS a
WHERE EXISTS (
     SELECT 1
     FROM ratings AS b
     WHERE b.userid = :user
       AND a.userid <> b.userid
       AND a.showid = b.showid
)
GROUP BY a.userid
HAVING COUNT(1) > 4

這對嗎？

對於問題的第二部分，據我了解，表格定義為：

create table shows
(   showid  int not null primary key
,   showname    varchar(10) not null
,   showimage   varchar(10)
,   x int default 0 not null );

create table ratings
(   userid  varchar(200) not null
,   showid  int not null
       references shows(showid)
,   rating  int not null
,       primary key (showid, userid) );

如果是這種情況，我看不出為什麼插入評級很慢的特別原因，順便說一句，什麼是慢的？

嘗試查明問題的一些想法，製作評級表的副本，例如評級2，刪除外鍵並查看它如何影響插入性能

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