인덱스를 지정하지 않으면 MySQL 속도가 느려지는 이유

Question

SQL 쿼리를 최적화하려고 하는데 올바르게 수행하는 방법을 알고 싶습니다.

으아악

여기에서는 가장 가까운 날짜로부터 25초 이내에 쿼리를 실행하는 데 약 100ms가 걸리는 인덱스 (booking_id, from_spot_id, to_spot_id)를 강제로 사용합니다!

booking 表大约有 200 万行，而 ride 테이블에는 약 500만 행이 있습니다.

그러나 강제 인덱싱을 사용하면 더 많은 행을 스캔하는 것을 볼 수 있습니다.

id	유형 선택	테이블	파티션	유형	가능한 키	키	key_len	참고	알았어	필터링됨	추가
1	간단	b1_		범위	메인, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX, booking_cancelled_at_IDX	IDX_E00CEDDE37D3107C	6		111456	6.6	색인 조건 사용, 위치 사용
1	간단	r0_		참고	ride_booking_id_IDX	ride_booking_id_IDX	109	ector.b1_.id	1	100.0
1	간단	s2_		eq_ref	메인, IDX_B9327A739F2C3FAB, spot_type_IDX	메인	4	ector.r0_.from_spot_id	1	72.52	사용장소
1	간단	s3_		eq_ref	메인	메인	4	ector.r0_.to_spot_id	1	100.0	사용장소

색인이 없는 동일한 쿼리와 비교:

id	유형 선택	테이블	파티션	유형	가능한 키	키	key_len	참고	알았어	필터링됨	추가
1	간단	s2_		참고	메인, IDX_B9327A739F2C3FAB, spot_type_IDX	spot_type_IDX	767	상시	161	100.0	색인 조건 사용
1	간단	r0_		참고	IDX_9B3D7CD0ABAF30D3, IDX_9B3D7CD03301C60, ride_booking_id_IDX, booking_from_spot_to_spot_IDX	IDX_9B3D7CD0ABAF30D3	5	ector.s2_.id	392	100.0
1	간단	b1_		eq_ref	메인, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX, booking_cancelled_at_IDX	메인	108	ector.r0_.booking_id	1	5.0	사용장소
1	간단	s3_		eq_ref	메인	메인	4	ector.r0_.to_spot_id	1	100.0	사용장소

내가 아는 한, start_at 和 end_at와 비교하는 데 사용하는 날짜 때문에 쿼리가 눈에 띄게 빨라졌습니다.

그래서 저는 가장 느린 부분을 더 작은 쿼리로 분리하려고 노력합니다.

从预订 b 中选择 *，其中 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01';

테이블 예약에는 두 개의 인덱스가 있습니다(start_at) 和 (end_at) 최대값과 최소값에 가까워질수록 이 쿼리가 더 빠르게 실행되는 데 도움이 됩니다(인덱스는 대부분의 행을 필터링하므로 남은 행은 거의 남지 않음).

그러나 과거에 충분히 먼 임의의 값을 취하면 속도가 훨씬 느려집니다. 위 쿼리는 예상대로 두 인덱스 중 하나만 사용하기 때문에 실행하는 데 10초가 걸립니다. 이렇게 간단한 쿼리에서 merge_index에 대한 설명이 표시되지 않는 이유는 무엇인지 모르겠습니다.

id	유형 선택	테이블	파티션	유형	가능한 키	키	key_len	참고	알았어	필터링됨	추가
1	간단	b		범위	IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C	IDX_E00CEDDEB75363F7	6		1147319	50	색인 조건 사용, 위치 사용

두 범위 조건을 모두 만족하는 인덱스가 없어서 쿼리를 반으로 나누어 보았습니다

으아아아

이 쿼리는 약 600밀리초가 소요되어 훨씬 빠르게 실행됩니다. 그러나 쿼리가 단순하고 약 7,000개의 행을 반환한다는 사실로 인해 기껏해야 두 자릿수가 될 것으로 예상됩니다.

쿼리가 내 인덱스를 자동으로 선택하지 않는 이유를 이해할 수 없나요 (id, start_at) 和 (id, end_at)? 내가 무엇을 놓치고 있나요?

더 나은 결과를 얻기 위해 테이블을 분할할 수 있다는 것을 알고 있지만 삭제할 수 없는 외래 키가 있으므로 이는 해결책이 아닙니다. 다른 스키마를 고려하고 외래 키 없이 예약 날짜를 별도로 보유하는 테이블이 있어야 하며 예약 테이블을 분할할 수 있도록 예약 테이블이 이를 참조하도록 해야 합니까? 구독 시 분할된 구독 테이블을 참조하기 위해 외래 키를 사용할 수 있습니까?

Mysql 엔진은 8.0.mysql_aurora.3.02.2

버전으로 AWS에서 실행되고 있습니다.

SELECT @@optimizer_switch의 출력은 다음과 같습니다.

index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、物化=on，半连接=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、provided_merge=on、use_invisible_indexes=off、skip_scan=on、hash_join=on、subquery_to_衍生=off、prefer_ordering_index =开，hypergraph_optimizer=关，衍生_条件_pushdown=开

Answer 1

색인 (id, start_at) 未被选取，因为没有固定的 id을 검색할 수 있습니다.

사용 사례에 따라 start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01';즉시 적용해야 할 수도 있습니다. 검색 기준에 따라 MySQL은 MERGE INDEX 최적화 작업을 통해 하나의 인덱스 또는 둘 다를 사용할 수 있습니다.

단일 인덱스를 사용하려면 인덱스가 정의된 순서와 동일한 순서로 사용되므로 필드 순서를 신중하게 선택해야 합니다.

EDIT: OP 편집 후 내 생각은 다음과 같습니다.

이 내용은 매우 잘 설명되어 있습니다. SELECT *，MySQL将被迫读取整个表。尝试仅选择id라고 가정하면 인덱스 포함 쿼리가 되기 때문에 인덱스를 사용할 가능성이 높습니다.