- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

【mysql】索引的基本使用

兮动人发表于 2023/01/03 22:04:33 2023/01/03

【摘要】【mysql】索引的基本使用

1. 索引的声明与使用

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有4种,分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引.
按照作用字段个数进行划分，分成单列索引和联合索引。

1、普通索引

在创建普通索引时,不附加任何限制条件，只是用于提高查询效率。这类索引|可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后,可以通过索引进行查询。例如，在表 student 的字段 name.上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2、唯一性索引
使用 UNIQUE 参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。
例如，在表 student 的字段 email 中创建唯一性索引，那么字段 email 的值就必须是唯一的。通过唯一- 性索引，可以更快速地确定某条记录。

3、主键索引
主键索引就是一种特殊的唯一性索引，在唯一索弓的基础，上增加了不为空的约束，也就是

NOT NULL+UNIQUE

一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

4、单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索弓|只对应一个字段即可。一个表可以有多个单列索引。

5、多列(组合、联合)索引
多列索引是在表的多个字段组合，上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段 id、name 和 gender 上建立一个多列索引 idx_ id_ name_ gender ，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

6、全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。
使用参数FULLTEXT可以设置索弓为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引|列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR 或TEXT类型及其系列类型的字段上,查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型，该字段包含了很多文字信息。在字段 information.。上建立全文索引|后，可以提高查询字段information的速度。
全文索弓|典型的有两种类型:自然语言的全文索引和布尔全文索引。
自然语言搜索弓|擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反,非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。
MySQL数据库从3.23.23版开始支持全文索引,但MySQL5.6.4I以前只有Myisam支持，5.6.4版本以后 innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时,要先看-下自己的MySQL版本、存储弓|擎和数据类型是否支持全文索引。
随着大数据时代的到来，关系型数据库应对全文索弓的需求已力不从心，逐渐被solr 、ElasticSearch 等专门的搜索弓|擎所替代。

7、空间索引
使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT 、LINESTRING 和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

小结：不同的存储引擎支持的索引类型也不一样
InnoDB:支持B-tree、Full-text 等索引，不支持Hash索引;
MyISAM :支持B-tree、Full-text 等索引，不支持Hash索引;
Memory :支持B-tree、Hash 等索引，不支持Full-text索引;
NDB :支持Hash索引,不支持B-tree、Full-text 等索引;
Archive :不支持B-tree、Hash、 Full-text 等索引;

1.2 创建索引

1.2.1 创建表的时候创建索引（隐式）

隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE 中指定索引列，使用 ALTER TABLE 语句在存在的表上创建索引，或者使用CREATE INDEX 语句在已存在的表上添加索引。

创建表的时候创建索引
使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。
举例：

CREATE TABLE dept(
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(20) 
);

执行完成后，可以看到就会自动创建一个主键索引，主键约束创建后就会自动给dept_id字段添加个主键索引

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

执行后，可以看到创建了三个索引，创建主键约束、唯一性约束、外键约束就会创建与之对应的主键索引、唯一性索引和普通索引

1.2.2 在已经存在的表上创建索引（显式）

显式的方式创建：

显式创建表时创建索引，基本语法格式如下:

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指定升序或者降序的索引值存储

1、创建普通索引

在book表中的 year_ publication 字段上建立普通索引，SQL语句如下:

CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX idx_bname(book_name)
);

通过命令查看索引

方式1：

SHOW CREATE TABLE book;

或

SHOW CREATE TABLE book;

这里的Key表示索引

方式2：

SHOW INDEX FROM book;

性能分析工具：EXPLAIN

EXPLAIN SELECT * FROM book WHERE book_name = 'mysql检索';

2、创建唯一索引

声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);

当insert重复的语句时，也会报错，和声明唯一约束一样，不能有重复的值，可以插入 null

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'book1','第一本书');

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'book2',NULL);

3、主键索引

通过定义主键约束的方式定义主键索引

CREATE TABLE book2(
book_id INT PRIMARY KEY ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book2;

4、创建单列索引

CREATE TABLE book3(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX idx_bname(book_name)
);

Seq_in_index 为 1 说明是单列索引

5、创建联合索引

CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

分析

EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';

EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';

6、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR 和TEXT列创建索引。索引总是对整个列进行，不支持局部(前缀)索引。|
举例1：创建表test4, 在表中的info字段上建立全文索引，SQL语句如下: .

CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
)ENGINE=MyISAM;

在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因为在此版本中InnoDB支持全文索引。

语句执行完毕之后，使用SHOW CREATE TABLE查看表结构: .

SHOW INDEX FROM test4;

由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。
举例2：创建一个给title和body字段添加全文索引的表

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT, 
FULLTEXT index (title, body)
) ENGINE = INNODB;

举例3：

CREATE TABLE papers (
id int(10) unsigned NOT NULL AUTO_ INCREMENT,
title varchar(200) DEFAULT NULL,
content text,
PRIMARY KEY ('id'),
FULLTEXT KEY title (title, content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询:

SELECT * FROM papers WHERE content LIKE ' %查询字符串%' ;

全文索引用match+against方式查询：

SELECT * FROM papers WHERE MATCH(title, content) AGAINST ('查询字符串');

明显的提高查询效率

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

7、创建空间索引

空间索引创建中，要求空间类型的字段必须为非空。
举例：创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test5;

test5表的geo字段上创建了名称为spa_jidx_geo的空间索引，注意创建时指定空间类型字段值为非空约束，并且表的存储引擎为MyISAM

1.3 删除索引

MySQL中删除索引使用ALTER TABLE 或者DROP INDEX 语句，两者可实现相同的功能，DROP INDEX语句在内部
被映射到一个ALTER TABLE语句中。
查看索引：

SHOW INDEX FROM 表名;

1、使用ALTER TABLE删除索引，ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name ;

注意：添加AUTO_ INCREMENT约束字段的唯一索引不能被删除
通过删除主键约束的方式删除主键索引

ALTER TABLE book2 DROP PRIMARY KEY;

2、使用DROP INDEX语句删除索引，DROP INDEX删除索引|的基本语法格式如下：

DROP INDEX index_name ON table_name;

3、删除联合索引中的某个字段索引

CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

但同时也会把表中对应的字段给删除掉

ALTER TABLE book6 DROP COLUMN book_name;

1.4 在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句

1.4.1 使用ALTER TABLE语句创建索引

ALTER TABLE语句创建索引|的基本语法如下：

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

与创建表时创建索引的语法不同的是，在这里使用了ALTER TABLE 和ADD关键字，ADD表示向表中添加索引
举例1：在book表中创建普通索引、唯一索引、联合索引。

CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);

ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

1.4.2 使用CREATE INDEX ON语句创建索引

基本结构如下：

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

效果还是和上面一样的，只是实现的方式有所差异

CREATE INDEX idx_cmt ON book6(COMMENT);

CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name);

CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

2. 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大,这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持隐藏索引(invisible indexes) ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引(即使使用force index (强制使用索引) , 优化器也不会使用该索引)，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐顺索引，再删除索引的方式就是软删除。
同时，如果想验证某个索弓|删除之后的查询性能影响，就可以暂时先隐藏该索引。
注意：主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。
索引默认是可见的，在使用CREATE TABLE, CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或者INVISIBLE关键词设置索引的可见性。

1、创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

CREATE TABLE tablename (
	propname1 type1 [CONSTRAINT1],
	propname2 type2 [CONSTRAINT2],
	...
	propnamen typen,
	INDEX [ indexname ] ( propname1 [(length)]) INVISIBLE
);

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引l为不可见索引。
创建隐藏索引：

CREATE TABLE book7(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR,
	# 创建不可见索引
	INDEX idx_cmt(COMMENT) invisible	
);

EXPLAIN SELECT * FROM book7 WHERE COMMENT = '注释信息';

可以看到索引的信息都为 null ，表述索引被隐藏了

2、创建表之后创建

ALTER TABLE book7 ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;

CREATE INDEX idx_bname_info	ON book7(info) invisible;

SHOW INDEX FROM book7;

3、修改索引的可见性

ALTER TABLE book7 ALTER INDEX idx_bname_info visible;

如果将idx_bname_info 索引切换成可见状态，通过explain查看执行计划，发现优化器选择了idx_bname_info索引。
测试添加索引之后可见或不可见查询的时间有所差别：

EXPLAIN SELECT * FROM book7 WHERE info = '图书信息';

注意：
当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

通过设置隐藏索引的可见性可以查看索引对调优的帮助

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

【mysql】索引的基本使用

1. 索引的声明与使用

1.1 索引的分类

1.2 创建索引

1.2.1 创建表的时候创建索引（隐式）

1.2.2 在已经存在的表上创建索引（显式）

1、创建普通索引

2、创建唯一索引

3、主键索引

4、创建单列索引

5、创建联合索引

6、创建全文索引

7、创建空间索引

1.3 删除索引

1.4 在已经存在的表上创建索引

1.4.1 使用ALTER TABLE语句创建索引

1.4.2 使用CREATE INDEX ON语句创建索引

2. 隐藏索引

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

【mysql】索引的基本使用

1. 索引的声明与使用

1.1 索引的分类

1.2 创建索引

1.2.1 创建表的时候创建索引（隐式）

1.2.2 在已经存在的表上创建索引（显式）

1、创建普通索引

2、创建唯一索引

3、主键索引

4、创建单列索引

5、创建联合索引

6、创建全文索引

7、创建空间索引

1.3 删除索引

1.4 在已经存在的表上创建索引

1.4.1 使用ALTER TABLE语句创建索引

1.4.2 使用CREATE INDEX ON语句创建索引

2. 隐藏索引

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品