Оптимизация Запроса ORDER BY на большой таблице MySQL
Я не эксперт, но, думаю, в sed сначала нужно добавить следующую строку в пространство шаблонов, используя bij, используя «N». Из раздела «Пространство многострочных шаблонов» в «Advanced sed Commands» книги sed & amp; awk (Дейл Догерти и Арнольд Роббинс; O'Reilly 1997; страница 107 в превью ):
Многострочная команда Next (N) создает пространство многострочного паттерна прочитав новую строку ввода и добавив ее к содержимому пространства шаблона. Исходное содержимое пространства шаблона и новая строка ввода разделены новой строкой. Встроенный символ новой строки можно сопоставить в шаблонах с помощью escape-последовательности "\ n". В многострочном шаблонном пространстве метасимвол «^» соответствует самому первому символу шаблонного пространства, а не символу (ам) после любой встроенной новой строки (ей). Точно так же, «$» соответствует только последней новой строке в пространстве шаблона, а не любой внедренной новой строке. После выполнения команды Next управление передается последующим командам в сценарии.
Из man sed:
[2addr] N
Присоединить следующую строку ввода к пространству шаблона, используя встроенный символ новой строки, чтобы отделить добавленный материал из оригинального содержания. Обратите внимание, что текущий номер строки изменяется.
Я использовал это для поиска (нескольких) плохо отформатированных файлов журналов, в которых строка поиска может быть найдена в «потерянной» следующей строке.
2 ответа
Попробуйте переписать свой запрос следующим образом:
SELECT p.*, i.type, i.width, i.height,
(SELECT name FROM Location_State WHERE id = p.state_id) AS state,
(SELECT name FROM Location_Region WHERE id = p.region_id) AS region,
(SELECT start_date FROM Promotion WHERE id = p.promotion_id) AS promotion_start_date,
(SELECT end_date FROM Promotion WHERE id = p.promotion_id) AS promotion_end_date
FROM parts p
LEFT JOIN
image i
ON i.id = p.image_id
WHERE EXISTS (
SELECT NULL
FROM Parts_Category pc
WHERE pc.category_id = '40'
AND pc.parts_id = p.id
UNION ALL
SELECT NULL
FROM Parts_Category pc
WHERE pc.main_category_id = '40'
AND pc.parts_id = p.id
)
AND p.status = 'A'
ORDER BY
p.status DESC, p.level DESC, p.warehouse DESC, p.updated DESC
LIMIT 15
Для эффективной работы вам потребуются следующие индексы:
parts (status, level, warehouse, updated) -- this one you have
parts_category (category_id, parts_id)
parts_category (main_category_id, parts_id)
Обновление:
Я только что создал такие таблицы:
DROP TABLE IF EXISTS `test`.`image`;
CREATE TABLE `test`.`image` (
`id` int(11) NOT NULL,
`type` int(11) NOT NULL,
`width` int(11) NOT NULL,
`height` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`location_region`;
CREATE TABLE `test`.`location_region` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`location_state`;
CREATE TABLE `test`.`location_state` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`parts`;
CREATE TABLE `test`.`parts` (
`id` int(11) NOT NULL,
`status` char(1) NOT NULL,
`level` int(11) NOT NULL,
`warehouse` int(11) NOT NULL,
`updated` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`region_id` int(11) NOT NULL,
`promotion_id` int(11) NOT NULL,
`image_id` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `status` (`status`,`level`,`warehouse`,`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`parts_category`;
CREATE TABLE `test`.`parts_category` (
`id` int(11) NOT NULL,
`parts_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`main_category_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_pc_cat_parts` (`category_id`,`parts_id`),
KEY `ix_pc_main_parts` (`main_category_id`,`parts_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`promotion`;
CREATE TABLE `test`.`promotion` (
`id` int(11) NOT NULL,
`start_date` datetime NOT NULL,
`end_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
и заполнил их образцами данных:
INSERT
INTO parts
SELECT id,
CASE WHEN RAND() < 0.1 THEN 'A' ELSE 'B' END,
RAND() * 100,
RAND() * 100,
RAND() * 100,
RAND() * 50,
RAND() * 50,
RAND() * 50,
RAND() * 50
FROM t_source
LIMIT 500000;
INSERT
INTO parts_category
SELECT id,
id,
RAND() * 100,
RAND() * 100
FROM t_source
LIMIT 500000;
INSERT
INTO location_state
SELECT id, CONCAT('State ', id)
FROM t_source
LIMIT 1000;
INSERT
INTO location_region
SELECT id, CONCAT('Region ', id)
FROM t_source
LIMIT 1000;
INSERT
INTO promotion
SELECT id,
'2009-07-22' - INTERVAL RAND() * 5 - 20 DAY,
'2009-07-22' - INTERVAL RAND() * 5 DAY
FROM t_source
LIMIT 1000;
Вышеупомянутый запрос выполняется для 30 миллисекунд и дает следующий план:
1, 'PRIMARY', 'p', 'ref', 'status', 'status', '3', 'const', 107408, 'Using where'
1, 'PRIMARY', 'i', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.image_id', 1, ''
6, 'DEPENDENT SUBQUERY', 'pc', 'ref', 'ix_pc_cat_parts', 'ix_pc_cat_parts', '8', 'const,test.p.id', 1, 'Using index'
7, 'DEPENDENT UNION', 'pc', 'ref', 'ix_pc_main_parts', 'ix_pc_main_parts', '8', 'const,test.p.id', 1, 'Using index'
, 'UNION RESULT', '<union6,7>', 'ALL', '', '', '', '', , ''
5, 'DEPENDENT SUBQUERY', 'Promotion', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.promotion_id', 1, ''
4, 'DEPENDENT SUBQUERY', 'Promotion', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.promotion_id', 1, ''
3, 'DEPENDENT SUBQUERY', 'Location_Region', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.region_id', 1, ''
2, 'DEPENDENT SUBQUERY', 'Location_State', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.state_id', 1, ''
Как видите, нет временных , нет filesort , все происходит очень быстро.
Чтобы помочь вам, мне просто нужно посмотреть, как определены ваши таблицы.
John, the problem is your query is constructed such that it is selecting from a derived table. The derived table cannot benefit from your indices. Try updating your query as follows:
SELECT
Parts.*,
Image.type, Image.width, Image.height,
Location_State.name AS state,
Location_Region.name AS region,
Promotion.start_date AS promotion_start_date,
Promotion.end_date AS promotion_end_date
FROM Parts
LEFT JOIN Image ON Parts.image_id = Image.id
LEFT JOIN Location_State ON Parts.state_id = Location_State.id
LEFT JOIN Location_Region ON Parts.state_id = Location_Region.id
LEFT JOIN Promotion ON Parts.promotion_id = Promotion.id
INNER JOIN Parts_Category ON (Parts_Category.category_id = 40 OR Parts_Category.main_category_id = 40)
WHERE Parts.status = 'A'
GROUP BY Parts.id
ORDER BY Parts.level DESC, Parts.warehouse DESC, Parts.updated DESC LIMIT 0, 15
Note, if you don't need LEFT JOIN's for your Location_State, Location_Region, Promotion tables, then use an INNER JOIN instead. It will likely perform better.
To further assist in optimizing this query, please provide the following:
SHOW CREATE TABLE Parts;
If the rewritten query I provided works the same as your example (it should), then also provide:
EXPLAIN <my query here>\G