Эффективный запрос таблицы из 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. Может это намек?


mysql optimization query-optimization indexing
person knorv    schedule 07.07.2009    source источник
comment
Можете ли вы опубликовать вывод EXPLAIN SELECT...   -  person Greg    schedule 07.07.2009
comment
Вы пытались поместить 100 MessageIds во временную таблицу и выполнить соединение с этой таблицей или Exists?   -  person Alex Black    schedule 07.07.2009
comment
3000 мс кажется неплохим с предложением where, имеющим более 100 ограничений. Вы можете проверить это, создав временную таблицу, сначала содержащую 1 столбец и все ваши 100 '?' который вы используете для фильтрации по message.id и присоединяетесь к нему вместо where message.id in(...), чтобы увидеть, имеет ли это какое-то значение.   -  person nos    schedule 07.07.2009
comment
3000 мс кажутся ужасными. Поиск каждого из 100 сообщений в индексе должен быть очень быстрым, тогда это просто вопрос извлечения этих 100 записей, поиска их пользователей в индексе и извлечения этих 100 записей.   -  person Alex Black    schedule 07.07.2009
comment
@AlexBlack: Да, messages.user_id проиндексирован. См. мое обновление с определениями таблиц.   -  person knorv    schedule 07.07.2009
comment
Можете ли вы запустить объяснение по запросу?   -  person Darryl Hein    schedule 07.07.2009
comment
Сокращается ли пропорционально количество времени при уменьшении количества идентификаторов в IN()?   -  person Darryl Hein    schedule 07.07.2009
comment
проверьте следующий ответ и замените форумы пользователями и темы с сообщениями. stackoverflow.com/questions/4419499/   -  person Jon Black    schedule 20.03.2016

Ответы (7)


arrow_upward
6
arrow_downward

  1. Нам НУЖНО это объяснить.
  2. MyISAM предлагает плохой параллелизм. Учтите, что одновременные вставки могут вызывать у вас головную боль. С такой большой базой данных InnoDB может быть правильным направлением.
  3. Если сообщения вставляются и удаляются, это может привести к перекосу, если ваши таблицы время от времени не оптимизируются. Кроме того, первичные ключи MyISAM не кластеризованы. Опять же, с такой большой базой данных InnoDB может быть правильным направлением.
person Jeff Ferland    schedule 07.07.2009

arrow_upward
11
arrow_downward

Я работал с таблицами 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, сделайте ее настолько большой, насколько это возможно, оставив при этом место для ОС для кэширования файлов и любых других приложений, которые у вас запущены, так что, возможно, пару ГБ, если сможете.

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

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

person Mike Venzke    schedule 28.07.2009

arrow_upward
3
arrow_downward 

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 хранятся вне строки, поэтому вам нужно будет сделать несколько дополнительных чтений страниц, чтобы получить их, что может занять много времени.

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

  1. Производительность этого запроса:

    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);
    • Execution plans generated by this query and your original query.
person Quassnoi    schedule 07.07.2009
comment
Я опубликовал обновление с точными определениями таблиц. Обратите внимание, что messages.message — это просто VARCHAR(160). messages.id имеет БОЛЬШОЕ значение - может быть это подсказка? - person knorv; 07.07.2009
comment
@knorv: не могли бы вы опубликовать планы для обоих запросов? Запустите EXPLAIN SELECT messages.id ... и опубликуйте вывод здесь. - person Quassnoi; 07.07.2009

arrow_upward
1
arrow_downward

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

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

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

person Brian Rudolph    schedule 07.07.2009

arrow_upward
0
arrow_downward

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

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

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

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

person frankodwyer    schedule 07.07.2009
comment
3000+ мс — это каждый раз, когда выдается запрос. См. мое обновление определений таблиц. - person knorv; 07.07.2009

arrow_upward
0
arrow_downward

Поскольку это обычно переписывается синтаксическим анализатором как:

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.

person Cade Roux    schedule 07.07.2009

arrow_upward
0
arrow_downward

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

Можете ли вы измерить количество операций ввода-вывода?

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

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

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

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

person MarkR    schedule 07.07.2009