记一次建立索引的研究过程

记一次建立索引的研究过程

遇到的问题：

最近在工作中遇到了sql执行慢的问题，现在记录一下解决的过程

问题场景：

1、首先有一张A表大概目前有7000万条数据，它需要去关联查询另外一张B表，数据条数和A表一样多，且是一对一的关系（理论上这两张表的数据可以合并为一张表，但是实际中却是两张表，有时候表不是我们建的，所以只能用）

2、A表和B表是由三个相同字段一起作为主键进行关联的。

3、A还需要通过一个字段关联一张C表，C表的数据只有几百

4、最后要按照A、B、C中多个字段进行筛选，还有根据时间排序。

因为工作中使用的是达梦数据库，且数据是保密的，因此我就在本地安装了mysql来做一些模拟数据来复现场景

现在创建一个学生表（student）相当于上述A表，一个学历表（education）相当于上述B表，一个班级表（class）相当于上述C表，建表语句如下。

其中学生表（student）通过name，age，birthday来关联学历表（education）

学生表（student）通过class_id来关联班级表（class）

CREATE TABLE `test`.`class`  (
  `id` int(11) NOT NULL COMMENT 'id',
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY USING BTREE (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

CREATE TABLE `test`.`education`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `age` int(255) NULL DEFAULT NULL COMMENT '学生年龄',
  `birthday` timestamp NULL DEFAULT NULL COMMENT '生日',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

CREATE TABLE `test`.`student`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `class_id` int(11) NULL DEFAULT NULL COMMENT '班级id',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '住址',
  `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '生日',
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `education` varchar(255) NULL DEFAULT NULL COMMENT '学历'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

下面就要给上面表存放模拟数据

于是我在网上找了一些方法感觉并不是太合适，我还是通过Java自己实现一下吧。

具体实现就略过了，本来还想使用多线程来插入呢，但是后来在网上发现说对于同一种表插入数据用多线程效果不大，不知道是不是这样，还需要求证。

于是我就多开了几个程序，感觉快了一点点

再然后数据库就拒绝连接了好像，不知道为什么，但是磁盘和cpu还没有跑满。

经过一段时间之后大概插入了600万条数据，但是执行

SELECT COUNT(*) from student

花掉了85秒，这比达梦数据库的7000万数据差多了，但是达梦为什么就秒执行，难道跟达梦数据库自己建立的索引有关系。

后来查了一些资料说count其实是有辅助索引的，而且和MySQL的版本有关系5.6之后的才可以，我现在的版本是 5.0.95，后面再试一下新版本。

后来我装了一个MySQL8.0的版本，让后将5.0版本的student所有数据迁移到了MySQL8.0版本里面，8.0版本里面数据存放在了固态硬盘里，且数据占用860 MB而5.0版本MySQL占用机械硬盘1.64 GB 这个不知道为什么。

在执行下面语句的时候，8.0仅用了2s，应该是和固态和版本都有关系。

SELECT COUNT(*) from student

于是我想给name字段加上索引，再用name字段统计数量会不会快一些

INDEX name USING BTREE(`name`)

SELECT COUNT(name) from student

然而上面语句执行耗时依然有85秒左右

于是我又想既然给name加上了索引，那么我用name进行普通条件查询会怎样

SELECT name FROM `student` where name ='student3'

很显然上面语句执行花费了0.9秒左右，效果显著，那么我让他展示更多字段会是怎样。

SELECT name,age FROM `student` where name ='student3'

加上age字段之后运行时间达到了20秒左右，这是为什么？

后面我注意到在执行上面语句的时候我的机械硬盘已经90%多以上的使用

难道是磁盘限制了查询速度，但是也不应该呀，后面再换一下固态试一下

后面换了MySQL8.0的版本，加上数据存储在固态硬盘里面，同样的数据和只有name的索引

执行上面的语句只用了2秒左右

那么我再增加几个字段会是怎样。

SELECT `name`,age,address,birthday,gender,class_id FROM `student` where name ='student3'

执行上面语句花费了22秒左右，只多了2秒，看来和显示多字段的关系并不大

到这里我发现我生成的数据有问题，其student表的age，name，birthday三个并不能唯一确定一条记录，和实际不符，我决定重新生成数据。

但是当我使用delete from student删除的时候，出现了说超过临时表的存储。

于是我就分多次执行delete from student limit 100000

但是问题又来啦，当我清空表之后我去查看磁盘中的student.idb文件发现仍然占用1G多。于是我查了资料发现是MySQL的回收机制原因https://www.jb51.net/article/197281.htm

终于跑进去了10万条数据进去，于是我愉快的试了一下查询速度，又发现第一次查询慢，第二次查询快，这我就去百度https://blog.csdn.net/weixin_31421625/article/details/113710491

发现是因为缓存的原因，还关不掉