Тайм-аут сервера Mysql на определенные запросы локации
Я программирую поиск с помощью ZF3 и модуля DB.
Каждый раз, когда я использую более 1 короткого ключевого слова — например, «49» и «am» или «1» и «is», я получаю эту ошибку:
Statement could not be executed (HY000 - 2006 - MySQL server has gone away)
Использование более длинных ключевых слов работает отлично, если я не использую 2 или более коротких ключевых слов.
Проблема возникает только на живом сервере, он нормально работает на локальном тестовом сервере.
Таблица проекта содержит ~ 2200 строк со всеми видами данных. Таблица project_search содержит 17000 строк с несколькими записями для каждого проекта, каждая из которых выглядит следующим образом:
id, projectid, searchtext
Столбец поискового текста полнотекстовый. Вот соответствующая часть кода php:
$sql = new Sql($this->db);
$select = $sql->select(['p'=>'projects']);
if(isset($filter['search'])) {
$keywords = preg_split('/\s+/', trim($filter['search']));
$join = $sql->select('project_search');
$join->columns(['projectid' => new Expression('DISTINCT(projectid)')]);
$join->group("projectid");
foreach($keywords as $keyword) {
$join->having(["LOCATE('$keyword', GROUP_CONCAT(searchtext))"]);
}
$select->join(
["m" => $join],
"m.projectid = p.id",
['projectid'],
\Zend\Db\Sql\Select::JOIN_RIGHT
);
}
Вот результирующий запрос:
SELECT p.*, m.projectid
FROM projects AS p
INNER JOIN (
SELECT projectid
FROM project_search
GROUP BY projectid
HAVING LOCATE('am', GROUP_CONCAT(searchtext))
AND LOCATE('49', GROUP_CONCAT(searchtext))
) AS m
ON m.projectid = p.id
GROUP BY p.id
ORDER BY createdAt DESC
Я переписал запрос, используя «MATCH (searchtext) AGAINST (‘$ keyword)» и «searchtext LIKE’% keyword% ‘с тем же результатом.
Проблема, кажется, с живым сервером MySQL, как я могу отладить это?
[РЕДАКТИРОВАТЬ]
Заметив, что ошибка возникла только в специальном представлении, в котором были другие связанные с поиском запросы — каждый из которых использовал несколько объединений (1 объединение / ключевое слово) — я объединил эти запросы, и ошибка исчезла. Количество запросов, казалось, убило сервер.
Решение
Попробуйте рефакторинг вашего внутреннего запроса следующим образом.
SELECT a.projectid
FROM (
SELECT DISTINCT projectid
FROM projectsearch
WHERE searchtext LIKE '%am%'
) a
JOIN (
SELECT DISTINCT projectid
FROM projectsearch
WHERE searchtext LIKE '%49%'
) b ON a.projectid = b.projectid
Это должно вернуть вам тот же набор projectid значения как ваш внутренний запрос. Это дает каждому projectid значение, которое соответствует searchtext для обоих поисковых терминов, даже если эти термины отображаются в разных строках project_search, Это то, что ваш запрос делает поиск GROUP_CONCAT() выход.
Попробуйте создать индекс на (searchtext, projectid), Использование column LIKE '%sample' означает, что вы не сможете получить произвольный доступ к этому индексу, но два запроса в соединении могут все же сканировать индекс, что быстрее, чем сканирование таблицы. Чтобы добавить этот индекс, используйте эту команду.
ALTER TABLE project_search ADD INDEX project_search_text (searchtext, projectid);
Попробуйте сделать это в клиентской программе MySQL (например, phpmyadmin), а не прямо из вашей программы php.
Затем, используя клиент MySQL, протестируйте внутренний запрос. Посмотрите, сколько времени это займет. использование EXPLAIN SELECT .... чтобы получить объяснение того, как MySQL обрабатывает запрос.
Возможно, ваши короткие ключевые слова возвращают смехотворно большое количество совпадений и каким-то образом перегружают вашу систему. В этом случае вы можете положить LIMIT 1000 пункт или что-то подобное в конце вашего внутреннего запроса. Это не вероятно, однако. 17 килограмм это не большое количество.
Если это не поможет, ваш производственный сервер MySQL, вероятно, неправильно настроен или поврежден. Если бы я был вами, я бы позвонил в службу технической поддержки вашего хостинга, как-нибудь обойтись с агентом первой линии поддержки (который ничего не узнает, кроме как «перезагрузить компьютер» и другие подобные глупости), и сообщить им точное время, когда вы получили «ушел» сообщение. Они смогут проверить журналы.
Pro tip: Я уверен, что вы знаете подводные камни использования LIKE '%text%' в качестве условия поиска. Это не масштабируется, потому что это не sargable: он не может получить произвольный доступ к индексу. Если вы можете изменить свою систему, это стоит ваших усилий и времени.
Другие решения
Вы можете попробовать / проверить, чтобы получить более конкретную ошибку:
BEGIN TRY
BEGIN TRANSACTION
--Insert Your Queries Here--
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
Хотя, поскольку вы говорите о коротких словах и полнотексте, мне кажется, это должно быть связано с StopWords.
Попробуйте выполнить этот запрос как с вашего сервера dev, так и с рабочего сервера, и проверьте, есть ли различия:
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
Также проверьте текстовый файл my.ini (если это файл конфигурации), если они установлены на:
ft_stopword_file = ""
ft_min_word_len = 1
Как указано в моей РЕДАКТИРОВАТЬ, проблема была не в запросе из исходного Вопроса, а в некоторых других запросах, использующих параметр поиска. Каждый запрос имеет следующую часть:
if(isset($filter['search'])) {
$keywords = preg_split('/\s+/', trim($filter['search']));
$field = 1;
foreach($keywords as $keyword) {
$join = $sql->select('project_search');
$join->columns(["pid$field" => 'projectid']);
$join->where(["LOCATE('$keyword', searchtext)"]);
$join->group("projectid");
$select->join(
["m$field" => $join],
"m$field.pid$field = p.id");
$field++;
}
}
Это привело к большому количеству запросов с множеством результатов, которые в конечном итоге убили сервер MySQL. Я объединил эти запросы в первый, и ошибка исчезла.