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