使用兩個範圍條件優化查詢

我有一個類似於這個的結構：

CREATE TABLE `author` (
 `id` int(11) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE `book` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `author_id` int(11) unsigned NOT NULL,
 `org` int(11) unsigned NOT NULL,
 `country` char(3) NOT NULL,
 `publish_date` date NOT NULL,
 `price` decimal(6,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `author_id` (`author_id`),
 KEY `publish_date` (`publish_date`),
 KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
 KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

這是一個SQL 小提琴

所以我正在嘗試使用兩個範圍條件執行查詢

SELECT
 id as author_id,
 (SELECT COUNT(DISTINCT `book`.`id`)+1 
  FROM `book` 
  WHERE 
     `book`.`org` = 1
     AND `book`.`country` = 'USA' 
     AND `book`.`publish_date` BETWEEN '2010-04-30' AND '2011-04-30'
     AND `book`.`author_id` = `author`.`id`
     AND `book`.`price` < 50
 ) AS `books_under_fifty`
FROM `author` 
ORDER BY books_under_fifty desc;

但優化器只使用我的索引的一部分：const,const,db_9_6349e2.author.id從i0

有沒有辦法優化它？

好吧，讓我們跳出框框思考。似乎有兩組結果——作者有一些這樣的書，而那些沒有。第一種情況通過以下方式更有效地完成：

SELECT  author_id,
       COUNT(*)+1 AS books_under_fifty
   FROM  `book`
   WHERE  `org` = 1
     AND  `country` = 'USA'
     AND  publish_date >= '2010-05-01'
     AND  publish_date <  '2010-05-01' + INTERVAL 1 YEAR
     AND  `price` < 50 

這可能最好由

INDEX(country, org, publish_date, price, author_id)

如果您對它的表現感到滿意，那麼您可以跳出框框思考尋找“其餘作者”。

回到 2 範圍問題。這有時可以通過分區來解決。不知道數據的分佈（一年是總數的一小部分嗎？或者價格呢？），我不能說哪個會更好：

PARTITION BY RANGE(TO_DAYS(publish_date))

按價格分區已失效，因為DECIMAL無法使用。儲存price為美分數量並使用PARTITION BY RANGE(cents)會起作用，但很笨拙。

然後對分區鍵進行 20-50 個分區。這將為您提供一個“範圍”的“分區修剪”，但隨後需要針對另一個範圍調整索引。假設您按以下方式劃分publish_date：

INDEX(country, org, price)
PRIMARY KEY(id, publish_date)

同時， …

如果您使用標準國家/地區程式碼，請不要使用 utf8；至少在該列中使用 ascii。

這org本書有什麼區別？還是有一些缺失的列？沒有其他“獨特”列嗎？

另一個可能更好的變體：

PRIMARY KEY(country, org, price,  -- for clustering
            publish_date,   -- because partitioning requires it
            id)           -- to assure uniqueness
INDEX(id)

此變體假定您沒有“自然”PK。而且它會更有效，因為它將所需的行聚集在一起。我沒有包含author_id在 PK 中，因為它在行中很容易獲得。

添加索引

ADD INDEX ac(author_id, country)欺騙它使用i0. 去搞清楚。注意：我保持(author_id)原樣並在索引列表中排名第一；所以它不僅僅是索引的順序。

我正在使用 Percona 的 5.6.22-71.0-log 。

請注意，只有 4 個不同的country值，均勻分佈。但是有很多不同author_id，使其本身更具“選擇性”。

您可以將子查詢更改為LEFT JOIN, 和GROUP BY：

SELECT
   author.id as author_id, (count(distinct book.id) + 1) AS books_under_fifty
FROM
   author
   LEFT JOIN book ON
         book.author_id = author.id
     AND book.org = 1
     AND book.country = 'USA' 
     AND book.publish_date BETWEEN '2010-04-30' and '2011-04-30'
     AND book.price < 50
GROUP BY
   author.id
ORDER BY 
    books_under_fifty desc, author_id;

…但在執行計劃方面確實沒有任何區別。

你可以在*這裡*查看dbfiddle的所有內容

所以，恐怕答案很可能是，你無法進一步優化。無論如何，我建議使用真實數據進行測試，而不是使用我使用的模擬。

筆記：

1. 我添加了第二個 ORDER BY 表達式，以確保順序完全確定。
2. 首先檢查 MariaDB，而不是 MariaDB。MySQL 使用兩種不同的計劃，但它甚至做出了更糟糕的選擇（在http://rextester.com/JDHP57216上查看）

您還可以檢查以下內容的變體LEFT JOIN：

SELECT
   author.id as author_id, coalesce(ccc, 1) AS books_under_fifty
FROM
   author
   LEFT JOIN 
   (SELECT 
       author_id, count(distinct book.id) + 1 AS ccc
   FROM
       book 
   WHERE
           book.org = 1
       AND book.country = 'USA' 
       AND book.publish_date BETWEEN '2010-04-30' and '2011-04-30'
       AND book.price < 50
    GROUP BY
         author_id
    ) AS q0
    ON q0.author_id = author.id
ORDER BY 
    books_under_fifty desc, author_id;

但是，再一次，MariaDB 似乎沒有任何優勢。

dbfiddle在這裡

---

另一個開源數據庫（PostgreSQL 9.6）可以以更複雜的方式處理事情，並且可以為您提供更好的執行計劃……（而且很可能更快，儘管 MariaDB 沒有得到任何時間）。

PostgreSQL 可以使LEFT JOIN工作更快（在大多數情況下，儘管這取決於每次執行的隨機性）。

dbfiddle在這裡

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