MySql 查询不使用复合索引

Question

我在 2 个表上有匹配的 Btree 索引，但解释计划说引擎正在对其中一个表进行全面扫描，而且速度非常慢。部署时两者都将从 ~750,000 行开始，关系是 em_localitems:em_submenuitemassoc => 1:n。我的理解是，下面带有 ** 的索引应该在下面的查询中运行良好，因为它们是从左到右使用的。

CREATE TABLE IF NOT EXISTS em_localitems (
  localitemid int(11) NOT NULL AUTO_INCREMENT,
  profitcenterid int(11) DEFAULT NULL,
  productid int(11) DEFAULT NULL,
  PRIMARY KEY (localitemid),
  UNIQUE KEY locationid (profitcenterid,productid),
  **KEY productid_2 (productid,profitcenterid)**
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;


CREATE TABLE IF NOT EXISTS em_submenuitemassoc (
  submenuitemassocid int(11) NOT NULL AUTO_INCREMENT,
  productid int(11) NOT NULL,
  profitcenterid int(11) NOT NULL DEFAULT '0',
  submenuid int(11) NOT NULL,
  enddate datetime DEFAULT NULL,
  PRIMARY KEY (submenuitemassocid),
  UNIQUE KEY productid (productid,profitcenterid,submenuid),
  **KEY productid_3 (productid,profitcenterid,submenuid)**
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

这是查询:

SELECT * from
em_submenuitemassoc sm
LEFT outer JOIN em_localitems li2 on li2.productid=sm.productid
  and li2.profitcenterid=sm.profitcenterid
  and sm.profitcenterid is not null

我也试过索引提示:

SELECT * from
em_submenuitemassoc sm **use index(productid_3)**
LEFT outer JOIN em_localitems li2 on li2.productid=sm.productid
  and li2.profitcenterid=sm.profitcenterid
  and sm.profitcenterid is not null

“显示来自 em_submenuitemassoc 的索引；”返回这个:

+---------------------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table               | Non_unique | Key_name       | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| em_submenuitemassoc |          1 | productid_3    |            1 | productid          | A         |        1136 |     NULL | NULL   |      | BTREE      |         | 
| em_submenuitemassoc |          1 | productid_3    |            2 | profitcenterid     | A         |        1136 |     NULL | NULL   |      | BTREE      |         | 
| em_submenuitemassoc |          1 | productid_3    |            3 | submenuid          | A         |        1136 |     NULL | NULL   |      | BTREE      |         | 
+---------------------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+

这是解释计划:

+----+-------------+-------+------+------------------------+-------------+---------+--------------------------------------------------+------+-------+
| id | select_type | table | type | possible_keys          | key         | key_len | ref                                              | rows | Extra |
+----+-------------+-------+------+------------------------+-------------+---------+--------------------------------------------------+------+-------+
|  1 | SIMPLE      | sm    | ALL  | NULL                   | NULL        | NULL    | NULL                                             | 1136 |       | 
|  1 | SIMPLE      | li2   | ref  | locationid,productid_2 | productid_2 | 10      | datatest.sm.profitcenterid,datatest.sm.productid |    1 |       | 
+----+-------------+-------+------+------------------------+-------------+---------+--------------------------------------------------+------+-------+

这有什么可怕的错误？

Answer 1

看到您对表 sm 的查询访问 type 是“ALL”。优化器知道无论连接条件如何，它都必须读取 sm 的所有行。

这可能是因为您正在使用 LEFT OUTER JOIN，它返回 所有 连接中左表的行，而不管左表中是否有匹配的行连接的右表。

您还使用了 SELECT *，因此无论如何它都必须获取 sm 的所有列。如果查询只需要索引中的列，则可以跳过读取基表。

所以在这个查询中使用索引没有任何好处，MySQL 通过跳过读取索引来提高效率。