Mysql群数据整理过程

previous page next page
CREATE TABLE `qq_num1` LIKE `qq_group1`; INSERT INTO `qq_num1` SELECT * FROM `qq_group1` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group2` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group3` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group4` where LENGTH(QQNum) = 7; ... INSERT INTO `qq_num1` SELECT * FROM `qq_group96` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group97` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group98` where LENGTH(QQNum) = 7; INSERT INTO `qq_num1` SELECT * FROM `qq_group99` where LENGTH(QQNum) = 7;


2. 利用php根据搜索字段长度来区分搜索的表.
核心代码:

    $type   = ($_POST['type']);
    switch ($type) {
        case 'qq':
                if (strlen("$length") >= 5 && strlen("$length") <= 7)
                {
                $tables = 'qq_num7';
                }
                elseif (strlen("$length") == 8)
                {
                $tables = 'qq_num8';
                }
                elseif (strlen("$length") == 9)
                {
                $tables = 'qq_num9_';
                }
                elseif (strlen("$length") == 10)
                {
                $tables = 'qq_num10';
                }
                else {
                echo "It's null nab ;D";
                exit();
                }


3. 去重, 查询过程中发现有部分重复


最终处理完后, 文件大小变大一些(Myisam), 处理前为924,676,608 字节, 处理后为959,333,988 字节，速度后速度提升N倍! 8位一下及10位QQ查询速度在0.1s内, 9位QQ查询速度在2s内. (i7, hdd, 8g)

5位:
select QQNum,Nick,Age,QunNum from `qq_num7` where QQNum = '12345' limit 200页面运行时间: 0.065625905990601 秒

QQ号码QQ昵称年龄QQ群号群名字群信息
1234512345·深圳1132084455

12345123451142184150斯坦索姆-《逆鳞》
12345123451164591366霏凡
12345123451165264217心情祁东来到启东矿可能就是一种缘分我们可能来自不同的学校我们还有梦想吗
12345123451165264348没事找事


9位:
select QQNum,Nick,Age,QunNum from `qq_num9_1` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_2` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_3` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_4` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_5` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_6` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_7` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_8` where QQNum = '123123123' limit 200 union all select QQNum,Nick,Age,QunNum from `qq_num9_9` where QQNum = '123123123' limit 200页面运行时间: 0.13813106536865 秒

QQ号码QQ昵称年龄QQ群号群名字群信息
123123123皮皮3040720626ONE
123123123丫o℡莼粉Sè3045441346☆BABY々十二☆不许骂人。。不许打架。。。不许泡妞。。。
123123123丫o℡莼粉Sè3084318726~吹BB~祢⒋我;⒈軰子啲樶噯ぅ゛.︵鮱婆硪愛tan///??
123123123丫o℡莼粉Sè3085265897看群公告！好消息！免费拿話費兩百块!群里的朋友们用手机拨打1259064212参加侣友在线答题就可以了，我今



previous page start next page