MySQL 在表连接中不使用索引

Question

我有 2 个表，产品和产品_图片，有 10 万行。

我在product_pic表的porduct_id上有索引，但是mysql在加入它们时不使用索引!

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `type` int(11) NOT NULL,
  `price` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `keyword` text CHARACTER SET utf8 NOT NULL,
  `summary` text COLLATE utf8_general_ci NOT NULL,
  `note` text CHARACTER SET utf8 NOT NULL,
  `catid` int(11) NOT NULL DEFAULT 0,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `expdate` date NOT NULL DEFAULT '0000-00-00',
  `show` int(11) NOT NULL DEFAULT 0,
  `hit` int(11) NOT NULL,
  `userid` int(11) NOT NULL DEFAULT 0,
  `usertype` tinyint(4) NOT NULL DEFAULT 0,
  `rand` int(11) NOT NULL DEFAULT 0,
  `provinceid` tinyint(4) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `active` tinyint(4) NOT NULL,
  `expdate_email` tinyint(4) NOT NULL,
  `active_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `weight` float NOT NULL,
  `ucategory` int(11) NOT NULL DEFAULT -1,
  `searchstatus` tinyint(1) NOT NULL DEFAULT 0,
  `vitreen` tinyint(4) NOT NULL DEFAULT 1,
  `summery` varchar(256) COLLATE utf8_general_ci NOT NULL,
  `notification` varchar(255) COLLATE utf8_general_ci NOT NULL,
  `active_buy` tinyint(4) NOT NULL DEFAULT 1,
  `autotranslate` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `usertype` (`usertype`),
  KEY `catid` (`catid`),
  KEY `active` (`active`),
  KEY `type` (`type`),
  KEY `userid` (`userid`),
  KEY `update_time` (`update_time`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `note` (`note`,`keyword`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=42987 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC

产品图片:

 CREATE TABLE `product_pic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT 0,
  `productid` int(11) NOT NULL DEFAULT 0,
  `pic` varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `default_img` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`,`productid`),
  KEY `productid` (`productid`)
) ENGINE=InnoDB AUTO_INCREMENT=48020 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

我尝试使用提示，但什么也没发生!

我们的查询:

SELECT p.`id`, p.`title` name, product_pic.`pic` 
FROM `product` p
LEFT JOIN `product_pic` USE INDEX (productid)  ON p.`id`=product_pic.`productid`
where p.id in (SELECT * FROM `PID`) GROUP BY p.`id`;

解释输出:

+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+
| id   | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows | Extra                                           |
+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY     | p           | ALL    | PRIMARY       | NULL    | NULL    | NULL |    1 | Using temporary; Using filesort                 |
|    1 | PRIMARY     | product_pic | ALL    | productid     | NULL    | NULL    | NULL |    1 | Using where; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | PID         | eq_ref | PRIMARY       | PRIMARY | 4       | p.id |    1 | Using index                                     |
+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+

我们的查询需要 3 分钟才能完成 36 行!!

36 rows in set (3 min 27.481 sec)

Answer 1

• 每个产品最多有 1 张图片吗？如果是这样，则不需要 GROUP BY。

• 如果每个产品有多个图片，则 GROUP BY 使用不当 - 在 GROUP BY 之后，即 Product_pic.pic 你想要它显示吗？

• 其中 p.id in (SELECT * FROM PID) --> JOIN PID USING(id)

• EXPLAIN使用了伪造的——它说ALL，但是Rows=1，每个表中只有一行吗??

• 独立于其他更改，将 KEY (productid) 更改为 KEY(productid, pic)。

• 避免索引提示。