Введение
"Ну у вас и запросы, - сказала база данных и повесилась."
фольклор
Тестируется скорость обработки сервером MySQL запросов ограниченных модификатором LIMIT на больших объемах данных. Также тестируется скорость выборки по идентификатору связанной таблицы.
При тестировании использовался
MySQL 5.0.27
Sempron 2500+ (1.75GHz), 480 MB of RAM, Windows XP sp 2.
Тестирование производилось на двух самых популярных движках MySQL: InnoDB и MyISAM.
InnoDB
Достоинства: умеет транзакции, поддерживает целостность данных
Недостатки: все таблицы всех баз данных на сервере хранит в одном файле, медленный (если сравнивать с MyISAM), хочет очень много памяти.
Для тестирования были созданы таблицы main, table1, table2, table3.
CREATE TABLE `table1` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
table2, table3 были созданы аналогично
CREATE TABLE `main` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) default NULL,
`text` varchar(200) default NULL,
`t_id` int(11) default NULL,
`t2_id` int(11) default NULL,
`t3_id` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `t_id` (`t_id`),
KEY `t2_id` (`t2_id`),
KEY `t3_id` (`t3_id`),
CONSTRAINT `main_fk` FOREIGN KEY (`t_id`)
REFERENCES `table1` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `main_fk1` FOREIGN KEY (`t2_id`)
REFERENCES `table2` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `main_fk2` FOREIGN KEY (`t3_id`)
REFERENCES `table3` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
В таблицы table1, table2, table3 были записаны по 100000 записей. В поле name записывалась строчка из примерно 150 случайных символов.
В таблицу main было записано 250000 записей, в поле name и text записывалась строчка из примерно 150 случайных символов, в t_id, t2_id и t3_id записывался случайный идентификатор из таблицы table1, table2 и table3 соответственно.
К финалу операции размер файла ibdata1 достиг размера 226 мб.
Первый же запрос
"select main.id, main.name, main.text, table1.name, table2.name, table3.name from main, table1, table2, table3 where table1.id=t_id and table2.id=t2_id and table3.id=t3_id limit 90000,1000"
озадачил MySQL на 45 минут, после чего терпение экспериментатора лопнуло и сервер был перезапущен. Изучение настроек показало, что серверу банально не хватает памяти, каковая ему была немедленно предоставлена. Основная правка конфигурации заключалась в выставлении переменной innodb_buffer_pool_size на 300M.
Последовательно вызываем запрос
"select main.id, main.name, main.text, table1.name, table2.name, table3.name from main, table1, table2, table3 where table1.id=t_id and table2.id=t2_id and table3.id=t3_id limit X,Y"
с разными значениями X и Y
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
90000 |
1000 |
1:38 |
|
2 |
100000 |
1000 |
0:02 |
|
3 |
110000 |
10000 |
0:14 |
|
4 |
120000 |
10000 |
0:02 |
|
5 |
130000 |
10000 |
0:02 |
|
6 |
13000 |
10000 |
0:01 |
|
7 |
1000 |
1000 |
0:00.3 |
|
8 |
50000 |
10000 |
0:01 |
|
9 |
80000 |
20000 |
0:03 |
|
перезапуск сервера |
|
10 |
100000 |
1000 |
1:38 |
|
11 |
120000 |
10000 |
0:03 |
(здесь и далее считается только время выполнения запроса, выкачивание данных не производится)
Отсюда можно сделать вывод, что после первого запроса его результат был закэширован, причем закэширован целиком для полного запроса, без limit-а и последующие запросы просто обращались к кэшу. Не совсем понятно, почему запрос №3 выполнялся 14 секунд, предположительно закэширован был не весь результат, а только первые 91 тысяча строчек + еще сколько-то. А запрос №3 спровоцировал кэширование остатка.
Также отсюда видно, что выборка 10000 записей работает практически также быстро, как и 1000.
Перезапуск сервера благополучно убивает кэш.
Выборка по id связанной таблицы:
интервал в 5000 значений:
"select main.id, main.name, main.text, table1.name, table2.name, table3.name from main, table1, table2, table3 where table2.id>15000 and table2.id<20000 and table1.id=t_id and table2.id=t2_id and table3.id=t3_id"
выполняется 1 минуту, 33 секунды.
одно значение:
"select main.id, main.name, main.text, table1.name, table2.name, table3.name from main, table1, table2, table3 where table1.id=t_id and table2.id=t2_id and table3.id=t3_id and table1.id=45300"
в первый раз запрос выполняется 2 секунды, повторные запросы такого типа выполняются примерно 800 миллисекунд.
1000 значений:
последовательно вызываем запрос
"select main.id, main.name, main.text, table1.name, table2.name, table3.name from main, table1, table2, table3 where table3.id>X and table3.id<Y and table1.id=t_id and table2.id=t2_id and table3.id=t3_id"
с разными значениями X и Y
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
15000 |
16000 |
0:57 |
|
2 |
16000 |
17000 |
0:19 |
|
3 |
17000 |
18000 |
0:10 |
|
4 |
18000 |
19000 |
0:03 |
|
5 |
19000 |
20000 |
0:02 |
|
6 |
20000 |
21000 |
0:01 |
|
7 |
60000 |
61000 |
0:01 |
под финал видимо кэшируется все что только можно и результаты радуют своей скоростью.
Тот же самый эффект наблюдается на запросе
"select * from main where t_id>X and t_id<Y"
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
60000 |
61000 |
0:30 |
|
2 |
80000 |
81000 |
0:18 |
|
3 |
10000 |
11000 |
0:07 |
|
4 |
90000 |
91000 |
0:03 |
|
5 |
20000 |
21000 |
0:02 |
|
6 |
30000 |
31000 |
0:01 |
Попробуем теперь запросы попроще:
|
№ |
Запрос |
Время выполнения м:сс |
|
1 |
select * from main |
0:06 |
|
перезапуск сервера |
|
2 |
select * from main |
0:12 |
|
3 |
select * from main |
0:06 |
|
4 |
select * from main limit 200000,10000 |
0:00.8 |
|
перезапуск сервера |
|
5 |
select * from main limit 200000,10000 |
0:08 |
|
6 |
select * from table1_my order by name |
0:15 |
Результаты запросов попроще тоже благополучно кладутся в кэш и берутся оттуда по мере необходимости.
Теперь попробуем добавить в наш исходный запрос сортировку:
"select main.id,main.name,main.text,table1.name,table2.name,table3.name from main, table1, table2, table3 where table1.id=t_id and table2.id=t2_id and table3.id=t3_id order by table1.name limit X,Y"
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
90000 |
1000 |
1:40 |
|
2 |
10000 |
10000 |
0:01 |
|
3 |
140000 |
1000 |
7:52 |
|
4 |
150000 |
1000 |
0:07 |
На третьем запросе у сервера благополучно закончилась память, и время выполнения увеличилось радикально, что вообще не есть здорово. Если перезапустить сервер, а потом выполнить запрос еще раз, это займет минуту и 40 секунд. Похоже у сервера проблемы с перераспределением памяти.
MyISAM
Достоинства: быстрый на несложных запросах.
Недостатки: не умеет транзакций, не умеет constraints, запись в таблицу блокирует чтение.
Продублируем нашу базу на MyISAM таблицах. Кстати, заполнить мусором таблицы получилось гораздо быстрее, особенно таблицу main (видимо из-за того, что в ней нет constraints). Каждая табличка получит постфикс "_my". Созданная база заняла 208 мб.
Начнем с того, с чего закончили тестирование InnoDB
"select main_my.id, main_my.name, main_my.text, table1_my.name, table2_my.name, table3_my.name from main_my, table1_my, table2_my, table3_my where table1_my.id=t_id and table2_my.id=t2_id and table3_my.id=t3_id order by table1_my.name limit 90000,1000"
Выполняется оно аж 8 минут.
Повторно - 6 минут и 2 секунды.
Теперь уберем сортировку "select main_my.id, main_my.name, main_my.text, table1_my.name, table2_my.name, table3_my.name from main_my, table1_my, table2_my,table3_my where table1_my.id=t_id and table2_my.id=t2_id and table3_my.id=t3_id limit X,Y"
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
90000 |
1000 |
0:28 |
|
2 |
80000 |
1000 |
0:03 |
|
3 |
10000 |
1000 |
0:00.5 |
|
4 |
200000 |
1000 |
0:07 |
|
5 |
240000 |
1000 |
0:09 |
|
6 |
150000 |
1000 |
0:05 |
|
7 |
70000 |
1000 |
0:02 |
|
8 |
30000 |
1000 |
0:01 |
|
9 |
230000 |
1000 |
0:08 |
|
10 |
220000 |
10000 |
0:08 |
|
11 |
0 |
10000 |
0:00.8 |
|
перезапуск сервера |
|
12 |
полная выборка |
0:49 |
|
13 |
полная выборка |
0:21 |
И все возвращается на круги своя. Здесь тоже запрос кэшируется, но почему-то по мере приближения к концу результата времени на запрос уходит все больше и больше.
Выборка по id связанной таблицы:
интервал в 5000 значений:
"select main_my.id, main_my.name, main_my.text, table1_my.name, table2_my.name, table3_my.name from main_my, table1_my, table2_my, table3_my where table2_my.id>15000 and table2_my.id<20000 and table1_my.id=t_id and table2_my.id=t2_id and table3_my.id=t3_id"
выполняется 2 минуты, 14 секунд.
одно значение:
"select main_my.id, main_my.name, main_my.text, table1_my.name, table2_my.name, table3_my.name from main_my, table1_my, table2_my, table3_my where table1_my.id=t_id and table2_my.id=t2_id and table3_my.id=t3_id and table1_my.id=45300"
выполняется примерно 100 миллисекунд (на порядок быстрей, чем в InnoDB)
1000 значений:
последовательно вызываем запрос
"select main_my.id, main_my.name, main_my.text, table1_my.name, table2_my.name, table3_my.name from main_my, table1_my, table2_my, table3_my where table3_my.id>X and table3_my.id<Y and table1_my.id=t_id and table2_my.id=t2_id and table3_my.id=t3_id"
с разными значениями X и Y
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
15000 |
16000 |
1:00 |
|
2 |
16000 |
17000 |
0:35 |
|
3 |
17000 |
18000 |
0:22 |
|
4 |
18000 |
19000 |
0:16 |
|
5 |
19000 |
20000 |
0:13 |
|
6 |
20000 |
21000 |
0:10 |
|
7 |
60000 |
61000 |
0:09 |
|
8 |
80000 |
81000 |
0:07 |
|
9 |
10000 |
11000 |
0:06 |
|
10 |
11000 |
12000 |
0:05 |
|
11 |
12000 |
13000 |
0:05 |
Здесь тоже наблюдается тенденция к сокращению времени выполнения, но не такая заметная как в InnoDB.
Тот же самый эффект наблюдается на запросе
"select * from main_my where t_id>X and t_id<Y"
|
№ |
X |
Y |
Время выполнения м:сс |
|
1 |
60000 |
61000 |
0:08 |
|
2 |
80000 |
81000 |
0:07 |
|
3 |
10000 |
11000 |
0:06 |
|
4 |
90000 |
91000 |
0:06 |
|
5 |
20000 |
21000 |
0:05 |
|
6 |
30000 |
31000 |
0:04 |
|
7 |
31000 |
32000 |
0:04 |
|
8 |
32000 |
33000 |
0:03 |
|
9 |
33000 |
34000 |
0:03 |
Попробуем теперь запросы попроще:
|
№ |
Запрос |
Время выполнения м:сс |
|
1 |
select * from main_my |
0:05 |
|
2 |
select * from main_my limit 10000 |
0:00.2 |
|
3 |
select * from main_my limit 200000,10000 |
0:00.5 |
|
4 |
select * from table1_my |
0:01 |
|
5 |
select * from table1_my limit 80000,20000 |
0:00.3 |
|
6 |
select * from table1_my order by name |
0:12 |
|
7 |
select main_my.id, main_my.name, main_my.text, table1_my.name from main_my, table1_my where table1_my.id=t_id |
0:11 |
Здесь не происходит никакого кэширования - при повторном вызове время выполнения меняется на сотые секунды.
| 
Тестирование производительности MySQL на больших объемах данных. InnoDB и MyISAM таблицы.
Лучшие игроки