MySQL 下索引的分类(一)｜学习笔记

开发者学堂课程【MySQL 实操课程：MySQL 下索引的分类(一)】学习笔记，与课程紧密联系，让用户快速学习知识。  

课程地址：https://developer.aliyun.com/learning/course/717/detail/12821

MySQL 下索引的分类（一）

 

内容简介：

一、 索引的分类

二、 普通索引

三、 唯一索引

 

一、索引的分类

两种分类的维度：

1、按索引字段个数分类

(1)单列索引：即一个索引只包含单个列(就是一个字段)，一个表可以有多个单列索引，但这不是组合索引。

注意：不要以为有多个单列就是组合索引，其实它是多个单列索引。多个列之间是没有关系的，每个列中有一个单独的索引。

(2)组合索引：即一个索包含多个列,最左前缀法则 abc 建立索引相当于 a,ab,abc 建立索引，被称为组合索引，也叫复合索引。

2、按类型分类

(1)普通索引

(2)唯一索引

(3)主键索引

(4)全文索引

下面进行展开详细的讲解。

 

二、普通索引

1、概念

在 where , order by , group by 中经常使用的字段，创建索引，可以加快查询速度。

举例如上图:

在类型中，图中红色箭头指到的类型就是一个索引，没有包含其他关键字，就是一个 INDEX 关键字，这个关键字就是一个普通的索引，那么像这种普通的索引，它是没有一些其他要求的，只需要给索引指定索引名称和索引对应的关联字段，对索引的字段没有别的要求，既不要求字段的值在表中是唯一的，也不要求一定是主键，一般在创建索引时，会在 where , order by , group by 中经常使用的字段进行创建索引，可以加快查询速度。

2、explain 语句进行索引查询

explain 语句，可以查看 sql 查询情况，是否使用了索引。

（1）概况：创建索引的过程中，索引是否被运用是需要考虑的问题，可以使用explain 语句，可以查看sql查询情况，是否使用了索引;  explain 实际上是一个查看MySQL 中的 sql 语句执行情况的命令。（2）下面进行简单的演示：

①复制公网 IP

首先，打开 RDS 管理控制台，然后，回到云服务器管理控制台复制公网 IP

②进行数据库连接

紧接着，通过公网 IP 在 Cloud Shell中，输入 ssh root@47.112.159.55 运行，并输入密码，得到如下:

shell@Alicloud：~ssh root@47. 112.159.55

root@112.159.55’s password:

last login：Thu Aug…

在这里面之前有用到，使用命令访问 mysql 中 bin 下面的 mysql，这里直接连接本地安装的 (-uroot), 就不需要输入 -h；输入密码，如：

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usx/local/mysql/bin/mysql -uroot -p

Enter password:

Welcome to the MysQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.31 MySQL Comeunity Server (GPL)

Copyright (C) 2000， 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. other names ay be trademarks of their respect ive

Owners.

Type "help;' or '\h' for help. Type '\c' to clear the Current input statement .

之前在这里有一个叫 aliyun 的测试数据库，执行 use aliyun; 命令得到以下:

mysql> use aliyun;

Reading table information for completion of table and column names

You Can turn off this feature to get a quicker startup with -A

Database changed

使用 show tables; 命令显示出里面的表，如:

mysql> shom tables;

Tables_in_aliyun 1

dept

emp

user

user_bak

4 rows in set (0.00 sec)

③不加索引的情形

为了不影响之前的，可以在这里创建字段或表，使用 create table school(id int,name varchar(20)); 命令创建一个有 id 和 name 的 school 表，这是不加索引的情形，如:

mysql> create table school(id int , naime varchar (20));

Query OK, 0 rows affected (0.01 sec)

使用 insert into school values(1001,’yizhong’);命令插入 id 为1001，name 为yizhong 的数据，如：

mysql> insert into school values (1001, 'yizhong');

Query OK, 1 row affected (0. 00 sec)

使用 select * from school; 命令进行查询，得出一条数据，如：

mysql> select . from school;

id        name

1001    yizhong

row     in set (0.00 sec)

使用 insert into school values(1002,’erzhong’);和 insert into school values(1001,’sanzhong’);命令插入两条数据，再使用 select * from school; 命令进行查询，得到三条数据，如：

mysql> insert into school values (1002, 'erzhong');

Query OK, 1 row affected (0.00 sec)

mysql> insert into school values (1003, 'sanzhong');

Query OK, 1 row affected (0.00 sec)

mysql> select from school;

id     name

1001  yi zhong

1002  erzhongI

1003  sanzhong

3 CONS in set (0.00 sec)

使用explain select * from school where id=1001; 命令查看是否运用到索引，如：

mysql> explain select * from school where id=1001;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table  | partitions | type | possible keys | key…

|   1| SIMPLE     |school | NULL    |ALL  |NULL        |NUL…

+----+------------+-------+-----------+-----+--------------+---…

1 row in set, 1 warning (0.00 sec)

这里打印出来的信息，select_type 为 SIMPLE，代表它是一个单表；table 是指定查询的表名; partitions为NULL,代表分区是没有的；type 为 ALL, 这个 ALL 就说明它没有运用索引，代表的是全表扫描，就是在查询 id=1001 这条数据的时候，它做了一个全表扫描的动作，所以它没有运用索引；rows 代表当前扫描的行数。

④加索引的情形

使用 create table school_index(id int,name varchar(20),index

(id)); 命令创建一个以 id 为索引的表，如：

mysql> create table school index (id int Dame varchar (20)，index (id));

Query OK, 0 EONS affected 0.02 sec)

然后进行查看信息，可以看到表名为 school_index，里面有一个索引，如：

mysql> show indexes from school_index;

使用 insert into school_index select * from school; 命令把 school 表中的三条数据插入到 school_index 表中，如：

mysql> insert into school index select . from school;

再进行查看数据，如：

mysql> select * from school_index;

|  id | name        |

|1001 | yizhong      |

|1002 | erzhong      |

|1003 | sanzhong      |

进来之后，再使用命令进行查询，如：

mysql> select * from school_index where id=1001;

|  id | name        |

|1001 | yizhong      |

使用命令查询 id 为1001的这条数据，如：

mysql> explain select * from school_index where id=1001;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ref  |id        |id…

+----+------------+-------+-----------+-----+--------------+---…

打印信息中前面都是和之前一样的，但是 type 变为了 ref，而不是 ALL,代表它没有进行全表扫描；rows为1，扫描的行数变成了1；所以像这个 sql 语句的执行过程中，就执行了全表审核的功能。

3、like '%test%’，不使用索引

4、like 'test%'，使用索引

5、是否运用索引（演示）

使用命令来演示是否运用索引，得出的结果为 type 是 ALL,rows 是3，没有运用索引，如：

mysql> explain select * from school_index where id like ’%1001%’;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ALL  | NULL        | NU…

+----+------------+-------+-----------+-----+--------------+---…

再进行查看1001，得出的结果也都表示没有运用索引：

mysql> explain select * from school_index where id like ’1001%’;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ALL  | NULL        | NU…

+----+------------+-------+-----------+-----+--------------+---…

mysql> explain select * from school_index where id like ’1001’;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ALL  | NULL        | NU…

+----+------------+-------+-----------+-----+--------------+---…

注意：在使用时，一定要注意用法。如果这个 like 是整型的情况下，这里用的是1001，就没有运用索引，这就是关于 id 的用法。因为id是整型的，所以在用的时候要注意了，它只是会在按照 id=1001的方式做等值查询的时候运用索引，其他方式都不会运用索引。

在 name 上创建索引，现在表已经创建好了，还需要使用命令在 school_index 表中为 name 创建索引，如：

mysql> create index idx_name on school_index(name);

再执行 name 的查询，得出的结果说明运用了索引，如：

mysql> explain select * from school_index where name=’yizhon

g’;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ref  | idx_name    | 23…

+----+------------+-------+-----------+-----+--------------+---…

再修改命令运行，得出的结果说明没有运用索引，如：

mysql> explain select * from school_index where name like '%yizhong%';

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ALL  | NULL       | NUL…

+----+------------+-------+-----------+-----+--------------+---…

但是把命令修改为 explain select * from school_index where name like 'yizhong%'; 运行，得出的结果说明运用了索引，如：

mysql> explain select * from school_index where name like 'yizhong%';

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL |range  |idx_name    | 23…

+----+------------+-------+-----------+-----+--------------+---…

type 为 range, 是一个范围的索引，和前面的整型是不一样的，整型的 type 是ref，和 range 的等值是一样的，而这里是 range 并且是百分百的。

 

三、唯一索引

1、unique，不允许重复，可以为空值

比如在创建值的时候，使用 select * from school_index; 命令进行查询，得出的结果,可以看出没有设置主键，也没有设置唯一键，如：

mysql> select * from school_index;

id    name

1001 yizhong

1002 erzhong

1003 sanzhong

然后插入一条数据，再使用命令进行查询，得出的结果说明插入数据没问题，如：

mysql> insert into school_index values(1001,’sizhong’);

mysql> select * from school_index;

id     name

1001 yizhong

1002 erzhong

1003 sanzhong

1001 sizhong

紧接着进行查询 id=1001的数据，得出的结果为 type 是 ref，rows 是2；所以它运用了索引，会把数据扫描出来，而这是一个普通索引，没有任何限制，如：

mysql> explain select * from school_index where id=1001;

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE  |school_index | NULL   |ref  |id            | id…

+----+------------+-------+-----------+-----+--------------+---…

而现在在这个表中进行打印表，如：

mysql> show create table school;

可以发现没有 index 这个表，所以可以去创建一个有唯一索引的表，再使用命令打印表，打印信息为 Key_name 为 idx_id，Column_name 为 id，可以看到已经有一个索引了，这就是唯一索引，如：

mysql> create unique index idx_id on school(id);

mysql> show indexes from school;

|  Table    | Non_unique | Key_name | Seq…  | Sub part…

| school    |           0 | idx_id    |      1… |    NULL…

然后查询表中的数据，再进行插入数据，就会提示报错，如：

mysql> select * from school；

mysql> insert into school values(1001,’sizhong’);

(ERROR 1062 (23000):Duplicate entry '1001' for key 'idx_id')

因为这里的 'idx_id' 是前面创建的唯一索引，那么这个唯一索引是不能给它赋重复的值，必须给它赋不同的值，创建唯一索引时，一定要注意字段的值是不是唯一的。

对于查看是否运用了索引可以再次执行查询1001数据命令，如：

mysql> explain select * from school where id=1001；

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE   |  school   | NULL   |const |idx_id        | idx…

+----+------------+-------+-----------+-----+--------------+---…

如上得出的结果为 type 值为 const，并且这里 rows 是1(扫描的是一行),可以看出运用了索引，因为这里使用的是唯一索引，与之前的普通索引的 type 是不一样的；因为在这里面它扫描出一行就不用再扫描了，这里本来就是唯一的，只有一条数据，所以唯一索引的效率更高。

2、可以对多个字段设置成唯一性索引

也可以创建多个字段去指定也行，比如把 email 和 username 作为一个唯一索引。先进行删除索引，再使用命令创建一个唯一索引。这个时候再来查看 school 表，如：

mysql> drop index idx_id on school;

mysql> create unique index idx_id_name on school(id,name);

mysql> show index from school；

|  Table    | Non_unique | Key_name | Seq…  | Sub part…

| school    |           0 | idx_id_name |      1… |    NULL…

| school    |           0 | idx_id_name |      2… |    NULL…

从上面查询结果可以看到它创建了类似于两个字段的索引，其实它不是两个索引，这里的 Key_name 都是一样的，并且指定的一个是1，一个是2；Column_name 是id 和 name。这就是唯一索引的概念。就是可以通过多列设置这样的唯一索引。

在使用唯一索引时会遇到的问题：在做查询的时候，是否任何一个字段作为查询条件，都可以走索引，如：

mysql> explain select * from school where id=1001；

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possible_keys | key…

|  1|SIMPLE   |  school   | NULL   |ref  |idx_id_name| idx_id…

+----+------------+-------+-----------+-----+--------------+---…

运行如上得出的结果显示是走了索引的。

再分别使用 like 命令进行查询，如：

mysql>explain select * from school where name like '%yizhong%';

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possibl… | rows…

|  1|SIMPLE  |  school   | NULL   |index |NULL …   | 3…

+----+------------+-------+-----------+-----+--------------+---…

mysql> explain select * from school where name like 'yizhong%';

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possibl… | rows…

|  1|SIMPLE  |  school   | NULL   |index |NULL …   | 3…

+----+------------+-------+-----------+-----+--------------+---…

mysql> explain select * from school where name= 'yizhong';

+----+------------+-------+-----------+-----+--------------+---…

|  id | select_type | table | partitions | type | possibl… | rows…

|  1|SIMPLE  |  school   | NULL   |index |NULL …   | 3…

+----+------------+-------+-----------+-----+--------------+---…

如上命令做的等值查询，但得出的结果显示都是扫描了三行，都没有走索引。但实际上 name 字段是加了索引的，所以这就反映了一个侧面的问题，在创建唯一索引的并涉及到多个列时，它只会以最左边的这一列作为索引的条件。