Интернет технологии -> PHP -> Веб-программирование на PHP (Часть V)

Веб-программирование на PHP (Часть IV)

42. MySQL: Коррекция таблиц, дамп/загрузка данных
43. MySQL: Использование индексов

^^^   42. MySQL: Коррекция таблиц, дамп/загрузка данных

Что помогает работать с MySQL.

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

Итак. Чего полезного умеет MySQL.

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

Далее. Обращаться с запросами к MySQL можно как для работы с данными, так и для настройки самого MySQL или для настройки базы. Для работы с MySQL можно использовать любой SQL-клиент, который вам наиболее привычен и удобен. Лично я использую стандартного клиента MySQL, работающего в UNIX-shell. Обычное общение с MySQL сильно напоминает работу в shell или в DOS (для тех, кто никогда не видел в глаза UNIX).

Для начала, вы подключаетесь к MySQL, что у меня на моем хостинге в masterhost.ru выглядит как:

[atos]$ /usr/local/bin/MySQL -h MySQL.int -u 21ru -p 21ru

После нажатий enter-а, MySQL запросит у меня пароль, проверит его и пустит в свой shell:

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  CoMySQLands end with ; or \g.
Your MySQL connection id is 3060933 to server version: 3.23.49

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

MySQL>

Это подключение ничем не отличается от тех, которые совершают ваши скрипты. Они тоже подключаются к MySQL, отправляют в него запрос, получают ответ и закрывают сессию. Работа в MySQL shell отличается только тем, что мы долго держим открытой сессию, последовательно передавая MySQL всевозможные запросы. Кстати, много уроков назад, где мы создавали нашу базу данных, я цитировал именно ответы данного MySQL-клиента.

* * *

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

Можно модифицировать сами эти таблицы. Для этого используется запрос с ключевым словом alter table. Например, нам надо добавить в существующую таблицу tbl_text поле text_length в формате int с изначальным значением 100. Пишем:

alter table tbl_text add column text_length int not null default 100; [enter]

Кстати, в shell MySQL можно писать запросы в несколько строк. MySQL не начнет интерпретировать ваш запрос, пока вы не закончите его точкой с запятой ";" и enter-ом, следующим за этим знаком.

Подробнее о возможных преобразованиях таблиц можно почитать тут: http://www.MySQL.com/doc/A/L/ALTER_TABLE.html.

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

* * *

Еще хочется обратить внимание на проблемы многих пользователей, "перевозящих" данные из других баз данных.

Зачастую получается так, что нельзя подключиться сразу к двум базам и перелить данные из одной БД в другую. Особенно, когда старая база данных в старом неведомом формате или вообще представляет собой что-то самодельное. В подобных случаях пользователь начинает писать скрипт, который "сосет" данные из файла, парсит их и при помощи банального insert-а укладывает в таблицы. В общем, в этом подходе нет ничего предосудительного, особенно, когда вам необходимо залить десяток-другой тысяч строк. Но если ваша база похожа на настоящую, и количество записей измеряется миллионами, то лучше воспользоваться специальной возможностью MySQL, которая зовется LOAD DATA INFILE.

Это очень гибкая функция MySQL, позволяющая прочесть из файла данные в базу. При этом, гибкость заключается в том, что вы можете описать формат хранения данных в файле, указать диапазон данных, подлежащих заливке и так далее. Подробно эта функцию описана тут: http://www.MySQL.com/doc/L/O/LOAD_DATA.html. И, что особенно важно, скорость заливки данных при помощи LOAD DATA намного превышает построчную скорость заливки insert-ом. Надеюсь, функция LOAD DATA окажется вам полезной.

* * *

Ну а что делать, если у вас есть необходимость перенести не только ваши данные, но и всю конфигурацию базы: таблицы, правила, индексы и так далее? В подобном случае нам очень полезно будет дампироование базы.

Что такое дамп базы. Это вывод структуры и данных из базы в формате SQL-запросов. Поясню. Если у нас в базе есть таблицы, а в таблицах данные, то в дамп мы получим SQL-запросы на построение таблиц и insert-ы для заполнения этих таблиц данными. Да что я вам рассказываю, вы сами можете сделать дамп любой своей базы посмотреть на него.

В пакете MySQL есть mysqldump который этим и занимается. Запуск mysqldump очень похож на запуск стандартного клиента MySQL, о котором я писал вначале этого выпуска. То есть, указывается база, указывается пользователь и название database, дампировать которую мы собрались.

Например:

[atos]$ /usr/local/bin/mysqldump -h MySQL.int -u 21ru -p 21ru

Полный список ключей mysqldump можно получить:

[atos]$ mysqldump -help

[atos]$ /usr/local/bin/mysql -h MySQL.int -u 21ru -p 21ru < base.dump

MySQL подключится к базе (не забыв спросить у вас пароль) и построчно выполнит все инструкции из файла base.dump. Как видите, все просто. Попробуйте сами.

Итак. Если вы хотите перенести свой проект, написанный на php+MySQL, то это можно сделать в несколько несложных шагов:

1. Делаем дапм базы (с данными, если нужна копия проекта и без данных, если нужен новый чистый проект).
2. Копируем файлы самого сайта
3. Правим пути в php-файлах, если они изменились.

И все.

Таким образом я позавчера создал копию форума http://forum.21.ru на новом месте. Я не мог воспользоваться программой инсталляции форума, так как мой вариант уже очень сильно отличается от своего прародителя.

Кроме того, вы можете пользоваться mysqldump для бэкапа вашей базы. Не забывайте, что MySQL отличается от своих старших братьев отсутствием транзакций. Другими словами, поданную MySQL команду уже никак нельзя отменить. И если вы написали delete from table tbl_name и нажали enter прежде чем ограничить удаление какими-то критериями, таблица tbl_name будет безвозвратно очищена от данных. И в этом случае вас очень выручит mysqldump сделанный незадолго до этого.

* * *

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

Но это уже темы для других выпусков, которыми я буду перемежать выпуски по web-программированию.

^^^   43. MySQL: Использование индексов

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

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

Что же это за зверь такой - индексация? Попробую объяснить на примере. Вспомните любую публичную библиотеку. Пусть детскую или даже школьную. Помните зал со стеллажами книг? И даже если вы были очень давно в библиотеке, вы прекрасно знаете, что все книги в этом уважаемом заведении расставлены не абы как, не в порядке их поступления в библиотеку (как поступают данные в базу), а по каким-то правилам. Обычно, книги разносят по темам, авторам и по алфавиту.

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

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

Так какого же хрена, прошу прощения за резкость, мы заставляем наши компьютеры искать необходимые нам данные методом тупого перебора? Только потому, что они это делают быстро? Да, быстро. Но это если поиск надо произвести в тысячах записей. А если речь идет о миллионах? Или вы думаете, что ваша база данных не так велика, чтобы заниматься ее оптимизацией? Ошибаетесь, дорогие мои. Как только к вашей базе обратятся сотни человек, так ваши тысячи записей тут же превратятся для движка баз данных в миллионы! И ваш провайдер совершенно резонно сделает вам замечание.

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

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

А индексировать надо те поля таблицы, по которым происходит поиск или сортировка данных. Например, у нас есть таблица books и таблица authors. В первой мы храним информацию о книгах, а во второй - информацию об их авторах. Разумеется, самое логичное для подобной базы - искать в ней книги по названию и по автору. Не имея индексации такой базы, примитивный поиск по первым буквам произведения вынудит компьютер просмотреть все записи в таблице, чтобы выдать полный результат. Если база большая, на это уйдет время. Если же мы добавим в таблицу books индекс по полю book_name (название книги), то MySQL создаст индекс этой таблицы. То есть, отсортирует таблицу по указанному полю и расставит метки и ссылки на ячейки в реальной таблице.

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

Еще проще говоря, если мы ищем книги на букву "М", то компьютер не станет перебирать записи, начинающиеся на другие буквы, прекрасно зная, что там нет записей, удовлетворяющих запросу. И если в нашей книжной базе находится 100.000 книг, среди которых только 30 на букву Ж, то по запросу "найти все книги на букву Ж", компьютер переберет только 30 записей при наличии индекса, или переберет 100.000 записей при его отсутствии. По-моему, польза очевидна.

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

Скажем, если в таблице книг есть поле book_date, хранящее дату публикации книги, то добавление индекса к такому полю будет выглядеть примерно так:

alter table tbl_books add index i_date (book_date);

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

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

Другое ключевое слово, создающее индексы - KEY. Помните объявление primary key, которое я обязательно использую в каждой таблице для id-поля. Если вы хотите увидеть, какие поля в таблице проиндексированы, дайте команду MySQL:

desc tbl_name; 

Или более подробно, только об индексах:

show index from tbl_name;

alter table tbl_name add index i_name (field1, field2);

О синтаксисе создания индексов лучше всего написано тут: http://www.mysql.com/doc/A/L/ALTER_TABLE.html.

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

Удачной индексации, и да прибудет с вами порядок!