5+ фишек для оптимизации работы базы данных

2 Оптимизация SELECT и других запросов

Сначала приведем одно правило, касающееся всех запросов: Чем сложнее ваша
система привилений, тем больше издержек.

Если не было выполнено никаких операторов GRANT, MySQL каким-то образом
будет оптимизировать проверку полномочий. Таким образом при наличии очень
большого объема данных лучше, наверное, будет работать без привилегий. В
противном случае при большом количестве полномочий проверка результатов
будет происходить с увеличенными издержками.

Если проблема состоит в некоторой явной функции MySQL, всегда можно
протестировать ее в клиенте:

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

Из приведенного выше примера видно, что MySQL может выполнять более
1000000 операций сложения за 0,32 секунды на PentiumII 400MHz.

Все функции MySQL достаточно хорошо оптимизированы, но могут попадаться
некоторые исключения, и функция BENCHMARK(число_циклов,выражение) —
хороший инструмент для выяснения, присутствует ли проблема в запросе.

Главы

5.1. Oбзор оптимизации

5.1.1. Конструктивные ограничения
MySQL/компромиссы

5.1.2. Вопросы переносимости

5.1.3. Для чего мы использовали MySQL?

5.1.4. Набор тестов MySQL (The MySQL Benchmark Suite)

5.1.5. Использование собственных тестов

5.2. Оптимизация SELECT и других
запросов

5.2.1. Синтаксис оператора EXPLAIN
(получение информации о
SELECT)

5.2.2. Оценка производительности запроса

5.2.3. Скорость выполнения запросов
SELECT

5.2.4. Как MySQL оптимизирует выражения
WHERE

5.2.5. Как MySQL оптимизирует DISTINCT

5.2.6. Как MySQL оптимизирует LEFT JOIN и
RIGHT JOIN

5.2.7. Как MySQL оптимизирует ORDER BY

5.2.8. Как MySQL оптимизирует LIMIT

5.2.9. Скорость выполнения запросов
INSERT

5.2.10. Скорость выполнения запросов
UPDATE

5.2.11. Скорость выполнения запросов
DELETE

5.2.12. Другие советы по оптимизации

5.3. Вопросы блокировок

5.3.1. Как MySQL блокирует таблицы

5.3.2. Вопросы блокирования таблиц

5.4. Оптимизация структуры базы данных

5.4.1. Конструктивные особенности MySQL

5.4.2. Сделайте объем данных как можно меньше

5.4.3. Использование индексов в MySQL

5.4.4. Индексы столбцов

5.4.5. Многостолбцовые индексы

5.4.6. Почему так много открытых таблиц?

5.4.7. Открытие и закрытие таблиц в MySQL

5.4.8. Недостатки создания множества таблиц в
одной базе данных

5.5. Оптимизация сервера MySQL

5.5.1. Настройка параметров системы,
компляции и запуска

5.5.2. Настройка параметров сервера

5.5.3. Как компиляция и линкование влияет на
скорость MySQL

5.5.4. Как MySQL использует память

5.5.5. Как MySQL использует DNS

5.5.6. Синтаксис команды SET

5.6. Вопросы, относящиеся к диску

5.6.1. Использование символических ссылок

Оптимизация — сложная задача, потому
что она, в конечном счете, требует
понимания системы в целом. В
отдельных случаях для выполнения
локальной оптимизации достаточно
знать систему или приложение не в
полном объеме, однако чтобы сделать
систему более оптимальной, нужно
разбираться в том, как она устроена.

В этой главе будут рассмотрены
различные способы оптимизации MySQL и
представлены некоторые примеры ее
выполнения. Не следует забывать,
однако, что всегда можно найти
некоторые дополнительные
возможности сделать систему еще
быстрее (хотя каждый следующий шаг в
этом направлении будет даваться все
труднее и труднее).

Oбзор оптимизации

5.1.1. Конструктивные ограничения
MySQL/компромиссы

5.1.2. Вопросы переносимости

5.1.3. Для чего мы использовали MySQL?

5.1.4. Набор тестов MySQL (The MySQL Benchmark Suite)

5.1.5. Использование собственных тестов

Чтобы увеличить скорость системы,
необходимо, разумеется, прежде
всего разбираться в ее конструкции.
Кроме того, нужно знать, какие
функции будет выполнять система и
какие «узкие места» в ней имеются.

Ниже приведен список наиболее часто
встречающихся «узких мест»:

• Поиск данных на диске. Чтобы найти
  на диске какой-то фрагмент данных,
  требуется некоторое время. Для
  устройств выпуска 1999 года среднее
  время поиска составляет менее
  10мс, так что теоретически можно
  выполнять приблизительно 100
  операций поиска в секунду. Это
  время можно ненамного уменьшить,
  заменив диски более новыми. Для
  одной таблицы поиск на диске
  оптимизировать очень сложно.
  Такую оптимизацию можно
  выполнить путем распределения
  данных по нескольким дискам.

• Дисковое чтение/запись. После
  выполнения поиска, когда найдена
  соответствующая позиция на диске,
  мы можем считать данные. Для
  устройств выпуска 1999 года
  производительность одного диска
  составляет около 10-20Мб/с. Дисковое
  чтение/запись легче
  оптимизировать, чем дисковый
  поиск, поэтому читать можно
  параллельно с нескольких дисков.

• Циклы процессора. Когда мы
  помещаем данные в основную память
  (или если они уже находятся там),
  мы должны обработать их, чтобы
  получить результат. Наличие
  маленьких по сравнению с объемом
  ОЗУ таблиц — наиболее часто
  встречающийся лимитирующий
  фактор. Но в этом случае, в
  общем-то, скорость обработки
  маленьких таблиц значения не
  имеет.

• Пропускная способность ОЗУ (memory
  bandwidth). Когда процессору требуется
  больше данных, чем может вместить
  его кэш, узким местом становится
  пропускная способность памяти. В
  большинстве систем это узкое
  место встречается редко, однако о
  нем нужно знать.

5.1.1. Конструктивные ограничения
MySQL/компромиссы

При использовании обработчика
таблиц MyISAM MySQL применяет
очень быструю блокировку таблиц
(несколько потоков чтения/один
поток записи). Самая большая
проблема при использовании этого
типа таблиц возникает в случае
непрерывного потока обновлений в
сочетании с медленными выборками
из одной и той же таблицы. Если эта
проблема касается лишь некоторых
таблиц, можно использовать вместо
них таблицы другого типа. See
Глава 7, Типы таблиц MySQL.

MySQL может работать как с
транзакционными так и с
нетранзакционными таблицами.
Чтобы обеспечить возможность
нормальной работы с
нетранзакционными таблицами (для
которых невозможен откат, если
что-нибудь произойдет не так, как
надо), в MySQL существуют следующие
правила:

• Все столбцы имеют значения по
  умолчанию.

• Если в столбец вставляется
  «неправильное» значение
  (например, NULL в столбец
  NOT NULL или слишком
  большое числовое значение — в
  числовой столбец), MySQL не будет
  выводить сообщение об ошибке, а
  просто поместит в столбец
  «наиболее подходящее возможное
  значение». Для числовых значений
  это 0, наименьшие возможные
  значения или наибольшее
  возможное значение. Для строк
  это либо пустая строка, либо
  самая длинная строка, которая
  может быть в столбце.

• Все вычисляемые выражения
  возвращают значение, которое
  можно использовать вместо того,
  чтобы сигнализировать об ошибке.
  Например, выражение 1/0
  возвратит NULL

Существование приведенных выше
правил объясняется тем, что перед
началом выполнения запроса
невозможно проверить, сможет ли он
выполниться. Если проблема
обнаружится после обновления
нескольких строк, мы не можем
выполнить полный откат, поскольку
это может не поддерживаться типом
таблицы. Остановиться в этот
момент тоже нельзя, потому что
тогда обновления будут выполнены
наполовину, что является, вероятно,
самым худшим возможным
результатом. В данном случае лучше
выбрать «наименьшее из зол», а затем
продолжать, как будто ничего не
произошло.

Отсюда следует, что MySQL нельзя
использовать для проверки
содержимого полей. Это нужно
делать в приложении.

5.1.2. Вопросы переносимости

Поскольку все SQL-серверы
поддерживают разные части
стандарта SQL, то разработка
переносимых SQL-приложений занимает
время. Для очень простых
запросов/вставок это достаточно
просто, однако чем сложнее
становится ваше приложение, тем
сложнее делать запросы
переносимыми. Если вы хотите чтобы
ваше приложение работало
максимально быстро с разными
серверами SQL, задача еще более
усложняется.

Чтобы сделать сложное приложение
переносимым в области SQL, вам
следует выбрать те SQL-серверы, с
которыми оно должно работать.

Чтобы узнать, какие функции, типы и
ограничения существуют в
выбранных вами серверах, можно
воспользоваться приложением MySQL
crash-me. crash-me
пока еще далека от того, чтобы
тестировать все, что возможно, но
тем не менее, является достаточно
качественным сравнительным тестом
по более чем 450 характеристикам.

Например, если вы хотите
использовать Informix или DB2, имена
полей не должны быть длиннее 18
символов.

И тесты MySQL (MySQL benchmarks), и программа
crash-me являются
достаточно независимыми от
конкретной СУБД. Ознакомившись с
тем, как мы решили этот вопрос, вы
можете получить представление о
том, как следует писать
переносимые программы для работы с
базами данных. Тесты можно найти в
каталоге sql-bench в
поставке исходных текстов MySQL. Они
написаны на Perl с использованием
интерфейса DBI (который, кстати, уже
решает проблему получения доступа
к разным базам данных).

См. http://www.mysql.com/information/benchmarks.html — там
находятся результаты тестов.

Как можно видеть по этим
результатам, у каждой СУБД есть
свои слабые стороны. Все они
построены по-разному и
спроектированы с учетом различных
компромиссов, что приводит к
различиям в поведении этих систем.

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

Чтобы сделать свое приложение
не
зависящим от СУБД, вам следует
создать некий быстро расширяемый
интерфейс, через который
происходит обработка данных.
Поскольку C++ доступен на
большинстве систем, имеет смысл
создать соответствующие
классы-интерфейсы к базам данных.

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

В этом случае структура таблицы
должна содержать информацию об
изначальном размере кэша и то, как
часто таблица должна быть
обновлена в общем случае.

5.1.3. Для чего мы использовали MySQL?

На первых этапах развития MySQL его
функциональные возможности
разрабатывались под потребности
самого крупного из наших
заказчиков. Это делалось для
обслуживания больших хранилищ
данных для пары самых крупных
продавцов в Швеции.

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

Объем этих данных весьма
значителен (в сумме приблизительно
7 миллионов транзакций в месяц), и,
кроме того, мы должны
предоставлять пользователям
данные за периоды от 4 до 10 лет.
Каждую неделю мы получаем от
клиентов просьбы предоставить
«мгновенный» доступ к новым отчетам
на основе этих данных.

Для графических данных мы написали
простой инструмент на C, который
может создавать GIF-файлы на основе
результата SQL-запроса
(определенным образом обработав
результат). Это также динамически
выполняется из создаваемой Perl’ом
странички.

В большинстве случаев новый отчет
может быть создан просто путем
копирования существующего
сценария и модифицирования
SQL-запроса в нем. Иногда требуется
дополнительно добавить поля в
существующую итоговую таблицу или
сгенерировать новую таблицу, но
это также делается очень просто,
поскольку у нас все транзакционные
таблицы хранятся на диске (в
настоящее время у нас имеется
меньшей мере 50Гб транзакционных
таблиц и 200Гб других клиентских
данных.)

Кроме того, мы обеспечиваем для
наших клиентов возможность
обращаться к итоговым таблицам
непосредственно через интерфейс
ODBC; таким образом, продвинутые
пользователи могут самостоятельно
экспериментировать с данными.

У нас не было каких-либо проблем
при обработке этих данных на
весьма скромном Sun Ultra SPARCstation (2×200
МГц). Недавно мы заменили один из
наших серверов на
двухпроцессорный UltraSPARC с тактовой
частотой 400 МГц и теперь планируем
начать обрабатывать транзакции на
уровне продукта, что будет
означать десятикратное увеличение
объема данных. Мы полагаем, что
сможем справиться с этим объемом
лишь только добавлением
соответствующего количества
дисков.

Помимо этого мы экспериментируем с
Intel-Linux, чтобы получить больше
производительности по низшей цене.
Теперь, имея бинарно-переносимый
формат базы данных (появившийся в
версии 3.23), мы начнем использовать
его для некоторых частей
приложения.

Наша интуиция подсказывает, что у
Linux производительность
значительно выше при низкой и
средней загрузке, а у Solaris — когда
высокая загрузка начнет возникать
из-за критического дискового
ввода-вывода. Но у нас нет пока
никаких выводов по этому поводу.
После обсуждения с разработчиками
ядра Linux мы выяснили, что в это
может быть побочным эффектом
работы ядра: когда Linux дает слишком
много ресурсов пакетным заданиям,
задачи взаимодействия начинают
замедляться. Из-за этого машина
работает очень медленно и не
реагирует ни на что, пока
обрабатываются большие пакеты.
Надеемся, что в последующих ядрах
Linux этот вопрос найдет свое
решение.

5.1.4. Набор тестов MySQL (The MySQL Benchmark Suite)

В данном разделе будет находиться
техническое описание набора
эталонных тестов MySQL (и
crash-me), но оно пока еще не
написано. В настоящее время можно
получить хорошее представление об
эталонном тесте, глядя на код и
результаты в каталоге
sql-bench любого исходного
дистрибутива MySQL.

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

Обратите внимание: этот эталонный
тест — однопоточный, так что в нем
измеряется минимальное время
выполнения операций. В будущем мы
планируем добавить в данный набор
большое количество многопоточных
тестов.

Например (выполнено на одной
машине под NT 4.0):

В предыдущем тесте MySQL запускался с
8-мегабайтным индексным кэшем.

Гораздо больше результатов тестов
вы сможете найти по адресу
http://www.mysql.com/information/benchmarks.html.

Обратите внимание: данные об Oracle
отсутствуют — по просьбе компании
Oracle вся информация по их продукту
была удалена. Все эталонные тесты
для Oracle должны быть пропущены
через компанию Oracle! Мы считаем,
однако, что при таком способе
тестирования результаты эталонных
тестов для Oracle будут в
высокой
степени различаться, поскольку
приведенные на сайте результаты
призваны показывать на что
способна стандартная инсталляция
для одного клиента.

Чтобы выполнить набор эталонных
тестов, необходимо загрузить
исходный дистрибутив MySQL,
установить драйвер perl DBI, драйвер
perl DBD для той базы данных, которую
нужно проверить, а затем выполнить:

cd sql-bench
perl run-all-tests --server=#

где # — один из поддерживаемых
серверов. Список всех опций и
поддерживаемых серверов можно
получить, выполнив run-all-tests
--help.

Программа crash-me пытается
определить, какие функции
поддерживаются СУБД, и какие
возможности и ограничения имеют
эти функции при выполнении
запросов. Например, она определяет
следующее:

• какие типы столбцов
  поддерживаются

• сколько индексов поддерживается

• какие функции поддерживаются

• насколько большим может быть
  запрос

• насколько большим может быть
  столбец VARCHAR

5.1.5. Использование собственных тестов

Чтобы найти «узкие места» в своем
приложении и базе данных, вы должны
их тщательно протестировать. После
устранения «узкого места» (или
после замены его некой заглушкой)
можно легко идентифицировать
следующее «узкое место» (и так
далее). Даже если общая
производительность приложения
достаточна, нужно по крайней мере
выявить все «узкие места» и
определиться с тем, как их
устранять, — на будущее, если
когда-нибудь потребуется
дополнительная
производительность.

Примеры переносимых программ
программы для эталонного
тестирования можно найти в наборе
тестов MySQL. See Раздел 5.1.4, «Набор тестов MySQL (The MySQL Benchmark Suite)».
Можно взять любую программу из
этого набора и модифицировать ее
для своих потребностей. Таким
образом можно испытывать
различные решения проблемы и
проверять, которое из них самое
быстрое.

Зачастую некоторые проблемы
проявляются только тогда, когда
система очень сильно загружена. К
нам часто обращаются клиенты,
которые, запустив
(протестированную) систему в
производство, сталкиваются с
проблемами, связанными с
нагрузкой. На сегодня причиной
каждого из этих случаев были либо
проблемы, связанные с базовой
конструкцией (при высокой нагрузке

выполняется сканирование таблиц),
либо проблемы ОС/библиотек. И
большинство таких проблем было бы
легче
устранить до начала промышленной
эксплуатации систем.

Чтобы избежать подобных проблем,
нужно постараться выполнить
эталонное тестирование всего
приложения при самой плохой
возможной нагрузке! Для этого
можно использовать программу
Super Smack, которая доступна
по адресу:
http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz.
Эта программа, как следует из ее
имени (smack — шлепок — прим.
пер.), способна поставить
систему на колени, так что
используйте её только на
разрабатываемых системах (проще
говоря, в девелопменте).