“SELECT COUNT (*)” est lent, même avec la clause where

J’essaie de comprendre comment optimiser une requête très lente dans MySQL (je ne l’ai pas conçue):

SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391'; +----------+ | COUNT(*) | +----------+ | 3224022 | +----------+ 1 row in set (1 min 0.16 sec) 

En comparant cela à un compte complet:

 select count(*) from change_event; +----------+ | count(*) | +----------+ | 6069102 | +----------+ 1 row in set (4.21 sec) 

La déclaration d’explication ne m’aide pas ici:

  explain SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: me type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 4120213 Extra: Using where; Using index 1 row in set (0.00 sec) 

OK, il pense toujours qu’il faut environ 4 millions d’entrées pour compter, mais je pourrais compter les lignes dans un fichier plus vite que ça! Je ne comprends pas pourquoi MySQL prend autant de temps.

Voici la définition de la table:

 CREATE TABLE `change_event` ( `change_event_id` bigint(20) NOT NULL default '0', `timestamp` datetime NOT NULL, `change_type` enum('create','update','delete','noop') default NULL, `changed_object_type` enum('Brand','Broadcast','Episode','OnDemand') NOT NULL, `changed_object_id` varchar(255) default NULL, `changed_object_modified` datetime NOT NULL default '1000-01-01 00:00:00', `modified` datetime NOT NULL default '1000-01-01 00:00:00', `created` datetime NOT NULL default '1000-01-01 00:00:00', `pid` char(15) default NULL, `episode_pid` char(15) default NULL, `import_id` int(11) NOT NULL, `status` enum('success','failure') NOT NULL, `xml_diff` text, `node_digest` char(32) default NULL, PRIMARY KEY (`change_event_id`), KEY `idx_change_events_changed_object_id` (`changed_object_id`), KEY `idx_change_events_episode_pid` (`episode_pid`), KEY `fk_import_id` (`import_id`), KEY `idx_change_event_timestamp_ce_id` (`timestamp`,`change_event_id`), KEY `idx_change_event_status` (`status`), CONSTRAINT `fk_change_event_import` FOREIGN KEY (`import_id`) REFERENCES `import` (`import_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Version:

 $ mysql --version mysql Ver 14.12 Dissortingb 5.0.37, for pc-solaris2.8 (i386) using readline 5.0 

Y a-t-il quelque chose d’évident qui me manque? (Oui, j’ai déjà essayé “SELECT COUNT (change_event_id)”, mais il n’y a pas de différence de performance).

InnoDB utilise des clés primaires en cluster, de sorte que la clé primaire est stockée avec la ligne dans les pages de données, et non dans des pages d’index distinctes. Pour effectuer une parsing de distance, vous devez toujours parcourir toutes les lignes potentiellement étendues des pages de données. Notez que cette table contient une colonne TEXT.

Deux choses que je voudrais essayer:

  1. exécuter la optimize table . Cela garantira que les pages de données sont stockées physiquement dans l’ordre sortingé. Cela pourrait accélérer un balayage de distance sur une clé primaire en cluster.
  2. créer un index non primaire supplémentaire sur la colonne change_event_id uniquement. Cela stockera une copie de cette colonne dans des pages d’index qui seront beaucoup plus rapides à numériser. Après l’avoir créé, vérifiez le plan d’explication pour vous assurer qu’il utilise le nouvel index.

(vous souhaiterez probablement aussi que la colonne change_event_id bigint soit non signée si elle est incrémentée à partir de zéro)

Voici quelques choses que je suggère:

  • Changez la colonne d’un “bigint” à un “int unsigned”. Vous attendez-vous vraiment à avoir plus de 4,2 milliards d’enregistrements dans ce tableau? Sinon, vous perdez de la place (et du temps) dans le champ extra large. Les index MySQL sont plus efficaces sur les types de données plus petits.

  • Exécutez la commande ” OPTIMIZE TABLE ” et vérifiez si votre requête est plus rapide par la suite.

  • Vous pouvez également envisager de partitionner votre table en fonction du champ ID, en particulier si les anciens enregistrements (avec des valeurs d’ID inférieures) deviennent moins pertinents au fil du temps. Une table partitionnée peut souvent exécuter des requêtes agrégées plus rapidement qu’une énorme table non partitionnée.


MODIFIER:

En regardant de plus près cette table, cela ressemble à une table de style de journalisation, où les lignes sont insérées mais jamais modifiées.

Si cela est vrai, alors vous pourriez ne pas avoir besoin de toute la sécurité transactionnelle fournie par le moteur de stockage InnoDB, et vous pourrez peut-être passer à MyISAM , ce qui est beaucoup plus efficace sur les requêtes agrégées.

Je me suis déjà heurté à un comportement similaire avec les bases de données de géolocalisation IP. Passé un certain nombre d’enregistrements, la capacité de MySQL à tirer parti des index pour les requêtes basées sur les plages s’évapore apparemment. Avec les bases de données de géolocalisation, nous les avons traitées en segmentant les données en morceaux suffisamment raisonnables pour permettre l’utilisation des index.

Vérifiez la fragmentation de vos index. Dans mon entreprise, nous avons un processus d’importation nocturne qui détruit nos index et, avec le temps, il peut avoir un impact considérable sur la vitesse d’access aux données. Par exemple, une procédure SQL a duré 2 heures le lendemain de la fragmentation des index en 3 minutes. Nous utilisons SQL Server 2005 pour rechercher un script capable de vérifier cela sur MySQL.

Mise à jour: Découvrez ce lien: http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

Exécutez ” analyze table_name ” sur cette table – il est possible que les index ne soient plus optimaux.

Vous pouvez souvent le dire en lançant ” show index from table_name “. Si la valeur de cardinalité est NULL vous devez forcer une nouvelle parsing.

MySQL dit d’abord “Using where”, car il doit lire tous les enregistrements / valeurs des données d’index pour les compter. Avec InnoDb, il tente également de “saisir” cette plage de 4 mil pour la compter.

Vous devrez peut-être expérimenter différents niveaux d’isolation de transaction: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-uncommitted

et voir lequel est le meilleur.

Avec MyISAM, ce serait juste rapide, mais avec un modèle d’écriture intensif, il en résulterait des problèmes de locking.

Je créerais une table “counters” et appendais les déclencheurs “create row” / “delete row” à la table que vous comptez. Les déclencheurs doivent augmenter / diminuer les valeurs de comptage dans la table “counters” sur chaque insertion / suppression, vous n’avez donc pas besoin de les calculer chaque fois que vous en avez besoin.

Vous pouvez également accomplir cela du côté de l’application en mettant en cache les compteurs, mais cela impliquera l’effacement du “cache du compteur” à chaque insertion / suppression.

Pour quelques références, jetez un coup d’œil à cette http://pure.rednoize.com/2007/04/03/mysql-performance-use-counter-tables/