Mysql은 대용량 데이터 테이블을 어떻게 처리하나요? 다음 글에서는 Mysql 빅데이터 테이블 처리 솔루션을 소개하겠습니다. 도움이 되셨으면 좋겠습니다.
비즈니스 데이터베이스 테이블에 점점 더 많은 데이터가 있을 때, 귀하와 제가 다음과 같은 유사한 시나리오에 직면했다면, 이 문제를 함께 해결해 봅시다
테이블 용량/디스크 공간/인스턴스 용량의 세 가지 측면에서 데이터 볼륨을 평가할 수 있습니다. 다음으로, 별도로 살펴보겠습니다.
테이블 용량은 주로 레코드 수와 테이블의 평균 길이에 따라 달라지며, 증가량, 읽기 및 쓰기량, 전체 크기가 평가됩니다. 일반적으로 OLTP 테이블의 경우 단일 테이블의 데이터 행 수는 2천만 행을 초과하지 않고 전체 크기는 15G 이내를 권장합니다. 방문량: 단일 테이블의 읽기 및 쓰기량은 1600/s 이내입니다.
행 데이터 쿼리 방법: 테이블에 얼마나 많은 데이터가 있는지 쿼리할 때 일반적으로 사용하는 클래식 SQL 문은 다음과 같습니다.
라이브러리 이름을 사용
'테이블 이름'과 같이 테이블 상태를 표시하거나 테이블 상태를 표시합니다. 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;
쿼리 결과는 다음과 같습니다.
데이터 볼륨이 디스크 사용량의 70% 미만을 차지하는 것이 좋습니다. 동시에, 빠르게 증가하는 일부 데이터의 경우 데이터 보관을 위해 대용량 느린 디스크 사용을 고려할 수 있습니다. (보관의 경우 계획 3을 참조하세요.)
MySQL은 스레드 기반 서비스 모델이므로 동시성이 높은 일부 시나리오에서는 단일 인스턴스가 서버의 CPU 리소스를 완전히 활용할 수 없으며 처리량이 mysql 계층에서 정체됩니다. 비즈니스에 따라 자체 인스턴스 모드를 고려할 수 있습니다
위에서 데이터 테이블의 크기를 이미 알아냈습니다. 그렇다면 단일 테이블에 데이터의 양이 많을수록 비즈니스 실행 효율성이 느려지는 근본적인 이유는 무엇일까요?
테이블의 데이터 양이 수천만 또는 수억에 도달하면 인덱스 추가 효과가 그다지 뚜렷하지 않습니다. 성능이 나빠지는 이유는 인덱스를 유지하는 B+ 트리 구조의 수준이 높아져 데이터 조각을 쿼리할 때 더 많은 디스크 IO를 경험해야 하므로 쿼리 성능이 느려지기 때문입니다. . B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。
大家是否还记得，一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。
B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；
假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。
因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400
B+ 트리가 얼마나 많은 데이터를 저장할 수 있는지 아직도 기억하시나요?
16k입니다. 
🎜🎜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万数据
定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据
缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作
知道了两个知识后，我们来看一下分库分表的方案
분할하기 전에 데이터 양을 추정하세요. 예를 들어, user 테이블에는 4천만 개의 데이터가 있으며 이제 데이터를 user1 user2 uesr3 user4 4개의 테이블로 나누어야 합니다. 예를 들어, id = 17, 17 모듈로 4는 1, 더하기 이므로 이 데이터는 user2 테이블에 저장됩니다.
참고: 수평 분할 후에는 테이블에서 Auto_increment를 제거해야 합니다. 이때 ID는 ID 자체 증가 임시 테이블을 이용하거나 redis incr 방식을 사용하여 얻을 수 있다.
장점: 데이터가 다양한 테이블로 균등하게 나누어져 있어 핫이슈가 발생할 확률이 매우 낮습니다.
단점: 향후 데이터 확장 및 마이그레이션이 어려울 수 있습니다. 데이터 양이 증가하면 이전에 4개 테이블로 나누어졌던 것이 이제는 8개 테이블로 나누어 모듈로 값 변경 및 데이터 마이그레이션을 수행해야 합니다. 다시.
데이터를 범위별로 분할합니다. 즉, 특정 범위 내의 주문이 특정 테이블에 저장됩니다. 예를 들어 id=12는 user1 테이블에 저장되고 id=1300만은 user2 테이블에 저장됩니다.
장점: 향후 데이터 확장에 유리함
단점: 한 테이블에 핫 데이터가 존재하면 한 테이블에만 압력이 가해지고, 다른 테이블에는 압력이 가해지지 않습니다.
위의 두 가지 솔루션은 단점이 있지만 보완적이라는 것을 알 수 있습니다. 그렇다면 이 두 가지 솔루션을 결합하면 어떻게 될까요?
아래 그림에서 볼 수 있듯이 그룹 그룹은 0부터 4천만까지의 데이터를 저장하고 있으며 데이터베이스에는 DB0 DB1 DB2가 있습니다. DB0에는 4개의 데이터베이스가 있고, DB1과 DB2에는 3개의 데이터베이스가 있습니다
id가 15000이고 모듈로 10을 취하고(테이블이 10개이므로 모듈로 10을 취하는 이유), 0을 취하고 DB_0에 들어간 다음 범위에 따라, Table_0에 속합니다.
요약: 해시 모듈러스와 범위 체계의 조합은 핫 데이터 문제를 피할 수 있을 뿐만 아니라 향후 데이터 확장을 촉진할 수 있습니다.
우리는 이미 mysql 파티션과 하위 테이블에 대해 배웠으므로 이 두 가지를 살펴보겠습니다. 이 기술과 적용 가능한 시나리오의 차이점은 무엇입니까?
데이터베이스 및 테이블 샤딩 문제
추가 데이터 관리 부담, 가장 눈에 띄는 것은 데이터 위치 지정 문제와 데이터 추가, 삭제, 수정 및 쿼리의 반복적인 실행입니다. 예를 들어 사용자 점수를 기록하는 사용자 데이터 테이블 userTable의 경우 비즈니스에서는 테이블을 분할하기 전에는 하나의 명령만 수행할 수 있지만 이후에는 최고 점수를 찾아야 합니다. 테이블을 분할하면 각 분할 테이블의 상위 100개 사용자 데이터를 찾은 다음 데이터를 결합하여 결과를 얻으려면 n order by 문이 필요합니다.
핫 및 콜드 보관 이유: 사실 그 이유는 두 번째 옵션과 비슷합니다. 단일 테이블의 데이터 양을 줄이기 위한 트리 높이가 낮아지고 쿼리로 인해 발생하는 디스크 IO가 줄어들어 효율성이 향상될 수 있습니다. 예를 들어, 비즈니스 데이터에 더운 것과 추운 것이 명확하게 구분되어 있는 경우 지난 주 또는 월의 데이터만 표시하면 됩니다. 이때 이번 주, 한 달 동안의 데이터를 핫(Hot) 데이터라고 하고, 나머지 데이터를 콜드(Cold) 데이터라고 합니다. 그런 다음 콜드 데이터를 다른 데이터베이스 테이블에 보관하여 핫 데이터의 운영 효율성을 향상시킬 수 있습니다.
보관 테이블 만들기 생성된 아카이브 테이블은 원본 테이블과 일치해야 합니다.
데이터의 양이 많아 명확한 핫 영역과 콜드 영역을 구분할 수 없으며, 데이터를 간격에 따라 완전히 구분할 수 있습니다.
| 이제 제가 이야기하고 싶은 내용은 거의 끝났습니다. 문제가 있거나 의심스러운 점이 있으면 언제든지 문의하세요! | 【관련 추천: | mysql 비디오 튜토리얼】 |
|---|
위 내용은 MySQL은 대용량 데이터 테이블을 어떻게 처리합니까? 솔루션 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
