同一列上的多列和單列索引

我在同一列上有兩個索引：

1. store_deleted_idx (store_id, deleted)
2. store_idx (store_id)

DDL：

CREATE TABLE `stores_shoppers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `store_id` int(11) NOT NULL,
 `store_shopper_id` varchar(128) NOT NULL,
 `country_code` varchar(30) DEFAULT NULL,
 `zip_code` varchar(50) DEFAULT NULL,
 `email` varchar(128) NOT NULL,
 `first_name` varchar(128) DEFAULT NULL,
 `last_name` varchar(128) DEFAULT NULL,
 `modified_on` datetime NOT NULL,
 `is_marketing_allowed` tinyint(1) DEFAULT NULL,
 `created_on` datetime DEFAULT NULL,
 `is_guest` tinyint(1) DEFAULT NULL,
 `phone` varchar(45) DEFAULT NULL,
 `total_spent` decimal(12,2) DEFAULT NULL,
 `orders_count` int(11) DEFAULT NULL,
 `group_id` int(11) DEFAULT NULL,
 `deleted` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `store_idx` (`store_id`),
 KEY `store_deleted_idx` (`store_id`,`deleted`),
 CONSTRAINT `_stores_shoppers_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`),
 CONSTRAINT `_stores_shoppers_ibfk_4` FOREIGN KEY (`group_id`) REFERENCES `stores_shopper_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=56909900 DEFAULT CHARSET=utf8;

我的查詢：

SELECT s.*, t.*, g.*
FROM shoppers s
LEFT JOIN shopper_tag t ON s.id = t.shopper_id
LEFT JOIN shopper_group g ON s.group_id = g.id
WHERE s.store_id = '4494' AND (s.deleted = 0 OR s.deleted IS NULL)
ORDER BY s.created_on DESC
LIMIT 10 OFFSET 0;

如果我執行explain，則顯示查詢僅使用store_idx索引，但如果我刪除store_idx索引並執行explain，則顯示store_deleted_idx已使用。

store_deleted_idx為什麼在第一種情況下不使用？

我無法刪除單列索引。

擺脫OR

(s.deleted = 0 OR s.deleted IS NULL)

下定決心。使用0（並製作NOT NULL）或使用NULL. 最好有

TINYINT UNSIGNED NOT NULL DEFAULT '0'

在您擺脫 之前OR，包含的索引deleted是沒有用的。

綜合指數

之後，INDEX(deleted, store_id)無論以哪種順序，都變得最優。

通常，INDEX(a)如果您也有INDEX(a,b).

更好的是

INDEX(deleted, store_id,   -- in either order
     created_at)          -- last

這將避免目前需要的排序。（見EXPLAIN SELECT ...）

我無法刪除單列索引。

你的意思是你的手指不工作？如果你嘗試，你的老闆會打你的手嗎？你沒有權限？當你刪除它時它會回來嗎？礙事FOREIGN KEY？什麼？

基數

假新聞。

在復合索引中，基數無關緊要。

比較兩個索引時，基數很重要，但通常有更好的優化方法，例如使用複合索引。所以，再次，假新聞。

回到問題

你的實驗表明，要麼INDEX(store_id)或INDEX(store_id, deleted)將被使用。給定兩者之間的選擇，優化器將選擇較小的一個（假設工作量略少）。在沒有選擇的情況下（即，當您刪除了較小的那個時），優化器很高興地選擇了剩下的那個。

因此，為避免浪費磁碟空間，DROP請縮短（單列）INDEX。同時*，此*查詢將繼續有效工作。

“為什麼第一種情況不使用 store_deleted_idx？” ——因為OR.

更多關於索引

http://mysql.rjweb.org/doc.php/index_cookbook_mysql討論瞭如何一路通過WHERE（擺脫 之後OR）讓索引到達ORDER BY，如果你通過ORDER BY（通過附加created_by），你可以到達LIMIT. 然後它將只讀取 10 行，而不是整個表！

我在這個 Stack Overflow 答案中找到了解釋：

最大基數：所有值都是唯一的

最小基數：所有值都相同

某些列被稱為高基數列，因為它們具有適當的約束（如唯一），禁止您在每一行中放置相同的值。

基數是影響分群、排序和搜尋數據能力的屬性。因此，它是數據庫中查詢計劃器的重要衡量標準，是一種啟發式方法，可以用來選擇最佳計劃。

我跑了SHOW INDEX FROM table_name。我的索引“基數 store_id”是 2380，索引“store_deleted_idx” - store_id 2380 並刪除了 12

因此，MySQL 選擇第一個索引！

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