以下的文章主要描述的是MySQL数据库下limit与join的实际优化方案，我们大家都知道其在实际中的应用比例还是占为多数的，如果你对这一技术，心存好奇的话，以下的文章将会揭开它的神秘面纱。
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>selectid,substr(mobilefrom1for7),time,cpid,linkidfromcp_mo100227wherecpid=769limit888888,10; +———-+—————————–+———————+——+———————-+ |id|substr(mobilefrom1for7)|time|cpid|linkid| +———-+—————————–+———————+——+———————-+ |11535090|1353554|2010-02-2421:07:48|769|21064905903309587933| |11535091|1353750|2010-02-2421:07:48|769|21064912943389480033| |11535093|1353394|2010-02-2421:07:48|769|21064912945389480075| |11535098|1343073|2010-02-2421:07:50|769|21064905865309587977| |11535100|1369270|2010-02-2421:07:51|769|21064926770369210194| |11535103|1355683|2010-02-2421:07:51|769|21064912944389480113| |11535104|1368959|2010-02-2421:07:51|769|21064902508384448468| |11535105|1365243|2010-02-2421:07:51|769|21064905907309403124| |11535106|1362145|2010-02-2421:07:52|769|21065002511384448497| |11535107|1369228|2010-02-2421:07:52|769|21064902514384448437| +———-+—————————–+———————+——+———————-+ 10rowsinset(3.84sec) mysql>selectid,substr(mobilefrom1for7),time,cpid,linkidfromcp_mo100227wherecpid=769andid>=11535090limit10; +———-+—————————–+———————+——+———————-+ |id|substr(mobilefrom1for7)|time|cpid|linkid| +———-+—————————–+———————+——+———————-+ |11535090|1353554|2010-02-2421:07:48|769|21064905903309587933| |11535091|1353750|2010-02-2421:07:48|769|21064912943389480033| |11535093|1353394|2010-02-2421:07:48|769|21064912945389480075| |11535098|1343073|2010-02-2421:07:50|769|21064905865309587977| |11535100|1369270|2010-02-2421:07:51|769|21064926770369210194| |11535103|1355683|2010-02-2421:07:51|769|21064912944389480113| |11535104|1368959|2010-02-2421:07:51|769|21064902508384448468| |11535105|1365243|2010-02-2421:07:51|769|21064905907309403124| |11535106|1362145|2010-02-2421:07:52|769|21065002511384448497| |11535107|1369228|2010-02-2421:07:52|769|21064902514384448437| +———-+—————————–+———————+——+———————-+ 10rowsinset(0.00sec) mysql>selectid,substr(mobilefrom1for7),time,cpid,linkidfromcp_mo100227wherecpid=769andtime>=’2010-02-2421:07:48′limit10; +———-+—————————–+———————+——+———————-+ |id|substr(mobilefrom1for7)|time|cpid|linkid| +———-+—————————–+———————+——+———————-+ |11535090|1353554|2010-02-2421:07:48|769|21064905903309587933| |11535091|1353750|2010-02-2421:07:48|769|21064912943389480033| |11535093|1353394|2010-02-2421:07:48|769|21064912945389480075| |11535098|1343073|2010-02-2421:07:50|769|21064905865309587977| |11535100|1369270|2010-02-2421:07:51|769|21064926770369210194| |11535103|1355683|2010-02-2421:07:51|769|21064912944389480113| |11535104|1368959|2010-02-2421:07:51|769|21064902508384448468| |11535105|1365243|2010-02-2421:07:51|769|21064905907309403124| |11535106|1362145|2010-02-2421:07:52|769|21065002511384448497| |11535107|1369228|2010-02-2421:07:52|769|21064902514384448437| +———-+—————————–+———————+——+———————-+ 10rowsinset(0.01sec)
例中数据表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数据库表中来检索数据并不难MySQL数据库访问妙招在Linux之下MySQL 修改密码的6个好用方案配置MySQL与卸载MySQL实操备份MySQL数据库脚本的实际应用