MySQL-索引详解（二）

痛定思痛。 2023-10-12 13:06 13阅读 0赞

> ♥️**作者：小刘在C站**
> 
> ♥️**个人主页：[小刘主页][Link 1]**
> 
> ♥️**每天分享云计算网络运维课堂笔记，努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！**
> 
> ♥️**树高千尺，落叶归根人生不易，人间真情**

**目录**

3 索引分类

3.1 索引分类

3.2 聚集索引&二级索引

聚集索引选取规则:

具体过程如下:

4 索引语法

1). 创建索引

2). 查看索引

3). 删除索引

--------------------

### **3** **索引分类**  ###

### **3.1** **索引分类**  ###

在 MySQL 数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

![76b5a9cc166a415bb51da8e9df4495b1.png][]

### **3.2** **聚集索引****&****二级索引**  ###

而在在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

![32d7a909f85a4dfe96769550ba4f408d.png][]

### 聚集索引选取规则:  ###

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索

引。

聚集索引和二级索引的具体结构如下：

![5271c2645c084d228bd34b545e66b0c2.png][]

聚集索引的叶子节点下挂的是这一行的数据 。

二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的 SQL 语句时，具体的查找过程是什么样子的。

![952576711a7c4164a2b1dcf0640f5a11.png][]

### 具体过程如下:  ###

① .  由于是根据 name 字段进行查询，所以先根据 name='Arm' 到 name 字段的二级索引中进行匹配查

找。但是在二级索引中只能查找到  Arm  对应的主键值  10 。

② .  由于查询返回的数据是 \* ，所以此时，还需要根据主键值 10 ，到聚集索引中查找 10 对应的记录，最

终找到 10 对应的行 row 。

③. 最终拿到这一行的数据，直接返回即可。

> 回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 
> 
> 数据的方式，就称之为回表查询

## **4** **索引语法** ##

### 1). 创建索引  ###

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
    index_col_name,... ) ;

### 2). 查看索引  ###

SHOW INDEX FROM table_name ;

### 3). 删除索引 ###

###  ###

DROP INDEX index_name ON table_name ;

**案例演示** **:**

先来创建一张表  tb\_user ，并且查询测试数据。

create table tb_user(
    id int primary key auto_increment comment '主键',
    name varchar(50) not null comment '用户名',
    phone varchar(11) not null comment '手机号',
    email varchar(100) comment '邮箱',
    profession varchar(11) comment '专业',
    age tinyint unsigned comment '年龄',
    gender char(1) comment '性别 , 1: 男, 2: 女',
    status char(1) comment '状态',
    createtime datetime comment '创建时间'
    ) comment '系统用户表';
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
    '6', '2001-02-02 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
    '1', '0', '2001-03-05 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
    '2', '2002-03-02 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
    '1', '0', '2001-07-02 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
    '2', '1', '2001-04-22 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
    '0', '2001-02-07 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
    '2', '0', '2001-02-08 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
    '1', '5', '2001-05-23 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
    '1', '0', '2001-09-18 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
    化', 27, '1', '2', '2001-08-16 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
    程', 27, '1', '0', '2001-06-12 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
    '0', '2001-05-11 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
    44, '1', '1', '2001-04-09 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
    '1', '2', '2001-04-10 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
    '2', '3', '2001-02-12 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
    '2', '0', '2001-01-30 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
    '2', '0', '2000-05-03 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
    '1', '2001-08-08 00:00:00');
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
    createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
    30, '1', '0', '2007-03-12 00:00:00');

表结构中插入的数据如下：

![16919b0782e74838a400c3757a104265.png][]

数据准备好了之后，接下来，我们就来完成如下需求：

A. name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

CREATE  INDEX idx\_user\_name  ON  tb\_user(name);

B. phone 手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE  UNIQUE INDEX idx\_user\_phone  ON  tb\_user(phone);

C.  为 profession 、 age 、 status 创建联合索引。

CREATE  INDEX idx\_user\_pro\_age\_sta  ON  tb\_user(profession,age,status);

D.  为 email 建立合适的索引来提升查询效率

CREATE  INDEX idx\_email  ON  tb\_user(email);

完成上述的需求之后，我们再查看 tb\_user 表的所有的索引数据。

> ♥️关注，就是我创作的动力
> 
> ♥️点赞，就是对我最大的认可
> 
> ♥️这里是小刘，励志用心做好每一篇文章，谢谢大家

[Link 1]: https://blog.csdn.net/lzl10211345?type=blog
[76b5a9cc166a415bb51da8e9df4495b1.png]: https://img-blog.csdnimg.cn/76b5a9cc166a415bb51da8e9df4495b1.png
[32d7a909f85a4dfe96769550ba4f408d.png]: https://img-blog.csdnimg.cn/32d7a909f85a4dfe96769550ba4f408d.png
[5271c2645c084d228bd34b545e66b0c2.png]: https://img-blog.csdnimg.cn/5271c2645c084d228bd34b545e66b0c2.png
[952576711a7c4164a2b1dcf0640f5a11.png]: https://img-blog.csdnimg.cn/952576711a7c4164a2b1dcf0640f5a11.png
[16919b0782e74838a400c3757a104265.png]: https://img-blog.csdnimg.cn/16919b0782e74838a400c3757a104265.png