Почему моя пользовательская функция MySQL работает намного медленнее, чем встраивание в запрос?

Я повторно использую этот запрос SELECT для чтения целых чисел без знака, представляющих адреса IPv4, и представляю их как считываемые пользователем четкие четырехстрочные строки.

SELECT CONCAT_WS('.', 
  FLOOR(ip/POW(256,3)),
  MOD(FLOOR(ip/POW(256,2)), 256),
  MOD(FLOOR(ip/256), 256),
  MOD(ip, 256))
FROM ips;

С моими тестовыми данными этот запрос занимает 3,6 секунды.

Я думал, что создание пользовательской хранимой функции для преобразования int- > string позволит легче читать запросы и разрешать повторное использование, поэтому я сделал это:

CREATE FUNCTION IntToIp(value INT UNSIGNED)
  RETURNS char(15)
  DETERMINISTIC
  RETURN CONCAT_WS(
    '.', 
    FLOOR(value/POW(256,3)),
    MOD(FLOOR(value/POW(256,2)), 256),
    MOD(FLOOR(value/256), 256),
    MOD(value, 256)
  );

С помощью этой функции мой запрос выглядит так:

SELECT IntToIp(ip) FROM ips;

но с моими тестовыми данными требуется выполнить 13,6 секунды.

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

Я использую из MySQL MySQL версии 5.1 на Ubuntu 10.10 без изменений конфигурации.

Чтобы воспроизвести мой тест, создайте таблицу и заполните 1 221 201 строк:

CREATE TABLE ips (ip INT UNSIGNED NOT NULL);

DELIMITER //
CREATE PROCEDURE AddIps ()
BEGIN
  DECLARE i INT UNSIGNED DEFAULT POW(2,32)-1;
  WHILE (i>0) DO
    INSERT INTO ips (ip) VALUES (i);
    SET i = IF(i<3517,0,i-3517);
  END WHILE;
END//
DELIMITER ;

CALL AddIps();

Ответы

Ответ 1

С помощью этого вы можете повысить производительность:

CREATE FUNCTION IntToIp2(value INT UNSIGNED)
  RETURNS char(15)
  DETERMINISTIC
  RETURN CONCAT_WS(
    '.', 
    (value >> 24),
    (value >> 16) & 255,
    (value >>  8) & 255,
     value        & 255
  );

> SELECT IntToIp(ip) FROM ips;
1221202 rows in set (18.52 sec)

> SELECT IntToIp2(ip) FROM ips;
1221202 rows in set (10.21 sec)

Запуск исходного SELECT сразу после добавления ваших тестовых данных занял 4,78 секунды в моей системе (пример 2gB mysql 5.1 на четырехъядерном ядре (64-разрядная версия Fedora).

EDIT: Ожидается ли такая медлительность?

Да, хранимые процедуры медленны, куча величин медленнее, чем интерпретированный/скомпилированный код. Они оказываются полезными, когда вам нужно связать какую-либо логику базы данных, которую вы хотите не использовать из-за своего приложения, потому что оно выходит из определенного домена (то есть, протоколирует/выполняет административные задачи). Если хранимая функция не содержит запросов, всегда лучше писать функцию утилиты на выбранном вами языке, так как это не предотвратит повторное использование (запросов нет) и будет работать намного быстрее.

И причина, по которой в этом конкретном случае вы должны использовать функцию INET_NTOA, которая доступна и удовлетворяет вашим потребностям, как это предлагается в ответе санмая.

Ответ 2

Не изобретайте велосипед, используйте INET_NTOA():

mysql> SELECT INET_NTOA(167773449);
    -> '10.0.5.9'