MySQL как основа электронного магазина

Алексей Куленцов
akul@otamedia.com

MySQL — это сервер SQL баз данных, широко распространенный в качестве SQL-движка
сайтов Интернета благодаря удачному сочетанию пользовательских свойств, открытым
исходным кодам и хорошей технической поддержке. Вначале MySQL разрабатывали
программисты-энтузиасты, недовольные работой имевшегося сервера mSQL. В дальнейшем
для развития данного сервера была основана целая компания MySQL AB, обеспечившая
широкую поддержку данного продукта. Фирма существует за счет коммерческой технической
поддержки пользователей MySQL, сам же сервер по-прежнему бесплатен и поставляется
в исходных кодах. Исходные коды, скомпилированные модули под множество платформ
и разнообразные утилиты можно найти на сайте http://www.mysql.com
либо на каком-то из серверов-зеркал, которые в большом количестве разбросаны
по всему миру.

Исходный язык MySQL — C, что во многом определяет его слабые и сильные стороны.

Обычно MySQL рассматривается как основа для не слишком сложных, "несерьезных" проектов, не требующих высокой сохранности данных, как то: форумы, системы ведения статистики посещаемости и т. д. Автором накоплен опыт эксплуатации MySQL как основы электронного магазина, имеющего довольно большой трафик заказов. Этот опыт показывает, что MySQL вполне годится для достаточно сложных и требующих надежности проектов (работа с деньгами!), а по некоторым свойствам и превосходит своих именитых и дорогих конкурентов.

История одного проекта

Проект, благодаря которому была написана эта статья, возник так: потребовалось написать ПО для электронного магазина за достаточно короткое время, причем под платформу Microsoft Windows/IIS. В то время у автора уже имелся некоторый опыт по части создания сайтов, и было ясно, что однопользовательская и слишком уж "беззащитная" Windows (на момент принятия решения это была NT 4.0) не годится как платформа для хостинга и рано или поздно придется переходить на UNIX. С учетом этого обстоятельства в число обязательных требований к SQL-серверу была внесена многоплатформность. Кроме того, хотелось открытых исходных кодов, скорости, популярности, наличия языка программирования и бесплатности.

В качестве разумных альтернатив рассматривались Oracle, PostgreSQL и MySQL. Наиболее привлекательным кандидатом (открытые исходники, мощный язык программирования) оказался PostgreSQL, но… С трудом удалось найти порт под Windows, да и он оказался настолько "сырым" и малоизвестным, что я не решился применить его в данном проекте. Идея же сменить платформу (особенно с учетом того, что под Windows уже были куплены почтовый сервер, платежный драйвер и т. д.) исключалась.

Выбор между Oracle и MySQL оказался достаточно простым. Данный проект изначально рассматривался как не очень большой, и в результате поставляемый с открытым кодом, маленький, легко загружаемый из Интернета, быстрый и чрезвычайно легкий в установке MySQL оказался предпочтительнее мощного, но требующего больших начальных затрат продукта Oracle.

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

Язык

Одной из самых серьезных проблем MySQL по сегодняшний день остается неразвитость используемого диалекта SQL. Разработчики обещают реализовать недостающую функциональность в следующих версиях, но, глядя на исходники MySQL, понимаешь, что будет это еще не скоро. Дело в том, что написан MySQL в основном на C, в процедурно-ориентированном стиле. При этом для разборки входных выражений применяются закодированные на C процедуры. Судя по всему, это может дать прирост производительности по сравнению с применением библиотек построения компиляторов типа bison/flex за счет более эффективной "ручной" оптимизации (не секрет, что выполнение декларативных языков часто сводится к одному алгоритму: перебору с отсечением), но плата за это — высокая цена модификации входного языка. Так что пока нам придется учитывать при разработке проекта отсутствие некоторых важных составляющих.

Встроенный язык

Отсутствие UNION и подзапросов дает себя знать при конструировании запросов, а отсутствие встроенных процедур и триггеров вынуждает реализовывать бизнес-логику системы не средствами сервера базы данных, а с применением внешнего по отношению к базе языка программирования Web-страниц. Впрочем, второе становится проблемой только при использовании плохо сконструированных языков, таких как VBScript. Если же используется современный надежный объектно-ориентированный язык, к примеру, С++ или Java, обладающий развитыми средствами сокрытия сложности, конечный вид страниц не очень отличается от случая, когда имеются хранимые процедуры в SQL-сервере.

А вот пример решения проблемы хранимых процедур с применением PHP. Был написан класс Action (примерно 3 Кбайт кода), который позволил выполнять все действия путем вызова хранимых в базе процедур и заодно обеспечил протоколирование всех действий с магазином. В примере показан цикл, при помощи которого в новый вариант базы вносится информация о продуктах из старой базы.

Пример применения класса Action

for(	$P=$old_base->Execute('SELECT * FROM Products');
!$P->EOF();
	$P->MoveNext();
   )
{
	$create=new Action('ProductNew',
		array('ID' 		  => $P->Fields('ProductID'),
			'ProductName' => $P->Fields('ProductName'),
			'ShortName'   => $P->Fields('ProductName'),
			'MadeIn' 	  => 'USA'));
	$create->Execute();

	$set_price=new Action('SetProductPrice',
		array('ProductID'   => $P->Fields('ProductID'),
			'ParcelID' 	  => '1',
			'Amount' 	  => $P->Fields('Price')));
	$set_price->Execute();
}
$P->Close();

Фрагмент таблицы хранимых процедур

Union, Sub-Selects

Отсутствие некоторых конструкций типа подзапросов — это более неприятная вещь, вынуждающая программиста писать специальную процедуру в иных местах, где можно было бы обойтись одним запросом. При этом он вынужден вручную делать то, что обычно автоматически делается сервером: формировать временную таблицу, помещать туда результаты, а потом использовать их в следующем запросе. MySQL поддерживает средства, упрощающие эту работу: в частности, поддерживает временные таблицы, локальные для данного соединения и автоматически удаляющиеся при его закрытии, а также создание таблиц в оперативной памяти. В более простых случаях, когда результат выполнения подзапроса представляет собой одну запись, для хранения промежуточных значений можно использовать переменные MySQL.

Побывав на прокрустовом ложе языка MySQL, программист начинает более тщательно конструировать запросы, а в будущем учитывать это при проектировании структуры баз.

View

Еще одна неприятная черта — отсутствие так называемых View. Они вносят дополнительный уровень абстракции, делающий базу более гибкой, а логику — прозрачной, и очень полезны с точки зрения разделения прав доступа. Допустим, у нас есть таблица предложений от разных поставщиков, и мы хотим, чтобы каждый поставщик мог редактировать только свои предложения, используя ODBC-соединения и клиентскую программу на своем компьютере. При наличии View это очень просто: мы создаем пользователя для данного поставщика и View, куда выбираем только его предложения, а затем даем этому пользователю права на данный View. В MySQL же это решается весьма своеобразно: для каждого поставщика придется завести отдельную таблицу, а общая таблица будет собираться из них путем создания таблицы типа Merge — некоей комбинации View и Union, когда значения из нескольких идентичных таблиц MyISAM объединяются в одну и этому объединению дается свое имя. Принципиальная проблема при таком подходе состоит в отсутствии транзакций, так как Merge поддерживается только для таблиц типа MyISAM.

В MySQL отсутствуют и такие средства, как foreign keys и другие средства поддержания целостности, но, как показывает практика, это не слишком серьезная проблема. Данные средства действительно повышают качество проектирования базы, но при создании Web-сайтов ошибки необходимо выявлять на том уровне, на котором возможна их обработка, а именно на внешнем по отношению к SQL уровне языка создания страниц. Максимально возможное количество проверок следует проводить еще в браузере на стороне клиента. Таким образом, проверки целостности внутри SQL-сервера могут рассматриваться только как дополнительное средство, и рассчитывать на них при проектировании бизнес-логики все равно нельзя.

Надежность

Отсутствие транзакций

Данная проблема пока существует, но постепенно уходит в прошлое. Создатели MySQL выделили работу с собственно файлами таблиц в отдельный модуль, в результате чего в настоящее время MySQL поддерживает несколько конкурирующих типов таблиц. Они обеспечивают разное быстродействие и разные возможности, в том числе и что касается транзакций. В стандартной поставке MySQL имеются два типа таблиц, поддерживающих транзакции: BerkeleyDB и InnoDB. Еще один тип таблиц с поддержкой транзакций не входит в стандартную поставку MySQL — это GEMINI.

Несмотря на отсутствие транзакций в использующейся в проекте версии, MySQL продемонстрировал очень высокую надежность. Как показал опыт, другие компоненты данного сайта (Windows, ASP Engine, платежный драйвер и т. д.) имели гораздо худшую надежность и приводили к проблемам гораздо чаще, чем MySQL. Даже на сервере с аппаратными (как позже выяснилось) проблемами данные ни разу не были потеряны, самой серьезной неприятностью была необходимость пересоздания индексных файлов. С учетом появления таблиц с transaction-safe MySQL перестает уступать в надежности любому другому серверу. Правда, стоит признать, что самый перспективный вид таблиц, InnoDB, пока довольно-таки сыроват и требует доводки. В частности, в них нельзя индексировать поля BLOB/TEXT.

Недиагностируемые ошибки

В разговоре о надежности MySQL я не могу обойти молчанием одно обстоятельство, трудно формализуемое, но известное любому опытному сисадмину, — это отсутствие слабопрогнозируемых и слабодиагностируемых ошибок при его использовании. Для примера приведу пару случаев, с которыми недавно пришлось столкнуться. Оба они связаны с IIS и SSL.

Случай номер один: при переезде сайта на новый сервер отказал SSL-ключ, прекрасно работавший на старом сервере. Как это часто бывает, IE дал знать о данной проблеме шарадой: подвисанием на несколько минут и последующим сообщением, что "сервер не найден или ошибка DNS". Установка на новый сервер последних пакетов исправлений ничего не дала. Поиск решения в Интернете в конце концов увенчался успехом: оказалось, что пакет надо устанавливать и на новый, и на старый сервер, а затем повторить процедуру переноса ключа. Дело в том, что ошибка содержится в процедуре экспорта, после чего неверный ключ молча проглатывается обновленным обеспечением (которое, кстати, уже знает об этой проблеме!), а потом так же молча отказывается работать.

Вторая такая ошибка обнаружилась при отказе страницы заказа. Проверка показала: при запросе страниц через протокол HTTPS Internet Explorer …подвисает на несколько минут, затем выдает "сервер не найден или ошибка DNS". Раздумывая над тем, что искусственный интеллект давно создан, причем создан злобным, ехидным и мстительным, я принялся за отладку. Расшифровка показания Event Log, столь же уклончивого, как и диагностика Internet Explorer, показала следующее: несколько недель назад я включил Default Web site, не имеющий сертификата вообще и настроенный только на адрес 127.0.0.1 (он был нужен для локального использования), а полчаса назад, в период наименьшей активности пользователей (ибо в другое время это невозможно из-за практически непрерывного потока заказов в обработке), я перезапустил IIS. При рестарте Default Web Site и сайт магазина умудрились войти в конфликт за 443-й порт. Это и стало причиной неработоспособности SSL. Первый случай отсрочил на несколько дней введение в строй нового сервера, а второй привел к неработоспособности сайта на протяжении почти часа.

Здесь уместно задать вопрос: при чем же тут MySQL, зачем автор обсуждает не относящиеся к делу программные продукты? Очень просто: подробностью приведенных примеров я хочу подчеркнуть важность вывода, который я ими иллюстрирую. А именно: отсутствие плохо диагностируемых ошибок в используемых программных продуктах во много раз важнее для надежного функционирования "живого" сайта, чем наличие транзакций у SQL-сервера, второй сетевой карты у машины, второго винчестера в RAID, дополнительного ИБП у провайдера и тому подобных средств повышения надежности. Под "живым" подразумевается сайт, который постоянно или достаточно часто модифицируется и развивается в ходе работы. Опыт показывает, что в современных условиях довольно жесткой конкуренции и быстрой смены ситуации это необходимое условие успешного функционирования проекта.

Теперь непосредственно о MySQL. Учитывая вышеприведенное обстоятельство, можно утверждать, что надежность функционирования сайта на основе MySQL высока (определяется другими компонентами), так как авторы явно уделили этому аспекту пристальное внимание. За время работы с MySQL ошибок, подобных описанным выше, обнаружено не было вообще. Ясная диагностика и прекрасная документация практически не оставляют для них места. Кроме того, широкая популярность данного продукта позволяет быстро найти в Интернете ответ на любой вопрос даже человеку, который не в ладах с английским или просто ленится читать документацию.

Безопасность

MySQL имеет развитую систему привилегий, позволяющую предоставлять права на базы, таблицы и отдельные поля таблиц. При этом пользователей можно дифференцировать не только по именам, но и по их сетевому адресу. И, наконец, различным пользователям доступен различный набор привилегий, включающий четырнадцать их типов, чего в большинстве случаев достаточно. Привилегии на отдельные поля позволяют более точно управлять доступностью данных для различных служб. Например, службе доставки из таблицы заказов требуются только поля, содержащие адрес (с правом на чтение), а также возможность читать и изменять поле статуса заказа. Видеть поля, содержащие данные о способе и параметрах оплаты, им вовсе не обязательно.

Дифференцирование по адресу клиента также очень полезно. Например, у нас обязательно должен быть удаленный пользователь, имеющий право чтения всей базы, при помощи которого производится репликация этой базы в офис, и еще один, имеющий полный доступ, для закачки новых данных на сайт. Как правило, эти действия проводятся с фиксированного адреса или группы адресов. Это внешний адрес шлюза офиса в случае удаленного хостинга или адрес подсети (как правило, 192.168.1.*), если сайт хостится в составе офисной сети. В этом случае будет полезно завести таких "опасных" пользователей не с доменом '%', а с более специализированным, например 'my.gateway.ip.address'.

Что касается имеющихся "дырок" в системе безопасности, то за время эксплуатации сайта была обнаружена только одна серьезная ошибка. Она касалась процедуры проверки пароля и была исправлена в версии 3.23.11. Выражалась она в том, что процедура не проверяла длину входной последовательности, и при переборке 256 однобайтовых последовательностей та, которая совпадала с первым байтом последовательности пользователя, считалась подходящей.

Для аудита MySQL имеет несколько типов протоколов, от протокола ошибок до полного протоколирования всех запросов. Правда, последнее довольно трудно применить в реальной работе по причине того, что получающийся протокол имеет очень большой объем.

Скорость

По скорости работы MySQL входит в число лидеров среди SQL-серверов. Это осознанная стратегия его авторов, отдающих в разработке приоритет оптимизации по скорости и очень неохотно внедряющих то, что может замедлить работу MySQL. Не стоит безоговорочно доверять результатам тестов, представленных на сайте MySQL, как любым тестам производителей, но по большей части MySQL действительно быстрее конкурентов. За одним "но". Рассмотрим его поподробнее.

Блокирование на уровне таблиц

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

Если с таблицами работает только один процесс, блокирование таблиц не требуется, а если оно и делается, то занимает очень мало времени, и его можно не учитывать при расчете быстродействия. Если же с базой работает несколько процессов одновременно, возникает проблема коллизий в случае, когда процессы работают с одними и теми же данными. Опасность нарушения целостности данных возникает при модификации и одновременном чтении данных. Поэтому при всех операциях с базой данных процесс накладывает на определенную ее часть блокировку, предотвращающую на время операции какие-либо опасные действия со стороны других процессов. Например, когда процесс читает данные, он накладывает на них блокировку, запрещающую их модификацию. В результате процесс, который попытается что-либо записать в эту же область, не сможет наложить собственную "пишущую" блокировку и, следовательно, ему придется ждать, пока не будет снята предыдущая блокировка. Таким образом, система блокировок (вкупе с транзакциями) снимает с программиста часть работы по обеспечению целостности данных в условиях многопоточности.

Итак, в чем же состоит проблема? В том, что, стремясь к эффективности отдельно взятого процесса, создатели MySQL изначально приняли за минимальную блокируемую единицу целую таблицу. В результате при массовой обработке записей серверу практически не приходится тратить время на операции блокирования, но зато блокированным оказывается гораздо больший объем данных, чем обычно нужно. Поэтому при большом количестве одновременно обратившихся клиентов или при выполнении длительного запроса, работающего с данными, которые требуют модификации, всем остальным процессам приходится неоправданно долго ждать своей очереди. Как показал опыт, MySQL вполне успешно справляется с достаточно большим потоком покупателей, но запросы, собирающие статистику продаж для страниц back-office, практически парализуют прохождение заказов. И проблема длинных запросов при блокировании на уровне таблиц не решаема в принципе.

Попытки решения этой проблемы "малой кровью", например, путем переноса данных в дополнительные отдельные таблицы "только для статистики", просто перемещают проблему из одного места в другое. Таким образом, при создании большого проекта на основе MySQL есть два выхода: либо сразу ориентироваться на таблицы BerkeleyDB и InnoDB, где блокировка производится на уровне соответственно страниц и строк, в результате чего данная проблема отпадает сама собой. Либо еще на этапе проектирования магазина предусмотреть накопительные данные для всех видов статистики, которые вы хотели бы иметь. А лучше и то и другое вместе. Дополнительным методом решения данной проблемы в отдельных случаях могут быть таблицы типа Merge.

При создании временных таблиц для повышения скорости работы можно воспользоваться таблицами типа HEAP. Эти таблицы создаются в памяти и никогда не записываются на диск. Их также можно использовать для хранения сугубо оперативной информации, например, регистрационных записей посетителей чата или данных текущих сессий. Например, можно создать обработчик сессий в PHP, используя обращение к MySQL к таблице типа HEAP. Это уменьшит количество обращений к диску, позволив дисковому кэшу работать более эффективно, и избавит вас от неочищенных данных сессий во временном каталоге (заодно и в принципе исключив возможность взлома путем чтения сессионных данных).

Совместимость и переносимость

Совместимость по версиям

Поскольку у создателей MySQL нет особых стимулов постоянно предлагать пользователям все новые и новые версии продукта, совместимость по версиям у MySQL выше всяких похвал. При изменении формата данных авторы MySQL сделали для нового формата отдельный тип таблиц MyISAM, продолжая при этом полностью поддерживать старый ISAM, благодаря чему нет никаких проблем с данными, созданными любой версией MySQL. Опыт автора показал, что MySQL обычно обновляется в случае появления новых полезных возможностей, которые хочется применить (таковы, к примеру, COUNT(DISTINCT) или появление InnoDB).

Совместимость по операционным системам и компьютерам

Благодаря открытости кода MySQL портирован практически подо все ныне применяемые платформы. Это множество ОС семейства UNIX, Linux, все семейство Windows, включая Windows 95, OS/2, MacOS.

Перенос данных также не вызывает трудностей. Все виды таблиц, кроме устаревшего ISAM, бинарно-совместимы, поэтому отдельные таблицы и целые базы данных типа MyISAM можно переносить между компьютерами с разными ОС, не только используя встроенные средства (Backup Table, Restore Table, Load Data Infile, Select info outfile, утилиты mysqldump и т. п.), но и путем обычного копирования файлов или целого каталога. Например, можно заархивировать кроссплатформными RAR или gzip каталог с нужной базой на сервере под FreeBSD, потом скопировать архив по FTP к себе на ноутбук с ОС Windows98, раскрыть архив в соответствующий каталог /mysql/data — и можно начинать работу на локальной машине, так как база данных уже доступна через MySQL. Такой способ получения копии удаленной базы гораздо быстрее любого другого. Так же просто осуществляется резервное копирование базы или, к примеру, смена базы "на лету": останавливаем MySQL, переименовываем пару каталогов, пускаем опять — весь процесс занимает несколько секунд. Когда поток обрабатываемых сайтом (принятых и оплаченных) заказов достигает нескольких штук в минуту, такие вещи начинают играть роль. Впрочем, техническая поддержка и профилактические работы на онлайновом электронном магазине без его остановки — тема для отдельной статьи.

Совместимость по языкам

Благодаря открытости исходного кода и протоколов драйверы MySQL существуют для всех широко распространенных языков программирования. В поставку MySQL входит клиентская библиотека на C, которая используется для дальнейшего создания интерфейсов. Поэтому применение MySQL не накладывает никаких ограничений на используемый в проекте язык: есть объектно-ориентированная библиотека для C++, ODBC-драйвер MyODBC, два варианта JDBC-драйвера, модуль Perl. Для языка PHP MySQL вообще выступает как стандарт де-факто, и библиотека для обращения к MySQL компилирована в ядро.

* * *

Суммируя вышесказанное, можно сделать следующие выводы.

• MySQL пригоден для создания достаточно большого электронного магазина.
• При работе над проектом потребуются некоторые начальные затраты на создание
  функциональности, замещающей отсутствующие хранимые процедуры.
• Надежность и скорость работы при правильно построенной объектной модели
  окупят начальные затраты.
• Высокая переносимость избавляет от проблем, обычно возникающих при апгрейде
  программного обеспечения, и позволяет быстро перейти на любую платформу, а
  также отлаживать сайт на любом компьютере (при условии, что средство разработки
  также кросс-платформное, например, PHP, С++ или Java).