MySQL - Tabelle optimieren

Seite 1 von 1 - Forum: Coding Stuff auf overclockers.at

URL: https://www.overclockers.at/coding-stuff/mysql_tabelle_optimieren_114039/page_1 - zur Vollversion wechseln!

Hi,

Folgende Tabelle:

Code:

CREATE TABLE `mautdaten` (
  `Datum` date NOT NULL default '0000-00-00',
  `Zeit` time NOT NULL default '00:00:00',
  `Referenznummer` bigint(12) unsigned zerofill NOT NULL default '000000000000',
  `MautabschnittsID` smallint(4) unsigned zerofill NOT NULL default '0000',
  `Strassenkennzahl` char(5) NOT NULL default '',
  `Mautabschnittsbezeichnung` char(60) NOT NULL default '',
  `KfzKennzeichen` char(12) NOT NULL default '',
  `Nationalitaet` char(2) NOT NULL default '',
  `GoBoxNummer` char(18) NOT NULL default '',
  `KfzKennungFraechter` char(20) NOT NULL default '',
  `KostenstelleFraechter` char(20) NOT NULL default '',
  `Vertragsart` char(4) NOT NULL default '',
  `Kategorie` tinyint(2) unsigned zerofill NOT NULL default '00',
  `Hinweis` char(4) NOT NULL default '',
  `Zahlungsmittel` bigint(20) unsigned zerofill NOT NULL default '00000000000000000000',
  `Netto` int(7) unsigned zerofill NOT NULL default '0000000',
  `USt` int(7) unsigned zerofill NOT NULL default '0000000',
  `Brutto` int(7) unsigned zerofill NOT NULL default '0000000',
  KEY `Datum` (`Datum`),
  KEY `Zeit` (`Zeit`),
  KEY `GoBoxNummer` (`GoBoxNummer`),
  KEY `Netto` (`Netto`),
  FULLTEXT KEY `KfzKennzeichen` (`KfzKennzeichen`)
) TYPE=MyISAM ROW_FORMAT=FIXED

Code:

EXPLAIN
SELECT MAX(datum) AS LetztesDatum,KfzKennzeichen
FROM mautdaten
GROUP BY KfzKennzeichen
ORDER BY NULL;

Code:

Using temporary

Code:

EXPLAIN
SELECT LPAD(SUM(netto),7,'0') AS Netto,KfzKennzeichen
FROM mautdaten
WHERE datum>='DATUMVON' AND datum<='DATUMBIS'
GROUP BY KfzKennzeichen
ORDER BY NULL;

Code:

Using where; Using temporary

Die besten Hints habe ich immer noch bekommen wenn ichs direkt an die MySQL-Listen geschickt habe, hat sich immer ausgezahlt. Man bekommt auch recht schnell Antwort. HTH ;-)

temptables werden auch bei "group by" verwendet.
damit alles schön schnell geht sollte man
*) tmp_table_size sollte groß genug sein
*) der temptable sollte entweder auf einem memdrive oder auf mehreren physischen datenträgern sein

wenn er bei sorts einen temptable erstellt dann sort_buffer und read_rnd_buffer_size erhöhen.

wenn möglich, dann nichts aus dem table löschen, sonst wird der table "fragmentierter" und damit auch langsamer, bis OPTIMIZE TABLE ausgeführt wird.

allgemein zu datenspeicherung von so einem großem table:
wenn möglich dann teilen.. weil ein 4 gig table ist unbearbeitbar und damit meine ich auch von der wartung her. ich denke ich würde einen table machen der die großen datentable pro jahr oder vl besser pro fixer größe enthält und jegliche queries verwaltet. der overhead/query wäre imo ziemlich gering und du müsstest die tabletrennung niemals händisch durchführen. is nur so eine idee

1. wegen tmp_table_size:

Code:

If an in-memory temporary table exceeds this size,
MySQL automaticallys convert it to an on-disk MyISAM table.
Increase the value of tmp_table_size
if you do many advanced GROUP BY queries and you have lots of memory.

Code:

RENAME TABLE mautdaten TO mautdaten_04;
CREATE NEW TABLE mautdaten .........

ich nehm mal an du musst nicht nur daten aufnehmen sondern auch queries durch diese daten machen um gewissen informationen, statistiken, usw. herausholen zu können. ein allgemeines abstrahiertes system könnte dir viel arbeit ersparen = eine klasse die einen table mit allen bisher gesamelten tables und deren daten beinhält und queries, egal ob insert, select oder wwi handhabt.

also:

tblDataManager
beinhält tablename und vl auch noch andere wichtige und schnell ereichbare daten. zB daten eingetragen von datum bis datum (= 2 datelines) usw.

dieser table verwaltet also die anderen tables die deine mautdaten beinhalten. das ganze eingepackt in eine schöne klassen die funktionen wie query() usw. hat und entscheidet welche tables von den queries betroffen sind blabla.

ein beispiel:
tblDataManager
1. Datensatz: 'tbl2003', datelinevon (unix timestamp), datelinebis, ..
2. Datensatz: 'tbl2004', usw.

die tables werden natürlich von der klasse DataManager bei bedarf selber erstellt, nach bestimmten regeln.. zB größe des aktuellen tables größer als 500 mb oder anzahl der datensätze größer als 50.000 oder so ähnlich.

ich hoffe ich hab mich jetzt verständlicher ausgedrückt. das system wäre einfach nur g0il, kann aber natürlich sein dass es für deine Aufgabe/Zeitrahmen nicht geeignet ist.

Wenn du nach kfzKennzeichen gruppierst, muss das am Anfang (oder als einzige Spalte) in einem Index stehen.

Leider ist MySQL manchmal sehr unwillig, einen Index zu verwenden. Ich hab auch schon öfters Fälle gehabt, wo es sich einfach beim besten Willen nicht dazu überreden lässt. Aber dein Beispiel ist so simpel, da sollt's eigentlich ohne Probleme gehen.

ich muss mich phex bedingungslos anschließen. es stellt sich jedoch die frage, ob du nicht wegen performancegründen auf eine gewisse redundanz zurückgreifen solltest.
beispiel: du nimmst zusätzlich zur spalte "komplettes-kennzeichen" noch eine mit dem bezirk. fixe länge+index verstehen sich von selbst imho.
das würde eben viel sinn machen wenn du sonst oft eine substring-funktion o.ä. brauchst

Der phex hat ja gar nix gepostet ...

er meint matphex!

overclockers.at v4.thecommunity
© all rights reserved by overclockers.at 2000-2026