利用分页表来提升千万级mysql分页查询的性能 让你分页limit快如飞
lamp黄金组合,越来越被太多大型网站开始使用的了。但是MYSQL带来的性能瓶颈,肯定也是大家一直挥之不去的恶梦。今天it动力就带大家去利用分页表的方法来提高mysql在千万级数据表里的分页查询性能问题。
原始存储千级级的MYSQL数据表:
CREATE TABLE `t_wordlist` ( `id` int(11) NOT NULL auto_increment, `qv` decimal(10,2) NOT NULL, `name` varchar(20) NOT NULL, `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
原表总记录数:
mysql> select count(*) from t_wordlist;
+----------+
| count(*) |
+----------+
| 10324567 |
+----------+
1 row in set (0.00 sec)
新增的分页数据表:
CREATE TABLE `t_wd_ids` ( `id` int(11) NOT NULL, `wd_id` int(11) NOT NULL, PRIMARY KEY (`id`,`wd_id`), KEY `idx_id` (`id`), KEY `idx_wd_id` (`wd_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入分页表的sql语句。
mysql> insert into t_wd_ids select ceil(id/20),id from t_wordlist; Query OK, 10324567 rows affected (1 min 45.19 sec) Records: 10324567 Duplicates: 0 Warnings: 0
试验对比:
用普通LIMIT来实现分页。
mysql> select * from t_wordlist where 1 limit 20;
20 rows in set (0.01 sec)
用分页表来实现分页:
mysql> select a.* from t_wordlist as a inner join t_wd_ids as b where a.id = b.wd_id and b.id = 1;
20 rows in set (0.00 sec)
查询mysql表的最后一页的数据:
mysql> select * from t_group where 1 limit 10324547,20;
20 rows in set (4.88 sec)
昨用分页表来查询:
mysql> select a.* from t_wordlist as a inner join t_wd_ids as b where a.id = b.wd_id and b.id = 516227;
20 rows in set (0.01 sec)
大家可以看到,利用了分布表来查询越往后的数据,分页表的优势就越明显。在此,只提供大家一个思路,具体如何扩展,如何保证分页的同步更新问题,大家可以自由发挥下了。