Перед началом проектирования базы данных поставьте себе задачу добиться максимальной ясности спецификации, даже если на это уйдет больше времени. Помните о том, что услуги программистов стоят дорого, особенно если им приходится разбираться с малопонятным проектом. Простое решение обычно является наилучшим.
Перенося базу данных в производственную среду, позаботьтесь о том, чтобы производительность базы данных была адекватной. Если к проекту прилагается формальная спецификация требований, просмотрите, указываются ли в ней какие-либо ограничения производительности. Для приложений, работающих с базами данных, нередко задается максимальное время выполнения запросов. Продолжительность времени между вводом инструкции и получением результатов запроса зависит от многих факторов. Необходимо заранее учесть те факторы, которые впоследствии нельзя будет контролировать.
Если обнаруживается, что система требует оптимизации, в первую очередь подумайте об обновлении аппаратной части. Это может оказаться самым дешевым вариантом. В 1965 г. Гордон Мур (Gordon Moore) установил, что вычислительные мощности удваиваются каждые 18 месяцев. Данное правило называют законом Мура. Но, несмотря на столь стремительный рост производительности, удельная стоимость вычислительных средств неуклонно снижается. Например, центральные процессоры за полтора года удвоят тактовую частоту, хотя стоить будут так же, как и полтора года назад. Таким образом, обновление компьютера может обойтись дешевле, чем оптимизация проекта. Во вторую очередь стоит подумать об обновлении программного обеспечения. Основной программный компонент— это операционная система. Известно, что Linux и BSD UNIX позволяют повысить производительность старых компьютеров, превосходя в этом отношении коммерческие операционные системы, такие, как Windows, особенно если бессбойная работа сервера очень важна.
Обновляется и сама программа MySQL. Когда появится новая версия, ее производительность будет повышена в сравнении с текущей. Но и в текущую версию регулярно вносят мелкие исправления, так что желательно идти в ногу со временем. Основная причина оптимизации — желание сэкономить деньги (оставим в стороне личное удовлетворение и другие причины). Не забывайте об этом в своих попытках повысить производительность программы. Нет смысла затрачивать на оптимизацию больше денег, чем она способна принести. Стоит потрудиться над такой программой, с которой работает множество людей, особенно если это коммерческое приложение. Что касается программ с открытыми кодами, то важность оптимизации здесь трудно определить. Лично я рассматриваю работу над такими проектами как хобби.
Чтобы процесс оптимизации был максимально эффективным, сосредоточьте усилия на самой медленной части программы, улучшение которой обеспечит наибольшую отдачу. Обычно пытаются найти более быстрые альтернативы применяемым алгоритмам. В вычислительной технике относительная эффективность алгоритма записывается в нотации "большого О". Например, запись О(n) означает, что время выполнения алгоритма пропорционально числу обрабатываемых элементов п. Алгоритм типа О(n) является очень медленным. Проанализируйте используемые в программе алгоритмы и подумайте, что можно сделать для их улучшения.
Тесты производительности
Прежде чем приступать к оптимизации, нужно вооружиться средствами измерения производительности. Предусмотрительные разработчики MySQL написали группу Perl сценариев, предназначенных для тестирования производительности MySQL и других СУБД. Эти сценарии расположены в каталоге sql-bench исходного дистрибутива. В подкаталоге Results находятся результаты множества тестов существующих систем, которые можно сравнить с собственными оценками.
В сценариях используется демонстрационная база данных, в которой выполняется восемь различных тестов. Эта база данных называется test и инсталлируется вместе с MySQL. Сценарий run_all_tests запускает все тесты последовательно. При наличии опции -log результаты работы сценария будут сохранены в каталоге output для последующего просмотра. Ниже приведена команда, запускающая тесты из каталога sql-bench.
Для работы этого сценария необходимо наличие в системе интерпретатора Perl и модуля DBI. Для экспериментов я использую старый компьютер Pentium с частотой 100 МГц, работающий под управлением RedHat Linux. Несмотря на слабую вычислительную мощность, программа MySQL демонстрирует на нем вполне приемлемую производительность. Кроме того, ограниченные возможности системы позволяют быстро выявлять неэффективные программные решения. Результаты тестов, полученные на этом компьютере, показаны в листинге 10.1. Несложно убедиться, что моя система работает примерно в 10 раз медленнее, чем самая медленная из систем, результаты тестов которых имеются в каталоге Results. Если бы такую производительность продемонстрировал рабочий сервер, нужно было бы немедленно обновить его аппаратную часть.
The result logs which were found and the options: 1 mysql-linux 2.2.16_22_i586 MySQL 3.23.39
===============================================================Operation | 1| | mysql-l|-----------------------------------------------------------Results per test in seconds: | |-----------------------------------------------------------ATIS | 549.00|Alter-table | 4836.00|Big-tables | 270.00|Connect | 607.00|Create | 2027.00|Insert | +88846.00|Select | +18339.00|wisconsin | 135.00|-----------------------------------------------------------
Листинг 10.1. (html, txt)
The results per operation:
--------------------------------------------------------alter table add (992) | 2670.00|alter table drop (496) | 2066.00|connect (10000) | 95.00|connect select 1 row (10000) | 115.00|connect select simple (10000) | 106.00|count (100) | 364.00|count distinct (1000) | +779.00|count distinct 2 (1000) | +605.00|count distinct big (120) | 1185.00|count distinct group (1000) | +726.00|count distinct group on key (1000) | +716.00|count distinct group on key parts (1) | +708.00|count distinct key prefix (1000) | +671.00|count group on key parts (1000) | 706.00|count on key (50100) | +7005.00|create + drop (10000) | 109.00|create MANY tables (10000) | 1196.00|create index (8) | 52.00|create key drop (10000) | 115.00|create table (31) | 0.00|delete all (12) | 219.00|delete all many keys (1) | 8960.00|delete big (1) | 5.00|delete big many keys (128) | 8956.00|delete key (10000) | 145.00|drop index (8) | 47.00|drop table (28) | 0.00|drop table when MANY tables (10000) | 105.00|insert (350768) | 1044.00|insert duplicate (100000) | 211.00|insert (100000) | 17849.00|insert many fields (2000) | 97.00|insert select 1 key (1) | 102.00|insert select 2 keys (1) | 125.00|min max (60) | 301.00|min max on key (85000) | +2627.00|multiple value insert (100000) | 102.00|order by big (10) | 560.00|order by big key (10) | 503.00|order by big key2 (10) | 479.00|order by big key desc (10) | 531.00|order by big key diff (10) | 586.00|order by big key prefix (10) | 487.00|order by key2 diff (500) | 57.00|order by key prefix (500) | 31.00|outer join (10) | 989.00|outer join found (10) | 918.00|outer join not found (500) | +36000.00|outer join on key (10) | 810.00|select 1 row (10000) | 17.00|select 2 rows (10000) | 23.00|select big (10080) | 700.00|select column + column (10000) | 24.00|select diff key (500) | +1470.00|select distinct (800) | 145.00|select group (2925) | +896.00|select group when MANY tables (10000) | 502.00|select join (100) | 25.00|select key (200000) | +1123.00|select key2 (200000) | +1213.00|select key2 return key (200000) | +1174.00|select key2 return prim (200000) | +1197.00|select key prefix (200000) | +1245.00|select key prefix join (100) | 197.00|select key return key (200000) | +1106.00|select key many fields (2000) | 172.00|select query cache (10000) | 1075.00|select query cache2 (10000) | 1075.00|select range (410) | +1812.00|select range key2 (25010) | 185.00|select range prefix (25010) | 197.00|select simple (10000) | 11.00|select simple join (500) | 20.00|update big (10) | 440.00|update of key (40000) | 627.00|update of key big (501) | 351.00|update of primary key many keys(256) | 3290.00|update with key (300000) | 1011.00|update with key prefix (100000) | 290.00|wisc benchmark (114) | 44.00|--------------------------------------------------------TOTALS | +124540.00|--------------------------------------------------------
Листинг 10.2. (html, txt)
Сценарий compare results суммирует и сравнивает результаты тестов. В листинге 10.1 приведен лишь один набор результатов. В действительности я немного сократил выходные данные, удалив ряд малозначащих пояснений. В первом блоке чисел указано время выполнения каждого из восьми тестов в секундах. Во втором блоке отображается статистика отдельных операций по всем тестам. Числа со знаком "плюс" — это приблизительные оценки, полученные для тестов, время выполнения которых превысило максимум.
Результаты тестов, предоставляемые разработчиками MySQL, можно использовать для выбора аппаратной платформы и операционной системы. На Web узле MySQL (http://www.mysql.com) постоянно публикуются обновляемые результаты и графики сравнения показателей MySQL с показателями других СУБД, работающих на идентичном оборудовании. Приводятся также данные, касающиеся работы MySQL на разных платформах.
Конечно, все эти тесты отражают лишь относительную производительность сервера. С их помощью можно узнать, насколько возрастет скорость его работы при изменении тех или иных настроек, но тесты не могут помочь в оптимизации базы данных. Для оценки производительности запросов необходимо воспользоваться инструкцией EXPLAIN. Эта инструкция, помимо всего прочего, сообщает о том, сколько записей будет прочитано при выполнении заданной инструкции SELECT. Каждая строка результатов соответствует одной исходной таблице, а порядок строк совпадает с порядком обращения к таблицам. Сообщаемое число записей может быть приблизительным, но погрешность очень мала. Произведение счетчиков записей является грубым критерием производительности запроса. Чем меньше это произведение, тем быстрее выполняется запрос.
Представим себе, к примеру, объединение таблицы, содержащей 15000 слов, с таблицей, содержащей 100000 слов. В худшем случае программе MySQL придется просмотреть все записи обеих таблиц. Сначала выбирается первая запись первой таблицы, а затем начинается просмотр записей второй таблицы до тех пор, пока не будет найдено совпадение. Умножив 15000 на 100000, получим 1,5 миллиарда операций чтения. На практике это число оказывается немного меньшим, но и его достаточно, чтобы получить представление о скорости запроса. Далее будет рассказываться о том, как с помощью индексов уменьшить количество записей, читаемых в процессе объединения таблиц.
С помощью журнала медленных запросов, описанного в лекции 8, можно легко найти наименее эффективные запросы. В дистрибутив MySQL входит сценарий mysqldumpslow предназначенный для упорядочения записей этого журнала по указанному в них времени выполнения запроса.
