Pertanyaan MySQL query lambat dengan bergabung meskipun MENJELASKAN menunjukkan rencana yang baik


Saya memiliki skenario berikut: Dalam database MySQL, saya memiliki 2 tabel MyISAM, satu dengan 4,2 juta baris, dan lainnya dengan 320 juta baris. Berikut ini adalah skema untuk tabel:

Table1 (4.2M baris)

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 varchar(40)
f3 varchar(40)
f4 varchar(40)
f5 varchar(40)
f6 smallint(6)
f7 smallint(6)
f8 varchar(40)
f9 varchar(40)
f10 smallint(6)
f11 varchar(10)
f12 tinyint(4)
f13 smallint(6)
f14 text

Table2 (320M baris)

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 INTEGER UNSIGNED NOT NULL

Table2 berada dalam database yang berbeda tetapi saya menggunakan prosedur tersimpan yang mempertanyakan dua tabel. Hubungan antara dua tabel adalah bahwa untuk Table1.F1 mungkin ada hingga kira-kira. 100 baris di Table2.F1 (foreign key) yang cocok, dan nilai untuk Table2.f2 akan dikembalikan untuk kunci yang cocok ini. Saya memiliki indeks IX1 (f2 (15), f3 (10)) pada Table1 dan indeks IX2 (F1, f2) dan IX3 (f2) pada Tabel 2

Kueri yang saya jalankan adalah sebagai berikut:

SELECT g.F1
FROM DB1.Table1 g 
INNER JOIN DB2.Table2 gp ON g.F1 = gp.F1 
WHERE (gp.f2 = 452677825) AND
(g.f2 = 'A string value') LIMIT 0,56

Permintaan ini kadang-kadang sangat cepat (<1s) tetapi mengubah nilai string yang g.F2 dibandingkan untuk menghasilkan pertanyaan yang mengambil lebih dari 11 dan kadang-kadang bahkan 30 detik. Saya tidak mengerti mengapa demikian. Berikut ini adalah output dari MENJELASKAN pada SELECT yang dieksekusi.

1, 'SIMPLE', 'g', 'ref', 'PRIMARY,IX1', 'IX1', '17', 'const', 901, 'Using where'
1, 'SIMPLE', 'gp', 'ref', 'IX3,IX2', 'IX2', '8', 'DB1.g.F1,const', 1, 'Using index'

yang tampaknya rencana eksekusi yang cukup baik. Jumlah baris di baris paling atas menjelaskan paling banyak 2000, tetapi saya tidak melihat mengapa ini harus memakan waktu lebih lama daripada sepersekian detik untuk mengembalikan hasil. Saya juga menjalankan profiler pada kueri dan mengetahui bahwa kueri menghabiskan 99,9% waktu pada tahap "Mengirim data". Adakah yang bisa menjelaskan mengapa ini begitu, dan apa yang dapat dilakukan untuk mengoptimalkan kueri?

Terima kasih sebelumnya, Tim


5
2017-08-16 22:15


asal


Jawaban:


Saya bukan ahli dalam bidang ini, tetapi di sini ada beberapa pemikiran:

Kecepatan kueri lebih lama saat g.F2 perubahan adalah karena caching. MySQL akan menyimpan hasil untuk setiap kueri (hingga cache penuh), tetapi kueri baru dijalankan pada cache kosong, sehingga mereka memakan waktu lebih lama. Anda seharusnya tidak mengoptimalkan berdasarkan ini. (Lihat Bagaimana mengukur secara akurat)

Saya tidak bisa mengatakan dari informasi Anda apakah itu g atau gp tabel memiliki kekhususan yang lebih besar (sepertinya gp?) dalam where klausa, tetapi Anda mungkin ingin mencoba subkueri. (Lihat Cara memaksa kueri internal untuk melakukan eksekusi terlebih dahulu)

Mengenai profiling, mungkin Anda mencapai ambang fisik seperti melebihi alokasi ram (menggunakan swap adalah bencana untuk kinerja) yang tidak akan jelas dari explain, atau apakah explain hanya salah dalam hal ini.


1
2017-08-16 23:00



Jika Anda mampu, Anda mungkin ingin mencoba mengutak-atik my.cnf Anda, properti yang ingin Anda mainkan key_buffer_size. Indeks MyISAM disimpan dalam file .MYI jika Anda menemukan ini dan total naik ukuran file (misalnya ls-lh /var/lib/mysql/dbname/*.MYI) Anda bisa memperkirakan kira-kira seberapa besar kunci buffer harus sesuai semua indeks Anda masuk. Dokumen MySQL tidak merekomendasikan untuk melebihi 25% dari memori sistem.


0
2017-08-17 22:35



Hubungan antara dua tabel adalah bahwa untuk Table1.F1 mungkin ada hingga kira-kira. 100 baris di Table2.F1

Untuk memperjelas, adalah hubungan antara Table1.F1 dan Table2.F1satu-ke-satu, atau satu ke banyak? Bagi saya, pernyataan ini menyiratkan satu-ke-banyak, tetapi dari skema, masing-masing bidang adalah kunci utama (yaitu unik).

Bagaimanapun, saya menduga bahwa seragam g.f2(15) tidak seragam, dan bahwa ketika outlier statistik dipukul, kinerja akan menurun dengan sendirinya.

Lakukan hasil

SELECT f2(15) AS f2_15, COUNT(*) AS cnt
FROM Table1
GROUP BY f2(15) 
ORDER BY cnt DESC

menunjukkan beberapa pencilan yang signifikan?


0
2017-12-08 22:33