가입 시 대용량 데이터 세트 쿼리(1500만 행 이상)

가입 시 대용량 데이터 세트 쿼리(1500만 행 이상)

두 테이블에 앉으려고 하는데products그리고.products_markets.하는 동안에products100만 건을 밑돌고 있어요product_markets2천만 장 가까이 됩니다.데이터가 변경되었기 때문에 스키마 작성 테이블에 오타가 있을 수 있습니다.

CREATE TABLE `products_markets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `country_code_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`product_id`,`country_code_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21052102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer_id` int(10) unsigned NOT NULL,
  `department_id` int(10) unsigned NOT NULL,
  `code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `popularity` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_code_unique` (`code`),
  KEY `products_department_id_foreign` (`department_id`),
  KEY `products_manufacturer_id_foreign` (`manufacturer_id`),
  CONSTRAINT `products_department_id_foreign`
       FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  CONSTRAINT `products_manufacturer_id_foreign`
       FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=731563 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

특정 국가에서 가장 인기 있는 상품 50장을 반환하려고 하는데 예상보다 높은 50초 정도 시간이 걸립니다.

몇 가지 다른 질문을 해봤지만 성공하지 못했습니다.

select  `products_markets`.`product_id`
    from  products_markets
    left join  
        ( SELECT  products.id, products.popularity
            from  products
        ) p  ON p.id = products_markets.product_id
    where products_markets.country_code_id = 121
    order by  `popularity` desc, `p`.`id` asc
    limit  50 

그리고.

select  `products`.*
    from  `products`
    where  products.id in (
        SELECT  product_id
            from  products_markets
            where  products_markets.country_code_id = 121
                          )
    group by  `products`.`name`, `products`.`manufacturer_id`
    order by  `popularity` desc, `products`.`id` asc
    limit  50 

이 쿼리의 설명은 다음과 같습니다.

id  select_type  table              type possible_keys key           key_len refs             rows              extra
1   PRIMARY      products           ALL  PRIMARY       NULL          NULL    NULL             623848            Using temporary; Using filesort
1   PRIMARY      products_markets   ref  unique_index  unique_index  4       main.products.id 14                Using where; Using index; FirstMatch(products)

제가 즐기는 한 가지 방법은 질문을 줄이기 위해 제품_시장을 각국의 개별 테이블로 나누는 것입니다.서버에 메모리를 증설하려고 했지만 그다지 성공하지 못했습니다.데이터베이스 설계/쿼리에 현저하게 문제가 있는 것을 식별할 수 있는 사람이 있습니까?

이 쿼리를 현재 최대 50초의 극히 일부분으로 만들기 위해 사용할 수 있는 다른 옵션은 무엇입니까?

제거하다id에products_markets추가하다

PRIMARY KEY(country_code_id, product_id)

그럼, 그 다음,UNIQUE키를 누릅니다(다른 쿼리에 필요하지 않은 경우).

이렇게 하면 큰 테이블의 디스크 설치 공간이 크게 줄어들기 때문에 테이블에 영향을 미치는 모든 쿼리 속도가 빨라질 수 있습니다.

그리고 그것은 하마자가 제안한 개혁에 도움이 될 것이다.

먼저 products_market 테이블에서 지정된 국가의 모든 제품을 선택하는 것보다 선호도에 따라 제품 테이블에서 해당 제품을 선택하고 50개로 제한한다는 이 질문의 의미를 시험해 보십시오.제품을 하지 않도록 하세요.* 데이터가 필요한 필드만 선택합니다.

select  products_markets.product_id, products_markets.county_code_id,
        products.*
    from  products_markets,products
    where  products_markets.country_code_id = 121
      and  products_markets.product_id=products.id
    group by  `products`.`name`, `products`.`manufacturer_id`
    order by  `products_markets.popularity` desc, `products`.`id` asc
    limit  50

언급URL : https://stackoverflow.com/questions/39128824/querying-large-dataset-on-join-15-million-rows