Что быстрее: char(1), integer или smallint?

Кузьменко Дмитрий, support@ibase.ru
 
Комментарий от 17.10.2003. Вопрос остался актуальным, несмотря на появление в Borland InterBase 7.x типа BOOLEAN. Этот тип данных имеет длину 4 байта, и все равно в большинстве случаев требует специальной обработки TField.GetText/SetText, если визуализация значения столбца отличается от checkbox или True/False.
Очень часто возникает необходимость хранения однобайтовых значений – 0/1, Y/N, М/Ж и т. п. Многие задаются вопросом – какой тип поля использовать в данном случае? Char(1) или smallint? А может, integer? На первый взгляд, кажется что char(1) лучше. Но если вспомнить, как IB хранит символьные данные, то окажется, что char(1) на самом деле занимает 3 байта (2 байта длины и 1 байт на символ). По этой же причине varchar(1) вообще не рассматривается, поскольку занимает вообще 5 байт (2 байта длины, 1 байт на символ, 2 байта на количество концевых пробелов). Integer занимает 4 байта, smallint соответственно 2.

Если исходить из размера типа данных, то кажется, что лучше выбрать smallint. Однако, вы увидите из теста, что это не совсем верное предположение.


Аппаратура и программное обеспечение

  • MB ASUS P5NP6, Pentium Pro 150MHz 128K cache, 128MB RAM, HDD Quantum Fireball ST 6.4Gb (IDE), BusMaster drivers.
  • Windows NT Workstation 4.0, SP4, RollUp post sp4 fix.
  • IB Database 5.5.0.742
Параметры IBCONFIG:
DATABASE_CACHE_PAGES      7500
SERVER_CLIENT_MAPPING      8192
SERVER_WORKING_SIZE_MIN  10000
SERVER_WORKING_SIZE_MAX  70000
LOCK_HASH_SLOTS             511

База данных TEST.GDB создана с размером страницы 8192 байта. Все тестовые запросы выполнялись через локальный интерфейс ("e:\test.gdb").
 

Тестовые таблицы

Основная таблица для тестов по скорости выборки и поиска:
CREATE TABLE TT (
  ID INTEGER NOT NULL PRIMARY KEY,
  C1 CHAR(1),
  I INTEGER,
  S SMALLINT)

Таблицы для определения размера хранимых данных:
CREATE TABLE C1 (C1 CHAR(1));
CREATE TABLE I (I INTEGER);
CREATE TABLE S (S SMALLINT);
 

Процедура заливки

Предварительно создан генератор idgen и объявлена функция GetRandom и SetRandSeed из randomudf. GetRandom(10) выдает случайное число от 0 до 9.
create procedure filltt (rnum integer)
as
 declare variable i integer;
 declare variable r integer;
begin
  i=1;
  while (:i < :rnum) do
    begin
      r=getrandom(10);
      insert into tt values(gen_id(idgen, 1), cast(:r as char(1)), :r, :r);
      i=:i+1;
    end
end

Перед вызовом процедуры инициализирован генератор случайных чисел – SELECT SETRANDSEED(0) FROM RDB$DATABASE – чтобы Вы могли повторить этот тест (получить идентичное распределение random).
 
Примечание. Если вы решили повторить этот тест, не забудьте перед наполнением таблицы и созданием индексов выключить Forced Writes – это сэкономит обращение к диску при записи данных.
Вызовом EXECUTE PROCEDURE FILLTT(1000000) создан 1 миллион записей (можно и порциями, например, по 100 тысяч – не имеет значения). Созданы 3 индекса по всем полям (отдельно) для каждого поля:
CREATE INDEX BYC1 ON TT (C1);
CREATE INDEX BYI ON TT (I);
CREATE INDEX BYS ON TT (S);
 

Статистика

Для чистоты эксперимента база данных установлена в режим "no reserve" (не резервировать пространство на страницах для версий записей) при помощи IB_WISQL, и сделан backup/restore. На самом деле, изначально я проводил тест на БД, у которой заполнение страниц таблиц было ~52%, т. е. при отключенном параметре "no reserve". Оказалось, что на скорость выполнения запросов это не влияет никоим образом, причем время выполнения запросов идентично вплоть до сотых секунды в обоих случаях. Параметр "no reserve" был установлен только для того, чтобы максимально точно определить разницу в объеме хранимых данных для каждого типа индексов (и таблиц C1, I, S статистика приведена в другом разделе этого документа. См. дальше). По крайней мере предполагалось, что это поможет. На самом деле получается, что "no reserve" ни на что, кроме объема базы данных, не влияет.
Database "e:\test.gdb"

Database header page information:
 Flags               0
 Checksum            12345
 Generation          13
 Page size           8192
 ODS version         9.1
 Oldest transaction  1
 Oldest active       2
 Oldest snapshot     1
 Next transaction    6
 Bumped transaction  1
 Sequence number     0
 Next attachment ID  0
 Implementation ID   16
 Shadow count        0
 Page buffers        0
 Next header page    0
 Creation date  Jul 22, 1999 17:12:53
 Attributes  no reserve

    Variable header data:
 Sweep interval:  0
 *END*
 

Database file sequence:
File e:\test.gdb is the only file

Database log page information:
 Creation date
 Log flags: 2
  No write ahead log

 Next log page: 0

    Variable log data:
 Control Point 1:
  File name:
  Partition offset: 0  Seqno: 0  Offset: 0
 Control Point 2:
  File name:
  Partition offset: 0  Seqno: 0  Offset: 0
 Current File:
  File name:
  Partition offset: 0  Seqno: 0  Offset: 0
 *END*

Analyzing database pages ...

TT (128)
    Primary pointer page: 133, Index root page: 134
    Data pages: 4362, data page slots: 4362, average fill: 91%
    Fill distribution:
  0 - 19% = 0
 20 - 39% = 0
 40 - 59% = 0
 60 - 79% = 0
 80 - 99% = 4362

    Index BYC1 (1)
 Depth: 2, leaf buckets: 737, nodes: 1000000
 Average data length: 0.00, total dup: 999990, max dup: 32707
 Fill distribution:
      0 - 19% = 0
     20 - 39% = 0
     40 - 59% = 0
     60 - 79% = 0
     80 - 99% = 737

    Index BYI (2)
 Depth: 2, leaf buckets: 737, nodes: 1000000
 Average data length: 0.00, total dup: 999990, max dup: 32707
 Fill distribution:
      0 - 19% = 0
     20 - 39% = 0
     40 - 59% = 0
     60 - 79% = 0
     80 - 99% = 737

    Index BYS (3)
 Depth: 2, leaf buckets: 737, nodes: 1000000
 Average data length: 0.00, total dup: 999990, max dup: 32707
 Fill distribution:
      0 - 19% = 0
     20 - 39% = 0
     40 - 59% = 0
     60 - 79% = 0
     80 - 99% = 737

    Index RDB$PRIMARY1 (0)
 Depth: 2, leaf buckets: 860, nodes: 1000000
 Average data length: 1.00, total dup: 0, max dup: 0
 Fill distribution:
      0 - 19% = 0
     20 - 39% = 0
     40 - 59% = 0
     60 - 79% = 0
     80 - 99% = 860

Как ясно видно из статистики, индексы по полям имеют абсолютно одинаковое количество страниц. С одной стороны это означает, что с точки зрения индекса нет разницы между char(1), integer и smallint, а с другой – что индексы не будут вносить искажения в результаты запросов.


Запросы

Параметр IBCONFIG – DATABASE_CACHE_PAGES был выбран таким (7500), чтобы постараться вместить в кэш как все страницы таблицы, так и страницы используемого индекса. Например, количество страниц таблицы – 4362, одного индекса – 737. Итого 4362+737 = 5099. Это минимум для database_cache_pages.

Поиск количества по одному значению – выборка диапазона из индекса, и просчет по нему count. (Несущественные параметры статистики убраны. Одинаковые значения статистики для разных запросов оставлены только у первого запроса)
SELECT COUNT(C1) FROM TT
WHERE C1 = 0

PLAN (TT INDEX (BYC1))

      COUNT
===========

      99878

Elapsed time= 5.52 sec
Buffers = 7500
Reads = 0
Fetches = 199843
 

SELECT COUNT(C1) FROM TT
WHERE C1 = '0'

PLAN (TT INDEX (BYC1))

      COUNT
===========

      99878

Elapsed time= 4.36 sec
 

SELECT COUNT(I) FROM TT
WHERE I = 0

PLAN (TT INDEX (BYI))

      COUNT
===========

      99878

Elapsed time= 3.24 sec
 

SELECT COUNT(S) FROM TT
WHERE S = 0

PLAN (TT INDEX (BYS))

      COUNT
===========

      99878

Elapsed time= 3.53 sec

Группировка всех значений в памяти. Параметр Reads показывает, что кэша в памяти не хватает для всех страниц таблицы и индекса, и происходит считывание с диска. Однако для всех запросов это значение одинаково, поэтому факт чтения страниц с диска можно игнорировать.
SELECT C1, COUNT(C1) FROM TT
GROUP BY C1
ORDER BY C1

PLAN (TT ORDER BYC1)

C1           COUNT
====== ===========

0            99878
1            99468
2           100207
3            99988
4            99988
5           100127
6           100265
7           100196
8           100156
9            99727

Elapsed time= 71.42 sec
Buffers = 7500
Reads = 743
Fetches = 3000750
 

SELECT I, COUNT(I) FROM TT
GROUP BY I
ORDER BY I

PLAN (TT ORDER BYI)

          I       COUNT
=========== ===========

          0       99878
          1       99468
          2      100207
          3       99988
          4       99988
          5      100127
          6      100265
          7      100196
          8      100156
          9       99727

Elapsed time= 59.80 sec
 

SELECT S, COUNT(S) FROM TT
GROUP BY S
ORDER BY S

PLAN (TT ORDER BYS)

     S       COUNT
====== ===========

     0       99878
     1       99468
     2      100207
     3       99988
     4       99988
     5      100127
     6      100265
     7      100196
     8      100156
     9       99727

Elapsed time= 59.99 sec

Битовое слияние индексов при поиске (выборке диапазона значений):
SELECT COUNT(C1) FROM TT
WHERE C1 = 0 OR C1 = 1

PLAN (TT INDEX (BYC1,BYC1))

      COUNT
===========

     199346

Elapsed time= 13.67 sec
 

SELECT COUNT(C1) FROM TT
WHERE C1 = '0' OR C1 = '1'

Elapsed time= 10.22 sec
 

SELECT COUNT(I) FROM TT
WHERE I = 0 OR I = 1

PLAN (TT INDEX (BYI,BYI))

      COUNT
===========

     199346

Elapsed time= 7.25 sec
 

SELECT COUNT(S) FROM TT
WHERE S = 0 OR S = 1

PLAN (TT INDEX (BYS,BYS))

      COUNT
===========

     199346

Elapsed time= 8.04 sec
Fetches = 398865

И последний тест – на чистый объем занимаемых данных. Посчитать в таблице TT это невозможно, поэтому я создал три отдельные таблицы, состоящие только из соответствующего поля, и перенес данные из tt в эти таблицы (insert into x select x from tt).
 
Примечание. Максимальный размер БД после всех тестов достигает 230 мегабайт (с параметром no_reserve – 144 мегабайта), а backup – до 90 мегабайт. Следовательно, для повторения теста потребуется минимум 320 мегабайт дискового пространства.
C1 (129)
    Primary pointer page: 135, Index root page: 136
    Data pages: 3437, data page slots: 3437, average fill: 93%
    Fill distribution:
  0 - 19% = 0
 20 - 39% = 1
 40 - 59% = 0
 60 - 79% = 0
 80 - 99% = 3436

I (130)
    Primary pointer page: 137, Index root page: 138
    Data pages: 3437, data page slots: 3437, average fill: 93%
    Fill distribution:
  0 - 19% = 0
 20 - 39% = 1
 40 - 59% = 0
 60 - 79% = 0
 80 - 99% = 3436

S (131)
    Primary pointer page: 139, Index root page: 140
    Data pages: 3437, data page slots: 3437, average fill: 93%
    Fill distribution:
  0 - 19% = 0
 20 - 39% = 1
 40 - 59% = 0
 60 - 79% = 0
 80 - 99% = 3436

Из статистики следует, что разница в объеме данных в миллион записей между char(1), integer и smallint отсутствует. Таким образом, даже предположение, что smallint будет занимать на диске места меньше чем integer, неверно (не говоря о char(1)).
 

Выводы

  1. Если вы храните числа в строках, никогда не производите сравнение значения поля с числом. Обрамляйте число кавычками, т. е. производите сравнение значения поля со строкой.
  2. Разницы в объеме занимаемых данных между char(1), integer и smallint нет как для таблиц, так и для индексов.
  3. Скорость обработки integer и smallint выше чем char(1), и integer обрабатывается быстрее smallint.
  4. Для хранения булевских значений integer подходит намного лучше char(1). Можно воспользоваться и smallint.
C1 (128)
Primary pointer page: 144, Index root page: 145
Data pages: 6897, data page slots: 6897, average fill: 93%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6896

I (129)
Primary pointer page: 146, Index root page: 147
Data pages: 6897, data page slots: 6897, average fill: 93%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6896

S (130)
Primary pointer page: 149, Index root page: 150
Data pages: 6897, data page slots: 6897, average fill: 93%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 6896

Подпишитесь на новости Firebird в России

Подписаться