SQL编程创建100万条数据测试索引

-- 测试索引 建表并用函数插入100万条数据
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED not null auto_increment,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) not null COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别 0男  1女',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP null DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY key(`id`)
)ENGINE = innodb DEFAULT charset = utf8mb4 comment = 'app用户表'

set global log_bin_trust_function_creators=TRUE;
-- 插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data_version2()
returns int 
begin
	DECLARE num int DEFAULT 1000000;
	DECLARE i int DEFAULT 0;
	WHILE i<num DO
	  INSERT into `app_user` (`name`,email,phone,gender,`password`,age)VALUES    (CONCAT('用户',i),'123456@qq.com',
		CONCAT('13',floor(RAND()*((999999999-100000000)+100000000))),
		FLOOR(RAND()*2),
		UUID(),
		FLOOR(RAND()*100));
	set i = i+1;
END WHILE;
RETURN i;
end;
SELECT mock_data_version2()

-- 测试索引作用
SELECT * FROM app_user WHERE `name` = '用户99999'  16秒
create index id_user_app_name on app_user(`name`)
SELECT * FROM app_user WHERE `name` = '用户99999'  0.1秒

• 设置索引前
  
• 设置索引后
  
  索引在小数据量时效果不大，在大数据量时候区别明显