Денис Колисниченко
SQLite – альтернатива MySQL
Три наиболее важных фактора веб-проектов – это быстрота, небольшая
ресурсоемкость и сравнительно малая себестоимость. Основная масса веб-проектов
не располагает ни кластерной архитектурой, ни выделенным сервером баз данных.
Под них не закупается дорогостоящее программное обеспечение. Типичный веб-сайт
– это нелюбимый пасынок в статье расходов.
Наибольшую популярность и распространенность
среди веб-разработчиков давно и заслуженно обрел сервер баз данных MySQL. Он
бесплатен, прост в установке, входит в состав большинства дистрибутивов Linux,
почти наверняка окажется на сервере вашего хостинг-провайдера. Это – рабочая
лошадка Web.
Долгое время поддержка работы с MySQL была по
умолчанию встроена в интерпретатор PHP. Но не все так безоблачно. Сейчас почти
повсеместно используется PHP 4-й версии, но не за горами выход следующей, пятой
версии. И вот тут ситуация может измениться.
Из-за возникающих лицензионных проблем поддержка MySQL
скорее всего не будет включена в PHP 5 по умолчанию. Так, из бета-версии PHP 5
уже удалена встроенная поддержка MySQL, зато появилась поддержка SQLite.
Замечание: речь идет о встроенной поддержке по
умолчанию. Возможность собрать PHP с поддержкой MySQL остается по-прежнему, но
требует «дополнительных телодвижений». А это, согласитесь, сделает не каждый.
MySQL всегда был ориентирован на быстроту, при
этом частично жертвуя дополнительными возможностями. Разумеется, в проектах,
предполагающих более сложную структуру базы данных, строгий контроль
целостности и сложные запросы к базе данных, стоит использовать более развитые
СУБД, например, PostgreSQL или Sybase. Но большинство малых и средних проектов,
таких как интернет-каталог, обновляемый прайс-лист или веб-форум, вполне
позволяют обойтись компактным, слегка ограниченным, но быстрым сервером баз
данных. Традиционно таким сервером и был MySQL. Подойдет ли SQLite в качестве
достойной замены?
Как любой программный продукт, SQLite обладает
своими преимуществами и недостатками. Среди его преимуществ бесплатность и
высокая производительность, помните о «трех китах» веб-программирования? Но
ничто не дается даром. Производительность SQLite достигается не столько за счет
уникального алгоритма, сколько из-за определенных ограничений его возможностей.
Традиционные системы управления базами данных
построены на архитектуре клиент-сервер. Это значит, что клиент-приложение
обращается к серверу-хранилищу данных с запросом для получения данных или для
выполнения каких-либо операций. Сервер выполняет нужные операции и возвращает
программе-клиенту результат своей работы. Такой подход предполагает возможность
размещать приложение и хранилище данных на разных компьютерах, разделяя
нагрузку на аппаратные ресурсы.
В этом смысле SQLite не является настоящим
сервером баз данных. То есть он умеет работать только с файловыми базами
данных, размещенными в виде файлов на локальном диске вашего компьютера. Для
сравнительно небольших проектов это не является критичным, но снижает
универсальность решения и лишает нас возможности разделить нагрузку на
аппаратные ресурсы между несколькими компьютерами.
Перечислю основные характеристики
SQLite:
n соответствует стандарту SQL-92. Правда, не
полностью: некоторые функции не поддерживаются, но этим «грешил» и MySQL;
n база данных (включая таблицы и индексы)
хранится в одном едином файле;
n максимальный размер файла базы данных – 2 Тб,
т.е. суммарный размер базы данных, включая таблицы данных и индексы, не может
превышать 2 Тб;
n существует реализация для платформ Linux и Windows;
n файлы баз данных имеют универсальный формат и
могут быть перенесены из одной операционной системы в другую без потерь и
дополнительных преобразований. Т.е. база данных, созданная на компьютере под
управлением ОС Linux, может быть просто скопирована на компьютер с ОС Windows и
наоборот. Это большой «плюс»;
n примерно в два раза быстрее, чем PostgreSQL и MySQL.
Результаты сравнительного тестирования рассмотрим
в конце статьи, а пока поговорим об установке и функциональных возможностях SQLite.
Установка SQLite
Пятая версия PHP обладает встроенной поддержкой SQLite, поэтому вам не нужно
производить никаких дополнительных действий по подключению SQLite к PHP5:
просто загрузите SQLite и работайте.
Скачать SQLite можно по адресу:
n http://pecl.php.net/package/SQLite
n http://www.sqlite.org
Поддержка SQLite для Windows реализована
традиционно для PHP, в виде единой библиотеки – php_sqlite.dll, скачать которую
вы можете по адресу http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll
Подключение SQLite проблем не вызывает, поэтому
подробно останавливаться на нем мы не будем. Если вы работаете под ОС Windows,
то скачайте библиотеку php_sqlite.dll, скопируйте в каталог для модулей
расширений PHP и «пропишите» его в файле конфигурации php.ini:
extension=php_sqlite.dll
Каталог модулей расширений задается директивой «extension_dir»
файла конфигурации. php.ini. По умолчанию это подкаталог «extensions» каталога,
в который вы установили PHP.
Для установки SQLite для Linux скачайте файл sqlite.tar.gz
(http://www.sqlite.org/sqlite.tar.gz).
Затем выполните следующую последовательность команд:
# Распаковываем архив в
каталог «sqlite»
tar xzf sqlite.tar.gz
# Создаем каталог для сборки
SQLite – «bld»
mkdir bld
# Переходим в этот каталог
cd bld
# Запускаем конфигуратор
../sqlite/configure
# Запускаем make для сборки SQLite
make
Указанная последовательность действий
необязательна. Например, вам необязательно создавать отдельный каталог для
сборки. Однако я настоятельно рекомендую это сделать, чтобы исходные коды не
«перемешались» с откомпилированными модулями SQLite.
Сценарий конфигуратора configure для своей работы
использует autoconf 2.50 и libtool, поэтому проследите, чтобы до запуска
конфигуратора эти пакеты были установлены в вашей системе.
В процессе работы конфигуратор проверит,
установлены ли все необходимые для сборки и работы SQLite библиотеки и
программы. Если чего-то не хватает, вы увидите соответствующее сообщение. Если
же ваша система соответствует требованиям конфигуратора, предупреждений не
появится, и вы можете запускать программу сборки «make».
После сборки SQLite запустите
сценарий install-sh для установки SQLite, вспомогательных программ и сценариев,
а также базы данных. Вспомогательными утилитами являются:
n showdb – утилита для просмотра базы данных;
n showjournal – утилита для просмотра журнала
базы данных;
n diffdb – утилита сравнения двух баз данных.
Как уже отмечалось, SQLite работает с файловыми
базами данных. При этом всю базу данных SQLite хранит в одном файле. В MySQL
используется несколько другой подход: база данных – это каталог, содержащий
файлы таблиц и индексов.
Основные функции SQLite
В SQLite существуют аналогичные MySQL константы, отвечающие
за формирование результата выборки из базы данных:
n SQLITE_ASSOC – результат будет возвращаться в
виде ассоциативного массива.
n SQL_NUM – результат будет возвращен в виде
списка, то есть ключи массива – числа.
n SQL_BOTH – двойное индексирование элементов
массива: в зависимости от вашего желания вы можете работать с массивом так,
если бы он был ассоциативным, и так, если бы он был списком.
Эти константы аналогичны константам MYSQL_ASSOC,
MYSQL_BOTH, MYSQL_NUM для MySQL.
Вот список основных функций для
работы с SQLite, приведенный в алфавитном порядке.
n sqlite_array_query – выполняет SQL-запрос и
возвращает результат в виде массива, тип которого задан вышеупомянутыми
константами;
n sqlite_busy_timeout – устанавливает тайм-аут;
n sqlite_changes – возвращает число записей,
которые были изменены в результате выполнения последнего SQL-запроса;
n sqlite_close – закрывает базу данных;
n sqlite_column – получает одно поле из записи,
то есть возвращает одну конкретно указанную колонку результата;
n sqlite_current – получает текущую запись из
результата;
n sqlite_error_string – возвращает текстовое
описание ошибки;
n sqlite_fetch_array – получает следующую запись
из массива-результата;
n sqlite_fetch_single – возвращает первую колонку
(поле) записи;
n sqlite_fetch_string –псевдоним для sqlite_fetch_single;
n sqlite_field_name – возвращает имя поля;
n sqlite_has_more – позволяет узнать, есть ли еще
записи;
n sqlite_last_error – возвращает код последней
ошибки;
n sqlite_last_insert_rowid – возвращает
идентификатор последней добавленной записи;
n sqlite_libversion – возвращает версию SQLite;
n sqlite_next – переходит к следующей записи;
n sqlite_num_fields – возвращает число полей
(колонок) в результате;
n sqlite_num_rows – возвращает число записей в
результате;
n sqlite_open – открывает базу данных;
n sqlite_popen – открывает «постоянное»
соединение с базой данных. Если база данных не существует, она будет создана;
n sqlite_query – выполняет SQL-запрос и
возвращает идентификатор результата;
n sqlite_rewind – переходит к первой записи.
Остановимся на этих функциях подробнее.
Открытие и закрытие базы данных
Для открытия базы данных используется функция sqlite_open():
resource sqlite_open ( string
filename [, int mode [, string &error_message]])
Первый параметр – это имя файла таблицы, второй –
режим доступа к таблице. По умолчанию используется режим 0666, позволяющий
читать и записывать файл всем желающим – владельцу (то есть вам), вашей группе
и всем остальным пользователям. Последний параметр – это строка, в которую
будет записано сообщение об ошибке, если базу данных невозможно открыть.
В случае успеха функция возвращает дескриптор
базы данных, иначе – значение FALSE («ложь»).
Для того чтобы закрыть базу данных, используется
функция sqlite_close():
sqlite_close( resource dbhandle
)
В качестве её аргумента следует указать
дескриптор соответствующей базы данных.
Замечание: несмотря на то, что механизм работы
предполагает автоматическое закрытие используемых ресурсов после выполнения
сценария, принудительное закрытие базы данных – хороший тон программирования.
Не следует пренебрегать этой операцией. Автоматическая «сборка мусора» и
закрытие ресурсов могут произойти не сразу. Это может породить некоторые
неожиданные коллизии, которые довольно сложно будет отлавливать. Исключение –
использование постоянного соединения с базой данных.
Еще один способ получить доступ к базе данных –
функция sqlite_popen():
resource sqlite_popen ( string
filename [, int mode [, string &error_message]])
Эта функция открывает «постоянное» соединение с
базой данных, её аргументы аналогичны аргументам функции sqlite_open().
Функция sqlite_popen() использует механизм
постоянных ресурсов PHP. Вкратце это выглядит так: вы открываете постоянное
соединение с базой данных, работаете, потом работа сценария завершается. При
этом если вы не указали принудительно, закрытия и освобождения ресурса не происходит.
То есть, при следующем запуске сценария функция popen() попытается найти
открытое постоянное соединение и, если оно существует, будет его использовать,
а не открывать базу данных заново. При частых запусках сценария это позволяет
повысить его производительность, так не тратится время на открытие базы.
Однако при большом количестве одновременных
обращений к базе данных взамен этой экономии вы можете получить накопление
открытых соединений с базой данных, т.е. увеличить нагрузку на внешние ресурсы
системы. Второй неприятный момент, с которым вы можете тут столкнуться –
некорректное завершение сценария, при котором могут оставаться потерянные
соединения с базой данных. В общем, выбор между этими двумя способами требует
вдумчивого подхода и некоторого анализа предполагаемой работы вашего
приложения.
Выполнение запросов
Для выполнения запросов к базе данных используется функция sqlite_query():
resource sqlite_query ( resource
dbhandle, string query [,int result_type])
resource sqlite_query ( string
query, resource dbhandle [,int result_type])
Обратите внимание, что функцию можно вызывать
двояко: или сначала указывать дескриптор базы данных, а потом – SQL-запрос или
наоборот. Кроме того, этой функции можно непосредственно указать необязательный
параметр, определяющий тип возвращаемого результата в виде ассоциативного,
нумерованного или смешанного массива.
Листинг 1. Открытие и
закрытие базы данных
<?php
// открываем базу данных или
завершаем выполнение с сообщением об ошибке
if ( !$db = sqlite_open('mysqlitedb',
0666, $sqliteerror)) {
die ($sqliteerror);
}
// создаем таблицу tbl
sqlite_query($db,'CREATE
TABLE tbl (bar varchar(20))');
// добавляем в таблицу новые
записи
sqlite_query($db,"INSERT
INTO tbl VALUES ('val')");
// получаем результат в виде
ассоциативного массива
$result = sqlite_query($db,'select
bar from tbl', SQLITE_ASSOC);
// выводим результат
var_dump(sqlite_fetch_array($result));
?>
Если запрос невозможно выполнить, функция sqlite_query()
возвращает FALSE.
Функции для работы с
результатом
Функция sqlite_column() используется для получения только одной колонки из
всего результата выборки:
mixed sqlite_column ( resource
result, mixed index_or_name [, bool decode_binary])
Первый параметр – это результат, второй – имя
поля или индекс, а третий параметр указывает на необходимость двоичного
кодирования.
Функция sqlite_fetch_single() возвращает первое
поле текущей записи:
string sqlite_fetch_single ( resource
result [, int result_type [, bool decode_binary]])
Первый параметр – это результат, а второй – тип
результата. Данная функция похожа на функцию sqlite_fetch_array(), но
возвращает только первое поле, а не все поля текущей записи сразу.
Листинг 2. Функция sqlite_fetch_single
<?php
if ($dbhandle = sqlite_open('mysqlitedb',
0666, $sqliteerror)) {
$sql = "SELECT id
FROM tbl WHERE id = 77";
$res = sqlite_query($dbhandle,
$sql);
if (sqlite_num_rows($res)
> 0) {
// печатаем значение «77»
echo sqlite_fetch_single($res);
}
sqlite_close($dbhandle);
}
?>
Функция sqlite_array_query() выполняет SQL-запрос
и возвращает результат в виде массива:
array sqlite_array_query ( resource
dbhandle, string query [, int result_type [, bool decode_binary]])
Первый параметр – это дескриптор базы данных,
второй – запрос, третий – тип результата (см. константы SQLite), а третий – это
флаг кодирования данных.
Функция sqlite_next() перемещает указатель
результата на следующую запись:
bool sqlite_next ( resource result)
Параметр result – это результат. Если функция
вернула FALSE, значит, достигнута последняя запись.
Функция sqlite_rewind перемещает указатель
результата на первую запись:
bool sqlite_rewind ( resource
result)
Функция sqlite_seek() переходит к записи с
указанным номером:
bool sqlite_seek ( resource result,
int rownum)
Если функция возвращает FALSE, значит, нет записи
с таким номером.
Информационные функции
Функция sqlite_changes() возвращает число записей, которые были изменены в
результате выполнения последнего SQL-запроса:
int sqlite_changes ( resource
dbhandle)
Функция sqlite_field_name() возвращает имя поля
по его номеру:
string sqlite_field_name ( resource
result, int field_index)
Функция sqlite_has_more() возвращает TRUE, если
результат еще содержит данные, иначе возвращается FALSE:
bool sqlite_has_more ( resource
result)
Функция sqlite_last_error() возвращает код
последней ошибки:
int sqlite_last_error ( resource
dbhandle)
а функция sqlite_error_string() – ее текстовое описание:
string sqlite_error_string( resource
dbhandle )
Функции sqlite_num_rows() и sqlite_num_fields()
возвращают, соответственно, число записей и полей в результате:
int sqlite_num_rows(resource dbhandle)
int sqlite_num_fields(resource
dbhandle)
Другие функции
Функция sqlite_busy_timeout() позволяет установить тайм-аут ожидания для
базы данных:
void sqlite_busy_timeout ( resource
dbhandle, int milliseconds)
Напомню, что 1 секунда – это 1000 миллисекунд.
Результаты сравнительного
тестирования
В первой части статьи, говоря об основных характеристиках SQLite, я упомянул
о том, что SQLite в два раза быстрее PostgreSQL и MySQL.
Чтобы не быть голословным,
приведу результаты тестирования. В качестве тестовой платформы использовался
компьютер следующей конфигурации:
n процессор – AMD 1.6 ГГц Athlon;
n объем оперативной памяти – 1 Гб;
n жесткий диск EIDE;
n операционная система – Red Hat Linux 7.2.
Для сравнения использовались PostgreSQL версии
7.1.3 и MySQL версии 3.23.41. Версия SQLite, используемая для тестирования, –
2.7.6.
Первый тест – вставка 1000 записей:
CREATE TABLE t1(a INTEGER, b
INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen
thousand one hundred fifty three');
INSERT INTO t1
VALUES(2,75560,'seventy five thousand five hundred sixty');
... 995 строк пропущено
INSERT INTO t1
VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty
four thousand three hundred twenty two');
INSERT INTO t1
VALUES(1000,94142,'ninety four thousand one hundred forty two');
Диаграмма 1: Результаты
первого теста (в секундах)
Второй тест – выборка без индексов:
BEGIN;
SELECT count(*), avg(b) FROM
t2 WHERE b>=0 AND b
SELECT count(*), avg(b) FROM
t2 WHERE b>=100 AND b
... 96 строк пропущено
SELECT count(*), avg(b) FROM
t2 WHERE b>=9800 AND b
SELECT count(*), avg(b) FROM
t2 WHERE b>=9900 AND b
COMMIT;
Диаграмма 2: Результаты
второго теста
Третий тест – выборка со сравнением строки:
BEGIN;
SELECT count(*), avg(b) FROM
t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM
t2 WHERE c LIKE '%two%';
... 96 строк пропущено
SELECT count(*), avg(b) FROM
t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM
t2 WHERE c LIKE '%one hundred%';
COMMIT;
Диаграмма 3: Результаты
третьего теста
Как вы видите, при использовании простых запросов
SQLite ненамного ушел от MySQL. Он существенно быстрее, чем PostgreSQL, но
сильная сторона последнего не быстродействие, а большие функциональные
возможности.
А теперь попробуем оправдать наши ожидания и
показать, что SQLite быстрее в два раза, чем MySQL. В качестве тестового
используем запрос, состоящий из 25 000 операторов обновления (UPDATE):
Четвертый тест – обновление числовых полей:
BEGIN;
UPDATE t2 SET b=468026 WHERE
a=1;
UPDATE t2 SET b=121928 WHERE
a=2;
... 24996 строк пропущено
UPDATE t2 SET b=35065 WHERE
a=24999;
UPDATE t2 SET b=347393 WHERE
a=25000;
COMMIT;
Диаграмма 4: Результаты
четвертого теста
Вот тут преимущество SQLite налицо!
Этот тест обновлял только целые поля. Согласитесь
– действие несколько надуманное и не такое уж частое. Посмотрим, что будет,
если нам нужно обновить 25 000 текстовых полей:
Пятый тест – обновление
тестовых полей:
BEGIN;
UPDATE t2 SET c='one hundred forty
eight thousand three hundred eighty two' WHERE a=1;
UPDATE t2 SET c='three hundred
sixty six thousand five hundred two' WHERE a=2;
... 24996 строк пропущено
UPDATE t2 SET c='three hundred
eighty three thousand ninety nine' WHERE a=24999;
UPDATE t2 SET c='two hundred fifty
six thousand eight hundred thirty' WHERE a=25000;
COMMIT;
Диаграмма 5: Результат пятого
теста
Если сравнивать с предыдущим тестом, в котором SQLite
проявил себя «шустрее» в 2,6 раза по сравнению с MySQL, то на этот раз SQLite
оказался «проворнее» почти в три раза (2,899).
Приведем общую таблицу результатов тестирования,
для того чтобы более наглядно оценить производительность SQLite.
Таблица 1. Общая таблица
результатов сравнительного тестирования SQLite
|
Тест
|
PostgreSQL
|
MySQL
|
SQLite
|
|
Вставка (INSERT)
1000 записей
|
4.373
|
0.114
|
0.223
|
|
Выборка (SELECT)
без индексов (100 опер.)
|
3,629
|
2,760
|
2,494
|
|
Выборка со
сравнением строки (100 опер.)
|
13,409
|
4,640
|
3,362
|
|
Обновление (UPDATE)
целых полей (25000 оп.)
|
18,797
|
8,134
|
3,104
|
|
Обновление
текстовых полей (25000 оп.)
|
48,133
|
6,982
|
2,408
|
Более подробно о сравнительных результатах
тестирования вы можете прочитать на сайте продукта: http://www.sqlite.org.
Конечно, переход на SQLite потребует некоторых
усилий и изменений. Придется отказаться от автоматического инкремента при
создании таблиц, возможности сложного индексирования здесь также не
предусмотрены.
Но в целом, исключив автоматическую поддержку
работы с MySQL, разработчики PHP предоставили нам взамен простой, быстрый и
бесплатный механизм для работы с локальными базами данных в файловом
представлении.
Eng
Rus