Abfrageindex mit WHERE-Klausel und ORDER BY nicht verwendet

Question

Ich habe eine Tabelle wie unten gezeigt erstellt

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`)
  )

Verfügen Sie über eine große Abfrage, die Zeilen basierend auf der Where-Klausel auswählt und dann die Ergebnisse sortiert.

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

Ich versuche, den besten Index für diese Abfrage zu finden, indem ich EXPLAIN ausführe. Ich habe ein paar verschiedene Indizes erstellt ;

1. (gewählt, in Verwendung, geplant, Priorität, data_6, data_5, data_4, data_3, seq_id)
2. (Priorität, Daten_6, Daten_5, Daten_4, Daten_3, seq_id)
3. (geplant, in Verwendung, gewählt, Priorität, Daten_6)
4. (gebucht, in Nutzung)

Die

EXPLAIN-Abfrage zeigt Folgendes an:

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

Ich bin neugierig, warum kein Index verwendet wird, der die ORDER BY-Spalten (Index1 und Index3) enthält, und warum ein Index ausgewählt wird, der nur eine Teilmenge der Spalten der WHERE-Klausel enthält? Ich denke, Index 1 deckt die gesamte Spalte der Abfrage ab und ist ideal.

Der Index, der nur die ORDER BY-Spalte (index2) abdeckt, wird in „possible_keys“ überhaupt nicht angezeigt. Definiere ich hier die Indizes in der falschen Reihenfolge?

Ist es möglich, dass eine Abfrage einen Index zum Filtern und dann einen anderen Index zum Sortieren der Ergebnisse verwendet?

Wie Sie sehen, führe ich 448 Testzeilen durch. Diese Abfrage kann für größere Tabellen ausgeführt werden; Sind andere Indizes bei größeren Tabellen am Ende leistungsfähiger als Index 4?

Abschließend: Wird ein Index wie Index 1 mit vielen Spalten die Leistung nur aufgrund der Anzahl der Spalten beeinträchtigen?

Answer 1

3种可能：

如果这个组合选择性“足够”，那么它是有用的：INDEX(dialed, in_use, Schedule)。这 3 个的顺序并不重要。

如果您使用 MySQL 8.0，那么这可能很有用（按给定的顺序）：

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

旧版本忽略 DESC，使它们不使用 INDEX

甚至（再次在 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)