优化思路
思路一
- 优化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则无法用到这个联合索引。5>
- 当需要查询的数据库字段全部在索引中体现时,数据库可以直接查询索引得到查询信息无须对整个表进行扫描(这就是所谓的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官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你。