데이터베이스 열의 비트 플래그에 대한 단점은 무엇입니까?

데이터베이스 열의 비트 플래그에 대한 단점은 무엇입니까?

다음 표를 고려합니다.

CREATE TABLE user_roles(
    pkey         SERIAL PRIMARY KEY,
    bit_id       BIGINT NOT NULL,
    name         VARCHAR(256) NOT NULL,
);

INSERT INTO user_roles (bit_id,name) VALUES (1,'public');
INSERT INTO user_roles (bit_id,name) VALUES (2,'restricted');
INSERT INTO user_roles (bit_id,name) VALUES (4,'confidential');
INSERT INTO user_roles (bit_id,name) VALUES (8,'secret');

CREATE TABLE news(
    pkey          SERIAL PRIMARY KEY,
    title         VARCHAR(256),
    company_fk    INTEGER REFERENCES compaines(pkey), -- updated since asking the question
    body          VARCHAR(512),
    read_roles    BIGINT -- bit flag 
);

read_flags는 뉴스 항목을 읽을 수 있는 역할의 일부 조합을 지정하는 비트 플래그입니다.따라서 제한적이고 기밀로 읽을 수 있는 뉴스 항목을 삽입할 경우 read_roles 값이 다음과 같이 설정됩니다.2 | 4또는 6 그리고 특정 사용자가 볼 수 있는 뉴스 게시물을 다시 얻고 싶을 때와 같은 쿼리를 사용할 수 있습니다.

select * from news WHERE company_fk=2 AND (read_roles | 2 != 0) OR  (read_roles | 4 != 0) ; 
select * from news WHERE company_fk=2 AND read_roles = 6; 

일반적으로 데이터베이스 열에 비트 플래그를 사용할 때의 단점은 무엇입니까?저는 이 질문에 대한 답이 데이터베이스에 특정한 것일 수 있다고 생각하기 때문에 특정한 데이터베이스의 단점에 대해 알고 싶습니다.

저는 제 애플리케이션에 Postgres 9.1을 사용하고 있습니다.

업데이트 성능이 떨어지는 전체 테이블 검색이 필요한 비트 작업에 인덱스를 사용하지 않는 데이터베이스에 대한 비트를 얻었습니다.그래서 저는 제 상황을 더 자세히 반영하기 위해 질문을 업데이트했습니다. 데이터베이스의 각 행은 특정 회사에 속하므로 모든 쿼리에는 색인이 있는 company_fk가 포함된 WHERE 절이 있습니다.

업데이트 지금은 6개의 역할만 있으며, 앞으로 더 많은 역할이 있을 수 있습니다.

UPDATE 역할은 서로 배타적이지 않으며 서로 상속됩니다. 예를 들어 제한된 역할은 공용에 할당된 모든 사용 권한을 상속합니다.

소수의 역할만 가지고 있다면 Postgre에서 스토리지 공간을 절약할 수도 없습니다.SQL. 열은 4바이트, 8바이트를 사용합니다.두 가지 모두 정렬 패딩이 필요할 수 있습니다.

• Postgres 행 크기 이해
• Postgre에서 공간 계산 및 절약SQL

열은 1바이트를 사용합니다.효과적으로 하나에 대해 네 개 이상의 부울 열을 적합시킬 수 있습니다.integera 열, a 에에 8 , a 해 개 8 상bigint.

또한 다음 사항도 고려합니다.NULL값은 NULL 비트맵에서 하나의 비트(간체)만 사용합니다.

개별 열을 읽고 색인화하는 것이 더 쉽습니다.다른 사람들은 이미 그것에 대해 논평했습니다.

식이나 부분 인덱스의 인덱스를 사용하여 인덱스 문제("논사거블")를 피할 수 있습니다.다음과 같은 일반화된 진술:

데이터베이스는 이와 같은 쿼리에 대한 인덱스를 사용할 수 없습니다.

또는

이 조건은 SARGable이 아닙니다!

완전히 사실이 아닙니다. 아마도 이러한 기능이 없는 다른 RDBMS의 경우일 수도 있습니다.
하지만 당신이 그 문제를 완전히 피할 수 있는데 왜 회피합니까?

당신이 명확히 한 것처럼, 우리는 6가지 유형(아마도 그 이상)에 대해 이야기하고 있습니다.과 함께 인과께가기함개기▁goboolean 하나의 공간에 을 절약할 수 것입니다.bigint이 경우 공간 요구사항은 중요하지 않은 것으로 보입니다.

---

이러한 플래그가 상호 배타적인 경우, 한 열의 유형 또는 작은 조회 테이블과 이를 참조하는 외부 키를 사용할 수 있습니다. (문제의 업데이트에서 제외됨)

단점:데이터 쓰기가 어렵고, 데이터 읽기가 어렵고, 디버그하기가 어렵지만, 데이터베이스가 이러한 쿼리에 인덱스를 사용할 수 없기 때문에 쿼리 속도가 느려집니다.

장점은 몇 바이트를 절약할 수 있다는 것입니다.BIT 필드와 비교하여 백만 개의 레코드 테이블에 몇 MB를 저장할 수 있습니다.그럴 가치가 거의 없어요 :)

여기에 적어도 하나의 큰 단점이 있습니다.

이 조건은 SARGable이 아닙니다!

이것은 큰 것이고 저에게는 거래를 깨는 것이 될 것입니다.수행해야 하는 비트별 평가는 (내가 알기로는) 어떤 데이터베이스에서도 색인화할 수 없습니다. 엔진이 평가를 수행하려면 모든 행을 확인해야 하므로 성능이 저하됩니다.

비트 Server 수 .BIT NOT NULL열:

SQL Server Database Engine은 비트 열의 저장을 최적화합니다.테이블에 비트 열이 8개 이하인 경우 열은 1바이트로 저장됩니다.9 ~ 16비트 열이 있는 경우 열은 2바이트로 저장됩니다.

비트 learn.microsoft.com .

JNK가 언급했듯이 비트 필드 정수에 대한 부분 비교는 SARGable이 아니므로 전체 값을 한 번에 비교하지 않는 한 비트 필드 정수의 인덱스는 사용할 수 없습니다.

SQL Server의 디스크에 있는 인덱스는 정렬을 기반으로 하므로 특정 비트 집합이 있는 행을 분리하려면 각 비트 열에 대해 별도의 인덱스가 필요합니다.1을 찾는 경우 공간을 절약하는 한 가지 방법은 1 값만 저장하는 열을 필터링하도록 하는 것입니다(0 값에는 인덱스 항목이 전혀 없음).

CREATE TABLE news(
    pkey          INT IDENTITY PRIMARY KEY,
    title         VARCHAR(256),
    company_fk    INTEGER REFERENCES compaines(pkey), -- updated since asking the question
    body          VARCHAR(512),
    public_role BIT NOT NULL DEFAULT 0,
    restricted_role BIT NOT NULL DEFAULT 0,
    confidential_role BIT NOT NULL DEFAULT 0,
    secret_role BIT NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX ByPublicRole ON news(public_role, pkey) WHERE public_role=1;
CREATE UNIQUE INDEX ByRestrictedRole ON news(restricted_role, pkey) WHERE restricted_role=1;
CREATE UNIQUE INDEX ByConfidentialRole ON news(confidential_role, pkey) WHERE confidential_role=1;
CREATE UNIQUE INDEX BySecretRole ON news(secret_role, pkey) WHERE secret_role=1;

select * from news WHERE company_fk=2 AND restricted_role=1 OR confidential_role=1; 
select * from news WHERE company_fk=2 AND restricted_role=1 AND confidential_role=1;

두 쿼리 모두 내가 생성한 랜덤 테스트 데이터를 사용하여 멋진 계획을 만듭니다.

항상 그렇듯이 인덱스는 실제 쿼리 사용량을 기반으로 유지 관리 비용과 균형을 이루어야 합니다.

언급URL : https://stackoverflow.com/questions/12270461/any-disadvantages-to-bit-flags-in-database-columns