MySQL:为什么“按ID排序"比“按其他列排序"运行慢得多? [英] MySQL: Why does an Order By ID runs much slower than Order By other Columns?

问题描述

我正在使用MySQL 5.5.14版本从500万行的表中运行以下查询， QUERY 1 :

I am using MySQL version 5.5.14 to run the following query, QUERY 1, from a table of 5 Million rows:

SELECT P.ID, P.Type, P.Name, P.cty
     , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
     , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
     , P.lv, P.bd, P.bt, P.nb
     , P.ak * E.usD as 'usP' 
FROM PIG P 
  INNER JOIN EEL E 
    ON E.cur = P.cur 
WHERE act='1' 
  AND flA >= '1615' 
  AND ldA >= '0' 
  AND yr >= (YEAR(NOW()) - 100) 
  AND lv >= '0' 
  AND bd >= '3' 
  AND bt >= '2' 
  AND nb <= '5' 
  AND cDate >= NOW() 
  AND MBRContains(LineString( Point(39.9097, -2.1973)
                            , Point(65.5130, 41.7480)
                            ), latlng) 
  AND Type = 'g' 
  AND tn = 'l' 
  AND St + Tm - YEAR(NOW()) >= '30' 
HAVING usP BETWEEN 300/2 AND 300 
ORDER BY ak
LIMIT 100;

使用索引(类型，tn，act，fA)，我可以在 800ms 内获得结果.在 QUERY 2 中，我将ORDER BY子句更改为lv，我也能够在相似的时间内获得结果.在 QUERY 3 中，我将ORDER BY子句更改为ID，平均10次试验中，查询时间显着降低到了完整的 20s .

Using an Index (Type, tn, act, flA), I am able to obtain results within 800ms. In QUERY 2, I changed the ORDER BY clause to lv, I am also able to obtain results within similar timings. In QUERY 3, I changed the ORDER BY clause to ID and the query time slowed dramatically to a full 20s on an average of 10 trials.

运行EXPLAIN SELECT语句会产生完全相同的查询执行计划:

Running the EXPLAIN SELECT statement produces exactly the same query execution plan:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: P
         type: range
possible_keys: Index
          key: Index
      key_len: 6
          ref: NULL
         rows: 132478
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: BS.P.cur
         rows: 1
        Extra: 

我的问题是:为什么在QUERY 3中按ID排序比其他订单慢?

部分表定义如下:

CREATE TABLE `PIG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lv` smallint(3) unsigned NOT NULL DEFAULT '0',
  `ak` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`),
  KEY `id_ca` (`cty`,`ak`),
  KEY `Index` (`Type`, `tn`, `act`, `flA`),
) ENGINE=MyISAM AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

CREATE TABLE `EEL` (
  `cur` char(3) NOT NULL,
  `usD` decimal(11,10) NOT NULL,
  PRIMARY KEY (`cur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

更新:在对各种ORDER BY选项进行了广泛的测试之后，我确认恰好是主键的ID列是导致查询时间变慢的唯一列.

UPDATE: After extensive testing of various ORDER BYs options, I have confirmed that the ID column which happens to be the Primary Key is the only one causing the slow query time.

推荐答案

来自MySQL文档，网址为

From MySQL documentation at http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

在某些情况下，MySQL仍然无法使用索引来解析ORDER BY，尽管它仍然使用索引来查找与WHERE子句匹配的行.这些情况包括:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

. .

用于获取行的密钥与ORDER BY中使用的密钥不同:

The key used to fetch the rows is not the same as the one used in the ORDER BY:

`SELECT * FROM t1 WHERE key2=constant ORDER BY key1;`

这可能无济于事，但是如果在WHERE子句中添加AND ID > 0会发生什么呢?这会导致MySQL使用主键进行排序吗?我想值得尝试.

This probably won't help, but what happens if you add AND ID > 0 to the WHERE clause? Would this cause MySQL to use the primary key for sorting? Worth a try I suppose.

(用ak排序很有效，因为ak甚至没有索引，但这可能是因为ak的值较少吗?)

(It seems odd that ordering with ak is efficient, since ak does not even have an index, but that may be due to fewer values for ak?)

这篇关于MySQL:为什么“按ID排序"比“按其他列排序"运行慢得多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！