Mysql-Belag vs composite vs Spalte index

In der folgenden Abfrage

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'
  AND   col4='value2'

Wenn ich 2 getrennte Indizes auf col3 und die andere auf col4, Die einer von Ihnen verwendet werden in dieser Abfrage ?

Ich habe irgendwo gelesen, dass für jede Tabelle in der Abfrage nur ein index verwendet wird. Bedeutet das, dass es keine Möglichkeit gibt, die Abfrage zu verwenden, beide Indizes ?

Zweitens, Wenn ich einen zusammengesetzten index über beide col3 und col4 zusammen, aber nur verwendet col3 in der WO Klausel, dass sein schlechter für die performance?
Beispiel:

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'

Schließlich, Ist es besser, einfach zu verwenden Abdeckung Indizes in allen Fällen ? und wie unterscheidet es sich zwischen MYISAM und innodb-Speicher-engines ?

InformationsquelleAutor Songo | 2011-11-21

41

Einen abdeckenden index ist nicht das gleiche wie ein zusammengesetzter index.

Wenn ich 2 getrennte Indizes auf col3 und die andere auf col4, Die einer von Ihnen verwendet werden in dieser Abfrage ?

Den index mit der höchsten Kardinalität.

MySQL speichert Statistiken, die auf dem index hat, was Eigenschaften.

Der index, der die anspruchsvollsten power (wie offensichtlich in der MySQL-Statistik) verwendet werden.

Ich habe irgendwo gelesen, dass für jede Tabelle in der Abfrage nur ein index verwendet wird. Bedeutet das, dass es keine Möglichkeit für die Abfrage verwendet beide Indizes ?

Können Sie eine Teilauswahl treffen.

Oder noch besser, verwenden Sie einen zusammengesetzten index, der beide enthält col3 und col4.

Zweitens, Wenn ich einen zusammengesetzten index über beide col3 und col4 zusammen, aber nur verwendet, col3 in der WHERE-Klausel wird das schlimmer für die performance? Beispiel:

Zusammengesetzten index

Der richtige Begriff ist compound index, nicht composite.

Nur die Links-die meisten Teil des zusammengesetzten index verwendet werden.

Wenn also der index ist definiert als
```
index myindex (col3, col4)  <<-- will work with your example.
index myindex (col4, col3)  <<-- will not work. 
```
Finden Sie unter: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

Beachten Sie, dass wenn Sie einen linken Bereich, die Sie bekommen können Weg mit nicht mit, dass ein Teil des index in die where-Klausel.

Man Stelle sich vor wir haben einen zusammengesetzten index
```
Myindex(col1,col2)

SELECT col1 FROM table1 WHERE col2 = 200  <<-- will use index
SELECT * FROM table1 where col2 = 200     <<-- will NOT use index.  
```
Der Grund, warum dies funktioniert, ist, dass die erste Abfrage verwendet das abdeckenden index und macht einen scan auf.

Die zweite Abfrage muss die Tabelle zuzugreifen und aus diesem Grund Scannen, obwohl der index nicht sinnvoll.

Dies funktioniert nur in InnoDB.

Was ist ein covering index

Einen abdeckenden index bezieht sich auf den Fall, wenn alle ausgewählten Felder in einer Abfrage sind covered durch einen index, in diesem Fall InnoDB (nicht MyISAM) nie Lesen werden die Daten in der Tabelle, sondern nur die Daten in der index um ein Vielfaches beschleunigt das auswählen.

Beachten Sie, dass in InnoDB-der primary key ist enthalten in allen sekundären Indizes, also in einer Weise, die alle sekundären Indizes zusammengesetzte Indizes.

Dies bedeutet, dass wenn Sie führen Sie die folgende Abfrage auf InnoDB:
```
SELECT indexed_field FROM table1 WHERE pk = something
```
MySQL verwendet immer einen abdeckenden index und nicht auf die tatsächliche Tabelle.
- Danke für die Erklärung und vor allem der Teil über dem zusammengesetzten index. Ich wusste nicht, das nur der linke Teil des index wird zuerst überprüft. Ich dachte, wenn ein Teil des zusammengesetzten index verwendet wird, der andere Teil wird einfach verworfen.
- Tolle info!! Habt Ihr Dokument über das "index myindex (col4, col3) <<-- wird nicht funktionieren. "? Ich glaube nicht, nicht Vertrauen Sie, aber ich will einfach nur, um mehr darüber zu Lesen.
- die offiziellen docs oder eine große Quelle, aber beachten Sie, dass dies nicht das ganze Geschichte. Siehe die aktualisierte Antwort.
- Vielen Dank 🙂
- SELECT x FROM tbl WHERE pk=1 -- InnoDB, PK ist mit den Daten gespeichert. So, ob 'mit' oder nicht, ist irrelevant; es nicht auf die Daten zugreifen.
- SELECT col1 FROM table1 WHERE col2 = 200 <<-- wird, verwenden Sie einen index(col1, col2). Stimmt, aber... hat Es zu Scannen den gesamten index. Dies ist wahrscheinlich besser als das Scannen der Tabelle, aber nur, weil der index wahrscheinlich kleiner als die Tabelle.
- Sie sagen, "funktioniert nur in InnoDB". Sobald Sie verstehen, dass Sie eine MyISAM-PK ist unabhängig von der Daten. Und dass InnoDB die sekundären Tasten implizit enthalten das PK-Feld(s), dann sind die Regeln für die beiden Motoren sind im wesentlichen die gleichen.
- index myindex (col4, col3) <<-- wird nicht funktionieren. -- das bezieht sich auf SELECT ... WHERE col3=..., nicht die anderen WÄHLEN.
- Sie erklärte, dass select * from table1 where col2 = 200 wird nicht der index, aber bei mir lief der oben genannten tests, explain zeigt, dass die Indizes tatsächlich verwendet werden. Welche Betriebssysteme Sie auf?
- für die Letzte sql, ich denke du meinst `` SELECT id,indexed_field_1 AUS Tabelle1 WO indexed_field_2 = etwas`` - id oder z.B. den cluster-Schlüssel oder Primärschlüssel wird auf der select-Klausel.
InformationsquelleAutor Johan
5

Ich von Ihnen positiv bewertet werden Johan ' s Antwort für die Vollständigkeit, aber ich denke, dass die folgende Aussage, die er macht über sekundäre Indizes ist falsch und/oder verwirrend;
```
Note that in InnoDB the primary key is included in all secondary indexes, 
so in a way all secondary indexes are compound indexes.

This means that if you run the following query on InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

MySQL will always use a covering index and will not access the actual table.
```
Ich Stimme zwar der Primärschlüssel ist ENTHALTEN im sekundären index, ich nicht einverstanden MySQL "wird immer mit einem abdeckenden index" in der SELECT-Abfrage angegeben hier.

Zu sehen, warum, beachten Sie, dass ein vollständiger index "scan" ist immer erforderlich in diesem Fall. Dies ist nicht das gleiche wie ein "seek" - Vorgang, sondern ist ein 100% - scan der sekundäre index-Inhalt. Dies ist aufgrund der Tatsache, dass die sekundäre index ist nicht bestellt durch die primäre Taste; es wird bestellt von "indexed_field" (sonst würde es nicht viel nützen, da ein index!).

Im Lichte dieser letzteren Tatsache, es wird Fälle geben, wo es effizienter ist, um "suchen" den Primärschlüssel, und extrahieren Sie dann indexed_field "von der eigentlichen Tabelle," nicht aus dem sekundären index.
- wie egal ist es, was es bestellt hat(?) wenn es eine explizite Eigenkapital: pk = something...
- wo ist der entsprechende 'pk' finden sich in der sekundären index? die Datenbank-engine hat keine Ahnung. es ist zu beginnen am Anfang der sekundären index und 'scan' jeden Eintrag in es, von Anfang bis Ende. denken Sie über die index-Abschnitt ein Rezeptbuch: es ist geordnet nach dem alphabet, nicht nach der Seitenzahl. wenn Sie finden wollte, der Titel ein Rezept, und Sie wussten schon die Nummer der Seite, die Sie möglicherweise wenden Sie sich einfach an die Seite und beobachten Sie die Titel direkt auf, anstatt das Scannen der index von Anfang bis Ende für alle vorkommen der Seitenzahl.
- hier ist mein Lieblings-Buch zum Thema übrigens auch: amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026. es bezieht sich direkt auf MS-SQL-Server, aber die Konzepte sind praktisch die gleichen wie MySQL, anderen relationalen Datenbank-engines.
- "wo ist der entsprechende 'pk', zu finden in der Sekundär-index?" — "Beachten Sie, dass in InnoDB-der primary key ist enthalten in allen sekundären Indizes, also in einer Weise, die alle sekundären Indizes zusammengesetzte Indizes."
- was ich meine ist, wenn ich bin auf der Suche nach der pk mit dem Wert z.B. "123", wo nacheinander finde ich diese "123" in der sekundären index? die Datenbank-engine hat keine Ahnung, wie der sekundäre index ist nicht sortiert nach dem Primärschlüssel. es hat zu Scannen jeden Eintrag, der von Anfang bis Ende.
InformationsquelleAutor Sam T
1

Dies ist eine Frage, die ich viel hören, und es gibt eine Menge Verwirrung um die Probleme aufgrund von:
- Die Unterschiede in mySQL über die Jahre.
  Indizes und mehreren index-Unterstützung über die Jahre verändert (Richtung unterstützt wird)
- InnoDB /myISAM-Unterschiede
  Es gibt einige wichtige Unterschiede (unten), aber ich glaube nicht, dass mehrere Indizes einer von Ihnen
MyISAM ist älter, aber bewährt. Daten in MyISAM-Tabellen ist aufgeteilt zwischen drei unterschiedliche Dateien: - - Tisch-format -, Daten-und Indizes.

InnoDB ist ein relativ neuer ist als MyISAM, und ist die Transaktion sicher. InnoDB bietet auch row-locking im Gegensatz zu Tisch-Verriegelung erhöht die multi-user-Parallelität und Leistung. InnoDB hat auch foreign key-Einschränkungen.

Wegen seiner row-locking-Funktion InnoDB ist gut geeignet für high load Umgebungen.

Sicher sein über die Dinge, stellen Sie sicher, dass Sie explain_plan zu analysieren, die Ausführung der Abfrage.
- Sie betrachten InnoDB "bewiesen" jetzt?
- Ich würde sagen, dass InnoDB wurde "bewiesen" werden, bevor die Antwort gepostet wurde. 8.0 entledigt MyISAM. Das ist, wie zuversichtlich Oracle mit InnoDB heute.
InformationsquelleAutor Michael Durrant
-1

Verbindung index ist nicht das gleiche wie ein composite index.
- Composite index umfasst alle Spalten in Ihrer filter -, join, und wählen Sie Kriterien. Alle diese Spalten gespeichert sind, auf allen index-Seiten, die entsprechend während der index-B-Baum.
- Zusammengesetzten index deckt alle filter-und join-Schlüssel Spalten in der B-Baum, sondern hält die wählen Sie Spalten nur auf das Blatt, die Seiten werden nicht durchsucht, sondern nur extrahiert!
  Das spart Platz und schafft somit weniger index-Seiten somit schneller I/O.
- Nein. Eine "Abdeckung" index alle Spalten enthält, verwendet, während die SELECT. Ein "Composite" oder "Compound" - index mehr als eine Spalte in den index.
InformationsquelleAutor Christo Belokov

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.