В ситуациях, когда реально требуется получить только определенную порцию данных из 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 это и позволяет. Помещая в базу данных двоичные образы графических файлов, Вы только замедлите работу своего сервера. Прочитать файл с картинкой с диска гораздо проще и, с точки зрения потребляемых ресурсов, экономичнее, нежели соединиться из скрипта к SQL, сделать запрос, получить образ, обработать его и, выдав нужные http-заголовки, показать посетителю веб-сервера. Во втором случае операция выдачи картинки потребует в несколько раз больше ресурсов процессора, памяти и диска. Также стоит помнить о том, что существуют механизмы кэширования веб-документов, которые позволяют пользователю экономить на трафике, а при динамической генерации контента Вы фактически лишаете своих посетителей этой удобной возможности.
Вместо картинок лучше хранить в MySQL информацию, на основе которой можно генерировать ссылки на статические картинки в динамически создаваемых скриптами документах.
Март
28,
2008
— Рубрика: MySQL
Метки: http-заголовки, MySQL, данные
В процессе предоставления услуг хостинга мы обращаем внимание на наиболее часто встречающиеся ошибки, которые совершают пользователи при разработке своих виртуальных серверов. Одним из “тяжелых” мест для типичного веб-мастера является работа с MySQL-сервером. Обычно изучение принципов функционирования SQL и методов работы с базами данных ведется по литературе, из которой выбираются только актуальные на момент чтения вещи - как соединиться с базой, как сделать запрос, как обновить информацию или добавить новую запись в базу данных и так далее.
Такой подход, конечно, дает желаемый результат - интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с базой данных. Однако не всегда пользователи задумываются о том, насколько оптимально работает их база, как можно оптимизировать происходящие при работе с MySQL процессы и каково будет функционирование виртуального сервера при увеличившейся нагрузке, “наплывах” пользователей в результате, например, “раскрутки” сайта.
Эта статья поможет Вам оптимизировать работу с СУБД MySQL. Изложенный материал не претендует на детальное описание оптимизации MySQL вообще, а лишь обращает внимание на наиболее часто совершаемые пользователями ошибки и рассказывает о том, как их избежать. Более подробно узнать о тонкостях настройки MySQL можно на специализированных страницах, ссылки на которые приведены в конце этой статьи.
Март
27,
2008
— Рубрика: 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, оптимизация
Репликация может использоваться, что увеличить ошибкоустойчивость и быстродействие. Для ошибкоустойчивости Вы можете иметь две системы и использовать резервный сервер, если Вы имеете проблемы с главным сервером.
Дополнительное быстродействие может быть достигнуто, посылая часть запросов на выборку (select) данных на резервный сервер (где храниться точная копия данных).
Начинаясь с версии 3.23.15, MySQL поддерживает встроенную одностороннюю репликацию. Один сервер действует как главный(master), в то время как другой как резервный. Обратите внимание, что один сервер может быть, как и главным, так и резервным (в разных цепочках). Главный сервер сохраняет полный журнал изменений данных (binary log of updates) см. рук 4.9.4 и индексный файл для ротации логов.
Резервный сервер при соединении с главным, сообщает с какого этапа начать обновление и ждет ответа от главного насчет новых обновлений.
Обратите внимание - если Вы копируете базу данных, все модификации к этой базе данных должны быть сделаны на главном сервере! Другая выгода использования репликации - можно получить живые копии из системы, делая копию на резервном сервере, вместо главного.
Март
13,
2008
— Рубрика: MySQL
Метки: MySQL, Репликация
Итак, вы много раз видели, как дынные выводятся постранично и вы всегда хотели сделать так на своем сайте. Как это сделать с файлом я уже описывал. Сегодня я расскажу как сделать постраничный вывод из MySQL.
Для начала немного теории. Что надо сделать? Сначала, надо проверить выбрана ли страница просмотра. Потом надо подсчитать откуда выводить записи. Делается это просто: текущую страницу умножаем на количество записей на страницу. Наконец, надо сделать SQL-запрос к базе данных и вывести результат. И в конце концов написать нивигацию.
Приступим. Выполните SQL-запрос:
CREATE TABLE tbl_books (
book_id int(11) NOT NULL auto_increment,
book_name varchar(50) NOT NULL default ”,
book_cash float NOT NULL default ‘0′,
PRIMARY KEY (book_id)
) TYPE=MyISAM;
INSERT INTO tbl_books VALUES (1, ‘Книга номер 1′, ‘2′);
INSERT INTO tbl_books VALUES (2, ‘Книга номер 2′, ‘3′);
INSERT INTO tbl_books VALUES (3, ‘Книга номер 3′, ‘4′);
INSERT INTO tbl_books VALUES (4, ‘Книга номер 4′, ‘5′);
INSERT INTO tbl_books VALUES (5, ‘Книга номер 5′, ‘6′);
INSERT INTO tbl_books VALUES (6, ‘Книга номер 6′, ‘7′);
INSERT INTO tbl_books VALUES (7, ‘Книга номер 7′, ‘8′);
INSERT INTO tbl_books VALUES (8, ‘Книга номер 8′, ‘9′);
INSERT INTO tbl_books VALUES (9, ‘Книга номер 9′, ‘10′);
INSERT INTO tbl_books VALUES (10, ‘Книга номер 10′, ‘11′);
INSERT INTO tbl_books VALUES (11, ‘Книга номер 11′, ‘12′);
INSERT INTO tbl_books VALUES (12, ‘Книга номер 12′, ‘13′);
INSERT INTO tbl_books VALUES (13, ‘Книга номер 13′, ‘14′);
INSERT INTO tbl_books VALUES (14, ‘Книга номер 14′, ‘15′);
INSERT INTO tbl_books VALUES (15, ‘Книга номер 15′, ‘16′);
Я предлагаю все оформить в виде функций обработчиков. Создадим функцию page(), которая обрабатывает данные о запрошенном номере страницы:
function page()
{
if(empty($_GET["page"])){
$page = 0;
} else {
if(!is_numeric($_GET["page"])) die("Неправильный формат номера страницы!");
$page = $_GET["page"];
}
return $page;
}
Что она делает? Проверяет выбрана ли страница и если в ней присутствуют буквы, то выводит сообщение об ошибке. Далее сделаем SQL-запрос:
function sql_query($onpage, $page, $table)
{
$begin = $page*$onpage; // откуда начинать
$sql = "SELECT * FROM ".$table." LIMIT ".$begin.", ".$onpage;
$result = mysql_query($sql) or die(mysql_error());
return $result;
}
Функция навигации:
function navigation($onpage, $page, $table)
{
$return = null;
$count = mysql_query("SELECT COUNT(*) FROM tbl_books") or die(mysql_error());
$count = mysql_fetch_array($count);
$count = $count[0];
$pages = $count/$onpage;
if($page!==0){
$prev = "<“;
} else {
$prev = “<";
}
if($page
$next = ">“;
} else {
$next = “>”;
}
for($i=0;$i<$pages;$i++)
{
if($i==$page){
$return.="[".($i+1)."]";
} else {
$return.="[".($i+1)."]“;
}
}
return $prev.$return.$next;
}
С функциями покончили! Как ими пользоваться?
$onpage = 4; // записей на страницу
$table = “tbl_books”; // из какой таблицы
mysql_connect(”localhost”, “root”, “pass”) or die(mysql_error()); // коннект к БД
mysql_select_db(”test”); // выбор БД
$page = page(); // определяем страницу
$result = sql_query($onpage, $page, $table); // sql - запрос
while($data = mysql_fetch_array($result)) // цикл вывода
{
// шаблон вывода нужно изменить
// $data["название_поля"] - содержание поля
echo $data["book_id"].” “.$data["book_name"].”
“;
echo $data["book_cash"].”$
“;
}
$navigation = navigation($onpage, $page, $table); // определим навигацию
echo $navigation; // выведем ее
mysql_close(); // отключение от БД
?>
Февраль
23,
2008
— Рубрика: MySQL
Метки: MySQL, БД, Постраничный вывод
