Анатолий Мотев СанктПетербург бхвпетербург 2006 удк 681 06 ббк 32. 973. 26018. 2 М85
 Скачать 4.25 Mb.
|
|
82 Òàáëèöà 7.2. Òàáëèöà èíäåêñîâ äëÿ ïîëÿ id_addr id_addr 2 2 3 4 5 5 6 7 Соответственно, если потребуется найти читателей с номером адреса 2, то просмотр таблицы вернет нам два значения. Поиск можно прекратить по дос- тижению следующего номера (3), т. к. после него нужных нам значений нет. Если нужно найти какое-то значение, находящееся где-то в середине, то для этого есть специальные алгоритмы позиционирования, позволяющие быстро найти нужное значение без просмотра всех записей. Механизм индексирования еще более полезен при запросе данных из не- скольких таблиц. Допустим, при запросе к одной неиндексированной таб- лице, состоящей из 1000 записей, нам нужно просмотреть и проанали- зировать только 1000 строк. В случае, если таких таблиц две, получится 1000 × 1000 = 1 000 000 записей. А если их пять?! Индексация таблиц позволяет СУБД MySQL ускорить поиск записей, удов- летворяющих условию, описанному в предложении WHERE . Кроме этого ин- дексирование позволяет ускорить поиск минимального и максимального зна- чений в поле, сделать более быстрыми операции сортировки и группировки значений. Íåäîñòàòêè У индексирования есть и недостатки, но они столь незначительны, что с лих- вой перекрываются преимуществами. Индексный файл занимает определен- ное место на жестком диске сервера и поэтому при большом количестве ин- дексов он может быстро достичь максимального размера. Кроме этого индек- сы ускоряют поиск данных, но замедляют операции добавления, удаления и обновления в индексированных полях. Óðîê 7. Ñîçäàíèå è óäàëåíèå òàáëèö 83 Ñîçäàíèå èíäåêñà В MySQL можно проиндексировать столбцы всех типов. Максимальное ко- личество ключей и максимальная длина индексов зависят от типа таблицы (для типа MyISAM — по умолчанию 32 ключа в одной таблице и 500 байт на ключ). Первичный ключ индексируется автоматически. Таблицу можно индексировать как по одному, так и по нескольким полям. Индекс может содержать как уникальные, так и повторяющиеся значения. Для полей типа CHAR и VARCHAR можно индексировать префикс поля (несколь- ко первых символов), это намного быстрее и требует меньше места на диске, нежели индексация всего поля. Для полей типа TEXT и BLOB также необходимо индексировать префикс поля (до 255 байт). Нельзя индексировать поле це- ликом. Синтаксис инструкции для индексации префикса поля при создании таблицы может выглядеть примерно так: KEY имя_индекса (имя_поля(длина)) где имя_поля(длина) — имя поля с указанием количества индексируемых сим- волов. Пример запроса с применением такого индексирования показан на рис. 7.1. Рис. 7.1. Создание индекса для первых 10 символов поля title Просмотреть индексы вы можете с помощью следующей команды: SHOW INDEX FROM имя_таблицы Можно создавать индексы нескольких типов: обычный индекс — позволяет содержать дублирующиеся значения; уникальный индекс — запрещает наличие дублирующихся значений; индекс FULLTEXT — предназначен для осуществления контекстного поиска. Кроме оператора CREATE TABLE , для создания индексов можно использовать операторы CREATE INDEX и ALTER TABLE (это позволит создавать индексы в уже существующих таблицах). Синтаксис инструкции создания обычного индекса: ALTER TABLE имя_таблицы ADD INDEX имя_индекса (имя_поля); ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 84 Синтаксис инструкции создания уникального индекса: ALTER TABLE имя_таблицы ADD UNIQUE имя_индекса (имя_поля); Синтаксис инструкции создания первичного ключа: ALTER TABLE имя_таблицы ADD PRIMARY KEY (имя_поля); Синтаксис инструкции создания индекса типа FULLTEXT : ALTER TABLE имя_таблицы ADD FULLTEXT (имя_поля); Здесь имя_поля — имя индексируемого поля (полей). С помощью оператора CREATE INDEX вы можете добавить все перечисленные индексы (кроме PRIMARY KEY ): CREATE INDEX имя_индекса ON имя_таблицы (имя_поля); CREATE FULLTEXT INDEX имя_индекса ON имя_таблицы (имя_поля); CREATE UNIQUE INDEX имя_индекса ON имя_таблицы (имя_поля); Что же касается индексации по префиксу поля, то, как показано на рис. 7.1, вы должны указать количество символов ( n ). Приведу еще один пример: CREATE TABLE mytable (first_name VARCHAR(30), last_name VARCHAR(30), INDEX (first_name(), last_name(5))); Учтите, что длина префикса это количество байт, а не символов. То есть для однобайтовых кодировок это одно и то же, а для многобайтовых — нет. Индексирование по префиксу поля накладывает определенные ограничения на изменение длины поля. Нельзя делать длину такого поля меньше, чем длина объявленного индекса. Вам придется удалить индекс, изменить длину поля, а затем воссоздать индекс снова с более коротким префиксом. Поля с индексами типа FULLTEXT не могут иметь префикса. Óäàëåíèå èíäåêñà Удалить индекс можно с помощью оператора DROP INDEX или ALTER TABLE : DROP INDEX имя_индекса ON имя_таблицы; ALTER TABLE имя_таблицы DROP INDEX имя_индекса; Удаление индекса PRIMARY KEY : ALTER TABLE имя_таблицы DROP PRIMARY KEY; Если вы удаляете поле из таблицы, вы тем самым удаляете это поле из индек- са. Если удалить все индексированные поля, то удалится весь индекс. Óðîê 7. Ñîçäàíèå è óäàëåíèå òàáëèö 85 Ïðàâèëüíûé âûáîð ïîëÿ äëÿ èíäåêñèðîâàíèÿ Итак, в предыдущих разделах мы узнали, что такое индексы, для чего они нужны и как с ними работать. Но знания синтаксиса операторов недостаточ- но, чтобы эффективно применять индексирование для ускорения работы сис- темы. Давайте поговорим немного о том, какие поля лучше всего подходят на роль индекса. В качестве индексируемых нужно выбирать те поля, по которым будут про- изводиться сортировка и группировка, а не те, которые будут выбираться. То есть индексируйте поля, которые участвуют в предложении WHERE , ORDER BY или GROUP BY Рассмотрим пример запроса: mysql> SELECT title, year_issue FROM book WHERE id_author>3; Поля title и year_issue не стоит индексировать, а вот поле id_author являет- ся кандидатом на индексирование. Подробное описание оператора SELECT приведено в уроке 12. Индексы лучше работают со столбцами, содержащими уникальные значения или значения, которые повторяются редко. Например, если у вас есть поле, хранящее данные о возрасте сотрудников компании, то значения в этом поле будут повторяться нечасто. Если данные повторяются очень часто (например, какое-то значение повторяется в 40% записей), то индекс будет, в принципе, бесполезен. Например, поле, содержащее сведения о половой принадлежно- сти сотрудников ( Male или Female ), не нуждается в индексировании, т. к. все равно придется просматривать всю таблицу. С другой стороны, не стоит индексировать все, что попадется под руку. Пом- ните, каждый созданный вами индекс занимает место на диске и замедляет операции записи. При изменениях в таблице определенные изменения проис- ходят и с индексами, и чем их больше, тем больше времени это занимает. Óäàëåíèå è ïåðåèìåíîâàíèå òàáëèö С удалением таблиц дело обстоит гораздо проще. Для удаления таблицы ис- пользуется следующий оператор: DROP TABLE имя_таблицы [,имя_таблицы...] Например, команды mysql> DROP TABLE reader; достаточно, чтобы удалить таблицу reader ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 86 С помощью данного оператора можно удалить как одну, так и несколько таб- лиц, перечислив их имена через запятую. Здесь также можно использовать ключевые слова IF EXISTS для предупреждения возникновения ошибки в слу- чае, если таблица с указанным именем не существует. На рис. 7.2 приведен пример удаления таблицы. Сначала для БД test (стан- дартная БД) был включен режим консоли, а затем создана произвольная таб- лица с именем mytable , содержащая два поля. С помощью команды SHOW TABLES мы просмотрели список таблиц, а затем удалили только что созданную таблицу. Следующая команда SHOW TABLES вывела для нас сообщение о том, что список таблиц пуст. Рис. 7.2. Удаление таблицы Рис. 7.3. Переименование таблицы Óðîê 7. Ñîçäàíèå è óäàëåíèå òàáëèö 87 Также можно переименовать уже созданную таблицу (рис. 7.3): RENAME TABLE старое_имя_таблицы TO новое_имя_таблицы ÂÎÏÐÎÑÛ ÄËß ÑÀÌÎÊÎÍÒÐÎËß 1. Какие операторы можно использовать для удаления индекса? 2. Какие типы индексов можно создавать в MySQL? ÓÐÎÊ 8 Èçìåíåíèå ñòðóêòóðû òàáëèöû Возможность изменять структуру таблицы обеспечивает оператор ALTER TABLE . С его помощью вы можете, например, добавлять и удалять поля табли- цы, переименовывать поля или изменять их тип. Во время выполнения опера- тора ALTER TABLE MySQL создает временную копию таблицы (кроме команды RENAME ) и все изменения производятся с копией. Затем исходная таблица уда- ляется, а временная переименовывается. Благодаря оператору ALTER TABLE можно изменять уже существующую и работающую БД. Например, некото- рые типы, назначенные для полей, могут оказаться недостаточными для хра- нения всех значений или, наоборот, слишком большими. Или может появить- ся необходимость добавить еще несколько полей в таблицу. Общий синтаксис оператора: ALTER TABLE имя_таблицы операция1 [,операция2...] Операции, которые можно выполнить над таблицей: ADD описание_поля — добавляет поле к таблице (параметр описание_поля имеет такой же формат, как в операторе CREATE TABLE ). По умолчанию по- ле добавляется после последнего поля таблицы. Если указать ключевое слово FIRST , то поле будет вставлено первым. Если указать предложение AFTER имя_поля , то новое поле будет вставлено после поля имя_поля . При помощи операции ADD вы можете добавить различные индексы: • ADD FULLTEXT ( имя_поля) — индекс типа FULLTEXT (работает, начиная с MySQL 3.23.23); • ADD INDEX ( имя_поля) — добавляет обычный индекс (неуникальный); • ADD PRIMARY KEY — добавляет первичный ключ; • ADD UNIQUE — добавляет индекс с уникальным значением; Óðîê 8. Èçìåíåíèå ñòðóêòóðû òàáëèöû 89 ALTER имя_поля SET DEFAULT значение | DROP DEFAULT — изменяет значение поля, установленное по умолчанию, или удаляет значение по умолчанию. Например: mysql> ALTER TABLE address ALTER city SET DEFAULT 'Санкт-Петербург'; или mysql> ALTER TABLE address ALTER city DROP DEFAULT; CHANGE старое_имя_поля описание_поля — изменяет имя и описание поля ( старое_имя_поля — имя изменяемого поля, описание_поля — его новое описание, формат такой же, как в операторе CREATE TABLE ); DROP имя_поля — удаляет определенное поле. Также можно удалять ин- дексы: • DROP INDEX имя_индекса ; • DROP PRIMARY KEY ; MODIFY описание_поля — изменяет описание поля; RENAME новое_имя_таблицы — переименовывает таблицу. Давайте рассмотрим некоторые операции на примере. Создадим в БД test произвольную таблицу (рис. 8.1) и поэкспериментируем над ней. Рис. 8.1. Создание экспериментальной таблицы Далее добавим к нашей таблице новое поле с помощью опции ADD (рис. 8.2). Введите команду: mysql> ALTER TABLE myuser ADD description text; Рис. 8.2. Добавление нового поля к таблице ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 90 С помощью команды DESC имя_таблицы (сокращение от DESCRIBE ) можно про- смотреть структуру таблицы и определить, правильно ли выполнилась какая- либо команда. Не забывайте ставить в конце команды символ ';'. Команды языка SQL не чувствительны к регистру (я использую верхний регистр просто для удобства). То есть можно ввести команду и так: desc имя_таблицы; Если требуется указать местоположение поля, используйте ключевые слова FIRST и AFTER: mysql> ALTER TABLE myuser ADD description text FIRST; mysql> ALTER TABLE myuser ADD description text AFTER id_user; А сейчас мы попробуем с помощью опции CHANGE внести изменения в только что добавленное поле. Например, изменим его тип: mysql> ALTER TABLE myuser CHANGE description description TINYTEXT; Рис. 8.3. Изменение типа поля description Здесь обязательно дважды указать имя изменяемого поля. Сначала указыва- ется имя поля, которое будет изменяться, а затем указывается полное описа- ние поля, включая его имя. Если ввести команду mysql> ALTER TABLE myuser CHANGE description comment TINYTEXT; то изменится не только тип поля, но и его имя. Если вы не собираетесь изме- нять имя поля, то вместо опции CHANGE можно использовать опцию MODIFY В этом случае не придется дважды указывать имя поля. mysql> ALTER TABLE myuser MODIFY description TINYTEXT; Пришло время для удаления. Опция DROP позволяет удалить поле: mysql> ALTER TABLE myuser DROP desrcription; Óðîê 8. Èçìåíåíèå ñòðóêòóðû òàáëèöû 91 Этим запросом мы удалим из таблицы myuser поле description. Результат выполнения запроса представлен на рис. 8.4. Рис. 8.4. Удаление полей Если таблица содержит только одно поле, то его нельзя удалить. Вместо это- го вы можете удалить таблицу, воспользовавшись командой DROP TABLE Наконец, можно переименовать таблицу с помощью опции RENAME оператора ALTER TABLE : ALTER TABLE старое_имя_таблицы RENAME новое_имя_таблицы; Для переименования можно также воспользоваться оператором RENAME TABLE : RENAME TABLE старое_имя_таблицы TO новое_имя_таблицы; Рис. 8.5. Переименование таблицы при помощи опции RENAME ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 92 В отличие от переименования с помощью оператора ALTER TABLE , оператор RENAME TABLE позволяет переименовать сразу несколько таблиц: RENAME TABLE таблица1 TO врем_табл, таблица2 TO таблица1, врем_табл TO таблица2; Нельзя переименовать таблицу, если ее новое имя совпадает с именем другой существующей таблицы. Итак, теперь вы можете создавать, удалять и редактировать таблицы. Нам вновь нужно вернуться к учебной БД и создать все таблицы, которые мы спроектировали. Чтобы немного ускорить процесс, создайте в каталоге C:\MYSQL\BIN текстовый файл (например, LIBRARY.TXT) или скопируйте его с сопроводительного компакт-диска книги. В нем мы опишем набор команд необходимый для создания таблиц, а потом с помощью утилиты MYSQL.EXE выполним их все за один раз. В коде можно оставлять одно- строчные комментарии, используя символы '--' . В листинге 8.1 приведен код, создающий все таблицы для БД library Листинг 8.1 CREATE TABLE abonement ( id_note MEDIUMINT(5) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, id_reader MEDIUMINT(5) UNSIGNED, id_unit mediumint(6) UNSIGNED, get_date DATE, exp_date DATE); CREATE TABLE address ( id_addr MEDIUMINT(5) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, address VARCHAR(80), phone VARCHAR(19)); CREATE TABLE reader ( id_reader MEDIUMINT(5) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, reader VARCHAR(60), id_addr MEDIUMINT(5)); CREATE TABLE section ( id_section TINYINT(2) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, section VARCHAR(30)); Óðîê 8. Èçìåíåíèå ñòðóêòóðû òàáëèöû 93 CREATE TABLE publisher ( id_publisher SMALLINT(3) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, publisher VARCHAR(30)); CREATE TABLE author ( id_author SMALLINT(4) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, author VARCHAR(60)); CREATE TABLE unit ( id_unit MEDIUMINT(6) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, id_book MEDIUMINT(5)); CREATE TABLE book ( id_book MEDIUMINT(5) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, title VARCHAR(50), year_issue YEAR, id_author SMALLINT(4) UNSIGNED, id_publisher SMALLINT(3) UNSIGNED, id_section TINYINT(2)); После этого нужно выполнить все команды, занесенные в файл, с помощью утилиты MYSQL.EXE (рис. 8.6). Для выхода из режима консоли используйте уже знакомую вам команду exit : mysql> exit Bye C:\mysql\bin> Рис. 8.6. Выполнение команд из файла LIBRARY.TXT Утилита MYSQLSHOW.EXE поможет вам убедиться, что все выполнилось правильно. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 94 После создания БД рекомендую сделать дамп (сохранение) на случай сбоя операционной системы, нечаянного удаления каких-либо файлов и других стихийных бедствий: C:\mysql\bin> mysqldump -uroot --add-drop-table library>db_library.txt В листинге 8.2 приведено содержимое файла DB_LIBRARY.TXT. Листинг 8.2 -- MySQL dump 8.23 -- -- Host: localhost Database: library --------------------------------------------------------- -- Server version 3.23.58-max-debug -- -- Table structure for table 'abonement' -- DROP TABLE IF EXISTS abonement; CREATE TABLE abonement ( id_note mediumint(5) unsigned NOT NULL auto_increment, id_reader mediumint(5) unsigned default NULL, id_unit mediumint(6) unsigned default NULL, get_date date default NULL, exp_date date default NULL, PRIMARY KEY (id_note) ) TYPE=MyISAM; -- -- Dumping data for table 'abonement' -- -- -- Table structure for table 'address' -- DROP TABLE IF EXISTS address; CREATE TABLE address ( id_addr mediumint(5) unsigned NOT NULL auto_increment, address varchar(80) default NULL, phone varchar(19) default NULL, PRIMARY KEY (id_addr) ) TYPE=MyISAM; Óðîê 8. Èçìåíåíèå ñòðóêòóðû òàáëèöû 95 -- -- Dumping data for table 'address' -- -- -- Table structure for table 'author' -- DROP TABLE IF EXISTS author; CREATE TABLE author ( id_author smallint(4) unsigned NOT NULL auto_increment, author varchar(60) default NULL, PRIMARY KEY (id_author) ) TYPE=MyISAM; -- -- Dumping data for table 'author' -- -- -- Table structure for table 'book' -- DROP TABLE IF EXISTS book; |