MySQL建立图书信息表

文章目录

• 5.1 再谈索引的分类

• 5.1.1 普通索引
• 5.1.2 唯一索引
• 5.1.3 主键索引
• 5.1.4 单列索引
• 5.1.5 联合索引（多列索引）
• 5.1.6 全文索引
• 5.1.7 空间索引
• 小结

• 5.2 创建索引

• 5.2.1 在创建表时创建索引
• 5.2.2 在已经存在的表上创建索引

• 5.3 删除索引
• 5.4 MySQL8.0索引新特性

• 5.4.1 支持降序索引
• 5.4.2 隐藏索引

5.1 再谈索引的分类

• 按照功能逻辑上来分：普通索引、唯一索引、主键索引、全文索引。
• 按照物理实现方式来分：聚簇索引、非聚簇索引
• 按照作用字段个数来分：单列索引、联合索引
• 按照数据结构来分：B+树索引、Hash索引

5.1.1 普通索引

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

5.1.2 唯一索引

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

5.1.3 主键索引

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

5.1.4 单列索引

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

5.1.5 联合索引（多列索引）

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

5.1.6 全文索引

• 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。
• 使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型，该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。
• 随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被solr、ElasticSearch等专门的搜索引擎所替代。

5.1.7 空间索引

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

小结

搜索引擎/索引类型	B-树索引	全文索引	Hash索引
InnoDB	支持	支持
MyISAM	支持	支持
Memory	支持		支持
NDB			支持
Archive

5.2 创建索引

• 创建索引的三种方式：

• 在创建表的定义语句CREATE TABLE中指定索引
• 使用ALTER TABLE语句在存在的表上创建索引
• 使用CREATE INDEX语句在已存在的表上添加索引

5.2.1 在创建表时创建索引

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

CREATE DATABASE dbtest2;

USE dbtest2;

#隐式的方式创建索引：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

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

• 但是，如果显式创建表时创建索引的话，基本语法格式如下：

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.创建普通索引
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)
);

# 2.创建唯一索引
CREATE TABLE test1(
id INT NOT NULL,
name varchar(30) NOT NULL,
UNIQUE INDEX uk_idx_id(id)
);

# 3.创建主键索引
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);

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

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

# 6.创建全文索引
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;

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

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

• 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

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

• 使用CREATE INDEX创建索引，其实CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_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 idx_cmt ON book5(COMMENT);

CREATE UNIQUE INDEX  uk_idx_bname ON book5(book_name);

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

5.3 删除索引

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

ALTER TABLE table_name DROP INDEX index_name;

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

DROP INDEX index_name ON table_name;

实例：

SHOW INDEX FROM book5;

ALTER TABLE book5 
DROP INDEX idx_cmt;

DROP INDEX idx_cmt ON book5;

5.4 MySQL8.0索引新特性

5.4.1 支持降序索引

• 降序索引以降序存储键值。虽然在语法上，从MySQL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引(仅限于InnoDB存储引擎)。
• MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

5.4.2 隐藏索引

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

实例：

# 1.创建表时直接创建 在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
......
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);

# 2.在已经存在的表上创建
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

# 3.通过ALTER TABLE语句创建
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE

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

• 使隐藏索引对查询优化器可见：在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置，使隐藏索引对查询优化器可见。如果use_invisible_indexes 设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

// 查看优化器配置
 select @@optimizer_switch \G
 // 此时use_invisible_indexes属性配置值为off，说明隐藏索引默认对查询优化器不可见
 use_invisible_indexes=off
 // 使隐藏索引对查询优化器可见
 set session optimizer_switch="use_invisible_indexes=on";