С точки зрения эффективности, насколько эффективно использовать временную таблицу MySQL для высокоиспользуемой функции веб-сайта?

Я пытаюсь написать функцию поиска для веб-сайта, и я решил использовать временные таблицы MySQL для обработки ввода данных по следующему запросу:

CREATE TEMPORARY TABLE `patternmatch`
  (`pattern` VARCHAR(".strlen($queryLengthHere)."))

INSERT INTO `patternmatch` VALUES ".$someValues

Где $someValues - это набор данных с макетом ('some', 'search', 'query') - или в основном то, что пользователь искал. Затем я просматриваю основную таблицу images на основе данных в таблице patternmatch следующим образом:

SELECT images.* FROM images JOIN patternmatch ON (images.name LIKE patternmatch.pattern)

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

Мне интересно, сколько накладных расходов требуется для создания временной таблицы? Я понимаю, что они существуют только в сеансе и отбрасываются, как только заканчивается сеанс, но если у меня есть сотни тысяч запросов в секунду, какие проблемы с производительностью я могу встретить? Есть ли лучший способ реализации функции поиска?

Ответы

Ответ 1

То, что вы указали, абсолютно правильно, временная таблица будет видна только текущему пользователю/соединению. Тем не менее, есть некоторые накладные расходы и некоторые другие проблемы, такие как:

  • Для каждого из тысяч поисков вы собираетесь создавать и заполнять эту таблицу (и опускать ее позже) - не для каждого пользователя, за поиск. Поскольку каждый поиск, скорее всего, повторит выполнение script, а "за сеанс" не означает сеанс PHP - это означает сеанс базы данных (открытое соединение).
  • Вам понадобится привилегия CREATE TEMPORARY TABLES, которой у вас может не быть.
  • Тем не менее, эта таблица действительно должна иметь тип MEMORY, который крадет вашу RAM больше, чем кажется. Поскольку даже имея VARCHAR, таблицы MEMORY используют хранилище строк фиксированной длины.
  • Если ваши эвристики позже должны ссылаться на эту таблицу дважды (например, SELECT xyz FROM patternmatch AS pm1, patternmatch AS pm2 ...) - это невозможно с таблицами MEMORY.

Далее вам будет проще, а также для базы данных - добавить LIKE '%xyz%' непосредственно в таблицу images table WHERE. Он будет делать то же самое без накладных расходов при создании TEMP TABLE и присоединении к нему.

В любом случае - независимо от того, в каком направлении вы идете - что ГДЕ будет ужасно медленным. Даже если вы добавите индекс на images.name, вам скорее всего понадобится LIKE '%xyz%' вместо LIKE 'xyz%', чтобы индекс не стал использоваться.

Я спрашиваю, является ли временная таблица, относящаяся к сеансу, для обработки введенных пользователем запросов поиска (созданных при поиске, отбрасываемых в конце сеанса), является подходящим способом обработки функциональности поиска.

Нет.:)

Альтернативные параметры

MySQL имеет встроенный Fulltext-Search (начиная с 5.6 также для InnoDB), который даже может дать вам такой результат: я очень рекомендую давая ему прочитать и попробовать. Вы можете быть уверены, что база данных лучше знает, как эффективно выполнять поиск.

Если вы собираетесь использовать MyISAM вместо InnoDB, помните о часто забытом ограничении, которое FULLTEXT ищет только для возврата, если количество результатов меньше 50% от всех строк таблицы.

Другие вещи, которые вы, возможно, захотите посмотреть, например, Solr (Nice введение, прочитанное в эту тему, было бы началом http://en.wikipedia.org/wiki/Apache_Solr). Мы используем его в нашей компании, и он отлично справляется, но для этого требуется довольно некоторое обучение.

Резюме

Решение самой текущей проблемы (поиск) заключается в использовании возможностей FULLTEXT.

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

Чтобы дать вам номер, 10.000 звонков в секунду уже не "тривиальны" - сотни тысяч запросов в секунду относятся к тем проблемам производительности, с которыми вы столкнетесь, везде в вашей настройке. Вам понадобится пара серверов, балансировка нагрузки и множество других потрясающих технических средств. И одним из них будет, например, Solr;)

Ответ 2

  • Создание временных таблиц на диске относительно дорого. В вашем сценарии это звучит, как будто это будет медленнее, чем это стоит.
  • Обычно полезно создавать временные таблицы в памяти. Но вам нужно знать, что у вас достаточно памяти в любое время. Если вы планируете поддерживать столько запросов в секунду, это не очень хорошее решение.
  • MySQL имеет полнотекстовый поиск. Это хорошо для небольших систем. Вероятно, это будет намного лучше, чем ваша временная таблица и JOIN. Но если вы хотите поддерживать тысячи запросов в секунду, я бы не рекомендовал его. Он может потреблять слишком много вашей общей производительности базы данных. Кроме того, вы вынуждены использовать MyISAM для хранения, который может иметь свои проблемы в вашем сценарии.
  • Для столь многих поисков вы захотите разгрузить работу в другую систему. Уже существует множество поисковых систем с подсчетом. Взгляните на ElasticSearch, Solr/Lucene, Redis и т.д.

Ответ 3

Из кода, который вы даете, я действительно не думаю, что нужны таблицы tmp, а также поиск FULLTEXT. Но... о производительности таблицы tmp:

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

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

select images.* from images where name in ('some', 'search', 'query')

?