我正在寻找一种方法来优化具有 8M+ 行的大型表。
表结构如下所示: id INT PRIMARY AUTOINCREMENT 已删除 TINYINT(1) status TINYINT(1) ~10 个其他数据字段
还有(id,removed,status)唯一索引
大约一半行已删除=1,一半行已删除=0 90% 行的状态=1,剩余 10% 的值范围为 0-9
应用程序使用大约 200 个不同的查询直接或通过 JOIN 访问此表。重写应用程序和查询超出了我的范围。
大约 90% 的查询仅访问removed=0 AND status=1 行(WHERE...removed=0 AND status=1...),~1% 的查询同时访问已删除和未删除的行,无论状态如何,9%查询是直接 PK 命中(其中 id=X)。
问题是 - 按删除字段 (0,1) 进行分区是否会加速这 90% 的删除 = 0 的查询?如果 innodb 引擎只需访问约 3.5M 而不是 7M 行,理论上它应该加速所有查询?或者事实并非如此?
分区不是用于提高
SELECT
、INSERT
、UPDATE
或DELETE
查询的查询性能的工具。相反,它是一种用于改进表管理的工具(例如当您TRUNCATE
一次需要整个分区时)。这是一个常见的误解,因为数据越少越好,对吗?但这就是索引的目的。分区只是以线性方式减少数据。索引(特别是 B 树类型)以对数方式划分数据,这比分区的效率呈指数级提高。
所以回答你的问题:
不,事实并非如此。可能产生的任何性能提升都是可以忽略不计的,并且分区实际上使查询变慢是有原因的(特别是当需要跨分区数据时)。
您最好通过查看查询计划 (
EXPLAIN ANALYZE
) 并调整这些查询引用的表上的索引来了解查询通常过滤的内容。有时,查询调优或数据库重新架构也是唯一正确的解决方案。为JD的回答点赞。什么索引会更好或不会更好?...
PRIMARY KEY(id)
必然是UNIQUE
. 因此,UNIQUE(id, removed, status)
是浪费的。删除该索引。WHERE id = ...
)由 非常有效地处理PRIMARY KEY
。事实上,由于需要检查每个分区,这种情况PARTITIONing
会减慢速度。WHERE
?那么最好进行表扫描。status
。如果是这样,我建议INDEX(status, removed)
在测试稀有status
值(除了1
)时提供帮助。removed
,但不是status
,没有索引是有益的。仅对这种情况进行分区removed
可能会有所帮助,但会损害大多数其他情况。removed <> 0
可能会阻止使用索引和分区。请尝试修复代码remove = 1
。(INDEXes
对于相等和范围有用,但对于不相等有用。)