인덱스를 지정하지 않으면 MySQL 속도가 느려지는 이유
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=开
