Статьи

Небольшие улучшения MySQL 5.6: обнаружение повторяющихся индексов

Август 19, 2018
Этот пост принадлежит  Хайме Креспо из MySQL Performance Blog.

MySQL 5.6Здесь , в блоге MySQL Performance, мы уже обсуждали несколько новых функций , которые MySQL 5.6 принес: GTID на основе репликации , InnoDB Полный текст , Memcached интеграции , более полная схема производительности , онлайн DDL и несколько других InnoDB и запросов оптимизатор усовершенствования . Тем не менее, я планирую сосредоточиться на серии сообщений о небольших, но удобных улучшениях — изменениях и исправлениях ошибок — в MySQL 5.6, которые могут сделать нашу жизнь проще и прошли почти незамеченными большинством ( не всеми ) администраторами баз данных.

Обнаружение повторяющихся индексов

Я прокомментировал это на моем последнем вебинаре , но у меня не было времени проанализировать его подробно. Если вы попытаетесь сделать что-то подобное в MySQL 5.5, у вас все получится без ошибок и предупреждений:

mysql> ALTER TABLE test ADD INDEX (col2);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE test ADD INDEX (col2);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col2` int(11) DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `col2` (`col2`),
  KEY `col2_2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

В предыдущих версиях MySQL вы можете создавать два индекса с одинаковыми столбцами (в том же порядке), и сервер не будет жаловаться.

Если мы выполним те же предложения в MySQL 5.6, второй ALTER также будет успешным — и будет создан индекс — но мы получим предупреждение (обратите внимание на серьезность, если быть точным):

mysql> ALTER TABLE test ADD INDEX (col2);
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE test ADD INDEX (col2);
Query OK, 0 rows affected, 1 warning (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1831
Message: Duplicate index 'col2_2' defined on the table 'test.test'. This is deprecated and will be disallowed in a future release.
1 row in set (0.00 sec)

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

В отчете более подробно объясняется, что означает «будет запрещено в будущем выпуске». В MySQL 5.7 проверки будут более строгими : в режиме SQL по умолчанию дублирующийся индекс выдаст предупреждение вместо заметки. В строгом режиме он выдаст ошибку, и произойдет сбой второго ALTER, что предотвратит создание дублирующего индекса.

Означает ли это, что такие инструменты, как pt-duplicate-key-checker , не понадобятся для MySQL 5.6? Давайте посмотрим на код, реализующий эту функцию . Предупреждение будет выдано только в том случае, если индекс не был создан автоматически, он не является внешним ключом и имеет точное определение столбца в том же порядке. Другими словами, он проверяет дубликаты ключей, но не избыточные . В чем разница? Давайте посмотрим на пример. Если мы выполним:

mysql> ALTER TABLE test ADD INDEX (col2);
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE test ADD INDEX (col2, col3);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

Мы не получаем предупреждений и ошибок в 5.6, так как индексы разные. Но, как вы знаете, мы можем использовать второй индекс не только для фильтрации по обоим столбцам, но и для фильтрации только по первому. Почему MySQL не может применять такие ограничения? По многим причинам: первая из-за того, что она сломает 99% всех приложений, использующих MySQL, для которых в Percona мы находим избыточные индексы. И, во-вторых, потому что в некоторых случаях нам может понадобиться то, что на первый взгляд кажется избыточным, но фактически это не так, например, если один из двух индексов является уникальным или внешним ключом.

Это вывод нашего инструмента при запуске на одной и той же таблице, правильно идентифицирующий избыточность:

$ pt-duplicate-key-checker --tables test.test
# ########################################################################
# test.test
# ########################################################################
# col2 is a left-prefix of col2_2
# Key definitions:
#   KEY `col2` (`col2`),
#   KEY `col2_2` (`col2`,`col3`)
# Column types:
#         `col2` int(11) default null
#         `col3` varchar(200) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`test` DROP INDEX `col2`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes   5
# Total Duplicate Indexes  1
# Total Indexes            3

Кроме того, pt-duplicate-key-checker обнаружит тонкие избыточности, которые зависят от движка, например избыточные суффиксы для вторичных ключей в InnoDB . Поскольку некоторые из этих избыточностей могут быть необходимы, в зависимости от оптимизатора запросов и версии MySQL, мы всегда рекомендуем вручную проверять оптимизации, предложенные Percona Toolkit. Сервер MySQL, конечно, не может рисковать, чтобы заблокировать напрямую все случаи.

Oracle недавно представила набор утилит MySQL, который включает mysqlindexcheck , похожий на pt-duplicate-key-checker, но он не обнаруживает все случаи. Например:

mysql> alter table test add index redundant (col2, id);
Query OK, 0 rows affected (1.57 sec)
Records: 0  Duplicates: 0  Warnings: 0
$ mysqlindexcheck --server=user:pass@localhost test.test
# Source on localhost: ... connected.
$ pt-duplicate-key-checker --tables test.test
# ########################################################################
# test.test
# ########################################################################
# Key redundant ends with a prefix of the clustered index
# Key definitions:
#   KEY `redundant` (`col2`,`id`)
#   PRIMARY KEY (`id`),
# Column types:
#         `col2` int(11) default null
#         `id` int(11) not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`);
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes   9
# Total Duplicate Indexes  1
# Total Indexes            2

Кстати, если вы хотите больше узнать об этой и других особенностях последнего выпуска MySQL GA, ознакомьтесь с нашими предстоящими сессиями для учебного курса «Переход к 5.6 ″ в Америке ( Остин , Сан-Хосе ) и Европе ( Манчестер , Утрехт ).

34
Share: