mysql测试索引

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’ – 查询一条记录