Я бы посоветовал вам сделать много глубокого тестирования, но из моих тестов я получаю очень хорошие результаты как с 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).
- Выберите и создайте хороший уникальный индекс первичного ключа с двумя или тремя столбцами. Протестируйте с автоинкрементом версии и fk_entity в первом тесте.
- При необходимости разбейте таблицу на разделы и проверьте, не улучшит ли это скорость. Я бы посоветовал не разбивать его на первые тесты, чтобы проверить реальный прирост производительности за счет изменения типов данных и конфигурации mysql. По следующей ссылке вы найдете несколько советов по разделам и улучшениям. .
- Оптимизируйте и восстановите таблицу. Индекс будет создан снова и значительно ускорит поиск:
ТАБЛИЦА ОПТИМИЗАЦИИ test.entity_versionable;
ТАБЛИЦА РЕМОНТА test.entity_versionable;
*Создайте скрипт для оптимизации и поддержания вашего индекса в актуальном состоянии, запуская его каждую ночь.
3. Улучшите конфигурацию MySQL и оборудования, внимательно прочитав следующие темы. Их стоит прочитать, и я уверен, что вы получите лучшие результаты.
- Легко улучшите конфигурацию жесткого диска базы данных, потратив немного
денег: по возможности используйте твердотельный накопитель для основной базы данных MySQL и отдельный
механический жесткий диск для целей резервного копирования. Настройте сохранение журналов MySQL на другом третьем жестком диске, чтобы повысить скорость ваших
операций INSERT. (Не забудьте дефрагментировать механические жесткие диски через несколько недель).
- Ссылки на производительность: general&multiple-cores, конфигурация, оптимизация ввода-вывода, Debiancores, лучшая конфигурация, config 48gb ram..
- Профилирование запроса 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 64-бит с пакетом обновления 1 (SP1) на SSD, mySQL установлена на SSD, журналы записываются на механический жесткий диск.
Вероятно, мы должны добиться лучших результатов с одним из последних Intel i5 или i7 легко разогнать до 4500 МГц+, поскольку MySQL использует только одно ядро для одного SQL . Чем выше частота ядра, тем быстрее оно будет выполняться.
<сильный>6. Подробнее о MySQL:
O 'Reilly High Performance MySQL
MySQL Оптимизация операторов SQL
7. Использование другой базы данных: MongoDB или Redis идеально подходят для этого случая и, вероятно, намного быстрее, чем MySQL. Оба очень просты в освоении, и оба имеют свои преимущества:
- MongoDB: рост файла журнала MongoDB
Редис
Я бы определенно выбрал Redis. Если вы научитесь сохранять журнал в Redis, это будет лучший способ управлять журналом с безумно высокой скоростью: Redis для ведения журнала
Если вы используете Redis, обратите внимание на следующие советы:
Redis скомпилирован на C и хранится в памяти, имеет несколько различных методов автоматического сохранения информации на диск (постоянство), вам, вероятно, не придется об этом беспокоиться. (в случае аварийного сценария вы потеряете около 1 секунды регистрации).
Redis используется на многих сайтах, которые управляют терабайтами данных, существует множество способов обработки этого безумного количества информации, и это означает, что он безопасен (используется здесь в stackoverflow, blizzard, twitter, youporn..)
Поскольку ваш журнал будет очень большим, он должен будет уместиться в памяти, чтобы получить скорость без доступа к жесткому диску. Вы можете сохранять разные журналы для разных дат и устанавливать в памяти только некоторые из них. В случае достижения предела памяти у вас не будет никаких ошибок, и все по-прежнему будет работать отлично, но проверьте Часто задаваемые вопросы Redis для получения дополнительной информации.
Я полностью уверен, что Redis для этой цели будет намного быстрее, чем MySQL. Вам нужно будет узнать, как играть с lists и sets для обновления данных и запроса/поиска данных. Если вам может понадобиться действительно расширенный поиск запросов, вам следует использовать MongoDB, но в этом случае простой поиск по дате идеально подойдет для Redis.
Хорошая статья Redis в Блог в Instagram.
person
Community
schedule
18.06.2013
SOMEDOUBLEиз одного и того же объекта и, таким образом, создать две строки вentitychangeс одним и тем жеentity_id. Я обновил свой вопрос, поэтому процесс изменения становится более понятным. - person Stefan schedule 11.06.2013enumна самом деле выполняет нормализацию: он использует 1 или 2 байта для хранения в зависимости от количества значений (65 КБ - макс.). - person Stoleg schedule 15.06.2013enum-enumне создает вторую таблицу, а просто кодирует фиксированный список значений и сохраняет порядковый номер значения в списке. Но это не то, что вы описали normalization. Нормализация — это не обращение к атрибутам с помощью суррогатного ключа. Нормализация не направлена на то, чтобы сделать хранилище более компактным. Нормализация предназначена для предотвращения аномалий данных. - person Bill Karwin schedule 17.06.2013enumне имеет ничего общего с нормализацией. - person Stoleg schedule 17.06.2013