MySQL大表优化的示例分析

39次阅读

没有评论

共计 3094 个字符，预计需要花费 8 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下 MySQL 大表优化的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

背景

阿里云 RDS FOR MySQL（MySQL5.7 版本）数据库业务表每月新增数据量超过千万, 随着数据量持续增加, 我们业务出现大表慢查询, 在业务高峰期主业务表的慢查询需要几十秒严重影响业务

方案概述

一、数据库设计及索引优化

MySQL 数据库本身高度灵活，造成性能不足，严重依赖开发人员的表设计能力以及索引优化能力, 在这里给几点优化建议

时间类型转化为时间戳格式, 用 int 类型储存, 建索引增加查询效率

建议字段定义 not null，null 值很难查询优化且占用额外的索引空间

使用 TINYINT 类型代替枚举 ENUM

存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE

字段长度严重根据业务需求来, 不要设置过大

尽量不要使用 TEXT 类型，如必须使用建议将不常用的大字段拆分到其它表

MySQL 对索引字段长度是有限制的, innodb 引擎的每个索引列长度默认限制为 767 字节（bytes），所有组成索引列的长度和不能大于 3072 字节(mysql8.0 单索引可以创建 1024 字符)

大表有 DDL 需求时请联系 DBA

最左索引匹配规则

顾名思义就是最左优先，在创建组合索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。复合索引很重要的问题是如何安排列的顺序，比如 where 后面用到 c1, c2 这两个字段，那么索引的顺序是 (c1,c2) 还是 (c2,c1) 呢，正确的做法是，重复值越少的越放前面，比如一个列 95% 的值都不重复，那么一般可以将这个列放最前面

复合索引 index(a,b,c)

where a=3 只使用了 a

where a=3 and b=5 使用了 a,b

where a=3 and b=5 and c=4 使用了 a,b,c

where b=3 or where c=4 没有使用索引

where a=3 and c=4 仅使用了 a

where a=3 and b 10 and c=7 使用了 a,b

where a=3 and b like‘xx%’and c=7 使用了 a,b

其实相当于创建了多个索引：key(a)、key(a,b)、key(a,b,c)

二、数据库切换到 PloarDB 读写分离

PolarDB 是阿里云自研的下一代关系型云数据库，100% 兼容 MySQL 存储容量最高可达 100 TB，单库最多可扩展到 16 个节点，适用于企业多样化的数据库应用场景。PolarDB 采用存储和计算分离的架构，所有计算节点共享一份数据，提供分钟级的配置升降级、秒级的故障恢复、全局数据一致性和免费的数据备份容灾服务。

集群架构，计算与存储分离
PolarDB 采用多节点集群的架构，集群中有一个 Writer 节点（主节点）和多个 Reader 节点（只读节点），各节点通过分布式文件系统（PolarFileSystem）共享底层的存储（PolarStore）

读写分离
当应用程序使用集群地址时，PolarDB 通过内部的代理层（Proxy）对外提供服务，应用程序的请求都先经过代理，然后才访问到数据库节点。代理层不仅可以做安全认证和保护，还可以解析 SQL，把写操作（例如事务、UPDATE、INSERT、DELETE、DDL 等）发送到主节点，把读操作（例如 SELECT）均衡地分发到多个只读节点，实现自动的读写分离。对于应用程序来说，就像使用一个单点的数据库一样简单。

在离线混合场景：不同业务用不同的连接地址，使用不同的数据节点，避免相互影响

Sysbench 性能压测报告：

PloarDB 4 核 16G 2 台

PloarDB 8 核 32G 2 台

三、分表历史数据迁移到 MySQL8.0 X-Engine 存储引擎

分表业务表保留 3 个月数据(这个根据公司需求来), 历史数据按月分表到历史库 X -Engine 存储引擎表, 为什么要选用 X -Engine 存储引擎表, 它有什么优点?

节约成本, X-Engine 的存储成本约为 InnoDB 的一半

X-Engine 分层存储提高 QPS, 采用层次化的存储结构，将热数据与冷数据分别存放在不同的层次中，并默认对冷数据所在层次进行压缩

X-Engine 是阿里云数据库产品事业部自研的联机事务处理 OLTP（On-Line Transaction Processing）数据库存储引擎。
X-Engine 存储引擎不仅可以无缝对接兼容 MySQL（得益于 MySQL Pluginable Storage Engine 特性），同时 X -Engine 使用分层存储架构。因为目标是面向大规模的海量数据存储，提供高并发事务处理能力和降低存储成本，在大部分大数据量场景下，数据被访问的机会是不均等的，访问频繁的热数据实际上占比很少，X-Engine 根据数据访问频度的不同将数据划分为多个层次，针对每个层次数据的访问特点，设计对应的存储结构，写入合适的存储设备

X-Engine 使用了 LSM-Tree 作为分层存储的架构基础，并进行了重新设计：

热数据层和数据更新使用内存存储，通过内存数据库技术（Lock-Free index structure/append only）提高事务处理的性能。

流水线事务处理机制，把事务处理的几个阶段并行起来，极大提升了吞吐。

访问频度低的数据逐渐淘汰或是合并到持久化的存储层次中，并结合多层次的存储设备（NVM/SSD/HDD）进行存储。

对性能影响比较大的 Compaction 过程做了大量优化：

拆分数据存储粒度，利用数据更新热点较为集中的特征，尽可能的在合并过程中复用数据。

精细化控制 LSM 的形状，减少 I / O 和计算代价，有效缓解了合并过程中的空间增大。

同时使用更细粒度的访问控制和缓存机制，优化读的性能。

四、阿里云 PloarDB MySQL8.0 版本并行查询

分表之后我们的数据量依然很大, 并没有完全解决我们的慢查询问题, 只是降低了我们业务表的体量, 这部分慢查询我们需要用到 PolarDB 的并行查询优化

PolarDB MySQL 8.0 重磅推出并行查询框架，当您的查询数据量到达一定阈值，就会自动启动并行查询框架，从而使查询耗时指数级下降
在存储层将数据分片到不同的线程上，多个线程并行计算，将结果流水线汇总到总线程，最后总线程做些简单归并返回给用户，提高查询效率。
并行查询（Parallel Query）利用多核 CPU 的并行处理能力，以 8 核 32 GB 配置为例，示意图如下所示。