如何使用“分组依据”和“地点”加速“选择计数(*)”?

以下是我将尝试的几件事,以增加难度:

(更容易) 确保你有正确的覆盖指数

CREATE INDEX ix_temp ON relations (relation_title, object_title);

考虑到您现有的模式,这将最大限度地提高性能,因为(除非您的mySQL优化器版本真的很笨!)它将最小化满足查询所需的I/O数量(不像索引按相反顺序扫描整个索引),并且它将覆盖查询,因此您不必接触聚集索引。

MySQL上varchar索引的性能挑战之一是,在处理查询时,字段的完整声明大小将被拉入RAM。因此,如果您有一个varchar(256),但只使用4个字符,那么在处理查询时,您仍然需要支付256字节的RAM使用量。哎哟因此,如果您可以轻松地缩小varchar限制,这将加快您的查询速度。

(更难)-正常化

30%的行只有一个字符串值,这显然是要求将其规范化为另一个表,这样就不会重复字符串数百万次。考虑规范化为三个表,并使用整数ID加入它们。

在某些情况下,您可以在封面下进行规范化,并使用与当前表名称匹配的视图隐藏规范化。。。然后,您只需要让INSERT/UPDATE/DELETE查询知道规范化,但可以不进行选择。

(最难)-对字符串列进行散列并对散列进行索引

如果规范化意味着改变过多的代码,但是您可以稍微改变一下您的模式,您可能需要考虑为字符串列创建128位哈希(使用 MD5 function

CREATE INDEX ix_temp ON relations (relation_title_hash, object_title_hash);

另外,如果relationship\u title的varchar大小很小,但object title的大小很长,那么您可能只能对object\u title进行散列,并在其上创建索引 (relation_title, object_title_hash) .

请注意,只有当这些字段中的一个或两个字段相对于散列的大小非常长时,此解决方案才有帮助。

还要注意的是,由于小写字符串的散列与大写字符串的散列不同,因此散列会对大小写敏感度/排序规则产生有趣的影响。因此,您需要确保在散列字符串之前对字符串应用规范化——换句话说,如果您在不区分大小写的数据库中,则仅散列小写。您可能还希望从开头或结尾修剪空格,具体取决于DB处理前导/尾随空格的方式。

使用复合索引,首先尝试对GROUPBY子句中的列进行索引。这样的查询可能只使用索引数据来回答,根本不需要扫描表。由于索引中的记录已排序,DBMS不需要作为组处理的一部分执行单独的排序。但是,索引会减慢表的更新速度,因此,如果您的表经历了大量更新,请谨慎使用。

如果将InnoDB用于表存储,则表的行将通过主键索引进行物理聚集。如果这(或其中的前导部分)恰好与您的组按键匹配,那么应该会加快这样的查询,因为相关记录将一起检索。同样,这避免了必须执行单独的排序。

物化视图是另一种可能的方法,但MySQL也不直接支持这种方法。但是,如果不要求计数统计信息完全是最新的,则可以定期运行 CREATE TABLE ... AS SELECT ...

您还可以使用触发器维护逻辑级缓存表。此表将为GROUPBY子句中的每一列提供一列,并带有一个Count列,用于存储特定分组键值的行数。每次在基表中添加或更新行时,在摘要表中插入或递增/递减该特定分组键的计数器行。这可能比伪物化视图方法更好,因为缓存的摘要始终是最新的,并且每次更新都是增量完成的,对资源的影响应该较小。但是,我认为您必须注意缓存表上的锁争用。

如果您有InnoDB,count(*)和任何其他聚合函数将执行表扫描。我在这里看到了一些解决方案:

1. 使用触发器并将聚合存储在单独的表中。优点:正直。缺点:更新速度慢
3. 完全分离存储访问层,并将聚合存储在单独的表中。存储层将了解数据结构,并可以应用增量,而不是进行完整计数。例如,如果您在其中提供“addObject”功能,您将知道何时添加了对象,因此聚合将受到影响。那么你只需要做一个 update table set count = count + 1 . 优点:更新速度快,完整性好(如果多个客户端可以更改同一条记录,您可能需要使用锁)。缺点:您需要结合一些业务逻辑和存储。

米萨姆 -始终将当前行数放在手边。

最后,如@justin所述,确保您拥有适当的覆盖索引:

CREATE INDEX ix_temp ON relations (relation_title, object_title);

计数(myprimaryindexcolumn) 并将性能与您的计数(*)进行比较

有一点是你真正需要的 更多的RAM/CPU/IO。你的硬件可能已经达到了这一点。

我会注意到使用索引通常是无效的(除非它们是 如果您的大型查询正在进行索引查找和书签查找,则可能是 在WITH(INDEX=0)中,强制执行表格扫描并查看是否更快。

这是从: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&tid=4631bab4-0104-47aa-b548-e8428073b6e6&cat=&lang=&cr=&sloc=&p=1

如果您想知道整个表的大小,那么应该查询元表或info模式(存在于我所知道的每个DBMS上,但我不确定MySQL)。如果你的查询是选择性的,你必须确保有一个索引。

恐怕你无能为力了。

你应该保留一个单独的计数表!此表可在每次插入/删除时更新。这将使这种查询变得即时。