CREATE INDEX¶
在表中创建索引，以便更加快速高效地查询数据。
语法说明¶
在表中创建索引，以便更加快速高效地查询数据。
你无法看到索引，索引只能被用来加速搜索/查询。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
索引有几种常见的类型，分别为：
Primary Key：即主键索引，即标识在主键列上的索引。
Unique Key：即唯一索引，在非主键列上的索引，可以确保该列每个值都是唯一的。
Secondary Index：即二级索引，即在非主键列上标识的索引。
语法结构¶
> CREATE [UNIQUE] INDEX index_name
ON tbl_name (key_part,...)
COMMENT 'string'
语法释义¶
CREATE UNIQUE INDEX 语法¶
在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。
CREATE INDEX 语法¶
在表上创建一个次级索引。可以使用重复值和 Null 值。
VARCHAR 列的前缀索引¶
MatrixOne 支持对 VARCHAR 列创建前缀索引。前缀索引仅存储和索引列值的前 N 个字符（或字节），可以为长字符串列减小索引大小，同时仍支持高效的查找。
语法：
INDEX index_name (col_name(N))
UNIQUE INDEX index_name (col_name(N))
其中 N 是前缀长度，指定了索引中包含的列值字符数。
支持的创建方式：
CREATE TABLE中内联定义：CREATE TABLE t(a VARCHAR(32), INDEX idx_a(a(4)))独立的
CREATE INDEX：CREATE INDEX idx_a ON t(a(4))ALTER TABLE ... ADD INDEX：ALTER TABLE t ADD INDEX idx_a(a(4))
关键行为：
对于 UNIQUE 前缀索引，唯一性约束作用于前缀部分。例如，
UNIQUE INDEX uq_a(a(4))可确保列a前 4 个字符不重复。前缀索引支持回填（backfill）：可以先向表中插入数据，再创建前缀索引，已有数据会被索引。
前缀索引支持所有 DML 操作（INSERT、UPDATE、DELETE），并且优化器可利用前缀索引加速查询。
示例：
DROP TABLE IF EXISTS prefix_demo;
CREATE TABLE prefix_demo (
id INT PRIMARY KEY,
name VARCHAR(32),
description VARCHAR(32),
INDEX idx_name(name(4))
);
INSERT INTO prefix_demo VALUES (1, 'abcdef-long', 'blue');
INSERT INTO prefix_demo VALUES (2, 'uvwxyz-long', 'green');
-- 使用前缀索引进行查询
SELECT id, name, description FROM prefix_demo WHERE name = 'uvwxyz-long';
-- 独立 CREATE INDEX 创建前缀索引
CREATE TABLE prefix_demo2 (id INT PRIMARY KEY, name VARCHAR(32), val INT);
INSERT INTO prefix_demo2 VALUES (1, 'abcdef-long', 10), (2, 'lmnopq-long', 20);
CREATE INDEX idx_name2 ON prefix_demo2(name(4));
-- ALTER TABLE ADD INDEX 添加前缀索引
CREATE TABLE prefix_demo3 (id INT PRIMARY KEY, name VARCHAR(32), val INT);
INSERT INTO prefix_demo3 VALUES (1, 'abcdef-long', 10), (2, 'lmnopq-long', 20);
ALTER TABLE prefix_demo3 ADD INDEX idx_name3(name(4));
-- UNIQUE 前缀索引：唯一性约束作用于前 4 个字符
CREATE TABLE prefix_demo4 (
id INT PRIMARY KEY,
name VARCHAR(32),
val INT,
UNIQUE INDEX uq_name(name(4))
);
INSERT INTO prefix_demo4 VALUES (1, 'abcdef-long', 10);
-- 以下插入将失败，因为 'abcd' 与 'abcdef-long' 的前缀冲突
-- Expected-Success: false
-- INSERT INTO prefix_demo4 VALUES (2, 'abcdzz-conflict', 20);
DROP TABLE prefix_demo;
DROP TABLE prefix_demo2;
DROP TABLE prefix_demo3;
DROP TABLE prefix_demo4;
示例¶
示例 1：
drop table if exists t1;
create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
insert into t1 values(1,"Abby", 24);
insert into t1 values(2,"Bob", 25);
insert into t1 values(3,"Carol", 23);
insert into t1 values(4,"Dora", 29);
create unique index idx on t1(name);
mysql> select * from t1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | Abby | 24 |
| 2 | Bob | 25 |
| 3 | Carol | 23 |
| 4 | Dora | 29 |
+------+-------+------+
4 rows in set (0.00 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` INT NOT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`age` INT DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx` (`name`)
) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
create table t2 (
col1 bigint primary key,
col2 varchar(25),
col3 float,
col4 varchar(50)
);
create unique index idx on t2(col2) comment 'create varchar index';
insert into t2 values(1,"Abby", 24,'zbcvdf');
insert into t2 values(2,"Bob", 25,'zbcvdf');
insert into t2 values(3,"Carol", 23,'zbcvdf');
insert into t2 values(4,"Dora", 29,'zbcvdf');
mysql> select * from t2;
+------+-------+------+--------+
| col1 | col2 | col3 | col4 |
+------+-------+------+--------+
| 1 | Abby | 24 | zbcvdf |
| 2 | Bob | 25 | zbcvdf |
| 3 | Carol | 23 | zbcvdf |
| 4 | Dora | 29 | zbcvdf |
+------+-------+------+--------+
4 rows in set (0.00 sec)
mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`col1` BIGINT NOT NULL,
`col2` VARCHAR(25) DEFAULT NULL,
`col3` FLOAT DEFAULT NULL,
`col4` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`col1`),
UNIQUE KEY `idx` (`col2`) COMMENT `create varchar index`
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
示例 2：创建次级索引并查询
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary INT
);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'HR', 50000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (2, 'Jane', 'Smith', 'IT', 60000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (3, 'Mark', 'Johnson', 'IT', 55000);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (4, 'Mary', 'Brown', 'Sales', 48000);
mysql> CREATE INDEX DepartmentIndex ON Employees (Department);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM Employees WHERE Department = 'IT';
+------------+-----------+----------+------------+--------+
| employeeid | firstname | lastname | department | salary |
+------------+-----------+----------+------------+--------+
| 2 | Jane | Smith | IT | 60000 |
| 3 | Mark | Johnson | IT | 55000 |
+------------+-----------+----------+------------+--------+
2 rows in set (0.00 sec)