#原始SQL
yejr@imysql.com> EXPLAIN
SELECT
*
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: t1
type:
index
possible_keys:
NULL
key
:
PRIMARY
key_len: 4
ref:
NULL
rows
: 935510
Extra:
NULL
yejr@imysql.com>
SELECT
*
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935500, 10;
...
10
rows
in
set
(2.22 sec)
#采用子查询优化
yejr@imysql.com> EXPLAIN
SELECT
*
FROM
(
SELECT
*
FROM
`t1`
WHERE
id > (
SELECT
id
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935510, 1) LIMIT 10) t
ORDER
BY
id
DESC
;
*************************** 1. row ***************************
id: 1
select_type:
PRIMARY
table
: <derived2>
type:
ALL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref:
NULL
rows
: 10
Extra: Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table
: t1
type:
ALL
possible_keys:
PRIMARY
key
:
NULL
key_len:
NULL
ref:
NULL
rows
: 973192
Extra: Using
where
*************************** 3. row ***************************
id: 3
select_type: SUBQUERY
table
: t1
type:
index
possible_keys:
NULL
key
:
PRIMARY
key_len: 4
ref:
NULL
rows
: 935511
Extra: Using
index
yejr@imysql.com>
SELECT
*
FROM
(
SELECT
*
FROM
`t1`
WHERE
id > (
SELECT
id
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935510, 1) LIMIT 10) t
ORDER
BY
id
DESC
;
…
10
rows
in
set
(2.01 sec)
#采用子查询优化,从profiling的结果来看,相比原来的那个SQL快了:10.6%
#采用
INNER
JOIN
优化
yejr@imysql.com> EXPLAIN
SELECT
*
FROM
`t1`
INNER
JOIN
(
SELECT
id
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
id: 1
select_type:
PRIMARY
table
:
type:
ALL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref:
NULL
rows
: 935510
Extra:
NULL
*************************** 2. row ***************************
id: 1
select_type:
PRIMARY
table
: t1
type: eq_ref
possible_keys:
PRIMARY
key
:
PRIMARY
key_len: 4
ref: t1.id
rows
: 1
Extra:
NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table
: t1
type:
index
possible_keys:
NULL
key
:
PRIMARY
key_len: 4
ref:
NULL
rows
: 973192
Extra: Using
index
yejr@imysql.com>
SELECT
*
FROM
`t1`
INNER
JOIN
(
SELECT
id
FROM
`t1`
ORDER
BY
id
DESC
LIMIT 935500,10) t2 USING (id);
…
10
rows
in
set
(1.70 sec)
#采用
INNER
JOIN
优化,从profiling的结果来看,相比原来的那个SQL快了:30.2%