在索引布尔列与日期时间列上查询的性能

Question

如果索引是在datetime类型列而不是boolean类型列上设置（并且查询是在该列上进行的），则查询性能是否存在显着差异？

在我目前的设计中，我有2列：

• is_activeTINYINT（1），已 索引
• deleted_at 约会时间

查询是 SELECT * FROM table WHERE is_active = 1;

如果我deleted_at改为在列上创建索引并运行这样的查询，会不会更慢SELECT * FROM table WHERE deleted_at is null;？

Answer 1

这是一个具有1000万行的MariaDB（10.0.19）基准测试（使用sequence插件）：

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因为其他所有内容的总和都小于一毫秒。

TINYINT 索引：

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

运行时间： 〜738毫秒

TIMESTAMP 索引：

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倍，但索引大小甚至不是两倍。但是，如果索引大小不适合内存，则索引大小可能很大。因此，当我innodb_buffer_pool_size从更改为时1G，50M我得到以下数字：

• TINYINT: ~ 960 msec
• TIMESTAMP: ~ 1500 msec

为了更直接地解决这个问题，我对数据做了一些更改：

• 我使用DATETIME代替TIMESTAMP
• 由于条目通常很少被删除，因此我使用rand(1)<0.99（已删除1％）而不是rand(1)<0.5（已删除50％）
• 表大小从10M行更改为1M行。
• SELECT COUNT(*) 变成 SELECT *

索引大小：

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

deleted_at尽管非空的DATETIME需要8个字节（MariaDB），但由于99％的值为NULL，因此索引大小没有显着差异。

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

删除两个索引后，两个查询将在大约350毫秒内执行。并删除查询将在280毫秒内执行的is_active列deleted_at is null。

请注意，这仍然不是现实的情况。您不太可能希望从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列时，它在有索引的情况下以4毫秒执行，在没有索引的情况下以150毫秒执行。

因此，如果这种情况能以某种方式适合您的数据，那么结论将是：如果您很少选择已删除的条目，则删除该is_active列，并且不要在该列上创建索引deleted_at。或根据您的需求调整基准并做出自己的结论。