Какие существуют методы оптимизации для таблицы MySQL с более чем 300 миллионами записей?

Есть несколько вещей, которые вы можете сделать:

1. Создавайте свои индексы, чтобы они соответствовали запросам, которые вы выполняете. Запустите EXPLAIN, чтобы просмотреть типы запросов, которые выполняются и выполняются. убедитесь, что все они используют индекс, где это возможно.

2. Разделите таблицу. Разделение — это метод разделения большой таблицы на несколько меньших по определенному (агрегированному) ключу. MySQL поддерживает это внутри, начиная с ver. 5.1.

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

3 предложения:

1. показатель
2. показатель
3. показатель

p.s. для временных меток вы можете столкнуться с проблемами производительности - в зависимости от того, как MySQL обрабатывает DATETIME и TIMESTAMP внутри, может быть лучше хранить временные метки как целые числа. (# сек. с 1970 года или где-то еще)

Что ж, для начала я бы посоветовал вам использовать «автономную» обработку для создания «графически готовых» данных (для большинства распространенных случаев), а не пытаться запрашивать необработанные данные по запросу.

Если вы используете MYSQL 5.1, вы можете использовать новые функции. но имейте в виду, что они содержат много ошибок.

сначала вы должны использовать индексы. если этого недостаточно, вы можете попытаться разделить таблицы с помощью секционирования.

если это также не сработает, вы также можете попробовать балансировку нагрузки.

Несколько предложений.

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

Это означает, что я буду получать некоторые данные с интервалом, используя временные метки.

Значит, вы используете данные только за последние X дней?

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

Теперь, если вы используете MySQL, я настоятельно рекомендую использовать таблицы MyISAM. Вы не получаете отказоустойчивости или транзакций, а блокировка глупа, но размер таблицы намного меньше, чем у InnoDB, а это значит, что она может поместиться в ОЗУ, что означает гораздо более быстрый доступ.

Поскольку большие агрегаты могут включать в себя множество довольно последовательных дисковых операций ввода-вывода, быстрая система ввода-вывода, такая как RAID10 (или SSD), является плюсом.

Можно ли как-то оптимизировать таблицу или запрос, чтобы вы могли выполнять эти запросы за разумное время?

Это зависит от таблицы и запросов; не могу дать совет, не зная больше.

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

Я предлагаю вам протестировать с Postgres. Для больших агрегатов более умный оптимизатор работает хорошо.

Пример :

CREATE TABLE t (id INTEGER PRIMARY KEY AUTO_INCREMENT, category INT NOT NULL, counter INT NOT NULL) ENGINE=MyISAM;
INSERT INTO t (category, counter) SELECT n%10, n&255 FROM serie;

(серия содержит 16M строк с n = 1 .. 16000000)

MySQL    Postgres     
58 s     100s       INSERT
75s      51s        CREATE INDEX on (category,id) (useless)
9.3s     5s         SELECT category, sum(counter) FROM t GROUP BY category;
1.7s     0.5s       SELECT category, sum(counter) FROM t WHERE id>15000000 GROUP BY category;

В простом запросе, таком как этот, pg примерно в 2-3 раза быстрее (разница была бы намного больше, если бы использовались сложные соединения).

1. ОБЪЯСНИТЕ ваши запросы SELECT

2. LIMIT 1 при получении уникальной строки SELECT * FROM user WHERE state = 'Alabama' // неправильно SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1

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

4. Индексируйте и используйте одинаковые типы столбцов для объединений Если ваше приложение содержит много запросов JOIN, вам необходимо убедиться, что столбцы, по которым вы соединяетесь, индексируются в обеих таблицах. Это влияет на то, как MySQL внутренне оптимизирует операцию соединения.

5. НЕ ORDER BY RAND() Если вам действительно нужны случайные строки из ваших результатов, есть гораздо лучшие способы сделать это. Конечно, это требует дополнительного кода, но вы предотвратите узкое место, которое экспоненциально ухудшается по мере роста ваших данных. Проблема в том, что MySQL должен будет выполнять операцию RAND() (которая требует вычислительной мощности) для каждой отдельной строки в таблице, прежде чем отсортировать ее и дать вам только 1 строку.

6. Используйте столбцы ENUM вместо VARCHAR. Столбцы типа ENUM очень быстрые и компактные. Внутри они хранятся как TINYINT, но могут содержать и отображать строковые значения.

7. Используйте NOT NULL, если можете Если у вас нет особой причины использовать значение NULL, вы всегда должны устанавливать свои столбцы как NOT NULL.

   «Столбцы NULL требуют дополнительного места в строке, чтобы записать, являются ли их значения NULL. Для таблиц MyISAM каждый столбец NULL занимает один дополнительный бит, округляемый до ближайшего байта».

8. Сохраняйте IP-адреса как UNSIGNED INT В своих запросах вы можете использовать INET_ATON() для преобразования IP в целое число и INET_NTOA() для наоборот. В PHP также есть похожие функции, называемые ip2long() и long2ip().