Pertanyaan pangkalan data MySQL mengambil masa tertentu

Question

Saya mempunyai pangkalan data mesej yang besar dengan 24,000 baris:

Paparkan baris 0-24 (jumlah 2455455 baris, pertanyaan mengambil masa 0.0006 saat). </pra>
<p> mesej, jadi saya perlu memuatkan perbualan dengan lebih cepat, untuk pengguna yang kurang perbualan, memuatkan adalah seperti berikut (pengguna mempunyai 3.2k perbualan): </p>
<pre class="brush:php;toolbar:false;">Paparkan baris 0-24 (jumlah 3266 baris, pertanyaan mengambil masa 0.0345 saat) [id: 5009666... ​​​​- 4375619...]. </pra>
<p>Pemuatan yang lebih perlahan untuk pengguna yang mempunyai banyak perbualan (pengguna dengan 40k perbualan): </p>
<pre class="brush:php;toolbar:false;">Paparkan baris 0-24 (jumlah 40296 baris, pertanyaan mengambil masa 5.1763 saat) [id: 5021561... - 5015545...]. </pra>
<p>Saya menggunakan kekunci indeks untuk lajur ini: </p>
<pre class="brush:php;toolbar:false;">id, to_id, from_id, time, seen

Jadual pangkalan data:

BUAT JADUAL `mesej` (
  `id` int(255) BUKAN NULL,
  `ke_id` int(20) BUKAN NULL,
  `from_id` int(20) BUKAN NULL,
  Teks panjang `mesej` NOT NULL,
  `masa` ganda NOT NULL,
  `dilihat` int(2) BUKAN NULL,
) ENJIN=CHARSET LALAI InnoDB=latin1;



MASUKKAN KE DALAM `mesej` (`id`, `to_id`, `from_id`, `message`, `time`, `dilihat`) NILAI
(2, 6001, 2, 'Hai', 1587581995.5222, 1);


ALTER JADUAL `mesej`
  TAMBAHKAN KUNCI UTAMA (`id`),
  TAMBAHKAN KUNCI `time_idx` (`time`),
  TAMBAHKAN KUNCI `from_idx` (`from_id`),
  TAMBAHKAN KUNCI `ke_idx` (`ke_id`),
  TAMBAHKAN KUNCI `seenx` (`dilihat`),
  TAMBAH KEY `idx` (`id`);


ALTER JADUAL `mesej`
  UBAHSUAI `id` int(255) BUKAN NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
KOMIT;</pra>
<p>Saya menggunakan pertanyaan berikut: </p>
<pre class="brush:php;toolbar:false;">SELECT
  *
DARI
  mesej,
  (
    PILIH
      MAX(id) seperti yang terakhir
    DARI
      mesej
    DI MANA
      (
        messages.to_id = '1' -- ID untuk dibandingkan dengan (ID pengguna log masuk)
        ATAU messages.from_id = '1' -- ID untuk dibandingkan dengan (ID pengguna log masuk)
      )
    KUMPULAN OLEH
      CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        TERHEBAT(messages.to_id, messages.from_id)
      )
  ) sebagai perbualan
DI MANA
  id = perbualan.lastid
PERINTAH OLEH
  messages.id DESC

Saya tidak tahu bagaimana untuk menjadikannya lebih pantas untuk pengguna yang mempunyai banyak perbualan, jika saya perlu mencipta semula struktur pangkalan data.

Answer 1

Hmm, mungkin anda boleh cuba menambah indeks pada jadual anda: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#: ~ :text=Mencipta%20Indeks&teks=%20penyata%20kepada%20buat%20indeks,%20indeks%20mesti%20berbeza. Pastikan anda menambah indeks komposit berdasarkan baris yang anda tanya.

Jika ini tidak menambah baik masa pertanyaan anda, maka pertanyaan itu harus diperbaiki.

Answer 2

Nota:

• Gunakan UNION bukannya ATAU (lihat di bawah)
• Kunci berlebihan wujud. KUNCI PRIMER ialah kunci, jadi padam KEY(id)
• Jangan membuat indeks secara membuta tuli untuk setiap lajur, sebaliknya gunakan pertanyaan untuk menentukan indeks, terutamanya indeks kompaun, yang sebenarnya berguna.
• Dalam GROUP BY dan ORDER BY, CONCAT tidak diperlukan dan mungkin tidak produktif.
• Untuk jenis INT, medan panjang diabaikan. Apa yang anda ada ialah had 2 bilion nilai. (Manakah yang berlebihan untuk dilihat, dengan mengandaikan ia hanya mempunyai 0 atau 1?)
• Gunakan sintaks baharu: JOIN..ON.
• Jika dilihat hanya benar/salah, maka padamkan indeksnya. (Atau tunjukkan saya pertanyaan yang anda fikir anda akan mendapat manfaat daripadanya.)

CONCAT-LEAST-GREATEST - Adakah ini untuk membina "friends_id"? Mungkin apa yang anda mahukan sebenarnya ialah "id_perbualan"? Pada masa ini, dua pengguna tidak boleh mempunyai berbilang "perbualan", bukan?

Buat lajur baharu untuk conversation_id jika anda benar-benar memerlukannya. (Pada masa ini, GROUP BY tidak cekap.) Kod berikut menghapuskan keperluan untuk id sedemikian.

( SELECT lastid FROM (
    ( SELECT from_id, MAX(id) AS lastid FROM messages
           WHERE to_id = ? GROUP BY from_id )
    UNION DISTINCT
    ( SELECT to_id,   MAX(id) AS lastid FROM messages 
           WHERE from_id = ? GROUP BY to_id )
                     ) AS x
) AS conversations

Dan miliki indeks "penutup" dan "komposit" ini:

INDEX(to_id, from_id, id)
INDEX(from_id, to_id, id)

Mengalih keluar KEY(to_id), KEY(from_id) kerana indeks baharu saya boleh mengendalikan semua tugas lain bagi kedua-dua indeks ini.

Saya rasa ini mempunyai kesan yang sama tetapi berjalan lebih cepat.

Gabungkan mereka:

SELECT  *
    FROM (
            ( SELECT from_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages
                  WHERE to_id = ? GROUP BY from_id )
            UNION ALL
            ( SELECT to_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages 
                  WHERE from_id = ? GROUP BY to_id )
         ) AS latest
    JOIN  messages  ON messages.id = latest.lastid
    ORDER BY  messages.id DESC

(Tambahkan dua indeks ini)

Lagi

Saya pernah tersilap sangka bahawa UNION DISTINCT boleh menggantikan keperluan untuk conversation_id. tetapi ia bukanlah kebenaran. Saya segera melihat beberapa penyelesaian:

• Tambah id_perbualan dan gunakannya untuk penduadua. (Selain itu, saya menukar UNION DISTINCT kepada UNION ALL, menjadikan pertanyaan lebih pantas sedikit tanpa mengubah keputusan.)
• Letakkan hasil pertanyaan saya ke dalam jadual sementara yang mengandungi (from_id, to_id, latestid);
• Teknologi jadual sementara ini memudahkan penulisan dan penyahpepijatan. Cadangan ketiga saya hanyalah untuk menggabungkan bahagian ini menjadi satu pertanyaan (tidak boleh dibaca) dengan penyataan Pilih bersarang 3 tahap dalam.