Как правильно сделать индексацию базы mysql?

Question

[Soho]

Есть таблица с 1млн строк, каждый день добавляется 30 тысяч строк, не сразу пачкой, а по очереди

по такому запросу нужно делать выборку

SELECT * FROM car WHERE url='328382164-audi-a4-2012-blue-WВA2UAF48H38K007347'

Запрос составляет 3.2 сек

Добавил индекс и сразу запрос отрабатывает за 4ms

Подскажите при добавлении 30к записей каждый день, будет ли переиндексироваться вся таблица? что повлияет на производительность, используется на таких объёмах индексация? или только негативно отразится

Либо есть вариант вставлять car_id в id строку c primary key и уже по нему искать, исключив индексацию

Comments

[shurshur]

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

30к записей в день - это в среднем 1 запись в три секунды. Это вообще не нагрузка. Если вам там не надо вставлять эти 30 тыс. разовой операцией за 0.01 секунды, то вообще не должно быть никаких проблем. Но я бы обратил внимание, что это почти 1 млн в месяц, так что за год таблица вырастет в 12 раз, и к этому надо заранее готовиться (иметь достаточно места).

[ThunderCat]

Либо есть вариант вставлять car_id в id строку c primary key и уже по нему искать, исключив индексацию

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

Answer 1

[Михаил]

При вставке записей в таблицу будет обновляться индекс - в него будет добавляться информация о новой записи. "Вся таблица" переиндексироваться не будет.
Индексация именно для таких объемов и предназначена. Таблицу в 1000 строк СУБД без проблем пережевывает фуллсканом и никто этого не замечает. Это, кстати, типичный сценарий - все работает нормально и без индексов, но с некоторого объема данных начинает стремительно тормозить и чем дальше, тем хуже. Вероятно именно это у вас и произошло.
Наличие индекса делает модификацию таблицы (вставки, обновления, удаления) чуть дороже т.к. нужно обновить содержимое и индексов тоже. Но пока у вас не десятки индексов и не сотни вставок в секунду - вряд ли эта проблема окажет существенное влияние.
PRIMARY KEY - по сути тот же индекс, только в нем чуть больше ограничений (всегда уникальный и поля в него входящие всегда NOT NULL). Если у вас поле url действительно однозначно идентифицирует запись - можно построить PRIMARY KEY по нему. Но вообще иметь первичный ключ по одному полю и набор индексов (в том числе уникальных) по другим полям - более чем нормально.

Answer 2

[rPman]

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

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

Answer 3

[mayton2019]

В этой задаче всё нормально и никаких видимых проблем нету. Обычно индекс работает синхронно с обновлением основной таблицы (кроме редких типов индексов основанных на текстовом поиске).

Единственное что здесь избыточно так это сам ключ.
'328382164-audi-a4-2012-blue-WВA2UAF48H38K007347'
его копия будет лежать и в таблице и в индексе. Я-бы поискал какие-то закономерности.
Если вся таблица созавалась под audi - то стоит-ли держать еще признак рядом?

Если бы база была класса Oracle и ключ - композитный то я-бы разбил его на два-три подключа
и использовал-бы compressed index. Тогда марки машин можно было сжать и уменьшить объем индекса.