Эффективное обращение к таблице из 15 000 000 строк в MySQL

Рассмотрим следующие таблицы базы данных:

  • Таблица "сообщений" с 13 000 000 строк (одна строка для каждого сообщения).
  • Таблица "пользователи" с 3 000 000 строк (одна строка для каждого пользователя).

Следующий запрос используется для извлечения нескольких сообщений и соответствующих пользователей:

SELECT messages.id, messages.message, users.id, users.username
FROM messages
INNER JOIN users ON messages.user_id=users.id 
WHERE messages.id in (?, ?, ?, ? ... a total of 100 "?":s);

В каждом запросе выбирается 100 сообщений.

"сообщения" индексируются по идентификатору (первичный ключ, BIGINT не автогенерируется) и user_id.

"пользователи" индексируются по идентификатору (первичный ключ, автоматически сгенерированный INT).

База данных - это MySQL, использующий MyISAM.

В настоящее время для выполнения запроса требуется более 3000 мс, что вызывает у меня проблемы, поскольку "сообщения" индексируются на "id", поэтому получение правильных строк должно быть очень быстрым.

Мой вопрос: учитывая описанный сценарий и настройку, время запроса 3000 мс "нормально" или я что-то упускаю? Пожалуйста, дайте мне знать, если потребуется дополнительная информация.

Обновление # 1: Ниже приведены определения таблиц:

CREATE TABLE messages (
  id bigint(20) NOT NULL DEFAULT '0',
  user_id int(11) NOT NULL DEFAULT '0',
  message varchar(160) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY user_id (user_id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE users (
  id int(11) NOT NULL DEFAULT '0',
  username varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY username (username),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Единственная "нестандартная" вещь, которую я наблюдаю в определениях, заключается в том, что "messages.id" - это BIGINT, а не INT. Может быть, это намек?

Ответы

Ответ 1

  • НУЖНО, что объясняем.
  • MyISAM предлагает плохой concurrency. Учтите, что одновременные вставки могут вызывать головные боли. С таким большим базами данных InnoDB может быть направлением.
  • Если сообщения вставляются и удаляются, это может привести к искажениям, если ваши таблицы не будут оптимизированы иногда. Кроме того, первичные ключи MyISAM не являются clusterd. Опять же, с такой большой базой данных, InnoDB может быть направлением.

Ответ 2

Я работал над таблицами MyISAM с миллиардами строк, и одна из вещей, которые я нашел после некоторого ограничения количества строк, заключалась в том, что оптимизатор слишком долго решал, как подойти к запросу, и ошибочно выполнил некоторые сканирование таблицы. Я не могу найти точную страницу, которую я нашел, описывая ее, но я начал всегда использовать FORCE_INDEX для каждого сегмента запросов, где я знал, как он должен запрашивать объекты

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

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

Кроме того, если ваши таблицы большие, я предполагаю, что ваши индексы также велики. Вам абсолютно необходимо убедиться, что у вас есть правильные параметры конфигурации и что ваш key_buffer имеет достаточный размер, и у вас достаточно ввода/вывода. Если вы используете 32-разрядный mysql (чего вам не должно быть), тогда поставьте key_buffer размером 1 ГБ (при условии, что у вас есть 1 ГБ, чтобы сэкономить) и проверьте его использование с помощью mysqlreport

Если вы используете 64-битный mysql, вы можете сделать его настолько большим, насколько сможете, но при этом оставляете место для ОС для кеширования файлов и любых других приложений, которые у вас есть, поэтому, может быть, несколько GB, если сможете.

Даже если ваши запросы используют индексы, если индекс не может быть должным образом буферизирован в памяти, вы по-прежнему нажимаете на диск, и есть производительность, пропорциональная размеру индекса и скорости диска/доступного ввода/вывода.

Что касается int vs big int, единственная заметная разница в производительности, которую я видел, заключается в выполнении вычислений на больших int, таких как SUM. SUM заметно медленнее на большом int, чем на int, настолько, что я бы посмотрел на сохранение чисел разной величины или разделил их на два ints, если вам нужно выполнять частые вычисления на них.

Ответ 3

SELECT  messages.id, messages.message, users.id, users.username
FROM    messages
INNER JOIN
        users
ON      users.id = messages.user_id
WHERE   messages.id in (?, ?, ?, ? ... a total of 100 "?":s);

Кажется, что ваши сообщения имеют тип данных TEXT и длинны.

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

Не могли бы вы проверить две вещи:

  • Выполнение этого запроса:

    SELECT  messages.id, users.id, users.username
FROM    messages
INNER JOIN
        users
ON      users.id = messages.user_id
WHERE   messages.id in (?, ?, ?, ? ... a total of 100 "?":s);
    • Планы выполнения, сгенерированные этим запросом и вашим исходным запросом.

Ответ 4

Ну, сам дизайн запроса и таблицы, вероятно, не является причиной. Хотя запрос может использовать некоторую помощь (например, добавить "в список" в предикат соединения, чтобы исключить фильтр поздней стороны, хотя я бы предположил, что оптимизатор возвращает тот же план независимо)

Мое предположение - это симптом других проблем, фрагментация индекса\таблицы или устаревшая статистика. Из этих таблиц часто удаляются? Это может помочь дефрагментировать таблицы и индексы, в противном случае вы можете стать жертвой страниц, которые будут заполнены только на 10% или меньше, что приведет к большому количеству дисковых операций ввода-вывода.

Примечание: с целым семенем для первичного ключа вы не часто видите много фрагментации, если не получаете много удалений и обновлений для строк.

Ответ 5

В настоящее время запрос берет 3000 мс для выполнения

Каждый раз, или только первый запрос? Может быть, первый запрос связан с загрузкой индексов и т.д.?

И для сравнения, сколько времени требуется для выполнения одного и того же запроса для определенного идентификатора сообщения?

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

Ответ 6

Так как это обычно переписывается парсером как:

SELECT messages.id, messages.message, users.id, users.username
FROM messages
INNER JOIN users ON messages.user_id=users.id 
WHERE messages.id = ?
OR messages.id = ?
OR messages.id = ? etc.

Мне было бы интересно увидеть план выполнения и производительность для одного случая:

SELECT messages.id, messages.message, users.id, users.username
FROM messages
INNER JOIN users ON messages.user_id=users.id 
WHERE messages.id = ?

В этом случае вам может быть лучше сделать UNION или создать таблицу, содержащую идентификаторы, и выполнить JOIN.

Ответ 7

На каком оборудовании вы смотрите здесь? Я предполагаю, что у вас есть сервер с достаточным количеством ram и key_buffer, установленным достаточно большим (например, больше, чем объединенные размеры индекса двух таблиц с минимальным размером). Я предполагаю, что сервер - это простаивающий сервер проверки производительности.

Можете ли вы измерить количество IO?

Если вы повторяете тот же самый запрос, быстро ли он?

Если вы загрузите всю базу данных в диск RAM (небольшая таблица с 15-миллиметровыми рядами будет легко вписываться в диск с диском), это быстрее?

Также (как отмечали другие), разместите план EXPLAIN.

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