代码之家 › 专栏 › 技术社区 › harm

按查询优化订单

sql-order-by query-optimization optimization mysql

harm · 技术社区 · 14 年前

我不知所措。我有一张大约有10万行的桌子。查询此表时,结果通常很快,大约2毫秒左右。但是每当我使用一个按性能排序的命令时,它就会像石头一样下降到大约120毫秒。 MySQL ORDER BY Optimization 佩奇,但我不能说我什么都懂。尤其是指数对我来说不清楚。

最后,我想运行以下查询:

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 性能没有提高。我不明白。我也不知道所谓的多列索引,它应该能够帮助我完成最终的查询。

任何帮助都将不胜感激。我怎样才能让这个坏男孩表演?或者那是乌托邦式的探索?

这个 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此表每周重新创建一次,不会同时更新。

2 回复 | 直到 9 年前

Jonathan Leffler 14 年前

对于ORDERBY子句,可以进行多少优化是有限制的。有时有帮助的主要方法是按正确的顺序对正确的列集建立索引。因此,对于您的示例,一个(单个,复合)索引位于:

average_price_per_month ASC, phone_price_guestimate DESC, contract_length ASC

可能会有所帮助,但优化器可能仍然决定最好使用其他索引来处理查询中的筛选条件,然后它将对因此选择的数据本身进行排序。请注意,除非索引以正确的排序顺序提供数据,并且使用索引可以加速整个查询,否则优化器将不会使用它。对于优化器来说,只对其中一列进行排序的索引是有限的好处,而且它通常不会使用这样的索引。

需要考虑的一个问题:

如果没有ORDERBY子句,查询的执行速度会有多快。

这给了你一个非常直接的分类成本测量。您提到20 ms没有订购,120 ms有订购,因此订购方式的价格适中。下一个问题可能是“在应用程序中,您能超越它的同类产品吗?”。您可能可以这样做,但DBMS中的排序包通常是相当好的优化,您可能需要努力克服它。

tvanfosson 14 年前

我怀疑您的索引对您没有任何好处,因为它不是主键,查询选择逻辑(WHERE子句)没有使用它。因为您没有使用索引来选择行,所以最终必须在选择后对结果进行排序。事实上,它不是您的主键,这意味着结果还没有按每月的平均价格订购,这将减少或消除排序时间,因为它们已经被订购了。

一种解决方案是使用一个复合索引,其中包括最有选择的列(计划名称)和排序列(每个月的平均价格)。它仍然需要在选择之后进行排序,但是结果已经由主排序列排序,从而减少了所花费的时间。

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`,`average_price_per_month`),
  KEY `index_affiliate_new_contracts_on_price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=2472311 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

您可能还想使用 EXPLAIN 了解查询是如何执行的(如果我的直觉不正确),并相应地调整索引。