MySQL Optimize Big Table

优化思路

思路一

  • 优化SQL和索引。考虑点:引擎类型,数据分布,索引情况,锁策略,业务特点。
  • 加缓存,比如Redis等等
  • 主从复制;主主复制;读写分离
  • MySQL自带分区表
  • 垂直拆分,其实就是根据你模块的耦合度,将一个大的系统分为多个小的系统,也就是分布式系统;
  • 水平拆分,针对数据量大的表,这一步最麻烦,最能考验技术水平,要选择一个合理的sharding key,为了有好的查询效率,表结构也要改动,做一定的冗余,应用也要改,sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全部的表;

MySQL数据库一般都是按照这个步骤去演化的,成本也是由低到高。

思路二

对于一个存储设计,必须考虑业务特点,收集的信息如下:

  • 数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节;
  • 数据项:是否有大字段,那些字段的值是否经常被更新;
  • 数据查询SQL条件:哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等;
  • 数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中;
  • SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少?
  • 预计大表及相关联的SQL,每天总的执行量在何数量级?
  • 表中的数据:更新为主的业务,还是查询为主的业务
  • 打算采用什么数据库物理服务器,以及数据库服务器架构?
  • 并发如何?
  • 存储引擎选择InnoDB还是MyISAM?

大致明白以上10个问题,至于如何设计此类的大表,应该什么都清楚了!

至于优化若是指创建好的表,不能变动表结构的话,那建议InnoDB引擎,多利用点内存,减轻磁盘IO负载,因为IO往往是数据库服务器的瓶颈。

另外对优化索引结构去解决性能问题的话,建议优先考虑修改类SQL语句,使他们更快些,不得已只靠索引组织结构的方式,当然此话前提是,
索引已经创建的非常好,若是读为主,可以考虑打开query_cache,以及调整一些参数值:sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

思路三

MySQL 数据库设计总结:https://cloud.tencent.com/developer/article/1004367

  • 联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))

    • 联合索引中的字段应尽量满足过滤数据从多到少的顺序,也就是说差异最大的字段应该房子第一个字段
    • 建立索引尽量与SQL语句的条件顺序一致,使SQL语句尽量以整个索引为条件,尽量避免以索引的一部分(特别是首个条件与索引的首个字段不一致时)作为查询的条件
    • Where a=1;where a>=12 and a<15;where a=1 and b<5 ;where a="1" and b="7" c>=40为条件可以用到此联合索引;而这些语句where b=10;where c=221;where b>=12 and c=2则无法用到这个联合索引。
    • 当需要查询的数据库字段全部在索引中体现时,数据库可以直接查询索引得到查询信息无须对整个表进行扫描(这就是所谓的key-only),能大大的提高查询效率。特别注意,当a,ab,abc与其他表字段关联查询时可以用到索引。

  • 合理构造Query语句

    • Insert语句中,根据测试,批量一次插入1000条时效率最高,多于1000条时,要拆分,多次进行同样的插入,应该合并批量进行。注意query语句的长度要小于mysqld的参数 max_allowed_packet
    • 查询条件中各种逻辑操作符性能顺序是and > or > in,因此在查询条件中应该尽量避免使用在大集合中使用in

思路四

背景:有三张表,是5亿的数据,每天张表每天的增量是100w,每张表大概在10个columns左右

当前使用mysiam,没有做分区。在只读情况下,比innodb的效率要高13%左右。

在做了partition之后,读一下mysql的官方文档,其实对于partition,专门是对myisam做的优化,对于innodb,所有的数据是存在ibdata里面的,所以即使你可以看到schema变了,其实没有本质的变化。在分区出于同一个physical disk下面的情况下,提升大概只有1%

在分区在不同的physical disk下,分到了三个不同的disks下,提升大概在3%,其实所谓的吞吐量,由很多因素决定的,比如你的explain parition时候可以看到,record在那一个分区,如果每个分区都有,其实本质上没有解决读的问题,这样只会提升写的效率。

另外一个问题在于,分区,你怎么分,如果一张表,有三个column都是经常被用于做查询条件的,其实是一件很悲惨的事情,因为你没有办法对所有的sql做针对性的分区,如果你只是如mysql官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你。

参考链接

https://www.zhihu.com/question/19719997