Подтвердить что ты не робот

Поиск большой базы данных mysql с релевантностью

Я создаю довольно большой механизм поиска для нашей интрасети нашей компании, у нее есть 1 миллион плюс записи он работает на довольно быстром сервере, и для некоторых поисковых запросов требуется до 1 минуты.

Вот как выглядит таблица:

Table

Я попытался создать для него индекс, но кажется, что я что-то упустил, вот как показывает индекс show

idexes

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

SELECT SQL_CALC_FOUND_ROWS *
FROM `businessunit`
INNER JOIN `businessunit-postaddress` ON `businessunit`.`Id` = `businessunit-postaddress`.`BusinessUnit`
WHERE `businessunit`.`Name` LIKE 'tanto%'
ORDER BY `businessunit`.`Premium` DESC ,
CASE WHEN `businessunit`.`Name` = 'tanto'
THEN 0
WHEN `businessunit`.`Name` LIKE 'tanto %'
THEN 1
WHEN `businessunit`.`Name` LIKE 'tanto%'
THEN 2
ELSE 3
END , `businessunit`.`Name`
LIMIT 0 , 30

любая помощь очень ценится

Edit: Что задерживает этот запрос 99% упорядочивает по релевантности с wildcharacter % Когда я делаю объяснение, он говорит, используя где; используя fsort

4b9b3361

ОТВЕТЫ

Ответ 1

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

Нажмите здесь для получения более подробной информации.

Ответ 2

Похоже, что индекс не охватывает Premium, но это первый аргумент ORDER BY.

Используйте EXPLAIN your query here, чтобы выяснить план запроса и изменить индекс, чтобы удалить сканирование таблицы, как описано в http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Ответ 3

MySQL хорош для хранения данных, но не очень хорош, когда дело доходит до быстрого поиска на основе текста.

Помимо Sphinx, который уже был предложен, я рекомендую две фантастические поисковые системы:

  • Solr с http://pecl.php.net/package/solr - очень популярная поисковая система. Используется для массовых служб, таких как NetFlix.

  • Эластичный поиск - относительно новое программное обеспечение, но с очень активным сообществом и большим уважением.

Оба решения основаны на одной и той же библиотеке Apache Lucene

Ответ 4

Большинство сайтов с поддержкой поисковых систем используют FULL-TEXT-SEARCH.   Это будет очень быстро сравниваться с select и LIKE...   Я добавил один пример и некоторые ссылки...   Я думаю, это будет полезно для вас...   В этом полнотекстовом поиске есть некоторые условия...

ШАГ: 1

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);

ШАГ: 2

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

ШАГ: 3
Натуральный язык Полнотекстовые поисковые запросы:

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');

Логические полнотекстовые поисковые запросы

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

Перейдите по ссылкам viralpatel.net, devzone.zend.com, sqlmag.com, colorado.edu, en.wikipedia.org

Ответ 5

Если "ORDER BY" на самом деле является узким местом, прямолинейным решением будет удаление логики "ORDER BY" из вашего запроса и повторная реализация сортировки непосредственно в вашем коде приложения с использованием сортировки по С#. К сожалению, это означает, что вам также придется переместить свою разбивку на страницы в ваше приложение, так как вам нужно будет получить полный набор результатов, прежде чем вы сможете сортировать и разбивать на страницы. Я просто упоминаю об этом, потому что никто, похоже, пока не думал об этом.

Откровенно (как указывали другие), запрос, который вы показывали вверху, не нуждается в полнотекстовом индексировании. Единый шаблон суффикса (например, LIKE 'ABC%') должен быть очень эффективным, если в соответствующем столбце доступен индекс BTREE (а не HASH).

И, лично, у меня нет отвращения к двойной двойной подстановке (например, LIKE '% ABC%), которая, конечно же, никогда не сможет использовать индексы, если полное сканирование таблицы дешево. Вероятно, 250 000 строк это тот момент, когда я начну серьезно рассматривать полнотекстовое индексирование. 100 000, безусловно, не проблема.

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

Он грязный, как только он попадает в глаза пользователю в любом случае!

Ответ 6

Это так странный вопрос:) Попробуем понять, что он делает.

Результат составляет менее 30 строк из таблицы "businessunit" с некоторыми условиями.

Первое условие - это внешний ключ таблицы "businessunit-postaddress".
Проверьте, есть ли указатель в столбце businessunit-postaddress. BusinessUnit.

Второй - это фильтр для возврата строк только с BusinessUnit. Name, начинающийся с 'tanto'.
Если я не ошибся, у вас очень сложный индекс "Бизнес" состоит из 11 полей!
И поле "Имя" не является первым полем этого индекса.
Таким образом, этот индекс бесполезен, когда вы запускаете запрос типа "tanto%".
У меня есть серьезные сомнения относительно необходимости этого индекса вообще. Кстати, он требует довольно больших ресурсов для его поддержания и замедления операций редактирования с этой таблицей.
Вы должны сделать индекс с единственным полем "Имя".

После фильтрации запрос сортирует результаты и делает это каким-то странным образом.
Сначала он сортируется по полю BusinessUnit. Premium - это нормально.
Однако следующие утверждения с CASE тоже бесполезны.
Вот почему.
Нуль присваивается Name = 'tanto' (точно).
Следующие строки с ними - это строки с пробелом после "tanto" - это будет после "tanto" в любом случае (кроме специальных символов), потому что пространство меньше, чем любая буква.
Следующие строки с двумя являются строками с буквами после "tanto" (включая пробел!). Эти строки будут в этом порядке также по определению.
И три "зарезервированы" для "других" строк, но вы не получите "другие" строки - помните о [WHERE BusinessUnit. Name LIKE 'tanto%'] condition.
Так что эта часть ORDER BY бессмысленна.
И в конце ORDER BY снова появляется BusinessUnit. Name...

Мой совет: вам нужно перестроить запрос с нуля, имея в виду то, что вы хотите получить.

В любом случае, я думаю, вы можете использовать

SELECT SQL_CALC_FOUND_ROWS *
FROM `businessunit`
INNER JOIN `businessunit-postaddress` ON `businessunit`.`Id` = `businessunit-postaddress`.`BusinessUnit`
WHERE `businessunit`.`Name` LIKE 'tanto%'
ORDER BY `businessunit`.`Premium` DESC,
`businessunit`.`Name`
LIMIT 0 , 30

Не забывайте об индексе в поле businessunit-postaddress. BusinessUnit!

И у меня есть сильное предположение о поле Premium. Я предполагаю, что он предназначен для хранения двоичных данных (да/нет). Таким образом, обычный (BTREE) индекс не соответствует. Вы должны использовать bitmap index.

P.S. Я не уверен, что вам действительно нужно использовать SQL_CALC_FOUND_ROWS MySQL: разбиение на страницы - SQL_CALC_FOUND_ROWS vs COUNT() - запрос

Ответ 7

Его либо полнотекстовый (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html), либо соответствие шаблону (http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html) со стороны php и mysql.

Из опыта и теории:

Преимущества полнотекстового -
1) Результаты очень актуальны, и де-лимитные символы, такие как интервал в поисковом запросе, не мешают поиску.
Недостатки полнотекстового -
1) Существуют временные слова, которые используются в качестве ограничений веб-хостингами для предотвращения избыточной загрузки данных (например, результаты поиска, содержащие слово "один" или "moz", не отображаются. И этого можно избежать, если вы используете свой собственный сервер не сохраняя задержек.
2) Если я набираю "ree", он отображает только слова, содержащие точно "ree", а не "три" или "тростник".

Преимущества сопоставления шаблонов -
1) Он не имеет никаких стоп-слов, как в полнотекстовом, и если вы ищете "ree", он отображает любое слово, содержащее "ree", например "reed" или "three", в отличие от полного текста, где только точное слово удаляется.
Недостатки соответствия шаблонов -
1) Если в ваших словах поиска используются разделители, такие как пробелы, и если эти пробелы отсутствуют в результатах, потому что каждое слово отделено от каких-либо разделителей, то оно не возвращает результат.

Ответ 8

Если аргумент LIKE не начинается с символа wildchard, как в вашем примере, оператор LIKE должен иметь возможность использовать индексы.

В этом случае оператор LIKE должен работать лучше, чем LOCATE или LEFT, поэтому я подозреваю, что изменение состояния, похожего на это, может ухудшить ситуацию, но я все же думаю, что стоит попробовать (кто знает?):

WHERE LOCATE('tanto', `businessunit`.`Name`)=1

или

WHERE LEFT(`businessunit`.`Name`,5)='tanto'

Я бы также изменил ваш заказ по предложению:

ORDER BY
  `businessunit`.`Premium` DESC ,
   CASE WHEN `businessunit`.`Name` LIKE 'tanto %' THEN 1
        WHEN `businessunit`.`Name` = 'tanto'      THEN 0
        ELSE 2 END,
   `businessunit`.`Name`

Имя должно быть LIKE 'tanto%' уже, поэтому вы можете пропустить условие (CASE никогда не вернет значение 3). Конечно, убедитесь, что поле Premium проиндексировано.

Надеюсь, что это поможет.

Ответ 9

Я думаю, вам нужно только собирать ключи, сортировать их, а затем присоединяться к последним

SELECT A.*,B.* FROM
(
    SELECT * FROM (
        SELECT id BusinessUnit,Premium
            CASE
                WHEN Name = 'tanto'      THEN 0
                WHEN Name LIKE 'tanto %' THEN 1
                WHEN Name LIKE 'tanto%'  THEN 2
                ELSE 3
            END SortOrder
        FROM businessunit Name LIKE 'tanto%'
    ) AA ORDER BY Premium,SortOrder LIMIT 0,30
) A LEFT JOIN `businessunit-postaddress` B USING (BusinessUnit);

Это все равно будет генерировать файловый порт.

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

CREATE TABLE BusinessKeys
(
    id int not null auto_increment,
    BusinessUnit int not null,
    Premium      int not null,
    SortOrder    int not null,
    PRIMARY KEY (id),
    KEY OrderIndex (Premuim,SortOrder,BusinessUnit)
);

Заполните все ключи, соответствующие

INSERT INTO BusinessKeys (BusinessUnit,Premuim,SortOrder)
SELECT id,Premium
    CASE
        WHEN Name = 'tanto'      THEN 0
        WHEN Name LIKE 'tanto %' THEN 1
        WHEN Name LIKE 'tanto%'  THEN 2
        ELSE 3
    END
FROM businessunit Name LIKE 'tanto%';

Затем, чтобы разбивать страницы, запустите LIMIT только в BusinessKeys

SELECT A.*,B.*
FROM
    (
        SELECT FROM BusinessKeys
        ORDER BY Premium,SortOrder
        LIMIT 0,30
    ) BK
    LEFT JOIN businessunit A ON BK.BusinessUnit = A.id
    LEFT JOIN `businessunit-postaddress` B ON A.BusinessUnit = B.BusinessUnit
;

CAVEAT. Я использую LEFT JOIN вместо INNER JOIN, потому что LEFT JOIN сохраняет порядок ключей с левой стороны запроса.

Ответ 10

Я прочитал ответ, чтобы использовать Sphinx для оптимизации поиска. Но в отношении моего опыта я бы посоветовал другое решение. Мы использовали Сфинкс в течение нескольких лет и имели несколько неприятных проблем с сегментационными ошибками и искаженным индексом. Возможно, Sphinx не так глючен, как несколько лет назад, но уже год мы очень довольны другим решением:

http://www.elasticsearch.org/

Большие преимущества:

  • Масштабируемость - вы можете просто добавить еще один сервер с почти нулевой конфигурацией. Если вы знаете репликацию mysql, вам понравится эта функция.
  • Скорость - Даже при большой нагрузке вы получаете хорошие результаты гораздо меньше секунды
  • Легко учиться - только зная HTTP и JSON, вы можете использовать его. Если вы являетесь веб-разработчиком, вы чувствуете себя как дома.
  • Прост в установке - он можно использовать, не касаясь конфигурации. Вам просто нужна простая Java (без Tomcat или что-то еще) и брандмауэр для блокировки прямого доступа из общедоступного
  • Хорошая интеграция с Javascript - даже инструмент, похожий на phpMyAdmin, представляет собой простую HTML-страницу с использованием Javascript: https://github.com/mobz/elasticsearch-head
  • Хорошая интеграция с PHP https://github.com/ruflin/Elastica
  • Хорошая поддержка сообщества.
  • Хорошая документация (это не дружелюбно, но она охватывает почти каждую функцию!)

Если вам нужно дополнительное решение для хранения, вы можете легко комбинировать поисковую систему с http://couchdb.apache.org/