这是一个具有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
因为其他所有内容的总和都小于一毫秒。
索引:
SELECT COUNT(*) FROM test WHERE is_active = 1;
运行时间:
索引:
SELECT COUNT(*) FROM test WHERE deleted_at is null;
运行时间:
索引大小:
select database_name, table_name, index_name, stat_value*@@innodb_page_size
from @R_502_1638@.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
我得到以下数字:
为了更直接地解决这个问题,我对数据做了一些更改:
索引大小:
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
。或根据您的需求调整基准并做出自己的结论。