MySQL索引

最新推荐文章于 2024-06-22 15:26:37 发布

scj1022

最新推荐文章于 2024-06-22 15:26:37 发布

阅读量2.5k

点赞数 38

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/scj1022/article/details/136738198

版权

MySQL 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

文章目录

MySQL索引
一、磁盘 IO
二、索引的数据结构
三、聚簇索引 & 非聚簇索引
- 1、非聚簇索引（MyISAM）
- 2、聚簇索引（InnoDB）
四、唯一索引 vs 普通索引
五、组合索引
六、字符串 - 前缀索引
七、回表查询 & 覆盖索引
- 1、回表查询
- 2、使用覆盖索引
八、索引失效的情况
九、索引的设计与使用 ⭐️

MySQL索引

一、磁盘 IO

1、磁盘IO

每次从磁盘中查找数据称为磁盘IO。磁盘IO至少要经历磁盘寻道、磁盘旋转、数据读取等操作，非常影响性能。

所以对于读取数据，最大的优化，就是减少磁盘I/O。

在这里插入图片描述

磁道（Track）：磁盘旋转时，磁头在磁盘表面划出的每一个圆形轨迹。
扇区（Sector）：磁盘上的每个磁道被等分为若干个弧段，这些弧段称之为扇区。
磁盘块（Block）：相邻的扇区组合在一起，形成一个磁盘块。
页（Page）：一个页通常对应于一个或多个磁盘块。数据库系统通过磁盘块来存储和管理页上的数据。

小结一下：

扇区：磁盘中最小的物理存储单元。
磁盘块：物理存储介质（磁盘）上的数据组织单位。
页：数据库系统中逻辑上的数据组织单位。

数据库系统通过磁盘块来管理页的存储和访问，以实现数据的持久化存储和高效访问。

2、磁盘读取的特点

系统从磁盘读取数据到内存时，是以磁盘块 block 为基本单位的，位于同一磁盘块的数据都会被读取出来。

在这里插入图片描述

如上图所示，如果要查询数字5：

系统找到磁盘块2之后，会将整个磁盘块2的数据都读取出来，而不是只读取一个5。
读取整个磁盘块2的数据（4,5,6）之后，再根据条件过滤保留5。

3、数据库IO的特点

InnoDB引擎 将数据划分为若干个 页page ，每个页的默认大小为16KB。

记录是按照行来存储的，一个页中可以存储多个行记录。
InnoDB引擎将若干个地址链接磁盘块，以达到页的大小16KB。
查询数据时，一个页中的每条数据都有助于定位数据的位置，这将会减少磁盘IO次数，提高查询效率。

数据库管理存储空间的基本单位是页（Page），数据库I/O操作的最小单位是页。

不论读一行，还是读多行，都是将这些行所在的页进行加载。
也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

二、索引的数据结构

1、哈希表

用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

假设现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引如下：

在这里插入图片描述

优点：更新数据速度很快，只需要往后追加。
缺点：因为不是有序的，所以哈希索引做区间查询的速度是很慢的。
应用：适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。

2、有序数组

要查ID_card_n2对应的名字，用二分法就可以快速得到，时间复杂度是O(log(N))。

要查身份证在[nx, ny]区间的User，可以先用二分法找到 ID_card_nx（如果不存在ID_card_nx，就找到大于ID_card_nx的第一个User），然后向右遍历，直到查到第一个大于ID_card_ny的身份证号，退出循环。

在这里插入图片描述

优点：在 等值查询 和 范围查询 场景中的性能都非常优秀。
缺点：更新数据的时候比较麻烦，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
应用：静态数据存储，比如2020年某个城市的所有人口信息，这类不会再修改的数据。

3、搜索树

二叉搜索树的特点：每个节点的左儿子小于父节点，父节点又小于右儿子。时间复杂度是O(log(N))
多叉搜索树的特点：每个节点有多个儿子，儿子之间的大小保证从左到右递增。

在这里插入图片描述

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。对于一个100万行的表，单独访问一行可能需要 20×10 ms的时间。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。即使用多叉树，减少树高。

4、BTree

注意：这里的 BTree 是数据结构，和索引类型 BTree 是有区别的。

名词	含义
节点	指树中的一个元素（图中的一个框）
节点的度	节点拥有的子树的个数，二叉树的度不大于2
叶子节点	度为0的节点，也称之为终端结点
高度	叶子结点的高度为1，叶子结点的父节点高度为2，以此类推，根节点的高度最高

BTree又叫自平衡多叉查找树

在这里插入图片描述

1. 每个节点的组成：
		1）索引：索引值(如id)
        2）数据：索引值对应的data
        3）指针：该层没有指定索引，通过指针到下一层寻找

2. 每个节点的大小：
		索引大小（如5B） + 数据大小（如95B）

3. 每个节点的大小设置为一个页的大小
		这样每个节点只需要一次IO就可以完全载入。

4. 度比较大，每个节点可以存储多个索引值和数据
		度越大，树的高度越低，磁盘IO的次数就越少
		度 = 页的大小/(索引大小+数据大小)

5. 叶子节点具有相同的深度，叶节点的指针为空
		查询效率比较稳定

BTree和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，层级结构小，因此搜索速度快。

5、B+Tree

InnoDB 和 MyISAM 存储引擎默认的索引类型是BTree，其底层的数据结构就是 B+Tree （BTree的变种）

B+Tree 是 BTree索引 的数据结构，索引类型是没有B+Tree类型的。
B+Tree 通常用于数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。

在这里插入图片描述

B+Tree和BTree的区别

非叶子节点不存储data，只存储键值信息（索引+指针），可以增大度（子树数目），减少树的高度。
数据data都存放在叶子节点中，叶子节点不存储指针。
叶子节点之间通过链表指针/双向指针相连。（方便范围查询）

由于 B+Tree只有叶子节点保存data，因此查询任何key都要走到叶子节点。

6、不同存储引擎的索引数据类型

索引是在存储引擎中实现的，不同的存储引擎支持的索引也不一样，常见的有以下3类：

BTREE索引：InnoDB 和 MyISAM 存储引擎中默认的索引类型，底层数据结构为 B+Tree。
HASH索引：Memory 存储引擎中默认的索引类型，底层数据结构为 Hash。
R-tree索引：空间索引，MyISAM 存储引擎中一种特殊索引，主要用于地理空间数据类型，使用较少。

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	支持	支持	不支持

MySQL数据库 默认使用InnoDB引擎，因此我们平常所说的索引，都是指 B+Tree 结构的索引。

三、聚簇索引 & 非聚簇索引

在 B+Tree 索引模型中，每一个索引都对应着一棵B+树。

聚簇索引：索引和数据存储在一个文件中，索引文件本身又是数据文件。
非聚簇索引：索引文件和数据文件是分离的，数据文件需要通过索引文件查询。

1、非聚簇索引（MyISAM）

MyISAM 索引文件和数据文件是分离的（非聚簇索引）

索引文件：数据结构是 B+Tree，叶子节点存储的是数据的磁盘地址。
数据文件：磁盘地址对应的数据data。

非主键索引和主键索引的结构类似。

在这里插入图片描述

2、聚簇索引（InnoDB）

InnoDB 索引文件本身就包含数据文件，叶子节点包含了完整的数据记录（聚簇索引）

主键索引：叶子节点存的是整行数据。（主键索引也被称为 聚簇索引 clustered index）
非主键索引：叶子节点存的是主键的值。（方便加行锁，但是查询可能会产生二次查找）
- 唯一索引字段生成的 B+Tree 只能拿到唯一索引字段和主键字段
- 如果要拿到其他字段的值，就需要根据主键字段再去查询主键索引生成的 B+Tree

在这里插入图片描述

在InnoDB中，表都是根据主键顺序，以索引的形式存放的，这种存储方式的表称为索引组织表。

默认情况下，使用主键索引字段来生成 B+Tree
如果没有id字段，就使用唯一索引字段来生成 B+Tree
唯一索引也没有，就生成一个隐藏字段row_id来生成 B+Tree

四、唯一索引 vs 普通索引

1、查询过程比较

# 执行查询的语句
select id from T where k=5

这个查询语句，先是通过B+Tree从树根开始，按层搜索到叶子节点，然后在数据页内部通过二分法来定位记录。

普通索引：查找到第一个满足条件的记录后，还需要查找下一个记录，直到碰到第一个不满足条件的记录。
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB的数据是以数据页为单位来读写的，所以查询一条记录时，它所在的数据页就都在内存里了。
对于普通索引来说，一般不会在整个数据页都找不到不满足条件的记录。

如果第一条满足条件的记录刚好是数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个会复杂一些。

但是，每个数据页的大小默认是16KB，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。

因此，可以认为 普通索引 和 唯一索引 在查询时的平均性能差异微乎其微。

2、change buffer 概述

在进行更新过程的比较之前，先了解一下 change buffer

1）带 change buffer 的更新

WAL 提升性能的核心机制，也的确是尽量减少随机读写，这里放到一个流程中说明：

# k1所在的数据页在内存中(InnoDB buffer pool)，k2所在的数据页不在内存中
insert into t(id,k) values (id1,k1), (id2,k2);

带 change buffer 的更新过程：

Page1 在内存中，直接更新内存。
Page2 不在内存中，就在内存的 change buffer 区域，记录下 “ 我要往Page 2插入一行 ” 这个信息
将上述两个动作记入 redo log 中（两次操作合在一起顺序写入）

做完上面这些，事务就可以完成了。因此，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

在这里插入图片描述

2）带 change buffer 的查询

带 change buffer 的查询过程：

读 Page1 的时候，直接从内存返回。
读 Page2 的时候，从磁盘读入内存中，然后应用 change buffer 里的变更，生成一个正确的版本并返回结果。

可以看到，直到需要读 Page2 的时候，这个数据页才会被读入内存。

在这里插入图片描述

3）change buffer 对比 redo log

如果要简单地对比这两个机制在提升更新性能上的收益的话：

change buffer：主要节省的是随机读磁盘的IO消耗（更新操作时）
redo log：主要节省的是随机写磁盘的IO消耗（转成顺序写）

4）merge

change buffer 中的变更被写入到磁盘上的过程称为merge。

merge的触发场景：

访问这个数据页时，会触发 merge
系统有后台线程，会定期 merge
数据库正常关闭（shutdown）的过程中，也会执行 merge。

merge的执行流程：

从磁盘读入数据页到内存（老版数据页）；
从change buffer找出这个数据页的修改记录（可能有多个），依次应用，得到新版数据页；
写 redo log（包含了 数据的变更 和 change buffer的变更）

3、更新过程比较

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新，不能使用 change buffer。也就是只有普通索引可以使用 change buffer。

如果要在这张表中插入一个新记录 (4, 400) 的话，InnoDB的处理流程是怎样的？

第一种情况是，这个记录要更新的目标页在内存中。

唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
普通索引：找到3和5之间的位置，插入这个值，语句执行结束。

这种情况，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。

第二种情况是，这个记录要更新的目标页不在内存中。

唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
普通索引：只需将更新记录在change buffer，语句执行就结束了。

这种情况，普通索引可以使用change buffer，减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

4、两种索引的选择

普通索引和唯一索引在查询性能上是没差别的，主要考虑的是对更新性能的影响。

如果要通过数据库索引来保证业务的正确性，那么没得选，必须创建唯一索引。
在一些 “归档库” 的场景，也可以考虑使用唯一索引。

其他场景尽量选择普通索引。普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

5、change buffer 小结

1）change buffer 的优点

将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。
数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

2）change buffer 的大小

change buffer用的是buffer pool里的内存，因此不能无限增大。
change buffer的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。

这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

3）使用场景

change buffer的主要目的是将记录的变更缓存下来，merge才是真正进行数据更新的时候。

所以在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。

对于读多写少的业务（不适合）

将更新先记录在change buffer，但由于马上要访问这个数据页，会立即触发merge过程。
这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

对于写多读少的业务（适合）

页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。

综上所述，

对于读多写少的业务，应该关闭change buffer。
对于写多读少的业务，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个表数据写入速度。

五、组合索引

组合索引：多个字段组合成一个索引（where条件中经常存在多个条件查询时，可以创建联合索引）

# 创建组合索引
CREATE INDEX idx_age_classid_name ON student(`age`, `classId`, `name`);

为什么不单独为这三个字段创建索引？

假设有100w的数据，每个索引可以筛选出10%的数据

分别创建单独的索引，MySQL只会选择辨识度高的一列作为索引，可以筛选出10w 的数据。
建立组合索引，筛选的数据就是 100w * 10% * 10% * 10% = 1000条。

1、最左匹配原则

最左边的列必须存在，否则组合索引失效（索引字段的顺序可以是任意的，MySQL优化器会自动调整）

# 索引生效（ken_len=5）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10;

# 索引生效（ken_len=5+5=10）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and classid = 100;

# 索引生效（ken_len=5+5+63=73）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and classid = 100 and name = 'Abel';

# 索引生效（索引字段的顺序可以是任意的，MySQL优化器会自动调整字段顺序）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 100 and name = 'Abel' and age = 10;

# 索引失效（缺少组合索引最左列age）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 100;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 'Abel';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 100 and name = 'Abel';

# 索引部分失效（ken_len=5，age生效了，name没有生效）
# name因为缺少组合索引中的左边列，所以失效了
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and name = 'Abel';

2、失效情况 - 存在模糊查询

存在模糊查询 —> 使用模糊查询的字段及其右侧的字段索引都失效

# 索引部分生效（ken_len=5，只有age生效了）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and name like '%A' and classid > 100;

# 索引全部生效（like不以通配符%开头就没事）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and name like 'A%' and classid > 100;

3、失效情况 - 存在范围查询

存在范围查询 —> 使用范围查询的字段索引生效，该字段右侧的字段索引失效

# 索引部分失效（ken_len=10，age和classid生效了，name没有生效）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and classid > 100 and name = 'Abel';

# 同上（调换字段顺序没有意义）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and name = 'Abel' classid > 100;

但是，如果把索引顺序调整一下，情况就不一样了

# 重建索引，调整顺序
DROP INDEX idx_age_classid_name ON student;
CREATE INDEX idx_age_classid_name ON student(`age`, `name`, `classId`);

# 索引生效（ken_len=5+5+63=73，三个字段都用上了索引！）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 and name = 'Abel' and classid > 100;

有人可能会发现，以下情况也没有用上索引。

# 索引失效
SELECT SQL_NO_CACHE * FROM student WHERE age > 10

因为 MySQL 在执行查询时，选择索引的方式是根据查询的条件以及索引的选择性（selectivity）来决定的。

索引的选择性是指索引列中具有不同值的比例。如果age列的选择性很低，即有很多重复的值，那么 MySQL 可能会认为扫描整个表可能更快，而不是使用索引。如果age列的选择性很高，即大部分行的age值都不同，那么 MySQL 可能会选择使用索引。

六、字符串 - 前缀索引

1、业务场景

有时候需要索引很长的字符列（例如email）

select * from `user` where email = 'zhangsan@xxx.com';

如果email没有索引，那么这个语句就只能做全表扫描，但是如果索引整个email，这会让索引变得大且慢。

Alibaba在《Java开发手册》中规定：

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

2、前缀索引

通常可以索引开始的部分字符（前缀索引），这样可以大大节约索引空间，从而提高索引效率。

# 默认地，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
alter table `user` add index index1(email);

# 前缀索引（指定前缀长度）
alter table `user` add index index2(email(6));

如果使用的是index1（即索引整个email字符串）
1. 从index1索引树找到索引值是 zhangsan@xxx.com 的记录，获取主键id；
2. 到主键索引树根据主键id找到对应行，将这行记录加入结果集；
3. 从index1索引树查找下一条记录，发现不满足 email = zhangsan@xxx.com 的条件，查询结束。
如果使用的是index2（即索引email字符串前6个字符）
1. 从index1索引树找到索引值是 zhangs 的记录，获取主键id；
2. 到主键索引树根据主键id找到对应行，判断是否满足 email = zhangsan@xxx.com
  - 不满足，将这行记录丢弃
  - 满足，将这行记录加入结果集；
3. 从index1索引树查找下一条记录 …

因此，使用前缀索引，区分度足够的情况下，就可以做到既节省空间，又不用额外增加太多的查询成本。

3、区分度计算

前缀索引截取的长度决定了区分度（区分度越高约好）

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，索引的选择性又太差了

可以使用以下公式计算以下区分度

count(distinct left(列名, 索引长度)) / count(*)

一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上

七、回表查询 & 覆盖索引

1、回表查询

回表查询：先在普通索引树定位主键索引值，然后回到主键索引树定位行记录。（要多扫描一棵索引树）

在这里插入图片描述

# 主键索引ID，普通索引k
select * from T where k between 3 and 5;

# 执行流程如下：
1. 在k索引树上找到k=3的记录，取得ID=300
2. 到ID索引树查到ID=300对应的R3

3. 在k索引树取下一个值k=5，取得ID=500
4. 再回到ID索引树查到ID=500对应的R4

5. 在k索引树取下一个值
6. k=6，不满足条件，循环结束。

查询过程：读取了k索引树的3条记录（步骤1、3、5），回表查询了两次（步骤2、4）。

主键索引只要扫描主键索引树，而 基于非主键索引的查询需要多扫描一棵索引树，因此应该尽量使用 主键查询 或 覆盖索引。

2、使用覆盖索引

覆盖索引：在一颗索引树上就能获取SQL所需的所有列数据，不需要回表查询。

【SQL示例】

# 主键索引id、非主键索引name
create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
) engine = innodb;

第一个SQL语句：

在这里插入图片描述

命中name索引，SQL所需的数据 id 和 name 都在 name索引树 上，无需回表，符合覆盖索引，效率高。

第二个SQL语句：

在这里插入图片描述

命中name索引，但不是所有字段都在name索引树上，sex字段必须回表查询才能获取到，不符合索引覆盖。

第三个SQL语句：

# 创建联合索引(name, sex)
alter table user add index idx_name_sex(name, sex);

在这里插入图片描述

命中name索引，而且所有字段都在 (name, sex) 联合索引树上，符合覆盖索引，效率高。

八、索引失效的情况

# 建表
CREATE TABLE `class` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`className` VARCHAR ( 30 ) DEFAULT NULL,
	`address` VARCHAR ( 40 ) DEFAULT NULL,
	`monitor` INT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

CREATE TABLE `student` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`stuno` INT NOT NULL,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`age` INT ( 3 ) DEFAULT NULL,
	`classId` INT ( 11 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ) 
	#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 允许创建函数设置
set global log_bin_trust_function_creators=1;

# 创建函数 - 随机产生字符串，保证每条数据都不同
DELIMITER //
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = CONCAT(
				return_str,
			SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;

END // 
DELIMITER;

# 假如要删除 
# drop function rand_string;

# 创建函数 - 用于随机产生多少到多少的编号 
DELIMITER //
CREATE FUNCTION rand_num ( from_num INT, to_num INT ) RETURNS INT ( 11 ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET i = FLOOR(
		from_num + RAND()*(
			to_num - from_num + 1 
		));
	RETURN i;

END // 
DELIMITER;

# 假如要删除 
# drop function rand_num;

# 创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu ( START INT, max_num INT ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0; #设置手动提交事务
	REPEAT #循环
		
		SET i = i + 1; #赋值
		INSERT INTO student ( stuno, `NAME`, age, classId )
		VALUES
			((
					START + i 
					),
				rand_string ( 6 ),
				rand_num ( 1, 50 ),
			rand_num ( 1, 1000 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT; #提交事务
	
END // 
DELIMITER;

# 假如要删除 
# drop PROCEDURE insert_stu;

# 创建往class表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE `insert_class` ( max_num INT ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			
			SET i = i + 1;
		INSERT INTO class ( classname, address, monitor )
		VALUES
			(
				rand_string ( 8 ),
				rand_string ( 10 ),
			rand_num ( 1, 100000 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;
	
END // 
DELIMITER;

# 假如要删除 
# drop PROCEDURE insert_class;

# 执行存储过程，往class表添加1万条数据
CALL insert_class(10000);
# 执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

# 创建索引
CREATE INDEX idx_name ON student(`name`);
CREATE INDEX idx_age ON student(age);

01、发生类型转换（自动/手动）

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';

# 发生类型转换 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = age;

02、进行计算

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 20;

# 进行计算 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age+1 = 21;

03、使用函数

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

# 使用函数 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE SUBSTRING(name, 1, 3) = 'abc';

04、like以通配符%开头

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

# like以通配符%开头 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc%';

05、不等于（!= 和 <>）

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc';

# 使用不等于 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

06、not in

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age in (1, 2, 3);

# 使用not in - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age not in (1, 2, 3);

07、is not null

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name IS NULL;

# 使用is not null - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name IS NOT NULL;

08、or前后存在非索引列

# 索引生效（age、name都有索引，type=index_merge）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

# or左侧classid无索引 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 100 OR name = 'Abel';
# or右侧classid无索引 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel' OR classid = 100;

09、索引选择性太差

索引选择性太差 - 通过索引查询的重复的值太多（例如性别）

通过索引找到的数据超过了表总数的一定比例时，会导致mysql选择全表扫描（大概是25%）

Tips：无法避免索引失效时，尽量使用覆盖索引

10、数据库和表的字符集不统一

不同的字符集进行比较前，需要进行转换，会造成索引失效。

统一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。

九、索引的设计与使用 ⭐️

1、索引的优缺点

【优点】

可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性。
可以提高数据检索的效率，降低数据库的IO成本。（随机IO变为顺序IO）
索引是有序的，通过索引列对数据进行分组和排序，可以减少分组和排序所消耗的时间，降低CPU的消耗。
在表关联的连接条件建立索引，可以加速表与表之间的相连。

【缺点】

索引文件会占用物理空间。除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。
创建索引和维护索引会耗费时间，随着数据量的增加而增加。
表的数据发生修改时，索引也要动态的维护。增加了维护成本，降低了表更新的速度。

2、适合创建索引的情况

有唯一性约束的字段（主键索引、唯一索引）
频繁作为 WHERE 查询条件的字段。
经常 GROUP BY 和 ORDER BY 的列。
- 索引可以让数据按照某种顺序进行存储或检索。
DISTINCT 字段需要创建索引。
- 索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
多表 JOIN 连接时，一定要给被驱动表添加索引。
区分度高（散列性高）的列适合作为索引。
使用列的类型小的创建索引

3、不适合创建索引的情况

在where中用不到或很少用的字段，不要设置索引。
数据量小的表最好不要使用索引（比如不到1000行）
区分度不高的列上（例如：性别）不要建立索引。
经常更新的表和列避免创建索引。
不要定义冗余或重复的索引
- 冗余索引：组合索引中最左的字段又建立单独的索引
- 重复索引：主键索引又单独定义为唯一索引和普通索引
不建议用无序的值作为索引
- 例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

4、索引下推（MySQL 5.6 的优化）

最左前缀可以用于在索引中定位记录，那些不符合最左前缀的部分，会怎么样呢？

# 组合索引 (name, age)
select * from stu where name like '张%' and age=10 and ismale=1;

根据组合索引，只能用 “张” 找到第一个满足条件的记录ID3，然后判断其他条件是否满足

MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
MySQL 5.6开始，可以在索引遍历过程中，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推：不看age的值，按顺序把name第一个字是“张”的记录一条条取出来回表。（需要回表4次）

在这里插入图片描述

有索引下推：在 (name, age) 索引内部就判断了age是否等于10，直接跳过不等于10的记录。（只要回表2次）

在这里插入图片描述

5、索引的设计原则

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
- 唯一索引对 insert 的影响几乎可以忽略不计，但对 select 的速度提升是明显的。
在多个字段都要创建索引的情况下，联合索引优于单值索引。
使用最频繁的列放到联合索引的左侧。
- 可以较少的建立一些索引
- 由于"最左前缀原则"，可以增加联合索引的使用率。
在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
索引的数量不是越多越好
- 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
- 索引对于插入、删除、更新操作也会增加处理上的开销。因为表中的数据更改的同时，索引也会进行调整和更新。
- 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，索引过多会增加优化时间。