一、压入一百万条数据测试: MyISAM完胜InnoDB
二、避免重复的业务需求:
性能优先:唯一索引
扩展性优先:脚本语言判断
三、变量与会话
set @num=1;
select @num;
变量只存在在当前会话,并且随着会话结束销毁。
select id,user_name into @gid,@user_name FROM user_sys where user_name='user32';
select @gid,@user_name;
表的关联 变为一列
set @gid=0;
set @user_name=0;
#初始化;
select @gid,@user_name;
select @gid;
select 'success' as _result;
select * from (select 'success' as _result) a,(select @gid) as b;
前身代码:
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;
if @gid=0 THEN #代表登录不成功
set @_result='login error';
SELECT * from (SELECT 'login error' as _result) a,(SELECT @gid,@user_name) b;
ELSE
SELECT * from (SELECT 'login success' as _result) a,(SELECT @gid,@user_name) b;
SELECT @gid,@user_name
end if;
END;
优化后:
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;
if @gid=0 THEN #代表登录不成功
set @_result='login error';
end if;
SELECT * from (SELECT @_result as _result) a,(SELECT @gid,@user_name) b;
END
select * from tableName where xx limit
加上limit 速度快
查询10W条数据结果
[SQL]select * from user_sys2 limit 0,100000;
受影响的行: 0
时间: 0.096s
[SQL]
SELECT * from user_sys limit 0,100000;
受影响的行: 0
时间: 0.214s
select * from user_sys2 limit 0,100000;
SELECT * from user_sys limit 0,100000;
select COUNT(*) from user_sys2;
select COUNT(*) from user_sys;
LIMIT的影响
[SQL]select * from user_sys where user_name="user85";
受影响的行: 0
时间: 0.416s
[SQL]select * from user_sys where user_name="user85" limit 1;
受影响的行: 0
时间: 0.001s
影响好大!!
索引的影响
添加了索引以后 速度很好
[SQL]select * from user_sys where user_name="user85";
受影响的行: 0
时间: 0.031s