一、sql 优化思路
1. 优化更需要优化的Query;
2. 定位优化对象的性能瓶颈;
3. 明确的优化目标;
4. 从Explain 入手;
5. 多使用profile
6. 永远用小结果集驱动大的结果集;
7. 尽可能在索引中完成排序;
8. 只取出自己需要的Columns;
9. 仅仅使用最有效的过滤条件;
10. 尽可能避免复杂的Join 和子查询;
二、group_concat
在mysql中,有个函数叫“group_concat”,平常使用可能发现不了问题,在处理大数据的时候,会发现内容被截取了,
其实MYSQL内部对这个是有设置的,默认不设置的长度是1024,如果我们需要更大,就需要手工去修改配置文件。
修改默认字符大小
1).在MySQL配置文件中加上
group_concat_max_len = 102400 #你要的最大长度
2).可以简单一点,执行语句,可以设置作用范围
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
三、mysql 的游标和函数使用示例
set global event_scheduler = on; // mysql 执行事件
CREATE FUNCTION `NewProc`()
RETURNS int(11)
BEGIN
DECLARE uid VARCHAR(32); -- 声明变量
DECLARE success INT DEFAULT 0; -- 声明变量
DECLARE result VARCHAR(255); -- 声明变量
DECLARE ucount INT DEFAULT 0; -- 声明变量
-- 遍历数据结束标志
DECLARE done BIGINT DEFAULT 0;
declare userCursor cursor
FOR
SELECT uid,success,result from dev.temp_dxg tem;
-- 定义将结束标志绑定到游标
declare continue handler for sqlstate '02000' set done=1;
open userCursor; -- 打开游标
-- 开始循环
fetch userCursor into uid,success,result;
repeat
SELECT COUNT(0) into ucount FROM dxg.rxg_dxg dxg where uid=uid;
IF uid IS NOT NULL AND ucount>0 THEN
IF success=1 THEN
UPDATE dxg.rxg_dxg SET `check`=1,check_time=UNIX_TIMESTAMP() where uid=uid;
ELSE
UPDATE dxg.rxg_dxg SET `check`=2,check_msg=result,check_time=UNIX_TIMESTAMP() where uid=uid;
END IF;
END IF;
fetch userCursor into uid,success,result;
until done end repeat; -- 结束循环
close userCursor; -- 关闭游标
RETURN success;
END;
CREATE DEFINER = `dev`@`%` FUNCTION `NewProc`()
RETURNS int(11)
BEGIN
DECLARE tradeid VARCHAR(32); -- 声明变量
DECLARE xmoney FLOAT; -- 声明变量
DECLARE mobile VARCHAR(32); -- 声明变量
DECLARE xuid VARCHAR(32); -- 声明变量
DECLARE groupid VARCHAR(32); -- 声明变量
DECLARE progromno VARCHAR(32); -- 声明变量
DECLARE balance DOUBLE; -- 声明变量
DECLARE ucount INT DEFAULT 0;
DECLARE processNum INT DEFAULT 0;
-- 遍历数据结束标志
DECLARE done BIGINT DEFAULT 0;
declare userCursor cursor
FOR
SELECT tmp.mobile,tmp.tradeid,tmp.feeday FROM duce_fee_temp tmp;
-- 定义将结束标志绑定到游标
declare continue handler for sqlstate '02000' set done=1;
open userCursor; -- 打开游标
-- 开始循环
fetch userCursor into mobile,tradeid,xmoney;
repeat
SELECT COUNT(0) into ucount FROM w_user us where us.mobile=mobile;
IF mobile IS NOT NULL AND ucount=1 AND xmoney>0 THEN
SELECT u.avl_bal,u.id into balance,xuid from w_user u where u.mobile=mobile;
SELECT group_id,program_no INTO groupid,progromno from w_user_trade where id=tradeid and uid=xuid;
IF groupid IS NOT NULL AND progromno is NOT NULL THEN
if balance>=xmoney THEN
-- 新增配资保证金数据
INSERT into w_user_fund (id,uid,type,money,amount,pay_status,addtime,uptime,remark,lid,rid,`no`)
VALUES (replace(uuid(),'-',''),xuid,12,ROUND(-xmoney,2),ROUND(balance-xmoney,2),1,UNIX_TIMESTAMP(),
UNIX_TIMESTAMP(),CONCAT('补扣2015-07-06管理费:',ROUND(xmoney,2),'元'),groupid,progromno,tradeid);
update w_user SET avl_bal=ROUND(avl_bal-xmoney,2) where id=xuid;
ELSE
-- 新增配资保证金数据
INSERT into w_user_fund (id,uid,type,money,amount,pay_status,addtime,uptime,remark,lid,rid,`no`)
VALUES (replace(uuid(),'-',''),xuid,12,ROUND(-xmoney,2),ROUND(balance,2),0,UNIX_TIMESTAMP(),
UNIX_TIMESTAMP(),CONCAT('补扣2015-07-06管理费:',ROUND(xmoney,2),'元'),groupid,progromno,tradeid);
END IF;
SET processNum:=(processNum+1);
END IF;
END IF;
fetch userCursor into mobile,tradeid,xmoney;
until done end repeat; -- 结束循环
close userCursor; -- 关闭游标
RETURN processNum;
END;