49-MySQL-索引的创建与删除

作者: 紫荆秋雪_文 | 来源:发表于2022-10-08 18:13 被阅读0次

一、索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等
①：从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引
②：按照物理实现方式，索引可以分为 2 种，聚簇索引和非聚簇索引
③：按照作用字段个数进行划分，分成单列索引和联合索引

1、普通索引

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

2、唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引

3、主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL + UNIQUE，表里最多只有一个主键索引。这是由于主键索引的物理实现方式决定的，因为数据存储在文件中（叶子节点）只能按照一种顺序进行存储

4、单列索引

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

5、多列（组合、联合）索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。使用组合索引时遵循最左前缀集合

6、全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。他能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地赛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。
使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列种插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

6.1、自然语言的全文索引

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语

随着大数据时代的到来，关系性数据库应对全文索引的需求已力不从心，逐渐被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 等索引

二、创建索引

MySQL支持多种方法在单个或多个列上创建索引。
方式1：在创建表的定义语句CREATE TABLE中指定索引列
方式2：使用ALTER TABLE语句在存在的表上创建索引
方式3：使用CREATE INDEX语句在存在的表上创建索引

1、创建表的时候创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，在定义约束的同时还可以创建索引

1.1、使用`主键`标识时，自动会创建对应索引

创建表时指定主键

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

查看 dept 表的索引

SHOW INDEXES FROM dept;

dept表中的索引.png

1.2、使用`主键`、`外键`和`UNIQUE`等标识时，自动会创建对应索引

创建表代码

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)
);

查看索引

SHOW INDEXES FROM emp;

emp表索引.png

1.3、显示创建表时创建索引，基本语法格式

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.4、创建普通索引

在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 (year_publication)
);

1.5、创建唯一索引

CREATE TABLE test1
(
    id   INT         NOT NULL,
    name VARCHAR(30) NOT NULL,
    UNIQUE INDEX uk_idx_id (id)
);

1.6、主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

CREATE TABLE student
(
    id           INT(10) UNSIGNED AUTO_INCREMENT,
    student_no   VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY (id)
);

1.7、创建单列索引

CREATE TABLE test2
(
    id   INT      NOT NULL,
    name CHAR(50) NULL,
    INDEX single_idx_name (name(20))
);

1.8、创建组合索引

创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE test3
(
    id   INT(11)  NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT(11)  NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx (id, name, age)
);

1.9、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引

创建表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;

创建一个给 title 和 body 字段添加全文索引的表

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

FULLTEXT

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

1.10、创建空间索引

空间索引创建中，要求空间类型的字段必须为 非空

创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下

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

2、在已经存在的表上创建索引

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

2.1、使用ALTER TABLE语句创建索引

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

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

2.2、使用CREATE INDEX创建索引

CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为

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

三、删除索引

1、使用ALTER TABLE删除索引

ALTER TABLE删除索引的基本语法格式如下

ALTER TABLE table_name DROP INDEX index_name;

2、使用DROP INDEX语句删除索引

DROP INDEX删除索引的基本语法格式如下

DROP INDEX index_name ON table_name;

3、小结

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

创建表语句

CREATE TABLE test3
(
    id   INT(11)  NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT(11)  NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx (id, name, age)
);

查看索引

SHOW INDEXES FROM test3;

联合索引.png

删除 name 列

ALTER TABLE test3
DROP name;

查看索引

SHOW INDEXES FROM test3;

删除 name 列后的联合索引.png

删除 age 列

ALTER TABLE test3
DROP age;

查看索引

SHOW INDEXES FROM test3;

删除 age 列后的联合索引.png

删除 id 列

ALTER TABLE test3
DROP id;

查看索引

SHOW INDEXES FROM test3;

删除所有列后的联合索引也被删除.png

49-MySQL-索引的创建与删除

一、索引分类

1、普通索引

2、唯一性索引

3、主键索引

4、单列索引

5、多列（组合、联合）索引

6、全文索引

6.1、自然语言的全文索引

7、空间索引

小结：不同的存储引擎支持的索引类型也不一样

二、创建索引

1、创建表的时候创建索引

1.1、使用`主键`标识时，自动会创建对应索引

1.2、使用`主键`、`外键`和`UNIQUE`等标识时，自动会创建对应索引

1.3、显示创建表时创建索引，基本语法格式

1.4、创建普通索引

1.5、创建唯一索引

1.6、主键索引

1.7、创建单列索引

1.8、创建组合索引

1.9、创建全文索引

1.10、创建空间索引

2、在已经存在的表上创建索引

2.1、使用ALTER TABLE语句创建索引

2.2、使用CREATE INDEX创建索引

三、删除索引

1、使用ALTER TABLE删除索引

2、使用DROP INDEX语句删除索引

3、小结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL

49-MySQL-索引的创建与删除

一、索引分类

1、普通索引

2、唯一性索引

3、主键索引

4、单列索引

5、多列（组合、联合）索引

6、全文索引

6.1、自然语言的全文索引

7、空间索引

小结：不同的存储引擎支持的索引类型也不一样

二、创建索引

1、创建表的时候创建索引

1.1、使用主键标识时，自动会创建对应索引

1.2、使用主键、外键和UNIQUE等标识时，自动会创建对应索引

1.3、显示创建表时创建索引，基本语法格式

1.4、创建普通索引

1.5、 创建唯一索引

1.6、 主键索引

1.7、创建单列索引

1.8、创建组合索引

1.9、创建全文索引

1.10、 创建空间索引

2、在已经存在的表上创建索引

2.1、使用ALTER TABLE语句创建索引

2.2、使用CREATE INDEX创建索引

三、删除索引

1、使用ALTER TABLE删除索引

2、使用DROP INDEX语句删除索引

3、小结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL

1.1、使用`主键`标识时，自动会创建对应索引

1.2、使用`主键`、`外键`和`UNIQUE`等标识时，自动会创建对应索引

1.5、创建唯一索引

1.6、主键索引

1.10、创建空间索引