Как отсутствие id (int — primary key, auto_increment) поля влияет на скорость работы MySQL?

Question

[Spiriiit]

Всем привет!

Где-то слышал, что в любой таблице лучше использовать поле id (int - primary key, auto_increment), мол иначе эффективность будет намного меньше, даже если будут другие индексы.

Вот я задумался, есть у меня такая таблица:

CREATE TABLE IF NOT EXISTS `img` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `md5` varchar(32) NOT NULL,
  `time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `md5` (`md5`),
  KEY `time` (`time`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 ;

По сути id мне тут вообще не нужен.
Выборка идет по md5 либо, неактуальные записи, дергаю по time.

Вопрос: если убрать id и оставить только md5 и сделать его PRIMARY KEY / UNIQUE KEY, не повлияет ли это на скорость работы когда база разрастется до пару 10кк и нет ли тут ничего страшного?

Comments

[DevMan]

Spiriiit при чем тут тег PHP?

[Spiriiit]

DevMan: пардон

Answer 1

[Melkij]

Касательно именно mysql/innodb - innodb всегда кластеризован по первичному ключу. Поэтому все вторичные индексы всегда указывают на первичный ключ. Что из этого следует:
select time, md5 from img where md5=?
Потребует просмотра всегда двух индексов. Сначала индекса по md5, потом - первичного ключа.
С первичным ключом по md5 этот запрос сделает один просмотр индекса и для вычитывания time, не входящего в индекс, даже не потребует seek - данные лежат непосредственно рядом с листьями первичного ключа. Т.е. от выкидывания суррогатного ключа этому запросу чистый профит.

Не случайно написал time в запросе, если запросить только select md5 или select md5, id - то это будет index only scan по вторичному ключу и сейчас, без обращения ни к первичному ключу ни к самой таблице.

во-вторых,
int - это 4 байта. varchar32 для cp1251 (почему вообще varchar, а не char(32) или вообще binary(16)?) - 32 байта, timestamp 4 байта. Из-за необходимости ссылаться на куда более объёмный первичный ключ, резко увеличатся в объёме все вторичные индексы. Но вторичный индекс у вас останется только один, да один индекс исчезнет, а уникальный немного похудеет за счёт преобразования в первичный. Не столь огромный оверхед получится, вполне можно пережить. Но может быть не столь интересно, если показана часть таблицы и есть кучка других полей и индексов.
Поиск по time чуток просядет, строки сравнивать всё-таки сложнее пары интов. Но на десятке млн записей, да на mysql значения это играть не будет.

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

Comments

[Spiriiit]

Melkij, спасибо за подробный ответ.

Нет, таблица это вся. С varchar понял.
А вот с time: вы говорите, что лучше time сделать int и писать туда статично метку времени? Просто так изначально было, но я подумал, что выборка по timestamp будет быстрее или это ошибка?

[Melkij]

ммм, где я такое говорил?
Если сущность - датавремя около настоящего момента - то timestamp подходящий выбор.
А, это где я про сравнения писал? Физически timestamp в mysql занимает 4 байта, как int32 и хранится, поэтому и сравнение двух интов будет.

[Spiriiit]

Melkij: не, это я вашу мысль не правильно понял. Теперь все, пазл собрался, спасибо еще раз!)

Answer 2

[Draconian]

Принимая во внимание ответ выше, можно протестировать этот вариант на отдельном инстансе - убрать айди, сгенерировать хотя бы миллион строк, и поделать разные часто выполняемые запросы, всё будет понятно. Но проблем не должно быть никаких.
К тому же, если у вас в этой таблице появятся десятки миллионов строк, скорее всего, придется задуматься о партицировании, оптимизации и т.д.

Comments

[Spiriiit]

Спасибо за ответ!