Библиотека сайта rus-linux.net
SERVER 101: Освежите ваши знания в области работы с базами данных, часть 1
Оригинал: Server 101: Brush Up Your Database Skills. Part 1
Автор: Mike Saunders
Дата публикации: 11 августа 2016 г.
Перевод: А.Панин
Дата перевода: 24 октября 2016 г.
Часть 1: Большинство крупных веб-сайтов (а также приложений) построено на основе баз данных. В статье будет затронут вопрос важности понимания принципа их работы, а также рассмотрена методика введения в строй собственной базы данных.
Для чего это нужно?
- Вы узнаете об особенностях работы многих приложений.
- Вы сможете работать непосредственно с данными, сохраненными такими веб-приложениями, как WordPress, OwnCloud и другими.
- Вы научитесь использовать язык запросов SQL для выполнения сложных операций поиска.
При сравнении с окружениями рабочего стола, дистрибутивами, веб-браузерами, играми и другим быстроразвивающимся программным обеспечением для конечных пользователей, базы данных могут показаться малоинтересными программными компонентами, которые могут заинтересовать лишь бородатых системных администраторов. Но в реальности каждая база данных является крайне любопытным программным компонентом, который стоит изучить даже в том случае, если вы не планируете работать непосредственно с ним.
Если бы баз данных не существовало, большинство крупных веб-сайтов работало бы слишком медленно и неэффективно, постоянно становясь источником различных технических проблем; это же утверждение справедливо и в случае большинства приложений для настольных компьютеров, так как они тоже используют базы данных. Если вам нужно хранить большой объем информации в надежном и хранилище с поддержкой простого механизма поиска, даже в том случае, если эта информация относится к вашей коллекции подставок для пивных кружек, использование базы данных является вполне обоснованным.
Но чем же является база данных на самом деле? Представьте, что баз данных не существует и каждое приложение для настольного компьютера и веб-приложение должно хранить данные в своем собственном формате. Некоторые из этих приложений будут записывать данные в текстовые файлы, возможно, формата CSV или XML. Другие приложения будут использовать бинарные файлы недокументированного формата. В конечном счете, для каждого программного компонента придется разрабатывать свое хранилище данных, механизмы загрузки и поиска данных, затрачивая дополнительные усилия и сводя на нет возможность импорта данных другими приложениями.
Базы данных исправляют положение, выполняя всю грязную работу, связанную с хранением, загрузкой и поиском данных. Программа, использующая базу данных - не важно, исполняется ли она на веб-сервере или настольном компьютере - взаимодействует с базой данных с помощью стандартизированного языка запросов, поэтому сторонние программы могут также взаимодействовать с ее базой данных и получать или сохранять информацию в ней. База данных заботится о целостности данных, создании резервных копий, дубликатах записей и так далее, поэтому программы могут ограничиться выполнением лишь своих непосредственных функций.
Рисунок 1. Этот забавный морской котик используется проектом MariaDB в качестве талисмана, но, по всей видимости, у него нет имени. Читатели, у вас есть какие-нибудь предложения?
Богатство выбора
MariaDB (и ранее MySQL) является наиболее популярной базой данных для простых приложений и приложений средней сложности, таких, как блоги, простые серверные приложения и веб-сайты средней популярности. Если же вам нужно работать с действительно большим объемом данных, вы можете обратить внимание на отличную альтернативу под названием PostgreSQL (). Несмотря на сложное в произношении название, данный сервер базы данных снискал репутацию мощного программного продукта с множеством отсутствующих в MariaDB функций, реализованных ценой снижения производительности и простоты использования. В последние годы, однако, оба упомянутых сервера баз данных практически сравнялись в плане возможностей и скорости работы, поэтому на сегодняшний день они являются достойными конкурентами.
Если же вы разрабатываете какое-либо приложение для настольных компьютеров и хотите использовать базу данных без необходимости запуска отдельного сервера, вам следует обратить внимание на SQLite (). Это библиотека, разработанная с использованием языка программирования C, которая позволяет встроить полноценную базу данных в программу для использования ее возможностей, а именно, механизма поддержания целостности данных, развитых функций поиска и так далее… В качестве примера в данном случае можно привести веб-браузер Firefox, который использует базу данных SQLite для хранения данных конфигурации, закладок и другой информации.
Как все это работает
Большинство баз данных хранит информацию в таблицах, которые чем-то похожи на электронные таблицы. Таблица состоит из одного или нескольких столбцов, в рамках каждого из которых описан тип хранящихся в нем данных, а также строк, представляющих сами данные. Рассмотрите следующий пример:
| ID | Name | Login |
|---|---|---|
| (INTEGER) | (VARCHAR(20)) | (Date) |
| 1 | Mike | 2015-01-12 |
| 2 | Ben | 2015-04-25 |
| 3 | Graham | 2015-01-02 |
Данная таблица предназначена для хранения информации о посещениях пользователями сервера и имеет три столбца: столбец для хранения уникального идентификатора события посещения сервера, столбец для хранения имени пользователя и столбец для хранения даты посещения сервера (в формате год-месяц-день).
Базы данных могут хранить любую информацию, но для повышения надежности их работы и ускорения операций поиска рекомендуется выбирать один из стандартных типов данных для каждого из столбцов (это чем-то напоминает статическую типизацию в программировании). Исходя из этого, столбец ID нашей таблицы может хранить лишь целочисленные значения, столбец Name - последовательности символов (то есть, строки) с максимальной длиной, равной 20 символам, а столбец Login - даты.
Одна база данных может содержать много различных таблиц, причем при обращении программы (П) к серверу базы данных (Б) с запросом обновления данных в таблице (Т), обмен данными - обычно осуществляемый посредством сетевого сокета - будет выглядеть аналогичным образом:
П: Привет, я Программа П. Могу ли я получить доступ к базе данных login?
Б: Одну секунду... Позвольте мне проверить, уполномочены ли вы работать с этой базой данных. Ах, да, у вас есть все необходимые полномочия, но вам придется назвать пароль.
П: Пароль <password>.
Б: Отлично, можете работать с базой данных. Что бы вы хотели сделать?
П: Пожалуйста, вставьте в таблицу Т эти данные: "Mike, 2015-01-12".
Б: Сделано; идентификатор добавленной записи (ID) равен 5.
П: Спасибо! Я отключаюсь.
Обратите внимание на то, что программа предоставила данные для вставки в столбцы Name и Login, но не предоставила данных для вставки в столбец ID. Это объясняется тем, что база данных сама генерирует (и автоматически увеличивает) значение в столбце ID для того, чтобы избежать дубликатов, поэтому мы можем превратить значение ID в первичный ключ. Но что это такое? Ну, это уникальный идентификатор определенной записи в базе данных, поэтому мы можем иметь несколько записей с идентичными данными (например, в том случае, если Graham посетит сервер несколько раз за день), но с отличными первичными ключами (ID).
Работа реляционных баз данных основывается именно на уникальных первичных ключах. Благодаря их наличию мы можем создавать другие таблицы для хранения дополнительных данных, например:
| ID | Command | Exit code |
|---|---|---|
| 1 | df -h | 0 |
| 2 | crontab -e | 1 |
| 3 | shutdown | 1 |
Эта таблица дополняет таблицу login в плане информации и использует те же первичные ключи в столбце ID. Таким образом, из первой таблицы мы можем узнать, что Ben посетил сервер 2015-04-25, причем запись, соответствующая этому посещению имеет идентификатор (ID), равный 2, после чего нам не составит труда использовать этот же идентификатор в приведенной выше таблице для поиска команды, которую он выполнил (crontab -e).
Это обстоятельство позволяет нам создать большое количество таблиц, содержащих различные данные, причем все записи этих таблиц будут взаимосвязаны с помощью первичных ключей из столбца ID. Разумеется, вы можете поместить все данные в одну таблицу но описанный подходя является более эффективным и безопасным. При этом не стоит забывать о том, что имеется возможность ограничения доступа определенных программ к различным таблицам базы данных, например, в случае базы данных с информацией о персонале предприятия работники кухни могут иметь доступ к таблице с описаниями диет работников и в то же время не иметь доступа к таблицам с информацией об их зарплатах и персональными данными. Кроме того, данный подход позволяет оптимизировать процесс создания резервных копий таблиц базы данных таким образом, что резервные копии ее наиболее важных таблиц будут создаваться на регулярной основе (ежедневно), а всей базы данных, которая может содержать большое количество не самой важной информации - реже (раз в неделю) с целью экономии времени.
Операция модификации таблицы, будь то вставка, модификация или удаление записи, называется транзакцией, причем в этом плане хорошая база данных должна соответствовать требованиям ACID. Данная аббревиатура расшифровывается следующим образом:
- Атомарность (Atomicity) - транзакция должна либо завершиться корректно, либо не выполняться вовсе. Вам явно не захочется получить набор из новых и старых данных из-за того, что в процессе модификации таблицы произошла какая-либо ошибка.
- Согласованность (Consistency) - данные должны соответствовать ограничениям, накладываемым базой данных. Например, в таблице не должно быть дубликатов первичных ключей и в том случае, если столбец предназначен для хранения целочисленных значений, транзакция, в рамках которой осуществляется добавление в него текстовых данных, должна завершаться неудачей.
- Изолированность (Isolation) - если несколько программ работает с одним и тем же сервером базы данных и пытается одновременно модифицировать одну и ту же таблицу, сервер базы данных должен выполнять транзакции в по очереди. Каждая транзакция должна быть выполнена изолированно от других таким образом, чтобы данные, записанные в до момента завершения одной транзакции, не заменялись на данные, записанные в момент выполнения других транзакций.
- Долговечность (Durability) - после выполнения транзакции данные должны храниться на постоянной основе, даже в случае сбоя операционной системы или отключения питания компьютера.
Изолированность особенно важна при использовании баз данных в качестве хранилищ информации популярных веб-сайтов. Если вы представите разработанный с использованием языка программирования PHP форум с большим количеством посетителей и задумаетесь о том, сколько тысяч процессов интерпретатора PHP будут одновременно взаимодействовать с сервером базы данных и пытаться обновить таблицы по мере добавления и редактирования пользователями новых постов, вы наверняка придете к выводу о том, что серверу базы данных приходится выполнять важную работу, направленную на разумное решение данной проблемы.
Совет: допустили ошибку в процессе создания таблицы базы данных? В этом случае вы можете удалить ее с помощью команды drop. Например, если вы допустили ошибку при создании таблицы login_dates, вы можете удалить ее сразу же после создания с помощью команды drop table login_dates;. Стоит проявлять особую осторожность в процессе работы с данной командой: вас не попросят подтвердить свои намерения перед удалением таблицы со всеми данными!
Разговор на одном языке
Команды, которые используются в данном руководстве для взаимодействия с базой данных, записываются в соответствии с правилами языка запросов SQL (Structured Query Language - Язык структурированных запросов). Данный язык позволяет не только записывать команды для осуществления манипуляций с данными в читаемом человеком формате, но и является стандартизированным, то есть позволяет использовать одни и те же команды при работе с различными серверами баз данных. Вы оцените это преимущество данного языка в том случае, если вам понадобится перейти к использованию более мощного сервера базы данных, например, при быстром росте популярности вашего веб-сайта, с которым не справляется ваш текущий сервер базы данных.
Многие серверы баз данных также позволяют использовать несколько механизмов для сохранения данных на диске. Эти "механизмы хранилищ" различаются в зависимости от методов структурирования данных на диске, поэтому некоторые из них оптимизированы для достижения максимальной производительности, в то время, как другие были специально спроектированы для достижения максимальной надежности хранения данных. В большинстве случаев вам не стоит задумываться о соответствующих настройках сервера базы данных, но если вас все же интересует дополнительная информация, вы можете перейти по следующему адресу: .
Давайте приступим к практической части
Думаю, мы уже рассмотрели достаточно теоретической информации, поэтому стоит приступать к практической части, заключающейся в самостоятельном создании базы данных. Если вы уже устанавливали WordPress, OwnCloud или любые аналогичные веб-приложения, использующие базу данных, вы наверняка знакомы с описанными ниже действиями. Многие веб-приложения автоматизируют процесс создания базы данных, экономя немного вашего времени, но в то же время скрывая от вас технические подробности данного процесса. При этом некоторым другим приложениям для корректной работы требуются вручную созданные базы данных, поэтому вам в любом случае не помешают соответствующие знания.
В данном руководстве мы будем использовать популярный сервер базы данных MariaDB () с открытым исходным кодом, который был создан на основе кода сервера базы данных MySQL после покупки последнего компанией Oracle ввиду сомнений в его будущем. Сервер MariaDB используется многими популярными веб-приложениями и службами, такими, как упомянутые выше WordPress и OwnCloud и является одним из самых простых в изучении. Данный сервер доступен из репозиториев пакетов программного обеспечения большинства дистрибутивов, потому вы можете установить его с помощью менеджера пакетов с графическим интерфейсом или интерфейса командной строки системы. К примеру, в основанном на Ubuntu дистрибутиве для установки соответствующего пакета программного обеспечения достаточно выполнить следующую команду:
sudo apt-get install mariadb-server
В результате будет осуществлена загрузка и установка компонентов сервера базы данных в систему с последующим запуском соответствующей службы (mysqld). Ввиду того, что сервер MariaDB основан на кодовой базе сервера MySQL, он использует те же команды и имена исполняемых файлов, что и проект-родитель, и это позволяет заменять один сервер на другой без каких-либо модификаций приложений. Первой операцией, которую стоит выполнить сразу же после установки упомянутого пакета программного обеспечения является защита сервера MariaDB от несанкционированного доступа, которую можно осуществить с помощью следующей команды:
sudo mysql_secure_installation
Сервер MariaDB использует свою собственную учетную запись администратора с именем root (не связанную с учетной записью пользователя root операционной системы), которая задействуется при возникновении необходимости в добавлении учетных записей новых пользователей базы данных и выполнении других административных задач. Сразу же после исполнения приведенной выше команды MariaDB попросит вас ввести текущий пароль пользователя root, который является пустой строкой сразу же после установки, поэтому вы можете просто нажать клавишу Enter. После этого сервер базы данных попросит вас задать новый пароль пользователя root, поэтому стоит ввести в ответ какую-нибудь осмысленную фразу.
Рисунок 2. В данном руководстве мы будем взаимодействовать с сервером базы данных MariaDB с помощью клиента с интерфейсом командной строки, но существуют также и веб-приложения, позволяющие управлять базами данных, такие, как phpMyAdmin.
Учетные записи пользователей базы данных
Далее установочный сценарий предложит вам удалить анонимную учетную запись пользователя базы данных, предназначенную для тестирования, и ограничить возможность подключения к базе данных от лица пользователя root по сети, что является вполне разумным решением. Впоследствии вы можете просто отвечать согласием на все задаваемые вопросы, нажимая клавишу Y, что в конце концов приведет к возвращению к приветствию командной оболочки. Теперь нам нужно создать учетную запись обычного пользователя (не root) и базу данных, после чего установить все необходимые права доступа для того, чтобы новый пользователь мог модифицировать таблицы этой базы данных:
sudo mysql -uroot -p
В результате выполнения данной команды сервер MariaDB осуществит аутентификацию пользователя root на основе пароля, поэтому вам придется ввести пароль, который вы вводили ранее при настройке параметров сервера базы данных. После этого следует выполнить следующие команды:
create database lvtest; create user 'lvuser'@'localhost' identified by 'pass123'; grant all privileges on lvtest.* to 'lvuser'@'localhost';
Назначение этих команд является по большей части очевидным: мы создаем новую базу данных с именем lvtest, создаем учетную запись пользователя с именем lvuser на локальном компьютере с паролем "pass123", после чего предоставляем этому пользователю все привилегии (чтение, запись, удаление и.т.д.) для базы данных lvtest с помощью шаблона lvtest.*, причем в данном случае символ звездочки соответствует каждой таблице этой базы данных. Вы можете заменить этот символ на имя определенной таблицы в случае необходимости ограничения доступа.
На данный момент мы не создали ни одной таблицы базы данных, поэтому следует выполнить команду exit (или, как терминале, воспользоваться сочетанием клавиш Ctrl+D) для выхода из интерактивной оболочки сервера базы данных, которая была открыта ранее с правами пользователя root, после чего снова войти в нее с правами нового пользователя, воспользовавшись следующей командой:
mysql -ulvuser -p
Введите пароль pass123 после соответствующего запроса и вы снова окажитесь в интерактивной оболочке сервера MariaDB. Давайте начнем заполнять информацией новую базу данных! Мы будем создавать таблицы с информацией о посещениях сервера, описанные в размещенной выше теоретической части. В первую очередь следует выбрать базу данных, с которой мы будем работать:
use lvtest;
Теперь нужно создать таблицу, указав имена столбцов и типы данных, которые должны размещаться в них:
create table login_dates(ID int auto_increment primary key, Name varchar(20), Login date);
При создании таблицы мы в первую очередь указываем имя столбца (ID, Name, Login, как было сказано ранее), после чего - тип данных, которые должны размещаться в нем (int для целочисленных значений, varchar(20) для строки с максимальной длиной в 20 символов и date для даты). Мы можем также указывать дополнительные параметры столбца таким же образом, как в случае столбца ID, где мы указываем, что числовой идентификатор должен увеличиваться на единицу каждый раз при добавлении новой записи, причем сам столбец должен использоваться для хранения первичных ключей.
Если вы сейчас выполните команду show tables;, вы обнаружите, что таблица login_dates теперь присутствует в базе данных lvtest, а в случае выполнения команды desc login_dates; будет выведена информация об этой таблице, включающая список столбцов (или полей) и типов данных, которые могут храниться в них, как показано на Рисунке 3. Теперь давайте добавим данные в нашу таблицу:
insert into login_dates values (0, 'Mike', '2015-01-12');
Рисунок 3. Мы создаем таблицу, указывая имена ее столбцов и типы данных, которые будут размещаться в них. После этого мы знакомимся с ее описанием.
Повторите исполнение этой команды еще несколько раз, заменяя передаваемые данные на данные из рассмотренного выше примера таблицы (Ben 2015-04-25 и Graham 2015-10-02) и обратите внимание на неизменное нулевое значение, добавляемое в столбец ID. Данное значение принуждает базу данных к самостоятельной генерации первичных ключей начиная с 1 с их автоматическим увеличением в соответствии с запросом создания таблицы базы данных. После окончания процесса добавления данных в таблицу вы сможете ознакомиться с ними с помощью следующей команды:
select * from login_dates;
Данная команда select является особенно полезной и позволяет извлекать информацию любого типа из базы данных. В нашем случае она выведет все данные из таблицы (символ звездочки снова используется в качестве шаблона), как показано на Рисунке 4. Но почему данная команда имеет имя select (переводится, как "выбрать"), а не какое-либо другое более очевидное имя типа show (переводится, как "показать") или view (переводится, как "посмотреть")? Ну, в данном случае мы не выполняем каких-либо сложных операций с данными, а просто извлекаем их из базы. Но в реальных приложениях после извлечения данных из базы чаще всего осуществляется их последующая обработка, объединение с другими данными и так далее… Таким образом, каждый раз при использовании команды select вы осуществляете выборку данных в соответствии с поисковыми запросами, после чего обрабатываете их.
Рисунок 4. После аккуратной вставки данных в таблицу мы можем просмотреть их с помощью команды select.
Также у вас имеется возможность ограничения объема данных, которые будут извлечены с помощью команды select аналогичным образом:
select * from login_dates where Name = 'Ben';
В этом случае мы добавили ограничение для данных, которые будут извлечены. Мы сообщаем серверу базы данных о том, что следует извлечь данные из таблицы login_dates, причем нас интересуют лишь строки с присутствующей строкой "Ben" в столбце Name. Вы можете объединить несколько условий, объявленных с помощью ключевых слов where, с помощью ключевых слов and для осуществления сложных выборок. При этом некоторые поисковые запросы могут быть настолько длинными и запутанными, что они будут иметь больше общего со сложными языками программирования, чем с простым языком запросов…
Разумеется, вы можете также использовать и другие операторы для поиска данных. Рассмотрите следующий пример запроса:
select * from login_dates where Name != 'Ben';
Данный запрос позволяет получить все строки, в которых строка "Ben" не присутствует в столбце Name. Вы также мажете использовать знаки "больше" и "меньше" при осуществлении поиска данных:
select * from login_dates where ID > 2;
Учтите, что текстовые строки должны помещаться одинарные кавычки, а числовые значения - не должны. Также обратите внимание на то, что клиент с интерфейсом командной строки сервера MariaDB поддерживает большое количество экономящих время функций, которые уже присутствуют в командной оболочке Bash, таких, как история команд (вы можете использовать клавиши со стрелками для выбора одной из ранее исполненных команд), а также сочетание клавиш Ctrl+R с последующим вводом фрагмента команды для поиска последней команды с ним. Автоматическое завершение команд по клавише Tab также поддерживается для имен таблиц и столбцов.
Совет: Команды MariaDB и их параметры не зависят от регистра, причем многие системные администраторы используют верхний регистр для отделения их от рабочих данных. Поэтому в том случае, если вы захотите поискать примеры команд в сети, вы наверняка увидите примеры команд в таком формате, как CREATE DATABASE lvtest;. Использование верхнего регистра в данном случае является делом вкуса, но в том случае, если вы много работаете с базами данных, особенно в рамках сценариев, оно может оказаться полезным для улучшения читаемости кода.
Продолжение в следующей статье!
На данный момент вы обладаете базовыми знаниями относительно принципа работы баз данных, методик их создания и работы с данными, хранящимися в них. Соответствующие навыки применимы ко всем серверам баз данных, а в следующей статье серии мы пойдем еще дальше и рассмотрим некоторые сложные команды для осуществления манипуляций с данными, поиска данных, а также рассмотрим способ интеграции сервера базы данных с веб-приложением.