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

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

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

Цель настоящей работы – разработка методики выбора типа таблиц СУБД 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 и In-noDB. Первый тип больше подходит для использования в web-программировании и других средах, где преобладают запросы на чтение. Таблицы MyISAM являются платформенно независимыми, что позволяет перемещать таблицы между компьютерами разных архитектур и разными операционными системами без всякого преобразования. Таблицы типа InnoDB, в свою очередь, предназначаются для получения максимальной производительности при обработке больших объемов данных. Основное отличие InnoDB от других подсистем - это наличие обработчика таблиц, обеспечивающего безопасные транзакции с возможностями фиксации транзакции, отката и восстановления после сбоя.

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

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

Таблица 1

Характеристика	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

$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() -2678400, 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.

id	tex	ses		idO	id	status	time
1	afbeced	1524616324		1	1	fefdccfedfab	2011-04-12 10:23 11
2	aebebfe	6121526341		2	1	ffffcafabeec	2011-04-02 00:22:51
3	bfdafce	1131322124		3	1	eefafaaaeeee	2011-04-18 21:38:38
4	eaceaef	3316333464		4	1	caccfedacfbd	2011-03-22 04:40:44
5	cabfbcc	5466456164		5	1	cadcadafdfcf	2011-04-15 04:24 46
				6	2	cbbffcbecdbe	2011-03-24 18:45:17

2994	feeabce	4515143662
2995	caddbaf	2551151443		14995	2999	fcdeecbeadec	2011-04-19 14:43:08
2996	bdbfcfb	6434625321		14996	3000	bceeaabcbeff	2011-04-14 12:46:11
2997	aeaceda	5543213234		14997	3000	cafeacedbcaf	2011-03-20 02:12:23
2998	dcccaac	1661533315		14998	3000	adaafbcdacce	2011-04-14 02:11 21
2999	edffcbc	5531663523		14999	3000	ebeedabbccdb	2011-04-04 01:10:29
3000	cfddcec	5664656616		15000	3000	cbaeddcaebdd	2011-04-09 10:58:47
	tabi					tab2

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

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

Листинг 2

$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);

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

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

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

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

?>

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

ID: 2609 NAME №2158 NAME №2166 NAME ID:473 NAME:, nt 432 NAME:, ID: 934 NAME: ID: 265 NAME: ID: 1880 NAME ID: 655 NAME: ID: 1273 NAME ID: 1627 NAME ID: 88 NAME: d ID: 2142 NAME ID: 356 NAME: ID: 2772 NAME ID: 1115 NAME ID: 1154 NAME ID: 2939 NAME

baafEacPASS: 1111526452 fEbfdfePASS: 1112256236 deddbaaPASS: 1112311423 idafcdc PASS: 1113412431 idedaea PASS: 1113422565 iaccedc PASS: 1114211325 ffcdade PASS: 1114222154 affibfc PASS: 1114251522 leeadea PASS: 1114514512 dfecbbc PASS: 1114666663 bcebbec PASS: 1115213552 z&bc PASS: 6663656644 dfdbcbaPASS: 6664535462 :eccdbfPASS: 6664545214 cefibfd PASS: 6665153132 baaffcaPASS: 6665535151 dabdcbd PASS: 6666356635 aadffbfPASS: 6666516242

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

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

Листинг 3

$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.

idO	id	status	time	idO	id	status	time
1	1	fefdccfedfab	2011-04-12 10:23:11	1	1	fefdccfedfab	2011-04-12 10:23:11
2	1	ftffcafabeec	2011-04-02 00:22:51	2	1	ftffcafabeec	2011-04-02 00:22:51
3	1	eefafaaaeeee	2011-04-18 21:38:38	3	1	eefafaaaeeee	2011-04-18 21:38:38
4	1	caccfedacfbd	2011-03-22 04 40:44	4	1	caccfedacfbd	2011-03-22 04:40:44
5	1	cadcadafdfcf	2011-04-15 04:24:46	5	1	cadcadafdfcf	2011-04-15 04 24 46
6	2	cbbffcbecdbe	2011-03-24 18:45:17	6	2	cbbffcbecdbe	2011-03-24 18:45.27
7	2	fbafbaeedebb	2011-04-19 01:54:44	7	2	fbafbaeedebb	2011-04-19 01:54:54
8	2	bdcfceffaacf	2011-03-28 01:27 05	8	2	bdcfceffaacf	2011-03-28 01:27:15
9	2	ecaadaedbcdf	2011-04-08 16:06:40	9	2	ecaadaedbcdf	2011-04-08 16:06:50
10	2	cbdffdfeebfc	2011-03-29 21:21:47	10	2	cbdffdfeebfc	2011-03-29 21:21:47
11	3	beecdbeadbec	2011-04-07 13:43:40	11	3	beecdbeadbec	2011-04-07 13:43:40
12	3	cbddefffbefe	2011-03-31 03:50:00	12	3	cbddefffbefe	2011-03-31 03:50:00

До запуска скрипта                                      После запуска скрипта

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

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

Листинг 4

$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).

0,003 Y

0,0025     ---

0,002- — I---

0,0015" —I--

0,001-^1 —

0,0005---

COUNT(*J

0,008

0,007

0,006

0,005

0,004

0,003

0,002

0,001

UPDATE

□ MylSAM

□ InnoDB

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

Заключение

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

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

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

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

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

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