Как оптимизировать эту таблицу MySQL?
Это для предстоящего проекта. У меня есть две таблицы - сначала каждый отслеживает фотографии, и второй отслеживает разряд фотографии
Photos:
+-------+-----------+------------------+
| id | photo | current_rank |
+-------+-----------+------------------+
| 1 | apple | 5 |
| 2 | orange | 9 |
+-------+-----------+------------------+
Фото разряд продолжает изменяться регулярно, и это - таблица, которая отслеживает его:
Ranks:
+-------+-----------+----------+-------------+
| id | photo_id | ranks | timestamp |
+-------+-----------+----------+-------------+
| 1 | 1 | 8 | * |
| 2 | 2 | 2 | * |
| 3 | 1 | 3 | * |
| 4 | 1 | 7 | * |
| 5 | 1 | 5 | * |
| 6 | 2 | 9 | * |
+-------+-----------+----------+-------------+ * = current timestamp
Каждый разряд прослежен для цели создания отчетов/анализа. [Редактирование] Пользователи будет иметь доступ к статистике по требованию.
Я говорил с кем-то, у кого есть опыт в этом поле, и он сказал мне, что хранение разрядов как вышеупомянутое является способом пойти. Но я еще не так уверен.
Проблемой здесь является избыточность данных. Там будут десятками тысяч фотографий. Фото разряд изменяется на почасовой основе (много раз в течение минут) для недавних фотографий, но менее часто для более старых фотографий. На этом уровне таблица будет иметь миллионы записей в течение месяцев. И так как у меня нет опыта в работе с большими базами данных, это раздражает меня немного.
Я думал об этом:
Ranks:
+-------+-----------+--------------------+
| id | photo_id | ranks |
+-------+-----------+--------------------+
| 1 | 1 | 8:*,3:*,7:*,5:* |
| 2 | 2 | 2:*,9:* |
+-------+-----------+--------------------+ * = current timestamp
Это означает некоторый дополнительный код в PHP разделять разряд/время (и сортирующий), но это смотрит хорошо мне.
Действительно ли это - корректный способ оптимизировать таблицу для производительности?Что Вы порекомендуете?
9 ответов
Первый. Период.
На самом деле вы потеряете намного больше. Отметка времени, хранящаяся в столбце int, будет занимать всего 4 байта.
При этом одна и та же временная метка, сохраненная в строковом формате, займет 10 байт.
Я бы придерживался вашего первого подхода. Во втором у вас будет много данных, хранящихся в ряду, со временем они получают все больше рангов! То есть, если фотография набирает тысячи и тысячи рангов.
Первый подход также более удобен в обслуживании, то есть если вы захотите удалить ранг.
Из двух вариантов - как все до меня говорили - это должен быть вариант 1.
На самом деле вам следует беспокоиться об узких местах в самом приложении.Собираются ли пользователи часто обращаться к историческим данным или они отображаются только для нескольких избранных пользователей? Если ответ таков, что каждый может видеть исторические данные о рангах, то вариант 1 достаточно хорош. Если вы не собираетесь так часто обращаться к историческим рангам, вы можете создать третью «архивную» таблицу, и перед обновлением рангов вы можете скопировать строки исходной таблицы рангов в архивную таблицу. Это гарантирует, что количество строк в вызываемой основной таблице останется минимальным.
Помните, что если вы обновляете строки, а их десятки тысяч, может быть более плодотворным получить результаты в вашем коде (PHP / Python и т. Д.), Усечь таблицу и вставить результаты, а не обновлять это строка за строкой, так как это может стать потенциальным узким местом.
Вы также можете найти сегментирование (горизонтальное разделение) - http://en.wikipedia.org/wiki/Shard_%28database_architecture%29
И никогда не забывайте хорошо индексировать.
Надеюсь, что это помогло.
Ваш второй дизайн очень опасен, если у вас 1 миллион голосов за фотографию. Сможет ли PHP справиться с этим?
В первом проекте вы можете выполнять всю математику на уровне базы данных, которая вернет вам небольшой набор результатов .
Вы указали, что ранг связан только с изображением, и в этом случае все, что вам нужно, это таблица 1 и постоянно обновлять рейтинг в режиме реального времени. В таблице 2 просто хранятся ненужные данные. Недостатком такого подхода является то, что пользователь не может изменить свой голос.
Вы сказали, что вторая таблица предназначена для анализа / статистики, поэтому на самом деле это не то, что нужно хранить в db. Я предлагаю избавиться от второй таблицы и использовать средство ведения журнала для записи изменений ранга.
Я думаю, что "удар" по базе данных от чрезмерной нормализации (запрос таблицы рангов снова и снова) можно избежать, "кэшируя" последний ранг в current_rank. Не имеет значения, что ранги сильно растут, если к ней редко обращаются (анализ / отчетность, как вы сказали), она никогда не обновляется, а просто получает записи, вставленные в конце: даже очень легкий ящик не будет иметь проблем с миллионами строк в этой таблице.
Ваша альтернатива потребует множества обновлений в разных местах на диске, что, возможно, приведет к снижению производительности.
Конечно, если вам нужны все старые данные, и всегда по photo_id, вы можете запланировать запланированный запуск другой таблицы rankings_old, возможно, с photo_id, годом, месяцем, рейтингами (включая временные метки), когда закончится месяц, так что извлечение старых данных останется легко возможным, но в rankings_old или rankings не нужно обновлений, только вставки в конец таблицы.
И поверьте мне: миллионы записей в чисто журнальной таблице не должны быть проблемой.
Нормализованные данные или ненормализованные данные. Вы найдете тысячи статей об этом. :)
Это действительно зависит от ваших потребностей.
Если вы хотите построить свою базу данных только с учетом производительности (скорости или потребления оперативной памяти или ...), вам следует доверять только цифрам. Для этого вы должны профилировать свои запросы с ожидаемым «объемом» данных (вы можете сгенерировать данные с помощью написанного вами скрипта). Чтобы профилировать свои запросы, узнайте, как читать результаты двух следующих запросов:
EXPLAIN расширенный ...ПОКАЗАТЬ СОСТОЯНИЕ
Затем узнайте, что делать, чтобы улучшить цифры (настройки mysql, структура данных, оборудование , так далее).
Для начала я действительно советую эти две замечательные статьи:
- http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/
- http://ajohnstone.com/archives/mysql-php-performance-optimization-tips/
Если вы хотите создать академическую красоту нормализации: просто следуйте книгам и общим рекомендациям. :)
Ваш первый проект верен для реляционной базы данных. Избыточность в ключевых столбцах предпочтительнее, потому что она дает вам гораздо больше гибкости в том, как вы проверяете и запрашиваете ранжирование. Вы можете выполнять сортировку, подсчет, усреднение и т. Д. В SQL без необходимости писать какой-либо PHP-код для разделения вашей строки на шесть направлений с воскресенья.
Похоже, вы хотели бы использовать базу данных, отличную от SQL, например CouchDB или MongoDB. Эти позволят вам хранить частично структурированный список рейтингов прямо в записи для фотографии и впоследствии эффективно запрашивать рейтинги. С оговоркой, что вы на самом деле не знаете, что рейтинги имеют правильный формат, как в случае с SQL.