当前位置: 首页 > 资讯 > 正文

Mysql - 多张千万级统计数据实践笔记(PHP Script)|微资讯

来源:腾讯云    时间:2023-02-26 23:04:50

概述

项目场景是给做用户年报,项目属于活动类型,需要维持1个月左右,需要统计用户操作的一些数据,主要是统计方面的,当时注册用户大概280w左右,书单、评论、打赏还可以,之前的数据做过分表,只有阅读记录log大概将近1亿条,是个大难点。

技术方案

1.全量查询,减少链接断开次数,使用PHP处理,性能更高

一次性取出1000条数据,还是一次处理100? 答案是取1000条,如果服务器的内存允许,一次可以取更多条,应该尽量避免mysql进程中连接和断开的消耗,性能提高的非常明显,17w测试数据,从8条/秒 提升到 140条/秒!


【资料图】

把数据量偏小的取出,做map映射,以提高最大的性能,再使用的地方用key拼接使用。

Array(    [67] => 还阳    [69] => 做媒    [70] => 共业招感    [71] => 流心彩虹糖    [72] => 雪中燃灯    [73] => 王座    [74] => 不言而喻    [75] => 王以君倾    [76] => 踏雪寻泥    [77] => 有匪君子)

2. 获取Mysql中最小的数据,主要统计用户行为,所以对用户分组

SELECT `user_id` ,`novel_id` ,`chapter_id` ,MIN(DATETIME) AS `datetime`FROM `wm_novel_reward`WHERE `user_id` BETWEEN 1AND 1006GROUP BY user_id`

出于对安全的考虑,当mysql是字符串的时候,需要对字符串进行转义,保证sql语句的正常操作。

#面向对象风格mysqli::real_escape_string ( string $escapestr ) : string#过程化风格mysqli_real_escape_string ( mysqli $link , string $escapestr ) : string

3.UNION ALL 处理分表业务

对于操作频次较高的业务场景中,分表的情况比较多,使用UNION ALL进行优化处理。

$commentMostSqlArr = [];for ($i = 0; $i < 128; $i ++) {    $table = "cp_comment_msg_uid_".$i;    $commentMostSqlArr[] = "SELECT `uid`,`nid` ,`module_name` ,`aid` ,    `author_name` ,count(module_id) AS count_comment_number  FROM {$table}    Where  `uid` BETWEEN {$minUid} AND {$maxUid}     AND `gift_id` = 0 AND `create_time` > {$total_start_time} AND     `create_time` <= {$total_end_time}      Group by nid, uid ";}$commentMostSql = "SELECT * FROM (" . implode(" UNION ALL ", $commentMostSqlArr) . ") t";

4.避免出现重复数据

1.数据重复有两个方面,一个是PHP数组数据的重复,一个是Insert语句重复,这样解决的:

PHP数据重复使用array_unique( $array ),注意键名保留不变,array_unique() 先将值作为字符串排序,然后对每个值只保留第一个遇到的键名,接着忽略所有后面的键名。这并不意味着在未排序的 array 中同一个值的第一个出现的键名会被保留。

2.mysql层面使用sql语句去重,使用 INSERT IGNORE INTO去重:

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO,执行后不会出错,也不会向数据表中插入重复数据,而 REPLACE INTO 如果存在 primary 或 unique相同的记录,则先删除掉。再插入新记录,再性能上会慢一些。

INSERT IGNORE INTO person_tbl (last_name, first_name)  VALUES( "Jay", "Thomas");

5.insert批量添加操作

INSERT INTO可以拼接一次插入数据库,比单条插入的性能要快很多,尤其是多条的时候,也有一个小缺点,当其中一个字段有问题时整条语句一起失败。

INSERT INTO `annual_report_2020`(`user_id` ,`user_regtime` ,)VALUES (   2 ,1500341346 ) ,   (   5 ,1502195321 ) ,       (   6 ,1502242604 )

6.系统配置参数上的优化

1.memory_limit设置脚本内存

memory_limit的值是越大越好么?当然不是,memory_limit主要是为了防止程序 bug, 或者死循环占用大量的内存,导致系统宕机。在引入大量三方插件,或者代码时,进行内存限制就非常有必要了。

memory_limit会使每个 PHP process 都占用固定的内存?memory_limit 只是限制了每个 PHP进程的内存占用上限,而不是为每个进程分配了固定的内存。所以,并不会因为 memory_limit 设置越大,导致并发数出现降低。

2.set_time_limit设置脚本执行时间

在php中set_time_limit函数是用来限制页面执行时间的,如我想把一个php页面的执行时间定义为5秒

就可以set_time_limit(5)了,规定从该句运行时起程序必须在指定秒数内行结束,0代表不限制。

set_time_limit(0);ini_set("memory_limit", "1024M");

7.关于内存的说明

很多时候我们都在说内存,其实内存的概念很笼统,内存分为CPU上的缓存(catch)和内存条上的内存(Memory),平时说的服务器8G内存指的是内存条上的内存。Cpu已经从Lmp总线型,升级成NUMP的方式,Nginx就是利用NUMP很好的例子。

其实有一个问题特别迷惑,Mysql数据库里的数据就是以特殊结构存储(B-tree)的文件,Redis中的数据也是用(list、hash)结构存储的数据,存储上没有什么太大的不同,只是有快慢的区别。

而高性能就是尽最大的程度上利用Cpu和内存,提高吞吐量,词很贴切。

至此

后来在正式环境执行的时候,我们还是遇到了别的问题,等我有时间再整理,不要打断这一篇博客主题,我们下一篇见,谢谢您的阅读。

X 关闭

最近更新

Copyright ©  2015-2023 京津冀字画网版权所有  备案号:京ICP备2022022245号-12   联系邮箱:434 922 62 @qq.com