Mengapakah pertanyaan mengambil masa yang sama untuk mendapatkan data walaupun bilangan baris sangat berbeza?

Question

Saya mempunyai 29,938,766 baris dalam jadual LAWATAN saya yang kelihatan seperti ini

ID_Pengguna (INT)	DISI_MASUK(DATETIME)
65	2020-08-26 07:57:43
1182	2019-03-15 02:46:48
1564	2015-07-04 10:59:44
73	2021-03-18 00:25:08
3791	2017-10-17 12:22:45
51	2022-05-02 19:11:09
917	2017-11-20 15:32:06
3	29-12-2019 15:15:51
51	2015-02-08 17:48:30
1531	2020-08-05 08:44:55
Tunggu...	Tunggu...

Apabila menjalankan pertanyaan ini, ia mengambil masa 17-20 saat dan mengembalikan 63,514 (pengguna mempunyai 63,514 lawatan)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 917

Apabila menjalankan pertanyaan ini, ia mengambil masa 17-20 saat dan mengembalikan 193 (pengguna mempunyai 193 lawatan)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 716

Masalahnya ialah menanya 29,938,766 baris sentiasa mengambil masa 17-20 saat, walaupun pengguna hanya mempunyai 3, 50, 70 atau 1,000,000 lawatan.

Saya rasa masalahnya ialah kerana ia bergelung melalui semua baris?

Pertanyaan kedua mestilah lebih pantas daripada pertanyaan pertama. Ia bergantung kepada bilangan baris. Tetapi kedua-dua pertanyaan mengambil masa yang sama!

Apakah cadangan anda untuk saya mengelakkan masalah ini?

---

Struktur jadual

---

Kemas kini: Berikut ialah senario baharu yang dicadangkan:

Apabila pengguna pergi ke profilnya atau orang lain, dia boleh melihat bilangan lawatan profil dan boleh menapis lawatan dengan cara ini

Last 24 hours
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Last 7 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Last 30 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY);

All time
|
---> SELECT VISITS FROM USERS WHERE USER_ID = 5;

Selain itu, saya akan mencipta acara berulang yang akan melaksanakan arahan ini setiap hari.

DELETE FROM VISITS WHERE VISITED_IN <= DATE_SUB(NOW(), INTERVAL 30 DAY);

Selain itu, apabila menambah baris baharu dalam jadual LAWATAN, saya akan pastikan untuk menambah lajur LAWATAN.

UPDATE USERS SET VISITS = VISITS + 1 WHERE ID = 5

Answer 1

INDEX(user_id, visited_in)

akan mempercepatkan segalanya SELECTs yang anda nyatakan. Mereka perlu mengimbas sebahagian besar indeks; mereka tidak perlu "mengimbas keseluruhan jadual".

DELETE Memerlukan `INDEX(dilawati_dalam). Tetapi jika anda tidak menjalankannya dengan cukup kerap, masalah boleh timbul. Ini kerana pemadaman beribu-ribu baris sekaligus boleh menjadi masalah. Pertimbangkan untuk menjalankan operasi pemadaman sekurang-kurangnya sekali setiap jam.

Jika jadual sangat besar, dsb., pertimbangkan untuk menggunakan pembahagian "siri masa". Dengan DROP PARTITION, kelajuannya jauh lebih pantas. Partition

Sebarang perkhidmatan caching akan memberikan kiraan basi, tetapi kadangkala ia lebih pantas.

"Pangkalan data boleh diakses setiap kali seseorang membuka halaman", tetapi hanya jika pertanyaan itu cukup cekap. Lakukan pengindeksan.

Dalam jawapan saya kepada soalan anda yang lain, saya menerangkan cara jadual ringkasan boleh mempercepatkan lagi perkara. Walau bagaimanapun, ia menganggap bahawa "N hari terakhir" diukur dari tengah malam hingga tengah malam. Pertanyaan semasa anda ialah NOW() - INTERVAL N DAY. Ini lebih mengelirukan untuk dilaksanakan daripada tengah malam. Adakah anda ingin menukar maksud "N hari terakhir"?

(Beberapa asas INDEX...)

Sebab penting bagi mana-mana indeks ialah keupayaannya untuk mencari baris dengan cepat berdasarkan lajur tertentu.

• INDEX ialah senarai kunci yang dipetakan ke baris.
• INDEKS UNIK ialah UNIQUE INDEX 是 INDEX ditambah dengan kekangan keunikan - bermakna tiada dua baris dalam indeks mempunyai nilai yang sama.
• Unik PRIMARY KEY ialah indeks unik yang ditentukan untuk mengenal pasti secara unik setiap baris dalam jadual.

"kunci" dan "indeks" adalah sinonim.

Indeks (dalam enjin InnoDB MySQL) dilaksanakan sebagai BTree (sebenarnya B+Tree; lihat Wikipedia). Dalam kes PK, lajur yang tinggal berada di sana dengan nilai PK. Untuk kunci "sekunder", bahagian "nilai" BTree ialah lajur PK.

Sebarang indeks boleh mengandungi 1 atau lebih lajur (dipanggil "komposit")

INDEX(lastname) Tidak mungkin menjadi satu-satunya INDEX(lastname,firstname) Masih tidak mungkin unik, tetapi ia adalah "komposit".