mysql 主键排序慢,MySQL：为什么“按ID排序”比“按其他列排序”运行慢得多？...

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;

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.

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:

My question is: why does ordering by ID in QUERY 3 runs so slow compared to the rest?

The partial table definition is as such:

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

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.

解决方案

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:

. . .

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;`

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.

(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?)