Mysql 大表优化
如果存储的数据过多,查询效率会大大降低,如何去解决这个问题?
1,限定数据的范围:禁止不带任何限制数据范围条件的查询语句,这样就不用查询整个数据库
2,范式优化(垂直拆表):优化为 BC 范式,删除重复数据,或者单纯的拆字段
3,水平拆表:保持数据表结构不变,通过某种策略将存储的数据分片。这样每一片数据分散到不同的表或者库中
4,读写分离:主读副写,配置一下来实现 mysql 的主从复制
今天介绍的重点就是垂直拆表(拆字段)和水平拆表(拆数据)
分表
垂直分表:表中的字段较多,一般将不常用的、数据较大、长度较长的拆分到扩展表。一般情况加表的字段可能有几百列,此时是按照字段进行数竖直切。注意垂直分是列多的情况
垂直分表的拆分原则是将热点数据(可能会冗余经常一起查询的数据)放在一起作为主表,非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来,进而减少了随机读 IO。拆了之后,要想获得全部数据就需要关联两个表来取数据。但记住,千万别用 join,因为 join 不仅会增加 CPU 负担并且会讲两个表耦合在一起
水平分表:单表的数据量太大。按照某种规则(RANGE、HASH 取模等),切分到多张表里面去。 但是这些表还是在同一个库中,所以库级别的数据库操作还是有 IO 瓶颈。这种情况是不建议使用的,因为数据量是逐渐增加的,当数据量增加到一定的程度还需要再进行切分。比较麻烦
同时注意,分库分表不要自己手写代码,这样容易和业务代码耦合,维护起来很困难。我在线上就见过以 product_id 的某位分成10个表的业务代码,我们可以借助一些框架处理这个问题,比如 MyCAT、Cobar 之类的
分库
业务分库:一个数据库的表太多。此时就会按照一定业务逻辑进行垂直切,比如用户相关的表放在一个数据库里,订单相关的表放在一个数据库里。注意此时不同的数据库应该存放在不同的服务器上,此时磁盘空间、内存、TPS 等等都会得到解决。这个一般在业务线上都有涉及
使用业务分库基本上就可以服务化了。例如,随着业务的发展一些公用的配置表、字典表等越来越多,这时可以将这些表拆到单独的库中,甚至可以服务化
数据分库:水平分库理论上切分起来是比较麻烦的,它是指将单张表的数据根据某种依据(hash、range等)切分到多个服务器上去,每个服务器具有相应的库与表,只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力,突破 IO、连接数、硬件资源等的瓶颈
分库分表问题
非 partition key 的查询问题
partition key 表示数据分库的依据,比如我们按照主键的奇偶性,去将数据分到两个库中。此时主键就是 partition key。如果用户使用了其他字段做查询,我们可以先将这其他字段转换为主键,比如将数据放进 es 中,从 es 中查出主键,然后去数据库中查询完整的数据
扩容问题
数据太多了,我们先再增加几个库存放这张表的数据。此时我们可以借鉴 java 中 hashmap 扩容的做法。扩容是成倍的,就像下图一样