江苏龙网

  1. 主页 > 生活百科 > >

Mysql某个表有近千万数据，CRUD比较慢，如何优化？

优化mysql


Mysql某个表有近千万数据，CRUD比较慢，如何优化？

文章插图
 
数据千万级别之多 ， 占用的存储空间也比较大 ， 可想而知它不会存储在一块连续的物理空间上 ， 而是链式存储在多个碎片的物理空间上 。可能对于长字符串的比较 ， 就用更多的时间查找与比较 ， 这就导致用更多的时间 。
可以做表拆分 ， 减少单表字段数量 ， 优化表结构 。
在保证主键有效的情况下 ， 检查主键索引的字段顺序 ， 使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致 。
【Mysql某个表有近千万数据，CRUD比较慢，如何优化？】主要两种拆分 垂直拆分 ， 水平拆分 。
垂直分表
也就是“大表拆小表” ， 基于列字段进行的 。一般是表中的字段较多 ， 将不常用的 ，  数据较大 ， 长度较长（比如text类型字段）的拆分到“扩展表“ 。一般是针对那种几百列的大表 ， 也避免查询时 ， 数据量太大造成的“跨页”问题 。
垂直分库针对的是一个系统中的不同业务进行拆分 ， 比如用户User一个库 ， 商品Product一个库 ， 订单Order一个库 。切分后 ， 要放在多个服务器上 ， 而不是一个服务器上 。为什么？ 我们想象一下 ， 一个购物网站对外提供服务 ， 会有用户 ， 商品 ， 订单等的CRUD 。没拆分之前 ，  全部都是落到单一的库上的 ， 这会让数据库的单库处理能力成为瓶颈 。按垂直分库后 ， 如果还是放在一个数据库服务器上 ，  随着用户量增大 ， 这会让单个数据库的处理能力成为瓶颈 ， 还有单个服务器的磁盘空间 ， 内存 ， tps等非常吃紧 。所以我们要拆分到多个服务器上 ， 这样上面的问题都解决了 ， 以后也不会面对单机资源问题 。
数据库业务层面的拆分 ， 和服务的“治理” ， “降级”机制类似 ， 也能对不同业务的数据分别的进行管理 ， 维护 ， 监控 ， 扩展等 。数据库往往最容易成为应用系统的瓶颈 ， 而数据库本身属于“有状态”的 ， 相对于Web和应用服务器来讲 ， 是比较难实现“横向扩展”的 。数据库的连接资源比较宝贵且单机处理能力也有限 ， 在高并发场景下 ， 垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈 。
水平分表
针对数据量巨大的单张表（比如订单表） ， 按照某种规则（RANGE,HASH取模等） ， 切分到多张表里面去 。但是这些表还是在同一个库中 ， 所以库级别的数据库操作还是有IO瓶颈 。不建议采用 。
水平分库分表
将单张表的数据切分到多个服务器上去 ， 每个服务器具有相应的库与表 ， 只是表中数据集合不同 。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力 ， 突破IO、连接数、硬件资源等的瓶颈 。
水平分库分表切分规则
1. RANGE
从0到10000一个表 ， 10001到20000一个表；
2. HASH取模
一个商场系统 ， 一般都是将用户 ， 订单作为主表 ， 然后将和它们相关的作为附表 ， 这样不会造成跨库事务之类的问题 。取用户id ， 然后hash取模 ， 分配到不同的数据库上 。
3. 地理区域
比如按照华东 ， 华南 ， 华北这样来区分业务 ， 七牛云应该就是如此 。
4. 时间
按照时间切分 ， 就是将6个月前 ， 甚至一年前的数据切出去放到另外的一张表 ， 因为随着时间流逝 ， 这些表的数据 被查询的概率变小 ， 所以没必要和“热数据”放在一起 ， 这个也是“冷热数据分离” 。
分库分表后面临的问题
事务支持
分库分表后 ， 就成了分布式事务了 。如果依赖数据库本身的分布式事务管理功能去执行事务 ， 将付出高昂的性能代价； 如果由应用程序去协助控制 ， 形成程序逻辑上的事务 ， 又会造成编程方面的负担 。
跨库join
只要是进行切分 ， 跨节点Join的问题是不可避免的 。但是良好的设计和切分却可以减少此类情况的发生 。解决这一问题的普遍做法是分两次查询实现 。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据 。


推荐阅读


上一篇：数据库表分区是怎么回事？

下一篇：教你百度小程序如何配置H5域名