0
Другие записи за это число:
2009/12/21_loadlj - поправил глюки в loadlj
2009/12/21_sho - шо любопытно
<< предыдущая заметкаследующая заметка >>
21 декабря 2009
размышления о базе

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

-- Каждый вошедший по первому разу записывается в базу посетителей и получает в куку кодированный номер этой записи. Это — его регистрация в базе. Потом он ее сможет «официально подтвердить», оформив анкету с паролем или пройдя регистрацию по openid. Но нерегистрированный и регистрированный посетитель лежат все равно в единой базе всех посетивших сайт. Это имеет ряд плюсов — например, станет возможно включить подзамочный доступ какому-то посетителю сразу, не дожидаясь, пока он пройдет регистрацию. Зашел к вам президент Медведев (что стало неопровержимо понятно из его комментария) — включили ему подзамки и прочие дружественные фичи.

-- При каждом заходе делается новая запись в отдельной базе заходов. Это позволит вести точный учет, сколько именно ЧЕЛОВЕК читают дневник. А не крутят счетчик, забегая повторно.

-- При каждом посетительском действии (например, при голосовании за коммент) делается запись в базу голосов. Повторные голосования (накрутки) станут затруднены. То есть, для накрутки надо каждый раз менять браузер. А то и IP — можно, например, запретить регистрацию больше 10 человек с одного IP.

Вопросы по оптимизации базы:

1) Я заебываюсь работать с полем Datetime. Не проще ли будет (и не быстрее ли для MySQL) хранить дату в int 11?

2) Допустим, база посещений устроена так:

A = [int 11] номер посетителя
Б = [int 11] номер заметки
В = [int 11] дата посещения

Наиболее частая выборка из базы посещений будет:

-- Посещал ли человек 111 заметку 222 (если да — не вносить)? "COUNT(*) WHERE A=111 AND B=222"
-- Сколько человек открывали заметку 222? "COUNT(*) WHERE B=222"

Вопрос: какие мне надо индексы построить для такой базы? Я правильно понимаю, что нужен двойной индекс (A,B) и отдельно индекс B?

Еще вопрос: имеет ли смысл при каждом заходе нового посетителя (по моим предположениям 30% посещений заметки — повторные заходы) дублировать результат "COUNT(*) WHERE B=222" в поле счетчика заметки? Или по скорости никакого выигрыша не будет, и лучше для каждой заметки счетчик определять динамически, выполняя всякий раз "COUNT(*) WHERE B=222"?

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
Linux Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Тоже думал о подобной системе для своих сайтов.
Но заводить по юзеру на каждого визитёра, это иметь базу пользователей в перспективе -- на миллионы записей. В частности, роботы вряд ли будут хранить куки между сеансами.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
При такой компактной организации базы (всего 3 поля int, ну и ключ), я не думаю, что это сегодня проблема. Ну, миллион записей. И что? В крайнем случае, никто не мешает эту базу время от времени чистить, удаляя все незарегистрированное, что не заходило уже полгода.
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Лучше всего хранить счетчик посещений отдельным полем в таблице заметок, и обновлять, пересчитывая по крону (раз в 5 минут) или триггером (получится практически то же самое, что и без кеширования, но изящнее).
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Пересчитывая по крону... что?! Вот у меня в дневнике несколько тысяч заметок. Каждые пять минут пересчитывать все их счетчики?! Вот это будет ломовище для сервера!

Кроме того, это выглядит некрасиво: человек обновляет заметку, а счетчик пять минут не меняется.
Linux Safari
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ээх, хреновый из тебя гугол, Лёня
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Имею обыкновение советоваться с живыми умными людьми, а не с гуглом.
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я думаю надо просто научится готовить datetime это совсем не так сложно, зато "правильно".
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А какой в том практический смысл? Я пока не могу представить, зачем вообще может понадобиться поле datetime. В том смысле, что операций в духе "выбрать все записи, сделанные по четвергам" не предвидится, да и вообще никаких выборок по дате не предвидится. Однако что-то мне подсказывает, что операции с датой исполняются хоть чуть, но дольше, чем с простым int. Я не прав?
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Насколько я понимаю, работа с datetime и int (сиречь юниксовой меткой времени) в мускуле по скорости почти либо совсем не отличаются.

>-- Посещал ли человек 111 заметку 222 (если да — не вносить)? "COUNT(*) WHERE A=111 AND B=222"

Я вот полагаю, что эта проверка вообще лишняя будет, если создать уникальный индекс (номер_посетителя, номер_заметки) и при каждом посещении просто пытаться делать инсерт.
На круг быстрее выйдет.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Это разные базы: база посетителей и база посещений, база голосований и т.п. В базе посетителей хранится номер посетителя, его данные, IP, Браузер, емайл, регистрационная информация, год рождения и т.п. В базе посещений - только тот факт, что посетитель с номером 111 посетил заметку с номером 222. Это для счетчиков и статистики.
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Да, во. А если этот инсерт прошел (mysql_affected_rows), то плюсовать счетчик людей в таблице заметок.
И никаких частых долбёжек таблицы посещений. А то когда таблица очень сильно разбухает, частое выполнение даже самых простых селектов для сервера не сахар.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Не очень я наверно понял, о чем речь. Но подумаю на досуге.
Windows Safari Chrome
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
>Это разные базы: база посетителей и база посещений, база голосований и т.п. В базе посетителей хранится номер посетителя, его данные, IP, Браузер, емайл, регистрационная информация, год рождения и т.п. В базе посещений - только тот факт, что посетитель с номером 111 посетил заметку с номером 222. Это для счетчиков и статистики.

Это я понял. Я что предлагаю:
Есть база посещений, грубо говоря, три поля: (юзерID, заметкаID, дата_посещения). У нее есть УНИКАЛЬНЫЙ индекс на двух полях - (юзерID, заметкаID).
При каждом посещении заметки мы пытаемся делать
INSERT INTO база_посещений VALUES(юзерID,заметкаID,unix_timestamp())

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

Если мы сразу после инсёрта посмотрим значение функции mysql_affected_rows(), то в первом случае она возвратит 0, во втором - 1.

Так вот, если она вернула единицу, то мы можем со спокойной душой прибавить единицу к (предварительно созданному) счетчику людей в базе заметок.

Иначе говоря, в базе заметок будет не один счетчик посещений (я правильно понимаю, что он там есть?), а два: к счетчику посещений прибавляется единица каждый раз при обращении к заметке, а к счетчику людей - только тогда, когда приходит новый человек.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Мудро! А MySQL... нет, лучше спросить так: как мне создать уникальный индекс на двух полях? Таблицы блогодвижка выглядят так: http://lleo.aha.ru/blog/module/upgrade/sql.txt
Windows Safari Chrome
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
>нет, лучше спросить так: как мне создать уникальный индекс на двух полях?

Насколько я понимаю, таблицы, о которой мы говорим, в структуре еще нет, да?
Ну, например, можно сделать первичный ключ на двух полях - вот так:

CREATE TABLE `dnevnik_zapisi_views` (
`zametkaID` int(10) unsigned NOT NULL,
`userID` int(10) unsigned NOT NULL,
`access_date` int(11) unsigned NOT NULL,
PRIMARY KEY (`zametkaID`,`userID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Собственно, и всё.

Запрос INSERT INTO `dnevnik_zapisi_views` VALUES(1,1,unix_timestamp()) добавит в эту таблицу новую строку только один раз. При попытке исполнить его снова с теми же значениями первых двух столбцов мускуль скажет "Duplicate entry '1-1' for key 1" и больше ничего страшного не случится.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Да, спасибо, я, собственно, про это спрашивал: PRIMARY KEY (`zametkaID`,`userID`) Век живи - век учись. Эдак и старое кое-что переделать имело смысл.
Windows Safari Chrome
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Рад помочь.
Windows
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ошибочный совет выше
>>PRIMARY KEY (`zametkaID`,`userID`)

Так делать нельзя, да и работать не будет, надо вот так
UNIQUE KEY (`zametkaID`,`userID`)

а далее работать можно так

INSERT INTO база_посещений VALUES(юзерID,заметкаID,unix_timestamp())
ON DUPLICATE UPDATE SET
тут_название_поля_времени = values(тут_название_поля_времени)
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А зачем нам обновлять поле времени? По-моему не надо.
Windows
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
опечатался, вместо ON DUPLICATE UPDATE SET
читаем ON DUPLICATE KEY UPDATE SET
Windows Safari Chrome
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
> Так делать нельзя, да и работать не будет, надо вот так
UNIQUE KEY (`zametkaID`,`userID`)

Чудненько работает и с праймари.
Создавать первичный ключ на нескольких полях религия запрещает?))
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Скажите, я могу в MySQL одним запросом создать новую запись (ключ id autoincrement), и сразу же получить этот id? Неужели такого нет? Как-то глючно, переспрашивать базу, какой id нынче последний - вдруг за это время параллельно что-то создастся?
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Идея хорошая,только две проблемы
-у некотрых провайдеров нет статичных ипов (и их достаточно много),хотя в будущем скорее всего у каждого будет свой ип
-куки можно вырубить
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
1
1
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
У большинства посетителей нет статичных IP, и у меня динамический. На статичные IP никто не закладывается, авторизация всегда хранится в куках. Ну а людям с отключенными куками, я полагаю, не надо разрешать голосовать и писать комментарии. Пусть чувствуют свою ущербность и не выпендриваются, хацкеры!
Windows
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
>>Чудненько работает и с праймари.
Такую запись как у вас вижу первый раз.
Mysql 5 выдает ошибку - проверьте.

>>А зачем нам обновлять поле времени? По-моему не надо.
Ну это я как пример дал, типа не надо писать второго апдейта после fail инсерта, а можно одним запросом узнать, были ли данные и ежели нет, то их вставить, а если были то обновить.

>>Скажите, я могу в MySQL одним запросом создать новую
>>запись (ключ id autoincrement), и сразу же получить этот >>id?

Конечно. Функция php mysql_insert_id() - http://php.net/manual/en/function.mysql-insert-id.php
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
> Скажите, я могу в MySQL одним запросом создать новую запись (ключ id autoincrement), и сразу же получить этот id? Неужели такого нет? Как-то глючно, переспрашивать базу, какой id нынче последний - вдруг за это время параллельно что-то создастся?

Ну, во-первых, как уже выше было замечено, есть функция last_insert_id. Но с ней (как уже опять-таки было замечено в комментах к более поздней заметке) могут быть коллизии, поскольку между двумя запросами вполне может вклиниться какой-нибудь еще INSERT (даже не обязательно к этой таблице относящийся).

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

INSERT INTO `fucking_table` (fucking_field_name, ...)
values (fucking_field_value, ...);

исполнять что-то вроде

INSERT INTO `fucking_table` (fucking_id, fucking_field_name, ...)
SELECT max(fucking_id)+1, fucking_field_value, ...
FROM `fucking_table`;
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Что-то я чушь какую-то написал.
Это, конечно, работает, но без толку - ID-то мы все равно не получим таким образом.
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ну у меня сейчас довольно тупо:

msq_add('unic',array('bro'=>mysql_escape_string($BRO),'ipn'=>$IPNUM,'login'...
$unic=ms("SELECT `id` FROM `unic` ORDER BY `id` DESC LIMIT 1"
Windows
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Право слово, ну что вы городите
>>INSERT (даже не обязательно к этой таблице относящийся).

Читайте ман

>> сгенерированный колонкой с AUTO_INCREMENT последним
>> запросом INSERT к серверу, на который ссылается
>> переданный функции указатель link_identifier. Если
>> параметр link_identifier не указан, используется
>> последнее открытое соединение.

Это единственно надежный вариант.

Всякого рода коллизии для языка пхп очень маловероятны, так как каждый скрипт открывает свое соединение и работает в рамках него.
Если вас не устраивает mysql_insert_id (а за мою многолетнюю практику быдлопрограмминга эта функция не разу не сбоила), то рекомендую параноидально после инсерта лочить таблицу и брать MAX(id).
Windows Firefox
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
/me подавлен собственным ничтожеством и не видит смысла дальнейшего существования в этом мире
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
я что-то пропустил?
Linux Safari
0
0
batc0h
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
>>Имею обыкновение советоваться с живыми умными людьми, а не с гуглом.
Я имел ввиду совем не это, а недавнюю параною в некоторых кругах по поводу собирания гуглом информации о деятельности пользователя. Ну да ладно
Linux
whois*: title='{#countryname}
Чертаново{Россия'> {city:|:{#countryname}|*:Чертаново{Россия|}}
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А при чем тут собирание информации? Гугль собирает информацию о том, что делает пользователь в интернете, а я - только что он делает на МОЕМ САЙТЕ. Имею полное право. Давайте запретим блогеру интересоваться деятельностью посетителей в его блоге.

всего комментариев: 33

<< предыдущая заметка следующая заметка >>


Include not found: `/home/www/lleo.me/blog/template/_reklamnaya_lirica.htm`