인덱스된 부울 열과 Datetime 열에 대한 쿼리 성능

인덱스된 부울 열과 Datetime 열에 대한 쿼리 성능

인덱스가 다음과 같이 설정되어 있는 경우 쿼리 성능에 현저한 차이가 있습니까?datetime열 입력, 대신booleantype column (쿼리는 해당 컬럼에서 수행됨)

현재 설계에서는 2개의 컬럼이 있습니다.

• is_activeTINYINT(1), 색인화
• deleted_at날짜

쿼리는SELECT * FROM table WHERE is_active = 1;

더 느릴 수 있을까요?deleted_at대신 다음과 같은 쿼리를 실행했습니다.SELECT * FROM table WHERE deleted_at is null;?

10M 행의 MariaDB(10.0.19) 벤치마크를 다음에 나타냅니다(시퀀스 플러그인 사용).

drop table if exists test;
CREATE TABLE `test` (
    `id` MEDIUMINT UNSIGNED NOT NULL,
    `is_active` TINYINT UNSIGNED NOT NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX `is_active` (`is_active`),
    INDEX `deleted_at` (`deleted_at`)
) ENGINE=InnoDB
    select seq id
        , rand(1)<0.5 as is_active
        , case when rand(1)<0.5 
            then null
            else '2017-03-18' - interval floor(rand(2)*1000000) second
        end as deleted_at
    from seq_1_to_10000000;

사용하는 시간을 측정하기 위해set profiling=1실행하다show profile쿼리를 실행한 후.프로파일링 결과로부터 나는 다음 가치를 받아들인다.Sending data다른 것들은 모두 1밀리초보다 작기 때문입니다.

TINYINT 인덱스:

SELECT COUNT(*) FROM test WHERE is_active = 1;

실행 시간: 최대 738 밀리초

타임스탬프 인덱스:

SELECT COUNT(*) FROM test WHERE  deleted_at is null;

실행 시간: 최대 748 밀리초

인덱스 크기:

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats 
where database_name = 'tmp'
  and table_name = 'test'
  and stat_name = 'size'

결과:

database_name | table_name | index_name | stat_value*@@innodb_page_size
-----------------------------------------------------------------------
tmp           | test       | PRIMARY    | 275513344 
tmp           | test       | deleted_at | 170639360 
tmp           | test       | is_active  |  97107968 

TIMESTAMP(4바이트)는 TYNYINT(1바이트)의 4배이지만 인덱스 사이즈는 2배도 되지 않습니다.그러나 메모리에 맞지 않으면 인덱스 크기가 커질 수 있습니다.그래서 내가 변했을 때innodb_buffer_pool_size부터1G로.50M다음의 번호를 취득했습니다.

• TINYINT: 최대 960 밀리초
• 타임스탬프: 최대 1500밀리초

갱신하다

이 문제에 보다 직접적으로 대처하기 위해 데이터에 몇 가지 변경을 가했습니다.

• 타임스탬프 대신 DATETIME을 사용합니다.
• 엔트리는 거의 삭제되지 않기 때문에 사용합니다.rand(1)<0.99(1% 삭제) 대신rand(1)<0.5(50% 삭제)
• 테이블 크기가 10M 행에서 100M 행으로 변경되었습니다.
• SELECT COUNT(*)로 변경되었다SELECT *

인덱스 크기:

index_name | stat_value*@@innodb_page_size
------------------------------------------
PRIMARY    | 25739264
deleted_at | 12075008
is_active  | 11026432

99%가deleted_at값은 NULL입니다.인덱스 크기에는 큰 차이가 없습니다.다만, DATETIME이 비어 있지 않은 경우는, 8 바이트(MariaDB)가 필요합니다.

SELECT * FROM test WHERE is_active = 1;      -- 782 msec
SELECT * FROM test WHERE deleted_at is null; -- 829 msec

두 인덱스를 모두 폐기하면 두 쿼리 모두 약 350밀리초 안에 실행됩니다.그리고 드롭은is_active칼럼을 달다deleted_at is null쿼리는 280밀리초만에 실행됩니다.

이 시나리오는 아직 현실적이지 않습니다.1M에서 990K 행을 선택하여 사용자에게 전달하고 싶지 않을 것입니다.또한 표에 더 많은 열(텍스트 포함)이 있을 수 있습니다.하지만 그건 당신이 아마도 그 모든 것을 필요로 하지 않는다는 것을 보여준다.is_active열(추가 정보를 추가하지 않는 경우) 및 삭제되지 않은 항목을 선택하는 데 인덱스를 사용할 수 없습니다.

그러나 인덱스는 삭제된 행을 선택하는 데 유용할 수 있습니다.

SELECT * FROM test WHERE is_active = 0;

인덱스를 사용하는 경우는 10밀리초, 인덱스를 사용하지 않는 경우는 170밀리초 후에 실행됩니다.

SELECT * FROM test WHERE deleted_at is not null;

인덱스를 사용하면 11밀리초, 인덱스를 사용하지 않으면 167밀리초 만에 실행됩니다.

의 드롭is_active.column은 인덱스를 내에 실행됩니다.

이가 어떤 으로든 여러분의 은 " 안 된다"는 것입니다.is_active on 열 column column column column 。deleted_at[칼럼] 자신의 도 있습니다.또는 고객의 요구에 따라 벤치마크를 조정하여 독자적인 결론을 내릴 수도 있습니다.

생각에는is_active더 빠르겠지만 백만 개의 행에서 테스트할 수 있습니다.

언급URL : https://stackoverflow.com/questions/42875220/performance-of-query-on-indexed-boolean-column-vs-datetime-column