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

Question

[maksam07]

Имеется база одноразовых товаров, для теста загрузил туда около миллиона единиц товара. И вот UPDATE в этой таблице длится 3 секунды. Первый SELECT после UPDATE тоже длится 3 секунды (потом кешируется до следующего UPDATE).
SELECT использую редко, а вот UPDATE'м приходится пользоваться часто, от этого и вопрос, как оптимизируются базы данных? Я не считаю, что миллион строк это много, но уверен, что должно работать явно не 3 секунды все это. Какие данные Вам нужно предоставить или есть какие-то общие советы по составлению БД?

UPDATE and SELECT

только SELECT (закеширован)

timer - время выполнения запроса

Comments

[Евгений Самсонов]

1 млн записей это немного. EXPLAIN что показывает?

[maksam07]

Евгений Самсонов,
EXPLAIN SELECT * FROM goods WHERE ... > 0
[0] => Array(
[id] => 1
[select_type] => SIMPLE
[table] => goods
[type] => ALL
[possible_keys] =>
[key] =>
[key_len] =>
[ref] =>
[rows] => 957671
[Extra] => Using where
)

[Евгений Самсонов]

Максим Компаниец, он у вас всю таблицу просматривает (957671 рядов), поэтому и долго. Сколько в итоге рядов в ответе? Что за условие в WHERE? И покажите CREATE TABLE. Нужно сделать, чтобы с использованием индекса выборка была, если это возможно

[maksam07]

Евгений Самсонов, в ответе на этот запрос без EXPLAIN всего лишь 5 строк возвращается в данный момент.
Вот в индексах я и не разобрался, как правильно это все реализовать.

CREATE TABLE goods ( 
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	
	_data JSON NOT NULL,
	_date INT UNSIGNED NOT NULL,
	_status TINYINT(1) DEFAULT '0' NOT NULL,
	_order_id INT UNSIGNED DEFAULT '0' NOT NULL,
	
	PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

Выборка и обновление строк идет по _order_id, в перспективе еще будут меняться данные в столбце _status

[Евгений Самсонов]

Максим Компаниец, создайте индекс на _order_id
https://dev.mysql.com/doc/refman/8.0/en/create-ind...

[maksam07]

Евгений Самсонов, да, действительно, теперь время выполнение выборки с ~3 секунд сменилось на ~0.0009 сек.
Я создал такой индекс: CREATE INDEX _ind_order_id ON goods (_order_id);
Ошибся в названии, у меня MariaDB

Еще в примерах вижу создание индекса для двух столбцов, типа:

CREATE UNIQUE INDEX websites_idx ON websites (website_name, server_name);

А если у меня будет выборка по _order_id и _status, то мне как раз таки нужно будет создать такой индекс:

CREATE INDEX _ind_status_and_order_id ON goods (_status,_order_id);

или нет?

Сейчас сделал выборку:

SELECT * FROM goods WHERE _status = 0 AND _order_id > 0

Без индекса _ind_order_id_and_status и она осталась такой же быстрой.
Из-за этого не очень понимаю, для каких столбцов нужны индексы и когда нужно создавать индекс для нескольких колонок

Нашел такую вот документацию: www.mysql.ru/docs/man/MySQL_indexes.html

[Евгений Самсонов]

Максим Компаниец, индекс занимает дополнительное место на диске и дает оверхед при операциях добавления и изменении записей. Поэтому их следует добавлять при необходимости, вешать на все поля, по которым идет выборка, не стоит.

> в ответе на этот запрос без EXPLAIN всего лишь 5 строк возвращается в данный момент.
_order_id селективное поле, если по нему возвращается небольшое кол-во записей от общего числа, поэтому в данном случае делать составной индекс бессмысленно. Запрос сначала по индексу (_order_id) выберет 5 записей, а уже по статусу отфильтрует без индекса — это не займет много времени. Если делать составной индекс, то селективное поле лучше ставить на первое место, так как порядок в индексе имеет значение (если говорим о стандартном BTREE)

Рекомендую почитать книгу "MySQL по максимуму", там есть информация и по пониманию EXPLAIN и индексам

[maksam07]

Евгений Самсонов, оставьте Ваш комментарий в виде ответа, отмечу решением. В принципе Rsa97 тоже написал по делу, но вы как-то более правильно начали отвечать
За книгу тоже спасибо, лишней не будет

[Евгений Самсонов]

Максим Компаниец, объединил все комментарии в 1 ответ

Answer 1

[Rsa97]

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

Answer 2

[Евгений Самсонов]

Чтобы понять, в чем проблема с запросом, нужно посмотреть план выполнения с помощью команды EXPLAIN. Вероятнее всего потребуется добавить индекс на поле, по которому идет выборка данных (условие в WHERE). Могу порекомендовать книгу "MySQL по максимуму", где подробнее написано об EXPLAIN и правильном использовании индексов

Answer 3

[d'Ivan]

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