Оптимизация процесса поиска MySQL

Первый метод будет быстрее – но ИМО это не правильный способ сделать это. Я согласен с техваном об этом.

Я бы рекомендовал сохранить item_id как есть, но добавьте два дополнительных поля один для кода и один для размера, тогда вы можете сделать:

 select * from items where item_code = 'S' and item_size = 'm' 

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

 select * from items where item_code = 'S' and item_size IN ('m','s') 

Перенесите db следующим образом:

 alter table items add column item_code varchar(1) default ''; alter table items add column item_size varchar(1) default ''; update items set item_code = SUBSTRING(item_id, 1, 1); update items set item_size = SUBSTRING(item_id, 2, 1); 

Изменения в коде должны быть одинаково просты в добавлении. Долгосрочная выгода будет стоить усилий.

Для сценария 2 – это не эффективный способ хранения и извлечения данных из базы данных. При использовании таким образом база данных действует только как механизм хранения, путем кодирования нескольких данных в поля, которые исключают возможность использования реляционной части базы данных.

То, что вы должны делать в этом случае, – это иметь другую таблицу, назовите ее «items_collected». Схема была бы

 CREATE TABLE items_collected ( id int(11) NOT NULL auto_increment KEY, userid int(11) NOT NULL, item_code varchar(10) NOT NULL, FOREIGN KEY (`userid`) REFERENCES `user`(`id`), FOREIGN KEY (`itemcode`) REFERENCES `items`(`item_code`) ); 

Внешние ключи гарантируют целостность ссылочной информации , важно иметь ссылочную целостность .

Тогда для примера, который вы даете, у вас будет несколько записей.

  user_id | username | items_collected U0001 | Alex | Ss001 U0001 | Alex | Tm002 U0002 | Daniel | Sb002 U0002 | Daniel | Tb001 U0003 | Michael | ... U0004 | Thomas | ... 

Первая оптимизация будет разделять идентификатор на три разных поля: один для типа, один для размера, один для окончания текущего идентификатора (независимо от конечного). Если вы действительно хотите сохранить текущую структуру, немедленно выполните результат ( Опция 1).

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

Шаг 2 – создать индекс для каждого столбца. Помните, что mysql использует только один индекс для каждой таблицы для каждого запроса. Поэтому, если вам действительно нужны быстрые запросы, и ваши запросы сильно отличаются от этих свойств, вам может понадобиться создать индекс (тип, размер, конец), (тип, окончание, размер) и т. Д.

Например, запрос с

 select * from items where type = s and size = s and ending = 001 

Может извлечь выгоду из индекса (тип, размер, окончание), но:

 select * from items where size = s and ending = 001 

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

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

Для model , size и id необходимо иметь три столбца и индексировать их следующим образом:

 CREATE INDEX ix_1 ON (model, size, id) CREATE INDEX ix_2 ON (size, id) CREATE INDEX ix_3 ON (id, model) 

Затем вы сможете эффективно искать любой поднабор параметров:

• model-size-id , model-size и model ix_1 будут использовать ix_1 ;
• size-id и запросы size будут использовать ix_2 ;
• model-id и id будут использовать ix_3

Индекс на вашем столбце, как он сейчас, эквивалентен ix_1 , и вы можете использовать этот индекс для эффективного поиска в соответствующих условиях ( model-size-id model-size , model-size-id model-size и model ).

На самом деле существует определенный путь доступа, называемый INDEX SKIN SCAN который может использоваться для поиска в непервых столбцах составного индекса, но MySQL не поддерживает его AFAIK.

Если вам нужно придерживаться своего текущего дизайна, вам нужно проиндексировать поле и использовать такие запросы, как:

 WHERE item_id LIKE @model || '%' WHERE item_id LIKE @model || @size || '%' WHERE item_id = @model || @size || @id 

Все эти запросы будут использовать индекс, если таковой имеется.

Нет необходимости вводить несколько запросов.

Мне комфортно, что вы создали свой item_id для поиска с помощью теста «Начинается с». Индексы будут быстро решены для вас.

Я не знаю MySQL, но в MSSQL, имеющем индекс в столбце «Размер», который имеет только выбор S, M, L, скорее всего, ничего не добьется, индекс не будет использоваться, поскольку значения, которые он содержит, недостаточно избирательны, то есть быстрее, чем просто просматривать все данные, а не «найти первую запись S в индексе, теперь получить страницу данных для этой строки …»

Исключение составляет то, где запрос покрывается индексом – то есть в индекс включены несколько частей предложения WHERE (и действительно, все из них, а также столбцы SELECT). В этом случае, однако, первое поле в индексе (в MSSQL) должно быть выборочным. Поэтому сначала поставьте столбец с самыми разными значениями в индексе.

Сказав, что если ваше приложение имеет список выбора для размера, цвета и т. Д., Вы должны иметь эти атрибуты данных в отдельных столбцах в записи – и отдельные таблицы со списками всех доступных цветов и размеров, а затем вы можете подтвердить, что цвет / Размер, заданный Продукту, фактически определен в Таблицах Цвет / Размер. Сбрасывает проблему с мусором / мусором!

Ваш item_selected должен находиться в отдельной таблице, чтобы он был «нормализован». Не хранить список с разделителями в одном столбце, хранить его с помощью отдельных строк в отдельной таблице

Таким образом, ваша таблица USERS будет содержать user_id и имя пользователя

Ваша новая, item_collected таблица будет содержать user_id и item_id (а также, возможно, дату покупки или номер счета-фактуры)

Затем вы можете сказать «Что сделал Алекс» (ваш дизайн имеет это значение), а также «Кто купил Ss001» (который в вашем дизайне потребует вспашки всех строк в вашей таблице USERS и расщепления элементов_сочетания, чтобы найти, какие из них содержал Ss001 [1])

[1] Обратите внимание, что использование LIKE на самом деле небезопасно, потому что у вас может быть item_id из «Ss001XXX», который будет соответствовать WHERE items_collected LIKE '% Ss001%'