Краткий обзор некоторых возможностей MySQL. Советы по оптимизации.

Краткий обзор некоторых возможностей MySQL. Советы по оптимизации.

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

Статья будет состоять из пунктов, назовем их рецептами. Автор попытался отсортировать их, для более удобного чтения. Некоторые рецепты, универсальны и подходят для всех СУБД. Вы можете читать их в любом порядке, пропуская то, что уже хорошо знаете.

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

1) Во всех версиях 3.хх не поддерживается ссылочная целостность и вложенные запросы(как это обойти-рецепт 6). По первичному ключу MySQL автоматически создает индекс.

Для других атрибутов можно создать дополнительные индексы. Индекс создается вместе с таблицей -
CREATE TABLE [имя_таблицы] (... ,INDEX|KEY [имя_индекса] (аттрибут1,...))
причем служебные поля KEY и INDEX - синонимы. Начиная с версии 3.22 индекс можно создать и отдельно -
CREATE INDEX [имя_индекса] ON [имя_таблицы] (аттрибут1,...).
 

Обычно дополнительные индексы, создаются для ускорения процесса выборки из базы данных. При его создании объем таблицы увеличивается, поэтому если скоростные параметры вас устраивают, то индекс создавать не нужно. Если скорость неприемлима, индекс добавляется для атрибутов указанных в запросе после конструкции WHERE.
(Пример:
SELECT 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 * FROM table WHERE d > 900 LIMIT 100)

3) Иногда требуется сохранить результат выборки в файле. Это делается следующим образом -
SELECT ... INTO OUTFILE '/path/to/dir/filename'.
Файл не должен существовать.

Обратная операция загрузки из файла в таблицу -
LOAD DATA INFILE '/path/to/dir/filename' INTO TABLE table. Строки файла должны иметь формат вида (поле1tполе2t.., где 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, то можно использовать значения объекта.
$dbh->{'mysql_insertid'};

6) В версиях 3.xx не поддерживаются вложенные запросы. Но вместо них можно использовать временные таблицы. Пример создания такой таблицы:
CREATE TEMPORARY TABLE table SELECT ... .
А потом делать выборку из нее с дополнительным условием. При написании интерфейса к MySQL на perl, при создании временных таблиц вышеописанным способом, предпочтительнее использовать метод 'do' вместо пары 'prepare-execute', так как эта функция работает быстрее.

7) Некоторые способы хранения резервных копий таблиц или данных.

a) Резервное копирование данных можно производить с помощью cgi-скриптов, написанных на shell. Администратор с определенной периодичностью запускает данный скрипт, данные сохраняются в файле. Если файл уже существет, то при запуске скрипта информация в нем обновляется. Скрипт можно запускать cron'ом, тогда команды echo нужно убрать.
 

#!/bin/sh

DB=test
USER=user
PASSWORD=pass
HOST=host.ru

export DB USER PASSWORD HOST

echo "Content-type: text/plain"
echo ""
cd /usr/local/mysql/bin

./mysqldump -h$HOST -u$USER -p$PASSWORD -t $DB >db

echo "OK"

Файл (db) представляет из себя интструкции INSERT ... . Ключ -t указывает не копировать информацию о таблицах - CREATE TABLE ... Другой скрипт запускает SQL-команды из файла.
 

#!/bin/sh

DB=test
USER=user
PASSWORD=pass
HOST=host.ru

export DB USER PASSWORD HOST
cd /usr/local/mysql/bin

./mysql -D $DB -h$HOST -u$USER -p$PASSWORD <db

echo "Content-type: text/html"
echo ""
echo "OK"

Для повышения надежности этот файл можно скачивать по ftp. Одним из удобств данного метода является возможность портирования данных в любую СУБД.

б) Начиная с версии 3.23.25 появилась возможность резервного копирования таблиц (без индексов).
BACKUP TABLE [имя] TO '/dir';
И восстановления
RESTORE TABLE [имя] FROM '/dir';
 

в) Еще одой возможностью является, приведенный выше способ записи результата запроса в файл.
SELECT ... INTO OUTFILE '/dir/file';
И последующего восстановления из файла.
LOAD DATA ... .

8) Приятно удивила возможность полнотекстового идексирования полей VARCHAR и TEXT(начиная с версии 3.23.23). В запросе указываетя подстрока, и в результате выводятся те значения полей где она(подстрока) найдена. Например вы храните в БД статьи или анонсы статей и хотите предоставить поиск по статьям пользователю. Можно конечно установить поисковик(пример Yandex.Site), но для этого необходимо заводить отдельную БД, да и если требования к поисковой системе минимальны, данная установка просто излишество.

MySQL предоставляет возможность полнотекстового поиска. Проиллюстрирую на примере:
CREATE TABLE data (did int(7) AUTO_INCREMENT, a text, FULLTEXT (a),PRIMARY KEY (did));
INSERT INTO data (a) VALUES ('test message'), ('test mysql fulltext'), ('test test test');
SELECT * FROM data WHERE MATCH (a) AGAINST ('mysql');
В результате выведется строка = 'test mysql fulltext'. А по запросу
SELECT data,MATCH (a) AGAINST ('mysql') as m FROM data;
поле 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 возложены совершенно иные задачи, с которыми, на мой взгляд, она прекрасно справляется.



 

Основные термины

атрибут(поле) - определенная атомарная часть информации об объекте.

первичный ключ - атрибут или совокупность атрибутов однозначно идентифицирующих объект.

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