常见索引概念
1. 索引的声明与使用
2. MySQL8.0索引新特性
3. 索引设计原则
- 查看索引
  - 通过命令查看索引
    - 方式一：
    - 方式二:

常见索引概念

1. 索引的声明与使用

1.1索引的分类

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

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

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

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

4. 单列索引
在表中的单个字段上创建索引，单列索引只根据字段进行索引。单例索引可以是普通索引，也可以是唯一索引，还可以是全文索引。只要保证该索引对应一个字段即可，一个表可以有多个单列索引
5. 多列(组合、联合)索引
多列索引是表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询多条件中使用了这些字段中的第一个字段才会被使用。例如，在表中的字段id、name 和 gender上建立多列索引idx_id_name_gender，只有查询条件中使用了id字段时该索引才会被使用，使用组合索引时遵循最左前缀集合。

6. 全文索引
略。ElasticSearch代替

7. 补充：空间索引
略。
小结：不同的存储引擎支持的索引类型也不一样
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 创建索引
MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句 CREATE TABLE中指定索引列，使用ALTER TABLE 语句在存在的表上创建索引，或者使用CREATE INDEX 语句在已经存在的表上添加索引。

1.2.1查看索引是否生效
```
EXPLAIN 查询语句
```
1. 创建表时创建索引
使用CREATE TABLE 创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建一个索引。
举例：
```
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.创建普通索引

在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)
);
-- 性能分析工具
EXPLAIN SELECT * FROM book WHERE book_name = "rap速成"

possible_keys：有可能会用到的索引
key：实际上用到的索引
2.创建唯一索引
```sql — 创建表时创建唯一索引

— 声明唯一索引的字段，在添加数据时，要保证数据唯一性，但是可以添加为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 un_idx_bname (comment) );

![image.png](https://cdn.nlark.com/yuque/0/2022/png/12426545/1655135026178-a7ac752f-12b3-4b33-b777-b68a48abd8bf.png#clientId=u2dc59695-5a21-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=426&id=uafdb79a9&margin=%5Bobject%20Object%5D&name=image.png&originHeight=852&originWidth=2100&originalType=binary&ratio=1&rotation=0&showTitle=false&size=30004&status=done&style=none&taskId=u4e3daed3-3179-4d8a-bf90-a593db7ad83&title=&width=1050)
- Non_unique：0代表false，代表是唯一索引
<a name="AOCNa"></a>
#### 3.创建主键索引
 设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：  
```sql
-- 通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR
);
-- 通过删除主键约束的方式来删除主键索引
ALTER TABLE book2 DROP PRIMARY KEY;

4.创建联合索引

CREATE TABLE book3(
book_id INT PRIMARY KEY,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
-- 声明联合索引
INDEX mu_bid_bname_info(book_id,book_name,info)
);

4.1最左前缀原则：

name
name age
name age bir

MySQL 引擎在查询为了更好利用索引在查询过程中会动态调整查询字段顺序以便使用索引
假若建立符合索引 name age bir

name bir age 能否利用索引能
name age bir 能否利用索引能
age bir 能否利用索引 不可以，不包含name
bir age name 能否利用索引能
age bir 能否利用索引 不可以，不包含name
name age 能否利用索引能
name bir 能否利用索引能
2. 在已经存在的表上创建索引
在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。
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表示向表中添加索引。 ```sql CREATE TABLE book4( book_id INT, book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR );

SHOW index FROM book4

— ALTER TABLE … AND … —普通索引 ALTER TABLE book4 ADD INDEX idx_cmt(comment)

— 唯一索引 ALTER TABLE book4 ADD UNIQUE un_idx_cmt(book_name)

— 联合索引 ALTER TABLE book4 ADD INDEX mul_bid_bname_info(book_id,book_name,info)

<a name="JQydl"></a>
#### 2. 使用CREATE INDEX 语句
创建一个index 添加到哪个表
```sql
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
-- 普通索引
CREATE INDEX idx_cmt ON book5(comment)
-- 唯一索引
CREATE UNIQUE INDEX un_idx_bname ON book5(book_name)
-- 联合索引
CREATE INDEX mul_bid_bname_info ON book5(book_id,book_name,info)

1.3 删除索引

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

1. 使用ALTER TABLE删除索引

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

ALTER TABLE 表名 DROP INDEX 索引名

提示：添加AUTO_INCREMENT约束字段的唯一索引不能被删除

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

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

DROP INDEX 索引名 ON 表名;

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

2. MySQL8.0索引新特性

略

3. 索引设计原则

为了使索引的使用率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引.索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效索引对于获的良好的性能非常重要。设计索引时，应该考虑相应的原则。

略

查看索引

通过命令查看索引

方式一：

SHOW CREATE TABLE book;

方式二:

SHOW INDEX FROM book;

Non_unique：非唯一约束，1就是True

为生活，添砖 Java

08_索引的创建和设计原则

常见索引概念

1. 索引的声明与使用

1.1索引的分类

1. 普通索引 NORMAL

2. 唯一索引 UNIQUE

3. 主键索引

4. 单列索引

6. 全文索引

7. 补充：空间索引

1.2 创建索引

1.2.1查看索引是否生效

1. 创建表时创建索引