1、表关联查询
如果关联的表示亿万级 百万级 那么影响会很大 所以要加上冗余字段
关联表
select a.user_name ,a.id,b.log_date from user_sys a,user_log b where a.id=b.user_id ORDER BY b.id desc limit 0,10;
添加冗余字段
select a.* from user_log a ORDER BY id desc limit 0,10;
加入3000W条数据测试结果:
[SQL]select a.user_name ,a.id,b.log_date from user_sys a,user_log b where a.id=b.user_id ORDER BY b.id desc limit 0,10;
受影响的行: 0
时间: 204.259s
[SQL]
select a.* from user_log a ORDER BY id desc limit 0,10;
受影响的行: 0
时间: 0.000s
保存用户操作日志的方法:
BEGIN
#Routine body goes here...
SET @gid=0;
set @user_name='';
set @_result='login success';
SELECT id,user_name into @gid,@user_name from user_sys where user_name=_user_name and user_pwd=_user_pwd limit 1;
if @gid=0 THEN #代表登录不成功
set @_result='login error';
end if;
SELECT * from (SELECT @_result as _result) a,(SELECT @gid,@user_name) b;
INSERT INTO user_log(user_id,log_type) VALUES(@gid,@_result);
END
#Routine body goes here...
SET @gid=0;
set @user_name='';
set @_result='login success';
SELECT id,user_name into @gid,@user_name from user_sys where user_name=_user_name and user_pwd=_user_pwd limit 1;
if @gid=0 THEN #代表登录不成功
set @_result='login error';
end if;
SELECT * from (SELECT @_result as _result) a,(SELECT @gid,@user_name) b;
INSERT INTO user_log(user_id,log_type) VALUES(@gid,@_result);
END
插入3000W条数据方法:
BEGIN
#Routine body goes here...
set @num=1;
while @num<30000000 DO
INSERT into user_log(log_type,user_id) values('log success',FLOOR(RAND()*1000000));
#Routine body goes here...
set @num=1;
while @num<30000000 DO
INSERT into user_log(log_type,user_id) values('log success',FLOOR(RAND()*1000000));
@num++;
end WHILE;
END
end WHILE;
END