А Б В Г Д Е Ж З И К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Э Ю Я
0-9 A B C D I F G H IJ K L M N O P Q R S TU V WX Y Z #


Чтение книги "MySQL 5.0. Библиотека программиста" (страница 30)

   5.6. Резюме

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

   Глава 6
   Оптимизация

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

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

   Если «узким местом» вашей базы данных является одна или несколько таблиц, попробуем скорректировать структуру этих таблиц:
   • выбрать наиболее подходящий тип таблицы;
   • минимизировать объем данных в таблице;
   • пересмотреть набор индексов в таблице;
   • указать необходимые значения опциональных параметров.
   Как было сказано в предыдущих главах, каждый тип таблиц имеет свои преимущества и недостатки. Если требуется обеспечить высокую производительность операций чтения данных (например, таблица будет использоваться главным образом для анализа содержащихся в ней данных или для генерации динамических веб-страниц), предпочтительным типом такой таблицы является MyISAM. Если же данные в таблице будут редактироваться множеством пользователей (это часто происходит в корпоративных базах данных), желательно присвоить таблице тип InnoDB. В разделе 6.4 «Проблемы, связанные с блокировками» мы рассмотрим особенности блокировки для каждого типа таблицы.
   Минимизация объема данных позволяет ускорить чтение данных с диска и снизить загруженность оперативной памяти. Перечислим несколько способов минимизации объема данных.
   • Хранение мультимедийных данных (изображений, аудио– и видеозаписей) не в базе данных, а в файловой системе.
   Чтение большого файла требует в несколько раз меньше ресурсов, чем получение тех же данных с помощью запроса из столбца с типом BLOB. Поэтому рекомендуется хранить в базе данных не сами мультимедийные файлы, а только пути к ним.
   • Подбор типов столбцов с наименьшим размером.
   Например, если значения в целочисленном столбце не могут превышать 10 000, целесообразно объявить его как SMALLINT, а не INT или MEDIUMINT. Определить диапазон возможных значений столбца вы можете с помощью запроса

   SELECT <Список столбцов> FROM <Имя таблицы>
   PROCEDURE ANALYSE();

   Выполнив этот запрос после загрузки данных в таблицу, вы узнаете максимальное значение числового столбца, максимальную длину символьного столбца, количество неопределенных значений в столбце и многое другое.
   • Указание свойства NOT NULL для всех столбцов, для которых это возможно.
   Если в столбце не предполагается использовать неопределенные значения, задание свойства NOT NULL позволит уменьшить длину каждого значения на 1 бит.
   Исключением из правила минимизации объема данных является использование статического формата таблиц. Другими словами, если в таблице с типом MyISAM отсутствуют символьные столбцы, допускающие значения переменной длины (такие как VARCHAR, TEXT, BLOB и т. п.), то такая таблица по умолчанию сохраняется в статическом формате; если же в таблице есть столбцы с переменной длиной значений, то по умолчанию применяется динамический формат. Как правило, динамические таблицы занимают значительно меньше места, чем статические, однако статические таблицы работают намного быстрее.
...
   Примечание
   Вы также можете явно указать формат таблицы MyISAM с помощью опционального параметра ROW_FORMAT. Описание этого параметра приведено в конце данного раздела.
   Если вы все же используете динамические таблицы MyISAM, необходимо учитывать, что изменение данных в такой таблице может привести к ее фрагментации. Так, если значение в символьном столбце заменяется более длинным, то строка таблицы разделяется на две (или более) части, которые хранятся отдельно друг от друга. Фрагментация сказывается на скорости доступа к данным, поэтому динамическую таблицу рекомендуется время от времени (в зависимости от интенсивности изменений) дефрагментировать с помощью команды

   OPTIMIZE TABLE <Имя таблицы>;

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

   INDEX (date,product_id,customer_id)

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

   SELECT * FROM Orders WHERE date=CURDATE();
   SELECT * FROM Orders
   WHERE date=CURDATE() AND product_id=3;

   но будет бесполезен при выполнении запросов

   SELECT * FROM Orders WHERE product_id=3;
   SELECT * FROM Orders
   WHERE product_id=3 AND customer_id=533;

   • Более короткие индексы работают быстрее. Поэтому в качестве первичного ключа таблицы целесообразно использовать целочисленный столбец с наименьшим размером. При создании индекса для символьного столбца полезно ограничить длину индекса, включив в него только начальные подстроки значений (см. пункт «Ключевые столбцы и индексы»); количество индексируемых символов желательно подобрать так, чтобы минимизировать количество строк с одинаковой начальной подстрокой.
   Наконец, для повышения производительности таблиц вы можете использовать следующие опциональные свойства таблицы:
   • AVG_ROW_LENGTH <Размер в байтах>, MAX ROWS <Количество строк>.
   С помощью этих свойств вы можете задать, соответственно, предполагаемую среднюю длину строки таблицы и предполагаемое максимальное количество строк в таблице. Эти параметры полезно указать для больших динамических таблиц MyISAM: они помогут программе MySQL определить размер таблицы, а следовательно, выбрать оптимальную длину индексов.
   • DELAY_KEY_WRITE 1.
   Задание этого параметра для таблиц MyISAM включает режим отложенной записи на диск буфера индексов. Этот режим позволяет ускорить обновление индексов при добавлении и изменении записей благодаря более редкому обращению к диску.
   • PACK_KEYS <0, 1 или DEFAULT>.
   Данный параметр для таблиц MyISAM определяет режим сжатия индексов. Значение 1 указывает, что сжатие будет использоваться как для символьных, так и для числовых индексов. Это ускоряет поиск по таблице, но замедляет обновление индексов. Сжатие числовых индексов дает наибольший эффект в случае большого количества повторяющихся чисел. Значение DEFAULT указывает, что уплотняться будут только символьные индексы, а значение 0 полностью отключает сжатие.
   • ROW_FORMAT <Формат>
   Данный параметр определяет формат таблицы. Таблица с типом InnoDB может иметь формат COMPACT или REDUNDANT. Формат COMPACT используется по умолчанию и является оптимальным. Для таблицы с типом MyISAM вы можете указать значение FIXED (статический формат) или DYNAMIC (динамический формат). Обратите внимание, что статический формат нельзя установить для таблицы, содержащей столбцы с типом TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB и LONGTEXT: если вы укажете для такой таблицы статический формат, он автоматически изменится на динамический.
...
   Примечание
   Проверить, какие форматы были фактически присвоены таблицам, вы можете с помощью команды SHOW TABLE STATUS; Эта команда выводит информацию обо всех таблицах в текущей базе данных. В столбце Row_format вы увидите текущий формат таблицы.
   Если вы приняли все необходимые меры для улучшения структуры таблиц, но запросы все равно выполняются медленно, попытаемся сделать более эффективными сами запросы.

   6.2. Оптимизация запросов

   Основным способом повышения производительности запросов являются индексы. Определить, действительно ли созданные вами индексы используются запросом, позволяет команда

   EXPLAIN <Текст запроса>;

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

   EXPLAIN SELECT name,address,product_id,qty
   FROM Customers, Orders
   WHERE Customers.id=customer_id AND date='20007-12-12';

   выводит результат, представленный в табл. 6.1.
Таблица 6.1. Результат выполнения команды EXPLAIN
   Столбец table (таблица) содержит имя обрабатываемой таблицы, а столбец select_type (тип запроса) указывает место операции в структуре запроса:
   • SIMPLE – простой запрос без вложенных запросов и UNION;
   • PRIMARY – первый запрос в UNION или внешний запрос, имеющий вложенные запросы;
   • UNION – второй и последующие запросы в объединении UNION;
   • DEPENDENT UNION – второй и последующие вложенные запросы в объединении UNION, связанные с внешним запросом (о связанных подзапросах вы узнали в подразделе «Операторы сравнения с результатами вложенного запроса»);
   • UNION RESULT – операция объединения результатов запросов;
   • SUBQUERY – вложенный запрос;
   • DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY – вложенный запрос, связанный с внешним запросом;
   • DERIVED – запрос, генерирующий промежуточный результат (такой запрос следует после ключевого слова FROM).
   В столбце rows (строки) содержится оценка количества строк таблицы, которое потребуется просмотреть для данной операции. Если ваш запрос имеет сложную структуру, значения в столбце rows помогут вам выявить его «узкие места».
   Столбец possible_keys (возможные индексы) показывает, какие индексы из числа существующих в таблице программа MySQL могла бы использовать при выполнении запроса. Если этот столбец содержит значение NULL, а вы считаете необходимым ускорить операцию, добавьте в таблицу подходящие индексы (рекомендации по созданию индексов были изложены в предыдущем разделе).
   Возможна ситуация, когда нужный индекс существует, но программа MySQL не применяет его, считая по каким-либо причинам, что просматривать таблицу полностью будет эффективнее. Индекс, фактически используемый при выполнении запроса, отображается в столбце key (индекс); если данный столбец содержит значение NULL, значит, программа MySQL не выбрала ни один из доступных индексов. Если вы, тем не менее, считаете, что индекс должен использоваться, выполните следующие действия:
   1. Обновите статистику распределения индексов в таблице с помощью команды

   ANALYZE TABLE <Имя таблицы>;

   2. Повторно выполните команду EXPLAIN. Если и после обновления статистики индекс не начал использоваться, добавьте в текст запроса после имени таблицы параметр FORCE INDEX (<Имя индекса>). Сравните время выполнения запроса с параметром FORCE INDEX и без него и выберите оптимальный вариант.
   Помимо введения индексов, существуют и другие способы ускорения запросов. Вот наиболее простые из них.
   • Исключите получение лишней информации из базы данных. Результат запроса должен содержать только те данные, которые действительно необходимы. Например, если вы отображаете на веб-странице не более 20 товаров, не нужно получать из таблицы Products (Товары) все данные. Вместо этого используйте запросы вида

   SELECT <Список столбцов> FROM <Список таблиц>
   [WHERE <Условие отбора>]
   
   LIMIT <Количество строк> OFFSET <Сдвиг>;

   Так, для получения первой «порции» из 20 товаров выполните запрос

   SELECT * FROM Products LIMIT 20 OFFSET 0;

   Следующих 20 товаров —

   SELECT * FROM Products LIMIT 20 OFFSET 20;

   Затем

   SELECT * FROM Products LIMIT 20 OFFSET 40;

   и т. д.
   • Максимально упростите систему привилегий доступа. Чем сложнее система привилегий, тем больше времени занимает проверка прав доступа при выполнении запросов (а также других SQL-команд). Хорошим решением является разграничение доступа на уровне баз данных и отказ от присвоения привилегий доступа к отдельным таблицам и столбцам. В этом случае контроль действий пользователей не требует обращения к таблицам tables_priv и columns_ priv (см. подраздел «Просмотр привилегий»).
   • Если запрос содержит выражение, проверьте, не является ли вычисление этого выражения причиной замедления запроса. Для этого выполните команду

   SELECT BENCHMARK(<Количество повторений>,<Выражение>);

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

   SELECT BENCHMARK(10000000,SIN(1));

   При использовании процессора с тактовой частотой 1,6 ГГц выполнение этой команды займет приблизительно 1 с. Таким образом, программа MySQL способна производить около 10 млн вычислений синуса в секунду, а значит, функция SIN() не оказывает существенного влияния на скорость запроса. Итак, мы рассмотрели способы оптимизации таблиц и запросов. В следующем разделе вы узнаете о том, как увеличить быстродействие сервера путем настройки системных переменных.
Чтение онлайн



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 [30] 31

Навигация по сайту


Читательские рекомендации

Информация