Оптимизация запросов

В ситуациях, когда реально требуется получить только определенную порцию данных из MySQL, можно использовать ключ LIMIT для функции SELECT. Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю - каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователей провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида

select good, price from books limit 20,10

В результате, MySQL “отдаст” Вам 10 записей из базы начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки “Следующие 10 товаров”, в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к MySQL.

Также следует помнить, что при составлении запросов к базе данных (SQL queries) следует запрашивать только ту информацию, которая Вам реально нужна. Например, если в базе 10 полей, а в данный момент реально требуется получить только два из них, вместо запроса

select * from table_name

используйте конструкцию вида

select field1, field2 from table_name

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

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

select title from books where author=’Иванов’

Также есть ключ LIKE, который позволяет искать поля, значения которых “похожи” на заданный шаблон:

select title from books where author like ‘Иванов%’

В данном случае MySQL выдаст названия книг, значения поля author у которых начинаются с ‘Иванов’.

Март 30, 2008 — Рубрика: MySQL
Метки: MySQL, Оптимизация запросов, поля

Какие данные нужно хранить в MySQL

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

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

Март 28, 2008 — Рубрика: MySQL
Метки: http-заголовки, MySQL, данные

Введение

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

Такой подход, конечно, дает желаемый результат - интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с базой данных. Однако не всегда пользователи задумываются о том, насколько оптимально работает их база, как можно оптимизировать происходящие при работе с MySQL процессы и каково будет функционирование виртуального сервера при увеличившейся нагрузке, “наплывах” пользователей в результате, например, “раскрутки” сайта.

Эта статья поможет Вам оптимизировать работу с СУБД MySQL. Изложенный материал не претендует на детальное описание оптимизации MySQL вообще, а лишь обращает внимание на наиболее часто совершаемые пользователями ошибки и рассказывает о том, как их избежать. Более подробно узнать о тонкостях настройки MySQL можно на специализированных страницах, ссылки на которые приведены в конце этой статьи.

Март 27, 2008 — Рубрика: 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
Для повышения надежности этот файл можно скачивать по 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 возложены совершенно иные задачи, с которыми, на мой взгляд, она прекрасно справляется.

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

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

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

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

Март 25, 2008 — Рубрика: MySQL
Метки: MySQL, оптимизация

Как установить репликацию

Удостоверитесь, что Вы установили на обоих серверах свежую версию mysql,. Необходима версия 3.23.29 или выше. Предыдущие версии не будут корректно работать. Создайте специального пользователя репликации на основном сервере с привилегией FILE и разрешением коннекта от всех резервных серверов. Если пользователь только делает репликацию - не давайте ему никакие дополнительные привилегии. Например, пользователь repl (с любых серверов) GRANT FILE ON *.* TO repl@”%” IDENTIFIED BY ‘‘;

Остановите основной сервер MySQL. mysqladmin -u root -p shutdown

Сделайте копию всей базы данных Вашего сервера К примеру, на UNIX tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir Распакуйте на резервных серверах (в соответствующие каталоги)

На основном сервере в файле my.cnf, установите параметры
[mysqld]
log-bin
server-id=1

server-id - должен отличатся от server_id резервных серверов, возможно - использовать что-то похожее на IP-адрес без точек.

Перезапустите mysql на основном сервере.

На резервных серверах в файле my.cnf, установите параметры
master-host=
master-user=
master-password=
master-port=
server-id=

Установите необходимые значения и перезапустите резервные сервера (server_id - должны быть уникальны)

Если Вы забыли устанавливать server_id для резервного сервера, в логах будет ошибка: Warning: one should set server_id to a non-0 value if master_host is set. The server will not act as a slave. Если Вы не установите на основном, резервные сервера не смогут соединиться с основным. Проверьте лог ошибок на резервных серверах, при возникновении проблем.

После успешного соединения создается файл master.info в том же каталоге что и лог ошибок. Не трогайте этот служебный файл - он используется, чтоб отслеживать обработку полного журнала изменений данных (Binary Update Log) от основного сервера. Для того чтоб что-то изменить используйте команду CHANGE MASTER TO

Примечания: Версия 3.23.42-1, при использовании репликации определенной базы данных приведенная выше команда grant не работает, необходимо вручную подправить File_priv column в таблице user.

Март 19, 2008 — Рубрика: MySQL
Метки: IP-адрес, server-id, Репликация

Полный журнал изменений данных (Binary Update Log)

В будущем полный журнал изменений данных(binary update log) заменит журнал обновлений (update log), так что мы рекомендуем Вам перейти к использованию этого журнала как можно скорее! Полный журнал изменений данных(binary update log) содержит всю информацию, которую содержит журнал обновлений (update log), но в более эффективном формате. Он содержит также информацию о том, как долго выполнялся каждый запрос, который модифицировал базу данных.

Вы можете использовать опции binlog-do-db=database_name Будет обрабатываться только указанная база данных binlog-ignore-db=database_name Будут игнорироваться указанные базы данных

При использовании команд mysqladmin refresh mysqladmin flush-logs SQL оператор “FLUSH LOGS” или перезапуска сервера к расширению имени файла журнала будет добавляться инкрементальный номер.

Чтоб учитывать какие файлы журналов использовались Mysqld создает индексный файл, у которого имя совпадает с полным журналом изменений данных(binary update log), и имеет расширение ‘.index’. Вы можете поменять имя этого файла, указав опцию –log-bin-index=[filename].

Если Вы используете репликацию, Вы не должны удалить старые полные журналы изменений данных(binary update log), пока Вы не уверены, что никакой резервный сервер не будет использовать их. Один из вариантов делать mysqladmin flush-logs один раз в день и затем удалять любые файлы регистрации, у которых дата старше, чем 3 дня. Вы можете просмотреть полный журнал изменений данных(binary update log), командой mysqlbinlog. mysqlbinlog log-file | mysql -h server_name Дополнительные ключи Вы можете узнать, запустив команду mysqlbinlog –help

Если Вы используете BEGIN [WORK] или SET AUTOCOMMIT=0, Вы должны использовать полный журнал изменений данных(binary update log) вместо журнала обновлений (update log). Запись журнала происходит сразу, после выполнения запроса, перед выполнением возможных блокировок. Это гарантирует достоверную запись журнала Все обновления (UPDATE, DELETE or INSERT) кэшируются до операции COMMIT, если вы используете транзакции (таблицы BDB). Если Вы не используете транзакции - изменения сохраняются немедленно. Каждый поток при старте заполняет буфер размером (binlog_cache_size) для записи запросов. Если запрос больше чем размер этого буфера, используется временный файл, который будет удален при закрытии потока. Чтоб ограничить максимальный размер буфера, используйте опцию max_binlog_cache_size гарантировать, что Вы можете освежать точную копию ваших таблиц, применяя файл регистрации на резервировании (копии).

ВАЖНО! Полный журнал изменений данных (Binary Update Log) начинается с определенного момента времени. Ваши резервные сервера должны иметь точные копии данных на момент запуска журнала. В будущих версиях MySQL 4.0 это будет устранено, но на текущий момент Вам необходимо блокировать сервер для записи, и поставит блокировку на чтение, когда вы будете делать слепок текущие базы данных

При правильной конфигурации резервный сервер соединяется с основным и ждет данных по модификации. Если соединение прерывается, то резервный сервер пытается соединиться снова, каждые master-connect-retry сек. Резервный сервер отмечает момент отключения. Основной сервер не определяет, сколько подключено к нему резервных серверов.

Март 16, 2008 — Рубрика: MySQL
Метки: MySQL 4.0, журнал

Репликация MySQL

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

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

Начинаясь с версии 3.23.15, MySQL поддерживает встроенную одностороннюю репликацию. Один сервер действует как главный(master), в то время как другой как резервный. Обратите внимание, что один сервер может быть, как и главным, так и резервным (в разных цепочках). Главный сервер сохраняет полный журнал изменений данных (binary log of updates) см. рук 4.9.4 и индексный файл для ротации логов.

Резервный сервер при соединении с главным, сообщает с какого этапа начать обновление и ждет ответа от главного насчет новых обновлений.

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

Март 13, 2008 — Рубрика: MySQL
Метки: MySQL, Репликация

Деструкторы

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

PHP5 поддерживает деструкторы, аналогично другим объектно-ориентированным языкам, таким как Java. Когда последняя ссылка на объект уничтожена, прежде, чем память, выделенная объекту, уничтожается, вызывается метод класса с именем __destruct() без каких-либо параметров.

Пример:

Единое имя конструкторов

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

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

В PHP5 введен стандартный способ объявления конструктора класса. Конструктором в PHP5 является метод с именем __construct().

Пример:
class BaseClass {
function __construct() {
print "В конструкторе базового класса BaseClass\n";
}
}

class SubClass extends BaseClass {
function __construct() {
parent::__construct();
print "В конструкторе производного класса SubClass\n";
}
}

$obj = new BaseClass();
$obj = new SubClass();
?>

Для обратной совместимости, предусмотрено следующее: если PHP5 не находит функцию __construct() в определении данного класса, используется старый стиль именования конструкторов. Таким образом, единственный случай несовместимости при переходе на PHP5 - присутствие в объявлении класса метода с именем __construct().

Март 9, 2008 — Рубрика: PHP
Метки: PHP4, PHP5

Клонирование объектов

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

Данное поведение PHP4 не всегда позволяло достичь нужного результата. Хорошим примером необходимости конструктора копии является объект, представляющий GTK-окно и содержащий его ресурсы: при создании копии такого объекта, вам, скорее всего, хотелось бы создать новое окно с теми же свойствами, и получить объект, содержащий ресурсы нового окна. Еще один пример: объект хранит ссылку на другой используемый им объект, и при создании копии хотелось бы получить ссылку на новый экземпляр объекта, а не на тот же.

Для создания копии объекта, вызывается его метод __clone():
$copy_of_object = $object->__clone();
?>

При выполнении данной операции, PHP5 сначала проверит, определен ли метод __clone(). Если он не определен, PHP вызовет метод __clone(), созданный под умолчанию, который осуществит побайтное копирование всех свойств объекта. В случае же, если метод __clone() определен, он отвечает за установку требуемых свойств создаваемой копии объекта. Для удобства, Zend Engine предоставляет функцию, импортирующую все свойства копируемого объекта, так что достаточно переопределить только те свойства, которые необходимо изменить.

Пример:
class MyCloneable {
static $id = 0;

function MyCloneable() {
$this->id = self::$id++;
}

function __clone() {
$this->name = $that->name;
$this->address = “New York”;
$this->id = self::$id++;
}
}

$obj = new MyCloneable();

$obj->name = “Hello”;
$obj->address = “Tel-Aviv”;

print $obj->id . “\n”;

$obj = $obj->__clone();

print $obj->id . “\n”;
print $obj->name . “\n”;
print $obj->address . “\n”;
?>

Март 7, 2008 — Рубрика: PHP
Метки: PHP, Клонирование объектов