Лабораторная работа: MySQL

Заняться выполнением этой лабораторной работы меня побудили несколько причин. Во-первых, занимаясь построением серверов и сетей на основе Линукс'а, я догадывался, что где-то совсем рядом лежит сказочно богатый континент, пока не нанесенный на мою карту компьютерного мира. Во-вторых, авторы одной из самых популярных открытых программ - СУБД MySQL, недавно приняли GNU GPL (General Public License) как лицензию, по которой распространяется эта программа, и теперь MySQL является полноценным проектом GNU.

Эти юридические тонкости имеют самое непосредственное отношение к нам, пользователям, чему я немного ниже приведу пример. И наконец, в-третьих, пытаясь отыскать хорошие руководства по SQL в сети, я в конце концов обнаружил, что самые лучшие он-лайновые учебники по этой теме, оказывается, написаны нашими соотечественниками, на русском языке, и лежат у меня на диске - в зеркале сервера CITFORUM

Особенно полезны учебный курс "Введение в системы управления базами данных" Пушникова А.Ю., и курс лекций "Основы современных баз данных" Сергея Кузнецова. Недавно к ним добавилось подробное описание СУБД MySQL , сделанное Паутовым Алексеем Валентиновичем на основе оригинальной документации и такое же доскональное.

Итак, пришла пора взяться за учебники, а для меня еще и достать припасенный для такого случая особый файл. Этот файл представляет собой телефонный справочник службы 09 нашего города, пару лет назад попавший в местную ФИДО-сеть. Мне не очень важна его актуальность, зато очень подходит его размер - свыше 120 тысяч записей. Очень часто примеры, даваемые в учебниках, являются слишком игрушечными, чтобы вызывать интерес. Затем, на крошечной БД невозможно почувствовать скорость и мощь современных программ и компьютеров, или наоборот, плохо настроенную БД или неправильно составленный запрос. Кроме этого, ситуация с построеним БД вокруг уже имеющихся данных вполне жизненна.

Вполне возможно, что у вас файла с такими данными в пределах досягаемости нет. Ничего страшного - его можно сделать самому, использовав подручные средства - например, взять простой текстовый файл, обычные textutils, и интерпретатор языка awk. Пример, как это можно сделать, приведен здесь . Конечно, данные в таком файле будут случайными, только внешне похожими на настоящий телефонный справочник.
Для начала надо установить на вашем компьютере MySQL. Не буду пересказывать главы из документации, имеющиеся в описании Алексея Паутова. Скажу лишь, что для установленного у меня дистрибутива Дебьян установка программы свелась к выполнению команды: ...$ dpkg -i mysql-server_номер_версии.deb mysql-client_номер_версии.deb

В дистрибутиве Mandrake, который я также иногда использую, используется программа-установщик rpm с соответствующими ключиками, или же какая-то из графических надстроек над rpm. Вполне возможно, что в вашей Линукс системе эта СУБД установилась сама собой по умолчанию.
Инсталяция MySQL под Windows, равно как и Apache, PHP и Perl, рассказана Дмитрием Котеровым на том же ЦитФоруме.
Предупреждаю, однако, что все, что написано ниже, проверено только под Линуксом.
Если вам повезло, и команда ...$ mysql из вашего шелла выдала приглашение наподобие:

Welcome to the MySQL monitor.  Commands end with ; or g.  Your MySQL
connection id is 28 to server version: 3.22.32-log
Type 'help' for help.


mysql>, то в ответ на него наберите q, оставим на время интерпретатор SQL запросов, и займемся администрированием сервера MySQL.
Прежде всего, надеюсь, вы установили пароль администратора сервера БД, и пока его не забыли. Теперь нужно завести пользователей и дать им некоторые права. Все администрирование ведется через обычные таблицы MySQL, и их правка также осуществляется стандартными SQL командами. Самая первая таблица, которая определяет допуск юзера к серверу, так и называется - user. Давайте глянем, кто у нас там есть и что он может делать: ...$ mysqldump -u root -p --opt mysql user>mysql-users.sql
После выполнения этой команды у нас появился файл mysql-users.sql Загрузим его в текстовый редактор, чтобы поподробнее изучить, и, возможно, немного поправить.

# MySQL dump 7.1
#
# Host: localhost    Database: mysql
#--------------------------------------------------------
# Server version        3.22.32-log
#
# Table structure for table 'user'
#
DROP TABLE IF EXISTS user;
CREATE TABLE user (
Host char(60) DEFAULT '' NOT NULL,
User char(16) DEFAULT '' NOT NULL,
Password char(16) DEFAULT '' NOT NULL,
Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,
File_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,
References_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
PRIMARY KEY (Host,User)
);
#
# Dumping data for table 'user'
#
LOCK TABLES user WRITE;
INSERT INTO user VALUES
('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),
('localhost','ophil','','N','N','N','N','Y','N','Y','N','N','Y','N','N','N','N'),
('localhost','proba','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
UNLOCK TABLES;


Вот уже и показались первые SQL-предложения, хотя мы пока не начинали программировать или что-либо запрашивать. В предложении CREATE TABLE перечислены все 14 различных привилегий, которые могут иметь или быть лишены пользователи. Первые 6 - Select, Insert, Update, Delete, Create и Drop, касаются права пользователя работать с записями таблиц и с самими таблицами. Следующие 4 - Reload, Shutdown, Process и File - касаются сервера в целом. Привилегии Grant, References, Index и Alter дают право передавать права, а также изменять, связывать и индексировать таблицы.
Два важных замечания.

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

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

Теперь, размножив и поправив записи в таблице, но ни в коем случае не ее структуру, отправим команды обратно в MySQL.

...$ mysql -u root -p > mysql-users.sql


и попросим сервер перечитать измененные права

...$ mysqladmin -u root -p reload


Еще одно замечание насчет паролей. В рассмотренном нами файле поля паролей пусты, и это надо немедленно исправить. Править их в текстовом файле неудобно, потому что MySQL используем для шифрования пароля отдельную программу, и хранит пароль в зашифрованом виде. Чтобы установить пароль, например, пользователю "proba", надо выполнить такую команду:

...$ mysql mysql -e 'update user set password=password("0") where user="proba";


Поздравляю, мы только что составили и выполнили первый SQL запрос в нашей лабораторной работе, хотя и сделали это из командной строки, со всеми ее удобствами и неудобствами. К неудобствам можно отнести то, что наш пароль высветился на экране, мог попасть в список процессов, в разные журнальные файлы. Будет лучше не полениться, запустить монитор mysql и задавать пароли в нем с помощью того же запроса внутри СУБД MySQL.

Отличие от обычной системы паролей в том, что имена пользователей БД могут быть не связаны с их регистрационными именами в системе, пользователи не могут менять свои пароли, и этот пароль известен администратору БД.

Разобравшись с самой первой административной таблицей user, остальные таблицы: db, host, tables_priv, columns_priv, func - правим аналогично. Каждую из команд, посылаемую MySQL, можно задавать либо в мониторе запросов mysql, либо из командной строки, либо создав файл и отправив его в интерпретатор MySQL через тот же монитор. Можно также обратиться к MySQL через интерфейсы с другими языками программирования из программ, написанных на C, Perl, PHP, Python и других.

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

107003  банки "приорбанк"        первомайская ул.         1   бнк
107007  центры информацио        жукова ул.               4а  цен
107026  предприятия транс        артиллерийская ул.       8а  пре

и каждая запись содержит 5 полей:

1. номер телефона
2. фамилия или название организации
3. улица
4. номер дома
5. номер квартиры или примечание


Если бы исходные данные пришли из другой SQL БД и были в виде, как уже изученная нами таблица user, все, что нам пришлось бы сделать, это отправить в интерпретатор этот файл. Если бы текстовый файл был в более удобоваримом виде, например, с полями, разделенными знаками табуляции, то загрузить его в таблицу также можно было бы за один шаг. Но в нашем случае придется создать временную таблицу:

create table tmp
(
line varchar(80)
);


и импортировать в нее данные с помощью

load data infile '/tmp/09phone.txt' into table tmp;


Таким образом мы записали в таблицу tmp каждую запись как строку без разделения на поля. Импорт занял на моем компьютере около 4.4 сек. Здесь и далее и привожу время только для сравнения, для своего компьютера и настроек программы, сделанных по умолчанию.

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

create table old
(
phonum int unsigned not null,
title varchar(64) not null,
street varchar(40) not null,
bldng varchar(8) not null,
other varchar(8) not null
);


а затем заполняем ее данными, пройдя по всем строкам таблицы tmp:

insert into old
( phonum, title, street, bldng, other )
select
trim(mid(line,1,6)),
trim(mid(line,8,18)),
trim(mid(line,34,19)),
trim(mid(line,58,4)),
trim(mid(line,63,3))
from tmp;


Функция mid(...) извлекает из первого аргумента подстроку в соответствующих позициях, а функция trim(...) удаляет пробелы в начале и конце строки. Теперь можно спокойно сделать

drop table tmp;


Опять же, только для сравнения, 123 тысячи записей обработаны за 8.6 сек.Для того, чтобы узнать время запроса из программы, содержащей все команды и выполняемой неинтерактивно, пришлось применить такой способ:

create table times
(
start int unsigned
);
insert into times values ( unix_timestamp() );


Чистый SQL и MySQL не поддерживают никаких иных переменных, кроме таблиц и полей, так что для хранения времени пришлось завести отдельную таблицу. Нет также ничего похожего на print или echo, так что сообщать результат получилось только злоупотребив оператором select:

select "импорт данных выполнен: ", unix_timestamp()-start, " сек." from times;


Получившаяся таблица old еще нуждается в нормализации, но уже первые тривиальные запросы выявили одну проблему. Сортировка по алфавиту использовала по умолчанию чуждую русскому языку кодировку ISO-8859-1. Хотя в последних версиях, возможно, уже можно менять порядок сортировки на ходу, в той версии, которая входит в Debian v2.2, для правильной работы с русским языком необходима перекомпиляция (сборка) программы с параметром:

...$ ./configure --with-charset=koi8_ru


В Debian'е для сборки пакетов есть масса скриптов, которые и делают всю работу. Таким образом, поправив файл debian/rules и произведя магическое заклинание

...$ debuild -b -uc 2>&1|tee build.log


вскоре я получил готовый к инсталяции пакет с правильным понятием по-русски.
Но тут же возникла следующая проблема. FSF (Free Software Foundation) и Debian очень щепетильно относятся к любым ограничениям на свободу программ, и те ограничения на коммерческое использование MySQL, которые были в их старой лицензии, привели к тому, что MySQL оказался в секции non-free. Желание иметь клиентскую часть свободной вынудило разработчиков вырезать из оригинальных исходников чисто GPL-ные куски и образовать отдельное дерево исходников. По-английски это называется "fork", а в русском языке вполне подходит слово "раскол". Хоть это слово и с маленькой буквы, явление весьма неприятное, распыляющее силы разработчиков и создающее неудобства пользователям. В моем случае пришлось пересобирать также и GPL-ные исходники, а затем бороться с конфликтом зависимостей пакетов.

Но вот борьба позади, и мы приступаем к разбиению единой таблицы на несколько связанных и нормализованных, что, собственно, и дает право называться СУБД реляционной. Из таблицы old с теми же полями, что и в исходном файле, мы сделаем 3 таблицы, связанные, как это обычно рисуется на схемах, таким образом:

phone
------
phonum              building
naim                --------             street
bd_id >------------ bd_id                ------
other               st_id   >----------- st_id
bldng                nick


Значок >-- обозначает сторону "много" в отношении "один ко многим" и означает, что в одном здании может быть много телефонных номеров, а на одной улице много зданий.
Начать придется с конца, с таблицы street, которая будет содержать список улиц, и на которую будет ссылаться таблица building, содержащая, в свою очередь, список всех телефонизированных зданий в городе.

create table street
(
st_id smallint unsigned not null auto_increment,
nick varchar(32) not null,
primary key (st_id)
);
insert into street ( nick )
select distinct street
from old;


Заполнение таблицы заняло 12.1 сек. Теперь создадим таблицу building

create table building
(
bd_id smallint unsigned not null auto_increment,
st_id smallint unsigned not null references street,
bldng varchar(8) not null,
tmp varchar(40) not null,     # временно, для соответствия с old
primary key (bd_id)
);


и также заполним ее

insert into building ( st_id, tmp, bldng )
select distinct street.st_id, street.nick, old.bldng
from old, street
where old.street=street.nick;


Таблица заполнялась аж 5 мин. 23 сек., так что было время задуматься. Прояснить ситуацию в таких случаях помогает особая команда explain, например

explain select distinct street.st_id, street.nick, old.bldng
from old, street
where old.street=street.nick;


которая выдала следующую подсказку:

+--------+------+---------------+------+---------+------+--------+------------+
| table  | type | possible_keys | key  | key_len | ref  | rows   | Extra      |
+--------+------+---------------+------+---------+------+--------+------------+
| street | ALL  | NULL             | NULL |    NULL | NULL |    591 |                    |
| old      | ALL  | NULL             | NULL |    NULL | NULL | 122794 | where used |
+--------+------+---------------+------+---------+------+--------+------------+

Оказывается, для каждой записи из old происходит поиск в таблице street, т.е. просматриваются O(122794*591) строк.
Попробуем проиндексировать эти две таблицы по общему полю

create index street on street (nick);
create index street on old (street);


Тот же самый запрос теперь выглядит изнутри вот так:

+--------+------+---------------+------+---------+------+--------+
| table  | type | possible_keys | key  | key_len | ref  | rows   |
+--------+------+---------------+------+---------+------+--------+
| street | ALL    | street          | NULL |    NULL | NULL |    591    |
| old      | ALL    | street          | NULL |    NULL | NULL | 122794 |
+--------+------+---------------+------+---------+------+--------+
----------------------------------------------+
Extra                                        |
----------------------------------------------+
range checked for each record (index map: 1) |
----------------------------------------------+

и занимает 19.7 секунд. Даже с учетом ~1 мин. на создание индексов, выигрыш в скорости заметен. Разобравшись с индексами, можно их удалить

drop index street on street;
drop index street on old;


Создаем теперь новую таблицу phone

create table phone
(
phonum char(6) not null default "000000",
naim varchar(48) not null default "",
bd_id smallint unsigned not null references building,
other varchar(8) not null
);


Для заполнения последней таблицы даже не пробуем делать выборку из неиндексированых таблиц, а первым делом создаем индексы, используя заранее предусмотренное временное поле, соответствующее названию улицы.

create index building on building (tmp, bldng);
create index building on old (street, bldng);
insert into phone ( phonum, naim, bd_id, other )
select old.phonum, old.title, building.bd_id, old.other
from old, building
where old.street=building.tmp and old.bldng=building.bldng;


Индексы создались за 3 и 45 сек., а данные вставились за 19 сек. Теперь можно удалить рабочую таблицу и лишние индекс и поле:

drop table old;
drop index building on building;
alter table building drop tmp;


Подведем некоторые итоги.

Из исходного 8-мегабайтного текстового файла получились 3 связанные таблицы общим размером ~3.8MB. Простые запросы, например

select p.phonum, p.naim, s.nick, b.bldng
from phone p, street s, building b      # короткие синонимы таблиц
where
p.bd_id=b.bd_id                # таким образом
and b.st_id=s.st_id            #       связывают таблицы
and p.phonum like "%1234%"     # собственно запрос
order by p.naim;


занимают ~1.6 сек. Это приблизительно совпадает с результатом сканера grep на оригинальном текстовом файле при поиске тех же строк, и немного превосходит время, демонстрируемое интерпретатором awk.

Но, конечно, MySQL создан не для того, чтобы соревноваться с grep или awk. Используя язык SQL, можно создавать БД и манипулировать данными любой сложности. В области клиент-серверных приложений MySQL вполне способен конкурировать с признанными коммерческими СУБД. Но вся мощь MySQL раскрывается в соединении с технологиями Internet, если так можно выразиться, в "дважды клиент-серверных" технологиях. Доступ к БД выполняется из приложений, запускаемых на web-сервере, результат выдается в виде HTML страниц. Затем web-сервер доставляет страницу в клиентский браузер.

В таком виде web-сервер Apache и реляционная СУБД MySQL образуют необычайно масштабируемую платформу для создания приложений. MySQL успешно трудится на самых разных аппаратных платформах, включая суперкомпьютеры, и может обслуживать много web-серверов, работающих на одном или на разных компьютерах. А можно настроить такой же тандем для работы на единственной скромной персоналке.

Как уже упоминалось, для доступа к MySQL можно использовать разные языки программирования. Похожий выбор языков программирования предлагает также и Apache через дополнительные модули, расширяющие возможности сервера. Существуют и успешно развиваются общедоступные открытые проекты Zope и Midgard , объединяющие Apache, MySQL и распространенные языки программирования в интегрированную среду разработки с единым интерфейсом.

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

Знаете ли Вы, чем оранжевый цвет отличается от других ? Если нет, то знайте, что разница в скорости восприятия глазом отдельных цветов ра...

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Сжатие данных в целях экономии места и ускорения работы Oracle

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

2010-09-17