MySql增删改查的优化

一，问题

最近想看一下Mysql方面是怎么进行的优化。按照我的想法，mysql主要就增删改查操作，所以我想从这四个方向进行入手。

二，准备工作

2.1 建立数据库和表

课室表：

CREATE TABLE `class`  (
  `class_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '课室id',
  `student_id` int(11) NOT NULL COMMENT '学生id',
  `school_id` int(11) NOT NULL COMMENT '学校id',
  `class_type_id` int(11) NOT NULL COMMENT '课室类型id',
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

课室类型表：

CREATE TABLE `class_type`  (
  `class_type_id` int(11) NOT NULL COMMENT '课室类型id',
  `class_type_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课室类型',
  PRIMARY KEY (`class_type_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

学校表：

CREATE TABLE `school`  (
  `school_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校Id',
  `school_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学校名称',
  `school_addr` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学校地址',
  PRIMARY KEY (`school_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

学生表：

CREATE TABLE `student`  (
  `student_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生Id',
  `student_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.2 导入数据

最近想复习一下以前学过的存储过程，所以就自己写了一个来插入数据。其实我是第一次写存储过程，哈哈哈。

①添加学生数据的存储过程：

CREATE PROCEDURE `addStudentData`(in num int)
begin 
	DECLARE i int default 1;
  DECLARE j int default 1;
	DECLARE k int default 1;
	DECLARE insertid int default 0; 
	WHILE i < 4 DO	#三所学校
			SET j = 1;
			WHILE j < 5 DO	#四个课室类型
					SET k = 1;
					WHILE k < num DO	
							insert into student(student_name) VALUE(CONCAT("KOLO_",CONCAT(i,"_"),CONCAT(j,"_"),CONCAT(k,"_")));
							SELECT LAST_INSERT_ID() into insertid;
							CALL addClassData(insertid,i,MOD(k,4)+1);
							set k = k + 1;
					END WHILE; 
					SET j = j + 1;
			END WHILE; 
			SET i = i + 1;
	END WHILE;
end

②添加课室数据的存储过程：

CREATE PROCEDURE `addClassData`(in studentid int,
	in schooldid int,
	in classtypeid int)
begin
	declare empty_class_id int default 0;
	insert into class VALUES(empty_class_id,studentid,schooldid,classtypeid);
end

2.3 运行存储过程 addStudentData 获取3600条数据

call addStudentData(301);

三，解决方案

3.1 优化插入3600条数据：insert

①未优化前：
MySql增删改查的优化

②插入优化一：将多条insert语句变成一条insert语句
MySql增删改查的优化

说真的，看到这一幕，真的让我看到了新大陆。插入3600条数据竟然只用了0.497s，比没优化前要提高135倍！！！

解释：
1，将多条insert语句变成一条insert语句插入，可以减少mysql的解析次数。平时一条insert语句就解析一次，没优化前有3600条，就要解析3600次了。所以耗时比较长。
2，将多条insert语句变成一条之后，sql语句变短，那么它在网络中传输的效率也更高。所以它执行的效率也更快。

注意：sql语句的长度是有限制长度的，默认是1M，可以修改max_allowed_packe配置就进行修改。

③插入优化二：手动添加事务

MySql增删改查的优化

解释：
我们平时执行一条insert语句的时候，mysql会在后台自动帮我们创建事务和提交事务，未优化前有3600条insert语句，所以它会自动帮我们完成3600次的创建和提交事务操作。
现在我们手动创建事务和提交事务，让整个过程只需要完成一次创建和提交事务就行了。这样子就可以减少关于事务的操作次数。从而提高插入效率。

注意：因为在开启事务之后，在事务内的操作会记录到日志缓冲区中，该缓存区是由大小限制的，如果缓存区满了，那么它就会使用磁盘数据，其效率会有所下降。可以设置innodb_log_buffer_size配置项来进行修改。

MySql的Insert优化参考自这位大哥：https://blog.****.net/tigernorth/article/details/8094277

3.2 优化删除3600条数据

①使用drop命令

删除表结构和表内数据，数据不可恢复

②使用truncate命令

删除表内数据，数据不可恢复

③使用delete命令

删除表内数据，数据在事务提交后才删除，数据可以根据mysql的日志来恢复，并且可以触发trigger

我自己对这三个命令都测了十次，发现都不太稳定。但网上的人说在一般情况下，它们的效率：drop > truncate > delete 。我也不知道是不是。反正有时候，我自己测的时候不是这样的，有点迷。

本部分内容参考之：

https://blog.****.net/my543843165/article/details/53696441

https://blog.****.net/weixin_41004350/article/details/78547005

3.3 优化修改3600条数据

这个部分的话，我暂时还没在网上找到对应的资料，有空再找找看。

3.4 优化查询3600条数据

这部分待定，后面补上！

MySql增删改查的优化

相关推荐