为什么mysql在使用mysql中多列索引的第二列时仍然使用索引来获取数据?

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

为什么mysql在使用多列索引的第二列时,仍然使用索引来获取数据?我们知道mysql使用最左边的匹配规则,但是这里我没有使用第1列,我使用第2列,两列 select 下面的操作结果显示mysql有时使用索引,有时不使用索引。为什么?另外,我的mysql版本是5.6.17。
1.创建表:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_cid_INX` (`name`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

2.运行 select :

EXPLAIN SELECT * FROM student WHERE   cid=1;

三。结果:带索引的结果
它表明mysql使用索引来获取数据。
下表是另一个表格。
1.创建表:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `note` varchar(45) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NAME` (`name`),
  KEY `AGE` (`age`),
  KEY `LeftMostPreFix` (`name`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.运行 select :

explain SELECT * FROM test.test_table where  address = '东京'

3.结果:无指标结果
相反,这里显示mysql没有使用索引来获取数据。
比较以上两个结果,我感到困惑,为什么第一个结果使用索引,这是违反最左边的匹配规则。

qq24tv8q

qq24tv8q1#

来自mysql手册
key可能会命名一个不存在于possible\u keys值中的索引。如果所有可能的索引都不适合查找行,但查询选择的所有列都是其他索引的列,则会发生这种情况。也就是说,命名索引覆盖选定的列,因此尽管它不用于确定要检索的行,但索引扫描比数据行扫描更有效。
因此,虽然这里使用了一个键,但它实际上并不是正常意义上的。在某些情况下,将其用作表扫描(在第一个示例中)更有效,而在另一些情况下,可能不是(在第二个示例中)
大多数情况下,这些事情是由优化器基于一些事情(表的使用等)来决定的。
最好记住的是,这里不能“使用索引”,这就是为什么这里没有索引 possible keys . 只有第一列在索引中,才能使用索引。

muk1a3rh

muk1a3rh2#

两种情况下的索引都不是以 WHERE ,因此将对表或索引进行完全扫描。
案例1:索引是“覆盖”的,所以它是一个tossup,关于哪个(表扫描vs索引扫描)更好。优化器碰巧选择了第二个索引。 EXPLAIN FORMAT=JSON SELECT ... 可能有足够的细节来解释“为什么”。
案例二:因为 * (英寸 SELECT * ),第二个索引处于劣势--它不是“覆盖”,因此处理将在索引和数据之间来回跳转。因此,简单地扫描表格显然更好。
不要试图理解和解释(在这些情况下),而是将问题转过来:“针对这个表的这个查询的最佳索引是什么?”然后遵循这里的指导原则。

相关问题