MySQL 不同数据量的优化方案

小雷  |  2019. 07. 13   |  阅读 37 次
MySQL 数据 Node.js

MySQL 数据量达到百万级时,数据库的性能问题会逐渐显露出来,故本文就不同阶段不同数据量级的情况聊聊对应的优化方案。

百万级数据量

这个数据量基本上大家都经历过,也能感知一些性能问题显露出来了,这个阶段的优化几乎是最重要的,因为到后期千万级,甚至亿级别的阶段,数据库几乎无法动弹,可调整性很低。下面主要以字段,索引,sql 编写三方面优化:

字段优化

  • 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0。
  • 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1。因为这样可以加上 UNSIGNED,数值容量就会扩大一倍。
  • 可以的话用 TINYINT、SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小。
  • 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实现用整型代替的。
  • 字符串类型长度不要随意设置,保证满足业务的前提下尽量小。
  • 用整型来存 IP。
  • 单表不要有太多字段,建议在20以内。
  • 为能预见的字段提前预留,因为数据量越大,修改数据结构越耗时。
  • ...

索引设计

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

  • 一个常识:索引并不是越多越好,索引是会降低数据写入性能的。
  • 索引字段长度尽量短,这样能够节省大量索引空间;
  • 取消外键,可交由程序来约束,性能更好。
  • 复合索引的匹配最左列规则,索引的顺序和查询条件保持一致,尽量去除没必要的单列索引。
  • 值分布较少的字段(不重复的较少)不适合建索引,比如像性别这种只有两三个值的情况字段建立索引意义不大。
  • 需要排序的字段建议加上索引,因为索引是会排序的,能提高查询性能。
  • 字符串字段使用前缀索引,不使用全字段索引,可大幅减小索引空间。
  • ...

编写习惯

  • 查询不使用 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 以上。
  • ...

注:上述优化涉及到表结构修改,这个时候一定要小心,最好先做实验,一些耗时的操作中断也要好久。

千万级数据量

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

分区

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

  • 一个表最多只能有1024个分区(mysql5.6之后支持8192个分区)。
  • 如果分区字段中有主键或者唯一索引列,那么所有主键列和唯一索引列都必须包含进来,如果表中有主键或唯一索引,那么分区键必须是主键或唯一索引。
  • 分区表中无法使用外键约束。
  • NULL值会使分区过滤无效。
  • 目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。
  • 所有分区必须使用相同的存储引擎。

分区可按以下四种类型分区:

  • RANGE表分区:范围表分区,按照一定的范围值来确定每个分区包含的数据;
  • LIST表分区:列表表分区,按照一个一个确定的值来确定每个分区包含的数据;
  • HASH表分区:哈希表分区,按照一个自定义的函数返回值来确定每个分区包含的数据;
  • KEY表分区 :key表分区,与哈希表分区类似,只是用MySQL自己的HASH函数来确定每个分区包含的数据。

分表

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

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

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

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

分库

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

当然,主从模式也会有一些缺陷,主从同步延迟,binlog 文件太大导致的问题等等,这里不细讲(笔者也学不动了)。

亿级数据量

提前说明,这个数据量笔者没实践过,只能根据看到的听到的分享下自己的看法😊。

理论上,MySQL 绝对是可以支撑这个级别数据的,但是现在大部分公司数据库性能水平完全取决于开发人员能力,当然一些公司有 DBA 把关,如没有上述的优化习惯,大多情况到这个阶段,MySQL 数据库性能已经接近崩溃临界点,甚至有些都到不了这个阶段。

以下列了几点建议,可以做参考:

  • 运维方面做优化,MySQL 参数调优,增强硬件设备。
  • 分析一个业务需求时不仅仅只考虑数据库方面,应该全方面的考量,考虑缓存,日志,延迟读写等方面来分担数据库压力。
  • 不排除采用类如 HBase 这种大数据处理方案和云解决方案,尽管这个阶段还能勉强支撑,但如果数据库已经到崩溃边缘,风险也已经很大了。
  • ...

总结

本文分别从三个阶段聊了优化方案,百万级数据量阶段主要列了一些优化习惯,绝对不止这些,若有疑问或补充的都可以联系笔者:zhangxianlei@souche.com。千万级数据量阶段主要提供了优化方向,具体优化要结合自身的业务特性,没有深入,点到为止。亿级数据量,主要分享了自身的一些想法,仅限参考。强调的一点就是:数据本身以及衍生挖掘的价值也足够大。

参考

分享到

   
Kafka-node 的简单 Demo
加入我们