С точки зрения эффективности, насколько эффективно использовать временную таблицу 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')