Лабораторная работа: 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 и распространенные языки программирования в интегрированную среду разработки с единым интерфейсом.

Читать комменты и комментировать

Добавить комментарий / отзыв



Защитный код
Обновить

Лабораторная работа: MySQL | | 2010-09-12 23:34:06 | | Базы данных | | Заняться выполнением этой лабораторной работы меня побудили несколько причин. Во-первых, занимаясь построением серверов и сетей на основе Линукса, я догадывался, что где-то совсем рядом лежит | РэдЛайн, создание сайта, заказать сайт, разработка сайтов, реклама в Интернете, продвижение, маркетинговые исследования, дизайн студия, веб дизайн, раскрутка сайта, создать сайт компании, сделать сайт, создание сайтов, изготовление сайта, обслуживание сайтов, изготовление сайтов, заказать интернет сайт, создать сайт, изготовить сайт, разработка сайта, web студия, создание веб сайта, поддержка сайта, сайт на заказ, сопровождение сайта, дизайн сайта, сайт под ключ, заказ сайта, реклама сайта, хостинг, регистрация доменов, хабаровск, краснодар, москва, комсомольск |
 
Поделиться с друзьями: