Mysql Составной первичный индекс vs составной уникальный на миллионах записей в InnoDB

[Yoskaldyr]

Задача
есть таблица на несколько млн. записей

CREATE TABLE `stats` (
  `node_id` int(10) unsigned NOT NULL DEFAULT '0',
  `stats_date` int(10) unsigned NOT NULL,
  `stats_type` varbinary(25) NOT NULL,
  `counter` int(10) unsigned NOT NULL,
  PRIMARY KEY (`node_id`,`stats_date`,`stats_type`),
  KEY `stats_date` (`stats_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Почти все запросы это селекты выбирающие все записи по условиям типа
`node_id` = ? AND `stats_date` = ? AND `stats_type` = ?
или
`node_id` = ? AND `stats_date` = ?
или
`node_id` = ?
Остальное только вставки новых записей (апдейтов нет). Работа по вставке так и по селектам очень периодическая.
Т.е. пока никаких проблем нет - из базы все данные выбирается быстро насколько возможно из кластерного индекса.

Сейчас как все работает полностью устраивает, но т.к. записей в таблице будет еще больше и появилась необходимость добавления еще 1 простого индекса для выборки по stats_date, то возникает несколько проблем.
При создании любого дополнительного индекса в InnoDB первичный ключ неявно копируется в него, т.е. только добавив в данную таблицу 1 ключ размер данных увеличится в 2 раза.
Как вариант можно использовать автоинкрементый примари ключ + уникальный составной ключ по нужным полям вместо составного примари. Тогда да, каждый новый ключ в базе будет занимать значительно меньше места, но падает скорость выборок (у меня где-то на 10-30%), когда задействуется много записей.

Поэтому несколько вопросов:

• что лучше выбрать составной PK + простой ключ или автоинкремент PK + уникальный составной + простой ключ
  
• где можно почитать о выделении, использовании и резервировании памяти в InnoDB при работе с кластерным индексом (по обычному есть хоть какая-то информация) и как можно узнать в реалтайме сколько он занимает в памяти? Я понимаю что скорее всего я неправильно ищу, но почти все что выдает поисковик на мои поисковые запросы - это форумы и stackoverflow, где куча предположений по типу "я думаю что память выделяется так" или "по идее это работает так", но точного ответа нет.

P.S. Я прекрасно понимаю что разницы что использовать вообще нет на небольших таблицах в 100-200К записей, речь идет именно о таблицах больше 2-3млн записей
P.P.S. Моих знаний C++ недостаточно для изучения исходников Mysql

 

[Yoskaldyr]

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

Вопрос был в том что выбрать (и вопрос имеет смысл только для большого количества записей и только для innodb, у того же myisam-а первичный ключ не кластерный):
- использование выборки с помощью кластерного индекса для большинства запросов, что быстрее где-то на 20-30% использования обычного уникального индекса, но смириться перерасходом памяти при добавлении любого дополнительного индекса
- или использование обычного автоинкремента в качестве первичного ключа и использование обычного уникального индекса по тем же ключам что и в первом варианте. В результате выборка с использованием уникального ключа будет медленнее чем в первом варианте, но добавление любых дополнительных ключей не будет вызывать такого перерасхода по памяти.

P.S. И да я в курсе что можно использовать TokuDB, которая позволяет создавать несколько кластерных ключей, но смена типа таблиц не подходит.

 

[Yoskaldyr]

Т.е. выбор между 2-мя схемами (чтобы было понятнее)

CREATE TABLE `stats` (
  `node_id` int(10) unsigned NOT NULL DEFAULT '0',
  `stats_date` int(10) unsigned NOT NULL,
  `stats_type` varbinary(25) NOT NULL,
  `counter` int(10) unsigned NOT NULL,
  PRIMARY KEY (`node_id`,`stats_date`,`stats_type`),
  KEY `stats_date` (`stats_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

и

CREATE TABLE `stats` (
  `stats_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `node_id` int(10) unsigned NOT NULL DEFAULT '0',
  `stats_date` int(10) unsigned NOT NULL,
  `stats_type` varbinary(25) NOT NULL,
  `counter` int(10) unsigned NOT NULL,
  PRIMARY KEY(`stats_id`),
  UNIQUE KEY `stats_key` (`node_id`,`stats_date`,`stats_type`),
  KEY `stats_date` (`stats_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

[Yoskaldyr]

PRIMARY KEY - это ключ, по которому сортируется результат, если не указано обратное. И по которому происходит линковка двух таблиц, если прямо не указан ключ для объединения.
Если у тебя есть операции объединения сортировки по другим полям, у тебя в любом случае на них будут отдельные индексы.

Спасибо кэп. Только вопрос был не в этом.

Ты вопрос неправильно задаёшь. Так доступнее?

Да нет, это ты просто вопрос не понимаешь. И вопрос я задал правильно. А ты поленился погуглить/почитать оффдоку насчет кластерных индексов и их частной реализации в InnoDB. И не видишь разницы между кластерным и обычным индексом и не знаешь что скорость работы у них будет разная, хотя различаться будет только на очень больших таблицах.

Разница в скорости выборки обусловлена не тем что ключ примари, а тем что он кластерный, просто в Innodb примари ключ является кластерным и все, и на InnoDB таблицу кластерный ключ может быть только один. Поэтому для InnoDB можно говорить что первичный = кластерный.
Но еще раз повторю (для непонятливых) - утверждение, что по первичному ключу выборка быстрее чем по обычному только из-за того что первичный ключ является кластерным, а не из-за того что он такой главный или первый или еще какой магический и то это только в конкретном, частном случае InnoDB таблиц. Для Myisam это не верно, т.к. все индексы включая primary некластерные, а у TokuDB можно сделать несколько кластерных индексов на таблицу. И разница в скорости заметна только на большом количестве записей

 

[Redjik]

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

 

[Redjik]

Вообще могу немного помочь так:
1ая схема в твоем посте - расход памяти O(2n), скорость поиска O(logn);
2ая схема - расход памяти O(n), скорость поиска O(2logn)

По общей скорости роста второй вариант предпочтительнее, при условии, что Википедия не врет об алгоритмической сложности б-деревяшек )))

 

[Yoskaldyr]

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

P.S. Я тоже начал склоняться ко второму варианту, но пока что окончательно не решил

 

[fixxxer]

Такие тонкости, боюсь, знают только разработчики. Попробуй поспрашивать в перконовском форуме или их гуглогруппе.