Сравнение производительности типов char и varchar

Курсовая работа

по дисциплине «Базы данных»

тема: «Сравнение производительности типов char и varchar»

1. Планирование эксперимента

2. Реализация эксперимента

2.1 Требования к программе

2.2 Настройка окружения и создание баз данных

2.3 Разработка приложения

2.4 Проведение экспериментов

3. Анализ результатов

Заключение

Приложение, А (обязательное) «Скрипты на создание таблиц»

Приложение Б (обязательное) «Результаты выполнения программы»

Язык SQL является стандартом в области языков реляционных баз данных. Сегодня существует уже три версии стандарта от 1992, 1999 и 2003 годов. В каждой версии стандарта основные строковые типы это char и varchar определённой длины. Изначально предполагалось, что тип char, занимая фиксированный размер, способствует ускорению выполнения запросов, т.к. алгоритму, реализующему запрос, не потребуется выполнять дополнительных проверок на длину строки. Тип varchar был введён для уменьшения необходимого места для хранения данных, т.к. если строки имеют различную длину, а для поля указана максимальная, то строки почти всегда занимают меньше места, чем им отведено. В случае с char, незаполненное место записывается пробелами. Для реализации физического хранения значений типа varchar нужно дополнительно к каждому значению хранить целочисленный идентификатор — длину конкретного значения, либо использовать некий символ конца строки. Обработка этих дополнительных данных и уменьшает производительность СУБД.

Попытаемся определить, действительно ли существует разница в скорости выполнения запросов, построенных на таблицах с использованием char и varchar. Заметим, что язык SQL предполагает всего лишь интерфейс взаимодействия прикладных программ с СУБД. Это, в частности, значит, что разделение строковых типов, проведённое на уровне языка, может отсутствовать на уровне СУБД: типы могут быть реализованы одинаково.

1. Планирование эксперимента

В первую очередь мы должны разделить эксперименты на проводимые с типом char и типом varchar. Во вторых, следует уделить внимание тому, где расположены атрибуты: в первичном ключе или нет. Все СУБД создают явные или неявные индексы на атрибуты первичного ключа. Интересно узнать, как индексы влияют на выполнение запросов с использованием атрибутов рассматриваемых типов.

В третьих, эксперименты можно разделить по видам запросов. Нас интересуют основные виды запросов, т. е. запросы на вставку данных (insert), обновление данных (update) и выборку данных (select). Запросы на выборку можно провести с использованием 2х операций сравнения, специфичных для строковых типов: операция сравнения на равенство (=) и сравнение на совпадение по маске (like).

Таким образом, получаем следующее разделение (рисунок 1):

Рисунок 1 — Разбиение экспериментов

В итоге получается 16 экспериментов. Рассмотрим подробнее каждый эксперимент.

Ввиду ограничений конкретных СУБД (Interbase не поддерживает суммарную длину строк в первичном ключе более 200), возьмём длину строк равной 50. Будем заполнять эти атрибуты строками длиной от 10 до 48 символов (два символа потребуются далее, при обновлении). Длину и содержание строк будем генерировать случайным образом.

Для того чтобы расходы процессорного времени на обработку каждой строки таблицы были больше (или сопоставимы), чем расходы на перемещение между строками, в каждом случае будем заводить не одно, а несколько (возьмём 3) атрибутов типов char или varchar. В случае с первичным ключом, первичный ключ будут образовывать 5 соответствующих атрибутов плюс один идентификатор типа int, заполняемый возрастающей последовательностью чисел для поддержания явной уникальности первичного ключа. Остальные атрибуты (возьмём также 5 штук) будут иметь тип int и будут заполняться случайным образом. В случае без первичного ключа, будут присутствовать те же 9 атрибутов (3+1+5), только в таблице будет отсутствовать определение первичного ключа.

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

В случае вставки будем записывать заранее сгенерированный набор данных в 4 таблицы. В случае обновления, будем использовать запрос на обновление всех 3 полей типов char или varchar без условия на выборку (where). При обновлении данных, будем дописывать к значениям атрибутов по 2 символа в начало строки.

Update table1 set

f1='12'+f1,

f2='12'+f2,

f3='12'+f3

Уникальность первичного ключа в обоих случаях обеспечит специально введённый атрибут типа int. В случае выборки будем выбирать 5 полей типов char или varchar с использованием в одном случае сравнения на равенство (=), а в другом — сравнения по маске (like). В качестве аргумента для сравнения выберем значение атрибутов первой строки таблицы (для выполнения запроса СУБД всё равно должна просмотреть всю таблицу).

Select f1, f2,f3

From table1

Where f1='xxx' and f2='xxx' and f3='xxx'

Select f1, f2,f3

From table1

Where f1 like 'xxx' and f2 like 'xxx' and f3 like 'xxx'

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

Засечь время можно с точностью до миллисекунд. Для обеспечения малой погрешности, выполнение самого быстрого запроса (предположительно на выборку на равенство) должно составить несколько секунд. Предварительно можно заключить, что необходим набор данных от 10 000 до 50 000 строк.

запрос база данных char varchar

2. Реализация эксперимента

2.1 Требования к программе

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

— Генерация данных для вставки в 4 таблицы, существующие в БД. В каждую таблицу должен быть вставлен один и тот же набор данных. Кроме того, генерация такого набора данных может занять некоторое время, которое не должно суммироваться с общим временем выполнения запроса. Таким образом, набор данных следует сгенерировать заранее, по определённым выше правилам. Имеет смысл генерировать запросы на вставку данных сразу в виде строки SQL кода, готовой к выполнению.

— Вставка данных с замером затраченного времени.

— Обновление данных с замером затраченного времени.

— Извлечение данных, с замером затраченного времени. Имеет смысл замерить только время получения отклика от запроса, т. е. нет необходимости просматривать возвращённые запросом результаты.

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

В качестве инструмента для создания программы выберем Delphi 7.

2.2 Настройка окружения и создание баз данных

В качестве конкретной СУБД, проведём эксперименты сначала на Interbase 6.5, входящую в дистрибутив Delphi, а затем на свободно распространяемой СУБД MySQL 5.037. Для доступа к соответствующим СУБД в Delphi используются различные компоненты: для доступа к Interbase используются свои компоненты, в то время как для доступа к MySQL требуются компоненты BDE или ADO для доступа к ODBC. Мы используем компоненты BDE и ODBC драйвер для MySQL версии 3.51. Для того чтобы его настроить требуется создать БД. Для этого используем оболочку MySQL: MySQL ControlCenter версии 0.94 (рисунок 2).

Рисунок 2 — Создание БД в MySQL ControlCenter

Мы создали БД и назвали её test1. Создадим в БД таблицы, представленные в приложении А. Далее нам требуется настроить ODBC — создать запись для доступа к нашей БД. Поскольку мы работаем под операционной системой Windows XP, открываем «Пуск — Настройки — Администрирование — ODBC». Нажимаем «Добавить», выбираем драйвер для MySQL и настраиваем его как показано на рисунке 3:

Рисунок 3 — Настройка ODBC драйвера MySQL

Теперь мы можем обращаться к нашей БД по введённому нами псевдониму mysqltest1.

Далее создадим БД в Interbase. Для этого открываем IBConsole (встроенную оболочку СУБД Interbase). Соединяемся с сервером и создаём новую БД с именем test1 (рисунок 4).

Рисунок 4 — Создание БД в IBConsole

Далее создаём в БД таблицы, по скрипту из приложения А.

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

2.3 Разработка приложения

Создадим в Delphi форму, разместим на ней 3 кнопки, компонент RichEdit для вывода результатов, и компоненты для доступа к базам данных (рисунок 5). Для доступа к Interbase используем компоненты (TIBDataBase, TIBQuery, TIBTransaction) с соответствующей вкладки, для доступа к MySQL используем компоненты (TdataBase, TQuery) с вкладки BDE. Для выполнения тестов нам достаточно одного запроса (TQuery, TIBQuery) на БД, т.к. текст запросов будет генерироваться динамически.

Рисунок 5 — Форма Delphi с размещёнными компонентами

Далее инициализируем компоненты для работы с базами данных. Свяжем запросы с соответствующими базами данных и настроем базы данных. Для Interbase укажем соответствующий файл БД, для MySQL, выберем созданный ранее ODBC алиас (псевдоним) (рисунок 6).

Рисунок 6 — Настройка компонента TDataBase

Для каждого компонента БД настроим параметры пользователя и пароля для автоматического соединения с БД. Явно укажем пользователя и его пароль для соединения с БД и снимем флаг «требовать проверку пароля».

Для хранения набора тестовых данных в программе создадим класс:

//класс для хранения тестовых вставляемых данных

Tdata = class

//поля таблицы

f1,f2,f3:string;

i1,i2,i3,i4,i5,i6:integer;

//запросы на вставку данных в 4 таблицы

query_pk_ch,

query_pk_vc,

query_np_ch,

query_np_vc:string;

//генерировать данные и текст запросов

procedure generate (num:integer);

end;

Каждый экземпляр этого класса — строка в каждой из 4х таблиц. Экземпляры хранятся в свойстве Data формы. Построим программу таким образом, чтобы инициализировать набор данных только один раз. Для этого используем свойство Enabled кнопок. В методе generate класса Tdata создана подпрограмма genstr для инициализации каждого строкового атрибута случайным набором символов.

Теперь напишем обработчики для кнопок, взывающих выполнение экспериментов с БД. Код каждого обработчика включает следующие элементы:

— подключение к БД,

— внесение данных в таблицы с замером времени,

— обновление данных таблиц с замером времени,

— чтение данных из таблиц с замером времени,

— удаление данных из таблиц (для возможности дальнейших экспериментов),

— отключение от БД.

Теперь приложение нужно отладить и можно проводить эксперименты.

2.4 Проведение экспериментов

Предполагалось, что тестовый набор будет содержать от 10 000 до 50 000 строк. Тогда чтобы выполняться программе нужно от 4 до 11 Мб свободной оперативной памяти, чтобы не использовать файл подкачки и не замедлять выполнение запросов. Эта цифра получена следующим путём: хранение тестовых данных, минимум: 10 000…50 000*(3*50+6*4) байт = 1700…8500 Кб = 1,7…8,5 Мб. Пространство для выполнения программы: минимум 2 Мб.

В ходе проведения эксперимента выяснилось, что производительность СУБД сильно отличается: в MySQL запросы на вставку выполняются очень медленно, что, вероятно, вызвано, использованием медленного ODBC драйвера. СУБД активно использовала жёсткий диск. Поэтому было принято решение увеличить мощность тестового набора для Interbase (до 30 000 строк) и уменьшить для MySQL (до 3000 строк).

Тестирование производилось на компьютере cо следующими характеристиками: процессор — Intel Sempron 2800+ GHz, оперативная память 512 Mb, винчестерWD, 7200 Об/мин, операционная система Windows XP SP 2.

Для достоверности эксперименты с каждой СУБД проведены 3 раза. Результаты выполнения программы представлены в приложении Б.

Рисунок 7 — Программа во время тестирования

3. Анализ результатов

Проведём анализ результатов, исходя из цели, поставленной во введении. На следующих графиках представлено время выполнения тестов в миллисекундах.

Рисунок 8 — Вставка данных Interbase

Как видно из рисунка 8 вставка данных в атрибуты типа varchar по времени не отличается от вставки в атрибуты типа char. Есть значительное (~30%) отличие в использовании первичного ключа. Это говорит о том, что происходит обновление соответствующего индекса первичного ключа, которое увеличивает время обработки.

Рисунок 9 — Вставка данных MySQL

Из рисунка 9 видно, что в MySQL есть различие в использовании char и varchar. Как и ожидалось, varchar обрабатывается чуть медленнее (что более заметно при использовании первичного ключа). Так же наблюдается различие в использовании первичного ключа, однако гораздо менее заметное (~2%), чем в случае Interbase.

Рисунок 10 — Обновление данных Interbase

Рисунок 11 — Обновление данных MySQL

Как видно из рисунков 10 и 11, при обновлении данных имеет значение, какой тип имеют обновляемые атрибуты. Varchar в первичном ключе обрабатывается медленнее на ~30%. Не в первичном ключе можно считать скорость обновления примерно равной, и заметно меньшей, чем в первичном ключе. Таким образом, можно заключить, что различие между типами может лежать в способах построения индекса первичного ключа.

Рисунок 12 — Выборка данных Interbase

Глядя на рисунок 12 можно сделать следующие выводы. Во-первых, индекс имеет большое значение при выполнении запросов на выборку в СУБД Interbase. В обоих случаях сравнения скорость выполнения запроса на индексируемых полях значительно выше, более того, её можно считать нулевой. Во-вторых, при выборке незначительно быстрее оказываются поля типа varchar.

Рисунок 13 — Выборка данных MySQL

Из рисунка 13 видно, что в СУБД MySQL индексы так же имеют значение, более того, индексы на varchar работают эффективнее. В целом запросы выполняются довольно быстро (заметим однако, что при тестировании MySQL брался набор данных в 10 раз меньший чем при тестировании Interbase).

Заключение

В целом можно заключить, что производительность запросов при обработке типов char и varchar примерно одинакова, если не используются индексы.

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

При выполнении запросов на выборку, тип varchar оказывается эффективнее, особенно при использовании индексов. При выполнении запросов на вставку и обновление данных тип varchar менее эффективен, опять же особенно при использовании индексов.

Список использованной литературы

" SQL Полное руководство" BHV, Москва, 2003

Мамаев Е., Шкарина Л. «Microsoft SQl Server 2000 для профессионалов». — СПб: Питер, 2002

Хоторн Роб «Разработка баз данных, Micrososoft SQL Server 2000». — Вильямс, 2001

Шарон Б., Мэйбл Грэг «Sql Server 2000, Энциклопедия программиста». — ДиаСофт, 2001

Приложение А

(обязательное)

«Скрипт на создание таблиц»

/*не первичный ключ, char */

CREATE TABLE «TEST_NPK_CHAR»

(

" F1″ CHAR (50),

" F2″ CHAR (50),

" F3″ CHAR (50),

" I1″ INTEGER,

" I2″ INTEGER,

" I3″ INTEGER,

" I4″ INTEGER,

" I5″ INTEGER,

" I6″ INTEGER

);

/*не первичный ключ, varchar */

CREATE TABLE «TEST_NPK_VARCHAR»

(

" F1″ VARCHAR (50),

" F2″ VARCHAR (50),

" F3″ VARCHAR (50),

" I1″ INTEGER,

" I2″ INTEGER,

" I3″ INTEGER,

" I4″ INTEGER,

" I5″ INTEGER,

" I6″ INTEGER

);

/* первичный ключ, char */

CREATE TABLE «TEST_PK_CHAR»

(

" F1″ CHAR (50) NOT NULL,

" F2″ CHAR (50) NOT NULL,

" F3″ CHAR (50) NOT NULL,

" I1″ INTEGER NOT NULL,

" I2″ INTEGER,

" I3″ INTEGER,

" I4″ INTEGER,

" I5″ INTEGER,

" I6″ INTEGER,

PRIMARY KEY («F1», «F2», «F3», «I1»)

);

/* первичный ключ, varchar */

CREATE TABLE «TEST_PK_VARCHAR»

(

" F1″ VARCHAR (50) NOT NULL,

" F2″ VARCHAR (50) NOT NULL,

" F3″ VARCHAR (50) NOT NULL,

" I1″ INTEGER NOT NULL,

" I2″ INTEGER,

" I3″ INTEGER,

" I4″ INTEGER,

" I5″ INTEGER,

" I6″ INTEGER,

PRIMARY KEY («F1», «F2», «F3», «I1»)

);

Приложение Б

(обязательное)

«Результаты выполнения программы»

Примечание: первая колонка — начало выполнения запросов, вторая — окончание, третья — длительность в миллисекундах, третья — условное название теста. Приведены результаты первого выполнения тестов. Для Interbase тестовый набор из 30 000 строк, для MySQL — из 3000.

25 052 773 250 560 983 040генерация данных для вставки

начало тестирования Interbase

250 585 322 509 429 342 208вставка пк char

250 942 932 513 008 549 888вставка пк varchar

251 300 852 515 332 816 896вставка не пк char

251 533 282 517 649 129 472вставка не пк varchar

25 176 501 251 849 846 784обновление пк char

251 849 942 519 837 327 360обновление пк varchar

25 198 373 252 034 703 360обновление не пк char

25 203 470 252 092 084 224обновление не пк varchar

252 092 082 520 924 832выборка (=) пк char

25 209 258 252 092 580выборка (=) пк varchar

2 520 926 925 209 709 568выборка (=) не пк char

2 520 971 925 210 140 672выборка (=) не пк varchar

25 210 150 252 101 500выборка (like) пк char

252 101 502 521 016 000выборка (like) пк varchar

2 521 016 025 210 721 792выборка (like) не пк char

2 521 072 125 211 271 680выборка (like) не пк varchar

конец тестирования Interbase

2 534 997 125 350 301 184генерация данных для вставки

начало тестирования MySQL

253 533 362 542 593 081 344вставка пк char

254 259 302 549 848 457 216вставка пк varchar

254 984 942 556 820 570 112вставка не пк char

255 682 052 564 004 864 000вставка не пк varchar

2 564 004 825 640 619 520обновление пк char

25 640 629 256 417 800 192обновление пк varchar

2 564 178 025 642 281 472обновление не пк char

2 564 229 125 642 612 224обновление не пк varchar

256 426 122 564 263 232выборка (=) пк char

25 642 652 256 426 520выборка (=) пк varchar

256 426 622 564 267 200выборка (=) не пк char

256 426 822 564 269 216выборка (=) не пк varchar

256 427 022 564 271 200выборка (like) пк char

25 642 712 256 427 120выборка (like) пк varchar

256 427 222 564 274 208выборка (like) не пк char

256 427 422 564 276 224выборка (like) не пк varchar

конец тестирования MySQL