Может ли этот запрос SQLite выполняться намного быстрее?

У меня есть база данных, представляющая метаданные камеры безопасности NVR. Для каждого 1-минутного сегмента видеоролика имеется 26-байтовая строка recording. (Если вам интересно, проект doc находится в процессе здесь.) Мои пределы дизайна - 8 камер, 1 год (~ 4 миллиона строк, полмиллиона на камеру). Я проверил некоторые данные для проверки производительности. Этот запрос медленнее, чем я ожидал:

select
  recording.start_time_90k,
  recording.duration_90k,
  recording.video_samples,
  recording.sample_file_bytes,
  recording.video_sample_entry_id
from
  recording
where
  camera_id = ?
order by
  recording.start_time_90k;

Это просто сканирование всех данных для камеры с использованием индекса для фильтрации других камер и заказа. Индекс выглядит следующим образом:

create index recording_camera_start on recording (camera_id, start_time_90k);

explain query plan выглядит так, как ожидалось:

0|0|0|SEARCH TABLE recording USING INDEX recording_camera_start (camera_id=?)

Строки довольно малы.

$ sqlite3_analyzer duplicated.db
...

*** Table RECORDING w/o any indices *******************************************

Percentage of total database......................  66.3%
Number of entries................................. 4225560
Bytes of storage consumed......................... 143418368
Bytes of payload.................................. 109333605   76.2%
B-tree depth...................................... 4
Average payload per entry......................... 25.87
Average unused bytes per entry.................... 0.99
Average fanout.................................... 94.00
Non-sequential pages.............................. 1            0.0%
Maximum payload per entry......................... 26
Entries that use overflow......................... 0            0.0%
Index pages used.................................. 1488
Primary pages used................................ 138569
Overflow pages used............................... 0
Total pages used.................................. 140057
Unused bytes on index pages....................... 188317      12.4%
Unused bytes on primary pages..................... 3987216      2.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4175533      2.9%

*** Index RECORDING_CAMERA_START of table RECORDING ***************************

Percentage of total database......................  33.7%
Number of entries................................. 4155718
Bytes of storage consumed......................... 73003008
Bytes of payload.................................. 58596767    80.3%
B-tree depth...................................... 4
Average payload per entry......................... 14.10
Average unused bytes per entry.................... 0.21
Average fanout.................................... 49.00
Non-sequential pages.............................. 1            0.001%
Maximum payload per entry......................... 14
Entries that use overflow......................... 0            0.0%
Index pages used.................................. 1449
Primary pages used................................ 69843
Overflow pages used............................... 0
Total pages used.................................. 71292
Unused bytes on index pages....................... 8463         0.57%
Unused bytes on primary pages..................... 865598       1.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 874061       1.2%

...

Мне хотелось бы что-то вроде этого (может быть, только месяц за раз, а не полный год), чтобы запускаться каждый раз, когда на какой-то веб-сайт попадает, поэтому я хочу, чтобы он был довольно быстрым. Но на моем ноутбуке это занимает большую часть секунды, и на малине Pi 2 я бы хотел поддержать, это слишком медленно. Время (в секундах) ниже; он связан с CPU (пользователь + sys time ~ = real time):

laptop$ time ./bench-profiled
trial 0: time 0.633 sec
trial 1: time 0.636 sec
trial 2: time 0.639 sec
trial 3: time 0.679 sec
trial 4: time 0.649 sec
trial 5: time 0.642 sec
trial 6: time 0.609 sec
trial 7: time 0.640 sec
trial 8: time 0.666 sec
trial 9: time 0.715 sec
...
PROFILE: interrupts/evictions/bytes = 1974/489/72648

real    0m20.546s
user    0m16.564s
sys     0m3.976s
(This is Ubuntu 15.10, SQLITE_VERSION says "3.8.11.1")

raspberrypi2$ time ./bench-profiled
trial 0: time 6.334 sec
trial 1: time 6.216 sec
trial 2: time 6.364 sec
trial 3: time 6.412 sec
trial 4: time 6.398 sec
trial 5: time 6.389 sec
trial 6: time 6.395 sec
trial 7: time 6.424 sec
trial 8: time 6.391 sec
trial 9: time 6.396 sec
...
PROFILE: interrupts/evictions/bytes = 19066/2585/43124

real    3m20.083s
user    2m47.120s
sys 0m30.620s
(This is Raspbian Jessie; SQLITE_VERSION says "3.8.7.1")

Я, скорее всего, в конечном итоге сделаю какие-то денормализованные данные, но сначала я хотел бы посмотреть, могу ли я получить этот простой запрос для выполнения, а также он может. Мой тест довольно прост; он заранее готовит выражение, а затем перебирает это:

void Trial(sqlite3_stmt *stmt) {
  int ret;
  while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) ;
  if (ret != SQLITE_DONE) {
    errx(1, "sqlite3_step: %d (%s)", ret, sqlite3_errstr(ret));
  }
  ret = sqlite3_reset(stmt);
  if (ret != SQLITE_OK) {
    errx(1, "sqlite3_reset: %d (%s)", ret, sqlite3_errstr(ret));
  }
}

Я сделал профиль CPU с gperftools. Изображение:

$ google-pprof bench-profiled timing.pprof
Using local file bench-profiled.
Using local file timing.pprof.
Welcome to pprof!  For help, type 'help'.
(pprof) top 10
Total: 593 samples
     154  26.0%  26.0%      377  63.6% sqlite3_randomness
     134  22.6%  48.6%      557  93.9% sqlite3_reset
      83  14.0%  62.6%       83  14.0% __read_nocancel
      61  10.3%  72.8%       61  10.3% sqlite3_strnicmp
      41   6.9%  79.8%       46   7.8% sqlite3_free_table
      26   4.4%  84.1%       26   4.4% sqlite3_uri_parameter
      25   4.2%  88.4%       25   4.2% llseek
      13   2.2%  90.6%      121  20.4% sqlite3_db_config
      12   2.0%  92.6%       12   2.0% __pthread_mutex_unlock_usercnt (inline)
      10   1.7%  94.3%       10   1.7% __GI___pthread_mutex_lock

Это выглядит довольно странно, и я надеюсь, что его можно улучшить. Может быть, я делаю что-то немое. Я особенно скептически отношусь к sqlite3_randomness и sqlite3_strnicmp операции:

docs say sqlite3_randomness используется для вставки rowids в некоторых случаях, но я просто делаю запрос select. Зачем это использовать сейчас? Из скейминга исходного кода sqlite3 я вижу, что он используется в select для sqlite3ColumnsFromExprList, но это похоже на то, что было бы при подготовке заявления. Я делаю это один раз, а не в той части, которая сравнивается.
strnicmp - для сравнения строк без учета регистра. Но каждое поле в этой таблице является целым числом. Зачем использовать эту функцию? Что он сравнивает?
и вообще, я не знаю, почему sqlite3_reset будет дорогим или почему он будет вызван из sqlite3_step.

Схема:

-- Each row represents a single recorded segment of video.
-- Segments are typically ~60 seconds; never more than 5 minutes.
-- Each row should have a matching recording_detail row.
create table recording (
  id integer primary key,
  camera_id integer references camera (id) not null,

  sample_file_bytes integer not null check (sample_file_bytes > 0),

  -- The starting time of the recording, in 90 kHz units since
  -- 1970-01-01 00:00:00 UTC.
  start_time_90k integer not null check (start_time_90k >= 0),

  -- The duration of the recording, in 90 kHz units.
  duration_90k integer not null
      check (duration_90k >= 0 and duration_90k < 5*60*90000),

  video_samples integer not null check (video_samples > 0),
  video_sync_samples integer not null check (video_samples > 0),
  video_sample_entry_id integer references video_sample_entry (id)
);

Я загрузил тестовые данные + тестовую программу; вы можете скачать его здесь.

Изменить 1:

Ahh, просматривая код SQLite, я вижу ключ:

int sqlite3_step(sqlite3_stmt *pStmt){
  int rc = SQLITE_OK;      /* Result from sqlite3Step() */
  int rc2 = SQLITE_OK;     /* Result from sqlite3Reprepare() */
  Vdbe *v = (Vdbe*)pStmt;  /* the prepared statement */
  int cnt = 0;             /* Counter to prevent infinite loop of reprepares */
  sqlite3 *db;             /* The database connection */

  if( vdbeSafetyNotNull(v) ){
    return SQLITE_MISUSE_BKPT;
  }
  db = v->db;
  sqlite3_mutex_enter(db->mutex);
  v->doingRerun = 0;
  while( (rc = sqlite3Step(v))==SQLITE_SCHEMA
         && cnt++ < SQLITE_MAX_SCHEMA_RETRY ){
    int savedPc = v->pc;
    rc2 = rc = sqlite3Reprepare(v);
    if( rc!=SQLITE_OK) break;
    sqlite3_reset(pStmt);
    if( savedPc>=0 ) v->doingRerun = 1;
    assert( v->expired==0 );
  }

Похоже, что sqlite3_step вызывает sqlite3_reset при изменении схемы. (Запись в FAQ) Я не знаю, почему произошла смена схемы, поскольку моя инструкция была подготовлена, хотя...

Изменить 2:

Я загрузил SQLite 3.10.1 "amalgation" и скомпилировал его с помощью отладочных символов. Сейчас я получаю совсем другой профиль, который не выглядит таким странным, но не быстрее. Возможно, странные результаты, которые я видел раньше, были связаны с идентичным сгибанием кода или чем-то еще.

Изменить 3:

Попытка Ben кластеризованного индексного решения ниже, это примерно на 3,6 раза быстрее. Я думаю, что это лучшее, что я собираюсь сделать с этим запросом. Производительность процессора SQLite составляет около 700 МБ/с на моем ноутбуке. Не переписывая его, чтобы использовать JIT-компилятор для своей виртуальной машины или некоторых таких, я не собираюсь делать ничего лучше. В частности, я думаю, что причудливые звонки, которые я видел в моем первом профиле, на самом деле не происходили; gcc должен иметь письменную вводящую в заблуждение информацию об отладке из-за оптимизации или чего-то еще.

Даже если производительность ЦП будет улучшена, эта пропускная способность больше, чем мое хранилище может делать теперь холодное чтение, и я думаю, что то же самое верно для Pi (у которого есть ограниченная шина USB 2.0 для SD-карты).

$ time ./bench
sqlite3 version: 3.10.1
trial 0: realtime 0.172 sec cputime 0.172 sec
trial 1: realtime 0.172 sec cputime 0.172 sec
trial 2: realtime 0.175 sec cputime 0.175 sec
trial 3: realtime 0.173 sec cputime 0.173 sec
trial 4: realtime 0.182 sec cputime 0.182 sec
trial 5: realtime 0.187 sec cputime 0.187 sec
trial 6: realtime 0.173 sec cputime 0.173 sec
trial 7: realtime 0.185 sec cputime 0.185 sec
trial 8: realtime 0.190 sec cputime 0.190 sec
trial 9: realtime 0.192 sec cputime 0.192 sec
trial 10: realtime 0.191 sec cputime 0.191 sec
trial 11: realtime 0.188 sec cputime 0.188 sec
trial 12: realtime 0.186 sec cputime 0.186 sec
trial 13: realtime 0.179 sec cputime 0.179 sec
trial 14: realtime 0.179 sec cputime 0.179 sec
trial 15: realtime 0.188 sec cputime 0.188 sec
trial 16: realtime 0.178 sec cputime 0.178 sec
trial 17: realtime 0.175 sec cputime 0.175 sec
trial 18: realtime 0.182 sec cputime 0.182 sec
trial 19: realtime 0.178 sec cputime 0.178 sec
trial 20: realtime 0.189 sec cputime 0.189 sec
trial 21: realtime 0.191 sec cputime 0.191 sec
trial 22: realtime 0.179 sec cputime 0.179 sec
trial 23: realtime 0.185 sec cputime 0.185 sec
trial 24: realtime 0.190 sec cputime 0.190 sec
trial 25: realtime 0.189 sec cputime 0.189 sec
trial 26: realtime 0.182 sec cputime 0.182 sec
trial 27: realtime 0.176 sec cputime 0.176 sec
trial 28: realtime 0.173 sec cputime 0.173 sec
trial 29: realtime 0.181 sec cputime 0.181 sec
PROFILE: interrupts/evictions/bytes = 547/178/24592

real    0m5.651s
user    0m5.292s
sys     0m0.356s

Возможно, мне придется сохранить некоторые денормализованные данные. К счастью, я думаю, что могу просто сохранить его в своей прикладной ОЗУ, учитывая, что он не будет слишком большим, запуск не должен быть невероятно быстрым, и только один процесс когда-либо записывается в базу данных.

Ответ 1