我有一个很大的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字段上创建一个索引,是否有任何性能提升?据我所知,在位字段上创建索引可能不会增加太多性能,但删除记录后重新编制索引可能会由于索引而降低性能。
我的理解正确吗?有没有更好的方法来实现所需的功能?
2条答案
按热度按时间tp5buhyn1#
不要为分区而烦恼,它不太可能有助于提高性能。无论如何,您将需要有越来越多的分区和使用
PARTITION BY RANGE(to_days(..))
. 你不能使用DROP PARTITION
,这将使删除非常快。我暂时收回。这可能会起作用,也可能会考虑到
DROP PARTITION
,但我对语法感到困惑。如果你最终得到一个大的
DELETE
每晚,那么每小时(或连续)做一次,这样删除量就不会太大
如这里所讨论的
还有,有吗
(注:如果子分区可以工作;不需要此索引。)
其他提示:
使用innodb。
套
innodb_buffer_pool_size
大约是内存大小的70%。由于访问的随机性,UUID对于大型表来说非常糟糕——因此i/o很高。
Id varchar(36), --guid, primary key
--把它装进BINARY(16)
. (如果需要帮助,请告诉我。)节省空间-->收缩表-->减少i/o。由于uuid太多,分区可能有助于避免大量的i/o——这是因为本月的所有插入都将进入一个分区。也就是说,“工作集”,因此缓冲池大小可以更小。
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))。我想我在什么地方读过这样做的原因,但我已经忘记了)。