Ändern von MyISAM Tabellen nach InnoDB und beheben der SELECT COUNT(*) Situation

Es ist ein bekanntes Problem, dass das Ändern der Storage Engine von MyISAM nach InnoDB Probleme verursachen kann [ 1 ], wenn Abfragen der folgenden Art auftreten:

SELECT COUNT(*) from table;

Glücklicherweise kommt dieser Typ von Abfragen selten vor. Und wenn, kann die Abfrage meist einfach weggelassen oder darum herum gearbeitet werden, indem man die Anzahl Zeilen in der Tabelle schätzt. Zum Beispiel mit:

SHOW TABLE STATUS LIKE 'test';

Aber in einigen seltenen Fällen braucht der Kunde diese Werte aus bestimmten Gründen wirklich exakt. Um die Ressourcen des Servers mit dieser Abfrage, welche in manchen Fällen sehr oft abgesetzt werden kann, nicht zu erschöpfen, nutzen wir die Materialized Views und/oder Shadow Tabellen-Technik [ 2 ].

Das folgende Beispiel zeigt auf, wie dies umgesetzt werden kann.

Unsere ursprüngliche Situation

Wir haben eine Tabelle mit Angeboten, welche durch ein Host-System befüllt wird:

CREATE TABLE offer (
  id   int unsigned NOT NULL AUTO_INCREMENT
, `type` CHAR(3) NOT NULL DEFAULT 'AAA'
, data varchar(64) DEFAULT NULL
, PRIMARY KEY (`id`)
, INDEX (type)
) ENGINE=InnoDB;

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

Die Abfrage, welche wir absetzen wollen, schaut wie folgt aus:

SELECT COUNT(*) FROM offer;

Diese Abfrage wird bei InnoDB sehr teuer, wenn Zillionen von Zeilen in der Tabelle sind.

Die Lösung des Problems

Um das Problem zu lösen, legen wir eine Zähler-Tabelle an, in welcher wir die Zeilen zählen, welche auf der Angebots-Tabelle eingefügt, geändert oder gelöscht werden.

CREATE TABLE counter (
  `type` char(3) NOT NULL DEFAULT 'AAA'
, `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, PRIMARY KEY (type)
) ENGINE=InnoDB;

Um unsere Zähler-Tabelle zu füllen, brauchen wir einen initialen Stand:

INSERT INTO counter
SELECT type, COUNT(*), NULL
  FROM offer
 GROUP BY type;

SELECT * FROM counter;
SELECT COUNT(*) FROM counter;

Unterhalt der Zähler-Tabelle

Um die Zähler-Tabelle aktuell zu halten, benötigen wir die 3 folgenden Trigger:

DROP TRIGGER IF EXISTS insert_offer_trigger;

delimiter //

CREATE TRIGGER insert_offer_trigger
AFTER INSERT ON offer FOR EACH ROW
BEGIN
  INSERT INTO counter
  VALUES (NEW.type, 1, NULL)
  ON DUPLICATE KEY
  UPDATE count = count + 1, ts = CURRENT_TIMESTAMP();
END;
//

delimiter ;


DROP TRIGGER IF EXISTS update_offer_trigger;

delimiter //

CREATE TRIGGER update_offer_trigger
AFTER UPDATE ON offer FOR EACH ROW
BEGIN
  IF NEW.type = OLD.type THEN
    UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type;
  ELSE
    UPDATE counter
       SET count = count - 1, ts = CURRENT_TIMESTAMP()
     WHERE type = OLD.type;
    INSERT INTO counter
    VALUES (NEW.type, 1, NULL)
    ON DUPLICATE KEY
    UPDATE count = count + 1, ts = CURRENT_TIMESTAMP();
  END IF;
END;
//

delimiter ;


DROP TRIGGER IF EXISTS delete_offer_trigger;

delimiter //

CREATE TRIGGER delete_offer_trigger
AFTER DELETE ON offer FOR EACH ROW
BEGIN
  UPDATE counter SET count = count - 1 WHERE type = OLD.type;
END;
//

delimiter ;

Jetzt können wir einige Fälle testen und die Resultate beider Tabellen vergleichen:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');

-- Single offer change
UPDATE offer SET data = 'Single offer change' WHERE id = 2;

-- Multi offer change
UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA';

-- Single offer delete
DELETE FROM offer WHERE id = 1;

-- REPLACE (= DELETE / INSERT)
REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace');

-- New type
INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla');

-- Change of type
UPDATE offer SET type = 'ZZZ' where id = 2;

-- Change of type to new type
UPDATE offer SET type = 'YYY' where id = 3;

-- INSERT on DUPLICATE KEY UPDATE
INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE')
ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY';
INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE')
ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE';

SELECT * FROM offer;
SELECT COUNT(*) FROM offer;
SELECT * FROM counter;
SELECT SUM(count) FROM counter;

Diese Lösung hat den Vorteil, dass wir für eine Abfrage nach der Anzahl Zeilen für einen bestimmten Bestellungs-Typ ebenfalls eine sehr schnelle Antwort erhalten. Diese Abfrage wäre auch für MyISAM Tabellen eine teure Operation...

SELECT `count` FROM counter WHERE `type` = 'DDD';