Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Посоветуйте оптимальный составной индекс с null-полем / 12 сообщений из 12, страница 1 из 1
13.06.2017, 22:58
    #39471194
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Есть список сессий пользователей, в котором основным идентификатором является MAC-адрес пользователя.
MAC-адрес хранится в БД как BINARY(6).
Сессии могут быть актуальными (действующими) и неактуальными (завершенными), для чего в каждой сессии есть поля START и STOP, определяющие начало и конец действия сессии.
В действующих сессиях MAC-адрес должен быть уникальным. Однако в завершенных сессиях разрешается его неуникальность, там ожидаются сотни и тысячи дубликатов. Значения MAC-адресов случайны, но на гистограмме будут заметно выражены группировки по первым 3-4 байтам.
Я сделал так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE `SESSIONS` (
	`_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	...
	`_START` TIMESTAMP,
	`_STOP` TIMESTAMP,
	...
	`ACTIVE` TINYINT(1) UNSIGNED NULL,
	`MAC` BINARY(6) NOT NULL,
	...
	PRIMARY KEY (`_ID`),
	UNIQUE INDEX `MAC` (`ACTIVE`, `MAC`)
);


У актуальных сессий выставляется ACTIVE=1, у неактуальных выставляется ACTIVE=null.
Задумка в том, что null-значения (ACTIVE=null) в индекс не попадают, соответственно дубликаты разрешаются. А записи с заполненным ACTIVE попадают в уникальный индекс и срабатывает ограничение по неуникальности.
Задумка правильная или я что-то не учитываю?
Если правильная, то какой лучше выбрать индекс, HASH или BTREE? Я так понимаю, что записи с ACTIVE=null вообще не будут попадать в индекс, а для актуальных сессий MAC-адреса должны неплохо балансироваться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2017, 23:11
    #39471196
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Alibek B.Задумка в том, что null-значения (ACTIVE=null) в индекс не попадают , соответственно дубликаты разрешаются. А записи с заполненным ACTIVE попадают в уникальный индекс и срабатывает ограничение по неуникальности.
Задумка правильная или я что-то не учитываю?В MySQL выделенное не работает, насколько я помню.
Нечто близкое есть в Оракле. Там в индекс не попадают записи, в которых NULL во всех полях.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2017, 23:23
    #39471199
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Вот, нашел в доке: https://dev.mysql.com/doc/refman/5.7/en/create-table.html For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

Можно выкрутиться, если сделать уникальный индекс из одного поля ACTIVE, а в это поле писать сам MAC. Т.е. для активных сессий это поле будет временно дублировать поле MAC.

Или, если логику поля менять уже нельзя и если версия MySQL позволяет, то можно сделать Generated Column с такой логикой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2017, 23:55
    #39471205
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
miksoftВ MySQL выделенное не работает, насколько я помню.
Э... Я как-бы проверил.
Добавляю две записи ACTIVE=null,MAC=0x111111111111 — успешно.
Добавляю две записи ACTIVE=1,MAC=0x222222222222 — ошибка добавления второй записи, неуникальность.
Если в первой записи (MAC=0x111111111111) в поле ACTIVE пишу 1, то для одной записи получается, для второй ошибка неуникальности.

У меня MariaDB, может быть в этом отличие от MySQL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 23:11
    #39471799
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Alibek B.Добавляю две записи ACTIVE=null,MAC=0x111111111111 — успешно.А как проверяли, что записи в индекс не попали?
Насколько я понимаю, в индекс записи попали (хотя не могу найти деталей в доке для полной уверенности), но констрейнт на уникальность не сработал.
Кстати, записи вствляли с указанием слова IGNORE или нет?

Впрочем, вроде бы это именно то поведение, которое вам надо?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.06.2017, 08:54
    #39471894
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
miksoftНасколько я понимаю, в индекс записи попали (хотя не могу найти деталей в доке для полной уверенности)Отсутствие ссылки на запись в индексе - это разрушение индекса. Не думаю, что такие вещи требуют отдельного описания... например, не будь в нём ссылок, индекс не мог бы использоваться для запросов с проверкой поля на Null.

miksoftконстрейнт на уникальность не сработал.Почему? всё нормально сработало... просто (Null = Null) IS NULL. А NULL-safe compare в индексах не предусмотрено.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.06.2017, 09:30
    #39471920
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
AkinaПочему? всё нормально сработало... просто (Null = Null) IS NULL. А NULL-safe compare в индексах не предусмотрено.
Да, видимо так.
Но главное, что ограничение работает так, как мне нужно: для заданного ACTIVE уникальность MAC обеспечивает БД, для незаданного ACTIVE разрешаются повторы MAC.
А какой тип индекса будет оптимальным, хеш или сбалансированное дерево?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.06.2017, 10:48
    #39471993
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Alibek B.какой тип индекса будет оптимальным, хеш или сбалансированное дерево?Ооо... холивару захотел? не надо... лучше проверь экспериментально... опять же зависит от того, нафига... В общем случае я бы начал экспериментировать с хэша по комбинации (MAC, active) или наоборот, в зависимости от того, какие типы запросов превалируют или критичны по времени.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.06.2017, 00:49
    #39472737
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
AkinamiksoftНасколько я понимаю, в индекс записи попали (хотя не могу найти деталей в доке для полной уверенности)Отсутствие ссылки на запись в индексе - это разрушение индекса. Не думаю, что такие вещи требуют отдельного описания... например, не будь в нём ссылок, индекс не мог бы использоваться для запросов с проверкой поля на Null.Ну в Оракле-то так и происходит, если в какой-то записи все поля индекса IS NULL, то запись в индекс физически не попадает. И проверка на WHERE ... IS NULL не может использовать этот индекс. Т.е. такой подход тоже существует в природе.
И в некоторых случаях на этом можно удачно сыграть.

Akinamiksoftконстрейнт на уникальность не сработал.Почему? всё нормально сработало... просто (Null = Null) IS NULL. А NULL-safe compare в индексах не предусмотрено."Не сработал" не в смысле "не проверялся", а в смысле "не вызвал ошибку".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.06.2017, 01:00
    #39472741
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Akinaс хэша по комбинации (MAC, active)MAC - штука небольшая, хэш не очень-то и нужен.

Или можно немного схитрить - не считать честный хэш от всего MAC-а, а просто взять два младших байта от него. От старших байтов все равно толку мало, они обычно очень низкоселективны.
Физически это можно реализовать, например, разбиением MAC-а на два поля - UNSIGNED INT для старшей части и UNSIGNED SMALLINT для младшей. И в индекс включать только младшую часть. Тогда индекс "похудеет" на 4 байта на каждую запись.
Впрочем, если оперативки с избытком, то это может оказаться неэффективным, т.к. индекс в любом случае будет умещаться в кэш и лишние чтения из таблицы будут только мешать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.06.2017, 01:04
    #39472743
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
AkinaAlibek B.какой тип индекса будет оптимальным, хеш или сбалансированное дерево?Ооо... холивару захотел?А не будет никакого холивару.
https://dev.mysql.com/doc/refman/5.7/en/create-index.html#create-index-storage-engine-index-types Table 13.1 Index Types Per Storage Engine
Storage Engine Permissible Index TypesInnoDBBTREEMyISAMBTREEMEMORY/HEAP HASH; BTREENDB HASH; BTREE (see note in text)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.06.2017, 01:06
    #39472744
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посоветуйте оптимальный составной индекс с null-полем
Вот если разделить роли таблицы на две и одну типа InnoDB использовать для истории, а другую типа MEMORY для контроля активных сессий, то да, можно и HASH попробовать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Посоветуйте оптимальный составной индекс с null-полем / 12 сообщений из 12, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]