Mengapa MySQL perlahan tanpa menentukan indeks

Saya cuba mengoptimumkan pertanyaan SQL, tetapi saya ingin tahu cara melakukannya dengan betul.

SELECT 
    r0_.*
FROM ride r0_ use index (ride_booking_id_IDX)
LEFT JOIN booking b1_ ON r0_.booking_id = b1_.id 
LEFT JOIN spot s2_ ON r0_.from_spot_id = s2_.id 
LEFT JOIN spot s3_ ON r0_.to_spot_id = s3_.id 
WHERE b1_.start_at <= '2023-04-21' 
    AND b1_.end_at >= '2023-04-20' 
    AND b1_.paid_at IS NOT NULL 
    AND b1_.cancelled_at IS NULL 
    AND ((s2_.zone_id = 1 OR s3_.zone_id = 1)) 
    AND s2_.type = 'parking';

Di sini, saya memaksa penggunaan indeks (booking_id, from_spot_id, to_spot_id), yang menyebabkan pertanyaan dilaksanakan dalam masa 25 saat dari tarikh terdekat dalam kira-kira 100 milisaat!

booking 表大约有 200 万行，而 ride Meja mempunyai kira-kira 5 juta baris.

Walau bagaimanapun, saya dapat melihatnya mengimbas lebih banyak baris menggunakan pengindeksan paksa:

1Mudahr0_Rujukanride_booking_id_IDXride_booking_id_IDX109ector.b1_.id1100.01Mudahs2_eq_refUtama, IDX_B9327A739F2C3FAB, spot_type_IDXUtama4ector.r0_.from_spot_id172.52Tempat penggunaan1Mudahs3_eq_refUtamaUtama4ector.r0_.to_spot_id1100.0Tempat penggunaan

Berbanding dengan pertanyaan yang sama tanpa indeks:

id	Pilih jenis	Meja	Taip	Kunci yang mungkin	Kunci	key_len	OK	Ditapis	Tambahan
1	Mudah	b1_	Skop	Utama, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX	IDX_E00CEDDE37D3107C	6	111456	6.6	Gunakan syarat indeks;

id	Pilih jenis	Meja	Taip	Kunci yang mungkin	Kunci	key_len	Rujukan	OK	Ditapis	Tambahan
1	Mudah	s2_	Rujukan	Utama, IDX_B9327A739F2C3FAB, spot_type_IDX	spot_type_IDX	767	Malar	161	100.0	Gunakan syarat indeks
1	Mudah	r0_	Rujukan	IDX_9B3D7CD0ABAF30D3, IDX_9B3D7CD03301C60, ride_booking_id_IDX, booking_from_spot_to_spot_IDX	IDX_9B3D7CD0ABAF30D3	5	ector.s2_.id	392	100.0
1	Mudah	b1_	eq_ref	Utama, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX	Utama	108	ector.r0_.booking_id	1	5.0	Tempat penggunaan
1	Mudah	s3_	eq_ref	Utama	Utama	4	ector.r0_.to_spot_id	1	100.0	Tempat penggunaan

Setakat yang saya tahu, tarikh yang saya gunakan untuk dibandingkan dengan start_at 和 end_at adalah sebab mengapa pertanyaan itu nyata lebih pantas.

Jadi saya cuba mengasingkan bahagian paling perlahan kepada pertanyaan yang lebih kecil:

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

Pada tempahan meja, saya mempunyai dua indeks(start_at) 和 (end_at) ia membantu pertanyaan ini berjalan dengan lebih pantas apabila anda semakin hampir kepada maksimum dan min (memandangkan indeks akan menapis kebanyakan baris, terdapat sedikit baris yang tinggal) mengimbas).

Namun, apabila saya mengambil nilai rawak cukup jauh pada masa lalu, ia menjadi lebih perlahan. Pertanyaan di atas mengambil masa 10 saat untuk dijalankan kerana ia hanya menggunakan satu daripada dua indeks seperti yang dijangkakan, saya tidak tahu mengapa penjelasan itu tidak muncul untuk merge_index pada pertanyaan yang begitu mudah:

Memandangkan saya tidak boleh mempunyai indeks yang memenuhi kedua-dua syarat julat, saya cuba membahagikan pertanyaan kepada separuh

SELECT * from booking b
INNER JOIN booking b2 use index(booking_id_start_IDX)  ON b.id = b2.id and b2.start_at < '2021-01-01'
INNER JOIN booking b3 use index(booking_id_end_IDX) ON b.id = b3.id and b3.end_at > '2021-01-01';

Pertanyaan ini berjalan dengan lebih pantas, mengambil masa kira-kira 600 milisaat. Walau bagaimanapun, disebabkan oleh kesederhanaan pertanyaan dan fakta bahawa ia mengembalikan kira-kira 7k baris, saya menjangkakan ia berada dalam dua digit paling banyak.

Saya tidak faham mengapa pertanyaan tidak memilih indeks saya secara automatik (id, start_at) 和 (id, end_at)? Apa yang saya hilang?

Saya tahu saya boleh membahagikan jadual untuk mendapatkan hasil yang lebih baik, tetapi saya mempunyai kunci asing yang tidak boleh dipadamkan, jadi itu bukan penyelesaian. Perlukah saya mempertimbangkan skema lain dan mempunyai jadual yang menyimpan tarikh tempahan secara berasingan tanpa sebarang kunci asing dan meminta jadual tempahan merujuknya supaya saya boleh membahagikan jadual tempahan? Adakah mungkin menggunakan kunci asing untuk merujuk jadual langganan yang dipisahkan pada masa langganan?

Enjin MySQL sedang berjalan dalam AWS dengan versi berikut: 8.0.mysql_aurora.3.02.2

Keluaran

SELECT @@optimizer_switch ialah:

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=开

P粉787806024463 hari yang lalu594

membalas semua(1)saya akan balas

P粉0186537512023-09-08 23:04:14

Indeks anda (id, start_at) 未被选取，因为没有固定的 id boleh dicari.

Bergantung pada kes penggunaan anda, anda mungkin perlu start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; berkuat kuasa serta-merta; bergantung pada kriteria carian, MySQL mungkin menggunakan satu indeks atau kedua-duanya melalui operasi pengoptimuman MERGE INDEX.

Jika anda ingin menggunakan satu indeks, anda perlu memilih susunan medan dengan berhati-hati, kerana indeks digunakan dalam susunan yang sama di mana ia ditakrifkan.

EDIT: Selepas pengeditan OP, inilah pendapat saya.

Ini menerangkan perkara dengan baik. Dengan mengandaikan anda SELECT *，MySQL将被迫读取整个表。尝试仅选择id, dalam hal ini kemungkinan besar ia akan menggunakan indeks kerana ia akan menjadi pertanyaan yang diliputi indeks.

balas

Batalbalas

id	Pilih jenis	Meja	Partition	Taip	Kunci yang mungkin	Kunci	key_len	Rujukan	OK	Ditapis	Tambahan
1	Mudah	b		Skop	IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C	IDX_E00CEDDEB75363F7	6		1147319	50	Gunakan syarat indeks;