Январь 20, 2006
Лабораторная работа - MySQL (Часть 5)
Но вот борьба позади, и мы приступаем к разбиению единой таблицы на несколько связанных и нормализованных, что, собственно, и дает право называться СУБД реляционной.
Из таблицы 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;
продолжение следует...
Часть 4 |
Окончание |
Разместил:
Автор:
Источник: GomeLUG, группа пользователей Линукс, Гомель, BY