Добрый день, хочу спросить совета у более опытных коллег :)
Есть база данных с компаниями, есть основная таблица с данными, есть таблица со штатом сотрудников, есть таблица с данными сотрудников, есть еще пара таблиц с дополнительными данными, всего около 6 таблиц. В основной таблице с компаниями 1.5 млн записей, в остальных от 1 до 2.5 млн
Задача: пользователь вводит ключевые слова, система должна пройтись по всем этим таблицам и найти совпадения (используются LIKE и MATCH, да я слышал что в mysql полнотекстовый поиск работает плохо, но увы, так было уже до меня), алгоритм работает таким образом, что сперва делается поиск по одному ключу, отсеивается все лишнее, далее делается поиск по следующему ключу среди того что было найдено ранее.
Перепробовал несколько подходов
1. Использовал LEFT JOIN чтоб подключить все использующиеся таблицы (способ был эфективен, однако возникла проблема с дублями результятов, попытка использовать GROUP BY и DISTINCT значительно увеличила время выполнения)
2. Использовал вложенные подзапросы
3. Разбивал запрос на куски, выполнял поиск по очереди для каждого ключа, и результат в PHP подставлял в запрос через WHERE IN(...)
Ну и другие различные вариации.
Максимум чего удалось достигнуть это 30 секунд
Если есть кто-то, кто хорошо в этом разбирается и имеет большой опыт, буду ОЧЕНЬ рад совету
Интересует следующее:
1. В целом, mysql должен адекватно себя вести на таком объеме данных? Вроде не так много, как мне кажется
2. Как вообще умные люди поступают в таких ситуациях? Оптимизируется запрос, если да, какие методы используются?
3. Или может mysql не может работать быстро при таком объеме данных (но как по мне это смешно) при выборке из многих таблиц, и необходимо использовать другие инструменты для поиска, типа ElasticSearch?
Умные люди индексируют колонки, по которым производится поиск.
Можете показать запрос?
Скорее всего уже по нему будет приблизительно видно, где и как можно сделать оптимальнее и ускорить. Без запроса, всё это - гадание на гуще.
Вместе с запросом, можете показать результат того же запроса, но поставив перед ним слово EXPLAIN, чтобы MySQL вместо его выполнения показала анализ, что MySQL будет делать с запросом. По нему будет видно потенциальные тяжёлые места.
1. Для MySQL это не большие объемы данных, если все грамотно реализовано.
2. Нужно выбирать инструмент по задаче - Ваша задача поиск данных по всем таблицам, мускуль полноценно для этого не годиться. Используйте ElasticSearch или Sphinx, все Ваши попытки выкрутится через стандартные запросы ни к чему не приведут, вот даже сейчас Вы что-то оптимизируете - станет работать быстрее, а что будет через год-два-пять когда объемы данных вырастут?
Еще находил интересную штуку - представления. На сколько я понял, это виртуальная таблица, которая формируется на основе запроса выборки из других таблиц. Вот только не понял, эта выборка постоянно хранится в памяти, что могло бы помочь ускорить работу, либо это просто механизм для упрощения работы с базой, т.е. просто в момент запроса к представлению, выполняется тот самый запрос, который использовался при создании этого представления, после чего он расширяется?
при каждом обращении к представлению выполняется запрос заложенный в него. По факту представление попросту дает более удобный интерфейс для работы с данными, и не более...
www.mysql.ru/docs/man/Fulltext_Search.html , если не хочется ставить отдельный ElasticSearch (у которого может быть немного больше возможностей для полнотнкстового поиска, которые вам в вашей задаче не пригодятся).
Средний
