一個 3 列索引 VS 三個 2 列索引的 InnoDB 性能影響

我將 MariaDB 與 InnoDB 一起使用，但這也適用於 MySql。

我有一個屬於類別的記錄列表，並希望按 3 個不同的標準對它們進行排序。

我為表模式和索引提出了兩種方法。

方法一

CREATE TABLE `document` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `category_id` MEDIUMINT NULL,
 `nosql_document_id` VARCHAR NULL,
 `sort_criteria_1` MEDIUMINT NULL,
 `sort_criteria_2` MEDIUMINT NULL,
 `sort_criteria_3` MEDIUMINT NULL,
 PRIMARY KEY (`id`),
 INDEX `idx_cat_sc1` (`category_id` ASC, `sort_critetia_1` ASC),
 INDEX `idx_cat_sc2` (`category_id` ASC, `sort_critetia_2` ASC),
 INDEX `idx_cat_sc3` (`category_id` ASC, `sort_criteria_3` ASC)
) ENGINE=InnoDB CHARACTER SET utf8;

每個排序條件的查詢如下所示：

SELECT id FROM document WHERE category_id = 1 ORDER BY sort_criteria_1 LIMIT 50,50;
SELECT id FROM document WHERE category_id = 1 ORDER BY sort_criteria_2 LIMIT 50,50;
SELECT id FROM document WHERE category_id = 1 ORDER BY sort_criteria_3 LIMIT 50,50;

方法二

CREATE TABLE `document` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `category_id` MEDIUMINT NULL,
 `nosql_document_id` VARCHAR NULL,
 `sort_criteria_id` TINYINT NULL,
 `sort` MEDIUMINT NULL,
 PRIMARY KEY (`id`),
 INDEX `idx_cat_combo` (`category_id` ASC, `sort_criteria_id` ASC, `sort` ASC)
) ENGINE=InnoDB CHARACTER SET utf8;

每個排序條件的查詢如下所示：

SELECT id FROM document WHERE category_id = 1 AND sort_criteria_id = 1 ORDER BY sort LIMIT 50,50;
SELECT id FROM document WHERE category_id = 1 AND sort_criteria_id = 2 ORDER BY sort LIMIT 50,50;
SELECT id FROM document WHERE category_id = 1 AND sort_criteria_id = 3 ORDER BY sort LIMIT 50,50;

對於 100 萬份文件，第一種方法需要 1M 條記錄，第二種方法需要 3M 條記錄，它使用：

方法 1：129M 總表大小。

方法 2：193M 總表大小。

假設我不需要超過 3 個排序標準：

我的問題：

1. 由於整體數據和索引大小較小，我可以假設方法 1 的速度更好嗎？
2. 在這裡有覆蓋索引是否有意義，以便 nosql_document_id 始終在聚集索引中？(id, nosql_document_id)
3. 有沒有更好的方法來完成以上我應該考慮的？我想不出一種方法來擁有一個包括排序在內的聚集覆蓋索引。有辦法嗎？

更新 1

Approach 2替換為以下內容可能是有意義的：

方法 3

CREATE TABLE `category` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE TABLE `list` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `category_id` MEDIUMINT UNSIGNED NOT NULL,
 `sort_criteria_id` TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (`id`),
 INDEX `idx_combo` (`category_id` ASC, `sort_criteria_id` ASC)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE TABLE `document` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `list_id` INT UNSIGNED NOT NULL,
 `nosql_document_id` VARCHAR NULL,
 `sort` MEDIUMINT UNSIGNED NULL,
 PRIMARY KEY (`id`),
 INDEX `idx_combo` (`list_id` ASC, `sort` ASC)
) ENGINE=InnoDB CHARACTER SET utf8;

然而，這直到沒有解決與@rick-james correclty 指出的相同數據有 3 個重複記錄的問題。感覺它歸結為在方法 2 和 3 中添加新排序選項的靈活性，而不是方法 1 更受限制但有效的方法。

進一步研究大小影響，我可以看出索引大小本身幾乎相同，在方法 1 和 3 之間。由於複製了主 ID，只有數據大小增長了 3 倍。我想知道這如何影響 innodb 緩衝區中的記憶體使用。我確實希望從記憶體中提取所有索引，這是否意味著就記憶體而言，方法是相同的，而區別僅在於磁碟使用情況？

結果如下：

show table status from approach1;
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+
| Name     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+
| category | InnoDB |      10 | Dynamic    |    100 |            163 |       16384 |               0 |            0 |         0 |            101 |
| document | InnoDB |      10 | Dynamic    | 943703 |             39 |    37289984 |               0 |     83558400 |   6291456 |        1000001 |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+
2 rows in set (0.001 sec)

show table status from approach3;
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
| category | InnoDB |      10 | Dynamic    |     100 |            163 |       16384 |               0 |            0 |         0 |            101 |
| document | InnoDB |      10 | Dynamic    | 2986718 |             33 |    99205120 |               0 |     79314944 |   5242880 |        3000001 |
| list     | InnoDB |      10 | Dynamic    |     300 |             54 |       16384 |               0 |        16384 |         0 |            301 |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+
3 rows in set (0.001 sec)

一個基本的數據庫原則是避免數據重複。選項 2 有 3 行基本數據相同。所以，投票給選項 1。

選項 1 總體上較小，所以再投一票。

如果你超過了 3 種，問題就會變得更加混亂。

你在做SELECT id。這是對這個討論的簡化嗎？如果您真的只選擇id，那麼我們應該討論這種低效率。

應始終考慮“覆蓋” ，但由於您的查詢未提及nosql_document_id，因此無需將其包含在索引中。（這可以追溯到我之前的評論。）

以開頭的複合二級索引PRIMARY KEY幾乎總是無用的。

列的某些組合是否唯一且不為空？如果是這樣，你能擺脫id嗎？如果是這樣，那麼整個討論就會改變。

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