Stdit

Есть ли смысл переубеждать руководство? Можно просто сообщить (лучше даже письменно, или хотя бы по email), по мнению технического специалиста, чем такой выбор чреват, какие дополнительные затраты он принесёт, и в каком случае. Если руководство примет решение оставить такой набор — то пускай он остается, когда начнутся упомянутые проблемы с обновлениями, то руководству придётся терпеть даунтаймы и оплачивать работу специалиста по устранению этих проблем, вплоть до переноса бд на другой сервер. С другой стороны, если это будет какой-нибудь никому не нужный «корпоративный портал» на 100 хитов в сутки, то он может спокойно себе работать десять лет на любом офисном включенном компьютере с виндой, и никому от этого плохо не станет.

Скорее всего запускается фулскан, потому что не находит правильного индекса. Что говорят EXPLAIN запросов? И это, я надеюсь, $counthis[posid] эскейпится перед вставкой в строку запроса, а то выглядит опасно.

Однажды ошибка в этом месте (делать одно глобальное соединение с БД) стоила мне целых выходных. Сразу оговорюсь, что использовался не mysql, а модуль node-postgres, но суть должна быть одинаковой. При большой нарузке длинные запросы блокировали сокет базы, и как следствие работу всего сервиса. Поэтому нужно было срочно переделывать модель на Connection Pool, который на этапе разработки как-то ушел из внимания, так как на одном коннекте с небольшой бд тестовых данных вроде бы все было хорошо. :)

Пул может организовать несколько параллельных подключений, которые не будут мешать друг другу. По окончании запроса подключение освобождается и уходит в пул. При отсутсвии свободных слотов в пуле запрос ставится в очередь до освобождения одного из слотов. Что касается доступа в БД, я использовал простейший вариант — непосредственный доступ через модуль и его параметры подключения по умолчанию. В каждом файле, который нуждается в БД, делается require драйвера БД и через него происходит обращение в базу. При использовании пула надо обязательно следить за освобождением соединений (функция done). Количество соединений в пуле задается опцией настроек модуля.

О том, как использовать пул в pg, можно почитать здесь.

Если все запросы к статистике заранее известны, может быть полезна предварительная агрегация путем денормализации на триггерах. К примеру, таблице page_stat назначить триггер after insert, который заносит запись в таблицу page_uid (page, uid), если такая там ещё не имеется. Запрашивать page_uid будет уже полегче, хотя всё равно потребуется пересчёт. Чтобы избежать и его в том числе, можно и на эту таблицу поставить триггер after insert, который в случае появления новой записи инкрементирует счетчик конкретной страницы в третьей таблице page_uniques_count (page, count). Те данные, что уже есть в базе, надо будет перед активацией триггеров соответственно прогнать запросом и занести в служебные таблицы. Ситуация осложняется введением запросов на количество уников за определенный (не произвольный) период времени (например, за день, месяц). Это реально организовать соответствующим образом, добавив в служебные таблицы дату, округленную до нужного интервала. Для каждого такого интервала может потребоваться отдельная таблица-счетчик, но более крупные интервалы можно получить из мелких (например, по месецам из ежедневной) путем классической агрегации через group by, которая в этом случае будет работать значительно быстрее, чем с сырыми данными из page_stat.

Создайте отношение в таком виде: [id, dt, pupil_id, disc_id, value]. Для каждого ученика и предмета на нужную дату ставится оценка. Потом получаете все необходимые данные и рисуете по ним график. Другой плюс такой формы хранения в том, что сможете построить общий график успеваемости по дисциплине без учёта учеников, и другие аналитические запросы.

Этой проблеме много лет, и о ней много написано на разных ресурсах (search). Помимо предлагаемых вариантов запросов разной степени жуткости, можно использовать денормализацию (занесение данных во вспомогательную таблицу-топ на триггерах или по крону).

У меня несколько вритуальных машин для тестов с разной конфигурацией сервера. При необходимости любую машину можно легко клонировать и ставить туда обновления или что-то ещё. В случае, если вам нужен не индивидуальный, а именно общий сервер, виртуальные машины можно поставить на один из постоянно работающих компьютеров и сделать их членами офисной сети.

Как вариант, можно завести дополнительное поле (sort) у билетов и заполнить его уникальными случайными числами. После этого сделать индекс по user_id и sort. Тест на таблице в миллион записей (InnoDB):

mysql> SELECT * from ticket WHERE user_id = 0 ORDER BY sort LIMIT 10;
+--------+------+---------+
| id     | sort | user_id |
+--------+------+---------+
| 923164 |    1 |       0 |
| 171274 |    2 |       0 |
| 217458 |    3 |       0 |
| 182627 |    4 |       0 |
| 183120 |    5 |       0 |
| 483756 |    6 |       0 |
| 210156 |    7 |       0 |
| 362920 |    8 |       0 |
| 311591 |    9 |       0 |
| 545096 |   10 |       0 |
+--------+------+---------+
10 rows in set (0.00 sec)

mysql> UPDATE ticket SET user_id = 1 WHERE id IN (923164, 171274, 217458);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * from ticket WHERE user_id = 0 ORDER BY sort LIMIT 10;
+--------+------+---------+
| id     | sort | user_id |
+--------+------+---------+
| 182627 |    4 |       0 |
| 183120 |    5 |       0 |
| 483756 |    6 |       0 |
| 210156 |    7 |       0 |
| 362920 |    8 |       0 |
| 311591 |    9 |       0 |
| 545096 |   10 |       0 |
| 230442 |   11 |       0 |
| 472816 |   12 |       0 |
| 138187 |   13 |       0 |
+--------+------+---------+
10 rows in set (0.00 sec)

Я на дев-машине обычно включаю логгинг всех запросов средствами SQL-сервера, и смотрю что там происходит.

Вот пример (MySQL):

$st = $pdo->prepare('SELECT * FROM user WHERE id=:id');
$id = 2;
$st->bindParam(':id', $id, PDO::PARAM_INT);
$st->execute();
$r = $st->fetchAll();

Выдаёт в MySQL-лог строчку

 6 Query	SELECT * FROM user WHERE id=2

При использовании PostgreSQL выглядит это несколько по-другому:

LOG:  duration: 2.669 ms  parse pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
LOG:  duration: 0.126 ms  bind pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
DETAIL:  parameters: $1 = '20', $2 = '0'
LOG:  duration: 0.118 ms  execute pdo_stmt_00000af1: SELECT * FROM "item" LIMIT $1 OFFSET $2
DETAIL:  parameters: $1 = '20', $2 = '0'
LOG:  duration: 0.172 ms  statement: DEALLOCATE pdo_stmt_00000af1

При желании можно SQL-лог направить в тот же файл, что и дебаговый PHP-лог, чтобы не переключаться между окнами терминалов с «tail -f», которые следят за логами.

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

База спроектирована нормально, гораздо хуже было бы с таблицей вида (деталь, магаз1, магаз2, магаз3).
Попробуйте группировку, например так:

select номер_детали, group_concat(номер_магазина) as мазагины from детали group by номер_детали

Регулярные выражения — не самая лучшая идея (см EXPLAIN).
select * from t where str<='9'
select * from t where str in ('0','1','2','3','4','5','6','7','8','9')