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

В настоящее время запрос успешно выполняется 3000 мсек на выполнение

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

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

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

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

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

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

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

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

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

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. имя пользователя
   ОТ сообщений
   ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    пользователи
   ВКЛ. Users.id = messages.user_id
   WHERE messages.id в (?,?,?,? ... всего 100 "?": S);
   

   • Планы выполнения, сгенерированные этим запросом и вашим исходным запросом.

Well, the query and table design themselves are likely not the cause. Though the query could use some help(like adding the "in list" into the join predicate as to eliminate a late-side filter, though i'd guess the optimizer returns the same plan regardless)

My guess is this is a symptom of other issues, index\table fragmentation or out of date stats. Are these tables often deleted from? It might help to defrag the tables and indexes, otherwise you may be a victim of pages that are only 10% or less full, which would cause alot of disk I/O.

Note: with an integer seed for a primary key, you don't often see alot of fragmentation unless you get alot of deletes & updates to rows.

Since this is usually re-written by the parser as:

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.

I'd be interested to see the execution plan and the performance for a single case:

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

In which case you might be better off doing a UNION or creating a table containing the IDs and performing a JOIN.

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