Очень интересная проблема MYSQL (связанная с индексацией, миллионами записей, алгоритмом.)

Эту проблему довольно сложно описать, и поэтому трудно найти ответ. Я надеюсь, что некоторые эксперты могут поделиться своим мнением по этому поводу.

У меня есть таблица с примерно 1 миллионом записей. Структура таблицы примерно такая:

items{
  uid (primary key, bigint, 15)
  updated (indexed, int, 11)
  enabled (indexed, tinyint, 1)
}

Сценарий такой. Я должен выбирать все записи каждый день и выполнять некоторую обработку. Для обработки каждого элемента требуется около 3 секунд.

Я написал PHP-скрипт для извлечения 200 элементов каждый раз, используя следующее.

select * from items where updated > unix_timestamp(now()) - 86400 and enabled = 1 limit 200;

Затем я обновлю «обновленное» поле выбранных элементов, чтобы убедиться, что оно не будет выбрано снова в течение одного дня. Выбранный запрос примерно такой.

update items set updated = unix_timestamp(now()) where uid in (1,2,3,4,...);

Затем PHP продолжит работать и обрабатывать данные, которые больше не требуют подключения к MYSQL.

Поскольку у меня есть миллион записей, и каждая запись обрабатывается 3 секунды, это определенно невозможно сделать последовательно. Поэтому я буду выполнять PHP каждые 10 секунд.

Однако с течением времени и ростом таблицы выбор становится намного медленнее. Иногда для запуска требуется более 100 секунд!

У вас есть какие-либо предложения, как я могу решить эту проблему?


mysql indexing
person Terence Tsang    schedule 03.05.2010    source источник
comment
Я должен выбирать все записи каждый день и выполнять некоторую обработку. Для обработки каждого элемента требуется около 3 секунд. - 3 000 000 секунд обработки каждый день? Звучит невозможно (или очень дорого).   -  person aib    schedule 03.05.2010
comment
Кроме того, пожалуйста, предоставьте нам вывод EXPLAIN ваших запросов. (Или SELECT в этом случае, я не думаю, что MySQL может выполнить EXPLAIN UPDATE)   -  person aib    schedule 03.05.2010

Ответы (6)


arrow_upward
3
arrow_downward

Есть два момента, которые, как мне кажется, должны помочь:

а. unix_timestamp(сейчас()) - 86400)

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

б. Индексы помогают читать, но могут замедлять запись

Рассмотрите возможность удаления индексов перед обновлением (DISABLE KEYS), а затем повторно добавьте их перед чтением (ENABLE KEYS).

person amelvin    schedule 03.05.2010

arrow_upward
2
arrow_downward

Я не думаю, что индекс на enabled приносит вам пользу, кардинальность слишком мала. Удалите это, и ваши UPDATE должны работать быстрее.

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

person D'Arcy Rittich    schedule 03.05.2010
comment
Я буду получать 200 данных каждый раз и вызывать REST API. Время прохождения туда и обратно для REST API составляет около 3 секунд. Иногда API будет возвращать ошибку, и я должен установить для параметра enable значение 0, и этот элемент больше не будет извлекаться. - person Terence Tsang; 05.05.2010
comment
@ terence410: Похоже, проблема не имеет ничего общего с SQL или PHP - это медленный REST API. Если это ваш код, то вам повезло, вы можете попробовать улучшить его производительность. Если нет, то мало что можно сделать. - person D'Arcy Rittich; 05.05.2010

arrow_upward
2
arrow_downward

Вы можете сделать это:

  1. dispatcher.php: Manages the whole process.
    • fetches items in convenient packages from the database
    • вызывает worker.php на том же сервере с сообщением HTTP, содержащим все извлеченные UID (я понимаю, что worker.php не потребуется больше, чем UID, чтобы выполнить свою работу)
    • поддерживает счетчик количества запущенных worker.php скриптов. Когда он запускается, счетчик увеличивается до определенного предела, когда возвращается один рабочий, счетчик уменьшается. См. "Асинхронные вызовы PHP?".
    • повторяется до тех пор, пока все записи не будут получены один раз. Поддерживайте счетчик MySQL LIMIT и не работайте с updated.
  2. worker.php: does the actual work
    • does its thing with each item posted.
    • записывает во вспомогательную таблицу идентификатор каждого обработанного элемента (в этой таблице нет индекса)
  3. dispatcher.php: housekeping.
    • once all workers have returned, updates the main table with the helper table in a single statement
  4. error recovery
    • since worker.php would update the helper table after each item done, you can use the state of the helper table to recover from a crash. Saving the "work package" of each individual worker before it starts running would help to recover worker states as well.

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

person Tomalak    schedule 03.05.2010
comment
P.S.: Если асинхронная работа окажется слишком сложной в PHP, вы можете реализовать диспетчер на более удобном языке. Вышесказанное - это просто идея, я не знаю, возможно ли это. Возможно, необходимо несколько модификаций, чтобы приспособить PHP к отсутствию собственных потоков. - person Tomalak; 03.05.2010

arrow_upward
1
arrow_downward

Вы можете попробовать запустить это до обновления:

ALTER TABLE items DISABLE KEYS;

а затем, когда вы закончите обновление,

ALTER TABLE items ENABLE KEYS;

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

person cHao    schedule 03.05.2010
comment
ENABLE KEYS для большой таблицы может занять много времени, возможно, это не лучшая идея для обновления небольшого количества строк. - person David M; 03.05.2010
comment
Может быть. Но затем вы обновляете индекс (возможно, значительно) каждый раз, когда обновляете строку. Мне кажется, лучше сделать все это сразу, чем говорить, что нужно перемещать эти 500 000 записей индекса каждый раз, когда вы делаете обновление. - person cHao; 03.05.2010

arrow_upward
0
arrow_downward

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

person user21884    schedule 03.05.2010

arrow_upward
0
arrow_downward

Одна идея:

Используйте HANDLER, который значительно улучшит вашу производительность:

http://dev.mysql.com/doc/refman/5.1/en/handler.html

person David M    schedule 03.05.2010