Хранение изменений на объектах: Является ли MySQL правильным решением?

Question

Хранение изменений на объектах: Является ли MySQL правильным решением?

Я хочу сохранить изменения, которые я делаю, в моей таблице "сущностей". Это должно быть похоже на бревно. В настоящее время это реализовано с помощью этой таблицы в MySQL:

CREATE TABLE `entitychange` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `entity_id` int(10) unsigned NOT NULL,
  `entitytype` enum('STRING_1','STRING_2','SOMEBOOL','SOMEDOUBLE','SOMETIMESTAMP') NOT NULL DEFAULT 'STRING_1',
  `when` TIMESTAMP NOT NULL,
  `value` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

entity_id = первичный ключ моего entity Таблица.
entitytype = поле, которое было изменено в entity Таблица. иногда меняется только одно поле, иногда несколько. одно изменение = одна строка.
value = строковое представление "нового значения" поля.

Пример при изменении поля entity.somedouble с 3 до 2, я запускаю эти запросы:

UPDATE entity SET somedouble = 2 WHERE entity_id = 123;
INSERT INTO entitychange (entity_id,entitytype,value) VALUES (123,'SOMEDOUBLE',2);

Мне необходимо select изменения конкретной сущности и типа сущности за последние 15 дней. Например: последнее меняется с SOMEDOUBLE для entity_id 123 в течение последних 15 дней.

Теперь есть две вещи, которые мне не нравятся:

Все данные хранятся как TEXT - хотя большинство (менее 1%) на самом деле не текстовые, в моем случае большинство значений DOUBLE, Это большая проблема?
Таблица становится очень, очень медленной при вставке, так как таблица уже содержит 200 миллионов строк. В настоящее время нагрузка на мой сервер составляет до 10-15 из-за этого.

Мой вопрос: как мне решить эти два "узких места"? Мне нужно масштабировать.

Мои подходы были бы:

Сохраните его так: http://sqlfiddle.com/ (нажмите для просмотра) - Сохраните изменения в entitychange таблицу, а затем сохранить значение в соответствии с его типом данных в entitychange_[bool|timestamp|double|string]
Использовать разбиение по HASH(entity_id) - Я думал о ~50 разделах.
Должен ли я использовать другую систему баз данных, может быть MongoDB?

11

mysql database-design database-partitioning entity-attribute-value temporal-database

Источник

user944131 11 июн '13 в 11:15

8 ответов

Другие вопросы по тегам mysql database-design database-partitioning entity-attribute-value temporal-database

user2039695 15 июн '13 в 21:02 2013-06-15 21:02 · Answer 1 · 2013-06-15 21:02

Если бы я столкнулся с упомянутой вами проблемой, я бы разработал таблицу LOG следующим образом:

EntityName: (String) Объект, которым манипулируют. (Обязательно)
ObjectId: Объект, которым манипулируют, первичный ключ.
FieldName: (Строка) Имя поля сущности.
OldValue: (String) Старое значение поля сущности.
NewValue: (String) Поле сущности новое значение.
UserCode: Уникальный идентификатор пользователя приложения. (обязательный)
TransactionCode: Любая операция, изменяющая сущности, должна иметь уникальный код транзакции (например, GUID) (обязательно),
В случае обновления объекта, изменяющего несколько полей, эти столбцы будут ключевой точкой для отслеживания всех изменений в обновлении (транскрипция).
ChangeDate: Дата сделки. (обязательный)
FieldType: перечисление или текст, показывающий тип поля, например, TEXT или Double. (обязательный)

Имея такой подход
Любая сущность (таблица) может быть отслежена
Отчеты будут доступны для чтения
Только изменения будут зарегистрированы.
Код транзакции будет ключевым моментом для обнаружения изменений одним действием.

КСТАТИ

Store the changes in the entitychange table and then store the value 
according to its datatype in entitychange_[bool|timestamp|double|string]

Не понадобится, в одной таблице будут изменения и типы данных

Use partitioning by HASH(entity_id)

Я предпочитаю разделение по ChangeDate или создание резервных таблиц для changeDate, которые достаточно стары для резервного копирования и удаления из основной таблицы LOG

Should I use another database system, maybe MongoDB?

Любая база данных имеет свои проблемы и недостатки, вы можете использовать дизайн на любой RDBMS. Полезное сравнение основанных на документах баз данных, таких как MongoDB, можно найти здесь

надеюсь быть полезным.

user921456 11 июн '13 в 16:29 2013-06-11 16:29 · Answer 2 · 2013-06-11 16:29

Теперь я думаю, что понимаю, что вам нужно, измененная таблица версий с историей записей. Это может быть еще одним способом достижения того же самого, и вы можете легко провести несколько быстрых тестов, чтобы увидеть, дает ли это вам лучшую производительность, чем ваше текущее решение. Это то, как Symfony PHP Framework делает это в Doctrine с плагином Versionable.
Имейте в виду, что существует первичный ключ, уникальный индекс двух ключей, version и fk_entity.
Также взгляните на сохраненные значения. Вы сохраните значение 0 в полях, которые не изменились, и измененное значение в тех, кто изменился.

CREATE TABLE `entity_versionable` (
  `version` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `fk_entity` INT(10) UNSIGNED NOT NULL,
  `str1` VARCHAR(255),
  `str2` VARCHAR(255),
  `bool1` BOOLEAN,
  `double1` DOUBLE,
  `date` TIMESTAMP NOT NULL,
  PRIMARY KEY (`version`,`fk_entity`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;


INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "a1", "0", "0", "0", "2013-06-02 17:13:16");
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "a2", "0", "0", "0", "2013-06-11 17:13:12");
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "0", "b1", "0", "0", "2013-06-11 17:13:21");
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "0", "b2", "0", "0", "2013-06-11 17:13:42");
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "0", "0", "1", "0", "2013-06-16 17:19:31");

/*Another example*/
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
VALUES ("1", "a1", "b1", "0", "0", CURRENT_TIMESTAMP);


SELECT * FROM `entity_versionable` t WHERE 
(
    (t.`fk_entity`="1") AND 
    (t.`date` >= (CURDATE() - INTERVAL 15 DAY))
);

И, возможно, еще один шаг для повышения производительности, это может быть сохранение всех записей журнала истории в отдельных таблицах, один раз в месяц или около того. Таким образом, у вас не будет много записей в каждой таблице, и поиск по дате будет очень быстрым.

user2409965 14 июн '13 в 22:08 2013-06-14 22:08 · Answer 3 · 2013-06-14 22:08

Здесь есть две основные проблемы:

Как эффективно хранить данные, т. Е. Занимать меньше места и быть в удобном формате

2-3. Управление большой таблицей: архивация, простота резервного копирования и восстановления

2-3. Оптимизация производительности: быстрее вставляет и выбирает

Эффективное хранение данных

value поданной. Я бы предложил сделать это VARCHAR (N), Причины:
- Использование N<255 сэкономит 1 байт на строку только из-за типа данных.
- Используя другие типы данных для этого поля: фиксированные типы используют пространство независимо от значения, и обычно оно будет 8 байтов в строке (datetime, long integer, char (8)), а другие типы данных переменных слишком велики для этого поля.
- Также TEXT тип данных приводит к снижению производительности: (из-за ошибок при использовании типов данных BLOB и Text)

Экземпляры TEXT Столбцы в результате запроса, который обрабатывается с использованием временной таблицы, заставляют сервер использовать таблицу на диске, а не в памяти, потому что механизм хранения MEMORY не поддерживает эти типы данных. Использование диска влечет за собой снижение производительности, поэтому включайте столбцы BLOB или TEXT в результат запроса, только если они действительно необходимы. Например, избегайте использования SELECT *, который выбирает все столбцы.
Каждое значение BLOB или TEXT представляется внутри отдельно выделенным объектом. Это отличается от всех других типов данных, для которых хранилище выделяется один раз на столбец при открытии таблицы.

В принципе TEXT предназначен для хранения больших строк и фрагментов текста, тогда как VARCHAR() разработан относительно короткие строки.

id поле. (обновлено, спасибо @steve) Я согласен, что в этом поле нет полезной информации. Используйте 3 столбца для вашего первичного ключа: entity_id а также entitype а также when, TIMESTAMP гарантированно, что дубликатов не будет. Также те же столбцы будут использоваться для разбиения / разбиения.

Управление таблицами Существует два основных варианта: таблицы MERGE и разбиение на разделы. Механизм хранения MERGE основан на My_ISAM, который, насколько я понимаю, постепенно прекращается. Вот некоторые материалы о [MERGE Storage Engine]. 2

Основным инструментом является Секционирование, и он предоставляет два основных преимущества: 1. Переключение секций (которое часто является мгновенной операцией с большим объемом данных) и сценарий скользящего окна: вставьте новые данные в одну таблицу, а затем мгновенно переключите все их в таблицу архива. 2. Хранение данных в отсортированном порядке, что позволяет сокращать разделы - запрашивать только те разделы, которые содержат необходимые данные. MySQL позволяет разделить данные на группы.

Разделение по entity_id имеет смысл. Если вам нужно запрашивать данные в течение длительных периодов времени или у вас есть другой шаблон в запросах к вашей таблице - используйте этот столбец для разделения. Нет необходимости подразделять на все столбцы первичного ключа, если только разделы не будут переключены на этом уровне.

Количество разделов зависит от того, насколько велик размер файла базы данных для этого раздела. Количество подразделов зависит от количества ядер, поэтому каждое ядро может искать свой собственный раздел, N-1 подразделов должно быть в порядке, поэтому одно ядро может выполнять общую работу по координации.

оптимизация

Вставки:

Вставки выполняются быстрее в таблицу без индексов, поэтому вставьте большой кусок данных (выполните обновления), а затем создайте индексы (если это возможно).
+ Изменить Text за Varchar - это займет некоторое напряжение от двигателя дб
Может помочь минимальная регистрация и блокировки таблиц, но не часто можно использовать

Выбор:

Text в Varchar должно определенно улучшить вещи.
Иметь текущую таблицу с последними данными - последние 15 дней, затем перейти к архиву с помощью переключения разделов. Здесь у вас есть возможность разделить таблицу разделов, отличную от архивной таблицы (например, сначала по дате, затем entity_id), и изменить способ разделения, переместив небольшие (1 день) данные во временную таблицу и изменив ее разделение.

Также вы можете рассмотреть разбиение по дате, у вас много запросов по диапазонам дат. Сначала используйте ваши данные и их части, а затем решите, какая схема будет поддерживать их лучше всего.

А что касается вашего 3-го вопроса, я не вижу, как использование MongoDB конкретно поможет в этой ситуации.

user20860 16 июн '13 в 11:48 2013-06-16 11:48 · Answer 4 · 2013-06-16 11:48

Это называется временной базой данных, и исследователи уже более 20 лет пытаются найти лучший способ хранения и запроса временных данных.

Попытка сохранить данные EAV, как вы делаете, неэффективна, поскольку хранение числовых данных в столбце TEXT занимает много места, и ваша таблица становится все длиннее и длиннее, как вы обнаружили.

Другой вариант, который иногда называют шестой нормальной формой (хотя для 6NF существует несколько несвязанных определений), - это сохранение дополнительной таблицы для хранения изменений для каждого столбца, который вы хотите отслеживать во времени. Это похоже на решение, представленное ответом @xtrm, но для этого не нужно хранить избыточные копии столбцов, которые не изменились. Но это приводит к взрыву в количестве таблиц.

Я начал читать о Anchor Modeling, которая обещает обрабатывать временные изменения как структуры, так и контента. Но я недостаточно хорошо понимаю, чтобы объяснить это. Я просто сошлюсь на это, и, возможно, это будет иметь смысл для вас.

Вот пара книг, которые содержат обсуждения временных баз данных:

SQL Джо Селко для умников, 4-е изд.
Временные данные и реляционная модель, CJ Date, Хью Дарвен, Никос Лоренцос

user1446005 16 июн '13 в 22:36 2013-06-16 22:36 · Answer 5 · 2013-06-16 22:36

Хранение целого числа в TEXT колонка не пойдет! TEXT это самый дорогой тип.

Я бы зашел так далеко, что создал бы одну таблицу журналов для каждого поля, которое вы хотите отслеживать:

CREATE TABLE entitychange_somestring (
    entity_id INT NOT NULL PRIMARY KEY,
    ts TIMESTAMP NOT NULL,
    newvalue VARCHAR(50) NOT NULL, -- same type as entity.somestring
    KEY(entity_id, ts)
) ENGINE=MyISAM;

Разбейте их, действительно.

Обратите внимание, я рекомендую использовать MyISAM двигатель. Вам не нужны транзакции для этой (этих) неограниченных таблиц только для вставки.

user2097290 17 июн '13 в 20:20 2013-06-17 20:20 · Answer 6 · 2013-06-17 20:20

Почему ВСТАВКА такая медленная, и что вы можете сделать, чтобы сделать это быстрее.

Вот вещи, на которые я бы посмотрел (и примерно в том порядке, в котором я бы их проработал):

Создание нового идентификатора AUTO_INCREMENT и вставка его в первичный ключ требует блокировки (в InnoDB есть специальная блокировка AUTO-INC, которая удерживается до завершения оператора, эффективно действуя как блокировка таблицы в вашем сценарии). Обычно это не проблема, поскольку это относительно быстрая операция, но, с другой стороны, при значении нагрузки (Unix) от 10 до 15 вы, скорее всего, будете иметь процессы, ожидающие снятия этой блокировки. Из предоставленной вами информации я не вижу никакой пользы в вашем суррогатном ключе "id". Посмотрите, значительно ли снижает производительность этот столбец. (Кстати, нет правила, что для таблицы нужен первичный ключ. Если у вас его нет, это нормально)
InnoDB может быть относительно дорогим для INSERT. Это компромисс, сделанный для предоставления дополнительных функциональных возможностей, таких как транзакции, который может повлиять или не повлиять на вас. Поскольку все ваши действия являются атомарными, я не вижу необходимости в транзакциях. Тем не менее, дать MyISAM попробовать. Примечание: MyISAM, как правило, является плохим выбором для больших таблиц, поскольку он поддерживает только блокировку таблиц, а не блокировку на уровне записи, но поддерживает параллельные вставки, так что это может быть выбор здесь (особенно если вы отбрасываете первичный ключ, см. Выше)
Вы можете играть с параметрами механизма хранения базы данных. И InnoDB, и MyISAM имеют опции, которые вы можете изменить. Некоторые из них влияют на то, как на самом деле хранятся данные TEXT, другие имеют более широкую функцию. Один из них, на который вы должны обратить особое внимание, это innodb_flush_log_at_trx_commit.
Столбцы TEXT относительно дороги, если (и только если) они имеют ненулевые значения. В настоящее время вы храните все значения в этом столбце ТЕКСТ. Стоит попробовать следующее: добавить дополнительные поля value_int а также value_double к вашей таблице и сохраните эти значения в соответствующем столбце. Да, это приведет к потере некоторого дополнительного места, но может быть быстрее - но это будет в значительной степени зависеть от механизма хранения базы данных и его настроек. Обратите внимание, что многое из того, что люди думают о производительности колонки TEXT, не соответствует действительности. (См. Мой ответ на связанный вопрос на VARCHAR против ТЕКСТА)
Вы предложили распространить информацию более чем на одну таблицу. Это хорошая идея, если ваши таблицы полностью независимы друг от друга. В противном случае вы получите более одной операции INSERT для любого изменения, и вы, скорее всего, сделаете все намного хуже. Хотя нормализация данных, как правило, хороша (tm), это может снизить производительность.

Что вы можете сделать, чтобы SELECT работал быстро

Правильные ключи. И правильные ключи. И на всякий случай я забыл упомянуть: правильные ключи. Вы не указываете подробно, как выглядят ваши выборки, но я предполагаю, что они похожи на "SELECT * FROM entitychange WHERE entity_id=123 AND ts>...". Одного составного индекса для entity_id и ts должно быть достаточно, чтобы сделать эту операцию быстрой. Поскольку индекс должен обновляться с каждым INSERT, возможно, стоит попробовать производительность обоих entity_id, ts а также ts, entity_id: Это может иметь значение.
Разметка. Я бы даже не поднял эту тему, если бы ты не задал вопрос. Вы не говорите, почему вы хотите разделить таблицу. С точки зрения производительности это обычно не имеет значения, если у вас есть подходящие ключи. Существуют некоторые конкретные настройки, которые могут повысить производительность, но для этого вам потребуется правильная настройка оборудования. Если вы решили разделить свою таблицу, рассмотрите возможность сделать это либо по столбцу entity_id, либо по столбцу TIMESTAMP. Используя временную метку, вы можете получить архивную систему с более старыми данными, помещенными на диск архива. Такая система разбиения, однако, потребует некоторого обслуживания (добавление разделов с течением времени).

Мне кажется, что вы не так озабочены производительностью запросов, как исходной скоростью вставки, поэтому я не буду вдаваться в подробности о производительности SELECT. Если это вас заинтересует, пожалуйста, предоставьте более подробную информацию.

user921456 18 июн '13 в 10:57 2013-06-18 10:57 · Answer 7 · 2013-06-18 10:57

Я бы посоветовал вам сделать много в глубоком тестировании, но из моих тестов я получаю очень хорошие результаты как с INSERT, так и с SELECT с определением таблицы, которое я опубликовал ранее. Я подробно опишу свои тесты, чтобы любой мог легко повторить и проверить, получаются ли лучшие результаты. Сделайте резервную копию ваших данных перед любым тестом.
Я должен сказать, что это только тесты, и они могут не отражать или не улучшать ваш реальный случай, но это хороший способ изучения и, возможно, способ найти полезную информацию и результаты.

Советы, которые мы видели здесь, действительно хороши, и вы наверняка заметите значительное улучшение скорости, используя предопределенный тип VARCHAR с размером вместо TEXT. Однако вы могли бы набрать скорость, я бы посоветовал не использовать MyISAM по соображениям целостности данных, оставайтесь с InnoDB.

ИСПЫТАНИЯ:

1. Настроить таблицу и вставить 200 миллионов данных:

CREATE TABLE `entity_versionable` (
  `version` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `fk_entity` INT(10) UNSIGNED NOT NULL,
  `str1` VARCHAR(255) DEFAULT NULL,
  `str2` VARCHAR(255) DEFAULT NULL,
  `bool1` TINYINT(1) DEFAULT NULL,
  `double1` DOUBLE DEFAULT NULL,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`version`,`fk_entity`)
) ENGINE=INNODB AUTO_INCREMENT=230297534 DEFAULT CHARSET=latin1

Чтобы вставить в таблицу +200 миллионов строк за 35 минут, пожалуйста, проверьте мой другой вопрос, где peterm ответил на один из лучших способов заполнить таблицу. Работает отлично.

Выполните следующий запрос 2 раза, чтобы вставить 200 миллионов строк без случайных данных (меняйте данные каждый раз, чтобы вставить случайные данные):

INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE)
SELECT 1, 'a1', 238, 2, 524627, '2013-06-16 14:42:25'
FROM
(
    SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + g.N * 1000000 + h.N * 10000000 + 1 N FROM 
     (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) f
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) g
    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) h
) t;

* Поскольку у вас уже есть исходная таблица с 200 миллионами строк реальных случайных данных, вам, вероятно, не нужно будет ее заполнять, просто экспортируйте данные и схему таблицы и импортируйте ее в новую таблицу тестирования с той же схемой. Таким образом, вы будете делать тесты в новой таблице с вашими реальными данными, и полученные вами улучшения будут работать и для оригинальной.

2. Измените новую таблицу тестирования производительности (или используйте мой пример выше в шаге 1, чтобы получить лучшие результаты). После того, как мы настроили нашу новую тестовую таблицу и заполнили ее случайными данными, мы должны проверить приведенные выше советы и изменить таблицу, чтобы ускорить ее:

Измените текст на VARCHAR(255).
Выберите и создайте хороший уникальный индекс первичного ключа с двумя или тремя столбцами. Протестируйте с версией autoincrement и fk_entity в вашем первом тесте.
Разделите ваш стол, если необходимо, и проверьте, улучшает ли он скорость. Я бы посоветовал не разбивать его на ваши первые тесты, чтобы проверить реальный прирост производительности за счет изменения типов данных и конфигурации mysql. Проверьте следующую ссылку на некоторые разделы и советы по улучшению.
Оптимизация и ремонт вашего стола. Индекс будет сделан снова и значительно ускорит поиск:

ОПТИМИЗИРУЙТЕ СТОЛ test, entity_versionable;
РЕМОНТНЫЙ СТОЛ test, entity_versionable;
* Сделайте скрипт для выполнения оптимизации и поддержания вашего индекса в актуальном состоянии, запуская его каждую ночь.

3. Улучшите конфигурацию MySQL и оборудования, внимательно прочитав следующие темы. Их стоит прочитать, и я уверен, что вы получите лучшие результаты.

С легкостью улучшите конфигурацию жесткого диска базы данных, потратив немного
денег: если возможно, используйте SSD для своей основной базы данных MySQL и
отдельный механический жесткий диск для резервного копирования. Настройте журналы MySQL для сохранения на другом третьем жестком диске, чтобы повысить скорость вашего
Вставки. (Не забудьте дефрагментировать механические жесткие диски через несколько недель).
Ссылки на производительность: общие и многоядерные, конфигурация, оптимизация ввода-вывода, Debiancores, лучшая конфигурация, конфигурация, оперативная память 48 ГБ.
Профилирование SQL-запроса: как профилировать запрос, проверить возможные узкие места в запросе
MySQL очень интенсивно использует память, если возможно, используйте память CL7 DDR3 с малой задержкой. Немного не по теме, но если ваши системные данные критичны, вы можете искать память ECC, однако это дорого.

4. Наконец, проверьте ваши ВСТАВКИ и ПОИСКИ в тестовой таблице. В моих тестах с +200 миллионами случайных данных с помощью приведенной выше схемы таблицы, он тратит 0,001 секунды, чтобы вставить новую строку и около 2 минут, чтобы найти и выбрать 100 миллионов строк. И, тем не менее, это всего лишь тест и, похоже, хорошие результаты:)

5. Конфигурация моей системы:

База данных: MySQL 5.6.10 База данных InnoDB (тест).
Процессор: AMD Phenom II 1090T X6, 3910 МГц каждое ядро.
Оперативная память: 16 ГБ DDR3 1600 МГц CL8.
HD: Windows 7 64bit SP1 в SSD, mySQL установлен в SSD, журналы записаны на механическом жестком диске.
Вероятно, мы должны получить лучшие результаты с одним из последних Intel i5 или i7, легко разогнанным до 4500 МГц +, поскольку MySQL использует только одно ядро для одного SQL. Чем выше скорость ядра, тем быстрее он будет выполняться.

6. Узнайте больше о MySQL:
Высокая производительность MySQL от O'Reilly
MySQL Оптимизация операторов SQL

7. Использование другой базы данных: MongoDB или Redis будут идеальными для этого случая и, вероятно, намного быстрее, чем MySQL. Оба очень просты в освоении, и оба имеют свои преимущества:
- MongoDB: увеличение файла журнала MongoDB

Redis

Я бы окончательно пошел на Redis. Если вы научитесь сохранять журнал в Redis, это будет лучший способ управлять журналом с безумно высокой скоростью: Redis для регистрации
Имейте в виду следующие советы, если вы используете Redis:

Redis скомпилирован в C и хранится в памяти, имеет несколько различных методов для автоматического сохранения информации на диск (постоянство), вам, вероятно, не придется беспокоиться об этом. (в случае сценария бедствия вы потеряете около 1 секунды регистрации).
Redis используется на многих сайтах, которые управляют терабайтами данных, существует множество способов обработки этого безумного количества информации, и это означает, что он защищен (используется здесь в stackru, blizzard, twitter, youporn...)
Поскольку ваш журнал будет очень большим, он должен уместиться в памяти, чтобы получить скорость без доступа к жесткому диску. Вы можете сохранить разные журналы для разных дат и установить только некоторые из них в памяти. В случае достижения предела памяти у вас не будет никаких ошибок, и все будет по-прежнему работать идеально, но проверьте Redis Faqs для получения дополнительной информации.
Я полностью уверен, что Redis будет намного быстрее для этой цели, чем MySQL. Вам нужно будет узнать, как играть с lists а также sets обновить данные и запросить / найти данные. Если вам может потребоваться действительно расширенный поиск запросов, вам следует использовать MongoDB, но в этом случае простой поиск по дате будет идеальным для Redis.

Хорошая статья Redis в блоге Instagram.

user2154300 16 июн '13 в 09:00 2013-06-16 09:00 · Answer 8 · 2013-06-16 09:00

На работе у нас есть журнальные таблицы практически на каждом столе из-за условий клиента (финансовый сектор).

Мы сделали это следующим образом: две таблицы ("обычная" таблица и таблица журнала), а затем запускаются при вставке / обновлении / удалении обычной таблицы, в которой хранится ключевое слово (I,U,D) и старая запись (при обновлении)., удалить) или новый (при вставке) внутри таблицы

У нас есть обе таблицы в одной базе данных-схемы