Mysql PRIMARY KEY и COUNT(*)

[scorpion-ds]

Есть таблица:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `avatar_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `auth_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'auth_key',
  `role` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'user',
  `user_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Client',
  `profile_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'new',
  `completed_verified` tinyint(1) NOT NULL DEFAULT '0',
  `is_indexable` tinyint(1) NOT NULL DEFAULT '1',
  `user_version` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Live',
  `register_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `singup_source` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Desktop',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `password_reset_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_verification_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `new_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_unique` (`email`),
  UNIQUE KEY `Index 5` (`email_verification_token`),
  KEY `profile_status_user_role` (`profile_status`,`role`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=25298949 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

В гриде для построения постраничной навигации используется запрос с COUNT.

Недавно в БД были импортированны данные, в результате в таблице стало ~20 миллионов записей, после этого сайт по сути лег, так как не мог выполнять запросы с COUNT.

sql> SELECT COUNT(*) FROM users
[2018-11-15 11:50:40] 1 row retrieved starting from 1 in 1 m 29 s 806 ms (execution: 1 m 29 s 744 ms, fetching: 62 ms)

EXPLAIN показывал, что в качестве ключа он почему-то использует индекс "email_unique".

В качестве эксперемента, я создал еще один уникальный индекс по полю id и БД ожила:

sql> SELECT COUNT(*) FROM users USE INDEX (`PRIMARY`)
[2018-11-15 12:22:56] 1 row retrieved starting from 1 in 2 m 42 s 355 ms (execution: 2 m 42 s 333 ms, fetching: 22 ms)
sql> SELECT COUNT(*) FROM users USE INDEX (`id`)
[2018-11-15 12:23:32] 1 row retrieved starting from 1 in 3 s 434 ms (execution: 3 s 415 ms, fetching: 19 ms)

Вопрос, разве PRIMARY нельзя использовать в качестве индекса?

P.S.: На проекте я недавно, он Yii и имеет много проблем, сейчас пробуем его заставить работать по нормальному.

 

[флоппик]

Вопрос, разве PRIMARY нельзя использовать в качестве индекса?

Короткий ответ: проблема из-за обилия текстовых полей типа varchar() в таблице. Выдели их в отдельную, поменяй тип на TEXT, например, или продолжай использовать явный дополнительный индекс.
Длинный ответ: Иннодб хранит первичный ключ вместе с данными, и кластеризует его по файлам вместе с данными. Варчары хранятся внутри табличного пространства (TEXT выделяются в отдельные файлы), и на твоих обьемах превращают первичный ключ в месиво.

 

[c0dex]

Делать COUNT(*) на InnoDB это печаль в целом. Смотри ответ Флоппа выше. У меня есть табличка с 32kk записями под InnoDB, там COUNT() проходит за 5-6с для первого запроса, дальше он кешируется. Поля текстовые лежат отдельно.

Удаляешь данные с таблицы?

 

[scorpion-ds]

проблема из-за обилия текстовых полей типа varchar() в таблице.

Я вот так и думал, что из-за них будет проблема, там изначально даже все поля с датами и те что сейчас "tinyint(1)" были VARCHAR (собственно все, кроме ID), их я поправил, но остальные поля изменить мне не дали, хотя я хотел просто уменьшить их длину (не думаю, что это сильно бы помогло).

Не уверен, что мне дадут переносить их в отдельную таблицу, а просто TEXT их сделать, поменяет что-то?

Длинный ответ: Иннодб хранит первичный ключ вместе с данными, и кластеризует его по файлам вместе с данными. Варчары хранятся внутри табличного пространства (TEXT выделяются в отдельные файлы), и на твоих обьемах превращают первичный ключ в месиво.

За это спасибо, я примерно так и вычитал уже, теперь будет как аругментировать почему так вышло.

 

[scorpion-ds]

Удаляешь данные с таблицы?

Нет, таблица будет только расти, в билажайшее время будет до 65 лямов, я тут в печали, очевидно что проект не готов к таким размерам БД, еще тут динамический sitemap.xml по всем этим записям (разбит по 50000, я уменьшил до 15тыс.), собственно этот сайтмап и админка, где выводится количество пользователей и постраничная навигация и ложили сайт, после импорта пользователей, а клиент хочет начинать рекламную компанию ...

 

[WMix]

покажи sql или ты count(*) на всю таблицу делаешь?

 

[scorpion-ds]

покажи sql или ты count(*) на всю таблицу делаешь?

Да, на всю, для получения общего число пользователей. Ограничение в некоторых местах можно применить, но это будет все равно 99% от общего числа пользователей, это имеет мало эффекта, я пробовал.

SELECT COUNT(*) FROM users;

Еще вот такой запрос есть:

SELECT `id`, `slug`, `updated_at` FROM `users` WHERE (profile_status = 'active') AND (role = 'user')

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

 

[WMix]

первую часть можно закэшить, во вторую limit добавить

 

[pr1]

Что если делать постраничную навигацию без COUNT?

 

[scorpion-ds]

Что если делать постраничную навигацию без COUNT?

Думаю это крайний вариант, я так еще не делал, но уже задумываюсь над этим.

Еще проблема из той же БД.

Запрос зависает на более чем несколько минут, я прерываю его:

SELECT id, slug, created_at FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

Этот запрос выполняется нормально, хотя и медленно:

SELECT id FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

Если взять id из второго запроса и сделать такой запрос:

SELECT id, slug, created_at FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

то он выполняет мгновенно. В общем-то тут понятно почему, обращение напрямую по id.

Но почему, такая проблема с первым запросом?

 

[pr1]

Думаю это крайний вариант, я так еще не делал, но уже задумываюсь над этим.

Еще проблема из той же БД.

Запрос зависает на более чем несколько минут, я прерываю его:

SELECT id, slug, created_at FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

Этот запрос выполняется нормально, хотя и медленно:

SELECT id FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

Если взять id из второго запроса и сделать такой запрос:

SELECT id, slug, created_at FROM `users` WHERE (profile_status = 'active') AND (role = 'user') LIMIT 10 OFFSET 19765000;

то он выполняет мгновенно. В общем-то тут понятно почему, обращение напрямую по id.

Но почему, такая проблема с первым запросом?

Потому что в 3-ем запросе уже используется кэш. Попробуйте сбросить кэш, будет так же зависать.

 

[pr1]

Вообще нет смысла показывать все записи (при больших объемах данных). Тот же гугл в выдаче может писать, примерно найдено 2000000 записей, но в реале по запросу будет 20-30 страниц

 

[c0dex]

@scorpion-ds, пагинация больших таблиц дело очень больное, особенно если это InnoDB. Лучше всего гонять запросы с SQL_NO_CACHE, чтобы они не кешировались.

Я например сделал для себя вывод, что если мы выводим log-подобные данные из не партицированных таблиц, где могут быть миллионы записей (у меня было до лярда оО), которые при этом не удаляются со временем (твой и мой случай), то можно сделать так:

1. Из мета данных таблицы (information_schema) получаем количество строчек в ней (данные будут отличаться от count(*) и будут примерными на 95% где-то). Это дает нам простой способ считать, что у нас есть N пользователей из 32kk.
1.2 Или вводим внутренний счетчик строк (отдельная таблица, редис, мемкеш, файлик, да что угодно) и на его основе считаем число строк, но надо поддерживать актуальность данных, зависит от частоты вставок в таблицу.

При условии, что ты знаешь число строк и нужную тебе "страницу", ничто не мешает тебе сделать запрос вида:

SELECT SQL_NO_CACHE * FROM `log` WHERE id BETWEEN 29671851 AND 29671931 ...

У меня подобный запрос вылетает без кеша и прочих оптимизаций за ~0.0006с. Использует первичный ключ, который при моих настройках и объемах ОЗУ сервера БД полностью лежит в памяти. База лежит не на самом медленном диске в AWS правда.

 

[scorpion-ds]

Не, очистка кеша

SELECT SQL_NO_CACHE * FROM `log` WHERE id BETWEEN 29671851 AND 29671931

Это если id последовательные, но есть дыры в миллионы записей, от неудачных импортов данных. Собственно от прошлого разработчика у сайт мапа была пагинация по ID, но из-за удаления было много дыр на "страницах".

В админкие уже одобрен отказ от пагинации, будут последниее N записей и поиск, кторый наверно будет на elasticsearch, на фронте поиск уже на нем, но клиенту нужен сайтмап всех страниц и если на первых миллионах OFFSET LIMIT работают, то к "концу таблицы" траблы.

Тут собственно такие данные, что никогда не будут сводиться в общую статистику, каждый пользователь это профиль с карьерой человека, его социальными сетями и доп. текстовыми полями, я бы это все на MongoDB сделал, если бы изначально проектировал, хотя хз во что бы я тогда уперся.

Еще вот такой ключ меня смущает:

mysql> EXPLAIN SELECT `id`, `slug`, `updated_at` FROM `users` WHERE (profile_status = 'active') AND (role = 'user');
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref         | rows    | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+---------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | profile_status_user_role | profile_status_user_role | 1534    | const,const | 9192525 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+---------+----------+-------+

1534 - кажется слишком большим, но его длина понятна, там varchar в качестве индексных полей.

 

[c0dex]

@scorpion-ds, я спрашивал про удаления, ты сказал, что нет... Дальше есть варианты, но думай сам

Vsrchar изначально всегда был 255, что вы там с ключом сделали я хз.

 

[флоппик]

если мы выводим log-подобные данные из не партицированных таблиц, где могут быть миллионы записей (у меня было до лярда оО), которые при этом не удаляются со временем (твой и мой случай)

... то их нужно партиционировать обязательно.

 

[флоппик]

@scorpion-ds, не везет тебе с проектами, конечно. )

 

[scorpion-ds]

я спрашивал про удаления, ты сказал, что нет

ага, я не сообразил с чем связан вопрос, да и удаления в перспективне массового не будет, но среди данных много СПАМа, так что думаю клиент таки захочет чистить данные, даже если сейчас он говорит, что удалять ничего не будет.

не везет тебе с проектами, конечно. )

Да этот проект капец какой-то, вроде проекту год, но но совсем не готов к такому объему данных, а формально он два месяца назад ушел в релиз, плюс еще он идет по "фиксет-прайс", так что на подумать особо времени нет.

 

[Breeze]

В порядке пятничной шутки.
Делаешь таблицу users_pagination(user_id int, pagination_id int) где сохраняешь порядковый номер
1. Для получения count делаешь select pagination_id from .. order by pagination_id desc limit 1
2. select user_id .. where pagination_id between x and y будет работать

 

[fixxxer]

клиенту нужен сайтмап всех страниц и если на первых миллионах OFFSET LIMIT работают, то к "концу таблицы" траблы.

а зачем для этого offset?
делай так же, как делают кнопочку "загрузить еще"
where id > $lastId order by id limit N