索引#

备注

HORC表不支持索引，MAGMA表支持索引。以下内容均是对MAGMA表的操作。

索引是提高数据库性能的常用方法。与不使用索引相比，索引允许数据库服务器更快地查找和检索特定的行。但是索引也增加了整个数据库系统的开销，且索引必须在更新表时进行维护，所以应该合理地使用索引。

索引一旦创建，就不需要进一步的干预:当表被修改时，系统将更新索引，当它认为这样比连续的表扫描更有效时，它将在查询中使用索引。但是您需要定期运行ANALYZE命令来更新统计数据，以便优化器选择出更好的执行计划。创建索引之后，系统必须使它与表保持同步。这增加了数据操作操作的开销。因此，应该删除查询中很少或从不使用的索引。

以下情况适合考虑使用索引：

  • 对于查询返回小结果集的查询工作负载，索引可提升性能。

  • 创建选择性的B-tree索引（索引选择性是列的不同值数与表中的行数之比，例如最好的唯一索引的选择性比率总是1.0）。

  • 用于频繁连接(例如外键列)的列索引可以通过为查询优化器启用更多的连接方法来提高连接性能。

  • WHERE子句中经常引用的列适合作为索引。

索引类型#

OushuDB 支持创建主键索引、唯一索引和普通索引; 支持单列索引、多列组合索引，支持多种数据类型任意组合; 支持 btree 类型索引。

B-tree索引可以被声明为唯一的。当一个索引被声明为唯一时，将不允许具有相同索引值的多个表行。空值被认为是不相等的。一个多列唯一索引只会拒绝所有索引列在两行的情况。

B-tree索引可以处理可以按一定顺序排序的数据上的相等和范围查询。查询规划器会考虑使用B-tree索引，只要索引的列涉及到使用这些操作符的比较:

  • <

  • < =

  • =

  • >=

  • >

构造相当于这些操作符的组合，例如BETWEEN通过B-tree索引搜索实现。

OushuDB 目前不支持的索引类型如下:

  • 不支持并发式创建索引，例如：

    create index concurrently

  • 不支持表达式索引、部分索引、覆盖索引；

  • 不支持Hash、GiST、SP-GiST、GIN、BRIN等类型索引；

  • 不支持创建btree类型索引时指定存储参数（如btree的节点填充率）；

  • 不支持指定在某个table space创建索引，均在默认table space。

创建索引#

CREATE INDEX命令用于创建表索引。B-tree索引是默认的索引类型。例如:

::

CREATE TABLE films(id int, title text) CREATE UNIQUE INDEX title_idx ON films (id);

B-tree索引类型支持多列索引，目前最多可以指定32个列。

CREATE INDEX idx_mul ON films (id, title);

备注

多列索引应该谨慎使用。在大多数情况下，单个列上的索引就足够了，并且节省了空间和时间。除非表的使用非常程式化，否则超过三列的索引不太可能有帮助。

OushuDB 为保证全局唯一性，主键索引、唯一索引列必须包含全部的分布列。Insert/Update/Delete 表数据会同步更新该表的索引数据，无需额外干预。创建索引时不阻塞该表的同步读，但会阻塞表的同步写入操作。

检查索引使用#

尽管索引不需要维护和调优，但检查实际查询工作负载实际使用了哪些索引仍然很重要。使用EXPLAIN命令检查单个查询的索引使用情况;它在这方面的应用见 使用EXPLAIN 。还可以收集关于正在运行的服务器中索引使用情况的总体统计信息。

很难制定一个确定要建立哪些指标的一般程序。在大多数情况下，大量的实验是必要的。下面给出了一些技巧。

  • 总是先运行ANALYZE命令。此命令收集关于表中值的分布的统计信息。此信息用于猜测查询返回的行数，计划人员需要此信息将实际成本分配给每个可能的查询计划。在没有任何实际统计数据的情况下，会假定一些默认值，这些值几乎肯定是不准确的。因此，在没有运行分析的情况下检查应用程序的索引使用情况是不可避免的。

  • 使用真实的数据进行实验。使用测试数据来设置索引将告诉您测试数据需要哪些索引，但仅此而已。

    使用非常小的测试数据集尤其致命。虽然从100000行中选择1000行可以作为索引的候选，但从100行中选择1行几乎不可能，因为100行很可能适合一个磁盘页，而且没有任何计划可以比按顺序获取1个磁盘页更好。

    在编写测试数据时也要小心，这在应用程序尚未投入生产使用时常常是不可避免的。非常相似、完全随机或按排序顺序插入的值将使统计数据偏离实际数据的分布。

  • 当不使用索引时，测试强制使用索引可能很有用。有一些运行时参数可以关闭各种计划类型。例如，关闭顺序扫描(enable_seqscan)和嵌套循环连接(enable_nestloop)是最基本的计划，这将迫使系统使用不同的计划。如果系统仍然选择顺序扫描或嵌套循环连接，那么可能有一个更基本的原因导致不使用索引;

  • 如果强制使用索引确实使用了索引，那么有两种可能:要么系统是正确的，使用索引确实不合适，要么查询计划的成本估计没有反映实际情况。因此，您应该对有索引和没有索引的查询进行计时。EXPLAIN ANALYZE命令在这里很有用。

  • 如果事实证明，成本估算是错误的，那么还有两种可能性。总成本是由每个计划节点的每一行成本乘以计划节点的选择性估计计算出来的。计划节点的估计成本可以通过运行时参数进行调整。不准确的选择性估计是由于统计数据不足造成的。可以通过调优统计数据收集参数来改进这一点(请参阅ALTER TABLE)。

删除索引#

DROP INDEX命令用于删除索引，其定义及数据都将被删除。例如:

DROP INDEX title_idx, idx_mul;

DROP TABLE会删掉该表的所有索引。删除索引后其索引数据会由后台compact功能在短时间内清理。