优化ORDER BY查询 [英] Optimize an ORDER BY query

问题描述

我很茫然.我有一张约有10万行的表格.查询该表时，结果通常很快，大约2毫秒左右.但是，每当我使用ORDER BY时，性能就会下降到大约120毫秒.我阅读了 MySQL ORDER BY Optimization 页面，但是我不能说我了解一切.尤其是索引对我来说还不清楚.

I'm at a loss. I have a table with about 100K rows. When querying this table results are usually snappy, about 2ms or so. But whenever I use an ORDER BY performance drops like a rock to about 120ms. I read the MySQL ORDER BY Optimization page but I can't say I understand everything. Especially the indexes are unclear to me.

最终我想运行以下查询:

Ultimately I would like to run the following query:

SELECT *
  FROM `affiliate_new_contracts`
 WHERE  phone_brand IN ('Apple','Blackberry','HTC','LG','Motorola','Nokia',
                        'Samsung','Sony Ericsson')
   AND contract_length IN ('12','24')
   AND (addon IS NULL OR addon IN('Telfort Sms 300','Surf & Mail'))
   AND (plan_name = 'Telfort 100'
        AND 
        credible_shop = 1
       ) 
  ORDER BY average_price_per_month ASC, phone_price_guestimate DESC,
           contract_length ASC;

但是，如果我理解基本原理，我将很高兴.
在上一个查询中删除ORDER BY子句可使它在20毫秒内运行，而不是120毫秒.我在average_price_per_month字段上有一个索引，但是将ORDER BY子句简化为ORDER BY average_price_per_month并没有提高性能.我不明白.对于所谓的多列索引，我也一无所知，它应该能够帮助我进行最终查询.

But I would be happy if I understood the underlying principles.
Removing the ORDER BY clause in the previous query makes it run in 20ms in stead of 120ms. I have an index on the average_price_per_month field but simplifying the ORDER BY clause to ORDER BY average_price_per_month yielded no performance increase. That I don't understand. I'm also in the dark about the so called multi column indexes which should be able to help me with the ultimate query.

任何帮助将不胜感激.我该如何使这个坏男孩表演?还是那个乌托邦式的追求?

Any help would be appreciated. How do I make this bad boy perform? Or is that quest utopian?

CREATE TABLE语法如下:

$ show create table affiliate_new_contracts;
CREATE TABLE `affiliate_new_contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contract_length` int(11) DEFAULT NULL,
  `phone_brand` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` float DEFAULT NULL,
  `average_price_per_month` float DEFAULT NULL,
  `phone_price_guestimate` float DEFAULT NULL,
  `credible_shop` tinyint(1) DEFAULT '0',
  `addon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `addon_price` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_affiliate_new_contracts_on_plan_name` (`plan_name`),
  KEY `index_affiliate_new_contracts_on_average_price_per_month` (`average_price_per_month`),
  KEY `index_affiliate_new_contracts_on_price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=2472311 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

顺便说一句，该表每周重新创建一次，同时不会更新.

BTW This table is recreated weekly and is not updated in the meanwhile.

推荐答案

对ORDER BY子句可以进行的优化数量是有限制的.有时有帮助的主要方法是按照正确的顺序在正确的一组列上建立索引.因此，在您的示例中，(单个，复合)索引位于:

There is a limit to how much optimization you can do on ORDER BY clauses. The primary one that sometimes helps is having an index on the correct set of columns in the correct order. So, for your example, a (single, composite) index on:

average_price_per_month ASC, phone_price_guestimate DESC, contract_length ASC

可能会有所帮助，但是优化器可能仍会决定最好使用其他索引来处理查询中的过滤条件，然后它将对如此选择的数据本身进行排序.请注意，除非索引以完全正确的排序顺序提供数据，并且使用索引可以整体上加快查询速度，否则优化器将不会使用它.仅要排序的一列上的索引对优化器来说是有限的好处，通常不会使用这样的索引.

might help, but the optimizer might still decide that it is better to use some other index to deal with the filter terms in the query and then it will sort the data thus selected itself. Note that unless the index provides the data in exactly the correct sorted order and using the index speeds up the query overall, then the optimizer won't use it. An index on only one of the columns to be sorted is a limited benefit to the optimizer, and it normally won't use such an index.

要考虑的一个问题:

• 没有ORDER BY子句的查询执行速度有多快.

这使您可以非常直接地衡量分类成本.您提到20毫秒(不带订购)和120毫秒(带订购)，因此ORDER BY相当昂贵.下一个问题可能是您可以在应用程序中胜过它的排序吗?".您也许可以做到这一点，但是DBMS中的排序程序包通常已经进行了很好的优化，您可能需要付出更多的努力才能克服它.

That gives you a very direct measurement of the cost of sorting. You mention 20 ms without ordering and 120 ms with ordering, so the ORDER BY is moderately expensive. The next question might be "Can you outperform its sort in your application?". You might be able to do that, but the sort package in a DBMS is usually fairly well optimized and you're likely to have to work hard to beat it.

这篇关于优化ORDER BY查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！