- 关于 TiDB
- 快速上手
- 部署标准集群
- 数据迁移
- 运维操作
- 监控与告警
- 故障诊断
- 性能调优
- 系统调优
- 软件调优
- SQL 性能调优
- SQL 性能调优概览
- 理解 TiDB 执行计划
- SQL 优化流程
- 控制执行计划
- 教程
- TiDB 工具
- 功能概览
- 适用场景
- 工具下载
- TiUP
- TiDB Operator
- Dumpling
- TiDB Lightning
- TiDB Data Migration
- Backup & Restore (BR)
- TiDB Binlog
- TiCDC
- sync-diff-inspector
- TiSpark
- 参考指南
- 架构
- 监控指标
- 安全加固
- 权限
- SQL
- SQL 语言结构和语法
- SQL 语句
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ADMIN SHOW TELEMETRY
ALTER DATABASE
ALTER INDEX
ALTER INSTANCE
ALTER PLACEMENT POLICY
ALTER TABLE
ALTER USER
ANALYZE TABLE
BACKUP
BEGIN
CHANGE COLUMN
CHANGE DRAINER
CHANGE PUMP
COMMIT
CREATE [GLOBAL|SESSION] BINDING
CREATE DATABASE
CREATE INDEX
CREATE PLACEMENT POLICY
CREATE ROLE
CREATE SEQUENCE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP [GLOBAL|SESSION] BINDING
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP PLACEMENT POLICY
DROP ROLE
DROP SEQUENCE
DROP STATS
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLASHBACK TABLE
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
RESTORE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW [BACKUPS|RESTORES]
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW BUILTINS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CONFIG
SHOW CREATE PLACEMENT POLICY
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW DRAINER STATUS
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW PLACEMENT
SHOW PLACEMENT FOR
SHOW PLACEMENT LABELS
SHOW PLUGINS
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW PROFILES
SHOW PUMP STATUS
SHOW SCHEMAS
SHOW STATS_HEALTHY
SHOW STATS_HISTOGRAMS
SHOW STATS_META
SHOW STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SHUTDOWN
SPLIT REGION
START TRANSACTION
TABLE
TRACE
TRUNCATE
UPDATE
USE
WITH
- 数据类型
- 函数与操作符
- 聚簇索引
- 约束
- 生成列
- SQL 模式
- 表属性
- 事务
- 垃圾回收 (GC)
- 视图
- 分区表
- 临时表
- 字符集和排序规则
- Placement Rules in SQL
- 系统表
mysql
- INFORMATION_SCHEMA
- Overview
ANALYZE_STATUS
CLIENT_ERRORS_SUMMARY_BY_HOST
CLIENT_ERRORS_SUMMARY_BY_USER
CLIENT_ERRORS_SUMMARY_GLOBAL
CHARACTER_SETS
CLUSTER_CONFIG
CLUSTER_HARDWARE
CLUSTER_INFO
CLUSTER_LOAD
CLUSTER_LOG
CLUSTER_SYSTEMINFO
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
DATA_LOCK_WAITS
DDL_JOBS
DEADLOCKS
ENGINES
INSPECTION_RESULT
INSPECTION_RULES
INSPECTION_SUMMARY
KEY_COLUMN_USAGE
METRICS_SUMMARY
METRICS_TABLES
PARTITIONS
PLACEMENT_RULES
PROCESSLIST
REFERENTIAL_CONSTRAINTS
SCHEMATA
SEQUENCES
SESSION_VARIABLES
SLOW_QUERY
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_STORAGE_STATS
TIDB_HOT_REGIONS
TIDB_INDEXES
TIDB_SERVERS_INFO
TIDB_TRX
TIFLASH_REPLICA
TIKV_REGION_PEERS
TIKV_REGION_STATUS
TIKV_STORE_STATUS
USER_PRIVILEGES
VIEWS
METRICS_SCHEMA
- UI
- CLI
- 命令行参数
- 配置文件参数
- 系统变量
- 存储引擎
- 遥测
- 错误码
- 通过拓扑 label 进行副本调度
- 常见问题解答 (FAQ)
- 版本发布历史
- 术语表
CREATE INDEX
CREATE INDEX
语句用于在已有表中添加新索引,功能等同于 ALTER TABLE .. ADD INDEX
。包含该语句提供了 MySQL 兼容性。
语法图
- CreateIndexStmt
- IndexKeyTypeOpt
- IfNotExists
- IndexTypeOpt
- IndexPartSpecificationList
- IndexOptionList
- IndexLockAndAlgorithmOpt
- IndexType
- IndexPartSpecification
- IndexOption
- IndexTypeName
- ColumnName
- OptFieldLen
- IndexNameList
- KeyOrIndex
CreateIndexStmt ::=
'CREATE' IndexKeyTypeOpt 'INDEX' IfNotExists Identifier IndexTypeOpt 'ON' TableName '(' IndexPartSpecificationList ')' IndexOptionList IndexLockAndAlgorithmOpt
IndexKeyTypeOpt ::=
( 'UNIQUE' | 'SPATIAL' | 'FULLTEXT' )?
IfNotExists ::=
( 'IF' 'NOT' 'EXISTS' )?
IndexTypeOpt ::=
IndexType?
IndexPartSpecificationList ::=
IndexPartSpecification ( ',' IndexPartSpecification )*
IndexOptionList ::=
IndexOption*
IndexLockAndAlgorithmOpt ::=
( LockClause AlgorithmClause? | AlgorithmClause LockClause? )?
IndexType ::=
( 'USING' | 'TYPE' ) IndexTypeName
IndexPartSpecification ::=
( ColumnName OptFieldLen | '(' Expression ')' ) Order
IndexOption ::=
'KEY_BLOCK_SIZE' '='? LengthNum
| IndexType
| 'WITH' 'PARSER' Identifier
| 'COMMENT' stringLit
| IndexInvisible
IndexTypeName ::=
'BTREE'
| 'HASH'
| 'RTREE'
ColumnName ::=
Identifier ( '.' Identifier ( '.' Identifier )? )?
OptFieldLen ::=
FieldLen?
IndexNameList ::=
( Identifier | 'PRIMARY' )? ( ',' ( Identifier | 'PRIMARY' ) )*
KeyOrIndex ::=
'Key' | 'Index'
示例
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7 | 10.00 | root | | data:Selection_6 |
| └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t1.c1, 3) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)
CREATE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.30 sec)
EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| IndexReader_6 | 10.00 | root | | index:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 10.00 | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
2 rows in set (0.00 sec)
ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.30 sec)
CREATE UNIQUE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.31 sec)
表达式索引
在一些场景中,查询的条件往往是基于某个表达式进行过滤。在这些场景中,一般的索引不能生效,执行查询只能遍历整个表,导致查询性能较差。表达式索引是一种特殊的索引,能将索引建立于表达式上。在创建了表达式索引后,基于表达式的查询便可以使用上索引,极大提升查询的性能。
假设要基于 lower(col1)
这个表达式建立索引,示例的 SQL 语句如下:
CREATE INDEX idx1 ON t1 ((lower(col1)));
或者等价的语句:
ALTER TABLE t1 ADD INDEX idx1((lower(col1)));
还可以在建表的同时指定表达式索引:
CREATE TABLE t1(col1 char(10), col2 char(10), index((lower(col1))));
表达式索引中的表达式需要用 (
和 )
包围起来,否则会报语法错误。
删除表达式索引与删除普通索引的方法一致:
DROP INDEX idx1 ON t1;
表达式索引涉及众多表达式。为了确保正确性,当前仅允许经充分测试的一部分函数用于创建表达式索引,即生产环境中仅允许表达式中包含这些函数。这些函数可以通过查询变量 tidb_allow_function_for_expression_index
得到。在后续版本中,这些函数会持续增加。
mysql> select @@tidb_allow_function_for_expression_index; +--------------------------------------------+ | @@tidb_allow_function_for_expression_index | +--------------------------------------------+ | lower, md5, reverse, upper, vitess_hash | +--------------------------------------------+ 1 row in set (0.00 sec)
对于以上变量返回结果之外的函数,由于未完成充分测试,当前仍为实验特性,不建议在生产环境中使用。其他的表达式例如运算符、cast
和 case when
也同样为实验特性,不建议在生产环境中使用。如果仍然希望使用,可以在 TiDB 配置文件中进行以下设置:
allow-expression-index = true
表达式索引不能为主键。
表达式索引中的表达式不能包含以下内容:
- 易变函数,例如
rand()
和now()
等。 - 系统变量以及用户变量。
- 子查询。
- AUTO_INCREMENT 属性的列。一个例外是设置系统变量
tidb_enable_auto_increment_in_generated
为true
后,可以去掉该限制。 - 窗口函数。
- row 函数。例如
create table t (j json, key k (((j,j))));
。 - 聚合函数。
表达式索引将隐式占用名字,_V$_{index_name}_{index_offset}
,如果已有相同名字的列存在,创建表达式索引将报错。如果后续新增相同名字的列,也会报错。
在表达式索引中,表达式的函数参数个数必须正确。
当索引的表达式使用了字符串相关的函数时,受返回类型以及其长度的影响,创建表达式索引可能会失败。这时可以使用 cast()
函数显式指定返回的类型以及长度。例如表达式 repeat(a, 3)
,为了能根据该表达式建立表达式索引,需要将表达式改写为 cast(repeat(a, 3) as char(20))
这样的形式。
当查询语句中的表达式与表达式索引中的表达式一致时,优化器可以为该查询选择使用表达式索引。依赖于统计信息,某些情况下优化器不一定选择表达式索引。这时可以通过 hint 指定强制使用表达式索引。
在以下示例中,假设建立在 lower(col1)
表达式上的索引为 idx
。
当读取的结果为相同的表达式时,可以使用表达式索引。例如:
SELECT lower(col1) FROM t;
当过滤的条件中有相同的表达式时,可以使用表达式索引。例如:
SELECT * FROM t WHERE lower(col1) = "a";
SELECT * FROM t WHERE lower(col1) > "a";
SELECT * FROM t WHERE lower(col1) BETWEEN "a" AND "b";
SELECT * FROM t WHERE lower(col1) in ("a", "b");
SELECT * FROM t WHERE lower(col1) > "a" AND lower(col1) < "b";
SELECT * FROM t WHERE lower(col1) > "b" OR lower(col1) < "a";
当查询按照相同的表达式进行排序时,可以使用表达式索引。例如:
SELECT * FROM t ORDER BY lower(col1);
当聚合函数或者 GROUP BY
中包含相同的表达式时,可以使用表达式索引。例如:
SELECT max(lower(col1)) FROM t;
SELECT min(col1) FROM t GROUP BY lower(col1);
要查看表达式索引对应的表达式,可执行 show index
或查看系统表 information_schema.tidb_indexes
以及 information_schema.STATISTICS
表,输出中 Expression
这一列显示对应的表达式。对于非表达式索引,该列的值为 NULL
。
维护表达式索引的代价比一般的索引更高,因为在插入或者更新每一行时都需要计算出表达式的值。因为表达式的值已经存储在索引中,所以当优化器选择表达式索引时,表达式的值就不需要再计算。因此,当查询速度比插入速度和更新速度更重要时,可以考虑建立表达式索引。
表达式索引的语法和限制与 MySQL 相同,是通过将索引建立在隐藏的虚拟生成列 (generated virtual column) 上来实现的。因此所支持的表达式继承了虚拟生成列的所有限制。
不可见索引
不可见索引 (Invisible Indexes) 不会被查询优化器使用:
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE(c2));
CREATE UNIQUE INDEX c1 ON t1 (c1) INVISIBLE;
具体可以参考 ALTER INDEX
。
相关系统变量
和 CREATE INDEX
语句相关的系统变量有 tidb_ddl_reorg_worker_cnt
、tidb_ddl_reorg_batch_size
、tidb_ddl_reorg_priority
和 tidb_enable_auto_increment_in_generated
,具体可以参考系统变量。
MySQL 兼容性
- 不支持
FULLTEXT
,HASH
和SPATIAL
索引。 - 不支持降序索引 (类似于 MySQL 5.7)。
- 无法向表中添加
CLUSTERED
类型的PRIMARY KEY
。要了解关于CLUSTERED
主键的详细信息,请参考聚簇索引。 - 表达式索引与视图存在兼容性问题。通过视图进行查询时,无法使用上表达式索引。
- 表达式索引与 Binding 存在兼容性问题。当表达式索引中的表达式存在常量时,对应查询所建的 Binding 会扩大范围。假设表达式索引中的表达式为
a+1
,对应的查询条件为a+1 > 2
。则建立的 Binding 为a+? > ?
,这会导致像a+2 > 2
这样的查询也会强制使用表达式索引,得到一个较差的执行计划。这同样影响 SQL Plan Management (SPM) 中的捕获和演进功能。