database►mysql

记一次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 本文链接 : https://newbmiao.github.io/2015/09/25/remember-once-mysql-optimize-operations.html