数据库的那些事(全是干货)数据库

来源:互联网 / 作者:SKY / 2018-04-24 15:17 / 点击:
谈到数据库,大家第一想法就是怎么去优化,怎么让查询操作更快。我认为最好的方式就是从开始数据库设计的时候就要尽量考虑周全。如果不幸是个老项目,就得从优化

谈到数据库,大家第一想法就是怎么去优化,怎么让查询操作更快。我认为最好的方式就是从开始数据库设计的时候就要尽量考虑周全。如果不幸是个老项目,就得从优化入手了。接下就从设计和优化谈一下我的一些认识和经验。

数据库的那些事(全是干货)

1:数据库的设计

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。数据库的设计包含数据库架构和业务表的设计。

1)数据库架构

根据不同的数据量和访问量,来设计不同的架构。适合自己的才是最好的。

单实例:数据读取和写入都是一个数据库实例。(备份实例不算在内)。这个适用于小型的企业内部系统。缺点是只适合数据量少的场景,优点是能达到数据的强一致性。

数据库的那些事(全是干货)

垂直拆分,多实例。不同的业务走不同的实例。同样也是适用于单个业务,数据量不大,并且每个业务相对独立,不产生关联。

数据库的那些事(全是干货)

读写分离,主从架构。通过主从结构,主库抗写压力,通过从库来分担读压力。适用于写少读多,数据一致性的实时性要求不高的应用。

数据库的那些事(全是干货)

主从,集群结构。适用于写多,读也多的应用。实现是最为复杂的一种,需要考虑数据如何路由,后期扩容也相对麻烦。需要设计初期在理解业务的基础上,选择好合适的路由策略。例如range,hash等

数据库的那些事(全是干货)

云数据库:阿里云rds等。适合没有专业数据库运维团队的公司,使用非常方便和简单。

2)业务表的设计

常见业务表分类:

(1)配置表

这种表通常存放业务一些基础的配置信息或者字典信息。表的数据量一般都比较小,修改变化的操作不太频繁,通常都是Select查询操作。

(2)状态表

这种表通常存放在业务系统中实体读象的状态信息,常见的有用户信息表,订单信息表等。这种表的数据量与实体读象的规模有直接关系,比如一个APP有多少注册用户,通常这个APP的用户表都会有多少条记录。状态表的变化通常比较频繁,而且Insert、Update、Select操作都会有,Delete操作是否有,通常会根据业务情况的规定决定。

(3)日志表

这种表通常用来记录业务系统中某种实体的状态信息,常见的有用户登录表、充值信息记录表等。这种表的数据规模通常比较大,而且如果业务状态变化频繁,记录的变化信息比较多,这种表的数据量和插入性能都要求比较高。日志表的操作,通常会以Insert操作为主,个别业务会对日志表进行查询。MySQL五种特殊需求架构中的高性能写入架构,主要就是应用这种表的需求。

(4)归档表

这种表,是将上面三种OLTP业务表的数据进行归档或者冷热分离的表。对线上业务三类表进行数据归档、冷热分离,一方面可以控制线上业务表的数据规模,保证业务表性能;另一方面进行归档后,可用于对归档历史数据进行更好的查询反映和支持。归档表的数据量大小与对应的线上表大小、归档周期有关。归档表的操作,除了归档过程的数据加载外,主要就是Select查询操作了,归档后就算是只读表。

(5)统计数据表

统计数据表,是指业务有离线统计分析需求时,需要将各种线上表和归档表的数据,通过ETL过程流转到线上OLAP统计分析系统中的原始数据表。这类表通常数据量会非常大,一个OLAP统计分析平台会汇总多个线上业务系统的数据进行统计分析。统计数据表的操作,除了数据流转动作外,主要就是各种统计分析程序的访问计算。

(6)统计结果表

统计结果表是在业务有离线统计分析需求时,各种统计分析过程访问统计数据表中的数据,按照一定的逻辑进行统计分析后的结果数据。这种统计结果数据,通常数据量会比较小。统计结果表的操作,处理结果流转动作外,主要就是供访问接口进行Select查询。

对业务表类型的梳理,可以对所有的业务系统进行一个大体的划分,做到心中有数。

下面是设计表的一些经验:

将字段很多的表分解成多个表:对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来,因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

增加中间表:对于需要经常联合查询的表,可以建立中间表以提高查询效率。

增加冗余字段:合理的加入冗余字段可以提高查询速度。(冗余字段会导致一些问题,比如,冗余字段的值在一个表中被修改了,就要同步关联的表,否则会导致数据不一致。这要根据实际情况,平衡数据库性能,进行冗余字段的设计。)

所有字段均定义为NOT NULL,除非你真的想存储null。

提前做好数据量的预估,进行分表设计。不要等需要拆分时再拆,一般把表的数据量控制在千万级别。当单表数据量达到一定程度时(MySQL5.xA5的性能拐点则为1KW - 2KW行级别,具体需根据实际情况测试),为了提升性能,最为常用的方法就是分表。分表的策略可以是垂直拆分(比如:不同订单状态的订单拆分到不同的表),也可以是水平拆分(比如:按月将订单拆分到不同表)。如果在业务层分表,会将逻辑变得复杂,而且分散。可以引入分表的中间件屏蔽分表后的细节,让业务层像查询单表一样查询分表后的数据。比如Mycat。(访问量不大,但是表数据很多的表,我们可以采取分区表,实现起来也比较简单)

选择统一的字符集。MySQL采用“阶梯”式的方式来设定字符集默认值,每个数据库,每张表都有自己的默认值,它们逐层继承,最终最靠底层的默认设置将影响你创建的对象。不同字符集和校对规则之间的转换可能会带来额外的系统开销,影响数据库的性能。

合理的设置主键和索引。

主键分自增主键和业务主键。

自增主键:写入、查询效率和磁盘利用率都高,但每次查询都需要两级索引,因为线上业务不会有直接使用主键列的查询。

业务主键:写入、查询效率和磁盘利用率都低,但可以使用一级索引,依赖覆盖索引的特性,某些情况下在非主键索引上也可以实现1次索引完成查询

一般情况下都是采用业务主键。

索引大概分为三类。

主键索引:InnoDB会自动在表的主键上创建索引,数据结构使用B+Tree。

非主键索引:非主键列上的索引为二级索引(因为一次查询需要查找两个索引树)

联合索引:联合索引也叫多列索引,索引结构的key包含多个字段,排序时先第一列比较,如果相同再按第二列比较,以此类推。

2:数据库的优化(mysql)

说起mysql优化,一定要了解一下mysql原理,这样才能深入的理解那些sql规则。下图展示了MySQL的逻辑架构图。

数据库的那些事(全是干货)

阅读延展

1
3