Блог

Главная - Вопросы по программированию - Разделение данных или использование УНИКАЛЬНОГО индекса для оптимизации

Разделение данных или использование УНИКАЛЬНОГО индекса для оптимизации

#mysql #optimization #query-optimization

Вопрос:

У меня есть столик;

 Orders
* id INT NN AN PK
* userid INT NN
* is_open TINYINT NN DEFAULT 1
* amount INT NN
* desc VARCHAR(255)

и запрос SELECT * FROM orders WHERE userid = ? AND is_open = 1; , который я часто выполняю. Я хотел бы оптимизировать базу данных для этого запроса, и в настоящее время у меня есть два варианта;

  • Переместите закрытые ордера ( is_open = 0 ) в другую таблицу, так как текущие открытые ордера будут относительно меньше закрытых ордеров, тем самым сведя к минимуму количество строк для сканирования при поиске
  • Установите ограничение уникального ключа: ALTER TABLE orders ADD CONSTRAINT UNIQUE KEY(id, userid);

Я не знаю, как будет работать последнее, и я знаю, что первое поможет производительности, но я не знаю, является ли это хорошим подходом с точки зрения лучших практик.

Любые другие идеи будут оценены по достоинству.

Ответ №1:

Таблица имеет значение orders ; для каждого может быть несколько открытых/закрытых orders userid .

  • WHERE userid = ? AND is_open = 1 выиграли бы от любого из этих «составных» индексов: INDEX(userid, is_open) или INDEX(is_open, user_id) . Выбор того, что лучше, зависит от того, какие другие запросы могут извлечь из одного больше пользы, чем из другого.
  • Перемещение «закрытых» ордеров на другую таблицу, безусловно, является допустимым вариантом. И это поможет производительности. (Обычно я не рекомендую это делать только из-за неуклюжего кода, необходимого для перемещения строк и/или поиска в обеих таблицах в тех немногих случаях, когда это необходимо.)
  • Я не вижу в этом никакого преимущества UNIQUE(id, userid) . Предположительно id , уже «уникален» из-за того, что является PRIMARY KEY ? Кроме того, в составном индексе сначала будет проверен первый столбец; именно это уже делает PK.
  • Другой подход… AUTO_INCREMENT PK приводит к тому, что дерево данных является примерно хронологическим. Но вы обычно лезете в стол с помощью userid ? Чтобы сделать это более эффективным, измените PRIMARY KEY(id), INDEX(userid) на PRIMARY KEY(userid, id), INDEX(id) . (Однако… не зная других запросов, касающихся этой таблицы, я не могу сказать, обеспечит ли это значительное общее улучшение.)
  • Это может быть даже лучше: 
       PRIMARY KEY(userid, is_open, id),  -- to benefit many queries
  INDEX(id)   -- to keep AUTO_INCREMENT happy
  • Затраты на дополнительный индекс (на выполнение операций записи) обычно с лихвой компенсируются ускорением выбора.

Комментарии:

1. Это orders столик. У пользователя может быть 2 открытых ордера, поэтому комбинация user_id и is_open не является уникальной. Это закрытый «заказ», а не «пользователь». Это похоже на совершенную транзакцию электронной коммерции. Я думаю , UNIQUE KEY(id, userid) что если поиск сгруппирован по userid , то можно выполнить двоичный поиск, чтобы определить первое вхождение заданного userid значения, а затем его можно отфильтровать is_open = 1 .

2. GROUP BY нельзя использовать индекс (id, userid) , если он уже не отфильтрован ( WHERE ) одним id .

3. @Ozichukwu — Я очистил свой ответ, основываясь на нескольких orders для каждого user .

Ответ №2:

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

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

Вашим лучшим решением, скорее всего, будет добавить индекс user_id , чтобы MySQL мог сразу перейти к требуемому идентификатору пользователя и искать только эти строки. Вы могли бы получить дополнительный прирост за счет индексации user_id и is_open вместо этого, но дополнительная выгода, скорее всего, будет небольшой.

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

Метки: