쿼리를 밀리초 안에 실행하기 위해 인덱스를 향상시키는 방법은 무엇입니까?

쿼리를 밀리초 안에 실행하기 위해 인덱스를 향상시키는 방법은 무엇입니까?

아래 쿼리에서 더 나은 성능을 얻기 위해 밤새 노력했지만 성공하지 못했습니다.

    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만 건의 광고주를 보유하고 있지만, 프로덕션 DB는 각각 약 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 - @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

다중 열 인덱스를 설정해야 합니다.

그런 거.

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);

이렇게 하면 검색에서 먼저 마지막으로 제안을 검색한 다음 각 테이블에 대한 인덱스를 사용하여 가입을 수행합니다.

-- 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
;

언급URL : https://stackoverflow.com/questions/28643794/how-to-improve-indexes-in-order-to-get-queries-executed-in-milliseconds