介绍MySQL大表优化方案-百分百源码网

介绍MySQL大表优化方案

发布时间：01/01 来源：未知浏览：关键词：

免费学习引荐：mysql数据库(视频)

背景

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

方案概述

20201030141518

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

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）平衡地分发到多个只读节点，实现主动的读写别离。关于利用程序来说，就像使用一个单点的数据库一样简便。

在离线混合场景：不一样业务用不一样的连接地址，使用不一样的数据节点，幸免彼此影响

20201029160013

Sysbench机能压测报告：

PloarDB 4核16G 2台

20201029160525
20201029160550

PloarDB 8核32G 2台

20201029160755
20201029160845

三、分表历史数据迁移到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和运算代价，有效缓解了合并历程中的空间增大。
同时使用更细粒度的拜访操纵和缓存机制，优化读的机能。

20201029162440

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

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

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

20201029163124