Использование кэшей индексов

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

Кэш индексов

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

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

Размер кэша

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

Учтите, что сейчас будут обсуждаться размеры кэша MyISAM, другие типы таблиц (например, InnoDB) используют другие кэши.

Размер кэша MyISAM определяется значением глобальной переменной key_buffer_size. Изменение величины этой переменной приводит к изменению размера кэша:

sakila@sqlinfo.ru test> SET GLOBAL key_buffer_size = 10 * 1024 * 1024;
Query OK, 0 rows affected (0.10 sec)

Измененный такой командой кэш будет иметь размер 10 мегабайт, но он будет таким до перезагрузки сервера. Если Вы хотите, чтобы он оставался таким и после перезагрузки, Вам нужно указать его размер в файле настройки. При этом Вы можете пользоваться модификаторами К, М и G для обозначения килобайт, мегабайт и гигабайт соответственно:

[mysqld]
key_buffer_size = 10M

Именованные кэши

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

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

sakila@sqlinfo.ru test> SET GLOBAL mycache.key_buffer_size = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

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

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

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

Технология горячего кэша

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

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

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

Для того, чтобы установить соотношение частей кэша в процентах, используется параметр key_cache_division_limit. По умолчанию, его значение равно 100 (т.е. весь кэш является «горячим»). Разумеется, этот параметр кэша применим и к именованным кэшам:

sakila@sqlinfo.ru test> SET GLOBAL mycache.key_cache_division_limit = 50;
Query OK, 0 rows affected (0.01 sec)

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.