순서 - mysql 페이징 쿼리 최적화




수백만 행의 쿼리로 개수 및 순서를 최적화하는 방법 (3)

쿼리 순서를 최적화하고 쿼리를 계산할 때 도움이 필요하므로 수백만 (약 3 백만) 행의 테이블이 있습니다.

나는 4 개의 테이블을 조인하고 레코드를 가져와야 만한다. 간단한 쿼리를 실행하면 완료하는데 수 밀리 세컨드 밖에 걸리지 않는다.하지만 테이블을 조인 할 때 시간을 무제한으로 유지하려고한다.

아래 사례를 참조하십시오.

DB 서버 설정 :

CPU Number of virtual cores: 4
Memory(RAM): 16 GiB
Network Performance: High

각 표의 행 :

tbl_customers -  #Rows: 20 million.
tbl_customers_address -  #Row 25 million.
tbl_shop_setting - #Rows 50k
aio_customer_tracking - #Rows 5k

테이블 스키마 :

CREATE TABLE `tbl_customers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `shopify_customer_id` BIGINT(20) UNSIGNED NOT NULL,
    `shop_id` BIGINT(20) UNSIGNED NOT NULL,
    `email` VARCHAR(225) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `accepts_marketing` TINYINT(1) NULL DEFAULT NULL,
    `first_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `last_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `last_order_id` BIGINT(20) NULL DEFAULT NULL,
    `total_spent` DECIMAL(12,2) NULL DEFAULT NULL,
    `phone` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `verified_email` TINYINT(4) NULL DEFAULT NULL,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `created_at` DATETIME NULL DEFAULT NULL,
    `date_updated` DATETIME NULL DEFAULT NULL,
    `date_created` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `shopify_customer_id_unique` (`shopify_customer_id`),
    INDEX `email` (`email`),
    INDEX `shopify_customer_id` (`shopify_customer_id`),
    INDEX `shop_id` (`shop_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;


CREATE TABLE `tbl_customers_address` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT(20) NULL DEFAULT NULL,
    `shopify_address_id` BIGINT(20) NULL DEFAULT NULL,
    `shopify_customer_id` BIGINT(20) NULL DEFAULT NULL,
    `first_name` VARCHAR(50) NULL DEFAULT NULL,
    `last_name` VARCHAR(50) NULL DEFAULT NULL,
    `company` VARCHAR(50) NULL DEFAULT NULL,
    `address1` VARCHAR(250) NULL DEFAULT NULL,
    `address2` VARCHAR(250) NULL DEFAULT NULL,
    `city` VARCHAR(50) NULL DEFAULT NULL,
    `province` VARCHAR(50) NULL DEFAULT NULL,
    `country` VARCHAR(50) NULL DEFAULT NULL,
    `zip` VARCHAR(15) NULL DEFAULT NULL,
    `phone` VARCHAR(20) NULL DEFAULT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `province_code` VARCHAR(5) NULL DEFAULT NULL,
    `country_code` VARCHAR(5) NULL DEFAULT NULL,
    `country_name` VARCHAR(50) NULL DEFAULT NULL,
    `longitude` VARCHAR(250) NULL DEFAULT NULL,
    `latitude` VARCHAR(250) NULL DEFAULT NULL,
    `default` TINYINT(1) NULL DEFAULT NULL,
    `is_geo_fetched` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `customer_id` (`customer_id`),
    INDEX `shopify_address_id` (`shopify_address_id`),
    INDEX `shopify_customer_id` (`shopify_customer_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE `tbl_shop_setting` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,   
    `shop_name` VARCHAR(300) NOT NULL COLLATE 'latin1_swedish_ci',
     PRIMARY KEY (`id`),
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;


CREATE TABLE `aio_customer_tracking` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `shopify_customer_id` BIGINT(20) UNSIGNED NOT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `shop_id` BIGINT(20) UNSIGNED NOT NULL,
    `domain` VARCHAR(255) NULL DEFAULT NULL,
    `web_session_count` INT(11) NOT NULL,
    `last_seen_date` DATETIME NULL DEFAULT NULL,
    `last_contact_date` DATETIME NULL DEFAULT NULL,
    `last_email_open` DATETIME NULL DEFAULT NULL,
    `created_date` DATETIME NOT NULL,
    `is_geo_fetched` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `shopify_customer_id` (`shopify_customer_id`),
    INDEX `email` (`email`),
    INDEX `shopify_customer_id_shop_id` (`shopify_customer_id`, `shop_id`),
    INDEX `last_seen_date` (`last_seen_date`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

쿼리 케이스 실행 및 실행 안 함 :

1. Running:  Below query fetch the records by joining all the 4 tables, It takes only 0.300 ms.

SELECT `c`.first_name,`c`.last_name,`c`.email, `t`.`last_seen_date`, `t`.`last_contact_date`, `ssh`.`shop_name`, ca.`company`, ca.`address1`, ca.`address2`, ca.`city`, ca.`province`, ca.`country`, ca.`zip`, ca.`province_code`, ca.`country_code`
FROM `tbl_customers` AS `c`
JOIN `tbl_shop_setting` AS `ssh` ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN `tbl_customers_address` as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
LIMIT 20

2. Not running: Simply when try to get the count of these row stuk the query, I waited 10 min but still running.

SELECT 
     COUNT(DISTINCT c.shopify_customer_id)   -- what makes #2 different
FROM `tbl_customers` AS `c`
JOIN `tbl_shop_setting` AS `ssh` ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN `tbl_customers_address` as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
LIMIT 20


3. Not running: In the #1 query we simply put the 1 Order by clause and it get stuck, I waited 10 min but still running. I study query optimization some article and tried by indexing, Right Join etc.. but still not working.

SELECT `c`.first_name,`c`.last_name,`c`.email, `t`.`last_seen_date`, `t`.`last_contact_date`, `ssh`.`shop_name`, ca.`company`, ca.`address1`, ca.`address2`, ca.`city`, ca.`province`, ca.`country`, ca.`zip`, ca.`province_code`, ca.`country_code`
FROM `tbl_customers` AS `c`
JOIN `tbl_shop_setting` AS `ssh` ON c.shop_id = ssh.id 
LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id
LEFT JOIN `tbl_customers_address` as ca ON (c.shopify_customer_id = ca.shopify_customer_id AND ca.default = 1)
GROUP BY c.shopify_customer_id
  ORDER BY `t`.`last_seen_date`    -- what makes #3 different
LIMIT 20

QUERY # 1 설명 :

QUERY # 2 설명 :

QUERY # 3 설명 :

쿼리, 테이블 구조를 최적화하기위한 모든 제안을 환영합니다.

내가 뭘하고 싶은지 :

tbl_customers 테이블에는 고객 정보가 들어 있고, tbl_customer_address 테이블에는 고객의 주소가 들어 있으며 (한 고객은 여러 주소를 가질 수 있습니다), aio_customer_tracking 테이블에는 고객의 방문 기록이 들어 있습니다. last_seen_date 는 방문 날짜입니다.

이제 고객의 주소와 방문 정보를 가져와 계산합니다. 또한, 나는이 세 테이블의 열 중 하나를 사용하여 주문할 수 있습니다. 예를 들어 last_seen_date (기본 주문)로 주문하고 있습니다. 희망이 설명은 내가 뭘하려고하는지 이해하는 데 도움이됩니다.


쿼리 # 1에서는 다른 두 개는 아니지만 옵티마이 저는

UNIQUE INDEX `shopify_customer_id_unique` (`shopify_customer_id`)

검색어를 줄이기 위해

GROUP BY c.shopify_customer_id
LIMIT 20

이것은 색인의 20 개 항목 후에 중지 할 수 있기 때문입니다. 약 51K 행에 도달하는 파생 테이블 (하위 쿼리 t ) 때문에 쿼리가 초고속으로 수행되지 않습니다.

Optimizer가 중복 DISTINCT 를 감지하고 제거하지 못했기 때문에 쿼리 # 2가 느려질 수 있습니다. 대신 20 세 이후에 멈출 수 없다고 생각할 수도 있습니다.

쿼리 # 3 모든 shopify_customer_id 그룹을 얻기 위해 테이블 c 를 완전히 통과 해야합니다 . 이는 ORDER BY 가 짧은 커큐어가 LIMIT 20 에 도달하지 못하게하기 때문입니다.

GROUP BY 의 열은 그룹별로 열별로 정의 된 것을 제외하고 SELECT 모든 비 집계 열을 포함해야합니다. 하나의 shopify_customer_id 대해 여러 주소가있을 수 있다고 shopify_customer_id 을 가져 오는 ca.address1 GROUP BY shopify_customer_id 와 관련하여 적절하지 않습니다. 마찬가지로, 하위 쿼리는 last_seen_date, last_contact_date 와 관련하여 부적절한 것 같습니다.

aio_customer_tracking 에서이 변경 ( "덮음"색인에 대한)이 조금 도움이 될 수 있습니다.

INDEX (`shopify_customer_id`)

INDEX (`shopify_customer_id`, `last_seen_date`, `last_contact_date`)

목표 해부

자, 간단히 말해서 ... 고객을 셀 수 있습니다.

고객 수를 계산하려면이 작업을 수행하고 "가져 오기"와 결합하지 마십시오.

SELECT COUNT(*) FROM tbl_customers;

이제 고객을 가져오고 싶습니다.

tbl_customers - #Rows : 2,000 만

확실히 2 천만 행을 가져오고 싶지는 않습니다! 나는 그것을 어떻게하려고하는지 생각하고 싶지 않다. 명확히하십시오. 그리고 나는 그 많은 행들을 페이지 매김 (paginating)하는 것을 허용하지 않을 것입니다. 아마도 WHERE 절이있을 것입니다. ?? WHERE 절은 (일반적으로) 최적화의 가장 중요한 부분입니다!

이제 고객의 주소와 방문 정보를 가져 오기만하면됩니다.

WHERE 필터가 "소수"의 고객에게 필터를 JOINing "임의의"주소 및 "모든"방문 정보를 얻기 위해 다른 테이블에 JOINing 한다고 가정하면 문제가 있거나 비효율적 일 수 있습니다. "any"대신에 "first"또는 "last"를 요구하는 것이 더 쉽지는 않지만 더 의미있을 수 있습니다.

귀하의 UI가 처음 몇 고객을 찾았 다면 사용자가 원할 경우 모든 주소와 모든 방문으로 다른 페이지로 이동할 것을 제안 할 수 있습니다. 아니면 수백 또는 그 이상의 방문이있을 수 있습니까?

또한, 나는이 세 테이블의 열 중 하나를 사용하여 주문할 수 있습니다. 예를 들어 last_seen_date (기본 주문)로 주문하고 있습니다.

WHERE 를 최적화 한 다음 모든 인덱스 끝에서 last_seen_datelast_seen_date .


쿼리 2에는 다른 사람들이 지적한 논리적 실수가 있습니다. count(distinct(c.shopify_customer_id)) 는 단일 값을 반환 할 것이므로 그룹은 쿼리를 복잡하게 만듭니다 (이것은 MySQL을 shopify_customer_id로 먼저 그룹화 한 다음 실행 어떻게 든 긴 실행 시간의 이유가 될 수있는 count(distinct(shopify_customer_id ))

조회 할 수없는 부속 선택에 조인 할 때 조회 3의 주.을 최적화 할 수 없습니다. 소요 시간은 단순히 시스템이 결과 세트를 정렬해야하는 시간입니다.

문제의 해결책은 다음과 같습니다.

  1. 테이블 tbl_customers_address의 shopify_customer_id ( shopify_customer_id )를 shopify_customer_id ( shopify_customer_id , default )로 변경하여 다음 u 리를 최적화하십시오

  2. 쿼리 1 (결과)의 결과를 가진 테이블을 만들지 만

    LEFT JOIN (SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id) as t ON t.shopify_customer_id = c.shopify_customer_id .

  3. 결과 테이블을 변경하고 last_seen_date 및 last_seen_date 및 shopify_customer_id에 대한 색인 열을 추가하십시오

  4. 이 쿼리 결과에 대한 테이블을 생성하십시오 (last_Date) :

SELECT shopify_customer_id, last_seen_date, last_contact_date FROM aio_customer_tracking GROUP BY shopify_customer_id

  1. 결과 테이블을 last_Date 테이블의 값으로 업데이트하십시오.

이제 작성한 색인을 사용하여 last_Date에 의해 정렬 된 결과 테이블에 대해 조회를 실행할 수 있습니다.

전체 프로세스는 쿼리 2 또는 쿼리 3을 실행하는 것보다 시간이 적게 걸립니다.


인덱스너무 많아서 삽입, 업데이트 및 삭제와 같은 최적화 된 설정에 따라 때로는 선택 항목에 대한 실제 성능 저하 요인이 될 수 있습니다.

또한 GROUP BY 문을 제거하십시오 .

쿼리 최적화를 위해 클러스터형 인덱스와 비 클러스터형 인덱스, GROUP BY , ORDER BY , WHERE 및 뷰를 올바르게 사용하는 것에 대해 더 많이 말할 수 있습니다. 그러나 일부 인덱스를 제거하면 쿼리 속도가 빨라질 것이라고 생각합니다. (더 엄격한 SQL 표준을 따르고 조금 더 논리적이되도록 쿼리를 다시 작성하는 것도 가능하지만이 문제의 범위를 벗어납니다.)

한 가지 더 - 쿼리 결과로 무엇을하고 있습니까? 이것은 어딘가에 저장되어 있으며 조회를 위해 액세스되고, 계산에 사용되며, 자동화 된 보고서에 사용되며, 웹 데이터베이스 연결을 통해 표시됩니까? 플랫 파일에 대한 보고서 / 백업 또는 내보내기가 필요한 경우이 데이터를 가져 오는보다 효율적인 방법이 있기 때문에 차이가 있습니다. 당신이하는 일에 따라 다양한 옵션이 있습니다.