如何改进索引以使查询以毫秒为单位执行？

Question

我整晚都在试图在下面的查询中获得更好的性能，但没有成功：

    SELECT COUNT(o.id_offer)
      FROM offer o
      JOIN offer_product op
        ON op.id_offer = o.id_offer    
      JOIN advertiser a
        ON a.id_advertiser = o.id_advertiser 
      LEFT 
      JOIN offer_hidden h
        ON h.id_offer = o.id_offer 
       AND h.id_user = 5064
     WHERE o.finality = 'sale' 
       AND h.id_offer IS NULL;
+-------------------+
| COUNT(o.id_offer) |
+-------------------+
|            248250 |
+-------------------+
1 row in set (2.80 sec)

它大约需要2~4秒才能执行。应用程序需要执行大约8~10个类似的查询，因此，执行时间必须是毫秒。

解释查询：

+------+-------------+---------------+--------+---------------------------------------------------------+-----------------------------+---------+------------------------------+--------+--------------------------------------+
| id   | select_type | table         | type   | possible_keys                                           | key                         | key_len | ref                          | rows   | Extra                                |
+------+-------------+---------------+--------+---------------------------------------------------------+-----------------------------+---------+------------------------------+--------+--------------------------------------+
|    1 | SIMPLE      | offer         | ref    | PRIMARY,fk_offer_advertiser1_idx,fk_offer_finality1_idx | fk_offer_finality1_idx      | 1       | const                        | 167269 | Using index condition                |
|    1 | SIMPLE      | offer_product | ref    | fk_offer_product_offer1_idx                             | fk_offer_product_offer1_idx | 4       | db.offer.id_offer            |      1 | Using index                          |
|    1 | SIMPLE      | advertiser    | eq_ref | PRIMARY                                                 | PRIMARY                     | 4       | db.offer.id_advertiser       |      1 | Using index                          |
|    1 | SIMPLE      | offer_hidden  | eq_ref | PRIMARY,fk_offer_hidden_user1_idx                       | PRIMARY                     | 8       | db.offer.id_offer,const      |      1 | Using where; Using index; Not exists |
+------+-------------+---------------+--------+---------------------------------------------------------+-----------------------------+---------+------------------------------+--------+--------------------------------------+
4 rows in set (0.00 sec)

和offer的索引(高基数)：

SHOW INDEXES FROM offer;
+-------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                 | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| offer |          0 | PRIMARY                  |            1 | id_offer      | A         |      352683 |     NULL | NULL   |      | BTREE      |         |               |
| offer |          1 | fk_offer_advertiser1_idx |            1 | id_advertiser | A         |      352683 |     NULL | NULL   | YES  | BTREE      |         |               |
| offer |          1 | fk_offer_finality1_idx   |            1 | finality      | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.04 sec)

我的开发环境目前有大约30万的报价和20万的广告商，但是产品分局分别有800万和200万(分别)。

     SELECT COUNT(*) FROM offer;
+----------+
| COUNT(*) |
+----------+
|   327513 |
+----------+
1 row in set (0.06 sec)


    SELECT COUNT(*) FROM advertiser;
+----------+
| COUNT(*) |
+----------+
|   214885 |
+----------+
1 row in set (0.14 sec)

创建表语句：

CREATE TABLE `offer` (
  `id_offer` int(11) NOT NULL AUTO_INCREMENT,
  `id_advertiser` int(11) unsigned NOT NULL,
  `description` text,
  `date_offer` datetime NOT NULL,
  `finality` enum('buy','sale') NOT NULL,
  PRIMARY KEY (`id_offer`),
  KEY `fk_offer_advertiser1_idx` (`id_advertiser`),
  KEY `fk_offer_finality1_idx` (`finality`),
  CONSTRAINT `fk_offer_advertiser1` FOREIGN KEY (`id_advertiser`) REFERENCES `advertiser` (`id_advertiser`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

我是不是遗漏了什么？

编辑1 - In reply to @Strawberry

CREATE TABLE `advertiser` (
  `id_advertiser` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `locale` varchar(10) DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id_advertiser`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `offer_product` (
  `id_offer_product` int(11) NOT NULL AUTO_INCREMENT,
  `id_offer` int(11) NOT NULL,
  `id_product` int(11) NOT NULL,
  PRIMARY KEY (`id_offer_product`),
  KEY `fk_offer_product_offer1_idx` (`id_offer`),
  KEY `fk_offer_product_product1_idx` (`id_product`),
  KEY `fk_offer_product_offer_product1_idx` (`id_offer`,`id_product`),
  KEY `fk_offer_product_product_offer1_idx` (`id_product`,`id_offer`),
  CONSTRAINT `fk_offer_product_offer1` FOREIGN KEY (`id_offer`) REFERENCES `offer` (`id_offer`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_offer_product_product1` FOREIGN KEY (`id_product`) REFERENCES `product` (`id_product`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 CREATE TABLE `offer_hidden` (
  `id_offer` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  PRIMARY KEY (`id_offer`,`id_user`),
  KEY `fk_offer_hidden_user1_idx` (`id_user`),
  CONSTRAINT `fk_offer_hidden_user1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

您必须设置多列索引：

类似这样的东西

ALTER  TABLE offer ADD INDEX(finality, id_offer, id_advertiser);
ALTER  TABLE offer_product ADD INDEX(id_offer);
ALTER  TABLE advertiser ADD INDEX(id_advertiser);
ALTER  TABLE offer_hidden ADD INDEX(id_offer, id_user);

这样，您的搜索将首先通过最终性检索提供，然后执行连接，使用每个表的索引。

Answer 2

-- covering index access to 'offer'
-- (and, perhaps, better caching for 'offer_product' too)
ALTER TABLE offer ADD INDEX(finality, id_offer, id_advertiser);

SELECT COUNT(o.id_offer)
FROM (
    -- do 'left join' first to minimize number of rows
    -- before scanning 'offer_product' and 'advertiser'
    SELECT offer.id_offer, offer.id_advertiser
    FROM offer
    LEFT JOIN offer_hidden h ON
        h.id_offer = offer.id_offer
        AND h.id_user = 5064
    WHERE
        offer.finality = 'sale'
        AND h.id_offer IS NULL
) o
JOIN offer_product op ON
    op.id_offer = o.id_offer
JOIN advertiser a ON
    a.id_advertiser = o.id_advertiser
;