Эффективное извлечение 25 строк с максимальной суммой двух столбцов (MySQL)

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

В проблемном случае я хочу выбрать 25 строк, упорядоченных по сумме двух столбцов в порядке убывания.

SELECT username, rank1 + rank2 AS rank FROM users ORDER BY rank DESC LIMIT 25

Запрос работает, но занимает примерно 0,25 секунды, в отличие от запросов к одному столбцу, которые занимают около 0,0003 секунды. Ниже приведен результат для запроса объяснения:

id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra
1  | SIMPLE      | accounts | ALL  | NULL          | NULL | NULL    | NULL | 517874 | Using filesort

И rank1, и rank2 индексируются, но очевидно, что индексы не используются для этого запроса. Есть ли способ улучшить производительность, как-то отредактировав запрос или индексы?


mysql sql optimization indexing
person Lassi    schedule 23.02.2014    source источник
comment
Я удаляю тег sql-server, потому что синтаксис предназначен для MySQL.   -  person Gordon Linoff    schedule 23.02.2014
comment
Не могли бы вы рассказать больше о распределении значений в столбцах rank1 и rank2?   -  person Gordon Linoff    schedule 23.02.2014
comment
Вы пытались добавить индекс на (username, rank1, rank2)?   -  person Bob Jarvis - Reinstate Monica    schedule 23.02.2014
comment
Разве нельзя добавить третий столбец, в котором хранится сумма двух рангов, а затем создать индекс для этого столбца?   -  person Razvan    schedule 23.02.2014
comment
@BobJarvis У меня нет, это не влияет на производительность   -  person Lassi    schedule 23.02.2014
comment
@GordonLinoff Я не совсем уверен, что вы подразумеваете под распределением значений, но рассматриваемые столбцы представляют собой целые числа, ~ 70% из которых - нули.   -  person Lassi    schedule 23.02.2014
comment
@Ласси. . . Может ли наибольшая сумма получиться, когда одно из значений равно нулю? Принимают ли они отрицательные значения? Есть ли повторяющиеся ненулевые значения? Плотны ли значения или есть большие пробелы?   -  person Gordon Linoff    schedule 23.02.2014
comment
@GordonLinoff Наибольшая сумма может возникнуть, когда одно из значений равно нулю, и оба являются целыми числами без знака. Там действительно нет шаблонов, и могут быть и большие заглавные буквы. Чаще всего значения находятся в диапазоне от 0 до 1 000 000 000.   -  person Lassi    schedule 23.02.2014
comment
Является ли время поиска в 1/4 секунды проблемой?   -  person Bob Jarvis - Reinstate Monica    schedule 24.02.2014
comment
@BobJarvis В сочетании с другими похожими запросами на той же странице - да   -  person Lassi    schedule 24.02.2014

Ответы (1)


arrow_upward
2
arrow_downward

MySQL не очень хорошо справляется с этой ситуацией. Другие базы данных (например, Oracle, Postgres, SQL Server) предлагают некоторую форму индексов на основе функций, которые могут напрямую решить эту проблему. Для этого в MySQL требуется добавить новый столбец в таблицу, а затем добавить триггер, чтобы поддерживать его в актуальном состоянии. И, наконец, индекс для нового столбца. Возможно много работы.

В некоторых ситуациях вы можете предположить, что первые XXX по сумме будут в первых YYY для каждого ранжирования. Если это так, то такой запрос улучшит производительность:

select ur1.*
from (select u.*
      from users u
      order by rank1 desc
      limit 1000
     ) ur1 join
     (select u.*
      from users u
      order by rank2 desc
      limit 1000
     ) ur2
     on ur1.username = ur2.username
order by ur1.rank1 + ur1.rank2 desc
limit 25;

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

person Gordon Linoff    schedule 23.02.2014
comment
Создание нового столбца и установка триггера обеспечили улучшение производительности, которое я искал. Спасибо! - person Lassi; 24.02.2014