SQL Indizes

K

Knight1

0
Hi HaBo-Com,
wollte mal Frage wie Ihr das macht wenn Ihr Indizes auf Spalten einer MySQL-Tablle legen müsst die irgendwie zusammen gehören.

Erstellt Ihr dann Einzel-Indizes für jede Spalte oder erstellt Ihr einen einzelnen Mehrspalten Index?

Und was ist dann besser/schneller beim abarbeiten eines SELECTs?

Im Manual finde ich dazu nichts. Vielleicht bin ich auch nur zu blöd zum lesen.


Kai aka Knight1
 
OK,
hier, als Beispiel, eine Tabellen-Struktur.

Code: 
CREATE TABLE IF NOT EXISTS `tce__news_ticker` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `active` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `start_date` (`start_date`),
  KEY `end_date` (`end_date`),
  KEY `active` (`active`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

Es geht, in diesem Beispiel, um die Spalten start_date und end_date.
Sollten diese zum Beispiel besser weiter als einzelne Indizes oder doch besser als Mehrspaltenindex angelegt werden?

end_date kann NULL sein. Ist also keine Pflichtangabe.


Kai aka Knight1
 
Sofern ich das noch richtig im Kopf habe, sind dann DELETE/UPDATE/INSERT langsamer.
Zudem kommt es auf die Abfrage und Tabellenart an, ob eine Indexierung tatsächlich etwas bringt ;)
Z.B:
MyISAM hat laut der Dokumentation BTree als Index
MySQL :: MySQL 5.0 Reference Manual :: 12.1.8 CREATE INDEX Syntax


Wenn so eine Anfrage bei indexierten Spalten stattfindet, wird der Eintrag schnell durch das "durchlaufen" des B-Trees erreicht (2-3 Zugriffe). Bloß: wenn im Ergebniss der Query ~30% oder mehr aller Einträge enthalten sind (oder abgefragt werden), kehrt sich das ganze um - es ist dann für die DB-Engine günsteger, direkt die Spalte(n) Eintrag für Eintrag abzuklappern.
Die Indexierung kann also auch mal nach hinten losgehen ;)
 
generell würde ich sagen:

Unter welchen Gesichtspunkten setze ich einen Index?
Wie schon gesagt wurde: auf Tabellen mit großen Datenmengen, bei denen die Geschwindigkeit des LESENs (also SELECT-Abfragen) optimiert sein soll.
Beispiel:
Tabellen welche Kunden, Produkte, Adressen, etc. beinhalten und Tabellen, welche als Bindeglieder zwischen 2 Tabellen für n:m-Beziehungen genutzt werden.

Besonders z.B. bei Produkt-Tabellen kommt es recht selten vor, dass geschrieben wird (nur, wenn neue Produkte eingepflegt werden) aber es wird häufig gelesen.

Bei Kundendaten ist das Verhältnis nicht ganz so extrem, aber trotzdem: bei JEDEM Seitenaufruf werden die Kunden-Daten eingelesen und nur jeder sonst-wieviel-hundertste-oder-tausendste Request ist 'ne Neuanmeldung...

Und wenn sich die Kunden/User z.B. mit 'ner Mail-Adresse oder 'nem Nickname einloggen, dann macht es durchaus Sinn, genau auf dieses Feld einen Index zu setzen
(da ja die Abfrage dann "SELECT * FROM customers WHERE username='foo' wäre)

Index über eine Spalte oder mehrere
je nachdem, wie die Daten später abgefragt werden.

  • wenn immer die Kombination aus 2 Werten zu einem eindeutigen gewünschten Ergebnis führt, dann EINEN Index über diese BEIDEN Spalten
  • wenn die Daten für sich alleine Sinn ergeben, dann einzelne Indizes


WELCHE Spalten sollten einen Index bekommen?
Alle Spalten, die regelmäßig für Abfragen benötigt werden.
Der PrimaryKey bekommt ja sowieso automatisch 'nen Index und außerdem machen dann sämtliche Spalten, die irgend eine ID darstellen und somit möglicherweise wiederum eine Beziehung zu Daten anderer Tabelle darstellen, Sinn, mit einem Index versehen zu werden.
Gerade wenn du über diese Felder dann JOINs ausführst, SOLLTE AUF JEDEN FALL ein Index auf diese Feldern liegen
 
ob ein index einspaltig oder mehrspaltig ausgeführt werden sollte ist von der abfrage abhänig die du damit beschleunigen willst:

ein beispiel:

du suchst in einem telefonbuch nach einem namen...

"meier" oder "müller" gibts recht oft, daher schränkst du das ganze branchenbuch-artig weiter ein in dem du noch die branche auf "bäckerei" eingränzt ...

nun sind telefonbücher in der regel nach einem alphabetischen index auf den namen sortiert, und deine liste mit leuten namens "meier" oder "müller" würdest du recht schnell finden, aber mit der zweiten einschränkung wirds nun schwierig ... die sortierte liste der "meier" oder "müller" einträge ist normalerweise nicht nach branche sortiert/indiziert ...

aber wir haben ja noch das branchenbuch hier würde die suche viel schneller zum erfolg führen, da branchenbücher in der regel einen zweidimensionalen/zweispaltigen index als sortierkriterium haben:

sie sind zunächst alphabetisch nach branche sortiert, was es dir einfach macht alle bäckereien zu finden ... innerhalb der bäckereien findest du wiederum eine alphabetisch nach namen sortierte/indizierte liste ... hier bis "meier" oder "müller" zu suchen ist nicht schwer ... allerdings würde dir dieser index kaum helfen wenn du nach name unabhänig von der branche suchen wolltest ...

kurz gesagt auf die reihenfolge der spalten im index kommt es an ...

ein index der erst nach name und dann nach branche sortieren würde, könnte beide anfragen bedienen ... er könnte aber nur schlecht unabhänig vom namen durchsucht werden ... ebenso können wildcrads im namen ein problem darstellen, das kommt sehr auf die indizierungs methode an...

die liste von suchkriterien muss sich von der ersten spalte des index her aufzählen lassen ...

hast du einen index über die spalten ABCD und du hast als suchkriterium nur die spalten BCD ist der index quasi nutzlos, während das suchkriterium ABC problemlos bedient werden könnte ...

je nach durchzuführender suche muss also der passende index gewählt werden ... aber vorsicht: ein index braucht pflege ... jede index daten ändernde operation muss zusätzlich zur eigentlichen datenänderung auch den index aktualisieren ... eine tabelle mit indices vollzustopfen wird wie bereits von CDW erwähnt INSERT/UPDATE und DELETE anweisungen ausbremsen ...
 
Also bisher habe ich nicht mehr als zwei Spalten in einem Index.
Es kommt nur selten vor (wie in dem oben geschriebenen Beispiel) dass ich nicht beide Spalten in Kombination abfrage.

Habe mal im MySQL-Manual nachgeschaut, aber nichts gefunden dahingehend.
Gibt es vielleicht eine Möglichkeit MySQL einen Index nach einer Datenmainpulation neu aufbauen zu lassen.

Kai aka Knight1
 
GrafZahl hat gesagt.:
was meinst du damit?
Zum Vergrößern anklicken....

GrafZahl hat gesagt.:
... eine tabelle mit indices vollzustopfen wird wie bereits von CDW erwähnt INSERT/UPDATE und DELETE anweisungen ausbremsen ...
Zum Vergrößern anklicken....

Den Satz habe ich so verstanden/verstehe ich so dass MySQL irgendwo/irgendwie die einzelnen Indizes als Liste mit den wichtigsten Informationen zu den Datensätzen abspeichert. Bei MyISAM in einer der 3 Dateien.
Bei jeder Datenverändernden Aktion wird diese halt dann nicht oder nur teilweise aktualisiert.
Es gibt ja das Statement REPAIR TABLE ...
Dachte mir halt dass es dann ein ähnliches für die Indizes gibt.

Vielleicht habe ich es auch nur falsch verstanden.


Kai aka Knight1
 
repair table etc beziehen sich unter anderem auf indices ...

ja, die indexdaten stehen in einer dieser dateien ...

um die aktualisierung musst du dich im normalfall nicht kümmern ... wenn du ein UPDATE/INSERT/DELETE machst werden die daten der indices automatisch aktualisiert ... das problem ist nicht DASS das passiert, sondern dass diese aktion mit IO operationen verbunden ist, und dadurch ressourcen beansprucht ... das system muss arbeiten ... je mehr es arbeiten muss desto länger dauert es bis sich dein UPDATE/INSERT/DELETE zurück meldet ... die aktion dauert einfach länger ...

das spielt bei kleinen tabellen kaum eine rolle ... aber wehe du hast 20 möglicherweise auch noch mehrspaltige indices auf einer tabelle und lässt "mal eben" ein paar millionen zeilen eintragen ... da machen sich deine indices dann deutlich negativ bemerkbar ...

praxisbeispiel: ich habe während eines praktikums mal bei einem kunden gesessen, wo der admin "mal eben" daten aus einer preview DB ins produktivsystem schubsen sollte ... 30 mio zeilen ... ~ 5,5 GB

massiv indizierte tabellen ...

als ich am nächsten tag wieder kam, war die kiste immernoch dran ...

ne woche später das gleiche spiel ... er sollte wieder daten übernehmen ... ich hab ihm geraten die DB vom produktivsystem zu trennen, die indices zu droppen, das riesen insert/update zu machen, und die indices neu zu erstellen ... zeitersparnis in diesem fall: runde 18-19 stunden ... nun mag man dazu sagen dass das maschinchen nicht mehr das neueste war, aber der unterschied ist deutlich ... da im produktivbetrieb aber keinerlei änderungen an der DB durchgeführt werden, lohnt sich die masse an indices ...

wobei man darüber streiten kann, ob man nicht eher normalisieren sollte, aber das wirft dann neue performance fragen auf ... im zweifel hat halt immer der kunde recht wenn er seine tabellen genau so haben will ... auch wenns der größte käse ist ...
 
Ich habe gar nicht solch große Tabelle.
Die größte Tabelle die ich habe ist die ip_to_country-Tabelle mit etwas weniger als als 112.000 Einträgen.
Da ich ich nicht weiß ob Ihr die Tabelle kennt, hier mal die Struktur:
Code: 
CREATE TABLE IF NOT EXISTS `tce__ip_to_country` (
  `ip_range_start` int(100) NOT NULL,
  `ip_range_end` int(100) NOT NULL,
  `country_short` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `country_long` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `country_full` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  KEY `ip_range_start` (`ip_range_start`),
  KEY `ip_range_end` (`ip_range_end`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Wir zu sehen ist habe ich hier noch einzelne Indizes.

Nach Deinen Posts zu schliesen wäre hier ein mehrspaltiger Index mit den Spalten ip_range_start und ip_range_end in diesem Fall besser.

Die Abfrage lautet ungefähr so:
Code: 
SELECT `country_short` FROM `tce__ip_to_country` WHERE ((`ip_range_start` <= '".ip2long($_SERVER['REMOTE_ADDR'])."') && (`ip_range_end` >= '".ip2long($_SERVER['REMOTE_ADDR'])."'))

Datenmanipulation findet an dieser Tabelle nicht statt.

Wenn das Statement REPAIR TABLE... auch die Indizes neu aufbaut/orgnisiert (wie auch immer man das nennt), wäre es dann nicht sinnvoll dieses mal alle paar Wochen automatisch über die Tabellen laufen zu lassen? Vor allem bei Tabllen wo viele Schreiboperationen durchgeführt werden.

Für's erste werde ich, sobald das System Online geht, das ganze mal beobachten.


Kai aka Knight1
 
Um antworten zu können musst du eingeloggt sein.
Zurück
Oben