Форум пользователей MySQL

simple · 07.11.2011 17:12:33

Решил сделать мини тест на производительность ON DUPLICATE KEY:
Имеется тестовая таблица:

CREATE TABLE `test_table` (

    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    `a` SMALLINT(10) UNSIGNED NOT NULL,

    `b` SMALLINT(10) UNSIGNED NOT NULL,

    `c` SMALLINT(10) UNSIGNED NOT NULL,

    `d` SMALLINT(10) UNSIGNED NOT NULL,

    PRIMARY KEY (`id`),

    UNIQUE INDEX `d` (`d`, `a`)

)

COLLATE='latin1_swedish_ci'

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

AUTO_INCREMENT=0

Данный код исполняется в ХП обернутый в транзакцию:

BEGIN

DECLARE i int;

START TRANSACTION;

set i=0;

WHILE a<20000 DO

set i=i+1;

INSERT INTO table_test SET a=1,b=1,c=1,d=1

ON DUPLICATE  KEY UPDATE с=с+1;

END WHILE;

COMMIT;

END

Среднее время выполнения 2.281 сек.

Следующий код:

BEGIN

DECLARE a int;

set i=0;

START TRANSACTION;

WHILE a<20000 DO

set i=i+1;

UPDATE table_test SET c=c+1 WHERE d=2 and a=2;

IF(!ROW_COUNT()) THEN

INSERT INTO table_test SET a=2,b=1,c=1,d=2;

END IF;

END WHILE;

COMMIT;

END

Данная конструкция выполняет обновление в среднем 1.765 сек, что на 23% эффективней чем ON DUPLICATE KEY, чем это может быть обусловлено?

Отредактированно simple (07.11.2011 17:31:51)

rgbeast · 07.11.2011 17:22:35

Код неравнозначен. Во втором примере есть дополнительно WHERE i=100

simple · 07.11.2011 17:30:04

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

Отредактированно simple (07.11.2011 17:37:22)

rgbeast · 07.11.2011 17:36:29

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

simple · 07.11.2011 17:58:18

Вы правы, переделаю скрипт чтобы было по очередное срабатывание update и insert в цикле, такой тест будет честнее

simple · 07.11.2011 19:24:42

Проверил, да при таком варианте ON DUPLICATE KEY быстрее костыля на 16%, среднее время 1.489 сек и 1.781 сек соответственно.
Делал так:

BEGIN

DECLARE i,j,q int default 0;

START TRANSACTION;

WHILE q<20000 DO

set i=i+1;

set q=q+1;

INSERT INTO tmp SET a=1,b=1,c=1,d=i

ON DUPLICATE  KEY UPDATE c=c+1;

IF(j=0) THEN set i=i-1,j=1;

ELSE set j=0;

END IF; 

END WHILE;

COMMIT;

END

при старте вставляется новая запись, следующий цикл ее обновляет, потом новая запись и.т.д идет чередование INSERT UPDATE в цикле

код для костыля:

BEGIN 

DECLARE i,j INT DEFAULT 0; 

START TRANSACTION; 

WHILE i<20000 DO 

SET i=i+1;

UPDATE tmp SET c=c+1 WHERE d=i-j; 

IF(! ROW_COUNT()) THEN 

SET j=j+1;

INSERT INTO tmp SET a=1,b=1,c=1,d=j; 

END IF; 

END WHILE; 

COMMIT; 

END

теперь хоть на душе спокойно, буду использовать ON DUBLICATE KEY (16%-18% прироста к производительности это тоже круто), а то были сомнения на счет этой конструкции, теперь вся ясно

Отредактированно simple (07.11.2011 19:32:22)

evgeny · 07.11.2011 23:05:39

С ROW_COUNT() существует некая проблема когда новое значение равно текущему значению , в таком случае mysql не переписывает его заново и просто ничего не меняет, и в таком случае ROW_COUNT() возвращает "0"

В вашем случае это не грозит, но в других случаях, конструкция IF(!ROW_COUNT()) THEN может не прокатить.

Форум пользователей MySQL

#1 07.11.2011 17:12:33

ON DUPLICATE KEY VS Костыль

#2 07.11.2011 17:22:35

Re: ON DUPLICATE KEY VS Костыль

#3 07.11.2011 17:30:04

Re: ON DUPLICATE KEY VS Костыль

#4 07.11.2011 17:36:29

Re: ON DUPLICATE KEY VS Костыль

#5 07.11.2011 17:58:18

Re: ON DUPLICATE KEY VS Костыль

#6 07.11.2011 19:24:42

Re: ON DUPLICATE KEY VS Костыль

#7 07.11.2011 23:05:39

Re: ON DUPLICATE KEY VS Костыль

Board footer