#注意这里采用了2次倒序排，因此在取LIMIT的start值时，比原来的值加了10，即935510，否则结果将和原来的不一致
| yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESCG *************************** 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 where |
| #采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果，这里不需要加10 yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> 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: t2.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 where |
然后我们来对比下这2个优化后的新SQL执行时间：
| yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC; ... rows in set (1.86 sec) #采用子查询优化，从profiling的结果来看，相比原来的那个SQL快了：28.2% yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id); ... 10 rows in set (1.83 sec) #采用INNER JOIN优化，从profiling的结果来看，相比原来的那个SQL快了：30.8% |
我们再来看一个不带过滤条件的分页SQL对比：
| #原始SQL yejr@imysql.com> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10G *************************** 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% |
