|
Задавайте вопросы, мы ответим
Вы не зашли.
Доброго времени суток уважаемые!
Гипотетическая ситуация.
База: MySQL/MariaDB/Percona.
Есть таблица значений, состоящая из: id и char(250) или id и char(65535). Во втором поле (char) содержатся значения смешанного типа (строки, числа, etc), по которым производится выборка, от простого WHERE char_field = 100; до более сложных, вроде BETWEEN, LIKE '%abc%' и так далее, в т.ч. множественные AND/OR.
Ориентировочное количество записей в таблице - 1 млн. (возможно увеличение до 5-10 млн.)
Для меня не до конца понятны следующие моменты:
1. Максимальная длина индекса, можно ли проиндексировать поле VARCHAR(65535) полностью?
2. Учитывается ли при расчёте максимальной длины индекса кодировка БД (таблицы)
3. Длина каждого отдельно взятого индекса будет равняться максимальной длине поля или фактической (подозреваю, что максимальной)
4. Даст ли какой-то прирост производительности смена кодировки таблиц с utf8_general_ci на какую-то однобайтовую кодировку, вроде cp1251 или koi8?
Тип таблиц - InnoDB.
Буду крайне признателен за любые комментарии по теме. Спасибо.
Неактивен
1. нет
http://dev.mysql.com/doc/refman/5.7/en/create-index.html написал:
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB storage engine does not support prefixes
2.
там же написал:
Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.
Неактивен
По пунтку 4 - заменой кодировки Вы сможете проиндексировать более длинную части колонки. UTF8 в MySQL всегда занимает 3 байта на символ.
Неактивен
vasya, спасибо!
Я бы хотел уточнить некоторые моменты. Как я понимаю, максимальная длина индекса в InnoDB составляет 767 байт на единицу (если опция innodb_large_prefix выключена), при этом, текст в кодировке utf8_general_ci занимает 3 байта/символ. Соответственно, мы можем максимально проиндексировать после в 255 символов?
Что будет, если мне нужно проиндексировать поля по 65000 байт, или поля заранее не известной длины, но укладывающиеся в 16Mb (например, LONGTEXT поле?).
Если максимально сократить задачу до логического конца, то звучать вопрос будет примерно так: как максимально ускорить поиск и сложную выборку (LIKE, <=>, BETWEEN и пр. условия сравнения и выборки) по полям переменной длины, содержащие смешанные значения длиной от 1 цифры/символа, до нескольких Мб текста.
Благодарю за внимание.
Неактивен
rgbeast, спасибо! Я как раз так и подумал... А производительности это не добавит? Логически ведь индекс тогда будет занимать 1 байт на символ, вместо 3-х в памяти и на жестком диске...
Неактивен
Вы можете максимально проиндексировать первые 767 байт от значения. Поэтому реальная длина записи не имеет значения - индекс будет фиксированной, заданной вами, длины.
Неактивен
rgbeast, правильно ли я понимаю, что как-то ощутимо ускорить процесс индексации подобного рода полей невозможно, просто проиндексируются первые N-символов (в зависимости от кодировки), а если значение занимает, скажем, 1Мб текста, и соответственно большая его часть выходит за пределы индекса, то? Как поведёт себя MySQL? Он начнёт сканировать все поля в поисках подходящих под условие значений, или сначала пройдёт по индексированным, а потом определит длину полей которые выходят за пределы индекса и просканирует их отдельно?
Суть в том, что 95% значений будут от 1 до 30 символов, и примерно 5% будут иметь вариативную длину.
Неактивен
Если данные занимают меньше места в памяти и на диске, то это влияет на производительность. Однако, нужно вернуться к исходной задаче. Если ищете LIKE '%слово%', то такая операция не будет использовать индекс ни при каких условиях (альтернативный подход - FULLTEXT индексы). Чтобы понять почему, возьмите телефонный справочник (аналог индекса) и найдите в нем все фамилии, содержащие '%бекер%'. Только полный перебор поможет решить такую задачу.
Для запросов типа LIKE 'ворона%' или BETWEEN 'Брянск' AND 'Новосибирск' будет вполне достаточно первой части индекса, независимо от длины записи.
Неактивен
rgbeast я Вас понял, спасибо, это действительно очень логично. Не могли бы Вы просветить меня в двух словах о полнотекстовых индексах? На моей памяти я никогда их не использовал...
Интересует вот что:
- добавит ли полнотекстовый индекс скорости выборки по выражениям типа WHERE LIKE '%abc%'?
- какой может возникнуть негативный эффект, в результате использования полнотекстовых индексов? База будет больше есть оперативки, диска, медленнее работать?
Заранее благодарю.
P.S. Я поискал информацию касательно полнотекстовых индексов в интернете, разумеется, перед тем как кого-то дёргать, но информация сильно разрежена и в основном рассматривается полнотекстовый поиск match/against, никто не рассматривает примеры типа WHERE LIKE '%abc%' и толк от этих индексов при таких условиях.
Отредактированно E.Wolf (23.04.2016 14:50:01)
Неактивен
Я тут вычитал, что match/against работают быстрее WHERE LIKE '%abc%', на полях с полнотекстовым индексом, но это не работает в таблицах InnoDB, при этом указано, что "проблема в том, что нам нужно искать по нескольким полям", в моём конкретном случае, нужно искать по одному и только одному полю (одной колонке) и исключительно в InnoDB. Подскажите пожалуйста, возможно ли что-то подобное при условии поиска по одному полю, в том смысле, даст ли это какой-то ощутимый прирост скорости при замене условия WHERE LIKE '%abc%' на WHERE MATCH(`column1`) AGAINST('*abc*' IN BOOLEAN MODE)?
Неактивен
E.Wolf, вы уже на многое ответили. Полнотекстовый индекс не используется для LIKE. Он работает только для MATCH AGAINST. В Innodb такие индексы работают начиная с MySQL 5.6.
Неактивен
rgbeast, спасибо большое! В Innodb такие индексы работают начиная с MySQL 5.6. - вот это вообще очень ценная информация, мне нигде не попадался на глаза сей важнейший факт. Последнее, что хотел бы уточнить, начиная с версии 5.6 эти индексы работают по одному полю, или можно выставлять на несколько полей сразу?
P.S. Очень не хватает кнопочки "спасибо" или кнопочки "Понравился ответ? Пожертвовать чирик/полтинник", или обоих кнопочек сразу 
Неактивен
E.Wolf, спасибо за добрый отзыв!
Индексы FULLTEXT могут работать на несколько полей.
Неактивен
rgbeast, ещё раз примного благодарен!
Буквально только что нашел 3 или 4 упоминания полнотекстовый поиск работает только на MyISAM, и эта информация, как мы выяснили, уже устарела... Вот так и учимся, "пока жареный не клюнет"...
Думаю вопрос можно считать решенным (тему раскрытой), и где-нибудь жирным шрифтом написать "InnoDB теперь поддерживают полнотекстовые индексы!", хотя бы для таких ископаемых как я, которые помнят MySQL с версии 3.х а то и более ранних...
Неактивен
