Оптимизация инструкций 473

Итак, появление индекса привело к сокращению диапазона сканирования в 15 раз, но инструкция все ее вынуждена просматривать 45 миллионов записей. Осталось еще учесть столбцы word в обеих таблицах. Разберемся сначала с таблицей word.

В процессе объединения таблиц программа MySQL использует не более одного индекса от каждой таблицы. Если появляются варианты, то выбирается индекс с более узким диапазоном. нами индекс уже охватывает столбец word, к тому же, как видно из листинга 26.5, диапазон поиска существенно сузился.

Теперь перейдем к таблице dictionary. Пока чт укция SELECT вынуждена сканировать ее целиком. Добавление индекса к столбцу word позволит программе сразу же находить нужную запись (листинг

ALTER TABLE dictionary ADD INDEX (word)

Эффект этого действия продемонстрирован в листинге 26.7. Как видите, количество просматриваемых записей таблицы dictionary сократилось до одной!

EXPLAIN SELECT -> FROM word LEFT JOIN dictionary

-> ON word.word=dictionary.word

Оптимизация инструкций

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

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

CREATE TABLE address (

ID int (11) NOT NULL AUTOJNCREMENT, Naine Prefix CHAR(16) default NULL, Name First CHAR (32) default NULL, Name Middle CHAR (32) default NULL, Name Last CHAR (64) NOT NULL default Name Suffix CHAR (16) default NULL,

Company CHAR (64) Streetl CHAR(64) Street! CHAR(64) Streets CHAR(64)

default NULL, default NULL,

default NULL,

default NULL, City CHAR (64) NOT NULL default StateProv CHAR(64) NOT NULL default , PostalCode CHAR(16) NOT NULL default CountryCode CHAR(2) default NULL, Phone 1 CHAR(32) default NULL, Phone2 CHAR(32) default NULL, Fax CHAR(32) default NULL, Email CHAR(64) NOT NULL default PRIMARY KEY (ID)

INSERT INTO address {Name First, Name Last) VALUES (Leon, Atkinson);

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

Если есть несколько записей, предназначенных для вставки в таблицу, воспользуйтесь многострочной инструкцией INSERT. Еще быстрее работает инструкция LOAD DATA INFILE. Для полной очистки таблицы лучше вызывать инструкцию TRUNCATE TABLE, а не DELETE. этрм случае программа MySQL удалит иснова создаст табличный файл, вместо того чтобы удалять записи одна за другой.

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

Обслуживание таблиц 475

Обслуживание таблиц

Можно ускорить выполнение запросов, если хранить таблицы и индексы в упорядоченном виде. Инструкции MIZE TABLE улучшает таблицу тремя способами. Во-первых, она устраняет пустые промежутки, оставшиеся после удаления записей. Для таблиц это означает возможность одновременного выполнения инструкций

INSERT и SELECT. Во-вторых, она соединяет распределенные фрагменты таблиц с динамическими записями. И наконец, она сортирует индексы.

Инструкция ALTER TABLE позволяет отсортировать записи таблицы. Это тоже способствует ускорению некоторых запросов, хотя и не устраняет потребность в индексах.

Если таблица меняется редко, а дисковое пространство ограничено, имеет смысл сжать таблицу с помощью утилит траск (см. главу 14, Утилиты командной строки ). После этого таблица будет доступна только для чтения. Ее индексы необходимо перестроить, вызвав утилиту Данная методика позволяет уменьшить размер таблицы на 40-70%, в зависимости от формата ее содержимого. В листинге 26.9 показан процесс сжатия таблицы, содержащей названия штатов США.

# myisampack.exe state Compressing state.MYD: (50 records)

- Calculating statistics

- Compressing file 32.42%

Remember to run myisamchk -rq on compressed tables

# myisamchk -rq state

- check key delete-chain

- check record delete-chain

- recovering (with sort) MylSAM-table state.MYI

Data records: 50

- Fixing index 1

- Fixing index 2

Настройка конфигурации сервера

Когда речь заходит об объеме оперативной памяти сервера, совет всегда один: чем больше- тем лучше. Увеличение объема памяти способствует ускорению работы программы MySQL, так как в оперативной памяти она хранит свои временные таблицы и буферы записей. В подкаталоге support-files дистрибутива содержатся образцы конфигурационных файлов с различными вариантами настроек, касающихся использования памяти. Выберите тот вариант, который соответствует исходным параметрам сервера. Поработав с сервером какое-то время, можно будет оценить, какие из настроек требуют корректировки.

В листинге 26.10 показана конфигурация сервера, располагающего как минимум 1 Гбайт ОЗУ, четырьмя жесткими дисками и четырьмя центральными процессорами. Обратите внимание на важность индексного буфера. В данной конфигурации ся, что сервер хранит табличные данные на первом диске, а временные файлы - на втором. Таблиц oDB находятся на третьем диске, а жрнал oDB - на четвертом.