InnoDB und große Tabellen

Vor kurzem hatte ich die Aufgabe, ein Gewinnspiel umzusetzen, bei dem User sich pro E-Mail-Adresse einen Gewinncode zurückgeben lassen konnten. Die 7 Millionen Gewinncodes kamen dabei vom Kunden und waren alle unique. Jeder Code durfte dabei nur einmal ausgespielt werden. Um das sicherzustellen kamen nur Transaktionen in Frage. Somit war InnoDB Pflicht.

Ok, als erstes hab ich die beiden Tabellen erstellt:

--
-- Tabellenstruktur für Tabelle `codes`
--
CREATE TABLE `codes` (
`code` char(6) character set latin1 collate latin1_bin NOT NULL,
`user_id` int(11) default NULL,
PRIMARY KEY (`code`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;


-- --------------------------------------------------------


--
-- Tabellenstruktur für Tabelle `users`
--


CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`salutation` set('Herr','Frau') NOT NULL,
`firstname` char(50) NOT NULL,
`lastname` char(50) NOT NULL,
`street` char(100) NOT NULL,
`hnr` char(20) NOT NULL,
`zip` char(10) NOT NULL,
`city` char(100) NOT NULL,
`email` char(100) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=1;

Problem 1: Wie importiert man 7 Millionen Datensätze möglichst schnell?

Einzelne Queries sind langsam. Mehrere Queries per Transaktion zu bündeln ist ein wenig schneller. Das schnellste aber scheint LOAD DATA zu sein, welches auch über das Shell-Tool mysqlimport zu verwenden ist. Da man aber bei mysqlimport keinen Fortschritt ausgeworfen bekommt, hab ich fix ein Shell-Skript zusammengeschrieben, welches alle 10.000 Zeilen eine Meldung auswirft.

$data_file = '/tmp/codes.txt';
$db = 'otto_paf_mai2009';
$chunk_file = '/tmp/codes.part';


# split the file into small chunks to output a status report
$line = 0;
$buffer = '';
$handle = fopen ($data_file, 'r');
while (!feof($handle))
{
$line++;
$buffer .= fgets($handle);


if ($line%10000 === 0)
{
file_put_contents($chunk_file, $buffer);
$buffer = '';


# the data file has to have the same name as the table
# it has to be readable by the mysql server (especially the directory)
# mysqlimport [options] [db_name] [data_file]
shell_exec('mysqlimport -uroot --columns=code --verbose '.$db.' '.$chunk_file);


stdout($result);
stdout(number_format($line, 0, ',', '.'));
}


}
fclose ($handle);


function stdout($out){fwrite(STDOUT, $out."\n");}
function stderr($out){fwrite(STDERR, $out."\n");}


Interessant war hierbei schon, wie lange MySQL dafür braucht und wie tödlich ein Unique-Key (in diesem Fall der PRIMARY) sein kann. Der Import in MyISAM ohne Unique-Key dauerte eine halbe Minute. Der Import in InnoDB ohne Unique-Key etwa 5 Minuten. Und der Import in InnoDB MIT dem Unique-Key dauerte letztendlich 5,5 Stunden. Autsch! Allerdings war es ganz gut, dass ich den Unique-Key gesetzt habe, denn bei den ersten Daten des Kunden waren tatsächlich Dubletten in den Codes.

Problem 2: Wie gibt man einen zufälligen Code aus?

Der offensichtlichste und verbreitetste Ansatz ist:

SELECT code
FROM codes
WHERE user_id IS NULL
ORDER BY RAND()
LIMIT 1

Dauert aber leider schon bei 600.000 Datensätzen über eine halbe Sekunde. Und das EXPLAIN macht alles klar. In der Spalte "extra" steht:

Using where; Using index; Using temporary; Using filesort

Das sieht übel aus.

Mein nächster Ansatz war, das ganze in zwei Abfragen auszulagern. Die erste sollte sich die Anzahl der gültigen Codes zurückliefern, die zweite schließlich mit einem zufälligen Offset beim LIMIT arbeiten, der halt zwischen 0 und der Anzahl der gültigen Codes liegen sollte. Das sah etwa so aus:

// get count of codes
$sql = $this->db->result("
SELECT count(*) as count
FROM codes
WHERE user_id IS NULL
");


$offset = rand(0, $sql['RESULT'][0]['count']);


// get code from db
$sql = $this-db->Result("
SELECT code
FROM codes
WHERE user_id IS NULL
LIMIT $offset, 1
");
$code = $sql['RESULT'][0]['code'];

Schon die erste Abfrage dauerte im Schnitt 1,7 Sekunden. Aber warum? Die Antwort findet sich hier: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/
Ein COUNT(*) auf viele Datensätze ist bei InnoDB einfach höllisch langsam. Die nächste Idee war hierbei, die erste Abfrage umzudrehen. Da ich nämlich weiß, wieviele Keys in der DB sind, brauche ich nur die user_ids zählen und von der Gesamtzahl abzuziehen. Das war auch tatsächlich sehr schnell. Selbst die zweite Abfrage...

ABER HALT...

... anscheinend nur bei der ersten Abfrage. Irgendwie schwanken die Zeiten zwischen 0,0003 Sekunden und 2,5 Sekunden. Nach ein bisschen Herumprobieren kam auch hier der Übertäter zum Vorschein: Das LIMIT. Ist der Offset klein, kommt das Ergebnis sehr schnell, ist er groß, dauert es ewig lang. Mist.

Mein derzeitiger Ansatz ist mehr ein Workaround. Da ich weiß, dass die Codes mit einem Klein- oder Großbuchstaben anfangen, wähle ich einfach einen aus und lasse mir nur Codes zurückgeben, die halt diesen am Anfang stehen haben:

$values = array_merge( range('a', 'z'), range('A', 'Z') );
$char = $values[ rand(0, count($values)-1 ) ];


$sql = $this-db->Result("
SELECT code
FROM codes
FORCE INDEX(PRIMARY)
WHERE code LIKE '".$char."%'
AND user_id IS NULL
LIMIT 1
");

Interessant war hierbei das FORCE INDEX. Ohne wollte MySQL lieber den Index für die user_id verwenden als den PRIMARY KEY. Verrückt. Allerdings ist die endgültige Lösung mit 0,007 Sekunden recht schnell. Allerdings bekomme ich nicht wirklich zufällige Ergebnisse heraus.

Wer also eine bessere Lösung kennt oder eine andere Idee hat: Immer her damit!