MYSQL10_索引的分类、创建修改删除索引、新特性降序隐藏

本文主要是介绍MYSQL10_索引的分类、创建修改删除索引、新特性降序隐藏，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

①. 索引的分类 - 功能、物理、个数

• ①. 从功能逻辑上说,索引主要有4种,分别是普通索引、唯一索引、主键索引、全文索引

• ②. 按照物理实现方式,索引可以分为2种:聚簇索引和非聚簇索引

• ③. 按照作用字段个数进行划分,分成单列索引和联合索引

②. 各个索引介绍 - 普通、唯一等

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

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

• ③. 主键索引
  主键索引就是一种特殊的唯一性索引,在唯一索引的基础上增加了不为空的约束,也就是NOT NULL+UNIQUE,一张表里最多只有一个主键索引

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

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

• ⑥. 全文索引

1. 全文索引也称全文检索是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集,对于小的数据集,它的用处比较小(目前可用es替代)
2. 使用参数FULLTEXT可以设置全文索引

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

③. 创建表时创建索引

• ①. 创建表时创建索引 - 语法

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

#语法
create table table_name [col_name data_type]
[UNIQUE | FULLTEXT |SPATIAL] [INDEX | KEY] [index_name](col_name[length][ASC | DESC])

• ②. 示例如下:

CREATE TABLE book (`book_id` INT,book_name VARCHAR ( 100 ),email varchar(20) ,AUTHORS VARCHAR ( 100 ),info VARCHAR ( 100 ),#声明索引PRIMARY KEY(`book_id`),INDEX idx_bname ( book_name ),UNIQUE INDEX u_idx_email(`email`)
);

• ③. 通过命令查看索引

#方式一
SHOW CREATE TABLE book;
#方式二
SHOW INDEX FROM book;
#性能分析工具
EXPLAIN SELECT * FROM book WHERE book_name = 'mqsql';

④. 使用ALTER创建索引

• ①. ALTER TABLE语法创建如下

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

• ②. 案例演示

CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);SHOW INDEX FROM book5;
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);

⑤. 使用CREATE INDEX

• ①. CREATE INDEX基本语法如下

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

• ②. 案例演示

CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);SHOW INDEX FROM book6;
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);

⑥. 删除索引 - index

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

ALTER TABLE table_name DROP INDEX index_name;

• ②. 使用DROP INDEX语句删除索引的基本语法格式如下

DROP INDEX index_name ON table_name;

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

⑦. MySQL8.0索引新特性 - 降序索引

• ①. 分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1,结果如下

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

• ②. 在MySQL 5.7版本中查看数据表ts1的执行计划,结果如下

1. 从结果可以看出,执行计划中扫描数为799,而且使用了Using filesort
2. 提示 Using filesort是MySQL中一种速度比较慢的外部排序,能避免是最好的。多数情况下,管理员可以通过优化索引来尽量避免出现Using filesort,从而提高数据库执行速度

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5

• ③. 在MySQL 8.0版本中查看数据表ts1的执行计划。从结果可以看出,执行计划中扫描数为5,而且没有使用Using filesort

• ④. 注意:降序索引只对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低。例如,上述查询排序条件改为order by a desc, b desc,MySQL 5.7的执行计划要明显好于MySQL 8.0

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

⑧. MySQL8.0索引新特性 - 隐藏索引

• ①. 在MySQL 5.7版本及之前,只能通过显式的方式删除索引。此时,如果发现删除索引后出现错误,又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大,或者数据表本身比较大,这种操作就会消耗系统过多的资源,操作成本非常高

• ②. 从MySQL 8.x开始支持 隐藏索引invisible indexes,只需要将待删除的索引设置为隐藏索引,使查询优化器不再使用这个索引即使使用force index强制使用索引,优化器也不会使用该索引,确认将索引设置为隐藏索引后系统不受任何响应,就可以彻底删除索引。 这种通过先将索引设置为隐藏索引,再删除索引的方式就是软删除

• ③. 创建表时直接创建

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

• ④. 在已经存在的表上创建

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

• ⑤. 通过ALTER TABLE语句创建

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

• ⑥. 切换索引可见状态 已存在的索引可通过如下语句切换可见状态:

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

这篇关于MYSQL10_索引的分类、创建修改删除索引、新特性降序隐藏的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---