索引布尔列和日期时间列的查询性能对比

Question

索引布尔列和日期时间列的查询性能对比

mysqlsqlperformanceindexingmariadb

10

如果在datetime类型列上设置索引，而不是在boolean类型列上进行查询，对于查询性能是否有显着差异？

在当前设计中，我有2个列：

is_active TINYINT(1)，已加索引
deleted_at DATETIME

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

如果我在deleted_at列上建立一个索引，并运行这样的查询SELECT * FROM table WHERE deleted_at is null;，会变慢吗？

- Alex

我不会期望有任何显著的差异。但是为什么不运行一些基准测试呢？ - Paul Spiegel

一个布尔标志的索引几乎没有用处 - 优化器会决定执行表扫描更快。（我不知道非布尔列上的NULLs。） - Rick James

2个回答

0

我认为is_active会更快，但这是你可以在一百万行上测试的东西。

- Ramy Talal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Paul Spiegel · Accepted Answer

以下是使用序列插件的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毫秒

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字节）比TINYINT（1字节）长4倍，但索引大小甚至不到两倍。但是如果索引大小不能适应内存，它可能会非常重要。所以当我将innodb_buffer_pool_size从1G更改为50M时，我得到以下数字：

TINYINT：〜960毫秒
TIMESTAMP：〜1500毫秒

更新

为了更直接地回答问题，我对数据进行了一些更改：

使用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

由于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毫秒。

请注意，这仍然不是一个现实的场景。您可能不想选择1百万行中的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列上创建索引。或者根据您的需求调整基准并得出自己的结论。