Оптимизация в MySQL, часть 1: оптимизация индексов

Данная статья является 1 из 3 частей цикла «Оптимизация MySQL»:

  1. Оптимизация индексов MySQL;
  2. Оптимизация конфигурации MySQL;
  3. Оптимизация проблемных моментов MySQL.

mysql

MySQL в сочетании с РНР является одним из наиболее часто используемых движков баз данных. Направить усилия на то, чтобы ваши базы данных MySQL работали наилучшим образом, это то, что вы должны сделать в первую очередь, когда веб-приложение начинает расти.

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

Мы будем использовать в основном инструменты из Percona Toolkit. В этой статье я расскажу об оптимизации индексов.

Общая информация об индексах

Если до этого времени вы еще не работали с индексами MySQL, то, вероятно, у вас очень маленькая или непроизводительная база данных. Индексы MySQL можно сравнить с содержанием книги.

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

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

Таким образом, обновление и добавление запросов будет осуществляться медленнее, и для этого потребуется немного больше места на диске.

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

Для таблиц, которые содержат всего несколько строк, реальная отдача от индексации будет незаметна. Можете представить, что поиск на 5 страницах осуществляется намного медленнее, если сначала выполнять индексацию, потом получать номер страницы, а затем уже открывать эту конкретную страницу.

В данной же статье мы продолжим рассматривать, как мы можем реально улучшить текущие индексы.

Поиск дублированных индексов

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

В Percona Toolkit есть инструмент под названием pt-duplicate-key-checker. Он позволяет проанализировать базу данных и получить отчет о том, какие таблицы содержат повторяющиеся ключи.

Представьте, что у нас есть следующая таблица:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `firstname` varchar(255) NOT NULL, 
  `lastname` varchar(255) NOT NULL, 
  PRIMARY KEY (`id`), KEY `id_firstname` (`id`,`firstname`), 
  KEY `firstname` (`firstname`), 
  KEY `firstname_2` (`firstname`), 
  KEY `firstname_lastname_id` (`firstname`,`lastname`,`id`) 
) ENGINE=InnoDB

В этой таблице есть 3 дубликата ключей. Давайте запустим pt-duplicate-key-checker , чтобы увидеть, какие ключи фактически продублированы:

# ######################################################################## 
# user # 
######################################################################## 
# firstname_2 is a left-prefix of firstname_lastname_id 
# Key definitions: 
# KEY `firstname_2` (`firstname`), 
# KEY `firstname_lastname_id` (`firstname`,`lastname`,`id`) 
# Column types: 
# `firstname` varchar(255) not null 
# `lastname` varchar(255) not null 
# # `id` int(11) not null auto_increment 
# To remove this duplicate index, execute: 
ALTER TABLE `user` DROP INDEX `firstname_2`; 

# firstname is a left-prefix of firstname_lastname_id 
# Key definitions: 
# KEY `firstname` (`firstname`), 
# KEY `firstname_lastname_id` (`firstname`,`lastname`,`id`) 
# Column types: 
# `firstname` varchar(255) not null 
# `lastname` varchar(255) not null 
# `id` int(11) not null auto_increment 
# To remove this duplicate index, execute: 
ALTER TABLE `user` DROP INDEX `firstname`; 

# Key firstname_lastname_id ends with a prefix of the clustered index 
# Key definitions: 
# KEY `firstname_lastname_id` (`firstname`,`lastname`,`id`) 
# PRIMARY KEY (`id`), 
# Column types: 
# `firstname` varchar(255) not null 
# `lastname` varchar(255) not null # `id` int(11) not null auto_increment 
# To shorten this duplicate clustered index, execute: 
ALTER TABLE `user` DROP INDEX `firstname_lastname_id`, ADD INDEX `firstname_lastname_id` (`firstname`,`lastname`); 

# ######################################################################## 
# Summary of indexes 
# ######################################################################## 

# Size Duplicate Indexes 1032 
# Total Duplicate Indexes 3 
# Total Indexes 5

Хотя индекс firstname является дубликатом firstname_2, оба они являются дубликатами ключа firstname_lastname_id. Почему это происходит? Потому, что firstname представляет собой так называемый левый префикс firstname_lastname_id.

При создании индекса для колонки 3 (A, B , C), вы изначально имеете 3 накладки индексов:

  • A;
  • A, B;
  • A, B, C.

Таким образом, так как firstname является первым столбцом индекса firstname_lastname_id, не существует такого эффекта, с помощью которого можно было бы создать отдельный индекс.

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

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

Поиск неиспользуемых индексов

Конечно, вы хотите по возможности избегать повторяющихся ключей, а как насчет неиспользованных ключей? Для этого в Percona Toolkit существует утилита pt-index-usage.

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

Давайте запустим утилиту pt-index-usage /var/log/mysql/mysql-slow.log и посмотрим, какие данные она нам выдаст:

ALTER TABLE `user` DROP KEY `lastname_index`; -- type:non-unique

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

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

Заключение

Индексы очень важны для оптимизации производительности MySQL. Обеспечить правильную индексацию таблиц так же важно, как использовать сами индексы.

Как вы проверяете, что ваши индексы применяются правильно? Я хотел бы услышать об этом от вас в комментариях.

Перевод статьи «Optimizing MySQL Indexes» был подготовлен дружной командой проекта Сайтостроение от А до Я.

 
Автор: Unknow, Источник
 

Добавить комментарий


Защитный код
Обновить