Это теоретический вопрос. Извините, но у меня нет данных рабочих таблиц, чтобы показать, я попытаюсь импровизировать с теоретическим примером. Использование 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;
объяснить показывает:
Я попытался выбрать из таблицы продуктов и присоединиться к 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 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 = '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
Спасибо за ссылку. Это было хорошее чтение (опять же). Я помню этот сайт несколько лет назад. Я не понимаю, как можно реализовать его предложение о разбиении на страницы, когда вы, например, упорядочиваете список по ProductName. Установка следующего возможного идентификатора на самом деле не помогает, так как вам нужно изменить порядок списка, чтобы узнать, какой будет следующий идентификатор ПОСЛЕ заказа
Я надеюсь, что с учетом буферного пула у вас есть как минимум 6 ГБ ОЗУ.
После последнего заказа на текущей странице. Или выберите одну дополнительную строку, чтобы увидеть, есть ли еще одна страница. Пагинация И, да, "запомнить, где вы остановились" может быть невозможно из-за Присоединения.
Похоже, что в 4 базах данных есть таблица Product2Section
? Какой актуален?
Хм... Я не вижу, где Оптимизатор избавился от DISTINCT
, и даже если бы это было так. Пожалуйста, укажите EXPLAIN FORMAT=JSON SELECT...
(для одного из запросов). Между тем, обратите внимание, что оптимизатор выбрал Product2Section
в качестве «первой» таблицы, независимо от порядка FROM/JOIN.
Если подумать, то "остановлено", вероятно, будет хорошо работать для вашего запроса. Это устранит серьезные накладные расходы, с которыми вы столкнулись. (Боты досаждали мне около 15 лет назад, и это привело меня к поиску обходного пути.)
Два отдельных индекса столбца на 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%';
Комментарии перемещены в чат ; пожалуйста, не продолжайте обсуждение здесь. Прежде чем публиковать комментарий под этим, пожалуйста, ознакомьтесь с целями комментариев . Комментарии, которые не требуют разъяснений или предложений по улучшению, обычно относятся к ответу , к Meta Stack Overflow или в чату переполнения стека. Комментарии, продолжающие обсуждение, могут быть удалены.