MySQL中大量数据优化方案

1 大量数据优化

1.1 引言

当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题：

• 数据的插入,查询时长较长
• 后续业务需求的扩展 在表中新增字段 影响较大
• 表中的数据并不是所有的都为有效数据 需求只查询时间区间内的

1.2 评估表数据体量

我们可以从表容量/磁盘空间/实例容量三方面评估数据体量，接下来让我们分别展开来看看

1.2.1 表容量

表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表，建议单表不要超过2000W行数据量，总大小15G以内。访问量：单表读写量在1600/s以内

查询行数据的方式：我们一般查询表数据有多少数据时用到的经典sql语句如下：

• select count(*) from table
• select count(1) from table

但是当数据量过大的时候，这样的查询就可能会超时，所以我们要换一种查询方式

• use 库名
• show table status like '表名' ; 或 show table status like '表名'\G ;

上述方法不仅可以查询表的数据，还可以输出表的详细信息 , 加 \G 可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等

1.2.2 磁盘空间

查看指定数据库容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查询单个库中所有表磁盘占用大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

查询出的结果如下：

建议数据量占磁盘使用率的70%以内。同时，对于一些数据增长较快，可以考虑使用大的慢盘进行数据归档

1.2.3 实例容量

MySQL是基于线程的服务模型，因此在一些并发较高的场景下，单实例并不能充分利用服务器的CPU资源，吞吐量反而会卡在mysql层，可以根据业务考虑自己的实例模式

1.3 出现问题的原因

上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢？

点击了解 MySQL 一棵B+树可以存多少数据量

1.4 解决问题

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

1.4.1 数据表分区

1.4.1.1 简介

表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区也可以进一步提高命中率，提升查询效率
分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

表分区有什么好处？

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。
• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；。通过并行，这意味着该查询可以在每个分区上同时进行，最终结果只需通过统计所有分区得到的结果。
• 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

1.4.1.2 分区限制和执行计划

表分区的限制因素：

• 一个表最多只能有1024个分区。
• MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
• 如果分区字段中有主键或者唯一索引的列，那么含有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
• 分区表中无法使用外键约束。
• MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

MySQL 如何选择执行计划，根据多个因素来选择是否使用索引，包括：

• 分区键是否与查询条件匹配：如果查询中涉及的列是分区键（如 order_date），MySQL 可以智能地跳过某些分区，从而提高查询效率。
• 索引列与查询条件的匹配：如果查询涉及的列有索引，MySQL 会优先使用该索引来优化查询。
• 分区裁剪（Partition Pruning）：如果查询中的条件涉及分区键，MySQL 会在查询执行前排除掉不相关的分区，这样可以减少扫描的分区数量。

1.4.1.3 分区表的索引

在 MySQL 中，当创建分区表时，索引的创建有两个方面需要注意：

• 主键索引：如果表有主键，MySQL 会自动将主键分配到每个分区。分区键必须是主键的一部分。否则，MySQL 会报错，要求修改表，使分区键成为主键的一部分。
• 二级索引（普通索引）： 创建普通索引时，需要指定LOCAL，如果没有指定 LOCAL 选项，MySQL 会创建一个全局索引，这意味着索引在所有分区之间共享。而如果使用 LOCAL 选项，它会在每个分区上创建一个局部索引。也就是说，局部索引仅在当前分区内有效。
  在每个分区内创建局部索引，适用于希望索引仅在当前分区内有效的场景。

全局索引与本地索引 (Local Index) 的区别：

• 全局索引（Global Index）：全局索引是跨分区的，即它将所有分区的数据当作一个整体进行索引。这意味着，无论数据在哪个分区，索引都会在整个表上保持一致。MySQL 默认情况下为分区表创建的是全局索引。
• 本地索引（Local Index）：本地索引是分区级别的，即每个分区有自己独立的索引。这意味着，索引只会在特定的分区内进行查找，不会跨分区。如果想要创建本地索引，可以通过 LOCAL 关键字显式地声明
  通常情况下，如果希望索引更高效地工作（尤其是在有大量数据的分区表中），使用 LOCAL 索引

CREATE TABLE orders (id INT PRIMARY KEY, order_date DATE,amount DECIMAL(10, 2),//INDEX idx_order_date (order_date)// 全局索引INDEX idx_order_date (order_date) LOCAL// 分区索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);

另外注意：索引不需要和分区列一样按分区进行索引，MySQL 会根据查询条件智能选择最优的执行计划。

1.4.1.4 为什么分区键必须是主键的一部分

在 MySQL 中，分区表会将数据分散到多个物理分区中，而每个分区必须是一个完整的表。这意味着，在进行分区时，MySQL 必须知道如何将数据分配到不同的分区上，因此它需要依据某个键（即分区键）来判断数据属于哪个分区。

主键与分区的关系：

• 主键的约束：主键要求每条记录必须是唯一的，并且必须有一个明确的唯一标识符。因此，主键的所有列在物理存储上必须能够唯一标识一行数据。
• 分区键的作用：分区键决定了数据行在哪个分区中。因此，分区键的值需要和主键一同存在，以便 MySQL 可以唯一地标识每条记录，并且可以将数据正确地分配到相应的分区中。

CREATE TABLE orders (id INT PRIMARY KEY, order_date DATE,amount DECIMAL(10, 2),INDEX idx_order_date (order_date)// 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);

在这个例子中，id 是主键，YEAR(order_date) 是分区键。
如果 YEAR(order_date) 不是主键的一部分：
在 MySQL 中，如果你的表使用了分区，分区键必须是主键的一部分。因此，你不能仅通过 id 作为主键，且不包含 YEAR(order_date) 作为主键的一部分。如果你这样做，会导致 MySQL 报错，类似于：

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

这是因为 MySQL 必须使用分区键来确定数据行的位置。如果分区键没有包含在主键中，MySQL 就不能正确地对每行数据进行唯一标识和分配，因此无法进行分区。

解决方法：
可以将 order_date 的 YEAR(order_date) 或者 order_date 本身添加到主键中，使得分区键是主键的一部分。例如：

CREATE TABLE orders (id INT,order_date DATE,amount DECIMAL(10, 2),PRIMARY KEY (id, YEAR(order_date)),  -- 将分区键加入主键INDEX idx_order_date (order_date)// 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);

1.4.1.5 操作分区

在进行分区之前可以用如下方法 看下数据库表是否支持分区

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

1.4.1.5.1 建表分区

CREATE TABLE test_table (id INT,name VARCHAR(50),created_at DATETIME
)
PARTITION BY RANGE (YEAR(created_at))
(PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022),...PARTITION pn VALUES LESS THAN MAXVALUE
);

1.4.1.5.2 建索引分区

创建分区索引的语法与创建普通索引类似。我们可以在创建表时同时创建分区索引，也可以在创建表后单独创建分区索引
在建表时建立索引分区

-- 在创建表时创建分区索引
CREATE TABLE orders (id INT,order_date DATE,amount DECIMAL(10, 2),INDEX idx_order_date (order_date) // 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2018 VALUES LESS THAN (2019),PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);
-- 创建表后单独创建分区索引
ALTER TABLE orders ADD INDEX idx_order_date (order_date) LOCAL;//局部索引

在建表后建立索引分区

-- 建立索引分区
ALTER TABLE your_table
PARTITION BY RANGE (YEAR(created_at))
(PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022),...PARTITION pn VALUES LESS THAN MAXVALUE
)
PARTITION BY KEY(id) PARTITIONS 4;

查询时可以添加分区，也可以不添加分区

SELECT * FROM t_order PARTITION (p0);
或者
SELECT * FROM t_order

1.4.1.5.3 删除分区

在 MySQL 中，删除分区时，数据会被删除，这是一个非常重要的注意事项。具体来说，删除分区不仅仅是删除分区的定义，还会导致该分区中所有数据的删除。

假设你有一个按 YEAR(order_date) 分区的表 orders，并且你有以下分区：
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)执行下面的 SQL 语句来删除 2020 年的分区：
ALTER TABLE orders DROP PARTITION p2020;

1.4.1.5.4 备份分区

• 将数据转移到其他分区： 在删除分区前，可以通过 INSERT INTO ... SELECT 将数据从删除的分区转移到其他分区。
• 备份数据： 在删除分区之前，可以通过 SELECT INTO OUTFILE 或其他备份方法将数据导出并备份，删除分区后再将数据导入回去。
• 使用 REORGANIZE PARTITION： 可以使用 REORGANIZE PARTITION 语句将数据从一个分区移动到另一个分区，而不是删除数据。

ALTER TABLE orders REORGANIZE PARTITION p2020 INTO (PARTITION p2020_new VALUES LESS THAN (2021)
);

1.4.2 数据库分表

1.4.2.1 简介

分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率
分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

mysql 分表分为两种：水平分表和垂直分表

• 水平分表
  定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据

• 垂直分表
  定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据
  缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加 join 或 union 之类的操作
  
  知道了两个知识后，我们来看一下分库分表的方案

1.4.2.2 分库分表方案

1.4.2.2.1 取模方案

拆分之前，先预估一下数据量。比如用户表有4000w数据，现在要把这些数据分到4个表user1 user2 uesr3 user4。比如id = 17，17对4取模为1，加上 ，所以这条数据存到user2表。
注意：进行水平拆分后的表要去掉auto_increment自增长。这时候的id可以用一个id 自增长临时表获得，或者使用 redis incr的方法。

优点：数据均匀的分到各个表中，出现热点问题的概率很低。
缺点：以后的数据扩容迁移比较困难难，当数据量变大之后，以前分到4个表现在要分到8个表，取模的值就变了，需要重新进行数据迁移。
点击了解 数据库之Sharding分库分表操作详解

1.4.2.2.2 range 范围方案

以范围进行拆分数据，就是在某个范围内的订单，存放到某个表中。比如id=12存放到user1表，id=1300万的存放到user2 表。

优点：有利于将来对数据的扩容
缺点：如果热点数据都存在一个表中，则压力都在一个表中，其他表没有压力。
我们看到以上两种方案 都存在缺点 但是却又是互补的，那么我们将这两个方案结合会怎样呢

1.4.2.2.3 hash取模和range方案结合

如下图 我们可以看到 group 组存放id 为 0~4000万 的数据，然后有三个数据库 DB0 DB1 DB2，DB0里面有四个数据库，DB1 和DB2 有三个数据库

假如id为15000 然后对10取模（为啥对10 取模 因为有10个表），取0 然后 落在DB_0,然后在根据range 范围，落在Table_0 里面。

总结：采用 hash 取模和 range 方案结合 既可以避免热点数据的问题，也有利于将来对数据的扩容

1.4.2.3 分区分表区别

分区分表区别：

• 实现方式上
  • mysql 的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构
  • 分区不一样，一张大表进行分区后，它还是一张表，不会变成两张表，但是它存放数据的区块变多了。
• 提高性能上
  • 分表重点是存取数据时，如何提高mysql并发能力上；
  • 分区是如何突破磁盘的读写能力，从而达到提高mysql性能的目的。
• 实现的难易度上
  • 分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
  • 分区实现是比较简单的，建立分区表，和建平常的表没什么区别，并且对开代码端来说是透明的

1.4.2.4 分区分表的联系

• 都能提高mysql的性高，在高并发状态下都有一个良好的表现。
• 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

1.4.2.5 分库分表存在的问题

• 事务问题
  在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
• 跨库跨表的join问题
  在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
• 额外的数据管理负担和数据运算压力
  额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算，例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

1.4.3 冷热归档

1.4.3.1 简介

为什么要冷热归档：其实原因和方案二类似，都是降低单表数据量，树的高度变低，查询经历的磁盘io变少，则可以提高效率 如果大家的业务数据，有明显的冷热区分，比如：只需要展示近一周或一个月的数据。那么这种情况这一周和一个月的数据我们称之为热数据，其余数据为冷数据。那么我们可以将冷数据归档在其他的库表中，提高我们热数据的操作效率。

1.4.3.2 操作

创建归档表 创建的归档表 原则上要与原表保持一致
归档表数据的初始化

业务增量数据处理过程

数据的获取过程

1.5 实际选择

方案	试用场景	优点	缺点
数据表分区	1.数据量较大 2.查询场景只在某个区 3.没有联合查询的场景	分区分表是在物理上对数据表所对应的文件进行拆分，对应的表名是不变的，所以不会影响到之前业务逻辑的sql	分表后的查询等业务会创建对应的对象，也会造成一定的开销分区数据若要聚合的话 耗费时间也较长;使用范围不适合数据量千万级以上的
数据表分表	数据量较大,无法区分明显冷热区 且数据可以完整按照区间划分	适用于对冷热分区的界限不是很明显的数据，对后续类似的数据可以采用该方式，将大表拆分成小表 提高查询插入等效率	若大数据表逐渐增多 那么对应的数据库表越来越多 每个表都需要分表; 区间的划分较为固定若后续单表的数据量大起来 也会对性能造成影响;实现复杂度相对方案三比较复杂 需要测试整人实现过程 在编码层处理 对原有业务有影影响
冷热归档分库	数据量较大;数据冷热分区明显;冷数据使用频率极低;	数据迁移的过程对业务的影影响较小 开发量也较少减少成本	需要确认分表规则