Início / dba / Perguntas / 45407
Accepted
Arash Mousavi
Asked: 2013-06-29 01:56:34 +0800 CST

Alterar a direção ORDER BY causa problemas de desempenho

  • 772

Eu uso o MySQL 5.6 e tenho duas tabelas cada uma com 16 milhões de linhas:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

Eu tenho uma consulta com pedido por order by newsstudios.id ASC:

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

o resultado dessa consulta é:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

mas quando mudo a direção do order by para DESCo tempo de execução da query diminui 100 vezes:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

Primeiro: por que essa mudança na direção do pedido causa essa diferença enorme de desempenho?

Segundo: antes desta consulta eu tentei LEFT JOINe WHERE INconsultas em vez de WHERE EXISTS, mas eles têm resultado duplicado que devo usar GROUP BYque causa using filesorte using temporaryque diminui muito o desempenho. Você tem alguma sugestão para que a consulta tenha melhor desempenho?

mysql performance

2 respostas

  1. Best Answer

    Se as diferentes categorias que você está pesquisando não forem muitas e você puder criar a consulta mais complexa, isso usará um índice (category_id, newsstudio_id)e espero que seja mais eficiente do que sua consulta anterior, com ASCou DESCescolha.

    É um pouco mais complexo de construir e espero que a eficiência diminua se o número de categorias for dezenas ou centenas:

    SELECT newsstudio_id
FROM 
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5 
    ) 
    UNION 
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;
    • 4

  2. Como o ypercube disse em seu comentário, seu índice deve ter um desempenho melhor neste caso se os campos estiverem invertidos.

    Além disso, desde que cada arquivo newsstudio_categories. newsstudio_idtem pelo menos uma linha correspondente em newsstudios. id(não garantido pelo seu esquema, mas parece provável), então você poderia fazer uma consulta que olhasse apenas paranewsstudio_categories

    SELECT SQL_NO_CACHE DISTINCT `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
ORDER BY `newsstudio_id` DESC
LIMIT 5

    ou

    SELECT SQL_NO_CACHE `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
GROUP BY `newsstudio_id`
ORDER BY `newsstudio_id` DESC
LIMIT 5
    • 2

relate perguntas