MySQL 没有从索引中选择正确的行数

Question

我有下表

CREATE TABLE `test_series_analysis_data` (
  `email` varchar(255) NOT NULL,
  `mappingId` int(11) NOT NULL,
  `packageId` varchar(255) NOT NULL,
  `sectionName` varchar(255) NOT NULL,
  `createdAt` datetime(3) DEFAULT NULL,
  `marksObtained` float NOT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `testMetaData` longtext,
  PRIMARY KEY (`email`,`mappingId`,`packageId`,`sectionName`),
  KEY `rank_index` (`mappingId`,`packageId`,`sectionName`,`marksObtained`),
  KEY `mapping_package` (`mappingId`,`packageId`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

以下是查询解释的输出:

explain select rank 
from (
   select email, @i:=@i+1 as rank 
   from test_series_analysis_data ta 
   join (select @i:=0) va 
   where mappingId = ?1 
   and packageId = ?2 
   and sectionName = ?3 
   order by marksObtained desc
) as inter 
where inter.email = ?4;

+----+-------------+------------+------------+--------+----------------------------+-------------+---------+-------+-------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys              | key         | key_len | ref   | rows  | filtered | Extra                    |
+----+-------------+------------+------------+--------+----------------------------+-------------+---------+-------+-------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>                | <auto_key0> | 767     | const |    10 |   100.00 | NULL                     |
|  2 | DERIVED     | <derived3> | NULL       | system | NULL                       | NULL        | NULL    | NULL  |     1 |   100.00 | Using filesort           |
|  2 | DERIVED     | ta         | NULL       | ref    | rank_index,mapping_package | rank_index  | 4       | const | 20160 |     1.00 | Using where; Using index |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL                       | NULL        | NULL    | NULL  |  NULL |     NULL | No tables used           |
+----+-------------+------------+------------+--------+----------------------------+-------------+---------+-------+-------+----------+--------------------------+

查询优化器本可以同时使用这两个索引，但 rank_index 是一个覆盖索引，所以它被选中了。令我惊讶的是以下查询的输出:

explain select rank 
from ( 
  select email, @i:=@i+1 as rank 
  from test_series_analysis_data ta use index (mapping_package) 
  join (select @i:=0) va 
  where mappingId = ?1 
  and packageId = ?2 
  and sectionName = ?3 
  order by marksObtained desc
) as inter 
where inter.email = ?4;

+----+-------------+------------+------------+--------+-----------------+-----------------+---------+-------+-------+----------+-----------------------+
| id | select_type | table      | partitions | type   | possible_keys   | key             | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+-------+-------+----------+-----------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>     | <auto_key0>     | 767     | const |    10 |   100.00 | NULL                  |
|  2 | DERIVED     | <derived3> | NULL       | system | NULL            | NULL            | NULL    | NULL  |     1 |   100.00 | Using filesort        |
|  2 | DERIVED     | ta         | NULL       | ref    | mapping_package | mapping_package | 4       | const | 19434 |     1.00 | Using index condition |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL            | NULL            | NULL    | NULL  |  NULL |     NULL | No tables used        |
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+-------+-------+----------+-----------------------+

当使用的索引是 mapping_package 时，为什么 rows 较少 (19434<20160)。 rank_index 可以更好地选择所需的内容，因此 rank_index 中的行数应该更少。

那么对于给定的查询，这是否意味着 mapping_package 索引优于 rank_index？

sectionName 是 varchar 是否有任何影响，因此两个索引应该提供相似的性能？

此外，我假设 Using index condition 仅从索引中选择几行并扫描更多行。而在 的情况下使用 where;使用索引，优化器只需要读取索引而不是表来获取行，然后它正在选择一些数据。那为什么在使用 rank_index 时缺少 Using where 呢？

而且为什么mapping_package的key_len在索引只有两列的情况下是4？

感谢帮助。

Answer 1

(19434<20160) -- 这两个数字都是估计值。他们如此亲密是不寻常的。我敢打赌你会不会ANALYZE TABLE ，两者都会改变，可能会改变不平等。

请注意其他事项:Using where; Using index与 Using index condition .

但首先，让我提醒您，在 InnoDB 中，PRIMARY KEY列被附加到辅助键上。所以，实际上你有

KEY `rank_index`      (`mappingId`,`packageId`,`sectionName`,`marksObtained`,`email`)
KEY `mapping_package` (`mappingId`,`packageId`,`email`,`sectionName`)

现在让我们决定最佳索引应该是什么: 其中 mappingId = ?1 和 packageId = ?2 和 sectionName = ?3 按分数排序获得的desc

• 首先，=部分 WHERE : mappingId , packageId , sectionName , 以任何顺序；
• 然后是 ORDER BY列:marksObtained
• 奖励:最后 if email (在 SELECT 中唯一提到的其他列)在键中，它将是“覆盖”。

这表示 rank_index算是“完美”，另外一个指标就没那么好了。唉，EXPLAIN没有明确说明。

你也可以想出这个 - 你所需要的只是研究我的博客:http://mysql.rjweb.org/doc.php/index_cookbook_mysql (抱歉，时间不早了，我要厚脸皮了。)

其他提示:

• 不要盲目使用(255) .当需要 tmp 表时，这会使 tmp 表变大，从而降低效率。将限制降低到合理的程度。或者……
• 如果这是一个巨大的表，你真的应该“规范化”字符串，用可能是 2 字节的 SMALLINT UNSIGNED 替换它们.这将以其他方式提高性能，例如减少昂贵的 I/O。 (好的，20 行很小，所以这可能不适用。)

为什么是key_len 4？这意味着使用了一列，即 4 字节 INT mappingId .我原以为它也会使用第二列。所以，我很难过。 EXPLAIN FORMAT=JSON SELECT ...可能会提供更多线索。