Mysql은 대용량 데이터 테이블을 어떻게 처리하나요? 다음 글에서는 Mysql 빅데이터 테이블 처리 솔루션을 소개하겠습니다. 도움이 되셨으면 좋겠습니다.

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

시나리오:

비즈니스 데이터베이스 테이블에 점점 더 많은 데이터가 있을 때, 귀하와 제가 다음과 같은 유사한 시나리오에 직면했다면, 이 문제를 함께 해결해 봅시다

데이터 삽입, 쿼리 시간이 깁니다
이후 비즈니스 요구 사항의 확장은 테이블의 새 필드에 더 큰 영향을 미칩니다
테이블의 모든 데이터가 유효한 데이터는 아닙니다. 시간 간격 내의 데이터만 쿼리하면 됩니다

테이블의 데이터 볼륨을 평가합니다.

테이블 용량/디스크 공간/인스턴스 용량의 세 가지 측면에서 데이터 볼륨을 평가할 수 있습니다. 다음으로, 별도로 살펴보겠습니다.

테이블 용량:

테이블 용량은 주로 레코드 수와 테이블의 평균 길이에 따라 달라지며, 증가량, 읽기 및 쓰기량, 전체 크기가 평가됩니다. 일반적으로 OLTP 테이블의 경우 단일 테이블의 데이터 행 수는 2천만 행을 초과하지 않고 전체 크기는 15G 이내를 권장합니다. 방문량: 단일 테이블의 읽기 및 쓰기량은 1600/s 이내입니다.

행 데이터 쿼리 방법: 테이블에 얼마나 많은 데이터가 있는지 쿼리할 때 일반적으로 사용하는 클래식 SQL 문은 다음과 같습니다.

select count(*) from table
select count(1) from table 하지만 데이터의 양이 너무 많으면 쿼리 시간이 초과될 수 있으므로 쿼리 방법을 변경해야 합니다

라이브러리 이름을 사용
'테이블 이름'과 같이 테이블 상태를 표시하거나 테이블 상태를 표시합니다. like 'table name'G;

위 방법은 테이블의 데이터를 쿼리할 수 있을 뿐만 아니라 테이블의 세부 정보도 출력할 수 있습니다. G를 추가하여 출력 형식을 지정합니다. 테이블 이름, 스토리지 엔진 버전, 행 수, 행당 바이트 수 등을 포함합니다. 직접 시도해 볼 수 있습니다.

디스크 공간

지정된 데이터베이스 용량 보기

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;

로그인 후 복사

쿼리 결과는 다음과 같습니다.

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

데이터 볼륨이 디스크 사용량의 70% 미만을 차지하는 것이 좋습니다. 동시에, 빠르게 증가하는 일부 데이터의 경우 데이터 보관을 위해 대용량 느린 디스크 사용을 고려할 수 있습니다. (보관의 경우 계획 3을 참조하세요.)

인스턴스 용량

MySQL은 스레드 기반 서비스 모델이므로 동시성이 높은 일부 시나리오에서는 단일 인스턴스가 서버의 CPU 리소스를 완전히 활용할 수 없으며 처리량이 mysql 계층에서 정체됩니다. 비즈니스에 따라 자체 인스턴스 모드를 고려할 수 있습니다

문제 원인

위에서 데이터 테이블의 크기를 이미 알아냈습니다. 그렇다면 단일 테이블에 데이터의 양이 많을수록 비즈니스 실행 효율성이 느려지는 근본적인 이유는 무엇일까요?

테이블의 데이터 양이 수천만 또는 수억에 도달하면 인덱스 추가 효과가 그다지 뚜렷하지 않습니다. 성능이 나빠지는 이유는 인덱스를 유지하는 B+ 트리 구조의 수준이 높아져 데이터 조각을 쿼리할 때 더 많은 디스크 IO를 경험해야 하므로 쿼리 성능이 느려지기 때문입니다. . B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

大家是否还记得，一个B+树大概可以存放多少数据量呢？

InnoDB存储引擎最小储存单元是页，一页大小就是16k。

B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400

B+ 트리가 얼마나 많은 데이터를 저장할 수 있는지 아직도 기억하시나요?

InnoDB 스토리지 엔진의 가장 작은 저장 단위는 페이지이고, 페이지 크기는 `16k`입니다.

B+ 트리는 데이터를 저장하고 내부 노드는 키 값 + 포인터를 저장합니다. 인덱스 구성 테이블은 리프가 아닌 노드와 포인터의 바이너리 검색 방식을 통해 데이터가 어느 페이지에 있는지 확인한 후 해당 데이터 페이지로 이동하여 필요한 데이터를 찾습니다.

🎜🎜B+ 트리의 높이가 2라고 가정합니다. 즉, 하나의 루트 노드와 여러 개의 리프 노드가 있습니다. 이 B+ 트리에 저장된 총 레코드 수는 = 루트 노드 포인터 수 * 단일 리프 노드에 기록된 행 수입니다. 🎜🎜🎜레코드 행의 데이터 크기가 1k라면 단일 리프 노드가 저장할 수 있는 레코드 수는 =16k/1k =16입니다.🎜🎜리프가 아닌 노드에는 몇 개의 포인터가 저장됩니까? 기본 키 ID는 🎜bigint 유형, 길이 8바이트🎜(🎜면접관이 int 유형에 대해 질문했는데 int는 32비트, 4바이트🎜)이고 포인터 크기는 6으로 설정되어 있다고 가정합니다. InnoDB 소스 코드의 바이트이므로 8+6=14바이트, 16k/14B =16*1024B/14B = 1170🎜🎜🎜따라서 높이가 2인 B+ 트리는 1170 * 16=을 저장할 수 있습니다. 18720 이 데이터 기록과 같은 항목이 있습니다. 마찬가지로 높이가 3인 B+ 트리는 1170 *1170 *16 =21902400을 저장할 수 있으며 이는 약 2천만 개의 레코드를 저장할 수 있음을 의미합니다. B+ 트리 높이는 일반적으로 1~3개 레이어로 수천만 수준의 데이터 저장 요구 사항을 충족할 수 있습니다. 🎜🎜B+ 트리가 더 많은 데이터를 저장하려면 트리 구조 수준이 높아집니다. 데이터 조각을 쿼리할 때 더 많은 디스크 IO를 경험해야 하므로 쿼리 성능이 저하됩니다. 🎜🎜🎜단일 테이블에 데이터가 너무 많고 쿼리 속도가 느린 문제를 해결하는 방법🎜🎜🎜근본 원인을 파악한 후 문제 해결을 위해 데이터베이스를 최적화하는 방법을 고려해야 합니다🎜

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

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围并且索引分区也可以进一步提高命中率，提升查询效率

分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

我们首先看一下分区有什么优缺点：

表分区有什么好处？

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

表分区的限制因素

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

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

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

로그인 후 복사

方案二：数据库分表

为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率

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

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

水平分表

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

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

垂直分表

定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作

知道了两个知识后，我们来看一下分库分表的方案

1. 모듈러스 계획:

분할하기 전에 데이터 양을 추정하세요. 예를 들어, user 테이블에는 4천만 개의 데이터가 있으며 이제 데이터를 user1 user2 uesr3 user4 4개의 테이블로 나누어야 합니다. 예를 들어, id = 17, 17 모듈로 4는 1, 더하기 이므로 이 데이터는 user2 테이블에 저장됩니다.

참고: 수평 분할 후에는 테이블에서 Auto_increment를 제거해야 합니다. 이때 ID는 ID 자체 증가 임시 테이블을 이용하거나 redis incr 방식을 사용하여 얻을 수 있다.

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

장점: 데이터가 다양한 테이블로 균등하게 나누어져 있어 핫이슈가 발생할 확률이 매우 낮습니다.

단점: 향후 데이터 확장 및 마이그레이션이 어려울 수 있습니다. 데이터 양이 증가하면 이전에 4개 테이블로 나누어졌던 것이 이제는 8개 테이블로 나누어 모듈로 값 변경 및 데이터 마이그레이션을 수행해야 합니다. 다시.

2.범위 범위 체계

데이터를 범위별로 분할합니다. 즉, 특정 범위 내의 주문이 특정 테이블에 저장됩니다. 예를 들어 id=12는 user1 테이블에 저장되고 id=1300만은 user2 테이블에 저장됩니다.

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

장점: 향후 데이터 확장에 유리함

단점: 한 테이블에 핫 데이터가 존재하면 한 테이블에만 압력이 가해지고, 다른 테이블에는 압력이 가해지지 않습니다.

위의 두 가지 솔루션은 단점이 있지만 보완적이라는 것을 알 수 있습니다. 그렇다면 이 두 가지 솔루션을 결합하면 어떻게 될까요?

3. 해시 모듈러스와 범위 체계의 조합

아래 그림에서 볼 수 있듯이 그룹 그룹은 0부터 4천만까지의 데이터를 저장하고 있으며 데이터베이스에는 DB0 DB1 DB2가 있습니다. DB0에는 4개의 데이터베이스가 있고, DB1과 DB2에는 3개의 데이터베이스가 있습니다

id가 15000이고 모듈로 10을 취하고(테이블이 10개이므로 모듈로 10을 취하는 이유), 0을 취하고 DB_0에 들어간 다음 범위에 따라, Table_0에 속합니다.

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

요약: 해시 모듈러스와 범위 체계의 조합은 핫 데이터 문제를 피할 수 있을 뿐만 아니라 향후 데이터 확장을 촉진할 수 있습니다.

우리는 이미 mysql 파티션과 하위 테이블에 대해 배웠으므로 이 두 가지를 살펴보겠습니다. 이 기술과 적용 가능한 시나리오의 차이점은 무엇입니까?

테이블 분할과 분할의 차이점:

1 구현 방법 측면에서

mysql의 샤딩은 실제 샤딩입니다. 테이블이 여러 테이블로 분할된 후, 각각의 작은 테이블은 .MYD 데이터 파일, .MYI 인덱스 파일 및 .frm 테이블 구조에 해당하는 완전한 테이블입니다. 큰 테이블이 분할되어 있어도 여전히 하나의 테이블입니다. 두 개의 테이블이 되지는 않지만 데이터를 저장할 블록이 더 많아집니다.

2. 성능 향상

하위 테이블의 초점은 데이터에 액세스할 때 mysql 동시성을 향상시키는 방법입니다.

파티션의 경우 디스크의 읽기 및 쓰기 기능을 돌파하여 mysql 성능 향상의 목적.

3. 구현의 어려움

1. 테이블을 나누는 방법은 여러 가지가 있습니다. 테이블을 나누는 방법은 가장 간단합니다. 이 방법은 루트 분할과 거의 동일한 난이도이며 프로그램 코드에 투명할 수 있습니다. 다른 테이블 파티셔닝 방법을 사용하면 파티셔닝보다 더 번거롭습니다. 2. 파티셔닝 구현은 비교적 간단합니다. 파티션 테이블을 생성하는 것과 일반 테이블을 구축하는 것 사이에는 차이가 없으며, 파티셔닝과 파티셔닝 테이블의 관계가 투명합니다.

1. mysql.High의 성능은 높은 동시성 상태에서 좋은 성능을 발휘합니다.

2. 하위 테이블과 파티션은 모순되지 않으며 서로 협력할 수 있습니다. 액세스 볼륨이 크고 테이블 데이터가 상대적으로 큰 테이블의 경우 하위 테이블과 파티션을 결합할 수 있지만 액세스 볼륨은 크지 않습니다. 테이블 데이터가 큽니다. 테이블의 경우 분할할 수 있습니다.

데이터베이스 및 테이블 샤딩 문제

1. 트랜잭션 문제

데이터베이스 및 테이블 샤딩을 실행한 후에는 데이터가 서로 다른 데이터베이스에 저장되므로 데이터베이스 트랜잭션 관리가 어려워집니다. 트랜잭션을 실행하기 위해 데이터베이스 자체의 분산 트랜잭션 관리 기능에 의존한다면 높은 성능 비용을 지불하게 되며, 애플리케이션이 제어를 지원하고 프로그램 논리 트랜잭션을 구성하면 프로그래밍 부담도 발생합니다.

2. 교차 데이터베이스 및 교차 테이블 조인 문제

데이터베이스 및 테이블 샤딩을 수행한 후에는 원래 논리적으로 관련성이 높은 데이터가 다른 테이블과 다른 라이브러리로 분할되는 것은 불가피합니다. 서로 다른 하위 데이터베이스에 있는 테이블을 조인할 수 없으며, 서로 다른 하위 테이블 단위로 테이블을 조인할 수도 없습니다. 따라서 하나의 쿼리로 완료할 수 있는 비즈니스를 완료하려면 여러 쿼리가 필요할 수 있습니다.

3. 추가 데이터 관리 부담 및 데이터 계산 부담

추가 데이터 관리 부담, 가장 눈에 띄는 것은 데이터 위치 지정 문제와 데이터 추가, 삭제, 수정 및 쿼리의 반복적인 실행입니다. 예를 들어 사용자 점수를 기록하는 사용자 데이터 테이블 userTable의 경우 비즈니스에서는 테이블을 분할하기 전에는 하나의 명령만 수행할 수 있지만 이후에는 최고 점수를 찾아야 합니다. 테이블을 분할하면 각 분할 테이블의 상위 100개 사용자 데이터를 찾은 다음 데이터를 결합하여 결과를 얻으려면 n order by 문이 필요합니다.

옵션 3: 핫 및 콜드 보관

핫 및 콜드 보관 이유: 사실 그 이유는 두 번째 옵션과 비슷합니다. 단일 테이블의 데이터 양을 줄이기 위한 트리 높이가 낮아지고 쿼리로 인해 발생하는 디스크 IO가 줄어들어 효율성이 향상될 수 있습니다. 예를 들어, 비즈니스 데이터에 더운 것과 추운 것이 명확하게 구분되어 있는 경우 지난 주 또는 월의 데이터만 표시하면 됩니다. 이때 이번 주, 한 달 동안의 데이터를 핫(Hot) 데이터라고 하고, 나머지 데이터를 콜드(Cold) 데이터라고 합니다. 그런 다음 콜드 데이터를 다른 데이터베이스 테이블에 보관하여 핫 데이터의 운영 효율성을 향상시킬 수 있습니다.

보관 프로세스에 대해 이야기해 보겠습니다.

보관 테이블 만들기 생성된 아카이브 테이블은 원본 테이블과 일치해야 합니다.

위의 세 가지 옵션을 어떻게 선택합니까? 2. 쿼리 시나리오는 다음과 같습니다. 특정 영역에서만 3. 공동 쿼리 시나리오가 없습니다

MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유 파티셔닝 및 테이블 파티셔닝은 데이터 테이블에 해당하는 파일을 물리적으로 분할하는 것입니다. 해당 테이블 이름은 변경되지 않으므로 이전 비즈니스 로직 sql

데이터의 양이 많아 명확한 핫 영역과 콜드 영역을 구분할 수 없으며, 데이터를 간격에 따라 완전히 구분할 수 있습니다. MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유

핫 파티션과 콜드 파티션의 경계가 있는 데이터에 적합합니다. 이 방법은 후속 유사한 데이터 방법에 사용할 수 있으며, 큰 테이블을 작은 테이블로 분할하여 쿼리, 삽입 등의 효율성을 향상시킵니다. 데이터베이스 테이블은 테이블로 나누어야 하며, 후속 단일 테이블의 경우에는 구현 복잡성이 세 번째 솔루션보다 더 복잡합니다. 전체 구현 프로세스가 원래 비즈니스의 인코딩 계층 처리에 미치는 영향을 테스트합니다.