Как определить длину индекса префикса?

MySQL

Зачем нужен префиксный индекс

проблема

Сталкивались ли мы с этой проблемой при индексации поля или нескольких полей в таблице?

Specified key 'uniq_code' was too long; max key length is 767 bytes.

Структура таблицы следующая:

create table `t_account`(
  `id` BIGINT(20) UNSIGNED NOT NULL auto_increment COMMENT '自增ID',
  `date` varchar(50) NOT NULL DEFAULT '' COMMENT '日期',
  `nick_name` varchar(50) NOT NULL DEFAULT '' COMMENT '昵称',
  `account` varchar(50) NOT NULL DEFAULT '' COMMENT '账号',
  `city` varchar(100) NOT NULL DEFAULT '' COMMENT '城市',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code` (`nick_name`,`account`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Test';

причина

В MySQL 5.6 значение по умолчанию innodb_large_prefix=0 ограничивает длину индекса с одним столбцом до 767 байт.

В MySQL 5.7 значение по умолчанию innodb_large_prefix=1 снимает ограничение длины в 767 байт, но максимальная длина индекса с одним столбцом не может превышать 3072 байта.

Насчет того, почему именно 767 байт, это зависит от конкретного движка хранилища, я нашел официальный документ и не сказал, почему. https://dev.mysql.com/doc/refman/8.0/en/create-index.html

varchar(n)占用几个字节跟字符集有关系：
字符类型若为gbk，每个字符占用2个字节， 
字符类型若为utf8，每个字符最多占用3个字节,
字符类型若为utf8mb4，每个字符最多占用4个字节

Я установил здесь кодировку utf8mb4, один символ занимает 4 байта, а созданный мной индекс составляет 50+50+100=200 символов, всего 800 байт, поэтому он превышает длину.

Поэтому мы часто видим, что поля имеют длину varchar(255).В кодировке utf8 это максимальная длина 767 байт, но она не обязательно должна быть установлена ​​в varchar(255), или нам нужно установить каждую в соответствии с дело.Слишком большая длина поля не способствует нашему созданию общего индекса.

Решение

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

  2. Определив первые n символов поля в качестве индекса, можно получить конкретное значение путем измерения длины данных в реальном бизнесе.

    UNIQUE KEY `uniq_code` (`nick_name`(20),`account`(20),`city`(20))

    Указывает, что в качестве уникального индекса берутся первые 20 символов трех полей, так что длина не будет превышать, об этом и идет речь前缀索引

  3. Изменить максимальную длину одного индекса

    修改索引限制长度需要在my.ini配置文件中添加以下内容，并重启：
#修改单列索引字节长度为767的限制，单列索引的长度变为3072
innodb_large_prefix=1
但是开启该参数后还需要开启表的动态存储或压缩：
系统变量innodb_file_format为Barracuda
ROW_FORMAT为DYNAMIC或COMPRESSED

Как определить длину префиксного индекса

Выше мы упоминали, что можно前缀索引чтобы решить проблему, когда длина индекса превышает предел, но как мы определяем, насколько длинный префикс поля индекса является подходящим?

Здесь мы можем определить селективность индекса префикса путем расчета селективности, метод расчета следующий

Полная селективность столбца:

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

Селективность префикса определенной длины:

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

Когда селективность префикса ближе к селективности всего столбца, эффект индекса лучше.

Преимущества и недостатки префиксной индексации

  • Небольшие габариты и быстро
  • Нельзя использовать индекс префикса для ORDER BY и GROUP BY
  • Невозможно выполнить сканирование обложки с индексом префикса
  • Возможность увеличить количество строк сканирования

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

Давайте снова поговорим о создании совместного индекса

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

В следующем примере оценивается установление совместного индекса customer_id и staff_id и, наконец, выбор комбинации (customer_id, staff_id).

# staff_id_selectivity: 0.0001
# customer_id_selectivity: 0.0373
# COUNT(*): 16049 
# 通过结果发现，customer_id 的选择性更高，所以应该选择 customer_id 作为联合索引的第一列
SELECT 
 COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
 COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
 COUNT(*)
FROM payment

так

Когда селективность индекса ближе к селективности полной колонки, эффект индекса лучше.

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

Ссылаться на

Метод определения длины индекса префикса Mysql

Некоторые ограничения длины индекса mysql - yuyue2014 - Blog Park

Тип MySQL и длина данных — самородки

Следите, делитесь, смотрите! ! ! Ваша поддержка - самая большая мотивация для моего творчества.

В этой статье используетсяmdniceнабор текста

Категории