数据库分库分表策略和分库分表后数据的查询
在日常的工作中，关系型数据库本身比较容易成为系统的瓶颈点，虽然读写分离能分散数据库的读写压力，但并没有分散存储压力，当数据量达到千万甚至上亿时，单台数据库服务器的存储能力会成为系统的瓶颈，主要体现在以下几个方面：数据量太大，读写的性能会下降，即使有索引，索引也会变得很大，性能同样会降下。数据库文件会得很大，数据库备份和恢复需要耗时很长。数据库文件越大，极端情况下丢失数据的风险越高。...
在日常的工作中，关系型数据库本身比较容易成为系统的瓶颈点，虽然读写分离能分散数据库的读写压力，但并没有分散存储压力，当数据量达到千万甚至上亿时，单台数据库服务器的存储能力会成为系统的瓶颈，主要体现在以下几个方面：
- 数据量太大，读写的性能会下降，即使有索引，索引也会变得很大，性能同样会降下。
- 数据库文件会得很大，数据库备份和恢复需要耗时很长。
- 数据库文件越大，极端情况下丢失数据的风险越高。
因此，当流量越来越大时，且单机容量达到上限时，此时需要考虑对其进行切分，切分的目的就在于减少单机数据库的负担，将由多台数据库服务器一起来分担，缩短查询时间。
切分策略
数据切分分为两种方式，纵向切分和水平切分
- 纵向切分
常见有纵向分库纵向分表两种。
1）. 纵向分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库，做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与“微服务治理”的做法相似，每个微服务使用单独的一个数据库。
2）. 垂直分表是基于数据库中的列进行，某个表字段较多，可以新建一张扩展表，将不经常用或者字段长度较大的字段拆出到扩展表中。在字段很多的情况下，通过大表拆小表，更便于开发与维护，也能避免跨页问题，MYSQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的开销。另外，数据库以行为单位将数据加载到内存中，这样表中字段长度越短且访问频次较高，内存能加载更多的数据，命中率更高，减少磁盘IO，从而提升数据库的性能。
- 垂直切分的优点：
- 解决业务系统层面的耦合，业务清晰
- 与微服务的治理类似，也能对不同业务的数据进行分级管理，维护，监控，扩展等。
- 高并发场景下，垂直切分一定程度的提升IO，数据库连接数，单机硬件资源的瓶颈。
- 垂直切分的缺点
- 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度。
- 分布式事处理复杂
- 依然存在单表数据量过大的问题。
- 水平切分
当一个应用难以再细粒度的垂直切分或切分后数据量行数依然巨大，存在单库读写，存储性能瓶颈，这时候需要进行水平切分。
水平切分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。
库内分表只解决单一表数据量过大的问题，但没有将表分布到不同机器的库上，因些对于减轻mysql的压力来说帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
- 水平切分优点
- 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。
- 应用端改造较小，不需要拆分业务模块。
- 水平切分缺点
- 跨分片的事务一致性难以保证
- 跨库的join关联查询性能较差
- 数据多次扩展维度和维护量极大。
路由规则
水平切分后同一张表会出现在多个数据库或表中，每个库和表的内容不同，对于水平分表后分库后，如何知道哪条数据在哪个库里或表里，则需要路由算法进行计算，这个算法会引入一定的复杂性。
-
范围路由
选取有序的数据列，如时间戳作为路由的条件，不同分段分散到不同的数据库表中，以最常见的用户ID为例，路由算法可以按照1000000的范围大小进行分段，1 ~ 9999999放到数据库1的表中，10000000~199999999放到数据库2的表中，以此累推。
范围路由设计的复杂点主要体现在分段大小的选取上，分段太小会导致切分后子表数量过多增加维护复杂度，分段太大可能会导致单表依然存在性能问题，按一般大老们的经验，分段大小100W至2000W之间，具体需要根据业务选 取合适的分段大小。
- 范围路由的优点
- 可以随着数据的增加平滑地扩充新的表或库，原有的数据不需要动。
- 单表大小可控
- 使用分片字段进行范围查找时，连续分片可快速定位查询，有效避免分片查询的问题。
- 热点数据成为性能瓶颈，连续分片可能存在数据热点，例如按时单字段分片，有些分片存储最近时间内的数据，可能会被频繁读写，而有些历史数据则很少被查询。
-
hash算法
选取某个列或几个列的值进行hash运算，然后根据hash的结果分散到不同的数据库表中，以用ID为例，假如我们一开始就规划10个数据库表，路由算法可以简单地用id % 10的值来表示数据所属的数据库编号，ID为985的用户放到编号为5的子表中。ID为10086编号放到编号为6的表中。
Hash路由设计的复杂点主要体现 在初始表数量的选取上，表数量太多维护比较麻烦，表数量太小又可能导致单表性能存在问题。而用Hash路由后，增加字表数量是非常麻烦的，所有数据都要重新分布。
Hash路由的优缺点与范围路由相反，Hash路由的优点是表分布比较均匀，缺点是扩容时很麻烦，所有数据均需要重新分布。
-
路由配置
配置路由就是路由表，用一张独立的表来记录路由信息。同样以用户ID为例，我们新增一张ROUTER表，这个表包含table_Id两列，根据user_id就可以查询对应的修改路由表就可以了。
配置路由设计简单，使用起来非常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，然后修改路由表就可以了。
其缺点就是必须多查询一次，会影响整体性能，而且路由表本身如果太大，性能会成为瓶颈点，如果我们再将路由表分库分表，则又面临一个死循环。
分库分表带来的问题
-
join操作
水平分表后，虽然物理上分散在多个表中，如果需要与其它表进行join查询，需要在业务代码或者数据库中间件中进行多次join查询，然后将结果合并。
-
COUNT（*）操作
水平分表后，某些场景下需要将这些表当作一个表来处理，那么count(*)显得没有那么容易 了。
-
order by 操作
分表后，数据分散到多个表中，排序操作无法在数据库中完成，只能由业务代码或数据中间件分别查询每个子表中的数据，然后汇总进行排序。
分库分表后的查询：
在分表完之后显然对于数据的查询会变的比较的复杂，特别是在表的关联方面，在有些情况下根本就不能使用JOIN。
其实个人是比较鼓励将那些大的JOIN SQL拆分成几个小的SQL来查询数据。这样虽然总体的效率可能会稍稍下降（如果使用了连接池完全可以忽略），但是查询的语句变简单了，使得后续的维护带来的方便。同时也能带来比较便利的扩展。你可以感受一下有一个100行的SQL语句给你维护，和给你10个10行并且每一块都有很好的注释的SQL去维护，去帮助调优。你愿意选哪个。不管你们信不信，反正我是选第二种，而且第二种可以很好的理解业务。
上面说到要拆分JOIN，我的意思不是将每个语句都拆分。我的准则是 O(n) 次的查询。忌讳那种查出数据后通过程序循环查出结果再去数据库中查询，也就是需要 O(n*M)这种。 瞬间感觉方法论很重要有木有 ^_^。
模拟场景
- 场景1：购买者下订单
1、在浏览商品的时候能获得商品的 门店ID 和 商品ID，至于导购ID这里我们能以随机的形式得到（需要根据业务来确定如何获取导购ID）
2、通过导购ID获得导购的用户信息从而得到导购的数据应该放在那张分表。
3、将下单数据存入出售者的分表，和购买者的分表。
下面展示的是伪代码(因为只用SQL不好展示具体业务逻辑)，其实是自己比较懒不想写Python了。^_^
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- 获得导购分表信息，和所在门店 SELECT u.table_flag AS guide_flag, ug.store_id AS store_id FROM user AS u, user_guide AS ug WHERE u.user_id = ug.user_id AND user_guide_id = 导购ID;
SET autocommit=0; START TRANSACTION; -- 创建销售订单 sell_order_2 通过程序拼凑出来的 INSERT INTO sell_order_2 VALUES(order_SnowflakeID, 导购ID, 购买者ID, 订单总额, 订单状态); -- 记录此订单有哪些商品 INSERT INTO order_goods_2 VALUES(order_goods_SnowflakeID, order_SnowflakeID, 商品ID, 商品价格, 商品个数); -- 记录购买订单表 buy_order_6 购买者所在的分表，上面的是出售者所在的分表别弄混了 -- 购买者订单ID 和 出售者订单ID是一样的 INSERT INTO buy_order_6 VALUES(order_SnowflakeID, 用户ID, 导购ID)
COMMIT; SET autocommit=1; |
- 情况2：购买者浏览订单
浏览购买者订单就是比较麻烦的，因为购买者订单信息和商品信息不是在同一分表中。
1、分页查找出购买者的订单列表。
2、将订单信息返回给浏览器后，使用ajax获取每个订单的商品。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | -- 获得用户的分表信息 user_id = 66 SELECT table_flag FROM user WHERE user_id=66; +------------+ | table_flag | +------------+ | 9 | +------------+ -- 获取用户订单, 这些信息值直接先返回给浏览器的 SELECT * FROM buy_order_9 WHERE user_id=66 LIMIT 0, 1; +---------------------+---------+---------------+ | buy_order_id | user_id | user_guide_id | +---------------------+---------+---------------+ | 3792111966815784961 | 66 | 1 | +---------------------+---------+---------------+ -- 获取 user_guide_id=1 用户的分表信息 SELECT u.table_flag AS guide_flag FROM user AS u, user_guide AS ug WHERE u.user_id = ug.user_id AND user_guide_id = 1; +------------+ | guide_flag | +------------+ | 2 | +------------+ -- 浏览器通过ajax获取商品信息进行展现 SELECT * FROM order_goods_2 WHERE sell_order_id = 3792111966815784961 AND user_guide_id = 1; +---------------------+---------------------+---------------------+---------------+---------+------+ | order_goods_id | sell_order_id | goods_id | user_guide_id | price | num | +---------------------+---------------------+---------------------+---------------+---------+------+ | 3792112143781859329 | 3792111966815784961 | 3792111950445416449 | 1 | 3100.00 | 2 | | 3792112160789762049 | 3792111966815784961 | 3792111951305248769 | 1 | 5810.00 | 1 | +---------------------+---------------------+---------------------+---------------+---------+------+ |
从上面的试验我们可以看到原本在 '分库分表(1)--基础表介绍' 中的关联查询就能获得出订单的数据现在需要被拆为多个部分来查询(是不可避免的, 这样做也未必不是好事)。
这里说一下我们为什么要使用ajax来获取并展现 '订单商品' 的数据：
1、我们不知道 '购买订单' 的导购的分表是哪一个，因此我们需要便利查询出的每一条 '购买订单'，如果有10个订单就需要便利10次去获取对应导购是哪个分表。
2、获得分表完之后还需要通过每个分表去关联 '订单商品' 获得商品信息。
3、获得到以上信息或需要整合成一个列表返回给浏览器。
通过上面一次性把说有数据返回给浏览器的方法，会影响到用户体验，让用户觉得很慢的感觉。并且需要写复杂的逻辑，难以维护。
我们将查询时间放大，一个查是 1s 如果有10个订单 一次性完成就可能需要 11s 以上的时间才返回给浏览器。如果先将查询的订单返回给浏览器。看上去就只需要 1s就吧数据返回给浏览器了。
- 情况3：导购查看订单
导购也是一个普通用户, 因此一登陆系统就知道 导购ID 和 用户ID
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | -- 获得导购的分表信息 user_id = 6, user_guide_id = 5 SELECT table_flag FROM user WHERE user_id=6; +------------+ | table_flag | +------------+ | 6 | +------------+ -- 查询订单信息 SELECT * FROM sell_order_6 WHERE user_guide_id = 5 LIMIT 0, 3; +---------------------+---------------+---------+---------+--------+ | sell_order_id | user_guide_id | user_id | price | status | +---------------------+---------------+---------+---------+--------+ | 3792112033412943873 | 5 | 10 | 5197.00 | 1 | | 3792112033429721089 | 5 | 10 | 6826.00 | 1 | | 3792112033446498305 | 5 | 10 | 5765.00 | 1 | +---------------------+---------------+---------+---------+--------+ -- 查询订单商品信息 SELECT * FROM order_goods_6 WHERE sell_order_id IN( 3792112033412943873, 3792112033429721089, 3792112033446498305 ); +---------------------+---------------------+---------------------+---------------+---------+------+ | order_goods_id | sell_order_id | goods_id | user_guide_id | price | num | +---------------------+---------------------+---------------------+---------------+---------+------+ | 3792112273532653569 | 3792112033412943873 | 3792111951800176641 | 5 | 7826.00 | 1 | | 3792112292964864001 | 3792112033412943873 | 3792111952559345665 | 5 | 3057.00 | 2 | | 3792112273545236481 | 3792112033429721089 | 3792111952660008961 | 5 | 8540.00 | 1 | | 3792112292981641217 | 3792112033429721089 | 3792111951863091201 | 5 | 8545.00 | 1 | | 3792112273566208001 | 3792112033446498305 | 3792111952110555137 | 5 | 8383.00 | 2 | | 3792112292998418433 | 3792112033446498305 | 3792111952966193153 | 5 | 3282.00 | 2 | +---------------------+---------------------+---------------------+---------------+---------+------+ |
- 情况4：导购修改订单
| 1 2 | -- 修改订单价格 UPDATE sell_order_6 SET price = 1000.00 WHERE sell_order_id = 3792112033412943873; |
- 情况5：店主为店铺添加商品
添加商品只有店铺的店主有权限。然而店主也是一个普通用户。
| 1 2 3 4 5 6 7 8 9 | -- 获得店主的分表信息 user_id = 1 SELECT table_flag FROM user WHERE user_id=1; +------------+ | table_flag | +------------+ | 2 | +------------+ -- 店主添加商品 INSERT INTO goods_2 VALUES(SnowflakeID, 商品名称, 商品价格, 门店ID); |
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
25
0- 0
已为社区贡献1条内容
所有评论(0)