Блог

Предыстория:

У меня есть схема Mysql со следующей структурой

 @Data
public class DBQueryEvaluation {
    //primary key
    private final Long id;

    //The combination of a 'query' and an 'evaluationId' will always be unique.
    private final Long evaluationId;
    private final String query;

    private final Date createdAt;
    private final Date updatedAt;
}
  

Ограничение:

Комбинация ‘query‘ и ‘evaluationId‘ всегда будет уникальной.

Для данного evaluationId может быть довольно много запросов. Всего в таблице 5 миллионов записей. (~ 50 000 запросов на evaluationId, при 100 таких оценках = 5 миллионов записей).

Цель:

Хочу выполнить подсчет (записей) для данного evaluationId.

Вопросы:

Учитывая, что мощность evaluationId довольно низкая (один и тот же evaluationId повторяется для ~ 50 тыс. записей) :

1. Рекомендуется ли здесь индексировать ‘evaluationId’? Ожидаемая реализация BTree должна быть способна обеспечить подсчет в порядке миллисекунд. (< 10 мс)
2. Каковы могут быть возможные недостатки, если таковые имеются, для индексации такого атрибута с низкой мощностью?
3. Каковы были бы другие наилучшие подходы для получения count (*).

=== Обновление ===

• Я ожидаю полностью согласованного представления. Никаких приближений.
• Обновления могут быть применены поверх существующих строк.

1. Рекомендуется ли здесь индексировать ‘evaluationId’? Ожидаемая реализация BTree должна быть способна обеспечить подсчет в порядке миллисекунд. (< 10 мс)

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

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

Например, если размер блока равен 10, а 50 000 записей имеют идентификатор оценки, то необходимо прочитать около 5555 блоков. Сравните это по крайней мере с 500 000 блоками, которые необходимо прочитать при сканировании таблицы. Очевидно, что в базах данных есть методы оптимизации, которые усложнили бы справедливое сравнение, и поэтому имеет смысл просто попробовать это.

2. Каковы могут быть возможные недостатки, если таковые имеются, для индексации такого атрибута с низкой мощностью?

Роль мощности зависит от того, сколько записей данных умещается в одном блоке (т.е. <= recordsize / размер блока). Если это число приближается к мощности, то преимущество индекса исчезнет.

3. Каковы были бы другие наилучшие подходы для получения count (*).

Вы могли бы переоценить, насколько важно иметь точный подсчет, когда такие подсчеты составляют порядка 50 000, и зная, что в ту же секунду после получения подсчета, возможно, уже были новые вставки / удаления. Имеет ли значение, действительно ли это 49 756, а не 49 695?

Если приближение в порядке, то запустите запланированное пакетное задание, которое выполняет подсчет всех идентификаторов оценки и сохраняет это в отдельной таблице «count» (в которой будет около 100 записей). В зависимости от ваших потребностей, вы могли бы запланировать его выполнение один раз в день, в час, … в зависимости от оборота и необходимой точности. Тогда вы получаете молниеносную скорость ценой незначительной неточности.

Для повышения точности вы могли бы объединить приведенную выше таблицу «count» с триггером вставки / удаления в таблице данных, который вставил бы эффект этого изменения (в виде значения 1 или -1) в таблицу журнала. Затем запрос количества записей будет использовать таблицу «count» в качестве отправной точки и изменять результат на основе этих 1 / -1 в таблице журнала. Приведенное выше задание очищало бы журнал всякий раз, когда оно выполняется.