首先设计表的字段,其中waijian和email是索引,id是主键。
自动插入数据库数据
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
DECLARE n int DEFAULT 1;
loopname:LOOP
insert into test7_9_4(username,xuehao,email,password,waijian) values(CONCAT('username_',n),CONCAT('xuehao',n) ,CONCAT(n ,'@qq.com'), MD5(n),(n));
SET n=n+1;
IF n=1000000 THEN
LEAVE loopname;
END IF;
END LOOP loopname;
END;
CALL my_insert();
1.普通索引与普通字段模糊查询对比
SELECT * FROM test7_9_4 WHERE email LIKE ‘%@qq.com%’ 耗时1.489s(索引)
SELECT * FROM test7_9_4 WHERE xuehao LIKE ‘%xuehao%’ 耗时1.653s(普通)
2.主键与普通索引模糊查询对比
SELECT * FROM test7_9_4 WHERE id LIKE ‘%4%’ 耗时0.995s(主键)
SELECT * FROM test7_9_4 WHERE waijian LIKE ‘%4%’ 耗时0.986s(索引)
3.主键与普通索引具体查询对比
SELECT * FROM test7_9_4 WHERE id =743244 耗时0.311s(主键)
SELECT * FROM test7_9_4 WHERE waijian =743244 耗时0.242s(索引外键)
4.主键与普通字段模糊查询对比
SELECT * FROM test7_9_4 WHERE id LIKE ‘%4%’ 耗时0.995s(主键)
SELECT * FROM test7_9_4 WHERE username LIKE ‘%username%’ 耗时1.529s(普通)
5.联合索引与非联合索引查询对比
联合索引没建立前,查询时间是1.045s
联合索引建立后,查询时间是0.389s
6.唯一索引与非索引查询对比
唯一索引没建立前,查询时间是1.646s
唯一索引建立后,查询时间是1.505s