为什么mysql不能使用部分主键索引?

w1e3prcc  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(291)

mysql文档描述了索引扩展的使用,以下表为例,下面是查询:

CREATE TABLE t1 (
    i1 INT NOT NULL DEFAULT 0,
    i2 INT NOT NULL DEFAULT 0,
    d DATE DEFAULT NULL,
    PRIMARY KEY (i1, i2),
    INDEX k_d (d)
) ENGINE = InnoDB;

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

innodb将在内部转换索引 k_d 在末尾包含主键。即实际指数 k_d 将打开 (d, i1, i2) ,三列。
文件接着解释说(我的重点):
在这种情况下,优化器不能使用主键,因为主键由列(i1、i2)组成,并且查询没有引用i2。相反,优化器可以在(d)上使用辅助索引k\u d,执行计划取决于是否使用扩展索引。
我对上述说法感到困惑。首先它说 i1 不足以使用两列的主键索引 (i1, i2) . 然后,在第二句中,它说 k_d(d, i1, i2) 可以使用,尽管只有 d 以及 i1 正在使用,与 i2 缺席。
我对mysql和其他类型的sql中的索引的一般理解是,如果索引中所有列的子集都存在(从左边开始),则可以使用索引的左边部分。
主键(聚集)索引和允许后者使用部分索引但前者不能使用的非聚集辅助索引有什么不同?

oalqel3c

oalqel3c1#

您链接到的页面上的文档部分不准确。
演示,在MySQL5.7.21上运行:

mysql [localhost] {msandbox} (test) > CREATE TABLE t1 (
    ->     i1 INT NOT NULL DEFAULT 0,
    ->     i2 INT NOT NULL DEFAULT 0,
    ->     d DATE DEFAULT NULL,
    ->     PRIMARY KEY (i1, i2),
    ->     INDEX k_d (d)
    -> ) ENGINE = InnoDB;

mysql [localhost] {msandbox} (test) > explain SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | PRIMARY,k_d   | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

这个查询选择主索引,您可以看到 key_len 是4,证明它将只使用32位int列中的一列。
当使用innodb表时,mysql通常更喜欢使用主索引(聚集索引),因为它比使用辅助索引更有效。

i2byvkas

i2byvkas2#

在这种情况下

WHERE i1 = 3 AND d = '2000-01-01';

我更愿意明确地说 INDEX(d, i1) (或 INDEX(i1, d) ). 我的理由是,我告诉读者,我考虑了索引,并意识到这是最好的查询。这将是一个“覆盖”指数,因此速度更快。
当然,这个 INDEX(d) 你应该和 INDEX(d, i1, i2) ,应该得到有效的利用。至于原因,我猜是优化器的疏忽。
至于文件,有几个措辞不恰当的地方。他们欢迎在bugs.mysql.com上发表文档评论。

相关问题