В mysql для быстрого поиска по дате лучше использовать timestamp как int или как date (datetime)?

Question

[ctacua]

В mysql для быстрого поиска по дате лучше использовать timestamp как int или как date (datetime)?
Для больших объемов данных!

Comments

[Северное Сияние]

в чем проблема сделать тестовую базу, написать туда мильён записей и сделать замеры? А потом нам расскажешь выводы.

Answer 1

[Северное Сияние]

Сделал базу test

CREATE TABLE `test` (
  `name` varchar(255) NOT NULL,
  `date_int` int(11) UNSIGNED NOT NULL,
  `date_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `test`
  ADD KEY `date_int` (`date_int`),
  ADD KEY `date_time` (`date_time`);
COMMIT;

заполнил значениями дат, от 1970-01-01 00:00:01 до 1970-01-05 11:02:29, с разницей 1 секунда, итого вышло 1 603 869 строк (далее php умер и мне лень было настраивать Maximum execution time).

Разницы вообще нет, если искать точное соответствие вплоть до секунды:

SELECT * FROM `test` WHERE date_int = UNIX_TIMESTAMP('1970-01-03 12:12:12')

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	test 	ref 	date_int 	date_int 	4 	const 	2 	NULL

EXPLAIN SELECT * FROM `test` WHERE date_time = '1970-01-03 12:12:12'

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	test 	ref 	date_time 	date_time 	5 	const 	2 	NULL

Так что фанатик прав, нехрен для даты использовать числа.

Answer 2

[FanatPHP]

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

Comments

[Vitsliputsli]

Лентюй, почему? Понятно, что индекс будет на 1 байт больше, т.к. datetime как минимум 5, а timestamp - 4, но почему при поиске инт должен быть быстрее?

[Vitsliputsli]

Как обычно на тостере, "чушь" и аргументы не обязательны.
Поправьте меня, если я не прав, но поиск по индексу будет выглядеть примерно так: идем по бинарному дереву, читаем с диска в память, читаем процессором из памяти, выполняем сравнение. Чтение с диска выполняется не по байтам, поэтому прочитать 5 байт или 512 разницы нет. Чтение процессором будет работать примерно также, страница памяти и смещение, тут больше вопрос к выравниванию, но о нем речи не идет, поэтому разницы так же нет, если конечно сравниваемое не превышает размеров регистра. Ну и процессору без разницы сравнивать 4 байта или 5. Т.е. фактически разницы не будет.
Но это поиск, при сортировке казалось бы должно быть иначе, т.к. в совокупности индекс с datetime будет занимать больше места на диске. Но даже в этом случае, разница несущественна, т.к. индекс datetime будет больше индекса по timestamp примерно на 5%, что в работе не даст существенного выигрыша.

[FanatPHP]

Vitsliputsli, лошадь твой коммент не увидит
В целом все верно, но только диск тут совсем не при делах. Если бд читает индекс с диска, это значит что она уже давно не отвечает

[Vitsliputsli]

FanatPHP, индекс может быть огромным, собственно этот случай и интересен, когда оптимизируем выборки по большой таблице. Держать его весь в памяти постоянно тяжко, разве нет? Но надо это поизучать.

[FanatPHP]

Vitsliputsli, ну так под иннодб буффер пул рекомендуется отдавать 80-90 % всей физической памяти сервера.
Если индекс огромный, есть способы его подсократить. Но вообще правило такое что индексы живут в памяти.

[Vitsliputsli]

FanatPHP, это все верно, когда мы говорим о небольших данных. Если у нас достаточно большая таблица с соответствующими индексами, то памяти под все индексы никак не хватит. В принципе, этого и не нужно, т.к. в памяти хранятся только те страницы индекса к которым идет активное обращение. Т.е. возвращаясь к вопросу в обычной ситуации разницы нет никакой. Но, если взять описанную ситуацию, и мы формируем запрос требующий полный скан индекса, то за ранее неиспользуемыми страницами понадобится лезть на диск.
Представить себе запрос вида select * from order by на многомилионной таблице трудно, но даже если подобное потребуется такое стоит решать иными механизмами.

Answer 3

[Евгений]

Все равно, и то и то 4 байта.
Но timestamp мне кажется более подходящим из-за более удобных запросов на интервалы.

Comments

[ctacua]

int 4, data 3, time 3, datatime 10.
откуда вы взяли то о чем написали?

[shurshur]

ctacua, немного всё равно не так https://dev.mysql.com/doc/refman/8.0/en/storage-re... (о том что data и date это разные вещи промолчу...). А вообще нужно исходить из задачи. Например, если надо хранить даты раньше 1970 года или позже 2036 - unix timestamp уже не подойдёт по чисто архитектурным соображениям.

Answer 4

[Adamos]

Для быстрого поиска по большим объемам данных надо использовать - индексы.
Тогда формат самих данных будет влиять только на время вставки.

Comments

[Ромзес Панагиотис]

ctacua
Это и есть решение на мой взгляд. Индексы - вот что важно для скорости поиска в данном случае.

[ctacua]

про поиск без индексов я даже не думал!

[Adamos]

ctacua, тогда при чем тут формат поля вообще?
Поиск-то по дереву индексов, хоть вы строчкой это время записывайте.