CREATE TABLE app_user
(
id
BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name
VARCHAR(50) DEFAULT ‘’,
email
VARCHAR(50) NOT NULL,
phone
VARCHAR(20) DEFAULT ‘’,
gender
TINYINT(4) UNSIGNED DEFAULT ‘0’,
password
VARCHAR(100) NOT NULL DEFAULT ‘’,
age
TINYINT(4) DEFAULT NULL,
create_time
DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id
)
) ENGINE=INNODB DEFAULT CHARSET=utf8
– 插入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
)
VALUE(CONCAT(‘用户’,i),‘534240118@qq.com’,FLOOR (CONCAT(‘18’,RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
INSERT INTO app_user(name
,email
,phone
,gender
,password
,age
)
VALUE(CONCAT(‘用户’,i),‘534240118@qq.com’,FLOOR (CONCAT(‘18’,RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))
SELECT mock_data();
SELECT * FROM app_user WHERE name
=‘用户9999’ – 接近半秒
EXPLAIN SELECT * FROM app_user WHERE name
=‘用户9999’ – 查询99999条记录
– id _ 表名_字段名
– create index on 字段
CREATE INDEX id_app_user_name ON app_user(name
); – 0.001 s
EXPLAIN SELECT * FROM app_user WHERE name
=‘用户9999’ – 查询一条记录