MySQL索引详解

快捷查看指令 ctrl+f 进行搜索会直接定位到需要的知识点和命令讲解（如有不正确的地方欢迎各位小伙伴在评论区提意见，博主会及时修改）

MySQL索引

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

MySQL索引分类及用途

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

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

创建索引

普通索引(INDEX)

-- 在创建表时指定
mysql>create table t_stu1(
	id int not null,
	name varchar(100) not null,
	birthday date,
	sex char(1) not null,
	index nameindex (name(50))
);
Query OK, 0 rows affected (0.02 sec)

-- 基于表结构创建
mysql>create table t_stu1(
    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 t_stu1(name(50));

-- 修改表结构创建
mysql>create table t_stu1(
    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 t_stu1 add index nameindex(name(50));
Query OK, 0 rows affected (0.02 sec)

mysql> show index from t_stu1;    -- 查看某个表格中的索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

唯一索引(UNIQUE)

-- 在创建表时指定
mysql>create table t_stu2(
	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 t_stu2(
	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 t_stu2(id);

-- 修改表结构创建
mysql>create table t_stu2(
	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>alter table t_stu2 add unique index idindex(id);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

主键索引(PRIMARY KEY)

-- 创建表时指定
mysql>create table t_stu3(
	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>alter table t_stu3 add primary key (id);
Query OK, 0 rows affected (0.01 sec)

-- ##	主键索引不能使用基于表结构创建的方式创建。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

删除索引

普通索引(INDEX)

-- 直接删除
mysql>drop index nameindex on t_stu1;

-- 修改表结构删除
mysql>alter table t_stu1 drop index idindex;
1
2
3
4
5

唯一索引(UNIQUE)

-- 直接删除
mysql>drop index nameindex on t_stu2;

-- 修改表结构删除
mysql>alter table t_stu2 drop index idindex;
1
2
3
4
5

主键索引(PRIMARY KEY)

-- 修改表结构删除
mysql>alter table t_stu3 drop primary key;
-- ##	主键不能采用直接删除的方式删除。
1
2
3

查看索引

mysql>show index from t_stu1;
1

选择索引的原则

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

导致SQL执行慢的原因：
1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2.没有索引或者索引失效.
3.数据过多（分库分表、读写分离）
4.服务器调优及各个参数设置（调整my.cnf）
1
2
3
4
5
6
7
8
9
10
11
12
13