Какое же утро без хорошего эксперимента. Берем встроенный сервер
Firebird 2.5.3 и чистую БД:
Page size 8192
ODS version 11.2
Page buffers 75
Database dialect 3
Attributes force write
Создаем две идентичных по структуре таблицы:
create table t1 (i integer not null)
create table t2 (i integer not null)
Одинаково заполняем каждую из них миллионом случайных значений:
execute block
as
declare variable i integer = 0;
declare variable v integer = 0;
begin
while (i < 1000000) do
begin
v = round(rand() * 2000000000);
insert into t1 values (:v);
insert into t2 values (:v);
i = :i + 1;
end
end
Для таблицы
t1 создаем индекс:
create index t1x on t1 (i)
Делаем коммит транзакции и переподключение к базе данных. Организуем скан таблицы
t1 и случайным образом удаляем четверть записей через поиск по индексированному полю:
execute block
as
declare variable i integer;
begin
for select i from t1 into :i
do
begin
if (rand() < 0.25) then
delete from t1 where i = :i;
end
end
Статистика выполнения:
Execute : 10 717.00 ms
Read : 239 855
Writes : 6 250
Fetches : 4 264 681
Marks : 250 286
IR : 250 286
NIR : 999 934
Deletes : 250 286
Не совсем понятно почему NIR не миллион. Все остальное вполне логично. Коммит транзакции и еще раз удаляем четверть, теперь от оставшихся записей:
Execute : 582 243.00 ms
Read : 414 519
Writes : 246 390
Fetches : 5 712 020
Marks : 944 206
IR : 187 214
NIR : 749 693
Deletes : 187 214
Expunges : 250 286
Почти
10 минут! Обратите внимание на сумасшедшее количество Writes и Marks. Сборка мусора и перестроение индекса?
Далее, берем таблицу t2 и удаляем из нее четверть записей, но с помощью курсора и конструкции
CURRENT OF:
execute block
as
declare variable c cursor for
(select i from t2);
declare variable i integer;
begin
open c;
while (1=1) do
begin
fetch c into :i;
if (row_count = 0) then
leave;
if (rand() < 0.25) then
delete from t2 where current of c;
end
close c;
end
Статистика:
Execute : 8 362.00 ms
Read : 6 138
Writes : 6 064
Fetches : 2 762 582
Marks : 250 100
NIR : 1 000 000
Deletes : 250 100
Обратите внимание, насколько меньше чтений по сравнению с удалением по индексированному полю. Выполнение на 20% быстрее за счет того, что не надо дергать индекс.
Коммит транзакции и еще раз удаляем четверть записей:
Execute : 8 252.00 ms
Read : 6 137
Writes : 6 067
Fetches : 3 587 315
Marks : 693 789
NIR : 749 900
Deletes : 187 455
Expunges : 250 100
Выполнился даже быстрее чем первый запрос, несмотря на сборку мусора, и в 70 (!!!) раз быстрее чем запрос с удалением по индексированному полю. Непонятно почему так велико значение Marks, ведь удаляется всего 187 455 записей.
Подсчитаем количество записей в первой таблице:
select count(*) from t1
Статистика:
Execute : 493 697.00 ms
Read : 181 339
Writes : 181 084
Fetches : 3 009 620
Marks : 561 642
NIR : 562 500
Expunges : 187 214
Все ясно, сборка мусора напоролась на индекс. 8 минут на кофе.
Теперь считаем во второй:
select count(*) from t2
Статистика:
Execute : 6 443.00 ms
Read : 6 137
Writes : 6 064
Fetches : 2 261 901
Marks : 374 910
NIR : 562 445
Expunges : 187 455
Упс! Мы ожидали увидеть сравнимые цифры по времени выполнения для первой и второй таблицы, но разница составила 76 (!!!) раз. Все из-за наличия в первой таблице индекса, который при подсчете количества никак не используется, но катастрофически тормозит сборку мусора.
Попытаемся выяснить как влияет наличие индекса на выполнение операции
CURRENT OF. Возвращаемся к исходной базе данных. Будем удалять записи из таблицы t1 с помощью конструкции
CURRENT OF:
Execute : 8 050.00 ms
Read : 6 138
Writes : 6 064
Fetches : 2 765 717
Marks : 251 145
NIR : 1 000 000
Deletes : 251 145
В одно время с удалением из таблицы, по которой нет индекса.
Второй цикл удаления:
Execute : 688 480.00 ms
Read : 240 628
Writes : 240 341
Fetches : 4 592 695
Marks : 945 817
NIR : 748 855
Deletes : 186 248
Expunges : 251 145
Самое большое время, которое нам пришлось наблюдать сегодня. Сборка мусора и индекс явно не дружат друг с другом.
Для оценки влияния сборки мусора повторим все операции с самого начала на исходной базе данных с флагом подключения
no_garbage_collect. Первое удаление из таблицы t1 (поиск по индексированному полю):
Execute : 12 418.00 ms
Read : 239 735
Writes : 6 231
Fetches : 4 260 434
Marks : 249 809
NIR : 999 939
IR : 249 809
Deletes : 249 809
Странно, но NIR снова не миллион. Куда деваются около шестидесяти чтений непонятно.
Второе удаление:
Execute : 9 563.00 ms
Read : 181 842
Writes : 6 189
Fetches : 3 702 914
Marks : 187 849
NIR : 750 162
IR : 187 849
Deletes : 187 849
Записей стало меньше и время сканирования и удаления уменьшилось соответственно.
Для второй таблицы удаление через курсор. Первый проход:
Execute : 8 112.00 ms
Read : 6 138
Writes : 6 064
Fetches : 2 764 184
Marks : 250 634
NIR : 1 000 000
Deletes : 250 634
Второй:
Execute : 8 034.00 ms
Read : 6 137
Writes : 6 064
Fetches : 2 574 131
Marks : 187 283
NIR : 749 366
Deletes : 187 283
Обратите внимание, что цифры идентичны тем, которые мы имели при включенной сборке мусора. Т.е. нет индекса и сборка мусора не проблема.
Получаем количество записей для первой таблицы:
Execute : 546.00 ms
Read : 6 137
Writes : 2
Fetches : 2 012 281
Marks : 0
NIR : 562 342
Для второй:
Execute : 531.00 ms
Read : 6 137
Writes : 2
Fetches : 2 012 281
Marks : 0
NIR : 562 083
Выводы:
Нижесказанное применимо к частным случаям массовой обработки данных.
Если по таблице нет индексов, то включение/выключение сборки мусора практически не влияет на производительность.
Если по таблице созданы индексы, то сборка мусора способна радикально, в десятки и сотни раз, замедлить ход процесса.
В нашем примере CURRENT OF был на 20-25% быстрее чем поиск по индексированному полю.
CURRENT OF может применяться на таблицах, где индексов нет вообще. Совершенно очевидно, что удаление с поиском по неиндесированному полю выполнялось бы в нашем случае часами, если не сутками.
Так и осталось загадкой, почему количество неиндексированных чтений при скане таблицы с индексом дважды получилось меньше правильного по теории миллиона.
