큰 MySQL 제품 내역 테이블 파티셔닝?

큰 MySQL 제품 내역 테이블 파티셔닝?

저는 php, laravel framework, mariadb로 작성된 창고 관리 시스템을 개발하고 있습니다.각 제품에 대한 모든 정보를 얻으려면 특정 제품에 대해 수행된 모든 작업을 기록하는 제품 "이력" 표를 사용합니다.이 테이블은 매우 빠르게 확장되기 시작했으며, DB 테이블에는 1,500만 개의 행이 있습니다. 이 테이블은 특히 기능을 실행할 때 느리게 작동하기 시작했습니다. 이 테이블은 제품 판매, 생성, 폐기 등의 모든 제품을 분석하는 데 필요합니다. 한 쿼리에 1,500만 개의 행이 필요합니다.그래서 저는 이 큰 테이블을 어떻게 관리할 것인지를 찾기 시작했습니다. 왜냐하면 인덱싱이 더 이상 작동하지 않기 때문입니다.날짜별로 이 테이블을 분할/분할하는 것에 대해 생각하기 시작했습니다. 혹시 조치가 필요할까요?그래서 아마 누구든 그것에 대한 경험이 있고 나에게 조언을 나눌 수 있을까요? 도움을 주셔서 감사합니다!

CREATE TABLE `history` ( `id` int(11) NOT NULL AUTO_INCREMENT, `barcode` varchar(100) DEFAULT NULL, `bag` varchar(100) DEFAULT NULL, `action` int(10) unsigned DEFAULT NULL, `place` int(10) unsigned DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `old_price` decimal(10,2) DEFAULT NULL, `user` int(11) DEFAULT NULL, `amount` int(10) DEFAULT NULL, `rotation` int(10) unsigned DEFAULT NULL, `discount` decimal(10,2) DEFAULT NULL, `discount_type` tinyint(2) unsigned DEFAULT NULL, `original` int(10) unsigned DEFAULT NULL, `was_in_shop` int(10) unsigned DEFAULT NULL, `cate` int(10) unsigned DEFAULT NULL COMMENT 'grupe', `sub_cate` int(10) unsigned DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `helper` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `barcode` (`barcode`), KEY `action` (`action`), KEY `original` (`original`), KEY `created_at` (`created_at`), KEY `bag` (`bag`) ) ENGINE=InnoDB AUTO_INCREMENT=16274267 DEFAULT CHARSET=utf8

쿼리 예제:

select  cate,
SUM(amount) AS amount, SUM(IF(discount>0,(price*amount)-discount,
                    (price*amount))) AS sum, SUM(IF(discount>0,IF(discount_type=1,
                                            (discount*price)/100,discount),0)
   ) AS discount from  history
    where  (history.action = '4'
              and  history.created_at >= '2017-11-01 00:00:00'
              and  history.created_at <= '2017-11-23 23:59:59'
           )
      and  LENGTH(barcode) > 7
      and  history.deleted_at is null
    group by  cate

이 쿼리는 판매된 제품에 대한 금액, 합계, 할인 정보(액션 4)를 가져오는 데 사용됩니다. 이 예는 2017-11-01과 2017-11-23 사이의 정보입니다.EXPLAIN다음과 같은 정보를 제공합니다.

id - 1 select_type - SIMPLE table - history type - ref possible_keys - action,created_at key - action key_len - 5 ref - const rows - 1444272 Extra - Using where; Using temporary; Using filesort

2017-01-01부터 현재까지 데이터를 저장하는 테이블을 사용하면 150만 행이 필요하므로 2년 후에는 300만 행 등이 필요합니다.2017-11년 제품 판매 정보만 가져가야 할 때.그리고 저는 이것과 비슷한 질문들이 많이 있습니다.

• 더 작은 데이터 유형 사용(테이블 크기 축소가 성능 향상에 도움이 됨)INT4바이트가 소요되며, 다른 크기도 사용할 수 있습니다.
• PARTITIONing본질적으로 어떠한 성능도 제공하지 않습니다.
• history.deleted_at is null행을 실제로 삭제하는 것을 고려합니다.
• 다음과 같은 '복합' 인덱스에 대해 알아보기INDEX(action, created_at)한 번에 하나의 인덱스만 사용됩니다.

요약 표를 구축하고 유지 관리하면 크게 개선됩니다. http://mysql.rjweb.org/doc.php/summarytables 을 참조하십시오.그런 다음 쿼리를 실행합니다.그리고 대부분의 인덱스는 사라질 수 있습니다.

그것들 중 일부를 고치면 제가 더 도와드릴 수 있습니다.

더

설명은 요약 테이블 ID를 두 가지 방법으로 유지하는 방법에 대해 묻습니다.아직 명시되지 않은 세부 정보에 따라 두 가지 모두 실행 가능할 수 있습니다.

• INSERT INTO FactIODKU를 사용하여 요약 표를 삽입하거나 업데이트합니다.
• demand합니다. --를 요청할 때, "청요시요수" - 가데를요이다때실니먼합행청터할저행약자사용▁the▁do▁run다▁first,"를 실행합니다INSERT .. SELECT ..아직 요약되지 않은 행을 캡처하고 카운트/부분 집합을 요약 표에 넣습니다.

후자의 선택은 효과적이지만 주의해야 할 두 가지가 있습니다.

• 오랜 시간 동안 사용자가 나타나지 않으면 요약 비용이 많이 들 수 있습니다.간단한 해결책은 cron 작업을 주기적으로 "따라잡기"하는 것입니다.cron과 사용자가 동시에 동일한 행을 업데이트하지 않도록 코드를 연동해야 합니다.
• 인 PRIMARY KEY예를 들어 날짜(일 또는 시간)와 두 개의 차원 값과 같은 경우, 당신은 마치 문제가 있는 것 같습니다.INSERT이를 PK로 사용하지 않도록 하거나(따라서 여러 행이 발생하므로 '나쁜' 것이 아님) 형식으로 IODKU를 사용합니다.INSERT ... ON DUPLICATE KEY ... SELECT ... GROUP BY ...;다음을 사용합니다.VALUES(xx)기능.

ReferenceURL : https://stackoverflow.com/questions/49631578/big-mysql-product-history-table-partitioning