Node.js + MySQL 예제 : GigaBytes of Data를 100으로 처리

이 Node.js 및 MySQL 예제 프로젝트를 통해 수백 기가 바이트 의 저장 공간 을 차지하는 수십억 개의 행 을 효율적으로 처리하는 방법을 살펴 보겠습니다 .

이 글의 저의 두 번째 목표는 Node.js + MySQL이 여러분의 필요에 잘 맞는 지 판단하고 그러한 솔루션을 구현하는 데 도움을주기위한 것입니다..

왜 Node.js와 MySQL을 사용해야합니까?

우리는 MySQL을 사용하여 Node.js Monitoring & Debugging Tool의 Trace라는 사용자의 분산 된 추적 데이터를 저장합니다 .

우리는 결정할 때 Postgres가 행을 업데이트하는 것이 좋지 않았기 때문에 MySQL을 선택했습니다. 반면에 우리는 불변의 데이터를 업데이트하는 것이 부당하게 복잡했을 것입니다.

대부분의 사람들이 수백만 / 수십억 개의 행을 가지고 있다면 Cassandra 나 Mongo와 같은 NoSQL 솔루션을 사용해야한다고 생각합니다.

안타깝게도 이러한 솔루션은 ACID와 호환 되지 않으므로 데이터 일관성이 매우 중요 할 때 사용하기가 어렵습니다.

그러나 좋은 인덱싱과 적절한 계획을 통해 MySQL은 앞서 언급 한 NoSQL 대안과 마찬가지로 작업에 적합 할 수 있습니다.

MySQL에는 여러 스토리지 엔진이 있습니다. InnoDB는 대부분의 기능을 기본적으로 제공합니다. 그러나, InnoDB 테이블은 불변이므로, 모든 ALTER TABLE문장이 모든 데이터를 새로운 테이블에 복사 한다는 것을 고려해야한다 . 이미 존재하는 데이터베이스를 마이그레이션해야 할 필요가 생길 때 문제가 더욱 악화 될 것입니다.

값이 많고 관련 데이터가 많은 사용자가 있습니다. 예를 들어 각 사용자마다 수백만 개의 제품이 있고 사용자가 많습니다. 각 테이블을 만들고 이름을 지정하는 것이 가장 쉽습니다 <user_id>_<entity_name>. 이렇게하면 개별 테이블의 크기를 크게 줄일 수 있습니다.

또한 계정 삭제시 사용자 데이터를 삭제하는 것은 O (1) 작업입니다. 큰 테이블에서 많은 양의 값을 제거해야하는 경우, MySQL은 잘못된 인덱스를 사용할지 또는 인덱스를 전혀 사용하지 않기로 결정할 수 있기 때문에 매우 중요합니다.

인덱스 힌트를 사용할 수 없다는 것도 도움이되지 않습니다 DELETE. ALTER데이터를 제거 하려면 테이블을 사용해야 할 수도 있지만, 이는 각 행을 새 테이블에 복사하는 것을 의미합니다.

각 사용자에 대한 테이블을 만드는 것은 분명히 복잡성을 증가 시키지만 관련 데이터가 많은 사용자 또는 유사한 엔티티를 제거하는 경우 큰 이점이 될 수 있습니다.

그러나 동적으로 생성 된 테이블을 사용하기 전에 행을 청크로 삭제 해보십시오. 복잡성이 줄어들어 결과가 도움이 될 수 있습니다. 물론 삭제할 수있는 것보다 빠르게 데이터를 가져 오는 경우 위에 언급 한 해결 방법을 사용할 수 있습니다.

그러나 사용자가 테이블을 분할 한 후에 테이블이 여전히 크고 오래된 행을 삭제해야하는 경우에는 어떻게해야합니까? 제거 할 수있는 것보다 더 빨리 데이터가 제공됩니다. 이 경우 MySQL의 테이블 파티셔닝을 시도해야합니다. 생성 시간 소인과 같이 서수 또는 연속 스 I 일에 정의 된 값으로 테이블을자를 필요가있을 때 유용합니다.

MySQL을 이용한 테이블 파티셔닝

MySQL을 사용하면 분할 된 테이블은 다중 테이블 인 것처럼 작동하지만 이전과 동일한 인터페이스를 사용할 수 있으며 응용 프로그램 측면에서 추가 논리가 필요하지 않습니다. 이것은 또한 테이블을 삭제 한 것처럼 파티션을 삭제할 수 있음을 의미합니다.

문서 (이 간단한 주제되지 후), 그래서 당신이 분할 된 테이블을 만드는 방법을 간단히 살펴 보겠습니다 꽤 상세도 훌륭하지만.

파티션을 처리하는 방법은 릭 제임스 (Rick James) 의 주제에 대한 글에서 가져온 것입니다. 그는 또한 당신이 당신의 테이블을 어떻게 계획해야하는지에 관해 상당한 통찰력을 제공합니다.

CREATE TABLE IF NOT EXISTS tbl (
      id INTEGER NOT NULL AUTO_INCREMENT,
      data VARCHAR(255) NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id, created_at)
    )

PARTITION BY RANGE (TO_DAYS(created_at)) (
        start        VALUES LESS THAN (0),
        from20170514 VALUES LESS THAN (TO_DAYS('2017-05-15')),
        from20170515 VALUES LESS THAN (TO_DAYS('2017-05-16')),
        from20170516 VALUES LESS THAN (TO_DAYS('2017-05-17')),
        future       VALUES LESS THAN MAXVALUE
    );

이 부분까지 특이한 것은 없습니다 PARTITION BY RANGE.

MySQL은, 당신은에 의해 분할 할 수 RANGE, LIST, COLUMN, HASH그리고 KEY당신은 그들에 대해 읽을 수있는 문서 . 파티션 키는 기본 키 또는 고유 인덱스의 일부 여야합니다.

로 시작하는 from<date>것은 자명하다. 각 파티션에는 created_at열이 다음 날의 날짜보다 작은 값이 있습니다. 이는 또한 from201204142012-04-15보다 오래된 모든 데이터 를 보유 한다는 의미 이기 때문에 정리를 수행 할 때 삭제할 파티션입니다.

future및 start파티션은 약간의 설명이 필요 : future우리가 아직 정의하지 않은 일에 대한 값을 보유하고 있습니다. 따라서 시간 내에 파티션 분할을 실행할 수 없으면 도착한 모든 데이터가 2017-05-17그곳에서 끝나게되므로 모든 데이터가 손실되지 않습니다. start안전망 역할도합니다. 모든 행이 DATETIME created_at값 을 가지기를 기대 하지만 가능한 오류가 발생할 때 대비해야합니다. 웬일인지 행이 NULL거기에 있으면 결국 start파티션 에서 끝나고 일부 디버깅을해야한다는 신호로 사용됩니다.

파티셔닝을 사용하면, MySQL은 데이터를 별도의 테이블 인 것처럼 디스크의 다른 부분에 보관하고 파티셔닝 키를 기반으로 데이터를 자동으로 구성합니다.

그러나 몇 가지 제한 사항을 고려해야합니다.

• 쿼리 캐시가 지원되지 않습니다.
• 파티션 된 InnoDB 테이블에서는 외래 키가 지원되지 않는다.
• 파티션 된 테이블은 FULLTEXT 인덱스 또는 검색을 지원하지 않습니다.

새 파티션을 만들려면 기존 파티션을 재구성하고 필요에 맞게 분할해야합니다.

ALTER TABLE tbl
	REORGANIZE PARTITION future INTO (
		from20170517 VALUES LESS THAN (TO_DAYS('2017-05-18')),
		from20170518 VALUES LESS THAN (TO_DAYS('2017-05-19')),
		PARTITION future VALUES LESS THAN MAXVALUE
);

파티션 삭제는 alter table을 사용하지만 테이블을 삭제 한 것처럼 실행됩니다.

ALTER TABLE tbl
	DROP PARTITION from20170517, from20170518;

보시다시피, 명령문에 파티션의 실제 이름과 설명을 포함시켜야합니다. 그것들은 MySQL에 의해 동적으로 생성 될 수 없기 때문에 애플리케이션 로직에서 처리해야합니다. 그것이 우리가 다음에 다룰 내용입니다.

Node.js와 MySQL을 사용한 테이블 파티셔닝 예제

실제 솔루션을 살펴 보겠습니다. 여기 예제 에서는 JavaScript 용 쿼리 빌더 인 knex 를 사용 합니다. SQL에 익숙하다면 코드를 이해하는 데 문제가 없어야합니다.

먼저 테이블을 만들어 보겠습니다.

const dedent = require('dedent')
const _ = require('lodash')
const moment = require('moment')

const MAX_DATA_RETENTION = 7
const PARTITION_NAME_DATE_FORMAT = 'YYYYMMDD'

Table.create = function () {
  return knex.raw(dedent`
    CREATE TABLE IF NOT EXISTS \`${tableName}\` (
      \`id\` INTEGER NOT NULL AUTO_INCREMENT,
      \`data\` VARCHAR(255) NOT NULL,
      \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (\`id\`, \`created_at\`)
    )
    PARTITION BY RANGE ( TO_DAYS(\`created_at\`)) (
      PARTITION \`start\` VALUES LESS THAN (0),
      ${Table.getPartitionStrings()}
      PARTITION \`future\` VALUES LESS THAN MAXVALUE
    );
  `)
}

Table.getPartitionStrings = function () {
  const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)
  const partitions = days.map((day) => {
    const tomorrow = moment().subtract(day, 'day').format('YYYY-MM-DD')
    const today = moment().subtract(day + 1, 'day').format(PARTITION_NAME_DATE_FORMAT)
    return `PARTITION \`from${today}\` VALUES LESS THAN (TO_DAYS('${tomorrow}')),`
  })
  return partitions.join('\n')
}

이전에 보았던 것과 실제로 같은 문장이지만 파티션의 이름과 설명을 동적으로 만들어야합니다. 그래서 우리는이 getPartitionStrings방법 을 만들었습니다 .

첫 번째 행은 다음과 같습니다.

const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)

MAX_DATA_RETENTION - 2 = 55에서 -2 (마지막 값 배타적) -> 시퀀스를 생성하면 [ 5, 4, 3, 2, 1, 0, -1 ]현재 시간에서이 값을 빼고 파티션의 이름 ( today)과 해당 'limit ( tomorrow)을 만듭니다. 에 의해 파티셔닝 값이 진술에서 끊임없이 커지지 않으면 MySQL은 에러를 던지므로 순서가 중요합니다.

MySQL 및 Node.js를 사용한 대규모 데이터 제거 예제

이제 데이터 제거 단계별로 살펴 보겠습니다. 여기서 전체 코드를 볼 수 있습니다 .

첫 번째 방법 removeExpired은 현재 파티션 목록을 가져온 다음에 전달하는 것입니다 repartition.

const _ = require('lodash')

Table.removeExpired = function (dataRetention) {
  return Table.getPartitions()
    .then((currentPartitions) => Table.repartition(dataRetention, currentPartitions))
}

Table.getPartitions = function () {
  return knex('information_schema.partitions')
    .select(knex.raw('partition_name as name'), knex.raw('partition_description as description')) // description holds the day of partition in mysql days
    .where('table_schema', dbName)
    .andWhere('partition_name', 'not in', [ 'start', 'future' ])
    .then((partitions) => partitions.map((partition) => ({
      name: partition.name,
      description: partition.description === 'MAX_VALUE' ? 'MAX_VALUE' : parseInt(partition.description)
    })))
}

Table.repartition = function (dataRetention, currentPartitions) {
  const partitionsThatShouldExist = Table.getPartitionsThatShouldExist(dataRetention, currentPartitions)

  const partitionsToBeCreated = _.differenceWith(partitionsThatShouldExist, currentPartitions, (a, b) => a.description === b.description)
  const partitionsToBeDropped = _.differenceWith(currentPartitions, partitionsThatShouldExist, (a, b) => a.description === b.description)

  const statement = dedent
    `${Table.reorganizeFuturePartition(partitionsToBeCreated)}
    ${Table.dropOldPartitions(partitionsToBeDropped)}`

  return knex.raw(statement)
}

먼저 information_schema.partitionsMySQL이 관리 하는 테이블 에서 현재 존재하는 모든 파티션을 선택합니다 .

그런 다음 테이블을 위해 존재해야하는 모든 파티션을 생성합니다. 만약 A존재 B하는 파티션 세트가 존재해야하고 존재해야하는 파티션 세트라면

partitionsToBeCreated = B \ A

partitionsToBeDropped = A \ B.

getPartitionsThatShouldExist세트를 만듭니다 B.

Table.getPartitionsThatShouldExist = function (dataRetention, currentPartitions) {
  const days = _.range(dataRetention - 2, -2, -1)
  const oldestPartition = Math.min(...currentPartitions.map((partition) => partition.description))
  return days.map((day) => {
    const tomorrow = moment().subtract(day, 'day')
    const today = moment().subtract(day + 1, 'day')
    if (Table.getMysqlDay(today) < oldestPartition) {
      return null
    }

    return {
      name: `from${today.format(PARTITION_NAME_DATE_FORMAT)}`,
      description: Table.getMysqlDay(tomorrow)
    }
  }).filter((partition) => !!partition)
}

Table.getMysqlDay = function (momentDate) {
  return momentDate.diff(moment([ 0, 0, 1 ]), 'days') // mysql dates are counted since 0 Jan 1 00:00:00
}

파티션 객체 생성은 CREATE TABLE ... PARTITION BY RANGE명령문 작성과 매우 유사 합니다. 우리가 생성하려고하는 파티션이 현재 가장 오래된 파티션보다 오래되었는지 확인하는 것도 중요합니다 dataRetention. 시간이 지나면 변경할 필요가있을 수 있습니다 .

이 시나리오를 예로 들어 보겠습니다.

사용자가 7 일 동안 데이터를 보존한다고 가정하고 10 일로 업그레이드 할 수있는 옵션이 있다고 가정 해보십시오. 처음에는 사용자가 다음 순서로 날짜를 다루는 파티션을 가지고 있습니다 : [ start, -7, -6, -5, -4, -3, -2, -1, future ]. 한 달 정도 지나면 사용자가 업그레이드를 결정합니다. 누락 된 파티션은이 경우에 [ -10, -9, -8, 0 ]있습니다.

정리에서, 현재 스크립트는 재구성을 시도 할 future를 추가 누락 된 파티션에 대한 파티션을 한 후 현재의 것.

-7 이전의 파티션을 만드는 것은 처음부터 의미가 없습니다. 왜냐하면 그 데이터는 지금까지 버려진 것이었기 때문에 파티션 목록이 [ start, -7, -6, -5, -4, -3, -2, -1, -10, -9, -8, 0, future ] 단조롭게 증가하지 않는 것처럼 보였습니다. 따라서 MySQL 오류가 발생하고 정리가 실패합니다.

MySQL의 TO_DAYS(date)함수는 1 월 1 일부터 전달 된 일 수를 계산하므로 JavaScript로 복제합니다.

Table.getMysqlDay = function (momentDate) {
  return momentDate.diff(moment([ 0, 0, 1 ]), 'days')
}

이제는 삭제해야 할 파티션과 생성해야하는 파티션이 생겼으므로 새 파티션에 대해 먼저 새 파티션을 만듭니다.

Table.reorganizeFuturePartition = function (partitionsToBeCreated) {
  if (!partitionsToBeCreated.length) return '' // there should be only one every day, and it is run hourly, so ideally 23 times a day it should be a noop
  const partitionsString = partitionsToBeCreated.map((partitionDescriptor) => {
    return `PARTITION \`${partitionDescriptor.name}\` VALUES LESS THAN (${partitionDescriptor.description}),`
  }).join('\n')

  return dedent`
    ALTER TABLE \`${tableName}\`
      REORGANIZE PARTITION future INTO (
        ${partitionsString}
        PARTITION \`future\` VALUES LESS THAN MAXVALUE
      );`
}

새로 만들 파티션에 대한 설명을 작성하기 만하면됩니다.

우리는이 스크립트를 매 시간마다 실행하여 아무 것도 잘못되지 않도록하고 최소한 하루에 한 번 올바르게 정리를 수행 할 수 있습니다.

따라서 가장 먼저 확인해야 할 것은 파티션을 생성하는 것입니다. 이 작업은 처음 실행될 때만 수행되어야하며 하루에 23 번 작업하지 않아야합니다.

또한 구식 파티션을 삭제해야합니다.

Table.dropOldPartitions = function (partitionsToBeDropped) {
  if (!partitionsToBeDropped.length) return ''
  let statement = `ALTER TABLE \`${tableName}\`\nDROP PARTITION\n`
  statement += partitionsToBeDropped.map((partition) => {
    return partition.name
  }).join(',\n')
  return statement + ';'
}

이 메서드 ALTER TABLE ... DROP PARTITION는 이전에 본 것과 같은 문을 만듭니다 .

마지막으로, 모든 것이 재구성 준비가되었습니다.

  const statement = dedent
    `${Table.reorganizeFuturePartition(partitionsToBeCreated)}
    ${Table.dropOldPartitions(partitionsToBeDropped)}`
  
  return knex.raw(statement)

Wrapping 하기

보시다시피, 대중적인 믿음과는 달리 대량의 데이터를 처리 할 때는 MySQL과 같은 ACID 호환 DBMS 솔루션을 사용할 수 있으므로 트랜잭션 데이터베이스의 기능을 반드시 포기할 필요는 없습니다.

#MySQL과 같은 ACID 호환 DBMS 솔루션은 많은 양의 데이터를 처리하는 데 사용할 수 있습니다.

짹짹 클릭

그러나 테이블 파티셔닝에는 몇 가지 제한이 있습니다. 즉, InnoDB가 제공하는 모든 기능을 사용하지 않아 데이터 일관성을 유지할 수 있습니다. 또한 외래 키 제약 조건이나 전체 텍스트 검색과 같이 사용할 수있는 것이 무엇인지 응용 프로그램 논리에서 처리해야 할 수도 있습니다.