Пишу форум.
Нужны практические советы по оптимизации структцры БД форума.Возможно советы из этой темы пригодятся кому-то еще.
-------------------------------------------------------
В бд форума есть основные таблицы:
1. USERS: id_user, username, passw, count_post.
2. POSTS: id_post, id_topic, id_poster, name_poster, date_time_post, post_text.
3. TOPICS: id_topic, id_razdel, title_lopic, id_author, name_author, last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts.
4. RAZDEL: id_razdel, title_razdel.
Заранее хочу оптимизировать структуру БД под довольно большую нагрузку: 5-8 млн. сообщений, 300-500 тыс. тем, 30-50 тыс. хостов в день и около 200-300 тыс. просмотров в день. В онлайне постоянно 300-800 человек.
-------------------------------------------------------
1 глобальный ВОПРОСПри добавлении юзером сообщения оно добавляется в таблицу POSTS, одновременно в таблице USERS обновляется поле count_post, а так же в таблице TOPICS обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts.
Мне нужно сделать запрос на формирование главной страницы форума (например как на форуме sqlinfo.ru).
То есть нужно вытащить название раздела, количество тем и сообщений в разделе, автора и время последнего поста в разделе.
Сначала планировал сделать так:
1. Первым запросом запрашиваю из таблицы RAZDEL название разделов.
2. Из TOPICS запрашиваю все поля для тем, сгруппировав по разделам и найдя для каждого раздела тему с максимальным last_post_id.
То есть у меня будет для каждого раздела вытянуты параметры темы в которой написано последнее сообщение в разделе:
SELECT t1.id_topic, t1.title_topic, t1.id_author, t1.name_author, t1.last_post_id, t1.last_poster_id, t1.last_poster_name, t1.date_time_last_post
FROM TOPICS t1
WHERE t1.last_post_id = ( SELECT t2.last_post_id
FROM TOPICS t2
WHERE t2.id_razdel = t1.id_razdel
ORDER BY t2.last_post_id DESC LIMIT 1 )
ORDER BY t1.last_post_id DESC;
3. Третьим запросом вытаскиваю с помощью GROUP BY количество тем и постов для каждого раздела.
Теперь появилась идея в таблицу RAZDEL добавить поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts, count_topics.
Терерь при добавлении юзером сообщения, оно добавляется в таблицу POSTS, одновременно в таблице USERS обновляется поле count_post, в таблице TOPICS обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts,
в таблице RAZDEL обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts. И при создании темы обновляется count_topics.
Теперь одним простым запросом можно сформировать главную страницу.
Что с точки зрения оптимальности лучше сделать: первый вариант или второй (догадываюсь, что второй).
Но вот не слишком ли много АПДЕЙТОВ в 3-х таблицах при добавлении одного поста при довольно частом постинге и активном чтении форума пользователями? Не будет ли при многочисленных апдейтах тормозить сервер в моём случае при такой нагрузке?
2 глобальный ВОПРОСЭто на счет хранения текста поста в таблице POSTS.
Как я уже писал: заранее хочу оптимизировать структуру БД под довольно большую нагрузку: 5-8 млн. сообщений, 300-500 тыс. тем, 30-50 тыс. хостов в день и около 200-300 тыс. просмотров в день. В онлайне постоянно 300-800 человек.
У данного поля post_text будет индекс FULLTEXT. Не лучше ли будет хранить текст постов в отдельной таблице, где будет id_post и post_text?
До этого в книге прочитал, что из-за наличия большого текстового поля в таблице и индекса FULLTEXT на нем может снижаться скорость доступа к таблице, а ведь при заданной мной нагрузке из таблицы POSTS будут постоянно выбираться посты когда пользователь запрашивает данные при просмотре темы.
А так же будут постоянно добавляться новые посты – не замедлит ли индекс FULLTEXT добавление данных (ведь, если я не ошибаюсь, при добавлении будет перестраиваться индекс для всей этой огромной таблицы.)
Не лучше ли будет выбрать id поста из таблицы POSTS а потом join-ом присоединить текст поста к данной выборке из отдельной таблицы или лишний join в данном случае тоже зло?
P.s. Смотрел структуру таблиц других форумов: в phpBB2 текст хранится в отдельной таблице (индекса FULLTEXT нет вообще), в phpBB3 текст и остальные данные поста хранятся в одной таблице (индекса FULLTEXT нет вообще); в IP Board текст и остальные данные поста хранятся в одной таблице (на поле post_text индекс FULLTEXT !)...