主索引与辅助索引:性能差异 [英] primary indexes Vs secondary indexes: performance differences

问题描述

我有一个小问题:主索引和次索引之间的性能有何不同?是什么导致了这种差异?

I've got a little question: what is the difference in performance between the primary and secondary indexes? what causes this difference?

我正在四处搜寻，并且已经看到二级索引存储在另一个表中，因此这会使所有操作变慢..但是还有其他一些原因可以证明这种性能下降是合理的吗?

I'm googling around, and I've seen that secondary indexes are stored in another table, so this slows down all operations.. but there are some other reasons that justify this decrease in performance?

非常感谢

推荐答案

集群表是没有堆"部分的B树-行直接存储在集群索引(主键)的B树结构中. B树的节点可以拆分或合并，因此物理位置或行可以更改，因此我们无法从二级索引到行都具有简单的指针"，因此二级索引必须包含的完整副本主要索引字段，以便能够可靠地标识行.

A clustered table is a B-Tree without "heap" portion - rows are stored directly in the B-Tree structure of the clustering index (primary key). Nodes of the B-Tree can be split or coalesced, so the physical location or rows can change, so we can't have a simple "pointer" from a secondary index to the rows, so the secondary index must include a complete copy of the primary index fields to be able to reliably identify rows.

这对于Oracle，MS SQL Server来说是正确的，并且对于InnoDB也是如此. .

This is true for Oracle, MS SQL Server and is also true for InnoDB.

这意味着群集表中的二级索引比基于堆的表中的二级索引差强人意"，

Which means secondary indexes in clustered tables are "fatter" than secondary indexes in heap-based tables, which:

• 降低数据聚类，
• 降低缓存的有效性，
• 使它们的维护成本更高
最重要的是，
• 对查询性能有影响:
  • 通过二级索引查询可能需要双重查找-一种通过二级索引查找以找到键"数据，另一种通过主索引查找以定位行本身(Oracle为避免第二种查找而进行了一些有趣的优化，但是据我所知，InnoDB没有.)
  • 另一方面，二级索引自然会涵盖更多的字段，因此在传统的基于堆的索引需要表访问的地方，可以完全避免第二次查找.但是，只需向其添加更多字段，就可以在基于堆的索引中实现相同的效果.
  • lowers the data clustering,
  • lowers the effectiveness of the cache,
  • makes them more expensive to maintain,
  • and most importantly, has consequences on query performance:
    • Querying through a secondary index may require double lookup - one lookup through the secondary index to locate the "key" data and one through the primary, to locate the row itself (Oracle has some interesting optimizations for avoiding the second lookup, but InnoDB does not, to my knowledge).
    • On the other hand, the secondary index naturally covers more fields, so the second lookup could be avoided altogether where a traditional heap-based index would require a table access. However, the same effect can be achieved in the heap-based index, by simply adding more fields to it.

    让我引用使用索引，卢克！:索引组织表和聚集索引的优点主要限于不需要第二个索引的表."

    这很可耻，因为MySQL不允许您从存储引擎中独立选择集群.

    Which is shame, since MySQL doesn't let you choose the clustering independently from the storage engine.

    这篇关于主索引与辅助索引:性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！