Indeks pertanyaan dengan klausa WHERE dan ORDER BY tidak digunakan

Question

Saya mencipta jadual seperti yang ditunjukkan di bawah

CREATE TABLE IF NOT EXISTS
`table`
  (
     id          VARCHAR(100) NOT NULL,
     seq_id      BIGINT UNSIGNED NOT NULL,
     in_use      BOOLEAN NOT NULL DEFAULT false,
     scheduled   BOOLEAN NOT NULL DEFAULT false,
     dialed      BOOLEAN NOT NULL DEFAULT false,
     priority    INT UNSIGNED NOT NULL DEFAULT 0,
     data_0      VARCHAR(100) NULL,
     data_1      VARCHAR(100) NULL,
     data_2      VARCHAR(40) NULL,
     data_3      VARCHAR(200) NULL,
     data_4      VARCHAR(10) NULL,
     data_5      DECIMAL(65, 20) NULL,
     data_6      DECIMAL(65, 20) NULL,
     PRIMARY KEY (`id`)
  )

Mempunyai pertanyaan besar yang memilih baris berdasarkan klausa tempat dan kemudian mengisih hasilnya.

SELECT id
FROM
`table`
WHERE  ( dialed = false
         AND in_use = false
         AND scheduled = false )
ORDER  BY priority DESC,
          data_6 ASC,
          data_5 DESC,
          data_4 ASC,
          data_3 DESC,
          seq_id
LIMIT  100

Saya cuba mencari indeks terbaik untuk pertanyaan ini dengan menjalankan EXPLAIN. Saya mencipta beberapa indeks berbeza ;

1. (didail, sedang digunakan, dijadualkan, keutamaan, data_6, data_5, data_4, data_3, seq_id)
2. (keutamaan, data_6, data_5, data_4, data_3, seq_id)
3. (dijadualkan, sedang digunakan, didail, keutamaan, data_6)
4. (tempah, sedang digunakan)

JELASKAN pertanyaan memaparkan perkara berikut;

possible_keys: [index1],[index3],[index4]         
key: [index4]
key_len: 2
ref: const, const
rows: 448
filtered: 100.0
Extra: Using index condition; Using where; Using filesort

Saya ingin tahu mengapa ia tidak menggunakan indeks yang mengandungi lajur ORDER BY (indeks1 dan indeks3), dan mengapa ia memilih indeks yang hanya mengandungi subset lajur klausa WHERE? Saya rasa indeks 1 mempunyai liputan lajur penuh pertanyaan dan sesuai.

Indeks yang meliputi hanya lajur ORDER BY (indeks2) tidak ditunjukkan dalam kekunci_mungkin sama sekali. Adakah saya mentakrifkan indeks dalam susunan yang salah di sini?

Adakah mungkin untuk pertanyaan menggunakan satu indeks untuk menapis dan kemudian menggunakan indeks lain untuk mengisih keputusan?

Seperti yang anda lihat, saya menjalankan 448 baris ujian. Pertanyaan ini boleh dijalankan pada jadual yang lebih besar sehingga satu juta. Untuk jadual yang lebih besar, adakah indeks lain akan menjadi lebih berprestasi daripada indeks 4?

Akhir sekali, adakah indeks seperti indeks 1 dengan banyak lajur akan merendahkan prestasi hanya kerana bilangan lajur?

Answer 1

3 kemungkinan:

Gabungan ini berguna jika ia secara selektif "cukup": INDEX(dialed, in_use, Schedule). Urutan 3 ini tidak penting.

Jika anda menggunakan MySQL 8.0, maka ini mungkin berguna (dalam susunan yang diberikan):

INDEX(priority DESC,
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)

Versi lama diabaikan DESC，使它们不使用 INDEX

Malah (sekali lagi pada 8.0):

INDEX(dialed, in_use, schedule,   -- in any order
      priority DESC,    -- the rest, as specified
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)