mysql百万到千万级别数据量的优化方案

2021/04/18 07:55
阅读数 149

点击上方蓝色字体,选择“标星公众号”

优质文章,第一时间送达

76套java从入门到精通实战课程分享

百万级

1. 字段选择优化

  • 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0。

  • 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1。因为这样可以加上 UNSIGNED,数值容量就会扩大一倍。

  • 可以的话用 TINYINT、SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小。

  • 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实现用整型代替的。

  • 字符串类型长度不要随意设置,保证满足业务的前提下尽量小,用整型来存 IP。

  • 单表不要有太多字段,建议在20以内。

  • 为能预见的字段提前预留,因为数据量越大,修改数据结构越耗时。

2. 索引设计优化

索引,空间换时间的优化策略,基本上根据业务需求设计好索引,足以应付百万级的数据量,养成使用 explain 的习惯,关于 explain 也可以访问:explain 让你的 sql 写的更踏实了解更多。

  • 一个常识:索引并不是越多越好,索引是会降低数据写入性能的。

  • 索引字段长度尽量短,这样能够节省大量索引空间

  • 取消外键,可交由程序来约束,性能更好。

  • 复合索引的匹配最左列规则,索引的顺序和查询条件保持一致,尽量去除没必要的单列索引。

  • 值分布较少的字段(不重复的较少)不适合建索引,比如像性别这种只有两三个值的情况字段建立索引意义不大。

  • 需要排序的字段建议加上索引,因为索引是会排序的,能提高查询性能。

  • 字符串字段使用前缀索引,不使用全字段索引,可大幅减小索引空间。

3.查询语句优化

  • 尽量使用短查询替代复杂的内联查询。

  • 查询不使用 select *,尽量查询带索引的字段,避免回表。

  • 尽量使用 limit 对查询数量进行限制。

  • 查询字段尽量落在索引上,尤其是复合索引,更需要注意最左前缀匹配。

  • 拆分大的 delete / insert 操作,一方面会锁表,影响其他业务操作,还有一方面是 MySQL 对 sql 长度也是有限制的。

  • 不建议使用 MySQL 的函数,计算等,可先由程序处理,从上面提的一些点会发现,能交由程序处理的尽量不要把压力转至数据库上。因为多数的服务器性能瓶颈都在数据库上。

  • 查询 count,性能:count(1) = count(*) > count(主键) > count(其他字段)。

  • 查询操作符能用 between 则不用 in,能用 in 则不用 or。

  • 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符,因为这些查询无法使用索引。

  • sql 尽量简单,少用 join,不建议两个 join 以上。

千万级数据量

到了这个阶段的数据量,数据本身已经有很大的价值了,数据除了满足常规业务需求外,还会有一些数据分析的需求。而这个时候数据可变动性不高,基本上不会考虑修改原有结构,一般会考虑从分区,分表,分库三方面做优化:


1.分区

分区是根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分,是一种水平划分。对应用来说是完全透明的,不影响应用的业务逻辑,即不用修改代码。因此能存更多的数据,查询,删除也支持按分区来操作,从而达到优化的目的。如果有考虑分区,可以提前做准备,避免下列一些限制:

  • 一个表最多只能有1024个分区(mysql5.6之后支持8192个分区)。但你实际操作的时候,最好不要一次性打开超过 100 个分区,因为打开分区也是有时间损耗的。

  • 如果分区字段中有主键或者唯一索引列,那么所有主键列和唯一索引列都必须包含进来,如果表中有主键或唯一索引,那么分区键必须是主键或唯一索引。对于这点,笔者补充说明,笔者本人是按 created_at 所在天进行分区的,所以建表如下

CREATE TABLE `sms_record_hash` (
  ...... 省略字段定义
  PRIMARY KEY (`id`,`created_at`)  
  ......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (DAYOFYEAR(created_at))
PARTITIONS 366 */;

  • 分区表中无法使用外键约束。

  • NULL值会使分区过滤无效,这样会被放入默认的分区里,请千万不要让分区字段出现 NULL。

  • 所有分区必须使用相同的存储引擎。

2.分表

  • 分表分水平分表和垂直分表。

  • 水平分表即拆分成数据结构相同的各个小表,如拆分成 table1, table2…,从而缓解数据库读写压力。

  • 垂直分表即将一些字段分出去形成一个新表,各个表数据结构不相同,可以优化高并发下锁表的情况。

  • 可想而知,分表的话,程序的逻辑是需要做修改的,所以,一般是在项目初期时,预见到大数据量的情况,才会考虑分表。后期阶段不建议分表,成本很大。

3.分库

分库一般是主从模式,一个数据库服务器主节点复制到一个或多个从节点多个数据库,主库负责写操作,从库负责读操作,从而达到主从分离,高可用,数据备份等优化目的。


当然,主从模式也会有一些缺陷,主从同步延迟,binlog 文件太大导致的问题等等,这里就不细讲了!

————————————————

版权声明:本文为CSDN博主「蓝颜~岁月」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:

https://blog.csdn.net/weixin_40172337/article/details/114379553





锋哥最新SpringCloud分布式电商秒杀课程发布

👇👇👇


   
   
   
👆长按上方微信二维码 2 秒





感谢点赞支持下哈 

本文分享自微信公众号 - java1234(gh_27ed55ecb177)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部