Wie optimieren COUNT(*) performance auf InnoDB durch die Verwendung von index

Ich habe eine ziemlich große, aber schmale InnoDB-Tabelle mit ~9m records. Dabei count(*) oder count(id) auf dem Tisch ist extrem langsam (6+ Sekunden):

DROP TABLE IF EXISTS `perf2`;

CREATE TABLE `perf2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ts_uniq` (`channel_id`,`timestamp`),
  KEY `IDX_CHANNEL_ID` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

RESET QUERY CACHE;
SELECT COUNT(*) FROM perf2;

Während die Anweisung wird nicht ausgeführt, zu oft wäre es schön, es zu optimieren. Nach http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ dies sollte möglich sein, durch erzwingen einer InnoDB einen index:

SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);

Den explain plan scheint in Ordnung zu sein:

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      perf2   index   NULL            PRIMARY 4       NULL    8906459 Using index

Leider die Aussage ist so langsam wie vorher. Nach "SELECT COUNT(*)" ist langsam, sogar mit where-Klausel ich habe auch versucht die Optimierung die Tabelle ohne Erfolg.

Was/ist/wieder eine Möglichkeit zur Optimierung der COUNT(*) Leistung auf InnoDB?

Wechsel zu MyISAM, kann Wunder tun - und es kostet nur ein einzelnes klicken in PHPMyAdmin .)
Auch die Kosten der Durchsetzung von Fremdschlüsseln und Transaktionen verwenden.
jetzt begreife ich, ich dachte du meintest Transaktionen oder foreign keys waren obligatorisch für MyISAM -, das glaube ich nicht. Missverstanden "erzwingen"
wahr, aber nicht die Frage, die gestellt wird :/
Der index für channel_id ist redundant mit der beginnend mit Spalte; drop die ehemaligen.

InformationsquelleAutor andig | 2013-10-09

innodb mysql

17

Zur Zeit habe ich das problem gelöst, indem mit Hilfe dieser Näherung:
```
EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)
```
Die Ungefähre Anzahl der Zeilen, die gelesen werden können von der rows Spalte die explain-plan bei der Verwendung von InnoDB-wie oben gezeigt. Bei der Verwendung von MyISAM-dies bleibt so LEER wie der Tisch Referenz-isbeing optimierte Weg - also, wenn leer, fallback auf traditionelle SELECT COUNT statt.

Denken Sie daran, dass die "Annäherung" hier ist weit von genauen, und für eine Tabelle mit 1M rows zurückgeben könnte irgendwo zwischen 100K und 10M. SHOW TABLE STATUS ist ähnlich. Es ist wirklich unzuverlässig. Ich denke, dass viele der performance-Probleme behoben werden, die MySQL 5.7, obwohl.
Diese Näherung ist so unglaublich unzuverlässig, ich kann nicht denken Sie an eine situation, wo ich es verwenden. Für eine große Tabelle, ich würde eher führen Sie die vollständige COUNT(*) regelmäßigen Abständen und verwenden Sie diesen Wert, bis die nächste Zählung.
Die Auto_increment Feld innerhalb SHOW TABLE STATUS ist viel genauer und zählen Sie die gesamte Tabelle, mit viel größerer Geschwindigkeit.
seien Sie vorsichtig, wenn es wurden Datensätze aus der Tabelle gelöscht dann Auto_increment wird eine über-Schätzung.

InformationsquelleAutor andig
14

Seit MySQL 5.1.6 können Sie die Event Scheduler und legen Sie die Anzahl auf ein Statistik-Tabelle regelmäßig.

Erstellen Sie zuerst eine Tabelle zu halten, die zählen:
```
CREATE TABLE stats (
`key` varchar(50) NOT NULL PRIMARY KEY,
`value` varchar(100) NOT NULL);
```
Dann ein Ereignis erstellen, um die Tabelle zu aktualisieren:
```
CREATE EVENT update_stats
ON SCHEDULE
  EVERY 5 MINUTE
DO
  INSERT INTO stats (`key`, `value`)
  VALUES ('data_count', (select count(id) from data))
  ON DUPLICATE KEY UPDATE value=VALUES(value);
```
Es ist nicht perfekt, aber es bietet eine in sich geschlossene Lösung (kein cronjob oder Warteschlange), die leicht zugeschnitten zu laufen, so oft wie die erforderliche frische des Grafen.

Wusste nicht, über die Ereignisse, ich danke Ihnen. Wäre immer noch ein killer für meinen kleinen RasPi läuft die DB 🙁
Das ist schön und nützlich

InformationsquelleAutor Che

Basierend auf @Che-code, Sie können auch Trigger verwenden, auf EINFÜGEN und auf UPDATE zu perf2, um den Wert in der Statistik-Tabelle auf dem neuesten Stand.

CREATE TRIGGER `count_up` AFTER INSERT ON `perf2` FOR EACH ROW UPDATE `stats`
SET 
  `stats`.`value` = `stats`.`value` + 1 
WHERE
  `stats`.`key` = "perf2_count";

CREATE TRIGGER `count_down` AFTER DELETE ON `perf2` FOR EACH ROW UPDATE `stats`
SET 
  `stats`.`value` = `stats`.`value` - 1 
WHERE
  `stats`.`key` = "perf2_count";

Dies hätte den Vorteil, dass das performance-Problem der Durchführung einer count(*) und würde nur ausgeführt werden, wenn änderungen der Daten in der Tabelle perf2

InformationsquelleAutor MQuirion

-4

select max(id) - min(id) from xxx_table where ....

Diese verwenden "Auswählen von Tabellen optimiert away", und ist sehr schnell!!!

Hinweis max(id) - min(id) ist tatsächlich größer als count(1).

InformationsquelleAutor newbee

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.