删除分区表中记录的布尔字段索引

fbcarpbf  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(366)

我有一个很大的mysql表,其中可能包含1亿条记录。表的模式是这样的-

Id varchar(36), --guid,  primary key
IsDirty bit(1),
CreatedOn(Date),
Info varchar(500)

我在createdon字段上创建了一个分区,它为每月的数据创建了一个分区。表中的某些行被更新并 isDirty 设置为1。最大值时,只有10%的行具有 IsDirty = 1 . 有一个进程每晚运行,删除6个月前值为isdirty=0的数据。
如果我也在isdirty字段上创建一个索引,是否有任何性能提升?据我所知,在位字段上创建索引可能不会增加太多性能,但删除记录后重新编制索引可能会由于索引而降低性能。
我的理解正确吗?有没有更好的方法来实现所需的功能?

tp5buhyn

tp5buhyn1#

不要为分区而烦恼,它不太可能有助于提高性能。无论如何,您将需要有越来越多的分区和使用 PARTITION BY RANGE(to_days(..)) . 你不能使用 DROP PARTITION ,这将使删除非常快。
我暂时收回。这可能会起作用,也可能会考虑到 DROP PARTITION ,但我对语法感到困惑。

PARTITION BY RANGE(TO_DAYS(CreatedOn))
SUBPARTITION BY LINEAR KEY(IsDirty)
SUBPARTITIONS 2

如果你最终得到一个大的 DELETE 每晚,那么
每小时(或连续)做一次,这样删除量就不会太大
如这里所讨论的
还有,有吗

INDEX(IsDirty, CreatedOn) -- in this order.

(注:如果子分区可以工作;不需要此索引。)
其他提示:
使用innodb。
innodb_buffer_pool_size 大约是内存大小的70%。
由于访问的随机性,UUID对于大型表来说非常糟糕——因此i/o很高。 Id varchar(36), --guid, primary key --把它装进 BINARY(16) . (如果需要帮助,请告诉我。)节省空间-->收缩表-->减少i/o。
由于uuid太多,分区可能有助于避免大量的i/o——这是因为本月的所有插入都将进入一个分区。也就是说,“工作集”,因此缓冲池大小可以更小。

tp5buhyn

tp5buhyn2#

有一条经验法则说,最好用高基数索引列。基数是列中不同值的估计数。当你做一个 show indexes from your_table; 你会看到,你的 IsDirty 列的基数为2。非常糟糕。
然而,这并不考虑数据的分布。只有10%的人 IsDirty = 1 ,查询如下 select * from your_table where IsDirty = 1 会从指数中受益。另一方面,删除作业 IsDirty = 0 这样做不会有好处,因为简单地做一个完整的表扫描更便宜,因为使用辅助索引意味着从索引中读取主键(在每个辅助索引中存储主键,因此最好使主键尽可能小)来标识要读取的行。
手册说明了何时首选全表扫描:
查询每个表索引,并使用最佳索引,除非优化器认为使用表扫描更有效。有一次,扫描是基于最佳索引是否跨越表的30%以上而使用的,但固定的百分比不再决定使用索引还是扫描的选择。现在,优化器更加复杂,它的估计基于其他因素,如表大小、行数和i/o块大小。
还要注意,位数据类型不适合存储值0或1。有一个bool数据类型(内部实现为tinyint(1))。我想我在什么地方读过这样做的原因,但我已经忘记了)。

相关问题