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

— 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/

— 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);

— 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';

— 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`;

版权声明：本文为博主原创文章，未经博主允许不得转载。

Mysql