文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL中怎么优化千万级数据表

2023-06-20 19:24

关注

MySQL中怎么优化千万级数据表,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

我这里有张表,数据有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;

MySQL中怎么优化千万级数据表

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

所以这里就诞生了两个需求,一个是查询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

从上面查询所用时间可以看出来,如果是分页查询的话,查询的数据越往后用时是越长的,查询count也需要1.7s。这显然是不符合我们的要求的。所以,这里我们就需要优化。首先我们这里进行索引优化试试
首先看一下这是只有主键索引的执行计划:

MySQL中怎么优化千万级数据表

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

MySQL中怎么优化千万级数据表

看上面的执行计划,虽然type是从all->index,走了sindex索引,但是实际上查询速度并没有发生改变。

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

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;

MySQL中怎么优化千万级数据表

这里说一下,这种表一般不会查所有,只会查询一条,所以建表的时候,可以建成hash

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

count就进行优化完了。如果上面有选择条件的话,就可以建立索引,通过走索引筛选的形式来查询,这样就可以不用读这个count了。

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

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

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注编程网行业资讯频道,感谢您对编程网的支持。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯