Блог

Я настраиваю простую базу данных SQLite для хранения показаний датчиков. Таблицы будут выглядеть примерно так:

 sensors  
 - id (pk) 
 - name  
 - description
 - units  

sensor_readings  
 - id (pk)  
 - sensor_id (fk to sensors)  
 - value (actual sensor value stored here)
 - time (date/time the sensor sample was taken)
 

Приложение будет фиксировать около 100 000 показаний датчиков в месяц примерно с 30 различных датчиков, и я хотел бы сохранить все показания датчиков в базе данных как можно дольше.

Большинство запросов будет в форме

 SELECT * FROM sensor_readings WHERE sensor_id = x AND time > y AND time < z
 

Этот запрос обычно возвращает около 100-1000 результатов.

Таким образом, вопрос в том, насколько большой может быть таблица sensor_readings, прежде чем приведенный выше запрос станет слишком трудоемким (более пары секунд на стандартном ПК).

Я знаю, что одним из исправлений может быть создание отдельной таблицы sensor_readings для каждого датчика, но я бы хотел избежать этого, если в этом нет необходимости. Существуют ли какие-либо другие способы оптимизации этой схемы БД?

Если вы собираетесь использовать time его в запросах, стоит добавить к нему индекс. Это была бы единственная оптимизация, которую я бы предложил, основываясь на вашей информации.

100 000 вставок в месяц равняется примерно 2,3 в минуту, так что еще один индекс не будет слишком обременительным и ускорит ваши запросы. Я предполагаю, что это 100 000 вставок по всем 30 датчикам, а не 100 000 для каждого датчика, но, даже если я ошибаюсь, 70 вставок в минуту все равно должны быть в порядке.

Если производительность действительно становится проблемой, у вас есть возможность выгрузить более старые данные в историческую таблицу (скажем, sensor_readings_old ) и выполнять запросы только в неисторической таблице ( sensor_readings ).

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

Правильно ли вы устанавливаете индексы? Кроме того, и чтение http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html, единственный ответ: «вам придется измерить себя» — тем более, что это будет сильно зависеть от аппаратного обеспечения и от того, используете ли вы базу данных в памяти или на диске, а также от того, переносите ли вы вставки в транзакции или нет.

Тем не менее, я столкнулся с заметными задержками после пары десятков тысяч строк, но это было абсолютно неоптимизировано-из небольшого чтения у меня сложилось впечатление, что есть люди со 100 тысячами строк с правильными индексами и т. Д. у которых вообще нет никаких проблем.

SQLite теперь поддерживает индексы R-дерева ( http://www.sqlite.org/rtree.html ), идеально подходит, если вы собираетесь выполнять много запросов в диапазоне времени.

Том

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

SQLite, как правило, относительно быстр при чтении, если он одновременно обслуживает только одно приложение/пользователя. Параллелизм и блокировка могут стать проблемами при одновременном доступе к нему нескольких пользователей или приложений, а более надежные базы данных, такие как MS SQL Server, как правило, лучше работают в среде с высоким уровнем параллелизма.

Как уже говорили другие, я бы обязательно проиндексировал таблицу, если вас беспокоит скорость запросов на чтение. Для вашего конкретного случая я бы, вероятно, создал один индекс, который включал бы как идентификатор, так и время.

Вы также можете обратить внимание на скорость записи. Вставка может быть быстрой, но фиксации выполняются медленно, поэтому вы, вероятно, захотите объединить множество вставок в одну транзакцию, прежде чем нажимать фиксацию. Это обсуждается здесь: http://www.sqlite.org/faq.html#q19