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

这里先说明一下,网上很多人说阿里规定500w数据就要分库分表。实际上,这个500w并不是定义死的,而是与MySQL的配置以及机器的硬件有关。

MySQL为了提升性能,会将表的索引装载到内存中。但是当表的数据到达一定的量的时候,会导致内存无法存储这些索引,无法存储索引,就只能进行磁盘IO,从而导致性能下降。

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

实战调优

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

  • CREATETABLE`user`(
  • `id`int(10)NOTNULLAUTO_INCREMENT,
  • `uname`varchar(20)DEFAULTNULLCOMMENT'账号',
  • `pwd`varchar(20)DEFAULTNULLCOMMENT'密码',
  • `addr`varchar(80)DEFAULTNULLCOMMENT'地址',
  • `tel`varchar(20)DEFAULTNULLCOMMENT'电话',
  • `regtime`char(30)DEFAULTNULLCOMMENT'注册时间',
  • `age`int(11)DEFAULTNULLCOMMENT'年龄',
  • PRIMARYKEY(`id`)
  • )ENGINE=InnoDBAUTO_INCREMENT=10000003DEFAULTCHARSET=utf8;
  • MySQL千万级数据的表如何优化

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

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

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

  • select*fromuserlimit1,10//几乎不用时
  • select*fromuserlimit1000000,10//0.35s
  • select*fromuserlimit5000000,10//1.7s
  • select*fromuserlimit9000000,10//2.8s
  • selectcount(1)fromuser//1.7s
  • 从上面查询所用时间可以看出来,如果是分页查询的话,查询的数据越往后用时是越长的,查询count也需要1.7s。这显然是不符合我们的要求的。所以,这里我们就需要优化。首先我们在这里进行索引优化试试

    首先看一下这是只有主键索引的执行计划:

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

  • altertable`user`addINDEX`sindex`(`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)
  • MySQL千万级数据的表如何优化

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

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

  • select*fromuserwhereuname='6.445329111484186'//3.5s(无联合索引)
  • select*fromuserwhereuname='6.445329111484186'//0.003s(有联合索引)
  • 所以这就是有联合索引和无索引的差距

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

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

  • CREATETABLE`attribute`(
  • `id`int(11)NOTNULL,
  • `formname`varchar(50)COLLATEutf8_binNOTNULLCOMMENT'表名',
  • `formcount`int(11)NOTNULLCOMMENT'表总数据',
  • PRIMARYKEY(`id`)
  • )ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;
  • MySQL千万级数据的表如何优化

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

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

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

  • select*fromuserwhere
  • id>=(selectidfromuserlimit9000000,1)limit10//1.7s
  • 其实子查询这种写法,判断id,其实就是通过覆盖索引来查询。效率会大大增加。不过我这里测试是1.7s,以前在公司优化这方面的时候,比这个查询时间要低,大家也可以自己生成数据自己测试

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

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

    原文链接:https://juejin.cn/post/6989211911384481800

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/7365/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-08-01 7:30:47
    下一篇 2022-08-01 7:30:59

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!