Хранимая процедура mysql медленнее в 20 раз, чем стандартный запрос

У меня есть 10 таблиц с одинаковой структурой, кроме имени таблицы.

У меня есть sp (хранимая процедура), определяемая следующим образом:

 select * from table1 where (@param1 IS NULL OR [email protected])
 UNION ALL
 select * from table2 where (@param1 IS NULL OR [email protected])
 UNION ALL
 ...
 ...
 UNION ALL
 select * from table10 where (@param1 IS NULL OR [email protected])

Я вызываю sp со следующей строкой:

call mySP('test')  //it executes in 6,836s

Затем я открыл новое стандартное окно запросов. Я просто скопировал запрос выше. Затем замените @param1 на "test".

Это выполняется в 0,321 с и примерно в 20 раз быстрее, чем хранимая процедура.

Я неоднократно менял значение параметра для предотвращения кэширования результата. Но это не изменило результат. SP примерно в 20 раз медленнее, чем эквивалентный стандартный запрос.

Пожалуйста, помогите мне разобраться, почему это происходит?

Кто-нибудь сталкивался с подобными проблемами?

Я использую mySQL 5.0.51 на Windows Server 2008 R2 64 бит.

edit: Я использую Navicat для тестирования.

Любая идея будет полезна для меня.

EDIT1:

Я только что сделал тест в соответствии с ответом Бармара.

Наконец, я изменил sp, как показано ниже, только с одной строкой:

 SELECT * FROM table1 WHERE [email protected] AND [email protected]

Затем, во-первых, я выполнил стандартный запрос

 SELECT * FROM table1 WHERE col1='test' AND col2='test'  //Executed in 0.020s

После того, как я вызвал my sp:

 CALL MySp('test','test')    //Executed in 0.466s

Итак, я полностью изменил предложение, но ничего не изменилось. И я вызвал sp из командного окна mysql вместо navicat. Это дало тот же результат. Я все еще придерживаюсь этого.

my sp ddl:

 CREATE DEFINER = `myDbName`@`%`
 PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
 BEGIN
    SELECT * FROM table1 WHERE col1=param1 AND col2=param2
 END

И col1 и col2 объединяются с индексом.

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

EDIT2:

Я получил информацию о профиле запроса. Большая разница связана с "отправкой строки данных" в профильной информации SP. Отправка части данных занимает% 99 времени выполнения запроса. Я делаю тест на локальном сервере базы данных. Я не подключаюсь к удаленному компьютеру.

Информация о профиле SP

Информация о профиле запроса

Я попробовал инструкцию индекса силы, как показано ниже в моей sp. Но такой же результат.

 SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE [email protected] AND [email protected]

Я изменил sp, как показано ниже.

 EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2

Это дало такой результат:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:NULL
 key:NULL
 key_len:NULL
 ref:NULL
 rows:292004
 Extra:Using where

Затем я выполнил запрос ниже.

 EXPLAIN SELECT * FROM table1 WHERE col1='test' AND col2='test'

Результат:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:col1_co2_combined_index
 key:col1_co2_combined_index
 key_len:76
 ref:const,const
 rows:292004
 Extra:Using where

Я использую оператор FORCE INDEX в SP. Но он настаивает на том, чтобы не использовать индекс. Есть идеи? Я думаю, что я близок к концу:)

Ответ 1

Возможная проблема с набором символов? Если ваш набор символов в таблице отличается от набора символов базы данных, это может вызвать проблемы.

См. отчет об ошибке: http://bugs.mysql.com/bug.php?id=26224

[12.11.2007 21:32] Mark Kubacki По-прежнему не повезло с 5.1.22_rc - ключами вступают в процедуру в течение 36 секунд и снаружи 0.12s.

[12.11.2007 22:30] Марк Кубацки После изменения кодировок на UTF-8 (особенно для двух использованных), который используется для соединение в любом случае, ключи учитываются в сохраненных процедура!

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

Ответ 2

Просто догадаться:

Когда вы запускаете запрос вручную, выражение WHERE ('test' IS NULL or COL1 = 'test') может быть оптимизировано при анализе запроса. Парсер может видеть, что строка 'test' не является нулевой, поэтому она преобразует тест в WHERE COL1 = 'test'. И если есть индекс на COL1, это будет использоваться.

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

Попробуйте изменить процедуру:

IF @param IS NULL
THEN BEGIN
  SELECT * FROM table1
  UNION ALL
  SELECT * FROM table2
  ...
END;
ELSE BEGIN
  SELECT * FROM table1 WHERE col1 = @param
  UNION ALL
  SELECT * FROM table2 WHERE col1 = @param
  ...
END;
END IF;

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

Ответ 3

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

Это информация, которую я нашел: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

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

Это означает, что он работает как запроектированный.

Ответ 4

Я видел это поведение, но оно не было связано с набором символов.

У меня была таблица, в которой хранились иерархические данные самореференции (родитель с детьми, а у некоторых детей были собственные дети и т.д.). Поскольку parent_id должен ссылаться на первичный идентификатор (и столбец указал ограничение на этот эффект), я не мог установить родительский идентификатор в NULL или 0 (ноль), чтобы отключить дочерний элемент от родителя, поэтому я просто ссылался на него сам по себе.

Когда я пошел, чтобы запустить хранимую процедуру для выполнения рекурсивного запроса, чтобы найти всех детей (на всех уровнях) определенного родителя, запрос занял от 30 до 40 раз больше времени для запуска. Я обнаружил, что изменение запроса, используемого хранимой процедурой, чтобы исключить родительскую запись верхнего уровня (путем указания WHERE parent_id!= Id), восстановило производительность запроса.

Хранимая процедура, которую я использую, основана на том, что показано в: https://stackoverflow.com/info/27013093/recursive-query-emulation-in-mysql.