indexadvisor

Documentation

Index

• Constants
• Variables
• func CollectDNFColumnsFromQuery(q Query) (s.Set[Column], error)
• func CollectIndexableColumnsForQuerySet(opt Optimizer, querySet s.Set[Query]) (s.Set[Column], error)
• func CollectIndexableColumnsFromQuery(q Query, opt Optimizer) (s.Set[Column], error)
• func CollectSelectColumnsFromQuery(q Query) (s.Set[Column], error)
• func CollectTableNamesFromQuery(defaultSchema, query string) ([]string, error)
• func FilterInvalidQueries(opt Optimizer, sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)
• func FilterSQLAccessingSystemTables(sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)
• func GetOptions(sctx sessionctx.Context, opts ...string) (vals, desc map[string]string, err error)
• func NormalizeDigest(sqlText string) (normalizedSQL, digest string)
• func ParseOneSQL(sqlText string) (ast.StmtNode, error)
• func RestoreSchemaName(defaultSchema string, sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)
• func SetOption(sctx sessionctx.Context, opt string, val ast.ValueExpr) error
• func SetOptions(sctx sessionctx.Context, options ...ast.RecommendIndexOption) error
• func TestKey(key string) string
• type Column
  • func CollectOrderByColumnsFromQuery(q Query) ([]Column, error)
  • func NewColumn(schemaName, tableName, columnName string) Column
  • func NewColumns(schemaName, tableName string, columnNames ...string) []Column
  • func (c Column) Key() string
• type ImpactedQuery
• type Index
  • func NewIndex(schemaName, tableName, indexName string, columns ...string) Index
  • func NewIndexWithColumns(indexName string, columns ...Column) Index
  • func (i Index) Key() string
  • func (i Index) PrefixContain(j Index) bool
• type IndexDetail
• type IndexSetCost
  • func (c IndexSetCost) Less(other IndexSetCost) bool
• type Optimizer
  • func NewOptimizer(sctx sessionctx.Context) Optimizer
• type Option
• type Query
  • func (q Query) Key() string
• type Recommendation
  • func AdviseIndexes(ctx context.Context, sctx sessionctx.Context, userSQLs []string, ...) ([]*Recommendation, error)
• type WorkloadImpact

Constants

const (
	// OptModule is the module name for index advisor options.
	OptModule = "index_advisor"
	// OptMaxNumIndex is the option name for the maximum number of indexes to recommend for a table.
	OptMaxNumIndex = "max_num_index"
	// OptMaxIndexColumns is the option name for the maximum number of columns in an index.
	OptMaxIndexColumns = "max_index_columns"
	// OptMaxNumQuery is the option name for the maximum number of queries to recommend indexes.
	OptMaxNumQuery = "max_num_query"
	// OptTimeout is the option name for the timeout of index advisor.
	OptTimeout = "timeout"
)

Variables

var (
	// AllOptions is the list of all options.
	AllOptions = []string{OptMaxNumIndex, OptMaxIndexColumns, OptMaxNumQuery, OptTimeout}
)

var QueryPlanCostHook func(sctx sessionctx.Context, stmt ast.StmtNode) (float64, error)

QueryPlanCostHook is used to calculate the cost of the query plan on this sctx. This hook is used to avoid cyclic import.

Functions

func CollectDNFColumnsFromQuery

func CollectDNFColumnsFromQuery(q Query) (s.Set[Column], error)

CollectDNFColumnsFromQuery parses the given Query text and returns the DNF columns. For a query `select ... where c1=1 or c2=2 or c3=3`, the DNF columns are `c1`, `c2` and `c3`.

func CollectIndexableColumnsForQuerySet

func CollectIndexableColumnsForQuerySet(opt Optimizer, querySet s.Set[Query]) (s.Set[Column], error)

CollectIndexableColumnsForQuerySet finds all columns that appear in any range-filter, order-by, or group-by clause.

func CollectIndexableColumnsFromQuery

func CollectIndexableColumnsFromQuery(q Query, opt Optimizer) (s.Set[Column], error)

CollectIndexableColumnsFromQuery parses the given Query text and returns the indexable columns.

func CollectSelectColumnsFromQuery

func CollectSelectColumnsFromQuery(q Query) (s.Set[Column], error)

CollectSelectColumnsFromQuery parses the given Query text and returns the selected columns. For example, "select a, b, c from t" returns []string{"a", "b", "c"}.

func CollectTableNamesFromQuery

func CollectTableNamesFromQuery(defaultSchema, query string) ([]string, error)

CollectTableNamesFromQuery returns all referenced table names in the given Query text. The returned format is []string{"schema.table", "schema.table", ...}.

func FilterInvalidQueries

func FilterInvalidQueries(opt Optimizer, sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)

FilterInvalidQueries filters out invalid queries from the given query set. some queries might be forbidden by the fix-control 43817.

func FilterSQLAccessingSystemTables

func FilterSQLAccessingSystemTables(sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)

FilterSQLAccessingSystemTables filters out queries that access system tables.

func GetOptions

func GetOptions(sctx sessionctx.Context, opts ...string) (vals, desc map[string]string, err error)

GetOptions gets the values of options.

func NormalizeDigest

func NormalizeDigest(sqlText string) (normalizedSQL, digest string)

NormalizeDigest normalizes the given Query text and returns the normalized Query text and its digest.

func ParseOneSQL

func ParseOneSQL(sqlText string) (ast.StmtNode, error)

ParseOneSQL parses the given Query text and returns the AST.

func RestoreSchemaName

func RestoreSchemaName(defaultSchema string, sqls s.Set[Query], ignoreErr bool) (s.Set[Query], error)

RestoreSchemaName restores the schema name of the given Query set.

func SetOption

func SetOption(sctx sessionctx.Context, opt string, val ast.ValueExpr) error

SetOption sets the value of an option.

func SetOptions

func SetOptions(sctx sessionctx.Context, options ...ast.RecommendIndexOption) error

SetOptions sets the values of options.

func TestKey

func TestKey(key string) string

TestKey is the key for test context.

Types

type Column

type Column struct {
	SchemaName string
	TableName  string
	ColumnName string
}

Column represents a column.

func CollectOrderByColumnsFromQuery

func CollectOrderByColumnsFromQuery(q Query) ([]Column, error)

CollectOrderByColumnsFromQuery parses the given Query text and returns the order-by columns. For example, "select a, b from t order by a, b" returns []string{"a", "b"}.

func NewColumn

func NewColumn(schemaName, tableName, columnName string) Column

NewColumn creates a new column.

func NewColumns

func NewColumns(schemaName, tableName string, columnNames ...string) []Column

NewColumns creates new columns.

func (Column) Key

func (c Column) Key() string

Key returns the key of the column.

type ImpactedQuery

type ImpactedQuery struct {
	Query       string
	Improvement float64
}

ImpactedQuery represents the impacted query.

type Index

type Index struct {
	SchemaName string
	TableName  string
	IndexName  string
	Columns    []Column
}

Index represents an index.

func NewIndex

func NewIndex(schemaName, tableName, indexName string, columns ...string) Index

NewIndex creates a new index.

func NewIndexWithColumns

func NewIndexWithColumns(indexName string, columns ...Column) Index

NewIndexWithColumns creates a new index with columns.

func (Index) Key

func (i Index) Key() string

Key returns the key of the index.

func (Index) PrefixContain

func (i Index) PrefixContain(j Index) bool

PrefixContain returns whether j is a prefix of i.

type IndexDetail

type IndexDetail struct {
	Reason    string // why recommend this index
	IndexSize uint64 // byte
}

IndexDetail represents the detail of the index.

type IndexSetCost

type IndexSetCost struct {
	TotalWorkloadQueryCost    float64
	TotalNumberOfIndexColumns int
	IndexKeysStr              string // IndexKeysStr is the string representation of the index keys.
}

IndexSetCost is the cost of a index configuration.

func (IndexSetCost) Less

func (c IndexSetCost) Less(other IndexSetCost) bool

Less returns whether the cost of c is less than the cost of other.

type Optimizer

type Optimizer interface {
	// ColumnType returns the column type of the specified column.
	ColumnType(c Column) (*types.FieldType, error)

	// PrefixContainIndex returns whether the specified index is a prefix of an existing index.
	PrefixContainIndex(idx Index) (bool, error)

	// PossibleColumns returns the possible columns that match the specified column name.
	PossibleColumns(schema, colName string) ([]Column, error)

	// TableColumns returns the columns of the specified table.
	TableColumns(schema, table string) ([]Column, error)

	// IndexNameExist returns whether the specified index name exists in the specified table.
	IndexNameExist(schema, table, indexName string) (bool, error)

	// EstIndexSize return the estimated index size of the specified table and columns
	EstIndexSize(db, table string, cols ...string) (indexSize float64, err error)

	// QueryPlanCost return the cost of the query plan.
	QueryPlanCost(sql string, hypoIndexes ...Index) (cost float64, err error)
}

Optimizer is the interface of a what-if optimizer. This interface encapsulates all methods the Index Advisor needs to interact with the TiDB optimizer. This interface is not thread-safe.

func NewOptimizer

func NewOptimizer(sctx sessionctx.Context) Optimizer

NewOptimizer creates a new Optimizer.

type Option

type Option struct {
	MaxNumIndexes int
	MaxIndexWidth int
	MaxNumQuery   int
	Timeout       time.Duration
	SpecifiedSQLs []string
}

Option is the option for the index advisor.

type Query

type Query struct {
	Alias      string
	SchemaName string
	Text       string
	Frequency  int
}

Query represents a Query statement.

func (Query) Key

func (q Query) Key() string

Key returns the key of the Query.

type Recommendation

type Recommendation struct {
	Database           string
	Table              string
	IndexName          string
	IndexColumns       []string
	IndexDetail        *IndexDetail
	WorkloadImpact     *WorkloadImpact
	TopImpactedQueries []*ImpactedQuery
}

Recommendation represents the result of the index advisor.

func AdviseIndexes

func AdviseIndexes(ctx context.Context, sctx sessionctx.Context,
	userSQLs []string, userOptions []ast.RecommendIndexOption) ([]*Recommendation, error)

AdviseIndexes is the entry point for the index advisor.

type WorkloadImpact

type WorkloadImpact struct {
	WorkloadImprovement float64
}

WorkloadImpact represents the workload impact.