mysql插入优化和遇到的问题

1.数据库优化

优化配置

MySQL千万级数据库数据插入insert速度加速调优_bulk insert buffer size-CSDN博客

-- 在执行存储过程前

# 关闭对外键的检查
SET  FOREIGN_KEY_CHECKS=0; 
SELECT  @@FOREIGN_KEY_CHECKS;

# 修改批量插入缓冲区大小参数: bulk_insert_buffer_size
set global bulk_insert_buffer_size = 1024*1024*200;
SELECT @@bulk_insert_buffer_size

# 禁止时时同步日志到磁盘
set global innodb_flush_log_at_trx_commit=2;  #禁止时时同步日志到磁盘
# 相对于 innodb_flush_log_at_trx_commit = 1, 设置为 0 可以明显的提高导入的速度。
# set global innodb_flush_log_at_trx_commit=0;
SELECT @@innodb_flush_log_at_trx_commit

# 此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。
set global innodb_log_buffer_size=1024*1024*128;

# 将 innodb_log_file_size 配置由于默认 8M 调整到 128M，在配置文件里修改
-- set global innodb_log_file_size=1024*1024*128;
set global innodb_autoextend_increment=128;
set global innodb_io_capacity=2000;
SELECT @@innodb_io_capacity                                                                                 
set global innodb_io_capacity_max=20000; 
set global max_allowed_packet=1073741824;
show variables;

# 关闭事务自动提交，这句对优化有很大效果
SET AUTOCOMMIT=0;

-- 执行存储过程

-- 执行过后
COMMIT;  # 由于自动提交关闭了，所以要最后commit一下

# 恢复对外键的检查语句为： 
SET FOREIGN_KEY_CHECKS=1;
# 恢复自动提交语句为:
SET AUTOCOMMIT=1;

从内存表复制数据

1. 我们可以使用memory引擎创建一个一样的表，用这个表做插入查询，然后导进来，会看很多。可以看下面文章做参考。

MySQL如何快速生成千万数据量？_如何按照主key作成百万数据-CSDN博客

 但是导多了，删多了，我发现速度大幅度变慢，把表删除，重新建立之后就又变快了。(?)

2.可以在存储过程里建立临时表(内存表)，每次运行删除这个表

MySQL 临时表 | 创建、使用和删除 MySQL 临时表 (mysqljiaocheng.com)

建立：

CREATE TEMPORARY table(
 ...
);

删除：

每次会话关闭，或者整个流程关闭都会自动删除
想要显示的删除可以用 drop table 或者 drop temporary table

3.但是测试发现，在存储过程中建临时表没有在外面建快，可能是上下文原因

结论：在外面建memory表，在里面定时复制到主表，删除数据，准备下一下插入。

游标保存结果集

在生成数据的过程中，可能需要从其他表中随机选取数据，可以用语句

select a,b INTO a1,b1 from table order by rand() limit 1;

但是这个语句的效率是比较低的，可以建立游标，提前选好总数量要求的随机选取的数据

DECLARE cur CURSOR FOR select a,b INTO a1,b1 from table order by rand() limit counts;

但是要注意，limit 后面显示的数量如果大于该表总数量是直接返回该表总数量的数据，所以会导致随机数据没有选够。

策略一：

我们可以关闭这个游标后，重新打开这个游标。

游标open后会再次执行定义的sql语句，所以随机选取的是不一样的，满足我们的要求。

declare CONTINUE HANDLER FOR SQLSTATE '02000' 
BEGIN
close cur;
open cur;
END;

策略二：

我们可以编写一个动态sql，把要查询的语句用union来拼接到一起，然后执行保存到临时表中。
定义游标从临时表中获得数据集。(游标不支持获取动态sql，只支持静态)

注意：declare只能放在begin第一句，所以创建临时表后获取要用两个begin...end

CREATE PROCEDURE ProcessTempTableData()
BEGIN
 -- 创建临时表
    DROP TEMPORARY TABLE IF EXISTS temp_table;
    CREATE TEMPORARY TABLE temp_table (
        id INT,
        name VARCHAR(100)
    );

    -- 插入示例数据到临时表
    INSERT INTO temp_table VALUES (1, 'John'), (2, 'Jane'), (3, 'Doe');
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE _id INT;
    DECLARE _name VARCHAR(100);


    -- 声明游标
    DECLARE cur CURSOR FOR SELECT * FROM temp_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 循环处理游标中的数据
    read_loop: LOOP
        -- 从游标中获取一行数据
        FETCH cur INTO _id, _name;

        -- 检查是否已经没有更多的行
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 这里可以添加处理每一行数据的逻辑
        -- ...

    END LOOP;

    -- 关闭游标
    CLOSE cur;

    -- 删除临时表（可选）
    DROP TEMPORARY TABLE IF EXISTS temp_table;
END;
END;

测试发现，策略一较快。

结论：每次选取数据不够时，从新打开游标即可。

 最后，以上三种一起使用达到优化最佳。

一个案例

-- 采用内存表复制数据和游标保存结果集来优化
create table Bank_Deal_Info_memory (
BDNo int(4) auto_increment primary key not null comment '交易编号',
    BDBCNo char(19) not null comment '银行卡号',
    BDDealDate datetime(3) not null comment '交易日期',
    BDDealAcount decimal(10,2) not null comment '交易金额',
    BDDealType char(10) not null comment '交易类型',
    BDDealComment varchar(100) comment '描述'
)  ENGINE=memory

CREATE PROCEDURE proc_InsertBankDealInfo_optimize(in InfoCounts int)
BEGIN
DECLARE BDBCNo1 char(19) ;
DECLARE BCOpenDate1 datetime(3);
DECLARE BDDealDate1 datetime(3);  
DECLARE BDDealAcount1 decimal(10,2); 
DECLARE BDDealType1 char(10); 
DECLARE BDDealComment1 varchar(100);
DECLARE insert_times int DEFAULT 5000;
DECLARE i int DEFAULT 1;
DECLARE count int;
-- 用游标来获取InfoCount条随机数值
DECLARE cur CURSOR FOR select BCNo,BCOpenDate FROM bank_card_2267111227 order by rand() limit InfoCounts;
declare CONTINUE HANDLER FOR SQLSTATE '02000' 
BEGIN
close cur;
open cur;
END;
set @type='转入 转出 存款 取款';

open cur;
WHILE i<=InfoCounts/insert_times DO
	set count=0;
	WHILE count<insert_times DO
	-- 从游标中选取数据
	fetch cur into BDBCNo1,BCOpenDate1;
	set BDDealDate1=DATE_ADD(BCOpenDate1,INTERVAL floor(RAND()*TIMESTAMPDIFF(second,BCOpenDate1,now())) SECOND);
	set BDDealAcount1=ROUND(RAND()*2000,2);
	SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@type,' ',floor(RAND()*5+1)),' ',-1) INTO BDDealType1;
	set BDDealComment1=CONCAT(BDDealDate1,",交易类型为",BDDealType1,",交易金额为",BDDealAcount1);
	
	INSERT INTO bank_deal_info_memory(BDBCNo, BDDealDate, BDDealAcount, BDDealType, BDDealComment) VALUES
	(BDBCNo1, BDDealDate1, BDDealAcount1, BDDealType1, BDDealComment1);
	set count=count+1;
	END while;
	INSERT into bank_deal_info_2267111227 SELECT * FROM bank_deal_info_memory;
	DELETE FROM bank_deal_info_memory;
	set i=i+1;
END WHILE;
close cur;
END

INSERT into bank_deal_info_memory VALUES((SELECT max(BDNo)+1 FROM bank_deal_info_2267111227),1,'2024-01-06 02:41:14.000',1,1,1);
call proc_InsertBankDealInfo_optimize(500000);

drop PROCEDURE proc_InsertBankDealInfo_optimize;
TRUNCATE TABLE bank_deal_info_myisam;

使用随机数选取一条随机数据

用游标保存所有随机数据的语句是将所有数据每次重新排序，选取第一条，这个速度很慢，因为每次都要排一遍，也就是扫描所有数据。

所以我们可以先生成一个随机数，然后将这个随机数作为id，选取记录即可。

set @id=cell(rand()*2000);
SELECT * FROM table WHERE id = @id;

2.遇到的错误

1.Unknown column 'name' in 'where clause'

我自己是字段名前面有空格

id应该为`  id`

错误记录：Unknown column ‘xxx‘ in ‘where clause‘_unknown column 'username' in 'where clause-CSDN博客

2.数据库连接不上

在用存储过程插入百万级数据是时，可能会直接显示数据库连接不上。

mysql根据配置文件会限制server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败，导致项目访问异常。

set global max_allowed_packet=1073741824;
-- 把最大可以接收的数据大小改为1G