La manipulation de très grandes données avec mysql

Désolé pour le long post!

J'ai une base de données contenant environ 30 tables (moteur InnoDB). Seulement deux de ces tableaux, à savoir, "transaction" et "shift" sont assez grandes (le premier de 1,5 million de lignes et maj a 23k lignes). Maintenant tout fonctionne très bien et je n'ai pas de problème avec la base de données actuelle de la taille.

Cependant, nous aurons une base de données similaire (mêmes types de données, la conception ,..) mais en beaucoup plus grand, par exemple, la "transaction" table sur 1 milliard d'enregistrements (environ 2,3 millions de transactions par jour) et nous réfléchissons à la façon dont nous devrions traiter avec un tel volume de données MySQL? (il est à la fois en lecture et en écriture intensive). J'ai lu beaucoup de postes connexes pour voir si Mysql (et plus spécifiquement le moteur InnoDB) peut effectuer bien avec des milliards d'enregistrements, mais je n'ai encore quelques questions. Certains de ces related posts que j'ai lu sont les suivantes:

• Pouvez MySQL raisonnablement effectuer des requêtes sur des milliards de lignes?
• Est InnoDB (MySQL 5.5.8) le bon choix pour plusieurs milliards de lignes?
• Le meilleur magasin de données pour des milliards de lignes
• Grand comment une base de données MySQL obtenir avant la performance commence à se dégrader
• Pourquoi MySQL peut être très lente avec des grandes tables?
• Peut gérer des tables Mysql qui pourra contenir environ 300 millions de disques?

Ce que j'ai compris jusqu'à présent pour améliorer les performances de très grandes tables:

1. (pour les tables innoDB qui est mon cas) l'augmentation de la innodb_buffer_pool_size (par exemple, jusqu'à 80% de la RAM).
   Aussi, j'ai trouvé quelques autres performances MySQL modulable paramètres ici, dans
   percona blog
2. avoir un bon index sur la table (à l'aide de EXPLAN sur des requêtes)
3. partitionnement de la table
4. MySQL de Fragmentation ou de clustering

Voici mes questions/confusions:

• Sur le partitionnement, j'ai quelques doutes à savoir si nous devrions ou non de l'utiliser. D'une part, beaucoup de gens ont suggéré afin d'améliorer les performances lors de la table est très grande. D'autre part, j'ai lu de nombreux messages disant qu'il n'a pas d'améliorer les performances des requêtes et cela ne fait pas de requêtes de courir plus vite (par exemple, ici et ici). Aussi, j'ai lu dans Manuel De Référence De MySQL que InnoDB clés étrangères et MySQL partitionnement ne sont pas compatibles (nous avons les clés étrangères).

• Concernant l'index, maintenant ils sont bien, mais que j'ai compris, pour les très grandes tables d'indexation est plus restrictive (comme Kevin Bedell mentionné dans sa réponse ici). En outre, les indices de vitesse jusqu'lit tout ralentir écrire (insert/update). Donc, pour le nouveau projet similaire que nous aurons cette grande DB, devrions-nous d'abord insérer/charger toutes les données, puis créer des index? (pour accélérer l'insertion)

• Si nous ne pouvons pas utiliser le partitionnement pour notre grande table ("transaction" de la table), ce qui est une autre option pour améliorer les performances? (à l'exception de MySQl variable de paramètres tels que innodb_buffer_pool_size). Devrions-nous utiliser Mysql clusters? (nous avons aussi beaucoup de jointures)

MODIFIER

C'est le show create table déclaration pour notre plus grand table nommée "opération":

  CREATE TABLE `transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`terminal_transaction_id` int(11) NOT NULL,
`fuel_terminal_id` int(11) NOT NULL,
`fuel_terminal_serial` int(11) NOT NULL,
`xboard_id` int(11) NOT NULL,
`gas_station_id` int(11) NOT NULL,
`operator_id` text NOT NULL,
`shift_id` int(11) NOT NULL,
`xboard_total_counter` int(11) NOT NULL,
`fuel_type` int(11) NOT NULL,
`start_fuel_time` int(11) NOT NULL,
`end_fuel_time` int(11) DEFAULT NULL,
`preset_amount` int(11) NOT NULL,
`actual_amount` int(11) DEFAULT NULL,
`fuel_cost` int(11) DEFAULT NULL,
`payment_cost` int(11) DEFAULT NULL,
`purchase_type` int(11) NOT NULL,
`payment_ref_id` text,
`unit_fuel_price` int(11) NOT NULL,
`fuel_status_id` int(11) DEFAULT NULL,
`fuel_mode_id` int(11) NOT NULL,
`payment_result` int(11) NOT NULL,
`card_pan` text,
`state` int(11) DEFAULT NULL,
`totalizer` int(11) NOT NULL DEFAULT '0',
`shift_start_time` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE,
KEY `start_fuel_time_idx` (`start_fuel_time`),
KEY `fuel_terminal_idx` (`fuel_terminal_id`),
KEY `xboard_idx` (`xboard_id`),
KEY `gas_station_id` (`gas_station_id`) USING BTREE,
KEY `purchase_type` (`purchase_type`) USING BTREE,
KEY `shift_start_time` (`shift_start_time`) USING BTREE,
KEY `fuel_type` (`fuel_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Merci pour votre temps,

OriginalL'auteur mOna | 2016-09-26