CUBRID 8.4.3用户参考手册：查询优化

2022-04-08 00:00:00 查询 连接 优化 提示 计划

查询优化

  • 更新统计
  • 检查统计信息
  • 使用SQL提示
  • 查看查询计划
  • 使用索引

更新统计

描述

通过UPDATE STATISTICS ON语句，你可以通过查询处理器生成内部统计。这样的统计允许数据库系统更有效地执行查询优化。

语法

UPDATE STATISTICS ON { table_spec [ {, table_spec } ] | ALL CLASSES | CATALOGCLASSES } [ ; ]

table_spec :

single_table_spec

( single_table_spec [ {, single_table_spec } ] )

single_table_spec :

[ ONLY ] table_name

| ALL table_name [ (EXCEPTtable_name ) ]

  • ALL CLASSES: 如果ALL CLASSES关键字被指定，数据库中存在的所有表的统计将被更新。

检查统计信息

描述

你可以通过CSQL解释器的会话命令检查统计信息。

语法

csql> ;info stats <table_name>

  • table_name: Table name to check the statistics Information
例子

下面的例子展示了如何在CSQL解释器中显示t1表的统计信息。

CREATE TABLE t1 (code INT);

INSERT INTO t1 VALUES(1),(2),(3),(4),(5);

CREATE INDEX ON t1(code);

UPDATE STATISTICS ON t1;

;info stats t1

CLASS STATISTICS

****************

 Class name: t1 Timestamp: Mon Mar 14 16:26:40 2011

 Total pages in class heap: 1

 Total objects: 5

 Number of attributes: 1

 Atrribute: code

    id: 0

    Type: DB_TYPE_INTEGER

    Mininum value: 1

    Maxinum value: 5

    B+tree statistics:

        BTID: { 0 , 1049 }

        Cardinality: 5 (5) , Total pages: 2 , Leaf pages: 1 , Height: 2

使用SQL提示

描述

使用提示可以影响查询执行的性能。你可以允许查询优化器通过添加SQL提示创建更高效的执行计划。CUBRID提供叙述连接，索引，统计信息等相关SQL提示。

语法

CREATE /*+ NO_STATS */ [TABLE |CLASS] ...;

ALTER /*+ NO_STATS */ [TABLE |CLASS] ...;

 

CREATE /*+ NO_STATS */ INDEX ...;

ALTER /*+ NO_STATS */ INDEX ...;

DROP /*+ NO_STATS */ INDEX ...;

 

SELECT /*+hint [ {hint } ... ]*/

SELECT --+hint [ {hint } ... ]

SELECT //+hint [ {hint } ... ]

 

hint :

USE_NL[(spec-name[{,spec-name}...])]

USE_IDX[(spec-name[{,spec-name}...])]

USE_MERGE[(spec-name[{,spec-name}...])]

ORDERED

USE_DESC_IDX

NO_DESC_IDX

NO_COVERING_IDX

SQL提示通过使用加号和注释来指定。CUBRID将这个注释解释为由空格分隔的提示列表。提示注释必须出现在SELECT, CREATE或ALTER关键字后，并且必须以加号（+）开始，遵循以下注释分隔符。

  • hint: 如下提示可被指定。
    • USE_NL: 表连接相关，查询优化器通过该提示创建一个嵌套循环连接执行计划。
    • USE_MERGE: 表连接相关，查询优化器通过该提示创建一个排序合并连接执行计划。
    • ORDERED: 表连接相关，查询优化器通过该提示创建一个连接查询计划，基于FROM子句中指定的表的顺序。FROM子句中左边的表变为外部表，右边的表变为内部表。
    • USE_IDX: 索引相关，查询优化器通过该提示为指定表创建一个对应的索引连接执行计划。
    • USE_DESC_IDX: 这是一个进行降序索引扫描的提示。更多相关信息，请参照Index Scan in Descending Order.
    • NO_DESC_IDX: 这是一个不使用降序索引扫描的提示。
    • NO_COVERING_IDX: 这是一个不使用覆盖索引的提示。相关细节，请参照Covering Index.
    • NO_STATS: 统计信息相关，查询优化器不更新统计信息。相应查询的查询性能能被提高；因为信息未被更新，所以查询计划未被优化。
    • RECOMPILE: 重新编译执行计划。该提示用来删除存储在缓存中的查询执行计划，并且创建一个新的查询执行计划。
  • spec_name: 如果spec_name和USE_NL，USE_IDX或USE_MERGE被同时指定，指定的连接方法仅适用于spec_name。如果USE_NL和USE_MERGE被同时指定，给定的提示将被忽视。在某些情况下，查询优化器能够创建基于给定的提示的查询执行计划。例如，USE_NL 指定为右外连接，查询转换为内部左外连接，并且无法保证连接顺序。
例1

下面的例子展示了如何检索Sim Kwon Ho赢得奖章的年度和奖章的种类。这里，需要创建一个athlete表作为外部表，game表作为内部表的嵌套循环连接执行计划。可以表示为下面的查询。查询优化器创建了game表作为外部表，athlete表作为内部表的嵌套循环连接执行计划。

SELECT /*+ USE_NL ORDERED  */ a.name, b.host_year, b.medal

FROM athlete a, game b WHERE a.name = 'Sim Kwon Ho' AND a.code = b.athlete_code;

  name                    host_year  medal

=========================================================

  'Sim Kwon Ho'                2000  'G'

  'Sim Kwon Ho'                1996  'G'

2 rows selected.

例2

下面的例子展示了如何通过NO_STAT提示检索查询执行时间来提高删除分区表（before_2008）的功能；一些数据被存储在表中。假设participant2表中有超过一百万的数据。例子中的执行时间依赖于系统性能和数据库配置。

-- 未使用NO_STATS提示

ALTER TABLE participant2 DROP partition before_2008;

SQL statement execution time: 31.684550 sec

Current transaction has been committed.

1 command(s) successfully processed.

 

-- 使用NO_STATS提示

ALTER /*+ NO_STATS */ TABLE participant2 DROP partition before_2008;

SQL statement execution time: 0.025773 sec

Current transaction has been committed.

1 command(s) successfully processed.

查看执行计划

描述

查看CUBRID SQL查询的查询计划，使用SET OPTIMIZATION语句来改变优化级别的值。使用GET OPTIMIZATION语句获取当前优化级别的值。

The CUBRID query optimizer determines whether to perform query optimization and output the query plan by referencing the optimization level value set by the user. The query plan is displayed as standard output; the following explanations are based on the assumption that the plan is used in a terminal-based program such as the CSQL Interpreter. In the CSQL query editor, you can view execution plan by executing the;plan command. SeeSession Commands. For information on how to view a query plan, see the CUBRID Manager.

Syntax

SET OPTIMIZATION LEVEL opt-level [;]
GET OPTIMIZATION LEVEL [ { TO |INTO }variable ] [;]

  • opt-level: A value that specifies the optimization level. It has the following meanings.
    • 0: Does not perform query optimization. The query is executed using the simplest query plan. This value is used only for debugging.
    • 1: Create a query plan by performing query optimization and executes the query. This is a default value used in CUBRID, and does not have to be changed in most cases.
    • 2: Creates a query plan by performing query optimization. However, the query itself is not executed. In generall, this value is not used; it is used together with the following values to be set for viewing query plans.
    • 257: Performs query optimization and outputs the created query plan. This value works for displaying the query plan by internally interpreting the value as 256+1 related with the value 1.
    • 258: Performs query optimization and outputs the created query plan. The difference from the value 257 is that the query is not executed. That is, this value works for displaying the query plan by internally interpreting the value as 256+2 related with the value 2. This setting is useful to examine the query plan but not to intend to see the query results.
    • 513: Performs query optimization and outputs the detailed query plan. This value works for displaying more detailed query plan than the value 257 by internally interpreting the value as 512+1.
    • 514: Performs query optimization and outputs the detailed query plan. However, the query is not executed. This value works for displaying more detailed query plan than the value 258 by internally interpreting the value as 512+2.
Example

The following example shows how to view query plan by using the example retrieving year when Sim Kwon Ho won medal and metal type.

GET OPTIMIZATION LEVEL

              Result

=============

                        1

 

SET OPTIMIZATION LEVEL 258;

 

SELECT a.name, b.host_year, b.medal

FROM athlete a, game b WHERE a.name = 'Sim Kwon Ho' AND a.code = b.athlete_code

Query plan:

  Nested loops

        Sequential scan(game b)

        Index scan(athlete a, pk_athlete_code, a.code=b.athlete_code)

There are no results.

0 rows selected.


Using Indexes

相关文章