mysql 分页小测

普通分页：
SELECT  FF.*
FROM table_a FF
WHERE USER_ID = 25038378
AND SUSPENDED = 0
AND TYPE = 0
AND DATE_G > '2009-05-21 23:59:59'
AND DATE_G <= '2009-12-17 23:59:59'
AND RATE = 1
ORDER BY DATE_G DESC LIMIT 4000, 40;

优化分页：
SELECT c.* FROM (
SELECT  FF.ID ID
FROM table_a FF
WHERE USER_ID = 25038378
AND SUSPENDED = 0
AND TYPE = 0
AND DATE_G > '2009-05-21 23:59:59'
AND DATE_G <= '2009-12-17 23:59:59'
AND RATE = 1
ORDER BY DATE_G DESC LIMIT 4000, 40) t, table_a c
WHERE t.id =c.id;

show profiles;

show profile block io,cpu  for query ***;

简单的一些统计分析，
页数    普通    优化
1    0.000732    0.000832
5    0.002945    0.001145
10    0.003743    0.001233
20    0.009958    0.005825
50    1    0.05
100    1.4    0.053
200    4.8    0.08
400    8.14    0.11
2000 24.2 0.8

根据统计分析：
使用优化后的写法，在查询5页以后的数据有比较明显的优势，而且这个优势月到后面越明显。
其他同学的测试表明，在某些情况下查询前3页的话用普通分页写法会比优化的写法快很多，所以如何使用分页需要根据应用场景具体分析。

--
下面看一个特殊情况
sql只查询了前40行数据，但是实际上扫描了35440行，原因应该是数据的分布导致的。 RATE字段99%是1，只有1%的可能是0或者-1，所以在查询数据是会导致回表
# Query_time: 5  Lock_time: 0  Rows_sent: 40  Rows_examined: 35440
SELECT FF.*
FROM TABLE_A FF
WHERE USER_ID = 23501375
AND SUSPENDED = 0
AND TYPE = 0
AND DATE_G > '2009-05-21 23:59:59'
AND DATE_G <= '2009-12-17 23:59:59'
AND RATE = 0
ORDER BY DATE_G DESC
LIMIT 0, 40;

索引字段 IDX_RATED(`USER_ID`,`DATE_G`,`SUSPENDED`,`TYPE`,`RATE`,`VALID`)

---看sql的执行计划
root@feel_08 10:24:27>explain  SELECT FF.*
->    FROM TABLE_A FF
->   WHERE USER_ID = 23501375
->     AND SUSPENDED = 0
->     AND TYPE = 0
->     AND DATE_G > '2009-05-21 23:59:59'
->     AND DATE_G <= '2009-12-17 23:59:59'
->     AND RATE = 0
->   ORDER BY DATE_G DESC
->   LIMIT 0, 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: FF
type: range
possible_keys: IDX_FDATE,IDX_RATED
key: IDX_RATED
key_len: 24
ref: NULL
rows: 118650
Extra: Using where
1 row in set (0.00 sec)

直接查询的数据没有在索引列里，先回表了
--------
root@feel_08 10:27:52>explain SELECT c.* FROM (
->  SELECT FF.ID
->    FROM TABLE_A FF
->   WHERE USER_ID = 23501375
->     AND SUSPENDED = 0
->     AND TYPE = 0
->     AND DATE_G > '2009-05-21 23:59:59'
->     AND DATE_G <= '2009-12-17 23:59:59'
->     AND RATE =0
->   ORDER BY DATE_G DESC
->   LIMIT 0, 40) t, TABLE_A c
->  WHERE t.id =c.id\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 40
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: t.ID
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: FF
type: range
possible_keys: IDX_FDATE,IDX_RATED
key: IDX_RATED
key_len: 24
ref: NULL
rows: 118650
Extra: Using where; Using index
3 rows in set (0.02 sec)

因为子查询的数据直接使用索引获取ID，再用id去关联，索引回表的数据只有40条，速度就相当快了
执行时间是0.4s