使用ORDER BY进行mysql慢查询优化

Question

我的网站有opencart 1.5，MySQL5.7和慢查询，比如：

SELECT a.attribute_id, ad.name as attribute_name, pa.text, ag.attribute_group_id, agd.name as attribute_group_name FROM product_attribute pa
    LEFT JOIN attribute a ON (pa.attribute_id = a.attribute_id)
    LEFT JOIN attribute_description ad ON (a.attribute_id = ad.attribute_id)
    LEFT JOIN attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id)
    LEFT JOIN attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id)
    WHERE pa.product_id = 1029
            AND ad.language_id = agd.language_id
            AND pa.language_id = agd.language_id
            AND agd.language_id = 1
    ORDER BY ag.sort_order, agd.name, a.sort_order, ad.name

给我解释一下：

+----+-------------+-------+------------+--------+----------------------------------+--------------------+---------+-----------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                    | key                | key_len | ref                                     | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+----------------------------------+--------------------+---------+-----------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | agd   | NULL       | ALL    | PRIMARY                          | NULL               | NULL    | NULL                                    |   20 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ag    | NULL       | eq_ref | PRIMARY                          | PRIMARY            | 4       | my_shop.agd.attribute_group_id     |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | a     | NULL       | ref    | PRIMARY,attribute_group_id       | attribute_group_id | 4       | my_shop.agd.attribute_group_id     |    3 |   100.00 | Using where                                  |
|  1 | SIMPLE      | pa    | NULL       | eq_ref | PRIMARY,attribute_id,language_id | PRIMARY            | 12      | const,my_shop.a.attribute_id,const |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | ad    | NULL       | eq_ref | PRIMARY                          | PRIMARY            | 8       | my_shop.a.attribute_id,const       |    1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+----------------------------------+--------------------+---------+-----------------------------------------+------+----------+----------------------------------------------+

如何更改此查询以使用索引？

表和结构中的真实数据例如lower:如何更改此查询以使用索引？

表和结构中的真实数据例如lower:如何更改此查询以使用索引？

表和结构中的真实数据，例如下面：

CREATE TABLE `attribute` (
  `attribute_id` int(11) NOT NULL,
  `attribute_group_id` int(11) NOT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `attribute` (`attribute_id`, `attribute_group_id`, `sort_order`) VALUES
(22, 7, 120),
(21, 7, 100),
(23, 7, 110),
(20, 7, 90),
(19, 7, 80),
(18, 7, 70),
(17, 7, 60),
(16, 7, 40),
(15, 7, 30),
(14, 7, 20),
(13, 7, 15),
(12, 7, 10),
(24, 7, 130),
(25, 7, 150),
(26, 7, 160),
(27, 7, 170),
(28, 7, 180),
(29, 7, 190),
(30, 7, 200),
(31, 7, 210),
(32, 7, 140),
(33, 7, 50),
(34, 7, 85),
(35, 7, 87),
(36, 7, 88),
(37, 7, 85),
(38, 7, 135);


CREATE TABLE `attribute_description` (
  `attribute_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `attribute_description` (`attribute_id`, `language_id`, `name`) VALUES
(16, 2, 'Толщина корпуса, мм.'),
(14, 2, 'Гарантия'),
(13, 1, 'Product code'),
(17, 3, 'Dial Colour'),
(15, 1, 'Case width approx., mm.'),
(14, 3, 'Guarantee'),
(13, 2, 'Шифр'),
(17, 4, 'Dial Colour'),
(16, 1, 'Case depth approx., mm'),
(15, 2, 'Ширина корпуса, мм.'),
(14, 4, 'Guarantee'),
(13, 3, 'Product code'),
(14, 1, 'Guarantee'),
(13, 4, 'Product code'),
(12, 3, 'Model'),
(12, 2, 'Модель'),
(12, 1, 'Model'),
(17, 2, 'Цвет циферблата'),
(17, 1, 'Dial Colour'),
(18, 2, 'Люминофор'),
(18, 3, 'Luminous'),
(18, 1, 'Luminous'),
(19, 4, 'Case material'),
(19, 3, 'Case material'),
(19, 2, 'Корпус'),
(19, 1, 'Case material'),
(20, 4, 'Bezel material'),
(20, 3, 'Bezel material'),
(20, 2, 'Безель'),
(20, 1, 'Bezel material'),
(21, 3, 'Lens'),
(21, 2, 'Стекло'),
(21, 1, 'Lens'),
(22, 3, 'Strap type'),
(22, 2, 'Ремешок'),
(22, 1, 'Strap type'),
(23, 4, 'Water Resistant'),
(23, 2, 'Водонепроницаемые до:'),
(23, 3, 'Water Resistant'),
(23, 1, 'Water Resistant'),
(24, 3, 'Strap width'),
(24, 2, 'Ширина ремешка:'),
(24, 1, 'Strap width'),
(25, 3, 'Movement'),
(25, 2, 'Механизм'),
(25, 1, 'Movement'),
(26, 3, 'Automatic winding'),
(26, 2, 'Автоматический завод'),
(26, 1, 'Automatic winding'),
(27, 2, 'Ручной завод'),
(27, 3, 'Manual winding'),
(27, 1, 'Manual winding'),
(28, 3, 'Jewels'),
(28, 2, 'Количество камней'),
(28, 1, 'Jewels'),
(29, 3, 'Accuracy'),
(29, 2, 'Точность при температуре 20±5°С'),
(29, 1, 'Accuracy'),
(30, 3, 'Power reserve'),
(30, 2, 'Запас хода при полном заводе, не менее'),
(30, 1, 'Power reserve'),
(31, 4, 'Average term of service'),
(31, 3, 'Average term of service'),
(31, 2, 'Срок службы'),
(31, 1, 'Average term of service'),
(32, 3, 'Strap color'),
(32, 2, 'Цвет ремешка'),
(32, 1, 'Strap color'),
(33, 2, 'Длина (от лапок до лапок) мм.'),
(12, 4, 'Model'),
(33, 1, 'Lug to lug size, mm.'),
(18, 4, 'Luminous'),
(21, 4, 'Lens'),
(22, 4, 'Strap type'),
(24, 4, 'Strap width'),
(32, 4, 'Strap color'),
(25, 4, 'Movement'),
(26, 4, 'Automatic winding'),
(27, 4, 'Manual winding'),
(28, 4, 'Jewels'),
(29, 4, 'Accuracy'),
(30, 4, 'Power reserve'),
(16, 3, 'Case depth approx., mm'),
(16, 4, 'Case depth approx., mm'),
(15, 3, 'Case width approx., mm.'),
(15, 4, 'Case width approx., mm.'),
(33, 3, 'Lug to lug size, mm.'),
(33, 4, 'Lug to lug size, mm.'),
(34, 1, 'Dial'),
(34, 2, 'Циферблат'),
(34, 3, 'Dial'),
(34, 4, 'Dial'),
(35, 1, 'Hands'),
(35, 2, 'Стрелки'),
(35, 3, 'Hands'),
(35, 4, 'Hands'),
(36, 1, 'Crown'),
(36, 2, 'Головка'),
(36, 3, 'Crown'),
(36, 4, 'Crown'),
(37, 1, 'Caseback'),
(37, 2, 'Задняя крышка'),
(37, 3, 'Caseback'),
(37, 4, 'Caseback'),
(38, 1, 'Wrist size, mm'),
(38, 2, 'Максимальный объем запястья,мм'),
(38, 3, 'Handgelenkgröße, mm'),
(38, 4, 'Tamaño de la muñeca, mm');


CREATE TABLE `attribute_group` (
  `attribute_group_id` int(11) NOT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `attribute_group` (`attribute_group_id`, `sort_order`) VALUES
(3, 2),
(4, 1),
(5, 3),
(6, 4),
(7, 0);


CREATE TABLE `attribute_group_description` (
  `attribute_group_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `attribute_group_description` (`attribute_group_id`, `language_id`, `name`) VALUES
(3, 1, 'Память'),
(4, 1, 'Technical'),
(5, 1, 'Материнская плата'),
(6, 1, 'Процессор'),
(3, 2, 'Memory'),
(4, 2, 'Technical'),
(5, 2, 'Motherboard'),
(6, 2, 'Processor'),
(3, 3, 'Memory'),
(4, 3, 'Technical'),
(5, 3, 'Motherboard'),
(6, 3, 'Processor'),
(3, 4, 'Memory'),
(4, 4, 'Technical'),
(5, 4, 'Motherboard'),
(6, 4, 'Processor'),
(7, 1, 'Specification'),
(7, 2, 'Характеристики'),
(7, 3, 'Specification'),
(7, 4, 'Specification');

ALTER TABLE `attribute`
  ADD PRIMARY KEY (`attribute_id`),
  ADD KEY `attribute_group_id` (`attribute_group_id`),
  ADD KEY `sort_order` (`sort_order`);

ALTER TABLE `attribute_description`
  ADD PRIMARY KEY (`attribute_id`,`language_id`),
  ADD KEY `name` (`name`);

ALTER TABLE `attribute_group`
  ADD PRIMARY KEY (`attribute_group_id`),
  ADD KEY `sort_order` (`sort_order`);

ALTER TABLE `attribute_group_description`
  ADD PRIMARY KEY (`attribute_group_id`,`language_id`),
  ADD KEY `name` (`name`);


ALTER TABLE `attribute`
  MODIFY `attribute_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39;

ALTER TABLE `attribute_group`
  MODIFY `attribute_group_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

Answer 1

删除ON子句中的无用() (不是为了提高性能，而是为了正确使用sql )不要使用与left join表相关的列，否则work as inner join

你在使用它时有字面值

  SELECT a.attribute_id, ad.name as attribute_name, pa.text, ag.attribute_group_id, agd.name as attribute_group_name 
  FROM product_attribute pa
    LEFT JOIN attribute a ON pa.attribute_id = a.attribute_id
    LEFT JOIN attribute_description ad ON a.attribute_id = ad.attribute_id 
                and ad.language_id  = 1
    LEFT JOIN attribute_group ag ON a.attribute_group_id = ag.attribute_group_id 
    LEFT JOIN attribute_group_description agd ON ag.attribute_group_id = agd.attribute_group_id 
                AND agd.language_id = 1
    WHERE pa.product_id = 1029 
    AND pa.language_id = 1 
    ORDER BY ag.sort_order, agd.name, a.sort_order, ad.name