mysql-----------limit优化

PHP中分页肯定会使用到MySQL的limit,大部分对类似"select * from title where uid =** order by id desc limit m,n"很熟悉,也不是全部都能看出里面有什么不对,可是当是在大数据量下操作呢,比如百万类似"select * from title where uid =177 order by id desc limit 1234567,20"就会发现sql执行的时间明显变得很长,为什么呢?

先从MySQL的limit原理说起,使用limit m,n是时候,MySQL先扫描(m+n)条记录,然后从m行开始取n行.比如上面的例子就是先扫描1234587条数据,这样的话sql能快吗?这就要求我们尽可能的减少m的值,甚至没有m直接limit n这样是sql.

看个例子:

mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 limit 888888,10; | id?????? | substr(mobile from 1 for 7) | time??????????????? | cpid | linkid?????????????? | | 11535090 | 1353554???????????????????? | 2010-02-24 21:07:48 | 769 | 21064905903309587933 | 
| 11535091 | 1353750???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912943389480033 | 
| 11535093 | 1353394???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912945389480075 | 
| 11535098 | 1343073???????????????????? | 2010-02-24 21:07:50 | 769 | 21064905865309587977 | 
| 11535100 | 1369270???????????????????? | 2010-02-24 21:07:51 | 769 | 21064926770369210194 | 
| 11535103 | 1355683???????????????????? | 2010-02-24 21:07:51 | 769 | 21064912944389480113 | 
| 11535104 | 1368959???????????????????? | 2010-02-24 21:07:51 | 769 | 21064902508384448468 | 
| 11535105 | 1365243???????????????????? | 2010-02-24 21:07:51 | 769 | 21064905907309403124 | 
| 11535106 | 1362145???????????????????? | 2010-02-24 21:07:52 | 769 | 21065002511384448497 | 
| 11535107 | 1369228???????????????????? | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (3.84 sec)

mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and id>=11535090 limit 10; | id?????? | substr(mobile from 1 for 7) | time??????????????? | cpid | linkid?????????????? | | 11535090 | 1353554???????????????????? | 2010-02-24 21:07:48 | 769 | 21064905903309587933 | 
| 11535091 | 1353750???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912943389480033 | 
| 11535093 | 1353394???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912945389480075 | 
| 11535098 | 1343073???????????????????? | 2010-02-24 21:07:50 | 769 | 21064905865309587977 | 
| 11535100 | 1369270???????????????????? | 2010-02-24 21:07:51 | 769 | 21064926770369210194 | 
| 11535103 | 1355683???????????????????? | 2010-02-24 21:07:51 | 769 | 21064912944389480113 | 
| 11535104 | 1368959???????????????????? | 2010-02-24 21:07:51 | 769 | 21064902508384448468 | 
| 11535105 | 1365243???????????????????? | 2010-02-24 21:07:51 | 769 | 21064905907309403124 | 
| 11535106 | 1362145???????????????????? | 2010-02-24 21:07:52 | 769 | 21065002511384448497 | 
| 11535107 | 1369228???????????????????? | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (0.00 sec)

mysql> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and time>='2010-02-24 21:07:48' limit 10; | id?????? | substr(mobile from 1 for 7) | time??????????????? | cpid | linkid?????????????? | | 11535090 | 1353554???????????????????? | 2010-02-24 21:07:48 | 769 | 21064905903309587933 | 
| 11535091 | 1353750???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912943389480033 | 
| 11535093 | 1353394???????????????????? | 2010-02-24 21:07:48 | 769 | 21064912945389480075 | 
| 11535098 | 1343073???????????????????? | 2010-02-24 21:07:50 | 769 | 21064905865309587977 | 
| 11535100 | 1369270???????????????????? | 2010-02-24 21:07:51 | 769 | 21064926770369210194 | 
| 11535103 | 1355683???????????????????? | 2010-02-24 21:07:51 | 769 | 21064912944389480113 | 
| 11535104 | 1368959???????????????????? | 2010-02-24 21:07:51 | 769 | 21064902508384448468 | 
| 11535105 | 1365243???????????????????? | 2010-02-24 21:07:51 | 769 | 21064905907309403124 | 
| 11535106 | 1362145???????????????????? | 2010-02-24 21:07:52 | 769 | 21065002511384448497 | 
| 11535107 | 1369228???????????????????? | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (0.01 sec)

例中数据表id是主键,time也建了索引,表中总数据约为240w行,其中cpid为769的数据量大约为90w条.这里面的id和时间可能会是不连续的.故不能直接得获取id>m这样操作

所以可以显示 “1，2，3，4，5,末页” 或是 “首页，<<100,101,102,103 >>末页”这样,这样可以极大的减少m值!

MySQL里面的join顺便说一句就是,通常有点讲究的是用小表去驱动大表,而由于MySQL join实现的原理就是做循环比如left join就是对左边的数据进行循环去驱动右边的表,比如左边是可能会有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是m*n行数据.左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集.值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后.否则MySQL会当作普通的连表查询!

MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。

同样是取10条数据

select * from posts limit 10000,10 和
select * from posts limit 0,10 
就不是一个数量级别的。

网上也很多关于limit的五条优化准则，都是翻译自mysql手册，虽然正确但不实用。今天发现一篇文章写了些关于limit优化的，很不错。

文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。（测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19）

1、offset比较小的时候。

select * from posts limit 10,10

多次运行，时间保持在0.0004-0.0005之间
Select * From posts Where id >=(
Select id From posts Order By id limit 10,1
) limit 10

多次运行，时间保持在0.0005-0.0006之间，主要是0.0006
结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。

2、offset大的时候。
select * from posts limit 10000,10

多次运行，时间保持在0.0187左右
Select * From posts Where id >=(
Select id From posts Order By id limit 10000,1
) limit 10

多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。

以后要注意改正自己的limit语句，优化一下mysql了