这是具有 10M 行的 MariaDB (10.0.19) 基准(使用序列插件 https://mariadb.com/kb/en/mariadb/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 index:
SELECT COUNT(*) FROM test WHERE is_active = 1;
运行时间:~738 msec
时间戳 index:
SELECT COUNT(*) FROM test WHERE deleted_at is null;
运行时间:~748 msec
索引大小:
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'
Result:
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
from 1G
to 50M
我得到以下数字:
Update
为了更直接地解决这个问题,我对数据做了一些更改:
- 我使用 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 毫秒内执行。
请注意,这仍然不是一个现实的场景。您不太可能希望从 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
如果您很少选择已删除的条目,请参阅列。或者根据您的需要调整基准并得出自己的结论。