MySQL索引

时间：2024-03-19 22:49:33

文章目录

  • MySQL索引
  • 一、磁盘 IO
    • 1、磁盘IO
    • 2、磁盘读取的特点
    • 3、数据库IO的特点
  • 二、索引的数据结构
    • 1、哈希表
    • 2、有序数组
    • 3、搜索树
    • 4、BTree
    • 5、B+Tree
    • 6、不同存储引擎的索引数据类型
  • 三、聚簇索引 & 非聚簇索引
    • 1、非聚簇索引（MyISAM）
    • 2、聚簇索引（InnoDB）
  • 四、唯一索引 vs 普通索引
    • 1、查询过程比较
    • 2、change buffer 概述
      • 1）带 change buffer 的更新
      • 2）带 change buffer 的查询
      • 3）change buffer 对比 redo log
      • 4）merge
    • 3、更新过程比较
    • 4、两种索引的选择
    • 5、change buffer 小结
      • 1）change buffer 的优点
      • 2）change buffer 的大小
      • 3）使用场景
  • 五、组合索引
    • 1、最左匹配原则
    • 2、失效情况 - 存在模糊查询
    • 3、失效情况 - 存在范围查询
  • 六、字符串 - 前缀索引
    • 1、业务场景
    • 2、前缀索引
    • 3、区分度计算
  • 七、回表查询 & 覆盖索引
    • 1、回表查询
    • 2、使用覆盖索引
  • 八、索引失效的情况
    • 01、发生类型转换（自动/手动）
    • 02、进行计算
    • 03、使用函数
    • 04、like以通配符%开头
    • 05、不等于（!= 和 ）
    • 06、not in
    • 07、is not null
    • 08、or前后存在非索引列
    • 09、索引选择性太差
    • 10、数据库和表的字符集不统一
  • 九、索引的设计与使用 ⭐️
    • 1、索引的优缺点
    • 2、适合创建索引的情况
    • 3、不适合创建索引的情况
    • 4、索引下推（MySQL 5.6 的优化）
    • 5、索引的设计原则

MySQL索引

一、磁盘 IO

1、磁盘IO

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

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

在这里插入图片描述

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

小结一下：

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

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

2、磁盘读取的特点

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

在这里插入图片描述

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

  1. 系统找到磁盘块2之后，会将整个磁盘块2的数据都读取出来，而不是只读取一个5。
  2. 读取整个磁盘块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的区别

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

由于 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 的更新过程：

  1. Page1 在内存中，直接更新内存。
  2. Page2 不在内存中，就在内存的 change buffer 区域，记录下 “ 我要往Page 2插入一行 ” 这个信息
  3. 将上述两个动作记入 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的触发场景：

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

merge的执行流程：

  1. 从 磁盘 读入 数据页 到内存（老版数据页）；
  2. 从change buffer找出这个数据页的修改记录（可能有多个），依次应用，得到新版数据页；
  3. 写 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 (

相关文章