MySQL InnoDB 复合索引选择性和基数对性能的影响

Question

我想问几个有关 InnoDB 引擎的复合索引中的列顺序的问题，以及有关应满足的基数和选择性的解释(以及如何做到这一点)，以便在提供的示例中获得最佳性能.

1. 在 InnoDb 综合索引性能方面，选择性和基数之间有什么区别？
2. 如果创建复合索引，什么时候应该强调选择性，什么时候应该强调基数？

由于 InnoDB 使用 B-Tree (B-Tree+) 索引，并且复合索引是从构建索引的最左边的列开始搜索的。

我的理解是，使用这样的列组合顺序是有意义的，即最左边的列会将索引的最大不匹配部分从搜索中抛出，并继续使用较少的数据进行搜索，复合索引的下一列应具有相同的属性，因此所有其他列应具有相同的属性，直到查找缩小行数可能匹配到可以扫描精确匹配行的最小数量。

简而言之，我理解最左边的列应该是所有行的最粗粒度的划分，复合索引的下一列应该是越来越细粒度的行。

这是基数吗？如果复合索引按照我所描述的方式构建，那么基数是高还是低？

选择性怎么样？这与基数相关吗？

5.如下表设计如何获得最佳选择性和基数？

CREATE TABLE IF NOT EXISTS `data_list` (
  `one` varchar(64) NOT NULL,
  `two` mediumint unsigned NOT NULL,
  `three` varchar(128) NOT NULL,
  `four` datetime NOT NULL,
  `five` DECIMAL(5,2)
) ENGINE = InnoDB;

列的最大不同计数值为:
one最多 10 个；
two最多 100 个；
three最多 1000；
four最大 36500；//100年

临时连接的第二个表:

CREATE TEMPORARY TABLE IF NOT EXISTS `three_list` (
    `l_three` varchar(128) PRIMARY KEY NOT NULL
) ENGINE = InnoDB;

将发出的查询:

(A) 查询特定one , two , three , four

SELECT *
FROM 
    `data_list`
WHERE 
    `one` = 'abc'
AND    
    `two` = 1
AND    
    `three` = 'xyz'
AND    
    `four` = '2018-01-01'
;

(B) 查询特定one , two , three和范围 four

SELECT *
FROM 
    `data_list`
WHERE 
    `one` = 'abc'
AND    
    `two` = 1
AND    
    `three` = 'xyz'
AND    
    `four` >= '2018-01-01'
AND
    `four` < '2019-01-01'
ORDER BY
    `two`,
    `three`,
    `four`
;

(C) 查询特定one , two和任何three在 four 范围内

SELECT *
FROM 
    `data_list`
WHERE 
    `one` = 'abc'
AND    
    `two` = 1
AND    
    `four` >= '2018-01-01'
AND
    `four` < '2019-01-01'
ORDER BY
    `two`,
    `three`,
    `four`
;

(D) 查询 JOIN具体one和two , three上榜three_list和four范围内

SELECT *
FROM 
    `data_list`
INNER JOIN 
    `three_list`
ON 
    `three` = `l_three`
WHERE 
    `one` = 'abc'
AND    
    `two` = 1
AND    
    `four` >= '2018-01-01'
AND
    `four` < '2019-01-01'
ORDER BY
    `two`,
    `three`,
    `four`
;

也许整个表的设计从一开始就有缺陷(即由于没有 PK id 和 data_list 的 auto_increment )。这个问题是关于复合索引最佳性能的基数和选择性，但是如果没有良好的列顺序选择来进行上述查询，也欢迎使用替代表设计。

对我来说最重要的性能是 SELECT 语句。插入将很少见(每天一次)，并且不需要更新、删除。

data_list 中的行必须是唯一的我的意思是 one 的组合, two , three , four列值。

Answer 1

各个列的基数和选择性在复合索引中是不相关的。

将 InnoDB BTree 索引中的值视为指定列的串联。

大致按以下顺序用列填充索引:

1. 使用“=”测试的列，即使它是“标志”。
2. IN(const, ...) -- 优化器也许能够跳过索引。
3. 一个“范围”。将不再考虑其他范围。

“覆盖”索引会导致一些异常(exception)情况。

你的例子:

(A)查询特定一、二、三、四:

    INDEX(one, two, three, four) -- in _any_ order

(B) 查询特定的一、二、三和四的范围

    INDEX(one, two, three,  -- put these first (=), in _any_ order
          four)             -- after the =s

ORDER BY 也会被处理

(C) 查询特定的一、二以及四范围内的任意三

    INDEX(one, two,         -- either order; including `three` would hurt
          four)             -- after the =s

但是，这一次，ORDER BY 二、三、四也无法处理；将会有一个“文件排序”。

或者(尽管不太可能)，优化器可以选择使用 ORDER BY 而不是 WHERE。在这种情况下，这是最佳的:

INDEX(two, three, four)  -- in the same order as the ORDER BY.

(D) 使用 JOIN 查询特定的一和二，其中三个在列表 Three_list 上，四个在范围内

这变得更加棘手，因为优化器将选择从哪个表开始。 通常它会选择具有更多可用过滤功能的那个，您正在对 data_list 中的一、二、四进行过滤吗？但在另一张 table 上根本没有。所以...

data_list: INDEX(one, two,   -- either order,
                 four)       -- range
three_list;  INDEX(l_three)

更多:http://mysql.rjweb.org/doc.php/index_cookbook_mysql

“没有PK”——是的，这很糟糕。但它不一定是 AUTO_INCRMENT；如果您没有从一个(或多个)列构建“自然”PK，这是一种后备方案。

AUTO_INCRMENT 不会强制该列成为 PK。但是，您必须拥有以 AUTO_INCRMENT 列开始的一些索引。这是唯一的限制。

“另类表格设计也受到欢迎”——我们需要了解one(等等)的真正含义。

如果您有这 4 个查询并且想要一组最佳索引:

INDEX(one, two, three, four)
INDEX(one, two,        four)
INDEX(     two, three, four)

(其他组合也同样有效。)

如果您在 SELECT 子句中更具选择性，那么我还会谈论“覆盖”索引。