查詢中的 int 比較非常慢

為了簡單起見，我有這個虛構的表格來解釋我的問題：

CREATE TABLE IF NOT EXISTS `posts` (
 `pid` int(11) NOT NULL AUTO_INCREMENT,
 `user` int(11) NOT NULL,
 `posts` int(11) NOT NULL,
 `comments` int(11) NOT NULL,
 `notes` varchar(100) NOT NULL,
 PRIMARY KEY (`pid`),
 KEY `posts` (`posts`)
 KEY `comments` (`comments`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306;

該表有超過 100 萬行，MariaDB 版本 10.0.20

現在我想執行更新並在一篇評論多於文章的文章上設置通知

UPDATE posts SET notes = 'more comments' WHERE posts < comments

現在這個查詢大約需要 2-4 秒才能完成。

我想我缺少索引或者是否有必要重寫查詢？

當我使用固定比較進行查詢時

UPDATE posts SET notes = 'more comments' WHERE posts > 1000

查詢執行速度很快。

首先，具有比較兩列的條件的查詢或（更新）語句WHERE posts < comments不能有效地使用您的索引，因此它可能必須進行全表掃描。如果你有一個複合索引，(posts, comments)或者相反，它可能會更好，但它仍然需要進行全索引掃描。

如果要更新的行非常少，比如 100 行，那麼掃描 100 萬行以僅更新 100 行並不是很有效。

---

因此，最簡單的事情可能是添加這樣的複合索引並進行測試。如果全索引掃描的效率可以接受，您可以繼續使用此查詢。

---

你可以做的另一件事——因為你使用的是 MariaDB——是添加一個VIRTUAL（計算的）列並為其建立索引：

ALTER TABLE posts
 ADD COLUMN comment_posts_diff INT AS (columns - posts) PERSISTENT,
 ADD INDEX more_comments_ix (comment_posts_diff) ;

然後，您只需更改查詢條件即可使用：

WHERE comments - posts > 0

要麼：

WHERE comment_posts_diff > 0

並且索引將被有效地使用。

請注意，計算列有 2 種變體，theVIRTUAL和PERSISTENTthes。從名稱中可以明顯看出，持久列實際上佔用了磁碟空間，並且當修改其定義中的任何列時，值會自動修改。它們的優點是它們可以被索引。

---

如果'more comments'雖然只是一個適用於行的常量字元串，comments > posts並且有另一個字元串（'less comment'？）適用於其餘行或類似需求，那麼您實際上並不需要該列notes和上述任何內容。您的查詢可以在執行時計算相應的值（更多評論、更少評論、無評論）。

或者您可以有一個計算列來為您完成工作（這次是虛擬的，而不是持久的）：

ALTER TABLE posts
 DROP COLUMN notes,
 ADD COLUMN notes VARCHAR(20) AS
     CASE WHEN columns > posts THEN 'more comments'
          WHEN columns = 0     THEN 'no comments'
          WHEN columns = posts THEN 'comments = posts'
          ELSE 'less comments'
     END
   VIRTUAL ;

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