五、MySQL 索引

 

索引作为一种数据结构，其用途是用于提升检索数据的效率。

1、MySQL 索引的分类

- 普通索引（INDEX）：索引列值可重复
- 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL
- 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
- 全文索引（FULL TEXT）：给每个字段创建索引

2、MySQL 不同类型索引用途和区别

- 普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。
- 唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。
- 主键索引是行的唯一标识，因而其主要用途是检索特定数据。
- 全文索引效率低，常用于文本中内容的检索。

3、MySQL 使用索引

1、创建索引

1、普通索引（INDEX）

# 在创建表时指定
mysql> create table student1(id int not null, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)));
Query OK, 0 rows affected (0.02 sec)
​
​
# 基于表结构创建
mysql> create table student2(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
​
mysql> create index nameindex on student2(name(50));
​
# 修改表结构创建
mysql> create table student3(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
​
mysql> ALTER TABLE student3 ADD INDEX nameIndex(name(50));
​
mysql> show index from student3;   //查看某个表格中的索引

2、唯一索引（UNIQUE）

# 在创建表时指定
mysql> create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id));
Query OK, 0 rows affected (0.00 sec)
​
# 基于表结构创建
mysql> create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.00 sec)
​
mysql> CREATE unique INDEX idIndex ON student5(id);

3、主键索引（PRIMARY KEY）

# 创建表时时指定
mysql> create table student6(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, primary key (id));
Query OK, 0 rows affected (0.01 sec)
​
# 修改表结构创建
mysql> create table student7(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
​
mysql> ALTER TABLE student7 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引不能使用基于表结构创建的方式创建。

2、删除索引

1、普通索引（INDEX）

# 直接删除
mysql> DROP INDEX nameIndex ON student1;
​
# 修改表结构删除
mysql> ALTER TABLE student2 DROP INDEX nameIndex;

2、唯一索引（UNIQUE）

# 直接删除
mysql> DROP INDEX idIndex ON student4;
​
# 修改表结构删除
mysql> ALTER TABLE student DROP INDEX idIndex;

3、主键索引（PRIMARY KEY）

mysql> ALTER TABLE student DROP PRIMARY KEY;

主键不能采用直接删除的方式删除。

3、查看索引

mysql> SHOW INDEX FROM tab_name;

4、选择索引的原则

- 常用于查询条件的字段较适合作为索引，例如WHERE语句和JOIN语句中出现的列
- 唯一性太差的字段不适合作为索引，例如性别
- 更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引
​
- 使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系，可以通过索引直接获取对应的行数据，而无需进行全表搜索，因而加快了检索速度
- 但由于索引也是一种数据结构，它需要占据额外的内存空间，并且读取索引也加会大IO资源的消耗，因而索引并非越多越好，且对过小的表也没有添加索引的必要

index(key)每张表可以有很多列做index，必须的起名
​
面试题:
导致SQL执行慢的原因：
1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2.没有索引或者索引失效.
3.数据过多（分库分表）
4.服务器调优及各个参数设置（调整my.cnf）
索引：当查询速度过慢可以通过建立优化查询速度，可以当作调优