Optymalizacja zapytań - łączenie tabel, a indeksy Opcje

[ReallyGrid]

Proszę o wyjaśnienie na jakie kolumny nakładać indeksy typu INDEX gdy mamy do czynienia z kilkoma tabelami i wszystkie one są wykorzystane do złączenia, warunku selekcji i grupowania. Manual MySQL'a wyjaśnia połowicznie gdyż mówi o indeksach jedno i wielokolumnowych ale tylko dla jednej tabeli.
Załóżmy, że mamy takie 3 tabele (TABA, TABB, TABC):

Kod

          TABA                         TABB                         TABC
+---------+-----+-------+    +---------+-----+-------+    +---------+-----+-------+
|  POLE   | TYP | KLUCZ |    |  POLE   | TYP | KLUCZ |    |  POLE   | TYP | KLUCZ |
+---------+-----+-------+    +---------+-----+-------+    +---------+-----+-------+
|   ID    | INT |  PRI  |    |   ID    | INT |  PRI  |    |   ID    | INT |  PRI  |
|  INTA   | INT |       |    |   IDA   | INT | INDEX |    |   IDB   | INT | INDEX |
|  INTB   | INT |       |    |    X    | INT |       |    |    Y    | INT |       |
| VISIBLE | INT |       |    | VISIBLE | INT |       |    | VISIBLE | INT |       |
+---------+-----+-------+    +---------+-----+-------+    +---------+-----+-------+

Oraz zapytanie, które łączy te tabele:

[SQL] pobierz, plaintext 
SELECT taba.inta, taba.intb, tabb.x, tabc.y
  FROM taba
    JOIN tabb ON taba.id = tabb.ida
      JOIN tabc ON tabb.id = tabc.idb
        WHERE taba.visible = 1 AND tabb.visible = 1 AND tabc.visible = 1
          ORDER BY taba.inta, taba.intb, tabb.x, tabc.y
[SQL] pobierz, plaintext 

Oto fragment z manuala MySQL'a

MySQL uses indexes for these operations:

• To find the rows matching a WHERE clause quickly.
• To eliminate rows from consideration. ?
• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. ?
• To retrieve rows from other tables when performing joins. ?
• To find the MIN() or MAX() value for a specific indexed column key_col. ?
• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1, key_part2). ?
• In some cases, a query can be optimized to retrieve values without consulting the data rows. ?

To, że muszę dać indeksy na kolumny tabb.ida i tabc.idb jest dla mnie oczywiste bo w końcu łączę tabele z wykorzystaniem tych kolumn.
Ale dalej mam jeszcze klauzule WHERE i ORDER BY. Żeby selekcja była szybka to jasne jest też, że muszę i na nie dać indeksy. I tu właśnie pojawia się coś czego manual nie tłumaczy.
Nałożyć indeksy na tabelę TABB mogę na kilka sposobów, dla przykładu:
Rozumowanie nr 1:

[SQL] pobierz, plaintext 
CREATE TABLE tabb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ida INT UNSIGNED NOT NULL,
  x INT,
  visible INT NOT NULL,
  PRIMARY KEY (id),
  INDEX (ida, id, visible, x)
)
[SQL] pobierz, plaintext 

Kolejność, jest ważna gdyż najpierw porównuję po ida, następnie porównuję po id, później wybieram po visible, na końcu sortuję po x.
Rozumowanie nr 2:

[SQL] pobierz, plaintext 
CREATE TABLE tabb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ida INT UNSIGNED NOT NULL,
  x INT,
  visible INT NOT NULL,
  PRIMARY KEY (id),
  INDEX (ida, id),
  INDEX (visible, x)
)
[SQL] pobierz, plaintext 

Tutaj rozumowanie jest takie. Skoro łączę po drodze (tzn. między pierwszym ON a WHERE) z trzecią tabelą, to ilość wierszy w tym momencie się zaburza, a zatem trzeba indeksować oddzielnie po ida i id oraz visible i x (choć już nie tak optymalnie jak by się to zrobiło nakładając jeden indeks czterokolumnowy z rozumowania 1.).
Rozumowanie nr 3:

[SQL] pobierz, plaintext 
CREATE TABLE tabb (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ida INT UNSIGNED NOT NULL,
  x INT,
  visible INT NOT NULL,
  PRIMARY KEY (id),
  INDEX (ida, id),
  INDEX (visible),
  INDEX (x)
)
[SQL] pobierz, plaintext 

Rozumowanie jest podobne jak wyżej. Złączenie warunkowe z trzecią tabelą tak zaburza selekcję wierszy, że niemożliwe jest optymalne działanie indeksu dwukolumnowego z rozumowania 2. Pozostaje zatem indeksować oddzielnie po ida + id, visible i x. Jest to najmniej wydajne indeksowanie (ale zawsze wydajniejsze niż gdyby indeksów w ogóle nie było).

A pytanie jest takie jak w pierwszym zdaniu. Jak optymalnie nałożyć indeksy na kolumny z tych tabel wiedząc, że zapytanie będzie wyglądało tak jak powyżej? Czy można w tym przypadku łączyć indeksy w takiej kolejności w jakiej występują w zapytaniu czy też, rozdzielić na te grupy indeksów, których nazwy kolumn występują przy różnych klauzulach: jedna grupa na ON, następna grupa indeksu na kolumny z warunku WHERE, a jeszcze inna grupa na kolumny przy ORDER BY.

Ten post edytował ReallyGrid 4.12.2016, 08:09:39

[cfk]

Wydaje mi się że 3 rozwiązanie
Bo: w 1 masz indeks 4 kolumnowy - odchodzi się od tak szerokich indeksów chyba że tylko czytasz z tabeli, a inserty robisz poza godzinami produkcji i nie obchodzi Cię wydajność.
Drugie rozwiązanie może być lepsze od 3 w przypadku gdy masz zdecydowanie dużo kombinacji visible i x. Bo jeśli visible bądź x ma tylko kilka możliwych wartość to dałbym trzecie rozwiązanie.

Ten post edytował cfk 15.03.2017, 17:58:59