Оптимизация

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

Незаметно для пользователей программа MySQL оптимизирует предложения WHERE инструкции SELECT. Обычно не нужно заботиться о том, сколько скобок указано в выражении или каков порядок таблиц в объединении. Вместо этого сосредоточьтесь на индексах. Они позволяют ускорить операции выборки данных за счет замедления операций записи. Конечно, индексы занимают дополнительное место на диске, но они незаменимы с точки зрения эффективной организации таблиц.

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

Разрешается создавать индекс, охватывающий несколько столбцов. Программа MySQL может работать с частями индекса, но они должны просматриваться строго слева направо. Например, если индекс включает столбцы имени и фамилии, то при обращении к первому столбцу индекс будет использован, а ко второму — нет (при условии, что перед этим не было обращения к первому столбцу). Это правило применимо и к символам индексируемого столбца, содержащего текстовые данные (тип CHAR, VARCHAR или BLOB ). Когда в предложении WHERE присутствует оператор LIKE, индекс задействуется лишь в том случае, если шаблон сравнения содержит все литеральные символы слева, а метасимволы — справа. Так, шаблон ‘abc %’ разрешает использование индекса, а шаблон ‘abc % xyz’ — нет.

В листинге 10.2 приведены инструкции, создающие две таблицы. Таблица word будет содержать 14346 записей, а таблица dictionary — 104237. В первую таблицу слова заносятся пользователями, а вторая таблица содержит список известных программе слов. Пользователи часто вводят несуществующие слова. Запрос, анализируемый в листинге 10.3, предназначен для выяснения количества распознанных слов. Условию отбора соответствуют 911 записей.

mysql> EXPLAIN SELECT word.word, dictionary.word
-> FROM word LEFT JOIN dictionary
-> ON word.word=dictionary.word
-> WHERE word.class = '_VERBO' \G
*************************** 1.row ***************************
table: word
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14346
Extra: where used
table: dictionary
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL

В запросе участвуют три столбца: столбцы word и class таблицы word и столбец word таблицы dictionary. Известно, что тестовому условию отбора соответствуют 911 записей таблицы word, поэтому наша задача состоит в том, чтобы сократить диапазон сканирования первой таблицы до соответствующего уровня. Для этого необходимо создать индекс по столбцу class. Сначала я планировал включить в индекс только упомянутый столбец, но потом подумал о других запросах, которые приходится направлять таким таблицам. Я, например, часто создаю отчет, в который включается все содержимое таблицы, отсортированное сначала по классам, а затем — по словам. Разумнее будет включить в индекс сразу два столбца (листинг 10.4).

ALTER TABLE word
ADD INDEX (class, word)

Теперь инструкция EXPLAIN выдает другие результаты (листинг 10.5). В поле Key len сообщается о том, что индекс охватывает 16 символов столбца class. По оценке программы MySQL, ей придется просмотреть 1517 записей, хотя мы знаем, что их всего 911

Mysql > EXPLAIN SELECT word. word, dictionary. Word
->	FROM word LEFT JOIN dictionary
->	ON word. Word = dictionary. Word
->	WHERE word. Class = "_VERBO" \G
	
	   table: word
	    type: ref
possible keys: class 
          key: class
      key len: 16
     ref: const
    rows: 1517
   Extra: where used; Using index 

   table: dictionary
    type: ALL
possible keys: NULL
     key: NULL
 key len: NULL
     ref: NULL
    rows: 104237
        Extra:
2 rows in set (0.00 sec)

Итак, появление индекса привело к сокращению диапазона сканирования в 15 раз, но инструкция все же вынуждена просматривать 45 миллионов записей. Осталось еще учесть столбцы word в обеих таблицах. Разберемся сначала с таблицей word. В процессе объединения таблиц программа MySQL использует не более одного индекса от каждой таблицы. Если появляются варианты, то выбирается индекс с более узким диапазоном. Созданный нами индекс уже охватывает столбец word, к тому же, как видно из листинга 10.5, диапазон поиска существенно сузился. Теперь перейдем к таблице dictionary. Пока что инструкция SELECT вынуждена сканировать ее целиком. Добавление индекса к столбцу word позволит программе сразу же находить нужную запись (листинг 10.6).

ALTER TABLE dictionary
ADD INDEX (word)

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

Mysql > EXPLAIN SELECT word. word, dictionary. Word
->	FROM word LEFT JOIN dictionary
->	ON word. Word = dictionary. Word
->	WHERE word. Class = "_VERBO" \G
*********************** 1. row ********************	
	   table: word
	    type: ref
possible keys: class 
          key: class
      key len: 16
     ref: const
    rows: 1517
   Extra: where used; Using index 

   table: dictionary
    type: ref
possible keys: word
     key: word
 key len: 64
     ref: word. word
    rows: 1
        Extra: Using index
2 rows in set (0.26 sec)

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

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

CREATE TABLE address {
	ID int (11) NOT NULL AUTO INCREMENT,
	Name 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) default NULL,
Street1 CHAR (64) default NULL,
Street2 CHAR (64) default NULL,
Street3 CHAR (64) default NULL,
City CHAR NOT NULL default ‘’,
State Prov CHAR (64) NOT NULL default,
Postal Code CHAR (16) NOT NULL default,
Country Code CHAR (2) default NULL,
Phone1 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 удалит и снова создаст табличный файл, вместо того чтобы удалять записи одна за другой.

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