记一次mysql优化操作

文章目录

  1. 1. 情景简化
    1. 1.1. 表结构
    2. 1.2. 性别格式
    3. 1.3. 附加条件
  2. 2. 用户来源
  3. 3. 性别获取
    1. 3.1. 字符替换判断

这次操作,起因是需要获取用户来源及用户性别,而用户的性别信息在第三方授权的中有,存为JSON格式,
不想用php去解析获取,所以试试mysql操作
如果你有更好的解决方案,请留言告诉我!

情景简化

表结构

记录用户授权信息,有微博、QQ、微信,个人信息存为user_info

  • user_platform
    • weibo_id,qq_id,weixin_id,user_info,uid,user_platform_id

      性别格式

  • 微博 【gender】(‘m’ =>男,’f’ => 女,’n’ => 未知)
  • QQ 【gender】 (‘男’ ,’女’ )
  • 微信 【sex】(1 =>男,2 => 女)

    附加条件

  • 数据量近百万行,处理不能太耗时

用户来源

先来个简单的,因为授权账户不是和用户一一对应,所以就是判断相关第三方id不为空则为对应用户来源,没有涉及绑定所以第三方信息实际唯一(表设计不是很合理,这个略过)

SELECT IF((ISNULL(weixin_id) || weixin_id != '', IF((ISNULL(weibo_id) || weibo_id != '', IF((ISNULL(qq_id) || qq_id != '', '手机', 'QQ'), '微博'), '微信') AS user_source, uid
FROM user_platform
WHERE uid != 0

其中 ISNULL(weixin_id) || weixin_id != '' 用来判断null和空字符串两种情况 也可以用 ISNULL(weixin_id) || LENGTH(TRIM(weixin_id)) < 1

性别获取

  • 分两类(gender,sex)去截取性别,
  • 然后放到临时表中,处理为统一格式的sex(1=>男,2=>女,0=>未知)
  • 在用户表中加一列sex并利用临时表更新

    字符替换判断

    使用mysql函数
  • locate
  • mid

以微信为例,判断是否包含性别

SELECT locate('sex":', user_info, 50) AS is_sex
FROM user_platform
WHERE user_info != ''    
HAVING is_sex != 0

然后截取性别,开始用

SELECT locate('sex":', user_info, 50) AS is_sex, mid(user_info, locate('sex":', user_info, 50) + 5, 1) AS sex
FROM user_platform
WHERE user_info != ''
HAVING is_sex != 0

发现特变慢,而且locate一样的判断执行两次,不如换做一次

SELECT mid(user_info, is_sex + 5, 1) AS sex, uid
FROM user_platform p, (SELECT locate('sex":', user_info, 50) AS is_sex, user_platform_id
    FROM user_platform
    WHERE user_info != ''
        AND uid != 0
    HAVING is_sex != 0
    ) t
WHERE t.user_platform_id = p.user_platform_id
GROUP BY uid

既然判断index,那如果先知道从哪开始判断,那就先获取最小位置,再利用locate第三个参数pos加快查找

SELECT MIN(is_sex)
FROM (SELECT locate('sex":', user_info) AS is_sex
    FROM user_platform
    WHERE user_info != ''

    HAVING is_sex != 0
    ) t

获取到最小位置为:57,那就 locate(‘sex”:’, user_info,55) ,其实可以用57,保险起见小点啦

效率提升,主要时间上 (本地PC测试): 10.17 sec 》》》7.36 sec

然后插入到临时表,mysql不能直接select into,所以先创建表载insert into

CREATE TABLE user_sex (
    uid int(11) NOT NULL,
    sex char(1) NOT NULL,
    PRIMARY KEY (uid)
)ENGINE = InnoDB DEFAULT CHARSET = `utf8`;

INSERT INTO user_sex
    (uid, sex)
SELECT uid, sex
FROM (SELECT mid(user_info, is_sex + 5, 1) AS sex, uid
    FROM user_platform p, (SELECT locate('sex":', user_info, 55) AS is_sex, user_platform_id
        FROM user_platform
        WHERE user_info != ''
            AND uid != 0
        HAVING is_sex != 0
        ) t
    WHERE t.user_platform_id = p.user_platform_id GROUP BY uid
    ) tmp

微博和QQ的相同处理,只不过判断的是gender
最后需要处理的是sex字段的统一,用replace搞定

UPDATE user_sex
SET sex = replace(sex, '男', 1);
UPDATE user_sex
SET sex = replace(sex, '女', 2);
UPDATE user_sex
SET sex = replace(sex, 'm', 1);
UPDATE user_sex
SET sex = replace(sex, 'f', 2);
UPDATE user_sex
SET sex = replace(sex, '"', 0);
# 最后一个是发现`gender:""`情况

最后的最后,把临时表删了吧

DROP TABLE user_sex

到这里,可优化的部分还是有的,根据业务,还可以加一些判断减少待处理数据,比方说 uid为0:非注册用户 等等

总结一下,除了在添加user表sex列会锁表比较长时间,
最后的用临时表更新和其余查询操作的锁表时间几乎微乎其微,
把统一操作放临时表也可以避免查询操作等待的时间,
优化无非就是做了大化小的工作。

另外备注下 mysql的sql文件注释符”–”错了,需要一个空格,应该是”– “.
如果想发现更多,请使用mysql slow query log

#慢查询设置
set GLOBAL long_query_time=1
set GLOBAL log_slow_queries=ON
set GLOBAL log_queries_not_using_indexes=1
show variables like ‘%slow%’
show variables like ‘%long_query%’

# 以table查看
set global log_output='TABLE';
select * from mysql.slow_log;
如有疑问,请留言或邮件newbvirgil@gmail.com
本文链接 : http://blog.newbmiao.com/2015/09/25/remember-once-mysql-optimize-operations.html