반응형
MySQL 쿼리 런타임 감소
다음과 같은 질문이 있습니다.
SELECT DISTINCT `cr`.`idCustomer`, `rbase`.`id`
FROM `customers` `t`
JOIN `customersregion` `cr` ON t.idCustomer = cr.idCustomer
and cr.isDeleted = 0
JOIN `calendaritems` `rbase` ON rbase.idAgentsRegion = cr.idRegion
and rbase.isDeleted = 0
where (
(rbase.startDate <= '2020-07-06 00:00:00' and rbase.endDate >= '2020-07-06 00:00:00') or
(rbase.startDate <= '2020-07-28 00:00:00' and rbase.endDate >= '2020-07-28 00:00:00') or
(rbase.startDate >= '2020-07-06 00:00:00' and rbase.startDate <= '2020-07-28 23:59:59') or
(rbase.endDate >= '2020-07-06 00:00:00' and rbase.endDate <= '2020-07-28 23:59:59')
)
데이터베이스:MySQL
고객: 132,000줄
Customers Region: 1,754,000 행
캘린더 항목: 3,838,000 행 (조건은 555,000 행으로 감소)
t.id Customer & cr.id Customer & cr.isDeleted & rbase.idAgentsRegion & cr.idRegion & rbase.isDeleted는 인덱스입니다.
이 쿼리 실행 시간은 약 100초이며 이 쿼리의 실행 시간을 줄이고 싶습니다.
행을 제한하거나 테이블에 다른 조건을 설정할 수 없습니다.
좀 도와 줄래요?
감사해요.
질의 설명:
고객 DDL:
create table customers
(
idCustomer int auto_increment
primary key,
CustomerName varchar(255) not null comment 'نام فروشگاه',
FirstName varchar(60) null comment 'نام رابط',
LastName varchar(60) null comment 'نام مشتري',
idUser int null comment '!#dont show',
idPayment int null,
idCompany int default 0 not null,
LatitudePoint decimal(18, 12) default 0.000000000000 null comment 'gpslat',
LongitudePoint decimal(18, 12) default 0.000000000000 null comment 'gpslongs',
LastOrderDate datetime default '0000-00-00 00:00:00' null comment 'lastorderdate',
VisitPeriod int default 0 null comment 'visitperiod',
LastVisit datetime default '0000-00-00 00:00:00' null comment 'LastVisitDate',
LastNoOrderDate datetime default '0000-00-00 00:00:00' null,
Credit decimal(20, 4) default 0.0000 null comment 'credit',
RemainCredit decimal(20, 4) default 0.0000 null comment 'remaincredit',
Balance decimal(20, 4) default 0.0000 null comment '!#dont show',
RFID varchar(60) null comment 'rfid',
ReturnCheck tinyint(1) default 0 null comment '!#dont show',
AccountStatus tinyint(1) default 0 null comment 'accountstatus',
FaxNumber varchar(20) null,
LiquidationDate date default '0000-00-00' null comment '!#dont show',
EldestDue date default '0000-00-00' null comment '!#dont show',
MaturityDate date default '0000-00-00' null comment '!#dont show',
PriceKind int null,
isDefault tinyint(1) default 0 not null comment '!#dont show',
TimeStamp timestamp default current_timestamp() not null on update current_timestamp(),
isDeleted tinyint(1) default 0 not null,
Address varchar(255) null,
PhoneNumber varchar(60) null,
MobileNumber varchar(60) null,
CustomerErpCode varchar(60) null comment '!#dont show',
StoreType int null,
country varchar(255) null,
state varchar(255) null,
City varchar(30) null,
Region varchar(30) null,
idUserCreator int null,
idBranche int null,
idTagsinfo int null,
shop_id int null,
shop_id_address int null,
lastActivityDate datetime null,
lastActivityType tinyint(1) null,
duplicateOf int null,
isConfirmed tinyint(1) default 2 not null comment '0:rejected - 1:confirmed - 2:notChecked',
Status tinyint(1) default 1 not null,
createDate datetime null,
idProcess int null comment 'نیازی نیست به اینکه حتما پروسه داشته باشد',
idUserConfirmer int null comment 'this is refered to agents table',
nextDate datetime null,
prevDate datetime null,
idImage int null,
idColor int null,
idRate int null,
LastImageDate datetime null,
LastOrderAgentName varchar(255) null,
LastVisitAgentName varchar(255) null,
LastNoOrderAgentName varchar(255) null,
LastImageAgentName varchar(255) null,
LastOrderIdAgent int null,
LastVisitIdAgent int null,
LastNoOrderIdAgent int null,
LastImageIdAgent int null,
isSaleActive tinyint(1) default 1 null,
isReturnActive tinyint(1) default 1 null,
alley varchar(256) null,
street varchar(256) null,
plaque varchar(256) null,
secondAddress varchar(255) null,
description varchar(255) null,
appType varchar(50) default 'iorder' not null,
idPipeline varchar(255) default '0' null,
constraint shop_id
unique (shop_id),
constraint shop_id_address
unique (shop_id_address),
constraint ux_customererp
unique (CustomerErpCode),
constraint customers_ibfk_1
foreign key (idBranche) references branches (idBranche)
on update set null on delete set null,
constraint customers_ibfk_2
foreign key (idTagsinfo) references tagsinfo (idTag)
on update set null on delete set null,
constraint customers_ibfk_3
foreign key (idRate) references rates (idRate)
on update set null on delete set null,
constraint customers_ibfk_4
foreign key (idColor) references colors (idColor)
on update set null on delete set null,
constraint customers_ibfk_5
foreign key (idRate) references rates (idRate)
on update set null on delete set null,
constraint customers_ibfk_6
foreign key (idColor) references colors (idColor)
on update set null on delete set null,
constraint fk_customer_agents
foreign key (idUser) references agents (idAgents)
on update set null on delete set null,
constraint fk_customer_paymant
foreign key (idPayment) references payment (idPayment),
constraint fk_customer_pricelist
foreign key (PriceKind) references pricelist (idPriceList),
constraint fk_customer_storeinfo
foreign key (StoreType) references storesinfo (idStore)
)
charset = utf8;
create index fk_customer_agents_idx
on customers (idUser);
create index fk_customer_paymant_idx
on customers (idPayment);
create index fk_customer_pricelist_idx
on customers (PriceKind);
create index fk_customer_storeinfo_idx
on customers (StoreType);
create index idBranche
on customers (idBranche);
create index idColor
on customers (idColor);
create index idProcess
on customers (idProcess);
create index idRate
on customers (idRate);
create index idTagsinfo
on customers (idTagsinfo);
create index idx_isdeleted_customername
on customers (isDeleted, CustomerName);
create index isdeleted_lat_lng
on customers (isDeleted, LatitudePoint, LongitudePoint);
create index isdeleted_status_isconfirmed
on customers (isDeleted, Status, isConfirmed);
create index lat_lng
on customers (LatitudePoint, LongitudePoint);
캘린더 아이템 DDL:
create table calendaritems
(
id int auto_increment
primary key,
TimeStamp timestamp default current_timestamp() not null on update current_timestamp(),
isDone tinyint(1) null,
isDeleted tinyint(1) default 0 not null,
subject varchar(255) null,
startDate datetime not null,
endDate datetime not null,
isAllDayEvent tinyint(1) default 1 null,
message varchar(255) null,
color varchar(200) null,
rMessage varchar(255) null,
rTime datetime null,
rLocationLat decimal(18, 12) null,
rLocationLong decimal(18, 12) null,
idAgent int not null,
idCustomer int null,
idVisitPath int null,
isFinal tinyint(1) null,
idUserCreator int not null,
idAgentsRegion int null,
type int(5) default 1 not null,
systemFill tinyint(1) default 0 not null,
createDate datetime null,
reqUp tinyint(1) default 0 not null,
dependOn int null,
idPlan int null comment 'to keep track of customer types of a region inside a plan',
idPlanTour int null,
startTime time null,
endTime time null,
constraint calendaritems_ibfk_agents
foreign key (idAgent) references agents (idAgents),
constraint calendaritems_ibfk_agents2
foreign key (idUserCreator) references agents (idAgents),
constraint calendaritems_ibfk_customers
foreign key (idCustomer) references customers (idCustomer)
on delete set null
)
charset = utf8;
create index `Index 10`
on calendaritems (isDeleted, idAgent, startDate, idCustomer);
create index `Index 14`
on calendaritems (isDeleted, idAgent, idAgentsRegion, idPlan, startDate, endDate);
create index `Index 7`
on calendaritems (startDate);
create index `Index 8`
on calendaritems (isDeleted, idAgent, startDate, idVisitPath);
create index `Index 9`
on calendaritems (isDeleted, idAgent, startDate, idAgentsRegion);
create index createDate
on calendaritems (createDate);
create index idAgent
on calendaritems (idAgent);
create index idAgentsRegion
on calendaritems (idAgentsRegion);
create index idCustomer
on calendaritems (idCustomer);
create index idUserCreator
on calendaritems (idUserCreator);
create index idVisitPath
on calendaritems (idVisitPath);
create index reqUp
on calendaritems (reqUp);
create index `systemFill-startDate-idAgent-idPlan`
on calendaritems (systemFill, startDate, idAgent, idPlan);
Customers Region DDL:
create table customersregion
(
idCustomer int not null,
idRegion int not null,
idCompany int default 0 null,
isDeleted tinyint(1) default 0 null,
TimeStamp timestamp default current_timestamp() null on update current_timestamp(),
ERPCode varchar(255) default '' null,
createDate datetime null,
primary key (idCustomer, idRegion),
constraint customersregion_ibfk_1
foreign key (idCustomer) references customers (idCustomer)
on update cascade on delete cascade,
constraint customersregion_ibfk_2
foreign key (idRegion) references region (idRegion)
on update cascade on delete cascade
)
charset = utf8;
create index idRegion
on customersregion (idRegion);
create index isdeleted_idregion_idcustomer
on customersregion (isDeleted, idRegion, idCustomer);
EXPLINE 계획에서는 첫 번째 단계가 캘린더 항목 테이블("rbase")을 스캔하여 총 160만 개의 행을 스캔하는 것임을 보여 줍니다.
인덱스를 사용하고 있습니다만, 실제로는 사용하지 않는 여분의 열이 너무 많아서 적합하지 않습니다.보다 좋은 인덱스는 (isDeleted, startDate, endDate, idAgentsRegion)으로 구성된 인덱스로, WHERE 조건의 처음 3개의 OR 부분에는 적합하지만 마지막 3개의 컬럼에는 적합하지 않습니다.
WHERE 또는 JOIN 조건에는 idAgentsRegion 열이 전혀 필요하지 않습니다. 단 인덱스를 "커버링" 상태로 만들면 실제 테이블 행에 필요한 추가 검색 절차 없이 인덱스에서만 필요한 데이터를 가져올 수 있습니다.
이 경우 2개의 인덱스(isDeleted, startDate, endDate, idAgentsRegion)와 1개의 인덱스(isDeleted, startDate, endDate, idAgentsRegion)를 가진 후 쿼리를 UNION에 의해 결합된2개의 독립된 인덱스로 분할합니다.
SELECT DISTINCT `cr`.`idCustomer`, `rbase`.`id`
FROM `customers` `t`
JOIN `customersregion` `cr` ON t.idCustomer = cr.idCustomer and cr.isDeleted = 0
JOIN `calendaritems` `rbase` ON rbase.idAgentsRegion = cr.idRegion and rbase.isDeleted = 0
where (
(rbase.startDate <= '2020-07-06 00:00:00' and rbase.endDate >= '2020-07-06 00:00:00') or
(rbase.startDate <= '2020-07-28 00:00:00' and rbase.endDate >= '2020-07-28 00:00:00') or
(rbase.startDate >= '2020-07-06 00:00:00' and rbase.startDate <= '2020-07-28 23:59:59')
)
UNION
SELECT DISTINCT `cr`.`idCustomer`, `rbase`.`id`
FROM `customers` `t`
JOIN `customersregion` `cr` ON t.idCustomer = cr.idCustomer and cr.isDeleted = 0
JOIN `calendaritems` `rbase` ON rbase.idAgentsRegion = cr.idRegion and rbase.isDeleted = 0
where (rbase.endDate >= '2020-07-06 00:00:00' and rbase.endDate <= '2020-07-28 23:59:59')
첫 번째 부분의 경우 첫 번째 지수가 완벽하고, 두 번째 부분의 경우 두 번째 지수가 완벽하여 훨씬 더 작은 지수 범위 스캔이 이루어지며, 최종적으로는 결과를 조합하고 중복만 제거하면 된다.
우선, 고객으로부터 고객까지의 외부 키 관계가 있기 때문에, 문의에 고객 테이블이 필요 없습니다.여기서 아무것도 선택하지 않습니다.또한 외부 키 관계에 따라 고객 테이블에 없는 고객 ID는 선택되지 않습니다.이것이 100초를 크게 단축하는 것은 아니지만, 모든 것이 도움이 됩니다.
인덱스를 최대한 활용하려면 두 개의 인덱스가 더 필요합니다.
CREATE INDEX firstindextoadd ON calendaritems(idAgentsRegion, isDeleted, startDate, endDate);
CREATE INDEX secondindextoadd ON calendaritems(idAgentsRegion, isDeleted, endDate);
첫 번째 인덱스는 처음 세 가지 조건에 사용됩니다.
(rbase.startDate <= '2020-07-06 00:00:00' and rbase.endDate >= '2020-07-06 00:00:00') or
(rbase.startDate <= '2020-07-28 00:00:00' and rbase.endDate >= '2020-07-28 00:00:00') or
(rbase.startDate >= '2020-07-06 00:00:00' and rbase.startDate <= '2020-07-28 23:59:59')
두 번째는 네 번째 조건입니다.
(rbase.endDate >= '2020-07-06 00:00:00' and rbase.endDate <= '2020-07-28 23:59:59')
삭제한 레코드의 수에 따라 isDeleted를 포함시킬지 여부에 따라 다르지만, 만약을 위해 추가했습니다.
대규모 데이터 집합에서 테스트하지 않았기 때문에 이 방법이 효과가 있었는지 여부를 알려 주십시오.
또한 다음과 같은 조건을 간소화할 수 있습니다.
SELECT DISTINCT `cr`.`idCustomer`, `rbase`.`id`
FROM `customersregion` `cr` ON t.idCustomer = cr.idCustomer and cr.isDeleted = 0
JOIN `calendaritems` `rbase` ON rbase.idAgentsRegion = cr.idRegion and rbase.isDeleted = 0
where
rbase.startDate <= '2020-07-06 00:00:00' and rbase.endDate >= '2020-07-28 00:00:00' OR
rbase.startDate BETWEEN '2020-07-06 00:00:00' and '2020-07-28 00:00:00' OR
rbase.endDate BETWEEN '2020-07-06 00:00:00' and '2020-07-28 00:00:00'
rbase: INDEX(isDeleted, startDate, endDate, idAgentsRegion, id)
rbase: INDEX(isDeleted, endDate, startDate, idAgentsRegion, id)
다음과 같은 특성이 있습니다.
- 처음 두 열은 ON 및 WHERE에서 유용합니다.
- 는 Optimizer가 Optimizer인지 아닌지에 둘 중 합니다.
startDate★★★★★★★★★★★★★★★★★」endDate더 까다롭습니다. - 커버
은 옵티마이저가 「Optimizer」로 하는 것을 전제로 하고 있습니다.rbase약, 신, 작으로 cr옵티마이저
rbase: INDEX(idAgentsRegion, isDeleted, startDate, endDate, id)
rbase: INDEX(idAgentsRegion, isDeleted, endDate, startDate, id)
cr는 Optimizer에서할 수 Optimizer에는 Optimizer가 .)WHERE★★★★★★★★★★★★★★★★★★★★★」
cr: INDEX(isDeleted, idRegion, -- first, (in either order)
idCustomer) -- last
시작 <= 끝>을 가정하면 범위 테스트는 다음과 같이 단순화할 수 있습니다.
WHERE rbase.startDate < '2020-07-28'
AND rbase.endDate >= '2020-07-06'
('2020-07-28'과 '2020-07-28 23:59:59'의 재미있는 사업은 잘 모르겠습니다.)
"< midnight " > = midnight" 라고 합니다.을 사용법 다른 으로는 '하다'를 지정합니다.'2020-07-28''2020-07-06' + INTERVAL 22 DAY·스판(22일)을 알고, 윤일 등으로 소란을 피우고 싶지 않을 때는 후자가 편리합니다.
', '적절하다', '적절하다ON'관련되어 있는지와 '관련되어 있는지'를 명시합니다.WHERE필터링에 사용됩니다. ,, ②,isDeleted은 스테 the the tests tests tests tests tests tests tests tests tests tests 。WHERE형벌에 이 없다JOIN단, 「」에는 합니다.LEFT JOIN
Last...Id... ★★★★★★★★★★★★★★★★★」Last...Name이이중 복복 ??? ??? 곳에 ' 좋다'라는 지도가 요.id로로 합니다.name
Rates ★★★★★★★★★★★★★★★★★」Colors정상화할 가치가 없는 것 같다.어느쪽인가를 검색할 필요가 있는 경우는, 이 정규화를 해제하는 것으로, 퍼포먼스에 큰 도움이 될 가능성이 있습니다.
이 조합은 '잘못된' 것 같습니다.이유는 다음과 같습니다.
startDate DATETIME
startTime TIME
이 두 가지를 다 가지면
INDEX(a) -- drop
INDEX(a,b) -- keep (it takes care of the other case)
LatitudePoint decimal(18, 12)9바이트가 걸리고, 엄청난 과잉 살상입니다.권장되는 대안: http://mysql.rjweb.org/doc.php/latlng#representation_choices
예를 들어 startDate가 endDate 30일 전이라고 가정할 수 없는 한 모든 행을 체크할 필요가 없도록 이 쿼리를 인덱싱할 수 있는 실질적인 방법은 없습니다.(startDate, endDate)에 복합 인덱스를 사용해 볼 수 있습니다.이것에 의해서, 도움이 되는 경우가 있습니다.
일부 조건에서는 시작 날짜 인덱스를 사용하고 일부는 종료 날짜 인덱스를 사용하여 결합을 시도할 수 있지만, 380만 행 중 50만 행이 선택될 것으로 예상되면 전혀 도움이 되지 않을 수 있습니다.
언급URL : https://stackoverflow.com/questions/63109800/reduce-mysql-query-runtime
반응형
'source' 카테고리의 다른 글
| 변경 시 입력 모델이 Integer에서 String으로 변경됨 (0) | 2023.02.14 |
|---|---|
| React + ES6 + 웹 팩을 사용하여 구성 요소를 가져오고 내보내는 방법은 무엇입니까? (0) | 2023.02.14 |
| MySql에서 기본값으로 함수를 사용할 수 있습니까? (0) | 2023.01.29 |
| 테이블스페이스 오류: Mariadb에서 테이블 복원 (0) | 2023.01.29 |
| HTML 속성과 속성의 차이점은 무엇입니까? (0) | 2023.01.29 |