Хранение дат в mysql: сравнение производительности между различными методами для даты

Сейчас у меня в голове три варианта.

1st -> четыре столбца (дата, месяц, год, день) => 28, 03, 2011, 1 Я могу легко искать и изменять эти столбцы без дополнительного изучения дат mysql.

2-й -> один столбец даты (дд-мм-гггг) => 28-03-2011 Для этого требуется только один столбец, которым легче управлять, поскольку для поиска дат используется только один параметр WHERE. Но я не знаю, как я могу искать все записи, скажем, за определенный день. Допустим, все данные за все понедельники в прошлом или все данные за все 28 числа.

3rd -> два столбца (отметка времени unix для текущей даты, дня) => 1827328721, 1 Теперь здесь я могу хранить данные в виде отметки времени и легко выполнять поиск и сравнение, просто получая дату а затем превратить его в временную метку unix, а затем использовать ее в sql. Для дня я могу использовать столбец дня.

Теперь вопросы:

  1. Как производительность отличается между этими методами?
  2. Как будут построены запросы для выбора, вставки и обновления для этих различных предлагаемых решений?
  3. Какой из них лучший на ваш взгляд и почему?

Пожалуйста, ответьте на все три вопроса подробно. Потому что я не могу найти эту информацию в другом месте, и я знаю, что у переполнения стека есть блестящие программисты, которые могут ответить на этот вопрос очень связно. Этот вопрос принесет пользу не только такому новичку, как я, но и всем другим новичкам, которые могли бы использовать его для справки.

Заранее благодарим сообщество переполнения стека.


mysql sql date where-clause
person Vish    schedule 28.03.2011    source источник

Ответы (3)


arrow_upward
5
arrow_downward

Как производительность отличается между этими методами?

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

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

То же самое с третьим вариантом... это просто денормализованное представление даты; это может быть оправдано для очень ограниченного использования, но обычно это плохая идея.

--РЕДАКТИРОВАТЬ--

Под денормализованным я подразумеваю следующее...

Допустим, у вас есть запись со следующими полями...

Date       Day    Month    Year
3/28/2011  28     3        2011

Допустим, вам нужно изменить день с 28-го на 29-е. В этом случае вам нужно обновить два поля, поле «Дата» и «День»… вместо одного. Если вы всегда помните об обновлении обоих, то это не большая проблема. Но если вы этого не сделаете, со временем вы получите что-то вроде следующего.

Date       Day    Month    Year
3/28/2011  29     2        2009

Так какая на самом деле дата? Сохраняя информацию в одном месте, вы исключаете возможность несоответствия данных.

-- КОНЕЦ РЕДАКТИРОВАНИЯ--

Какой из них лучший на ваш взгляд и почему?

Если только ваша база данных не используется для OLAP... Я считаю второй вариант лучшим.

Как будут построены запросы для выбора, вставки и обновления для этих различных предлагаемых решений?

Для предпочтительного второго варианта это тривиально.

-- ВТОРОЕ РЕДАКТИРОВАНИЕ --

Вы можете передать дату в виде строки, и MySQL проанализирует ее в соответствии с ней. Формат даты «ГГГГ-ММ-ДД ЧЧ:мм:СС», но вам не нужно указывать время, если вы не хотите его хранить.

INSERT INTO tablename (date) VALUES ('2011-3-28')

Или, если вы просто хотите добавить текущую дату...

INSERT INTO tablename (date) VALUES (CURDATE() )

-- КОНЕЦ РЕДАКТИРОВАНИЯ --

person Michael Fredrickson    schedule 28.03.2011
comment
Отличный ответ... спасибо... Приложение, которое у меня есть, представляет собой виджет, который отображает несколько элементов. Оттиск каждого элемента записывается, в результате чего получается вставка. поэтому, если в виджете 500 элементов, то есть 500 вставок... Тогда для просмотра статистики это будет за сегодня, за последние несколько дней, в какой-то другой случайный день в соответствии с требованием. Вы бы сказали, что это приложение OLAP. Я почему-то не нахожу первый способ излишне сложным, потому что для вставок вы просто указываете три параметра: дату, месяц и год; поиск такой же. Не могли бы вы объяснить, почему это денормализовано. - person Vish; 29.03.2011
comment
Судя по вашему описанию, ваше приложение больше похоже на OLTP, чем на приложение OLAP... Я считаю, что типичное приложение OLAP имеет дело как минимум с миллионами строк данных. Что касается первого способа... Я бы посчитал его денормализованным, потому что одна и та же часть информации (дата) дублируется в нескольких полях... поля месяца, года и дня уже представлены в столбце даты. Если вам когда-либо понадобится изменить дату, вам потребуется изменить данные более чем в одном месте, чтобы сохранить их согласованность. - person Michael Fredrickson; 29.03.2011
comment
В настоящее время в этой таблице содержится около 300 000 записей, и в этой таблице происходит около 1200 вставок/обновлений в секунду и 500 выборок в секунду. - person Vish; 29.03.2011
comment
Главное, что возможность обновить записи на сегодняшнюю дату имеет решающее значение. Выбор сегодняшней даты также важен. Ведение статистики и отчеты о прошлом не так важны, и это может быть замедлено. Но вставки/обновления и выборки должны быть очень быстрыми. Что быстрее — использовать три оператора where в трех индексированных столбцах или использовать один оператор where в одном индексированном столбце даты. Не могли бы вы также объяснить, что вы имели в виду, говоря, что три столбца для даты являются деноминализованной формой хранения? Даюммм, ты так много знаешь.... - person Vish; 29.03.2011
comment
Я сохраняю только дату (например, сегодняшняя дата 28), месяц (сегодняшний месяц, март), год (сегодняшний 2011 год), день (сегодняшний день (понедельник или 1)), а не сохраняю всю дату. Будет ли это лучшим методом, если я использую это? Спасибо за лучшее объяснение денормализации. Извините, если я смутил вас использованием слова Дата - person Vish; 29.03.2011
comment
Ааа.. Спасибо за разъяснение. В этом случае я бы все же выбрал второй вариант. Что касается того, что будет быстрее, первый или второй вариант. Пока они проиндексированы, оба будут быстрыми, но я предполагаю, что второй вариант с реальной датой будет немного быстрее , так как индекс будет более избирательным. Кроме того, если вам когда-нибудь понадобится выполнить какие-либо манипуляции с датой, например добавить одну неделю к дате, будет намного проще сделать это с одним столбцом даты, чем с несколькими столбцами. - person Michael Fredrickson; 29.03.2011
comment
Дело в том, что я никогда раньше не использовал встроенный тип столбца даты в MySQL, и не так много приличных ссылок, чтобы узнать об этом больше. Документация по MySQL может быть отличной, но она определенно не для начинающих. Мне просто нужно знать, как я могу выполнять основные функции, такие как вставка сегодняшней даты, обновление сегодняшней даты, выбор сегодняшней и прошлой дат. Ничего больше.... Если вы знаете какие-нибудь достойные места, где я могу узнать об этом, пожалуйста, помогите мне со ссылкой... Хотя, спасибо, вы очень помогли и очистили мою голову от дат mysql. - person Vish; 29.03.2011

arrow_upward
4
arrow_downward

Если вам нужно хранить значения даты, выберите тип date (или datetime). В Mysql есть много функций datetime, которые помогают вы строите запросы. Например,

SELECT * FROM your_table
WHERE date_column < DATE(NOW()) AND DAYOFWEEK(date_column) = 2

// все понедельники в прошлом

person a1ex07    schedule 28.03.2011
comment
Это. Извините, не видел. - person wallyk; 28.03.2011
comment
Не могли бы вы ответить на вопрос о производительности, это очень важно! Спасибо :) - person Vish; 29.03.2011
comment
Производительность зависит от индексов и от того, как они соответствуют запросам. Если вы создадите индекс для столбца date, движок mysql будет использовать его, как только решит, что запрос может выиграть от использования индекса. Если вы создадите отдельный столбец для дня недели или дня месяца и индекс для этого столбца, этот индекс не будет достаточно избирательным, чтобы его мог рассмотреть оптимизатор. - person a1ex07; 29.03.2011

arrow_upward
1
arrow_downward

  1. Полезно только в том случае, если вам нужны эти части отдельно, что маловероятно.
  2. Date или Datetime являются родными, и вы можете сделать их индексом для ускорения.
  3. не хорошо, не родной для mysql. если вам нужно разрешение до секунд, используйте datetime.

Для получения дополнительной информации о работе с датой и датой-временем обратитесь к руководству.

person Raffael    schedule 28.03.2011