Datenbank Performance

Tja, ich bin gerade beim tweaken und logge seit einigen Stunden sämtliche Queries inklusive deren Dauer. In dieser Zeit hatte ich etwa 30-50 Queries pro Sekunde. Das Spiel läuft auf einem root mit 1GB Ram (ca 50% ungenutzt) und "AuthenticAMD, Dual-Core AMD Opteron(tm) Processor 1210 HE".

Aus meinen Logs stechen vor allem 2 Queries immerwieder heraus. Die benötigen oft mehrere Hundertstelsekunden und teilweise länger als eine Sekunde!

#1 (der schlimmere)

SQL:

		SELECT
			tq, def, block, mindam, maxdam
		FROM
			items_main I
			INNER JOIN
				user_items U
				ON
				I.typ = U.typ
				AND
				I.typ2 = U.typ2
		WHERE
			userID = %d
			AND
			equip  NOT IN ('none','kampf','temp','bank','auktion')

 #2

SQL:

			SELECT
				statID, value, `expire`, I.itemID
			FROM
				items_stats I
				INNER JOIN
					user_items UI
					ON
					UI.itemID = I.itemID
			WHERE
				I.itemID = UI.itemID
				AND
				UI.userID = %d
				AND
				UI.equip NOT IN ("none","kampf","temp","bank","auktion")
				AND
				( 
					expire = 0
					OR
					expire > %d
				)				

 Hier noch die entsprechenden Tabellenstrukturen

SQL:

-- phpMyAdmin SQL Dump
-- version 2.11.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 15. August 2008 um 15:20
-- Server Version: 5.0.18
-- PHP-Version: 5.1.2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Datenbank: `chaos_returns`
--
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `items_main`
--
CREATE TABLE IF NOT EXISTS `items_main` (
  `name` varchar(30) collate utf8_unicode_ci NOT NULL,
  `typ` tinyint(1) unsigned NOT NULL default '0',
  `typ2` tinyint(1) unsigned NOT NULL default '0',
  `preis` int(3) unsigned default '0',
  `def` int(2) unsigned default '0',
  `block` tinyint(1) unsigned default '0',
  `mindam` int(2) unsigned default '0',
  `maxdam` int(2) unsigned default '0',
  `tq` int(1) unsigned default '0',
  `req_str` tinyint(1) unsigned default '0',
  `req_dex` tinyint(1) unsigned default '0',
  `req_int` tinyint(1) unsigned default '0',
  `pfad` varchar(40) collate utf8_unicode_ci NOT NULL,
  `width` enum('1','2') collate utf8_unicode_ci NOT NULL default '1',
  `height` enum('1','2','3','4') collate utf8_unicode_ci NOT NULL default '1',
  `maxStack` tinyint(1) unsigned NOT NULL default '20',
  `maxSockets` tinyint(1) unsigned NOT NULL default '0',
  `dropclass` varchar(10) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`name`),
  KEY `typ` (`typ`),
  KEY `typ2` (`typ2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `items_stats`
--
CREATE TABLE IF NOT EXISTS `items_stats` (
  `itemID` int(3) unsigned NOT NULL default '0',
  `statID` int(2) unsigned NOT NULL default '0',
  `value` float default '0',
  `expire` int(4) unsigned NOT NULL default '0',
  KEY `itemID` (`itemID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `user_items`
--
CREATE TABLE IF NOT EXISTS `user_items` (
  `itemID` int(3) unsigned NOT NULL auto_increment,
  `userID` int(3) unsigned NOT NULL default '0',
  `name` varchar(51) collate utf8_unicode_ci NOT NULL default '%ARGERROR%',
  `typ` tinyint(1) unsigned NOT NULL default '0' COMMENT 'BasisTyp',
  `typ2` tinyint(1) unsigned NOT NULL default '0' COMMENT 'SubTyp',
  `status` enum('normal','socket','magic','rare','speziell','profession') character set utf8 NOT NULL default 'normal',
  `sockets` tinyint(1) unsigned NOT NULL default '0',
  `socketTypes` varchar(50) collate utf8_unicode_ci NOT NULL,
  `anzahl` int(2) unsigned default '1',
  `preis` int(2) unsigned NOT NULL default '1',
  `equip` enum('none','head','amu1','amu2','hand1','hand2','armor','ring1','ring2','ring3','boots','belt','gloves','soul','kampf','temp','bank','auktion') character set utf8 NOT NULL default 'none',
  `fachID` int(3) unsigned NOT NULL COMMENT 'Bank',
  `bagSlot` int(1) unsigned NOT NULL COMMENT 'invSlot für Bank und Inventar',
  `req_lvl` tinyint(1) unsigned NOT NULL default '1',
  `req_klasse` enum('magier','gladiator','waldläufer') character set utf8 default NULL,
  PRIMARY KEY  (`itemID`),
  KEY `fachID` (`fachID`),
  KEY `typ` (`typ`),
  KEY `typ2` (`typ2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=23601 ;

In keiner der Tabellen sind mehr als ein paar tausend Einträge. Kann mir jemand verraten, wieso #1 auf Ausführzeiten von teilweise über einer Sekunde kommt?

explain query; liefert sinnvolle hinweise wie mysql querys abarbeitet. danach kann man dann optimieren. ganz kurz weil ich grad keine lust hab mich durch wirre selects durchzuguggen: 1.1.) du machst ein select auf eine table + join wobei das where nur die innere table zutrifft=> sehr schlecht. 1.2.) du hast ein not in in der where => auch sehr schlecht.

2) siehe 1.1 und 1.2 und sinnloses wherebedingung

Als erstes danke für die Antwort :)

Zu 1.1:

Heißt das, es wäre sinnvoller, von user_items in items_main zu joinen? Ich hatte es in dieser Reihenfolge, da die Anzahl Datensätze in items_main konstant ist und mir eingebildet habe, das sei so sinnvoller.

zu 1.2:

Also den Teil lieber weglassen und per PHP rausfiltern?

zu 2) Ups, ja die eine sinnlose Zeile im Where fiel mir auch schon auf - die wollte ich vor dem abschicken eigentlich noch entfernen ^^"

/edit:

Hier mal das Explain zu #1

Code:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE I ALL typ,typ2 NULL NULL NULL 125   1 SIMPLE U ref typ,typ2 typ 1 chaos_returns.I.typ 379 Using where

Dem entnehme ich, dass er die Indizes überhaupt nicht nutzt. Ich beles mich in der Sache gerade aber wenn mir dazu jemand einen Tip aussprechen kann, bin ich ihm auch sehr dankbar :)

/edit2: hab jetzt was an der indizierung geschraubt aber ich kann dem nicht entnehmen, ob das jetzt besser ist ...

Code:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE U ALL typ NULL NULL NULL 8081 Using where 1 SIMPLE I ref typ typ 2 chaos_returns.U.typ,chaos_returns.U.typ2 1  

/edit 3: hm, bis gerade eben war das obere auch noch in Tabellenform ... Technik die begeistert

Kannst du die Ausgabe als preformatted posten? Ist recht schwer rauszulesen.

MySQL benutzt keine Indizies wenn es sich nicht lohnt. Was viele nicht wissen: Man kann mehrere Felder in einem Index zusammenfassen und MySQL kann pro Abfrage nur einen Index benutzen!

Also versuch erstmal mit einem Index alle Felder des WHERE einzubinden und das gleiche für die Join-Bedingungen.

Okay aktueller Stand

#1 sieht jetzt so aus:

SQL:

		SELECT
			tq, def, block, mindam, maxdam, equip
		FROM
			items_main I
			INNER JOIN
				user_items U
				ON
				I.typ = U.typ
				AND
				I.typ2 = U.typ2
		WHERE
			userID = %d

Den Rest filter ich via PHP. Außerdum habe ich, wie du empfohlen hast, in beiden Tabellen typ und typ2 zu einem Index zusammengefasst. Zusätzlich user_items.userID indiziert und ich bin bei 0.0007s Queryzeit - sogar, wenn ich den "not in"-Teil wieder reinnehmen würde. Geil!

#2

SQL:

			SELECT
				statID, value, `expire`, I.itemID, UI.equip
			FROM
				items_stats I
				INNER JOIN
					user_items UI
					ON
					UI.itemID = I.itemID
			WHERE
				UI.userID = %d

 ebenfalls um den "not in"-Teil gekürzt. Auch dieser Query profitiert von dem Index auf user_items.userID. Ergebnis sind ähnliche Zeiten wie bei #1.

Wahnsinn, wie sich das mit der richtigen Indizierung optimieren ließ. Ich danke euch für die gute Hilfe!

generell: NOT IN ist nicht index-kompatibel (iirc.) Ich glaub da haut er dann sogar jedes mal einen seq-scan rein (so ziemlich das grauenhafteste).

hth ;)

zum NOT IN, da das scheinbar ein festes set ist, würd ich das einfach mit IN und den entsprechenden werten ersetzen, da ja alle werte bekannt sind.

zum index, ein index hilft nur beim select und update. beim insert wirkt sich ein index logischerweise negativ aus, weil man ja auch den index updaten muss. somit sollte man sich schon überlegen wo man welchen index und über welche spalten setzt und wie viele indexe man nutzt.

Ach IN ist so viel besser als NOT IN? Sagt das doch, dann kann ich es natürlich ganz leicht umschreiben - ich dachte, die Befehle sind beide verteufelt :)

Und dass man nicht mit Indices um sich werfen sollte ist mir auch bewusst. Es kommt immer auf das Verhältnis von Inserts und Updates/Selects an. Bei der user_items Tabelle habe ich mitlerweile 4 Indices, die Indextabellen sind genauso groß wie die Tabelle selbst. Aber den Geschwindigkeitsvorteil ist mir das allemal wert.

Kleines Beispiel zur verdeutlichung:

Select from Telefonbuch where Name NOT IN (lange Liste);

und

Select from Telefonbuch where Name IN ('Schmidt', 'Meier', 'Müller');

Da ein Telefonbuch in der Regel alphabetisch ist, wird der 2. Versuch schneller laufen.

Eine Datenbank kann im ersten Beispiel aber auch "cheaten". Der index ist meist ja ein B-Tree. Da kann die DB ab einer bestimmten Tiefe auch ganze Teile vom Baum weglassen und muss so nicht alles vergleichen.

Ob das auch so implementiert ist, ist die andere Frage.

Und ob der Query-Planner das dann auch so erkennt und anwendet ist auch nochmal ne ganz andere Geschichte. ;)

Um alles mögliche zu optimieren muss man sehr tief in der Materie stecken. Außerdem ist manchmal nicht immer alles schneller was logisch betrachtet schneller sein sollte.

Expertenblog: http://www.mysqlperformanceblog.com