如何优化MySQL千万级数据的表

最新推荐文章于 2024-08-08 07:00:00 发布

原创

最新推荐文章于 2024-08-08 07:00:00 发布 · 347 阅读

标签

#java #mysql优化

本文介绍了如何优化拥有千万数据的MySQL表，针对查询count和分页查询的性能问题，提出了索引优化和利用子查询的方法。通过创建联合索引改善有条件查询的速度，同时为count字段单独存储以加快查询。对于分页查询，使用子查询配合覆盖索引提升效率。当数据量过大时，建议考虑使用ES或其他解决方案。

这里先说明一下，网上很多人说阿里规定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;
复制代码

查询所有大概16s。可谓是相当慢了。通常我们一个后台系统，比如这个是一个电商平台，这个是用户表。后台管理系统，一般会查询这些用户信息，做一些操作，比如后台直接新增用户啊，或者删除用户啊这些操作。