PHP и WEB для новичков (часть 12): Ключи, индексы и сложные запросы

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

До этих пор мы имели дело с одной таблицей в БД. Таблица называлась mp3 и содержала данные об MP3-файлах. Я специально не стал делать в этой таблице поля для хранения имени исполнителя или названия альбома.

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

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

Поэтому имеет смысл исполнителей и альбомы организовать в форме отдельных таблиц и связать с таблицей песен через ссылку. Схематически это можно изобразить так (рис. 1).

Каждый большой прямоугольник представляет собой таблицу. На данной схеме их три: mp3 (песни в файлах MP3); album (перечень альбомов); performer (все имеющиеся в БД исполнители).

Для компактности иллюстрации таблица mp3 показана не полностью, но как видно, в нее добавлены новые поля: id, perf_id, album_id.

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

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

Для обеспечения уникальности в таблице выделяют одно поле, что гарантированно является уникальным для таблицы. Это поле называют первичным ключом (primary key, PK).

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

Если таблица содержит ФИО работника, дату рождения, отдел, специальность, должность, то, какое бы поле не взяли, оно не будет уникальным, ведь существует вероятность, что в одном отделе будут работать двое сотрудников с одинаковыми данными должностями, специальностями, годами рождения или даже однофамильцы.

В таком случае каждой записи таблицы присваивают идентификатор - некоторое число, уникальное для таблицы (не повторяющееся внутри таблицы) и делают его первичным ключом. Так мы и сделали, добавив для таблицы mp3 еще одно поле id и сделали его первичным ключом. О полях perf_id и album_id чуточку позже.

Аналогичным образом мы создали еще две таблицы: performer (исполнители) с тем же полем id и именем или названием коллектива в поле name; album (альбомы) с полем id, названием альбома title и годом выхода year.

На схеме также видно стрелки, показывающие связи (зависимости) между таблицами. Разумеется, если есть песня, то она принадлежит какому-то альбому. Аналогично она принадлежит какому-то исполнителю.

Для упрощения задачи, будем считать, что песня принадлежит лишь какому-то одному альбому и одному исполнителю. Поэтому для каждой песни (в записи таблицы mp3) можем добавить ссылки на запись в таблице performer и ссылку на запись в таблице album. Этими ссылками и будут поля perf_id и album_id.

Если поле одной таблицы является ссылкой на запись другой таблицы, то такое поле называется внешним ключом (foreign key, FK).

С ключами разобрались. Теперь речь пойдет об индексах. Представьте себе, что вам необходимо работать с некоторой энциклопедией. Энциклопедия представляет собой набор статей. Вам необходимо быстро находить необходимую статью.

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

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

Рассмотрим пример с таблицей mp3. Для начала приведем ее к новому виду, который мы уже обсудили выше. Добавим в таблицу поля id, album_id и perf_id в phpMyAdmin во вкладке Структура (рис. 2)

После добавления новых полей удалим все записи из таблицы mp3 (закладка Обзор > Очистить).

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

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

Первичный ключ создается в phpMyAdmin кликом на ссылке Первичный в списке полей в закладке Структура.

Как только поле id стал первичным ключом, в списке полей он стал подчеркнутым, а в списке индексов под списком полей видим индекс по этому полю.

Поскольку мы должны связывать эту таблицу по полям album_id и perf_id с таблицами album и performer, то нам необходимо будет делать выборки по этим полям. Значит, необходимы индексы для этих полей. Создание индекса по полю выполняется в phpMyAdmin кликом на ссылке Индекс в списке полей в закладке Структура (рис. 4).

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

Если рассматривать пример с энциклопедией, то индексация (т.е. алфавитный указатель или размещение статей в алфавитном порядке) безусловно, увеличит скорость поиска.

Но если стоит задача добавления в энциклопедию новых статей, их удаление или редактирование, то львиная часть времени этих операций будет тратиться именно на обновление индекса - например, добавление номера страницы во многие позиции алфавитного указателя.

То же самое происходит и с таблицами БД. Чем больше индексов имеет таблица, тем больше скорость поиска в ней, но и меньше скорость добавления, удаления и изменения записей.

Поэтому имеет смысл создавать индексы только по тем полям, по которым непосредственно будет вестись поиск, а именно по тем, которые будут указываться в условии WHERE SQL-запросов. На самом деле скорость выборки из таблицы по проиндексированному полю почти не зависит от количества записей в ней.

Теперь рассмотрим запросы не по одной таблице, а по двум или больше. Для этого создадим уже описанные таблицы album и performer, перейдя непосредственно к базе multimedia и, создав их так же, как создавали таблицу mp3. Также создадим первичные ключи из полей id в этих таблицах.

Для того, чтобы было на чем тренироваться, добавим пару записей в каждую таблицу. Обратите внимание, что ключ id у нас уникальный, поэтому повторения значений в этом поле исключены. В поле id будем записывать числа по порядку - 1, 2, и т.д. Например, у меня получилась такая таблица album (рис. 5)

Обратите внимание, что хотя тут и есть некоторое соответствие - альбом с id=1 принадлежит исполнителю с id=1, а альбом с id=2 принадлежит исполнителю с id=2, но это просто случайность из-за малого количества песен в примере и не является правилом. Теперь таблица mp3 выглядит как на рис. 7.

Тут обратите внимание, что значения полей album_id и perf_id указывают на те id таблиц album и performer, которым принадлежат соответствующие песни, например, первая запись имеет album_id=1, что значит, что она принадлежит альбому с id=1, т.е. N3XT.

Теперь, когда у нас есть кое-какой материал для практики, рассмотрим, какие SQL-запросы мы можем выполнять для этих данных. Давайте вспомним, как мы делали выборку (SELECT) из одной таблицы. Это было приблизительно так:

Такой запрос означал выборку всех полей filename из таблицы mp3. Теперь поставим перед собой задачу вывести названия песен и их исполнителей. Я покажу формирование запроса по шагам. Во-первых, нам необходимо объединить в FROM две таблицы:

Если выполнить данный запрос, то вы заметите, что он выдаст все песни и исполнителей, но без соответствия одного другому (рис. 8), что нас совершенно не устраивает, поэтому нужно связать эти две таблицы условием:

Данный запрос можно расценивать так: "выбрать поля title и name из таблиц mp3 и performer так, чтобы поле perf_id записи таблицы mp3 равнялось полю id таблицы performer". Теперь результат будет правильный (рис. 9).

На самом деле условие mp3.perf_id=performer.id реализует внешнее связывание (внешний ключ) - связь между таблицами mp3 и performer по полям id=perf_id. Что собственно нам и нужно.

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

Кое-какая сложность состоит в том, что некоторые поля (например, id или title) существуют в разных таблицах. Поэтому, чтобы четко указать принадлежность поля к таблице, следует указывать имя таблицы, и ее поле через точку. Тем самым мы даем знать серверу, из какой именно таблицы поле мы имеем в виду. Полученный результат можно видеть на рис. 10.

Обратите внимание, что в результате имеются два столбца title. Если мы хотим, чтобы столбцы в таблице результата имели необходимое нам название, то можно воспользоваться так называемыми алиасами (alias). Алиасы указываются после названия поля или таблицы со служебным словом AS:

Разумеется, можно добавить и другие условия в этот запрос. Например, выберем то же самое, но чтобы песни были новее 2000 года:

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

Думаю, имеет смысл порекомендовать функцию CONCAT, которая позволяет "сшить" в единую строку несколько значений, перечисленных через запятую:

При этом, данную функцию допустимо указывать не только в секции SELECT, но и в условии WHERE. Получим результат на рис. 12.

Еще одна функция, которая может оказаться полезной - это LPAD, дополняющая слева строку str до длины count символом char. Формат функции:

Допустим, имеется таблица с полями целочисленных компонент даты рождения birth_year, birth_month, birth_day и надо не просто выдать даты в формате вида ГГГГ/ММ/ДД, а чтобы в случае коротких чисел месяца или дня они все равно занимали по два символа, например, 1961/04/12, т.е. были строго форматированны.

Подробнее о базовых функциях языка SQL можно почитать, скачав книгу Мартина Грубера.

На нашем сайте можно не только бесплатно скачать игры, но и документацию и книги по программированию на MIDLetPascal, Turbo Pascal 6, Turbo Pascal 7, Borland Pascal, по программированию устройств Sound Blaster, Adlib, VESA BIOS, справочник Norton Guide и много другой полезной информации для программистов, включая примеры решения реальных задач по созданию резидентных программ.

PHP и WEB для новичков (часть 12):

Ключи, индексы и сложные запросы

Ключи

Индексы

Выборка из нескольких таблиц

Вспомогательные функции