SQL编程创建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 DEFAUl`app_user`T CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100万条数据
DELIMITER $$  -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
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),'3414142331@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i+1;
   END WHILE;
   RETURN i;
END;         

SELECT mock_data();

SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.993 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

SELECT * FROM student

-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户9999';   -- 0.001 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';