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

梦里南慌

如果索引是在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;？

解决方案:


            这是一个具有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)为了衡量我执行查询后使用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)
表大小从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。或根据您的需求调整基准并做出自己的结论。

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

梦里南慌 LV1