MYSQL-简单总结

112 阅读12分钟

这是我参与11月更文挑战的第7天,活动详情查看:2021最后一次更文挑战

简单总结

规范

表结构设计

  • INT 类型不使用 unsigned 无符号属性,容易引入额外的计算问题。

  • 自增用 8 字节 BIG INT,不要使用 4 字节 INT,且自增在 MySQL 8.0 版本前有回溯问题,请考虑是否业务有影响。

  • 字符集使用 UTF8MB4 字符编码,不推荐 GBK、UTF-8 等其他字符集。

  • 日期类型用 DATETIME 类型,需要精确到毫秒用 DATETIME(6),不要使用 INT、TIMESTAMP。

  • 类型 JSON 可用于存储非结构化数据,典型场景为用户标签,不要将 JSON 用于频繁更新的字段场景。

  • 每张表一定要有一个主键,这样至少满足一范式的要求,核心业务表用全局唯一字段(雪花算法、有序UUID)做主键,不要使用自增做主键。

  • 对于日志类的流水表、报警表、日志表,可以使用压缩设计,提升存储效率。MySQL 5.7 版本开始推荐使用透明页压缩,不要使用传统的 KEY_BLOCK_SIZE 的页压缩。

  • 类别设计,用 ENUM+CHECK 约束,不要使用 INT 类型的设计。

  • 敏感字段需加密,如账户密码、信用卡号等存储使用:动态盐 + 非固定加密算法(MD5/AES256等) + 多轮加密,不要简单使用 MD5 算法加密,容易被暴力破解。

  • MySQL 可以通过 KV 的方式访问表中的数据,若业务只是简单的 SET、GET 请求,可考虑将其转化为 Memcached 的 KV 访问方式,减少 SQL 解析的开销,性能可以有至少 50% 的提升。

索引设计

  • 不要陷入设置单表行数、列数限制的固有印象,其他关系型数据库没有行数、列数限制,MySQL 也没有,大表的缺点不是性能,而是后续的 DDL 管理问题,随着 MySQL 8.0 快速加列功能的上线,大表 DDL 问题基本已解决。

  • MySQL 是索引组织表,表中的数据以 B+ 树索引结构,根据主键逻辑排序,由于 B+ 树索引的特点是树的高度为 3~4 层,所以从数十亿的记录中,通过主键查询一条记录只需要 3、4 次 I/O,当前到 SSD 存储设备设置每秒至少能完成 10000 次的 I/O 查询,不要担心通过索引查询一条或几条记录的性能,每秒百万次查询并不难。

  • MySQL 是索引组织表,二级索引只存储(键值、主键值),因此需要再通过一次主键索引查询得到记录,这种方式成为回表。在核心业务中,使用索引覆盖技术,提升索引查询性能,对于回表记录数比较大的场景,甚至可以有 10 倍的性能提升;

  • 对类似 WHERE a = ? ORDER BY b 这样的查询,一定要创建(a、b)组合索引,这样可以避免一次额外排序,提升查询性能。

  • MySQL 优化器是 CBO(Cost-based Optimizer),所有查询基于成本而不是规则,若发现 SQL 执行计划发生变化,不要怀疑 MySQL 出错,请先分析数据特点、索引创建是否合理,是否可以通过直方图校准数据。

  • MySQL JOIN 支持 NLJ(Nested Loop Join)和 NHJ(Nested Hash Join)两种方式。对于 OLTP 业务,放心大胆使用 JOIN,但一定要做好索引的设计和索引覆盖的考虑(不考虑分布式数据库场景);对于 OLAP 业务,MySQL 8.0 版本开始,支持 Hash Join,对于大数据量的关联,性能提升非常多,可以在不超过 10T 的数仓场景中考虑使用,超过 10T数据量,请一定使用大数据产品,如 Hive、Spark、麒麟等产品。

  • MySQL 5.7 版本开始子查询优化已经做得不错,但是编写的子查询不能是关联子查询,上线前一定需要确认,若发现关联子查询,请改写子查询为 JOIN 或其他方式。

  • 不要因为数据量大,使用分区表,MySQL 是索引组织表,数据量再大,定位记录也只需要3、4 次 I/O。可以考虑分区表唯一的应用场景是:需要定期清理历史流水类数据,但如果业务可以按月、按天做分表,那么当前 MySQL 8.0 版本,分区表也不推荐使用。

  • 业务上线或新版本发布前,DBA 一定要进行所有 SQL Review,确保 SQL 走索引,否则不予上线,或由业务以邮件等正式方式,通知 DBA 该 SQL 不会引起线上事故,业务方承担后续责任。

  • DBA 每天要对数据库进行巡检,及早发现慢查询或潜在数据库风险,将任何潜在问题尽早抛出,否则后续自己承担相关责任。

高可用架构设计

  • MySQL 高可用的基石是利用二进制日志的复制技术,核心业务一定要使用无损半同步方式,但凡不适用无损半同步的高可用架构,请业务方业务以邮件等正式方式回复,数据丢失等后续问题自己承担相关责任。

  • MySQL 5.7 版本开始,一定要使用基于 WRITESET 的从机回放,避免主从延迟。

  • 当前 MySQL 发生主从延迟的可能性主要是存在大事务,比如定期计算收益等操作,这类大事务,一定要通知业务方将大事务拆成小事务,否则不予上线;若要上线,请业务方以邮件等方式回复,自己承担后续主从延迟带来的后续一系列问题。

  • MySQL 8.0 版本开始推荐金融业务使用 MGR(MySQL Group Replication),通过 Paxos 协议保证数据一致性,并自己完成选主的逻辑,也可以使用多主模式,数据不冲突的情况下,可以大幅提升写入性能。

  • 对于核心业务,必须遵循互不信任原则,数据一致性不单单依赖 MySQL 复制本身,DBA 这里需要通过逻辑的方式,对主从数据进行核对,业务这里也需要一套业务层的逻辑进行“对账”。

  • 核心业务,务必使用一地三中心,两地三中心的跨机房复制架构,这样发生机房级故障,可以切换到另一个机房,保证业务可用性。

  • 同城容灾架构一定要评估切换到另一个机房后业务访问的性能,多次跨机房访问 DB,虽然每次只多了 2~3ms,但也存在业务雪崩问题,推荐 DB 切换机房,上层业务跟着一起联动切换。

  • 对于有跨城容灾需求的业务,可以考虑使用三地五中心架构,但是由于 30ms 延迟,业务需要进行评估,对于核心业务,务必使用业务层的跨城机制,将数据层的多次网络耗时合并为一次,这样能大大提升业务的性能。

  • 业界的 MHA、Ochestrator 等高可用套件都是基于 ssh 访问 MySQL,稳定性、安全性不高,不推荐大厂使用;自己开发一个数据库管理平台,通过 agent 的模式管理高可用和 MySQL 数据库的日常操作更为安全、有效。

  • 一定做好数据备份架构的设计,全备 + 增量备份 + 延迟备机(可选),做到可以基于任何一点恢复和回滚,同时,遵循互不信任原则,备份文件一定要进行检查,确保需要时一定能够进行恢复。

分布式架构设计

  • 分布式数据库的本质就是根据某几个列的规则,将数据水平打散,存在不同的实例中。数据拆分的列就成为分区键,分区键一定是业务大部分访问(超过 80%)的表都会使用的列。若选不出合适的分区键,那就一定不要进行分布式数据库架构的设计;互联网业务绝大部分分区键的选择是用户维度。

  • 分区算法绝大部分场景使用 Hash算法,这样数据的存储和访问可以平均到下面多个实例,真正的做到可扩展性,Range 算法通常无法解决热点问题,会是灾难,但 Range 算法可以但实例中使用,作为二级拆分数据的规则。

  • 分布式数据库分片时,一开始就设计为不少于 1000 个分片的规则,不用担心分片过多的问题,管理 1 个分片和 1000 个分片的成本是一样的,但为后续的扩展做好了充足的准备。

  • 分布式数据库扩缩容就是通过部分过滤的复制技术,按库或按表进行数据同步,分库分表设计推荐库名、表都不同,做到全局唯一,方便后续拆分。

  • 分布式数据库索引设计中,非分区键的唯一索引一定带入分区键信息,这样业务查询时可以直接定位到数据所在分片,提升查询效率。

  • 分布式数据库索引设计中,数据库层的唯一约束只在单个实例中保证,若要保证全局唯一,一定要使用全局唯一的索引设计。

  • 直接使用 JOIN 请确认一定可以单元化在一个分片中完成,如果涉及跨分片的 JOIN,请通知业务修改成多条 SQL 的访问方式,只访问指定分片而不是所有分片。

  • 分布式数据库可以进行业务层的分库分表访问,和通过数据库中间件的访问,对于业务耗时敏感的业务,推荐业务层直接根据路由规则访问数据,否则使用数据库中间件,简单易用。

  • 对于耗时敏感的核心业务,推荐使用最终一致的业务层柔性事务,数据库层的 2PC 分布式事务耗时较大,性能较为一般,但是 2PC 使用简单,能满足大部分业务的使用。

  • 一定要利用好分布式数据库架构的特点,设计多活架构,每个机房都可以有写入流量,提升资源使用率和业务连续性,请 DBA 和业务方一起做好全链路的架构设计。

优雅删除大表

在 MySQL 5.5.23 版本前,当删除一张表时,会去遍历 BP(Buffer Pool 缓冲池)中所有该表对应的页,这时需要持有 BP 的互斥锁,而这是一把巨大的锁,对于 BP 的访问都需要持有该锁才能继续。

MySQL 5.5.23 版本开始,做了两点优化:

  • 遍历脏页列表(flush list)时,只持有一小段时间,之后就释放 BP 的大锁,并且不需要真正刷新脏页,这样的处理能让其他业务请求有机会获得大锁的可能,从而业务的请求不会掉底;

  • 不处理 LRU 列表中干净的页。因为 LRU 中不使用的页会慢慢被淘汰,因此删除表,不用同步地去处理对应的页,可以通过 LRU 机制,异步最终淘汰机制。

MySQL 5.6 版本支持了多个 BP 拆分,5.7 版本支持多个脏页列表,因此对于上述 DROP TABLE 的处理,又得到进一步优化,即持有大锁的时间越来越少,对业务的影响也就越来越少。

I/O 优化

在 DROP TABLE 前,对 ibd 文件创建硬链接

在业务低峰区,异步删除物理文件

ionice -c 2 -n 6 rm xxx.ibd

自适应哈希索引

默认 AHI 参数的设置也是比较合理的,例如参数 innodb_adaptive_hash_index_parts 设置为 8 。然而,AHI 存在一个副作用:当删除大表,且缓冲池(Buffer Pool,下简称 BP)比较大,如超过 32G,则 MySQL 数据库可能会有短暂被 hang 住的情况发生。

产生这个问题的原因是在删除表的时候,InnoDB 存储引擎会将该表在 BP 中的内存都淘汰掉,释放可用空间。这其中包括数据页、索引页、自适应哈希页等。当 BP 比较大时,扫描 BP 中 flush_list 链表需要比较长的时间,因此会产生系统的抖动。

  • 业务低峰期删除大表;

  • 删除表前禁用 AHI 功能;

  • 控制脏页链表长度,只有长度小于一定阈值,才发起删除操作;

  • 删除表后启用 AHI 功能。

不过,这么麻烦的处理在 MySQL 8.0.23 版本之后,就都不再需要了。因为官方已经彻底修复了这个问题

系统共享表空间(System Tablespace)

每张表的表空间都存放到系统的系统共享表空间中,这样删除操作就不需要删除物理空间,而释放的空间可以给到其他表使用。

在初始化时,就完成系统共享表空间的空间分配,即固定单个 MySQL 实例能使用到存储空间上限。这样可以省去后续磁盘空间的申请释放,进一步提升在业务高峰期,数据库耗时的平稳性。

innodb_file_per_table = 0
innodb_data_file_path=ibdata1:500G

用户可能只想日志表、流水表等数据放在一个表空间中,这样 DROP TABLE 时不需要删除物理空间。

CREATE TABLESPACE `ts_log` ADD DATAFILE 'ts_log.ibd'
CREATE TABLE log_2020_01 ( ... ) tablespace = ts_log;
CREATE TABLE log_2020_02 ( ... ) tablespace = ts_log;