Mysql
優化查詢有 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
身份,並為所有現有表提供INT
id 的版本而不是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,刪除外鍵並查看它如何影響插入性能