mysql 쿼리가 잘못된 인덱스를 사용합니다.

mysql 쿼리가 잘못된 인덱스를 사용합니다.

mysql 데이터베이스의 일부 쿼리에 최적화 문제가 있습니다.애플리케이션을 구축한 후 mysqltuner를 사용하여 최적화하고 인덱싱되지 않은 쿼리를 찾기 위해 설명하려고 합니다.이 쿼리는 자주 실행되며 인덱스를 사용하지 않는 보고서입니다.

SELECT  count(*) AS rangedandselling
    FROM  
      ( SELECT  DISTINCT `store_formats`.`Store Name`
            FROM  (`eds_sales`
                    JOIN  `store_formats`
                      ON (`eds_sales`.`Store Nbr` = `store_formats`.`Store Nbr`)
                  )
            WHERE  `eds_sales`.`Prime Item Nbr` = '4'
              AND  `eds_sales`.`Date` BETWEEN CAST('2016-07-14' AS DATETIME)
                                          AND CAST('2016-07-21' AS DATETIME)
              AND  `store_formats`.`Format Name` IN ('format1','format2')
              AND  `store_formats`.`Store Name` IN (
                SELECT  DISTINCT `store_formats`.`Store Name`
                    FROM  (`eds_stock`
                            JOIN  `store_formats`
                              ON (`eds_stock`.`Store Nbr` = `store_formats`.`Store Nbr`)
                          )
                    WHERE  `eds_stock`.`Prime Item Nbr` = '4'
                      AND  `eds_stock`.`Date` BETWEEN CAST('2016-07-14' AS DATETIME)
                                                  AND CAST('2016-07-21' AS DATETIME)
                      AND  `store_formats`.`Format Name` IN ('format1','format2')
                      AND  `eds_stock`.`Curr Traited Store/Item Comb.` = '1' )
      ) t

다음은 설명 출력입니다. https://tools.mariadb.org/ea/pyb3h

조인 및 조회와 관련된 열을 인덱싱했지만 다른 인덱스를 선택하는 것처럼 보입니다. 이 인덱스는 고유성이라고 하며, 삽입에 사용하는 원본 열에 있는 6개의 열로 구성되어 있습니다(이러한 열의 조합이 행을 고유하게 만드는 유일한 요소이므로 이름을 지정했습니다).그런 다음 다른 열에 대한 인덱스가 있는지 확인하고 설명에서 해당 인덱스를 볼 수 있습니다.왜 이런 일이 생기는지 잘 모르겠는데, 누가 도와줄 수 있나요?

이 쿼리를 최적화하는 방법이 있습니까?

위의 링크가 작동하지 않는 경우에 대한 설명은 다음과 같습니다.

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 167048 |  |
| 2 | DERIVED | eds_sales | ref | uniqness,Prime Item Nbr,Store Nbr | uniqness | 4 | const | 23864 | Using where; Using index; Using temporary |
| 2 | DERIVED | store_formats | ref | Store Nbr,Store Name,Format Name | Store Nbr | 5 | equidata.eds_sales.Store Nbr | 1 | Using where |
| 2 | DERIVED | <subquery3> | eq_ref | distinct_key | distinct_key | 84 | func | 1 | Distinct |
| 3 | MATERIALIZED | store_formats | ALL | Store Nbr,Store Name,Format Name | NULL | NULL | NULL | 634 | Using where; Distinct |
| 3 | MATERIALIZED | eds_stock | ref | uniqness,Prime Item Nbr,Store Nbr | uniqness | 8 | const,equidata.store_formats.Store Nbr | 7 | Using where; Distinct |
+---+---+---+---+---+---+---+---+---+---+

관련 테이블 구조도 게시합니다.

--
-- Table structure for table `eds_sales`
--
CREATE TABLE `eds_sales` (
  `id` int(12) NOT NULL,
  `Prime Item Nbr` int(12) NOT NULL,
  `Prime Item Desc` varchar(255) NOT NULL,
  `Prime Size Desc` varchar(255) NOT NULL,
  `Variety` varchar(255) NOT NULL,
  `WHPK Qty` int(5) NOT NULL,
  `SUPPK Qty` int(5) NOT NULL,
  `Depot Nbr` int(5) NOT NULL,
  `Depot Name` varchar(255) NOT NULL,
  `Store Nbr` int(5) NOT NULL,
  `Store Name` varchar(255) NOT NULL,
  `EPOS Quantity` int(5) NOT NULL,
  `EPOS Sales` float(4,2) NOT NULL,
  `Date` date NOT NULL,
  `Client` varchar(255) NOT NULL,
  `Retailer` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `eds_sales`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniqness` (`Prime Item Nbr`,`Prime Item Desc`,`Prime Size Desc`,`Variety`,`WHPK Qty`,`SUPPK Qty`,`Depot Nbr`,`Depot Name`,`Store Nbr`,`Store Name`,`Date`,`Client`) USING BTREE,
  ADD KEY `Prime Item Nbr` (`Prime Item Nbr`),
  ADD KEY `Store Nbr` (`Store Nbr`);

테이블의 테이블 구조eds_stock

CREATE TABLE `eds_stock` (
  `Prime Item Nbr` int(12) NOT NULL,
  `Prime Item Desc` varchar(255) NOT NULL,
  `Prime Size Desc` varchar(255) NOT NULL,
  `Variety` varchar(255) NOT NULL,
  `Curr Valid Store/Item Comb.` int(12) NOT NULL,
  `Curr Traited Store/Item Comb.` int(12) NOT NULL,
  `Store Nbr` int(12) NOT NULL,
  `Store Name` varchar(255) NOT NULL,
  `Curr Str On Hand Qty` int(12) NOT NULL,
  `Curr Str In Transit Qty` int(12) NOT NULL,
  `Curr Str On Order Qty` int(12) NOT NULL,
  `Curr Str In Depot Qty` int(12) NOT NULL,
  `Curr Instock %` int(12) NOT NULL,
  `Max Shelf Qty` int(12) NOT NULL,
  `On Hand Qty` int(12) NOT NULL,
  `Date` date NOT NULL,
  `Client` varchar(255) NOT NULL,
  `Retailer` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `eds_stock`
  ADD UNIQUE KEY `uniqness` (`Prime Item Nbr`,`Store Nbr`,`Date`,`Client`,`Retailer`),
  ADD KEY `Prime Item Nbr` (`Prime Item Nbr`),
  ADD KEY `Store Nbr` (`Store Nbr`),
  ADD KEY `Curr Valid Store/Item Comb.` (`Curr Valid Store/Item Comb.`);

테이블의 테이블 구조store_formats

CREATE TABLE `store_formats` (
  `id` int(12) NOT NULL,
  `Store Nbr` int(4) DEFAULT NULL,
  `Store Name` varchar(27) DEFAULT NULL,
  `City` varchar(19) DEFAULT NULL,
  `Post Code` varchar(9) DEFAULT NULL,
  `Region #` int(2) DEFAULT NULL,
  `Region Name` varchar(10) DEFAULT NULL,
  `Distr #` int(3) DEFAULT NULL,
  `Dist Name` varchar(26) DEFAULT NULL,
  `Square Footage` varchar(7) DEFAULT NULL,
  `Format` int(1) DEFAULT NULL,
  `Format Name` varchar(23) DEFAULT NULL,
  `Store Type` varchar(20) DEFAULT NULL,
  `TV Region` varchar(12) DEFAULT NULL,
  `Pharmacy` varchar(3) DEFAULT NULL,
  `Optician` varchar(3) DEFAULT NULL,
  `Home Shopping` varchar(3) DEFAULT NULL,
  `Retailer` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `store_formats`
  ADD PRIMARY KEY (`id`),
  ADD KEY `Store Nbr` (`Store Nbr`),
  ADD KEY `Store Name` (`Store Name`),
  ADD KEY `Format Name` (`Format Name`);

CAST('2016-07-14' AS DATETIME)그CAST필요 없음;'2016-07-14'잘 작동합니다. (특히 날짜와 비교하기 때문에)

IN ( SELECT ... )비효율적입니다.a로 변경JOIN.

온eds_stock대신에

INDEX(`Prime Item Nbr`)

다음 두 가지가 있습니다.

INDEX(`Prime Item Nbr`, `Date`)
INDEX(`Prime Item Nbr`, `Curr Traited Store/Item Comb.`, `Date`)

INT라고 해도 항상 4바이트 숫자입니다.int(2)로 전환하는 것을 고려합니다.TINYINT UNSIGNED(및 다른 크기의INT).

float(4,2)사용 안 함(m,n)그것은 여분의 반올림과 나의 원인이 원하지 않는 절단을 유발합니다.둘 중 하나 사용DECIMAL(4,2)(돈을 위해서), 또는 단순한.FLOAT.

벌레요? 정말로 일주일이 아니라 8일을 원했나요?

AND `Date` BETWEEN CAST('2016-07-14' AS DATETIME) AND CAST('2016-07-21' AS DATETIME)

이 패턴이 마음에 듭니다.

AND `Date` >= '2016-07-14'
AND `Date`  < '2016-07-14' + INTERVAL 1 WEEK

두 개의 선택 대신

SELECT  count(*) AS rangedandselling
    FROM ( SELECT  DISTINCT `store_formats`.`Store Name` ...

한 번만 선택하면 작동(그리고 더 빠름)할 수 있습니다.

SELECT COUNT(DISTINCT `store_formats`.`Store Name`) AS rangedandselling ...

대부분의 문제를 해결한 후에도 여전히 문제가 있는 경우 '잘못된 색인'에 대한 질문으로 돌아갈 수 있습니다. (추가적인 도움이 필요한 경우 새 질문을 시작하십시오.)

언급URL : https://stackoverflow.com/questions/38522104/mysql-query-using-the-wrong-indexes