Приветствую всех. Спроектировал базу данных, но попал в тупик при даже сравнительно небольших объёмах (100к записей) в связи с очень медленной обработкой данных (до 1й минуты) Суть самого проекта в следующем. Это статистические данные по заходам посетителей. Вебмастер получает счётчик, ставит себе его на сайт и получает возможность смотреть уникальные и неуникальные заходы, группировать их по сайтам, странам. Как у меня всё устроено. Есть 3 таблицы: users, sites и daily_stats. В users - данные о пользователях, в sites - данные о сайтах, в countries - страны, daily_stats - вся статистика. Сами таблицы:
Таблица пользователей:
1.
2.
3.
4.
CREATE TABLE `users` (
`id` int( 11 ) NOT NULL auto_increment,
`username` varchar( 100 ) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT= 11 DEFAULT CHARSET=utf8;
Таблица сайтов:
1.
2.
3.
4.
CREATE TABLE `sites` (
`id` int( 11 ) NOT NULL auto_increment,
`domain` varchar( 100 ) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT= 11 DEFAULT CHARSET=utf8;
Таблица стран:
1.
2.
3.
4.
5.
CREATE TABLE `countries` (
`id` int( 11 ) NOT NULL auto_increment,
`code` varchar( 2 ) NOT NULL,
`name` varchar( 100 ) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT= 11 DEFAULT CHARSET=utf8;
Таблицы users, sites и countries - справочники. Пример:
idusername1user12user2
iddomain1domain1.com2domain2.com
idcodename1RUРоссия2UAУкраина
В моём случае служат исключительно для того, чтобы их джойнить к статсам и выводить имена сайтов, юзеров, стран для админа и для самих юзеров.
И, собственно, таблица статистики:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE `stats_last` (
`crc` bigint( 20 ) NOT NULL default '0',
`site_id` int( 11 ) NOT NULL default '0',
`user_id` int( 11 ) NOT NULL default '0',
`country_id` int( 10 ) default '0',
`date` int( 11 ) unsigned default NULL,
`hit_unique` int( 11 ) NOT NULL default '0',
`hit_raw` int( 11 ) NOT NULL default '0',
PRIMARY KEY (`crc`),
KEY `site_id` (`site_id`),
KEY `user_id` (`user_id`),
KEY `date` (`date`),
KEY `country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Здесь внесу ясность по всем полям.
crc - это хеш-сумма даты ид сайта, ид юзера, ид страны.
site_id, user_id, country_id - ид соответствующих таблиц
date - дата в формате timestamp, с шагом в день (т.е. детализация статистики - это день)
hit_unique, hit_raw - статистика по посещениям.
С помощью crc я очень быстро могу занести данные в базу с помощью ON DUPLICATE ENTRY, т.к. мне не надо делать кучу проверок. По сути, для каждой комбинации данных (ид сайта, юзера, страны, даты) создаётся отдельная запись. Это позволяет делать выборку и группировать статистику по:
- Дате
- Сайтам
- Пользователям (для админа)
- Странам
Пример записей в таблице daily_stats:
crcsite_iduser_idcountry_iddatehit_uniquehit_raw11318984391211235854800101511318984392211235854800152511318984392111235854800571131898439122123585480024
Это очень удобная структура, как мне кажется, так как, чтобы отобразить статистику по дням, мне необходимо сделать один запрос:
SELECT SUM(hit_unique), SUM(hit_raw) FROM daily_stats GROUP_BY date
По юзерам:
SELECT SUM(hit_unique), SUM(hit_raw) FROM daily_stats GROUP_BY user_id
SUM(), как не трудно догадаться, служит для суммирования статистики для каждого юзера, сайта, даты (в зависимости от группировки) Таким образом, я могу видеть, сколько всего было заходов в такой-то день или у такого-то пользователя.
Что самое удобное в данной структуре, так это то, что я могу накладывать дополнительные фильтры, выбирая статистику по дополнительным критериям, к примеру, по промежуткам дат:
SELECT SUM(hit_unique), SUM(hit_raw) FROM daily_stats WHERE date= 1235854800 GROUP_BY user_id
JOIN делаю, когда необходимо совместить две таблицы и вывести помимо статистики ещё и имена польователей или сайты. По типу:
SELECT SUM(s.hit_unique), SUM(s.hit_raw),u.username FROM daily_stats s LEFT JOIN users u ON u.id=s.user_id GROUP_BY s.user_id
Так вот, всё это просто шикарно работает на сравнительно небольшой базе. Выборки можно делать очень гибкими. Но при размере базы в 100к (а скорость её роста в месяц примерно такая и есть) она начинает просто дико тормозить даже на обычных SUM запросах:
SELECT SUM(s.hit_unique) as total FROM daily_stats
Ладно, это я могу кешировать, но вот статистика, сгруппированная по размым критериям - я не могу ничего с ней поделать и она дико тормозит, как бы я не старался. 10 пользователей, 10 стран у каждого, 10 сайтов и 100 дней и скорость открытия такой статистики около 20и секунд. А это вообще беда, т.к. всё должно отрабатывать менее чем за одну секунду, как это делается у других.
Так как мне, в основном, необходима статистика, с группированная по сайтам, пользователям, странам, датам, то я решил переделать структуру и разбить таблицу daily_stats на соответствующие таблицы: stats_bysite, stats_byuser, stats_bycountry, stats_bydate. Вот так выглядят поля:
crcsite_iddatehit_uniquehit_raw
crcuser_iddatehit_uniquehit_raw
crccountry_iddatehit_uniquehit_raw
Записывать в каждую таблицу уже просуммированные значения и всё, во время вывода необходима будет выборка исключительно по дате, т.к. всё остальное будет уже просуммированно и сгруппировано.
Но вот ведь проблема. Группировать и суммировать ведь тоже когда то необходимо. Статистика уже получается не риалтайм, а её необходимо дампить раз в какое-то время. Раз в минуту, к примеру. Тогда логика работы такова:
Изначально всё пишется в общую таблицу daily_stats, структура у которой прежнаяя. Раз в период времени скрипт берёт данные, группирует и суммирует их сначала по сайтам, затем по юзерам, затем по странам, затем по датам и запишсывает каждый результат в свою таблицу. Затем daily_stats очищается.
Но тут проблема какого характера... А если у меня тысяча пользователей, тысяча стран? И в момент сбора статистики она (статистика) будет у всех пользователей, то мне придётся делать кучу INSERT ON DUPLICATE ENTRY UPDATE для группы каждого типа. И неизвестно, сколько это займёт времени.
Итак, следующие вопросы. Как вообще организуются хранилища подобной информации и какая у них структура? Рассматривается MySQL и один среднестатистический сервер.
И во второй моей структуре с различными группировками и, по сути, кешированию всего - нет ли в ней косяков? Т.к. мне кажется, что нагрузка будет всё равно, если не в момент чтения, то в момент записи. И как мне во втором варианте, к примеру, сделать выборку с группировкой по сайтам, но определённого пользователя? В первой моей структуре это делается очень просто:
SELECT * FROM daily_stats WHERE user_id= 1 GROUP BY site_id
Как это сделать во второй - у меня нет идей, кроме как хранить дополнительно общую базу со всей статистикой и подобные сложные запросы выполнять через неё.
Заранее огромное спасибо!
