Анализ и выбор типа таблиц MYSQL для построения автоматизированных систем обработки и хранения информации

УДК 20.23.17

студент группы ИС-07

Научный руководитель:

доц., к. т.н.

Московский государственный горный университет

АНАЛИЗ И ВЫБОР ТИПА ТАБЛИЦ MYSQL ДЛЯ ПОСТРОЕНИЯ АВТОМАТИЗИРОВАННЫХ СИСТЕМ ОБРАБОТКИ И ХРАНЕНИЯ ИНФОРМАЦИИ

MySQL TABLES ANALYSIS AND TYPE SELECTION FOR STORAGE&PROCESSING INFORMATION

SYSTEMS DEVELOPMENT

Введение

Потоки информации, циркулирующие в окружающем нас мире огромны. С каждым днем информации накапливается все больше. Хранение таких объемов информации в бумажном виде является неудобным. Именно поэтому предпочтительным является компьютеризированный способ хранения, а именно использование баз данных. Базы данных (в дальнейшем БД) позволяют эффективно хранить большие объемы данных и извлекать их удобным для пользователя образом.

Так как БД представляют собой только данные, то для работы с ними необходимо специальное программное обеспечение – системы управления базами данных (СУБД). СУБД обеспечивает поддержку создания баз данных, централизованного управления и организации доступа к ним различных пользователей.

Цель настоящей работы – разработка методики выбора типа таблиц СУБД MySQL.

Для достижения поставленной цели необходимо решить следующие задачи:

1. Создать программное обеспечение для получения исходных данных для анализа.

2. Проанализировать полученные данные и обосновать выброр типа таблиц СУБД MySQL.

3. Разработать рекомендаций по выбору типа таблиц СУБД MySQL.

Виды запросов в СУБД MySQL

На данный момент на рынке имеется большое число СУБД: Microsoft SQL Server, Oracle Database, InterBase и др. Одной из наиболее распространенных СУБД на данный момент является MySQL. Основным плюсом данной СУБД является ее доступность и бесплатное распространение. MySQL - это система управления реляционными базами данных. В реляционной базе данных данные хранятся не все скопом, а в отдельных таблицах, благодаря чему достигается выигрыш в скорости и гибкости. Таблицы связываются между собой при помощи отношений, благодаря чему обеспечивается возможность объединять при выполнении запроса данные из нескольких таблиц [2].

Предварительный анализ программного обеспечения (ПО) автоматизированных систем хранения и обработки информации показал, что в таком ПО чаще всего используются следующие основные виды запросов:

· INSERT – вставляет новые строки в существующую таблицу;

· SELECT – применяется для извлечения строк, выбранных из одной или нескольких таблиц;

· UPDATE – обновляет столбцы в соответствии с их новыми значениями в строках существующей таблицы;

· COUNT() – это агрегатная функция, которая возвращает количество строк, которое удовлетворяет условиям поиска запроса [1].

Типы таблиц СУБД MYSQL

Типы таблиц СУБД MySQL делятся на два различных типа: транзакционные (InnoDB, BDB) и без поддержки транзакций (MyISAM, MERGE, ISAM).

Транзакция – это последовательность команд, которая должна быть выполнена целиком либо, в случае возникновения ошибки, не выполняться вовсе.

Преимущества транзакционных таблиц (Transaction-safe tables, TST):

· Надежность. Даже если произойдет сбой в работе MySQL или возникнут проблемы с оборудованием, свои данные вы сможете восстановить

· Есть возможность отменить внесенные изменения (если работа не производится в режиме автоматической фиксации).

· Если произойдет сбой во время обновления, все изменения будут восстановлены (в не транзакционных таблицах все внесенные изменения не могут быть отменены).

Преимущества не транзакционных таблиц (non-transaction-safe tables, NTST):

· Намного быстрее работают, так как не выполняются дополнительные транзакции.

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

· Для обновлений используется меньше памяти.

Каждый тип таблиц обладает рядом особенностей. Рассмотрим эти особенности.

InnoDB:

· все таблицы хранятся в едином табличном пространстве, поэтому имена таблиц должны быть уникальны;

· хранение данных в едином табличном пространстве позволяет снять ограничение на объём таблиц;

· таблицы поддерживают автоматическое восстановление после сбоя;
имеется расширенная поддержка кодировок.

BDB (BerkeleyDB):

· для таблиц ведётся журнал, что позволяет повысить устойчивость базы и увеличить вероятность успешного восстановления в случае сбоя;

· таблицы типа BDB хранятся в виде бинарных деревьев. Это замедляет сканирование таблицы (допустим для выборки всех строк таблицы) и увеличивает занимаемое таблицей место на диске. Однако поиск отдельных значений в таблице становится быстрее;

· все таблицы BDB имеют первичный ключ, если он не был задан при создании, то создаётся скрытый первичный ключ с атрибутом AUTO_INCREMENT;

· файлы таблиц BDB нельзя переносить между системами простым копированием, поскольку при их создании путь к файлу таблицы сохраняется;

· для переноса базы необходимо использовать утилиту mysqldump.

НЕ нашли? Не то? Что вы ищете?

MyISAM:

· данные хранятся в кроссплатформенном формате, что позволяет переносить базы с сервера непосредственным копированием файлов, минуя промежуточные формы;

· максимальное количество индексов — 64 (с версии 4.1.2). Каждый индекс может быть максимум из 16 столбцов;

· Поддерживается полнотекстовый поиск;

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

MERGE:

· этот тип позволяет объединять несколько таблиц типа MyISAM в одну;

· применяется для снятия ограничения на объём для таблиц MyISAM, в том случае если операционная или файловая система не позволяют создавать файлы более определённого размера (например: 4gb для FAT32 и т. п.).

MEMORY (HEAP):

· этот тип таблиц MEMORY (HEAP) хранится в оперативной памяти, из-за чего все запросы к таким таблицам выполняются очень быстро;

· недостаток у таких таблиц один — полная потеря данных в случае сбоя работы сервера. В связи с этим в таких таблицах хранят в основном временные данные, которые можно легко восстановить заново [3].

Типы таблиц MYISAM и INNODB

Основными типами таблиц в СУБД MySQL являются MyISAM и InnoDB. Первый тип больше подходит для использования в web-программировании и других средах, где преобладают запросы на чтение. Таблицы MyISAM являются платформенно независимыми, что позволяет перемещать таблицы между компьютерами разных архитектур и разными операционными системами без всякого преобразования. Таблицы типа InnoDB, в свою очередь, предназначаются для получения максимальной производительности при обработке больших объемов данных. Основное отличие InnoDB от других подсистем - это наличие обработчика таблиц, обеспечивающего безопасные транзакции с возможностями фиксации транзакции, отката и восстановления после сбоя.

Основные отличия этих двух типов таблиц приведены в табл. 1.

Таблица 1

Сравнение таблиц типов MyISAM и InnoDB

Характеристика	MyISAM	InnoDB
Производительность	Быстрое выполнение большого количества запросов SELECT, INSERT, но при небольшом количестве запросов DELETE или UPDATE.	Медленная запись, более медленная обработка BLOB, проблемы при работе с очень большим количеством таблиц, медленная загрузка данных и ALTER TABLE
Хранение данных	Для каждой таблицы создается один файл данных.	Данные в настройках по умолчанию хранятся в больших совместно используемых файлах
Надежность и восстановление	Тaблицa может отказаться работать без явной на тo причины. Необходимо выполнение REPAIR TABLE. Отсутствие возможности самoвосстановления по журналу при сбоях.	Обеспечивается надежное хранение данных за счет транзакционности и блокирование данных на уровне СТРОКИ. InnoDB обеспечивает также и быстрое самовосстановление после сбоев.
Транзакции и внешние ключи	Не поддерживаются	Поддерживаются
Полнотекстовый поиск	Поддерживается	Не поддерживается
Объем таблиц	Таблицы более компактные	Без сжатия наблюдается увеличение объема таблиц

Методика сравнения

Для сравнения типов таблиц удобно воспользоваться менеджером для работы с базами данных MySQL – PHPMyAdmin. Он предоставляет удобный и интуитивно понятный интерфейс для создания структуры таблиц [1].

Создадим две таблицы tab1 и tab2. Первая таблица содержит уникальный номер пользователя, его имя и пароль. Вторая таблица для каждого пользователя будет хранить по несколько, созданных пользователем, статусов и время их создания. Структура таблиц приведена ниже (см. табл. 2, 3).

Таблица 2

Структура tab1

tab1
Поле	Тип	Дополнительно
id	int(4)	AUTO_INCREMENT
tex	varchar(100)
ses	varchar(100)

Таблица 3

Структура tab2

tab2
Поле	Тип	Дополнительно
id0	int(4)	AUTO_INCREMENT
id	int(4)
status	varchar(100)
time	datetime

Заполнять таблицы удобно автоматически. Для этого напишем программу (скрипт) на языке программирования PHP, который будет заполнять таблицы случайными текстовыми и числовыми значениями (см. листинг 1).

Листинг 1

<?php

$DBHost="localhost";

$DBUser="root";

$DBPassword="";

$DBName="icq_my";

$Link=mysql_connect($DBHost, $DBUser, $DBPassword); //подключаемся к серверу

mysql_select_db($DBName,$Link); //выбираем нашу базу

//засекаем время начала работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tstart = $mtime;

//цикл отвечает за количество строк

for ($a=1; $a<=3000; $a++) {

$text="";

$alph=range('a','z');

$length =strlen(strval($alph));

for ($b=0; $b<7; $b++)

$text.=$alph[mt_rand(0, $length)];

$num=""; //цифры

$alph=range(1,9);

$length =strlen(strval($alph));

for ($b=0; $b<10; $b++)

$num.=$alph[mt_rand(0, $length)];

//добавляем записи в первую таблицу базы

$Query1="INSERT INTO tab1 VALUES(0, '".$text."', '".$num."' )";

mysql_query($Query1,$Link);

$id=mysql_insert_id();

// Добавляем генерацию статусов

for ($j = 0; $j < 5; $j++)

{ // Генерируем статус

$stat="";

$alph= range('a', 'z');

$length =strlen(strval($alph));

for ($b=0; $b<12; $b++)

$stat.=$alph[mt_rand(0, $length)];

$t=rand(time() , time()); // генерируем время

//добавляем данные во вторую таблицу

$Query2="INSERT INTO tab2 VALUES(0,'".$id."','".$stat."', FROM_UNIXTIME($t) )";

mysql_query($Query2,$Link);

}

//засекаем время окончания работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tend = $mtime;

$tpassed = ($tend - $tstart);

echo $tpassed." ";

echo "Таблицы заполнены."." ";

mysql_close($Link); // закрываем соединение

В вышеприведенном скрипте мы используем два запроса INSERT, а также используем функцию mysql_insert_id(). Она возвращает идентификатор записи для автоинкрементного поля.

Результат работы скрипта приведен на рис. 1.

Рис. 1. Результат работы скрипта с запросом INSERT.

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

Листинг 2

<?php

$DBHost="localhost";

$DBUser="root";

$DBPassword="";

$DBName="icq_my";

$Link=mysql_connect($DBHost, $DBUser, $DBPassword); //подключаемся к серверу

mysql_select_db($DBName,$Link); //выбираем нашу базу

//засекаем время начала работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tstart = $mtime;

$Query="SELECT*FROM tab1 WHERE 1 ORDER BY ses";//делаем запрос на выборку всех данных

$Rez=mysql_query($Query,$Link);//запрашиваем

//засекаем время окончания работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tend = $mtime;

//вычисляем время работы

$tpassed = ($tend - $tstart);

echo $tpassed." ";

$array = mysql_fetch_array($Rez);

//выводим данные на экран браузера

{

echo "ID: ".$array['id']." NAME: ".$array['tex']." PASS: ".$array['ses']." ";

}

while($array = mysql_fetch_array($Rez));

mysql_close($Link); // закрываем соединение

Результат работы вышеприведенного скрипта представлен на рис. 2.

Рис. 2 – Результат работы скрипта с запросом SELECT

Для работы с запросом UPDATE напишем скрип, который обновляет время во второй таблице в диапазоне указанный значений (см. листинг 3).

Листинг 3

<?php

$DBHost="localhost";

$DBUser="root";

$DBPassword="";

$DBName="icq_my";

$Link=mysql_connect($DBHost, $DBUser, $DBPassword); //подключаемся к серверу

mysql_select_db($DBName,$Link); //выбираем нашу базу

//засекаем время начала работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tstart = $mtime;

$Query="UPDATE tab2 SET time=FROM_UNIXTIME(UNIX_TIMESTAMP(time)+10) WHERE id0>5 AND id0<10";

mysql_query($Query,$Link);

//засекаем время окончания работы запросов

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tend = $mtime;

//вычисляем время работы

$tpassed = ($tend - $tstart);

echo $tpassed." ";

mysql_close($Link); // закрываем соединение

Результат работы скрипта представлен на рис. 3.

Рис. 3 – Результат работы скрипта с запросом UPDATE

Напишем скрипт для получения номера последней записи в таблицах (см. листинг 4).

Листинг 4

<?php

$DBHost="localhost";

$DBUser="root";

$DBPassword="";

$DBName="icq_my";

$Link=mysql_connect($DBHost, $DBUser, $DBPassword); //подключаемся к серверу

mysql_select_db($DBName,$Link); //выбираем нашу базу

//засекаем временные параметры для первой таблицы

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tstart = $mtime;

//echo $tstart." ";

$Query1="SELECT COUNT(*) FROM tab1";//делаем запрос на выборку всех данных

$Rez1=mysql_query($Query1,$Link);//запрашиваем

$array1 = mysql_fetch_array($Rez1);

echo "Последняя запись в таблице 1 - номер ".$array1[0]." ";

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tend = $mtime;

$tpassed = ($tend - $tstart);

echo $tpassed." ";

//засекаем временные параметры для второй таблицы

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tstart = $mtime;

//echo $tstart." ";

$Query2="SELECT COUNT(*) FROM tab2";//делаем запрос на выборку всех данных

$Rez2=mysql_query($Query2,$Link);//запрашиваем

$array2 = mysql_fetch_array($Rez2);

echo "Последняя запись в таблице 2 - номер ".$array2[0]." ";

$mtime = microtime();

$mtime = explode(" ",$mtime);

$mtime = $mtime[1] + $mtime[0];

$tend = $mtime;

$tpassed = ($tend - $tstart);

echo $tpassed." ";

mysql_close($Link); // закрываем соединение

Результат работы скрипта приведен на рис. 4.

Рис. 4. Результат работы скрипта с использованием COUNT().

Теперь сведем полученные значения времен в таблицу (см. табл. 4):

Таблица 4

Сводная таблица данных, полученных в результате работы

	MyISAM	InnoDB
Запрос	Tab1	Tab2	Tab1	Tab2
INSERT	6,458	18,52
SELECT	0,044	-	0.056	-
SELECT COUNT(*)	0,0005	0,0002	0,0026	0,0109
UPDATE	-	0,0018	-	0,0075
Размер	114,0 кБ	680,3 кБ	160 кБ	1,5 мБ

Для наглядного представления результатов проделанной работы удобно привести данные в виде гистограммы для каждого типа запросов

(см. рис. 5).

Рис. 5. Время работы запросов.

Заключение

В результате проделанной работы можно определить следующие рекомендации по выбору типа таблиц СУБД MySQL при построении автоматизированных систем обработки и хранения информации:

· если требуется высокая надежность таблиц, то предпочтительнее выбирать тип таблиц - InnoDB;

· если требования надежности не высокие и дано ограниченное место на диске, то следует выбирать MyISAM;

· также MyISAM следует выбирать при большом количестве запросов на добавление и вывод данных;

· если общее число запросов на чтение менее 1000, а запросов на добавление меньше 500, то с данной задачей лучше справятся таблицы типа InnoDB.

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

Литература

1. РНР 5. – М.: НТ Пресс, 2007. – 256с.

2. , Джон РНР и MySQL. – СПб.: Символ-плюс, 2008. – 448с.

3. Справочное руководство по MySQL версии 5.0.0-alpha. – [Электронный источник] – режим доступа: http:// *****/db/mysql5 (проверено – 17.04.2011).

Аннотация

Данная статья предоставляет обзор методов анализа и выбора типа таблиц MySQL для построения автоматизированных систем обработки и хранения информации. Статья будет полезна при разработке серверных приложений на языке php.

This article describes basic principles of MySQL tables type selection for automation processing&storage systems development. The article can be useful for PHP server applications developers.

Ключевые слова

СУБД, база данных, типы таблиц, MySQL, php, транзакция, система обработки и хранения информации

DBMS, database, table types, MySQL, php, transaction, processing system and information storage

Анализ и выбор типа таблиц MYSQL для построения автоматизированных систем обработки и хранения информации

Домашний очаг

Справочная информация

Техника

Общество

Образование и наука

Мир

Бизнес и финансы