Правило ESR и порядок полей в составном индексе

В прошлом уроке мы перебрали виды индексов и пообещали отдельный разговор про самый частый из них, составной. С ним часто ошибаются так: поля выбраны верно, а стоят в неудачном порядке. Индекс вроде бы используется, explain показывает IXSCAN, а запрос всё равно читает тысячи лишних записей и сортирует в памяти. Сегодня разберём правило, по которому порядок полей выбирают с первого раза.

Картотека заказов

Представь шкаф с карточками заказов. Менеджер каждый день спрашивает одно и то же: «покажи доставленные заказы дороже девяти тысяч, по дням, первые десять».

Если ящики шкафа подписаны по статусу, а внутри ящика карточки лежат по дням, задача решается за минуту. Открываешь ящик «доставлен», идёшь по карточкам от первого дня и откладываешь те, что дороже девяти тысяч. Набрал десять и закрыл ящик.

А если внутри ящика карточки лежат по сумме? Тогда нужные карточки найти легко, они в конце ящика. Но они перемешаны по дням. Придётся вынуть все две с половиной тысячи на стол, разложить по дням и только потом взять первые десять.

Составной индекс работает как такой шкаф: первое поле это ящики, второе это порядок карточек в ящике, третье порядок внутри одинаковых значений второго. Порядок полей решает, придётся ли раскладывать карточки на столе.

Коллекция для опытов

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

use shopstats
const statuses = ['new', 'paid', 'shipped', 'delivered']
const docs = []
for (let i = 0; i < 100000; i++) {
  docs.push({ _id: i, customer: 'c' + (i % 2000), status: statuses[Math.floor(i / 7) % 4], amount: (i * 7919) % 10000, day: i % 365 })
}
db.orders.insertMany(docs)

У каждого заказа покупатель из двух тысяч, один из четырёх статусов, сумма от 0 до 9999 и номер дня в году. Доставленных заказов 24997, а доставленных дороже девяти тысяч 2502.

В уроке про explain мы писали функцию short. Сделаем её чуть умнее: пусть она показывает всю цепочку этапов плана, от внешнего к внутреннему.

const stages = (s) => s.stage + (s.inputStage ? ' > ' + stages(s.inputStage) : '')
const plan = (cursor) => {
  const s = cursor.explain('executionStats').executionStats
  return { stages: stages(s.executionStages), nReturned: s.nReturned, keys: s.totalKeysExamined, docs: s.totalDocsExamined }
}

Три индекса на один запрос

Вот запрос менеджера:

const q = () => db.orders.find({ status: 'delivered', amount: { $gte: 9000 } }).sort({ day: 1 }).limit(10)

В нём три вида условий, и у каждого своя буква:

• E, equality, равенство: status: 'delivered', поле сравнивается с одним значением.
• S, sort, сортировка: sort({ day: 1 }).
• R, range, диапазон: amount: { $gte: 9000 }, поле попадает в промежуток.

Построим три индекса с этими полями в разном порядке:

db.orders.createIndex({ status: 1, amount: 1, day: 1 })
db.orders.createIndex({ status: 1, day: 1, amount: 1 })
db.orders.createIndex({ amount: 1, status: 1, day: 1 })

Чтобы сравнить их честно, заставим сервер выполнить запрос по каждому через hint. Метод принимает имя индекса:

plan(q().hint('status_1_amount_1_day_1'))
plan(q().hint('status_1_day_1_amount_1'))
plan(q().hint('amount_1_status_1_day_1'))

{
  stages: 'FETCH > SORT > IXSCAN',
  nReturned: 10,
  keys: 2502,
  docs: 10
}
{ stages: 'LIMIT > FETCH > IXSCAN', nReturned: 10, keys: 12, docs: 10 }
{
  stages: 'FETCH > SORT > IXSCAN',
  nReturned: 10,
  keys: 3502,
  docs: 10
}

Сведём в таблицу:

Индекс	Порядок	Этапы	Прочитано ключей
status, amount, day	E, R, S	FETCH > SORT > IXSCAN	2502
status, day, amount	E, S, R	LIMIT > FETCH > IXSCAN	12
amount, status, day	R, E, S	FETCH > SORT > IXSCAN	3502

Ради десяти заказов второй индекс прочитал двенадцать ключей, первый в двести раз больше, третий почти в триста.