代码之家  ›  专栏  ›  技术社区  ›  Jimmix

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

  •  0
  • Jimmix  · 技术社区  · 5 年前

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

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

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

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

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

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

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

    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语句。插入将是罕见的(每天一次),不需要更新、删除。

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

    0 回复  |  直到 5 年前
        1
  •  0
  •   Rick James diyism    5 年前

    基数与选择性 单列 在某种程度上是无关紧要的 混合成的 指数

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

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

    1. 使用“=”测试列,即使它是“标志”。
    2. IN(const, ...) --优化器可能能够跳过索引。
    3. 一个“范围”。将不再研究进一步的范围。

    “覆盖”指数会导致一些例外情况。

    你的例子:

    (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) 查询4范围内的具体1、2和任意3

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

    但是,这一次, ORDER BY two, three, four 也无法处理;将有一个“文件排序”。

    或者(尽管不太可能),优化器可以选择使用 订购人 而不是 WHERE .在这种情况下,这是最佳的:

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

    (D) 使用JOIN查询特定的1和2,3个在列表中,3个在列表中,4个在范围内

    这会变得更加棘手,因为优化器将选择从哪个表开始。 通常 它会选择一个有更多过滤可用的,你在数据列表中过滤一,二,四?但另一张桌子上一点也没有。所以

    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_INCREMENT ; 如果你没有从一个(或多个)列构建“自然”PK,那么这是一种退路。

    自动增量 不强制列成为主键。然而,你必须 一些 指数 启动 自动增量 柱这是唯一的限制。

    “另类桌子设计也受欢迎”——我们需要有一个什么样的感觉 one (等)真的是。

    如果您有这4个查询,并且希望得到最佳的索引集:

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

    (其他组合也会做得同样好。)

    如果你在面试中更挑剔 SELECT 子句,然后我还要讨论“覆盖”索引。

    推荐文章