Упорядочивание по полям из разных таблиц создает полное сканирование таблицы. Должен ли я объединять данные в одну таблицу?

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

У меня есть система, исторический дизайн которой имел таблицу ProductType, что-то вроде:

ID=1, Description = "Milk"
ID=2, Description = "Bread"
ID=3, Description = "Salt"
ID=4, Description = "Sugar"

и так далее.

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

Существует также таблица Product с такими полями, как:

ID, ProductTypeID, Name

В Product:Name нет описания типа продукта, поэтому «Бутылка молока 1 л» будет иметь такую ​​запись:

ID=101, ProductTypeID=1, Name = "bottle 1l"

и "Сахар пачка 1кг" будет:

ID=102, ProductTypeID=4, Name = "pack 1kg"

Вы поняли идею...

Система объединяет ProductType:Description и Product:Name, чтобы показать пользователям полные названия продуктов. Это создает систематическое наименование для всех продуктов, поэтому нет возможности определить продукт с таким названием, как «бутылка молока 1 л». Я знаю, что на английском это может быть трудно проглотить, но этот способ отлично работает с моим местным языком.

Прошли годы, база данных выросла до миллионов товаров.

Поскольку полнотекстовый индекс должен содержать все данные поиска в одной таблице, мне пришлось хранить ProductType:Description внутри таблицы Product в добавленном строковом поле, имеющем разные ключевые слова, связанные с продуктом, поэтому полнотекстовый поиск сможет найти все, что связано с товаром (тип, название, штрих-код, артикул и т. д.)

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

Чтобы показать правильный порядок продуктов, есть ORDER BY TypeDescription ASC, ProductName ASC после того, как ProductType таблица присоединена к Product запросам выбора. Из моего исследования я вижу, что база данных не может использовать индексы, когда порядок выполняется для полей из разных таблиц, поэтому она выполняет полное сканирование таблицы, чтобы добраться до нужных записей.

Во время пагинации в запросе есть ORDER и LIMIT 50000,100, которые занимают много времени. Есть разделы с лотами для товаров, так что упорядочивание и ограничение вызывают очень долгое полное сканирование таблицы.

Как бы вы поступили в такой ситуации? Изменить дизайн и сохранить все данные, связанные с запросом, в таблице Product? Чувствуется немного дублирование и не естественное решение. Или, может быть, есть другой способ решить это? Будет ли индекс по типу VARCHAR (название продукта) эффективным для скорости ЗАКАЗА? Или база данных по-прежнему будет выполнять полное сканирование таблицы?

Мой первый вопрос здесь. Не смог найти ответы по похожим случаям. Спасибо!

Я пытался поиграть с запросами, чтобы увидеть, будет ли работать упорядочение по полю VARCHAR с индексом, но EXPLAIN SELECT по-прежнему показывает, что запрос не использовал индекс и выполнялся WHERE :(

ОБНОВЛЯТЬ

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

У меня есть таблица, которая сопоставляет идентификаторы продуктов с идентификаторами разделов:

CREATE TABLE `Product2Section` (
  `SectionId` int(10) unsigned NOT NULL,
  `ProductId` int(10) unsigned NOT NULL,
  KEY `idx_ProductId` (`ProductId`),
  KEY `idx_SectionId` (`SectionId`),
  KEY `idx_ProductId_SectionId` (`ProductId`,`SectionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

Запрос (после удаления всех не относящихся к вопросу полей):

SELECT DISTINCT
    DRIVER.ProductId AS ID,
    p.*
FROM
    Product2Section AS DRIVER
LEFT JOIN Product p ON
    (p.ID = DRIVER.ProductId)
WHERE
    DRIVER.SectionId IN(
544,545,546,548,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,566,567,568,570,571,572,573,574,575,1337,1343,1353,1358,1369,1385,1956,1957,1964,1973,1979,1980,1987,1988,1994,1999,2016,2020,576,577,578,579,580,582,586,587,589,590,591,593,596,597,598,604,605,606,608,609,612,613,614,615,617,619,620,621,622,624,625,626,627,628,629,630,632,634,635,637,639,640,642,643,644,645,647,648,651,656,659,660,661,662,663,665,667,669,670,672,674,675,677,683,684,689,690,691,695,726,728,729,730,731,734,736,741,742,743,745,746,749,752,758,761,762,763,764,768,769,771,772,773,774,775,776,777
    )
ORDER BY
    p.ProductName ASC
LIMIT 500900,100;

объяснить показывает:

идентификатор select_type стол тип возможные_ключи ключ key_len ссылка ряды Дополнительный 1 ПРОСТОЙ ВОДИТЕЛЬ индекс idx_SectionId idx_ProductId_SectionId 8 НУЛЕВОЙ 589966 Использование где; Использование индекса; Использование временного; Использование файловой сортировки 1 ПРОСТОЙ п eq_ref ОСНОВНОЙ, idx_ID НАЧАЛЬНЫЙ 4 4project.DRIVER.ProductId 1 Использование где

Я попытался выбрать из таблицы продуктов и присоединиться к Product2Section, чтобы отфильтровать результаты, но получаю те же результаты:

SELECT DISTINCT
    p.ID,
    p.ProductName
FROM
    Product p
LEFT JOIN 
    Product2Section p2s ON (p.ID=p2s.ProductId)
WHERE
    p2s.SectionId IN(
544,545,546,548,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,566,567,568,570,571,572,573,574,575,1337,1343,1353,1358,1369,1385,1956,1957,1964,1973,1979,1980,1987,1988,1994,1999,2016,2020,576,577,578,579,580,582,586,587,589,590,591,593,596,597,598,604,605,606,608,609,612,613,614,615,617,619,620,621,622,624,625,626,627,628,629,630,632,634,635,637,639,640,642,643,644,645,647,648,651,656,659,660,661,662,663,665,667,669,670,672,674,675,677,683,684,689,690,691,695,726,728,729,730,731,734,736,741,742,743,745,746,749,752,758,761,762,763,764,768,769,771,772,773,774,775,776,777
    )
ORDER BY
    p.ProductName ASC
LIMIT 500900,
100;

объяснять:

идентификатор select_type стол тип возможные_ключи ключ key_len ссылка ряды Дополнительный 1 ПРОСТОЙ p2s индекс idx_ProductId, idx_SectionId, idx_ProductId_SectionId idx_ProductId_SectionId 8 НУЛЕВОЙ 589966 Использование где; Использование индекса; Использование временного; Использование файловой сортировки 1 ПРОСТОЙ п eq_ref ОСНОВНОЙ, idx_ID НАЧАЛЬНЫЙ 4 4project.p2s.ProductId 1 Использование где

Не вижу выхода из этой ситуации.

БОЛЬШЕ ДАННЫХ

SELECT TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH + INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_NAME = 'Product2Section';
TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH + INDEX_LENGTH 7374 37 901120 589 821 41 75153408 (71,7 МБ) 7331 40 901120 0 0 65536
SELECT ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/POW(1024, 3), 2)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'databasename';


ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/POW(1024, 3), 2)  
4.27    

SHOW VARIABLES LIKE 'innodb_buffer%';

innodb_buffer_pool_chunk_size   134217728   
innodb_buffer_pool_dump_at_shutdown ON  
innodb_buffer_pool_dump_now OFF 
innodb_buffer_pool_dump_pct 25  
innodb_buffer_pool_filename ib_buffer_pool  
innodb_buffer_pool_instances    4   
innodb_buffer_pool_load_abort   OFF 
innodb_buffer_pool_load_at_startup  ON  
innodb_buffer_pool_load_now OFF 
innodb_buffer_pool_size 3758096384  
Rick's RoTs содержит несколько полезных советов и советов по разбивке на страницы больших наборов результатов. Использование LIMIT с большими смещениями всегда расточительно! Вы заявляете, что «пытались играть с запросами», поэтому я предлагаю вам добавить конкретный пример к вашему вопросу, вместо того, чтобы пытаться разбираться в абстрактных понятиях. Добавьте запрос, вывод объяснения и любой соответствующий DDL к вашему вопросу.
nnichols 18.02.2023 15:06

Спасибо за ссылку. Это было хорошее чтение (опять же). Я помню этот сайт несколько лет назад. Я не понимаю, как можно реализовать его предложение о разбиении на страницы, когда вы, например, упорядочиваете список по ProductName. Установка следующего возможного идентификатора на самом деле не помогает, так как вам нужно изменить порядок списка, чтобы узнать, какой будет следующий идентификатор ПОСЛЕ заказа

Alex 19.02.2023 12:05

Я надеюсь, что с учетом буферного пула у вас есть как минимум 6 ГБ ОЗУ.

Rick James 20.02.2023 21:29

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

Rick James 20.02.2023 21:30

Похоже, что в 4 базах данных есть таблица Product2Section? Какой актуален?

Rick James 20.02.2023 21:35

Хм... Я не вижу, где Оптимизатор избавился от DISTINCT, и даже если бы это было так. Пожалуйста, укажите EXPLAIN FORMAT=JSON SELECT... (для одного из запросов). Между тем, обратите внимание, что оптимизатор выбрал Product2Section в качестве «первой» таблицы, независимо от порядка FROM/JOIN.

Rick James 20.02.2023 21:59

Если подумать, то "остановлено", вероятно, будет хорошо работать для вашего запроса. Это устранит серьезные накладные расходы, с которыми вы столкнулись. (Боты досаждали мне около 15 лет назад, и это привело меня к поиску обходного пути.)

Rick James 20.02.2023 22:04
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
7
65
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Два отдельных индекса столбца на Product2Section не имеют смысла. Вы должны изменить свою соединительную таблицу на:

CREATE TABLE `Product2Section` (
  `SectionId` int unsigned NOT NULL,
  `ProductId` int unsigned NOT NULL,
  PRIMARY KEY (`SectionId`, `ProductId`),
  KEY `idx_ProductId_SectionId` (`ProductId`, `SectionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

Из документов:

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

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

Вы сказали:

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

Вы можете не увидеть разницы при выполнении отдельного запроса без конфликтов, но разница в рабочей среде с высокой конкуренцией может быть огромной из-за требуемого объема усилий.

Вам действительно нужно разместить 4 294 967 295 (int unsigned) секций? Возможно, 65 535, предоставленных smallint unsigned, будет достаточно?

Вы сказали:

Может в будущем изменить. Не думаю, что это как-то изменит производительность

Изменение SectionId на smallint уменьшит размер каждой записи индекса с 8 до 6 байт. Это уменьшение размера на 25%. Чем меньше, тем быстрее.

Почему вы используете LEFT JOIN? Тот факт, что вы можете изменить порядок таблиц в запросе на обратный, предполагает, что это должен быть INNER JOIN.

Правильно ли настроен пул буферов или он настроен по умолчанию? Пожалуйста, запустите ANALYZE TABLE Product2Section;, а затем предоставьте вывод:

SELECT TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH + INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_NAME = 'Product2Section';

И:

SELECT ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/POW(1024, 3), 2)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

И:

SHOW VARIABLES LIKE 'innodb_buffer%';

Другие вопросы по теме