MySQL使用存储过程插入千万级数据如何提升效率?

时间:2021-06-11 14:28:07
-- MySQL测试任务:使用存储过程,往表中插入千万级数据,根据索引优化速度
-- 1.使用索引查询
-- 2.不使用索引查
-- 3.比较两者查询速度的差异

-- 1.创建索引测试表
DROP TABLE IF EXISTS index_test;
CREATE TABLE index_test(
id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
USER VARCHAR(16) DEFAULT NULL,
psd varchar(64) default null
/*psd mediumint DEFAULT 0 存储随机数据*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*MYISAM存储引擎不产生引擎事务,数据插入速度极快,为方便快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB*/
MySQL使用存储过程插入千万级数据如何提升效率?

-- 2.创建存储过程,插入数据
DELIMITER $$
USE `xscj`$$
DROP PROCEDURE IF EXISTS `insert_data`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_data`(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;/*定义一个变量,存储当前执行的次数*/
WHILE n <= num
DO
  INSERT INTO index_test(USER,psd) VALUES(concat('用户',n),password(n));/*concat()字符串连接、PASSWORD()加密函数*/
  /*INSERT INTO index_test(USER,psd) VALUES(CONCAT('用户',n),ret_pwd());*/
set n=n+1;
end while;
END$$
DELIMITER ;


-- 3.创建一个函数,随机生成密码,并返回给pwd字段
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `xscj`.`ret_pwd`()
    RETURNS int(5)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
DECLARE r int default 0;
SET r = floor(10+rand()*1000);
RETURN r;
    END$$

DELIMITER ;

-- 4.调用存储过程,插入100万条数据(总耗时:6 min 35 sec)
CALL insert_data(10000000);
MySQL使用存储过程插入千万级数据如何提升效率?
-- 5.此步骤可以忽略。修改引擎(执行耗时:2 min 49 sec   传送时间:1.078 sec   总耗时:2 min 51 sec)
ALTER TABLE `index_test` ENGINE=INNODB;

-- 6.通过主键索引查询,不使用普通索引(总耗时:0.022 sec)
SELECT * FROM index_test WHERE id='1950000';
MySQL使用存储过程插入千万级数据如何提升效率?
-- 7.不通过索引查询(总耗时:7.058 sec)
SELECT * FROM index_test WHERE USER='用户1950000'

-- 8.为USER字段创建普通索引,并通过该索引进行查询
CREATE INDEX index_user ON index_test (USER ASC);-- (总耗时:1 min 20 sec)
SELECT * FROM index_test WHERE USER='用户1950000';-- (总耗时:0.078 sec)

-- 9.使用user、pwd联合查询,由于user添加了索引,pwd未添加索引,故进行筛选查询是仍旧采用全表扫描,因此时间略有提升(总耗时:7.086 sec)
SELECT * FROM index_test WHERE USER='用户1950000' OR psd='*B810355CF0690506E5295AA66741D44E6AF4E61D';

-- 10.对user、pwd字段创建聚合索引后,再查询
CREATE INDEX index_userpwd ON index_test (USER,psd ASC);-- (总耗时:2 min 25 sec)
SELECT * FROM index_test WHERE USER='用户1950000' OR psd='*B810355CF0690506E5295AA66741D44E6AF4E61D';-- (总耗时:13.014 sec)


-- 查看表结构
DESC index_test;
-- 查询表数据
SELECT * FROM index_test;
-- 删除表数据
DELETE FROM index_test;
-- 删除索引
DROP INDEX index_user ON `index_test`;
DROP INDEX index_userpwd ON `index_test`;