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

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

1. 在InnoDb综合指数表现的背景下,选择性和基数之间有什么区别?
2. 如果你创建一个综合指数,什么时候应该强调选择性,什么时候应该强调基数?

因为InnoDB使用B-Tree(B-Tree+)索引,并且从构建索引的最左边的列开始搜索coposite索引。

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

简言之,据我所知,最左边的列应该是所有行中最粗粒度的划分,复合索引的下一列应该是越来越多细粒度的行。

3. 这是基数吗?如果综合指数像我描述的那样建立,那么基数是高还是低?

4. 选择性呢?这和基数有关吗?

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//一百年

临时联接的第二个表:

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

将发布的查询:

(A) 查询特定的 一 , 二 , 三 , 四

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

(B) 查询特定的 一 , 二 , 三 以及 四

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) 查询特定的 一 , 二 还有 三 在…的范围内 四

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 具体来说 一 和 二 , 三 在名单上 three_list 和 四 在范围内

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 ).这个问题是关于复合索引最佳性能的基数和选择性,但是,如果上面的查询没有良好的列顺序选择,也欢迎使用替代表设计。

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

表中的行必须是唯一的 数据列表 我指的是 一 , 二 , 三 , 四 列值。