Резкая разница в скорости запросов

Question

Резкая разница в скорости запросов

Я не понимаю разницу (строка 2) этих двух EXPLAINs. Может быть, у меня есть подсказка, почему mysql так сильно отличается от тех, что сильно влияет на скорость запросов.

Медленный запрос длится 12 секунд (что соответствует запросу всех строк с этим запросом) и использует объединение целочисленных столбцов, в то время как объединенная таблица содержит только 3 записи:

SELECT `inv_assets`.`id` AS `id`, `site`.`description` AS `sitename`, 
  (SELECT COALESCE(DATE_FORMAT(CONVERT_TZ(MIN(inspdate),'UTC','Europe/Vienna'),'%Y-%m-%d'),'') 
   FROM `mobuto_inv_inspections` AS `nextinsp` 
   WHERE ((`nextinsp`.`objectlink` = `inv_assets`.`id` 
            AND `nextinsp`.`inspdate` >= NOW()))
   ) AS `nextinsp` 
FROM `mobuto_inv_assets` AS `inv_assets` 
LEFT JOIN `mobuto_inv_sites` AS `site` 
  ON (`site`.`siteid` = `inv_assets`.`site` 
  AND `site`.`_state` IN (2,0)) 
ORDER BY `inv_assets`.`type` ASC LIMIT 0, 20;

+----+--------------------+------------+--------+----------------+---------+---------+------------------------------+-------+----------------------------------------------------+
| id | select_type        | table      | type   | possible_keys  | key     | key_len | ref                          | rows  | Extra                                              |
+----+--------------------+------------+--------+----------------+---------+---------+------------------------------+-------+----------------------------------------------------+
|  1 | PRIMARY            | inv_assets | ALL    | NULL           | NULL    | NULL    | NULL                         | 24857 | Using temporary; Using filesort                    |
|  1 | PRIMARY            | site       | ALL    | PRIMARY,_state | NULL    | NULL    | NULL                         |     3 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DEPENDENT SUBQUERY | nextinsp   | ALL    | inspdate       | NULL    | NULL    | NULL                         |   915 | Using where                                        |
+----+--------------------+------------+--------+----------------+---------+---------+------------------------------+-------+----------------------------------------------------+

Быстрый запрос занимает всего несколько долей секунды, использует столбцы объединения по varchar(32), а объединенная таблица содержит 1352 записи:

SELECT `inv_assets`.`id` AS `id`, `guarantor`.`lastname` AS `guarantoruname`, 
      (SELECT COALESCE(DATE_FORMAT(CONVERT_TZ(MIN(inspdate),'UTC','Europe/Vienna'),'%Y-%m-%d'),'') 
       FROM `mobuto_inv_inspections` AS `nextinsp` 
       LEFT JOIN `users` AS `saveuser` 
       ON (`saveuser`.`uid` = `nextinsp`.`saveuser` 
            AND `saveuser`.`_state` = '0') 
       WHERE ((`nextinsp`.`objectlink` = `inv_assets`.`id` 
                AND `nextinsp`.`inspdate` >= NOW()))
       ) AS `nextinsp` 
FROM `mobuto_inv_assets` AS `inv_assets` 
LEFT JOIN `users` AS `guarantor` 
ON (`guarantor`.`uid` = `inv_assets`.`guarantor` 
     AND `guarantor`.`_state` = '0') 
ORDER BY `inv_assets`.`type` ASC LIMIT 0, 20;

+----+--------------------+------------+--------+----------------+---------+---------+---------------------------------+-------+----------------+
| id | select_type        | table      | type   | possible_keys  | key     | key_len | ref                             | rows  | Extra          |
+----+--------------------+------------+--------+----------------+---------+---------+---------------------------------+-------+----------------+
|  1 | PRIMARY            | inv_assets | ALL    | NULL           | NULL    | NULL    | NULL                            | 24857 | Using filesort |
|  1 | PRIMARY            | guarantor  | eq_ref | PRIMARY,_state | PRIMARY | 98      | mobuto_dev.inv_assets.guarantor |     1 | Using where    |
|  2 | DEPENDENT SUBQUERY | nextinsp   | ALL    | inspdate       | NULL    | NULL    | NULL                            |   915 | Using where    |
|  2 | DEPENDENT SUBQUERY | saveuser   | eq_ref | PRIMARY,_state | PRIMARY | 98      | mobuto_dev.nextinsp.saveuser    |     1 | Using where    |
+----+--------------------+------------+--------+----------------+---------+---------+---------------------------------+-------+----------------+

Странная вещь для меня, когда я удаляю столбец (description) объединенной таблицы в 'column-select-part' (хотя объединение все еще сохраняется, и IMHO mysql не оптимизирует его, когда он не используется), скорость возвращается (потому что mysql больше не использует временную таблицу и объяснение выглядит так же, как быстрый, имея type=eq_ref).

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

CREATE TABLE `mobuto_inv_assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invnum` varchar(10) NOT NULL,
  `oebglcat` varchar(4) NOT NULL,
  `mark` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `serialnum` varchar(100) NOT NULL,
  `desc` varchar(100) NOT NULL,
  `site` int(11) NOT NULL DEFAULT '0',
  `licnum` varchar(20) NOT NULL DEFAULT '',
  `inquirer` varchar(100) NOT NULL DEFAULT '',
  `inqdate` date NOT NULL DEFAULT '0000-00-00',
  `supplier` varchar(100) NOT NULL DEFAULT '',
  `suppldate` date NOT NULL DEFAULT '0000-00-00',
  `supplnumber` varchar(30) NOT NULL DEFAULT '',
  `invoicedate` date NOT NULL DEFAULT '0000-00-00',
  `invoicenumber` varchar(30) NOT NULL DEFAULT '',
  `purchaseprice` decimal(11,2) NOT NULL DEFAULT '0.00',
  `leased` varchar(1) NOT NULL DEFAULT 'N',
  `leasingcompany` varchar(100) NOT NULL DEFAULT '',
  `leasingnumber` varchar(30) NOT NULL DEFAULT '',
  `notes` text NOT NULL,
  `inspnotes` text NOT NULL,
  `inactive` varchar(1) NOT NULL DEFAULT 'N',
  `maintain` varchar(1) NOT NULL DEFAULT 'Y',
  `asset` varchar(1) NOT NULL DEFAULT 'Y',
  `inspection` varchar(1) NOT NULL DEFAULT '',
  `inspperson` varchar(100) NOT NULL DEFAULT '',
  `guarantor` varchar(32) NOT NULL DEFAULT '',
  `saveuser` varchar(32) NOT NULL,
  `savetime` int(11) NOT NULL,
  `recordid` varchar(32) NOT NULL,
  `_state` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `invnum` (`invnum`),
  KEY `_state` (`_state`),
  KEY `site` (`site`)
) ENGINE=InnoDB AUTO_INCREMENT=30707 DEFAULT CHARSET=utf8;


CREATE TABLE `mobuto_inv_sites` (
  `siteid` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(100) NOT NULL,
  `saveuser` varchar(32) NOT NULL,
  `savetime` int(11) NOT NULL,
  `recordid` varchar(32) NOT NULL,
  `_state` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`siteid`),
  KEY `_state` (`_state`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


mysql> SHOW INDEX FROM mobuto_inv_assets;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mobuto_inv_assets |          0 | PRIMARY  |            1 | id          | A         |       24857 |     NULL | NULL   |      | BTREE      |         |               |
| mobuto_inv_assets |          0 | invnum   |            1 | invnum      | A         |       24857 |     NULL | NULL   |      | BTREE      |         |               |
| mobuto_inv_assets |          1 | _state   |            1 | _state      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Изменения в соответствии с просьбой @Wilson Hauck:

Добавлен индекс в столбец site в mobuto_inv_assets (уменьшена скорость выполнения почти на полсекунды)
Кажется, что столбец nextinsp отсутствовал в первом запросе. Возможно потерян во время форматирования запроса. Конечно должно быть так же как в быстром
Удалил saveuser присоединиться, поскольку он там не используется (сохранил еще 2 секунды) и обновил свой EXPLAIN (последняя строка удалена)

SHOW INDEX FROM mobuto_inv_sites добавленной

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mobuto_inv_sites |          0 | PRIMARY  |            1 | siteid      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| mobuto_inv_sites |          1 | _state   |            1 | _state      | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

0

sql mysql explain

Источник

user2165807 03 авг '17 в 18:38

4 ответа

Другие вопросы по тегам sql mysql explain

user8413686 03 авг '17 в 19:14 2017-08-03 19:14 · Answer 1 · 2017-08-03 19:14

Ваш первый запрос использует меньше ключей, чем второй. possible_keys столбец в плане объяснения показывает, где ключи доступны для использования, однако key столбец показывает, где они на самом деле используются.

Я бы посоветовал, если вы не видите структуру вашей БД, активнее использовать эти ключи в предложениях JOIN и WHERE, чтобы ускорить ее.

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

user4420649 05 авг '17 в 16:06 2017-08-05 16:06 · Answer 2 · 2017-08-05 16:06

Спасибо за размещение ваших двух SHOW CREATE TABLE, очень полезных. Пожалуйста, рассмотрите возможность добавления индекса с помощью сайта ALTER TABLE mobuto_inv_sites ADD INDEX - если в вашей системе достаточно места. Кроме того, показ EXPLAIN для query1 не соответствует запросу. Запрос не ссылается на nextinsp или saveused, которые я вижу в EXPLAIN. Пожалуйста, замените EXPLAIN для query1 после создания индекса, когда у вас будет возможность снова протестировать и указать любое необходимое сокращение времени выполнения. Также было бы неплохо, если бы вы могли опубликовать результаты SHOW INDEX FROM mobuto_inv_sites, чтобы мы могли видеть объем ваших данных и количество элементов.

user4420649 03 авг '17 в 23:48 2017-08-03 23:48 · Answer 3 · 2017-08-03 23:48

12 секунд первый запрос, вероятно, вызван подсказками столбцов ROWS из 24857*3*915*1 = 68 232 465 рассмотренных строк. Менее 1 секунды для подсказок столбца ROWS второго запроса из 24857*1*915*1 = 22 744 155 рассмотренных строк. Использование первого запроса обработки блочных вложенных циклов является основной причиной задержки ответа.
Пожалуйста, опубликуйте результаты SHOW CREATE TABLE mobuto_inv_assets и mobuto_inv_sites. Пожалуйста, также опубликуйте результаты SHOW INDEX FROM mobuto_inv_assets и mobuto_inv_sites. С помощью этой дополнительной информации кто-то может предложить усовершенствования запросов SELECT ...., которые позволят избежать обработки блочно-вложенных циклов, которая сильно загружает ЦП с помощью RBAR (обработка строк за агонизирующей строкой). Может потребоваться дополнительная индексация.

user4420649 07 авг '17 в 08:58 2017-08-07 08:58 · Answer 4 · 2017-08-07 08:58

Если строки inv_assets заполнены данными ACCURATE _state, рассмотрите возможность изменения query1 на что-то вроде следующего:
ВЫБРАТЬ inv_assets,id КАК id, site,description КАК sitename, (ВЫБЕРИТЕ COALESCE(DATE_FORMAT(CONVERT_TZ(MIN(вдох)),'UTC','Европа / Вена'),'%Y-%m-%d'),'' ОТ mobuto_inv_inspections КАК nextinsp ГДЕ ((nextinsp,objectlink знак равно inv_assets,id А ТАКЖЕ nextinsp,inspdate > = СЕЙЧАС ()))) КАК nextinsp ОТ mobuto_inv_assets КАК inv_assets ГДЕ inv_assets,_state = 2 ИЛИ inv_assets,_state = 0
ЛЕВЫЙ РЕЙТИНГ mobuto_inv_sites КАК site ON (site,siteid знак равно inv_assets,site А ТАКЖЕ site,_state IN (2,0) ЗАКАЗАТЬ inv_assets,type ASC LIMIT 0, 20;

EXPLAIN должен избегать сканирования таблицы и последующей обработки блочных вложенных циклов.

Если _state data в inv_assets не является ТОЧНЫМ для каждой строки, это не будет работать.

2017-08-10 обновление 09:42 CT, пожалуйста, опубликуйте запрос QUERY, EXPLAIN, SHOW CREATE TABLE tblname для задействованных таблиц и SHOW INDEX FROM tblname для задействованных таблиц.