Обзор возможностей MySQL и оптимизация
Статья будет полезна как новичкам, так и профессионалам. Первые получат сведения об основных возможностях MySQL, без чтения документации. А уже имея представление о MySQL, можно обратится к соответствующим пособиям, для более детального изучения заинтересовавшей возможности. Для последних будет полезно узнать о некоторых способах проектирования баз данных и оптимизации.
Статья будет состоять из пунктов, назовем их рецептами. Автор попытался отсортировать их, для более удобного чтения. Некоторые рецепты, универсальны и подходят для всех СУБД. Вы можете читать их в любом порядке, пропуская то, что уже хорошо знаете.
Для того чтобы исключить не однозначную трактовку основных терминов, на которые опирается вся теория реляционных баз данных, в конце статьи приведен небольшой словарик.
1. Во всех версиях 3.хх не поддерживается ссылочная целостность и вложенные запросы(как это обойти-рецепт 6). По первичному ключу MySQL автоматически создает индекс.
Для других атрибутов можно создать дополнительные индексы. Индекс создается вместе с таблицей:
CREATE TABLE [имя_таблицы] (... ,INDEX|KEY [имя_индекса] (аттрибут1,...))
причем служебные поля KEY и INDEX - синонимы. Начиная с версии 3.22 индекс можно создать и отдельно:
CREATE INDEX [имя_индекса] ON [имя_таблицы] (аттрибут1,...)
Обычно дополнительные индексы, создаются для ускорения процесса выборки из базы данных. При его создании объем таблицы увеличивается, поэтому если скоростные параметры вас устраивают, то индекс создавать не нужно. Если скорость неприемлима, индекс добавляется для атрибутов указанных в запросе после конструкции WHERE:
ВЫБЕРИТЕ uid, data, comments FROM test_table WHERE uid = 90 AND data = 'test';
CREATE INDEX ind1 ON test_table (data))
2. Если из выборки необходимо извлечь лишь несколько строк, то в конструкции SELECT указывается LIMIT x,y.
x-смещение,
y-максимальное число строк
Параметр x-не обязателен.
При этом на выполнеие сокращенного запроса уходит меньше времени.
SELECT * ИЗ таблицы, где d> 900 LIMIT 100)
3. Иногда требуется сохранить результат выборки в файле. Это делается следующим образом:
SELECT ... INTO OUTFILE '/ путь / к / каталог / имя файла'.
Файл не должен существовать.
Обратная операция загрузки из файла в таблицу:
ЗАГРУЗИТЬ ДАННЫЕ INFILE '/ путь / к / каталог / имя файла' в таблицу TTO
Строки файла должны иметь формат вида (поле1\tполе2\t.., где \t - символ табуляции). Более сложный формат команды в данной статье рассматриваться не будет.
Последний вариант может применяться и в случае повышения скорости загрузки данных в таблицу. Предположим, ваша программа последовательно выполняет вставку большого количества строк в таблицу. Эта операция может занять несколько минут, в зависимости от объема данных и количества индексов. С помощью же LOAD DATA автору удалось повысить скорость вставки в 7 раз!
4. При частых операциях вставки, удаления и обновления данных таблицы(с типами - VARCHAR,BLOB,TEXT), возникает дефрагментация. Для того, чтобы ее уменьшить существует операция:
OPTIMIZE TABLE [имя_таблицы]
5. Удобной возможностью MySQL является авто увеличение значения поля. Создание такого поля:
CREATE TABLE .. pole int (7) AUTO_INCREMENT ...
Причем это поле должно быть объявлено, как первичный ключ и следовательно в таблице оно должно быть единственным.
При вставке в таблицу имя такого поля можно не указывать. Операция auto increment в MySQL упрощена. Например в Oracle для этого необходимо отдельно создавать, так называемую последовательнось(SEQUENCE). И при вставке ее явно указывать.
Иногда бывает нужно при добавлении данных в таблицы знать значения поля с AUTO_INCREMENT для вставленных строк, а лишние запросы слишком замедляют работу. Для этого существует функция LAST_INSERT_ID().
CREATE TABLE table1 (id AUTO_INCREMENT, comm varchar (20), PRIMARY KEY (id));
INSERT INTO table1 (comm) VALUES ('test');
INSERT INTO table2 (id_table1) VALUES (LAST_INSERT_ID ());
Или, если вы пишите на perl, то можно использовать значения объекта.
$ ДВГА -> { 'mysql_insertid'};
6. В версиях 3.xx не поддерживаются вложенные запросы. Но вместо них можно использовать временные таблицы. Пример создания такой таблицы:
СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ ВЫБРАТЬ ...
А потом делать выборку из нее с дополнительным условием. При написании интерфейса к MySQL на perl, при создании временных таблиц вышеописанным способом, предпочтительнее использовать метод 'do' вместо пары 'prepare-execute', так как эта функция работает быстрее.
7. Некоторые способы хранения резервных копий таблиц или данных.
a) Резервное копирование данных можно производить с помощью cgi-скриптов, написанных на shell. Администратор с определенной периодичностью запускает данный скрипт, данные сохраняются в файле. Если файл уже существет, то при запуске скрипта информация в нем обновляется. Скрипт можно запускать cron'ом, тогда команды echo нужно убрать.
#! / Bin / ш
DB = тест
USER = Пользователь
= ПАРОЛЬ
HOST = host.ru
экспорт БД ПОЛЬЗОВАТЕЛЬСКИЙ ПАРОЛЬ ХОСТ
echo "Content-type: text / plain"
эхо ""
cd / usr / local / mysql / bin
./mysqldump -h $ HOST -u $ USER -p $ ПАРОЛЬ -t $ DB> дБ
эхо "ОК"
Файл (db) представляет из себя интструкции INSERT ... . Ключ -t указывает не копировать информацию о таблицах - CREATE TABLE ... Другой скрипт запускает SQL-команды из файла.
#! / Bin / ш
DB = тест
USER = Пользователь
= ПАРОЛЬ
HOST = host.ru
экспорт БД ПОЛЬЗОВАТЕЛЬСКИЙ ПАРОЛЬ ХОСТ
cd / usr / local / mysql / bin
./mysql -D $ DB -h $ HOST -u $ USER -p $ ПАРОЛЬ <db
echo "Content-type: text / html"
эхо ""
эхо "ОК"
б) Начиная с версии 3.23.25 появилась возможность резервного копирования таблиц (без индексов).
BACKUP TABLE [имя] TO '/dir';
И восстановления
ВОССТАНОВИТЬ ТАБЛИЦУ [имя] ИЗ '/ dir';
в) Еще одой возможностью является, приведенный выше способ записи результата запроса в файл.
SELECT ... INTO OUTFILE '/ dir / file';
И последующего восстановления из файла.
ЗАГРУЗИТЬ ДАННЫЕ ...
8. Приятно удивила возможность полнотекстового идексирования полей VARCHAR и TEXT(начиная с версии 3.23.23). В запросе указываетя подстрока, и в результате выводятся те значения полей где она(подстрока) найдена. Например вы храните в БД статьи или анонсы статей и хотите предоставить поиск по статьям пользователю. Можно конечно установить поисковик(пример Yandex.Site), но для этого необходимо заводить отдельную БД, да и если требования к поисковой системе минимальны, данная установка просто излишество.
MySQL предоставляет возможность полнотекстового поиска. Проиллюстрирую на примере:
СОЗДАТЬ ТАБЛИЦУ данных (сделал int (7) AUTO_INCREMENT, текст, FULLTEXT (a), ПЕРВИЧНЫЙ КЛЮЧ (сделал));
INSERT INTO data (a) VALUES ('test message'), ('test mysql fulltext'), ('test test test');
ВЫБЕРИТЕ * ИЗ ДАННЫХ, ГДЕ СООТВЕТСТВУЮЩИМ (a) ПРОТИВ ('mysql');
В результате выведется строка = 'test mysql fulltext'. А по запросу
ВЫБЕРИТЕ данные, СООТВЕТСТВУЮЩИЕ (a) ПРОТИВ ('mysql') как m ИЗ данных;
поле m будет содержать числовое значение. Как их использовать оставляю на ваше усмотрение.
Таким образом организован простейший поиск.
9. Некоторые советы по оптимизации:
После заполнения данными таблицы, выполните команду 'isamchk -a' или 'myisamchk -a', в зависимости от типа таблицы. Эта команда соберет статистику по таблицам, которую сможет использовать оптимизатор.
Удалите ненужные скобки в запросе.
Всегда, где это возможно используйте WHERE вместо HAVING
Свойство атрибута NOT NULL экономит 1бит и ускоряет запросы
Испольэование типа CHAR вместо VARCHAR, позволит ускорить выборку по этим полям на 10-100%. Это целесообразно делать если количество символов поля фиксировано, приэтом оно принимает различные значения. В случае переменной длины, придется расплачиваться дисковым пространством. Вообще поля фиксированной длины обрабатываются быстрее. Выбор того или иного типа зависит от решаемой задачи и требований к БД.
Если выборки из нескольких таблиц слишком замедленна, а дополнительые индексы не хочется создавать, разбейте один большой запрос на несколько. Пример из жизни: выборка из 3 таблиц, со "сложным" условиям длилась 4мин.!!!. Причем все таблицы имели первичный ключ. Разбив один запрос на 2 удалось ускорить выборку на 3мин. 54сек. Но задача оптимизации индивидуальна и зависит от структуры БД и данный метод возможно подходит не для всех БД. Так что для собственной базы необходимо проводить исследования.
10. Одним из способов организации данных является определение в одной таблице и первичных и внешних ключей. Я сталкивался с такой таблицей, правда в Oracle. Это бывет удобным в случае когда, "подчинненная" строка имеет такую же структуру, скажем иерархия отделов и должностей в организации. Для извлечения всей иерархии заданного объекта, необходимо строить рекурсию, к сожалению в MySQL нет конструкции - connect by prior.
Для достижения необходимой производительности может помочь избыточность. Есть некоторое правило - в мастер таблицу(таблицу с первичным ключем) заносится последнее значение объекта. Например для таблицы продавцов, это может быть последняя осуществленная сделка.
Вот некоторые соображения, которые могут пригодится при проектировании БД в MySQL. На сегодняший день она получила наибольшее распространение в internet, в первую очередь благодаря своей простоте и минимальным требованиям к системным ресурсам. Конечно она не может конкурировать с "монстрами" СУБД, такими как, например Oracle и для крупных проектов ее будет недостаточно.
Но на MySQL возложены совершенно иные задачи, с которыми, на мой взгляд, она прекрасно справляется.
Основные термины:
атрибут(поле) - определенная атомарная часть информации об объекте.
первичный ключ - атрибут или совокупность атрибутов однозначно идентифицирующих объект.
кортеж(строка) - вся информация об одном объекте в таблице. Совокупность значений всех атрибутов объектов.
