循环添加10w行数据,测试索引效果
USE myschool;
#创建测试表
DROP TABLE IF EXISTS Test;
CREATE TABLE Test(
id INT,
NAME VARCHAR(50)
);
# 循环添加10W条数据数据
DROP PROCEDURE IF EXISTS usp_initial_data;
DELIMITER //
CREATE PROCEDURE usp_initial_data()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i <= 100000 DO
INSERT INTO Test VALUES (i, CONCAT('test-',CAST(i AS CHAR)));
SET i = i + 1;
END WHILE;
END //
CALL usp_initial_data();
#测试查询时间
SELECT * FROM test WHERE id=99998;
#创建索引
CREATE UNIQUE INDEX ix_test_id ON test(id);
#再次测试查询时间
SELECT * FROM test WHERE id=99998;