索引的增删改SQL

索引的创建与删除

创建表时添加索引

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |DESC]

UNIQUE 、FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 指定升序或者降序的索引值存储。

CREATE TABLE book(
	book_id INT UNIQUE PRIMARY KEY,
	book_name VARCHAR(100),
	authors VARCHAR(100),
	info VARCHAR(100) ,
	comment VARCHAR(100),
	year_publication YEAR,
	INDEX(year_publication) # 普通索引
	UNIQUE INDEX uk_idx_id(book_id) # 唯一性索引
	PRIMARY KEY(book_id) # 主键索引
	INDEX single_idx_name(book_name(20))  # 单列 前缀索引
	INDEX multi_idx(id,name,age)  # 联合索引
	FULLTEXT INDEX futxt_idx_info(info)  # 全文索引
	geo GEOMETRY NOT NULL,
	SPATIAL INDEX spa_idx_geo(geo) # 空间索引，空间类型字段必须非空。
);

查看表的索引
SHOW INDEX FROM test1;

全文索引用 match+against 方式查询：
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点

使用全文索引前，搞清楚版本支持情况；
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

已存在表上添加索引

模板
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

实例

ALTER TABLE `table_name` ADD PRIMARY KEY(`column`)
ALTER TABLE `table_name` ADD UNIQUE(`column`)
ALTER TABLE `table_name` ADD FULLTEXT(`column`)
ALTER TABLE `table_name` ADD INDEX index_name(`column`)
ALTER TABLE `table_name` ADD INDEX index_name(`column1`, `column2`, `column3`)

删除索引

ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

创建降序索引

创建降序索引

CREATE TABLE ts1(
  a int,
  b int,
  index idx_a_b(a, b desc)
);

隐藏索引

创建隐藏索引

CREATE TABLE tablename(a int, INDEX idx_name(a) INVISIBLE);
CREATE INDEX idx_name ON tablename(propname[(length)]) INVISIBLE;
ALTER TABLE tablename ADD INDEX idx_name (propname [(length)]) INVISIBLE;

更改索引可见性

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE;
ALTER TABLE tablename ALTER INDEX index_name VISIBLE;

使隐藏索引对查询优化器可见

# 查看查询优化器的开关设置
select @@optimizer_switch;
# 可以看到 use_invisible_indexes=off
# 设置隐藏索引对查询优化器可见
set session optimizer_switch="use_invisible_indexes=on";
# 设置隐藏索引对查询优化器不可见
set session optimizer_switch="use_invisible_indexes=off";

🪴 Obsidian Publish

探索