【mysql】—— 深度理解“索引”

本期，我将要给大家介绍的是关于 mysql 中的“索引”，这部分内容在mysql知识体系中可谓是相当重要的，接下来，让我带领大家进入本期的知识学习！！！

目录

（一）前言

?（二）索引的理解

1、硬件角度理解索引

1.1、MySQL 与磁盘交互基本单位

1.2、简单小结

2、软件角度理解索引

2.1、案例演示

2.2、为何IO交互要是 Page？

2.3、单个page和多个page?

（三）聚簇索引 VS 非聚簇索引

（四）索引操作

1、主键索引

2、唯一索引

3、普通索引

4、全文索引

（五）总结与归纳

（一）前言

首先，我先给大家直观的展示一下在查询的时候，看看没有索引时有什么问题？

• 开始时?构建了一个8000000条记录的数据 海量表?。接下来，我将通过这张表给大家直观的演示：

💨?需求：查询员工编号为998877的员工

• ?1?? 首先，我先用传统的方式进行查询操作：

select * from EMP where empno=998877;

• 输出展示：

• 解释说明：

• 2?? 接下来我通过创建索引可以很好的解决这一问题：

alter table EMP add index(empno);

• 输出展示：

• 为了防止事件的偶然性，接下来我换一个员工编号，在测试看看查询时间：

【小结】

• 通过以上直观的演示，我们可以发现有无索引在实际应用中的效率截然不同。因此，掌握好“索引”这个知识对我们学习mysql还是很重要的！！

?（二）索引的理解

接下来，我分别从硬件和OS两个角度带大家认识索引的底层细节。

1、硬件角度理解索引

💨? 首先带研究一下磁盘：

在看看磁盘中一个盘片：

• 数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

2.2、为何IO交互要是 Page？

现在有一个问题：为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少?

1. 如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
2. 但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的?Buffer Pool?中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。
3. 无法保证用户一定下次找的数据就在这个Page里面，我们不能严格保证，但是有很大概率，因为有局部性原理。
4. 往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

2.3、单个page和多个page?

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

1. 插入数据时排序的目的，就是优化查询的效率。
2. 页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
3. 正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的

💨理解多个Page ：

• 通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

• 答案很简单，可以很方便引入目录

MyISAM 存储引擎-主键索引

• 其中， MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

• 其中， InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

?下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别 ：

（四）索引操作

1、主键索引

1.1创建主键索引

• 💨第一种方式：

-- 在创建表的时候，直接在字段名后指定 primary key
create table test1(
    id int primary key,
    name varchar(30)
);

• 💨第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table test2(
    id int, 
    name varchar(30), 
    primary key(id)
);

• 💨第三种方式：

create table test3(
    id int, 
    name varchar(30)
);

-- 创建表以后再添加主键
alter table test3 add primary key(id);

1.2查询索引

• 第一种方法： show keys from 表名 ；
• 第二种方法: show index from 表名;
• 第三种方法（信息比较简略）： desc 表名；

1.3删除索引

• 方法-删除主键索引： alter table 表名 drop primary key;

• 一个表中，最多有一个主键索引，当然可以使符合主键

• 主键索引的效率高（主键不可重复）

• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int

2、唯一索引

1.1创建主键索引

• 💨第一种方式：

-- 在表定义时，在某列后直接指定unique唯一属性。
create table test3(
    id int primary key, 
    name varchar(30) unique
);

• 💨第二种方式：

-- 创建表时，在表的后面指定某列或某几列为unique
create table test3(
    id int primary key, 
    name varchar(30), 
    unique(name)
);

• 💨第三种方式：

create table test3(
    id int primary key, 
    name varchar(30)
）；

alter table test3 add unique(name);

1.2查询索引（方法同上）

1.3删除索引

• 第二种方法-其他索引的删除： alter table 表名 drop index 索引名（索引名就是show keys from 表名中的 Key_name 字段）；

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

3、普通索引

1.1创建主键索引

• 💨第一种方式：

create table test4(
    id int primary key,
    name varchar(20),
    email varchar(30),
    index(name)  --在表的定义最后，指定某列为索引
);

• 💨第一种方式：

create table test4(
    id int primary key, 
    name varchar(20), 
    email varchar(30)
);

alter table test4 add index(name); --创建完表以后指定某列为普通索引

• 💨第一种方式：

create table test4(
    id int primary key, 
    name varchar(20), 
    email varchar(30)
);

-- 创建一个索引名为 idx_name 的索引
create index idx_name on test4(name);

1.2查询索引（方法同上）?

1.3删除索引

• 第三种方法方法： drop index 索引名 on 表名

drop index name on test4;

• ?除了可以对一列进行索引之外，还可以对多列进行索引操作：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

4、全文索引

• 接下来，我先创建一个表并向其中插入一些数据，具体如下：

需求：查询有没有database数据

• 如果使用如下查询方式，虽然查询出数据，但是没有使用到全文索引

select * from articles where body like '%database%';

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

（五）总结与归纳

接下来，我们简单的回顾下本文学到的知识内容！！！

• 索引是一种数据结构，它能够快速定位到数据表中的特定行，类似于书籍的目录，使得数据库系统不必扫描整个表，而是能够更快地找到所需数据；
• 在MySQL中，常见的索引类型包括B+树索引、哈希索引、全文索引等，其中B+树索引是最常用和最常见的索引类型；
• 通过使用索引，数据库可以快速定位到需要查询的数据，加速查询速度，特别是在大型数据集的情况下效果显著；不仅能加速查询操作，还能加速排序和数据检索操作，提高数据库的整体性能。

以上便是本文的全部内容了，感谢大家的观看和支持！！！