Производительность сортировки и выборки в порядке индекса
Давайте попробуем на практике проверить тезисы, которые были выдвинуты в конце предыдущего раздела. Для сравнения взята таблица с 14 миллионами записей (средний размер записи 117 байт, общий объем таблицы 1.86 гигабайт). По данной таблице есть 2 индекса с разной селективностью (информация скопирована из IBAnalyst. тест проводился на Firebird 1.5.2 SS)
Код: Выделить всё
Index Depth Keys KeyLen MaxDup TotalDup Uniques Selectivity Size, mb
A 3 14287963 0.00 4827799 14286515 1448 0.0006906 82.03
B 3 14287963 1.00 100 6573235 7714728 0.0000001 99.52
У индекса B максимальное количество одинаковых ключей равно 100, поэтому относительно общего числа ключей этот индекс можно считать почти уникальным.
Для начала имеет смысл выполнить запрос
Код: Выделить всё
select count(*) from table
Код: Выделить всё
Prepare time = 0ms
Execute time = 42s 500ms
Avg fetch time = 42 500.00 ms
Current memory = 1 095 520
Max memory = 19 360 784
Memory buffers = 2 048
Reads from disk to cache = 118 792
Writes from cache to disk = 6
Fetches from cache = 28 814 893
Код: Выделить всё
SELECT A, COUNT(A)
FROM TABLE
GROUP BY A
PLAN (TABLE ORDER A)
Prepare time = 0ms
Execute time = 45m 55s 469ms
Avg fetch time = 153 081.61 ms
Current memory = 19 225 868
Max memory = 19 275 704
Memory buffers = 2 048
Reads from disk to cache = 3 733 434
Writes from cache to disk = 0
Fetches from cache = 42 869 143
Код: Выделить всё
SELECT B, COUNT(B)
FROM TABLE
GROUP BY B
PLAN (TABLE ORDER B)
Prepare time = 0ms
Execute time = 63ms
Avg fetch time = 3.50 ms
Current memory = 1 105 516
Max memory = 19 360 784
Memory buffers = 2 048
Reads from disk to cache = 48
Writes from cache to disk = 0
Fetches from cache = 12 495
Одновременно мы имеем обратный эффект - уникальных значений в индексе A всего 1448, поэтому после того как запрос выполнился, он, фактически, выдал нам сразу весь результат. Второй запрос, с выборкой в порядке индекса B, несмотря на мгновенность выполнения выдает только часть записей в grid, поэтому то же самое считывание страниц и их вытеснение из кэша может начаться по мере того, как пользователь будет нажимать в grid кнопку PageDn (или стрелку вниз). То есть, первый запрос работает дольше, но выдает весь результат почти сразу, а второй запрос работает моментально, но будет "тормозить" по мере выдаче результата.
Теперь попробуем отключить использование индекса в первом запросе, и посмотреть - будет ли быстрее и эффективнее использование файла сортировки. Все равно этот запрос уже показал себя как "медленно выдающий результат", а явная сортировка записей как раз и будет иметь этот эффект из-за нескольких фаз выполнения такого запроса (считывание, сортировка, выдача результата).
Код: Выделить всё
SELECT A+0, COUNT(A)
FROM TABLE
GROUP BY 1
Код: Выделить всё
PLAN SORT ((A NATURAL))
------ Performance info ------
Prepare time = 0ms
Execute time = 2m 5s 485ms
Avg fetch time = 6 971.39 ms
Current memory = 1 098 708
Max memory = 19 360 784
Memory buffers = 2 048
Reads from disk to cache = 118 757
Writes from cache to disk = 0
Fetches from cache = 28 813 410
Теперь повторим запрос для столбца B, который возвращает порядка 7-ми миллионов записей (число уникальных значений в этом столбце), и для которого, понятно, умолчательного размера сортировки в памяти не хватит:
Код: Выделить всё
SELECT B+0, COUNT(B)
FROM TABLE
GROUP BY 1