MySQL千万级数据表的优化实战记录

前言

这里先说明一下，网上很多人说阿里规定500w数据就要分库分表。实际上，这个500w并不是定义死的，而是与MySQL的配置以及机器的硬件有关。MySQL为了提升性能，会将表的索引装载到内存中。但是当表的数据到达一定的量的时候，会导致内存无法存储这些索引，无法存储索引，就只能进行磁盘IO，从而导致性能下降。

实战调优

我这里有张表，数据有1000w，目前只有一个主键索引

CREATE TABLE `user` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `uname` varchar(20) DEFAULT NULL COMMENT '账号',  `pwd` varchar(20) DEFAULT NULL COMMENT '密码',  `addr` varchar(80) DEFAULT NULL COMMENT '地址',  `tel` varchar(20) DEFAULT NULL COMMENT '电话',  `regtime` char(30) DEFAULT NULL COMMENT '注册时间',  `age` int(11) DEFAULT NULL COMMENT '年龄',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;

所以这里就诞生了两个需求，一个是查询count，一个是分页查询

我们分别来测试一下count用的时间和分页查询所用的时间

select * from user limit 1, 10   //几乎不用时select * from user limit 1000000, 10  //0.35sselect * from user limit 5000000, 10  //1.7sselect * from user limit 9000000, 10  //2.8sselect count(1) from user  //1.7s

alter table `user` add INDEX `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)

其实，创建联合索引，是为了有条件查询的时候速度更快，而不是全表查询

select * from user where uname='6.445329111484186' //3.5s(无联合索引)select * from user where uname='6.445329111484186' //0.003s(有联合索引)

这里基本上可以证明，加了索引和不加索引，进行全表查询的时候，效率就是会很慢

既然索引这个结果已经不好使了，那就只能找其他方案了。根据我之前mysql面试里面讲的，count我们可以单独存储到一个表里面

CREATE TABLE `attribute` (  `id` int(11) NOT NULL,  `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '表名',  `formcount` int(11) NOT NULL COMMENT '表总数据',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

select formcount from attribute where formname='user' //几乎不用时

那么，count是没问题了，分页查询优化要如何优化呢？这里可以使用子查询来优化

select * from user whereid>=(select id from user limit 9000000,1) limit 10 //1.7s

但是如果说数据量太大了，我还是建议走es或者进行一些默认选择，count可以单独列出来

至此，一个千万级的数据分页查询的优化就完成了。

前言

实战调优

总结

更多相关文章

随机推荐