Mysql - 多张千万级统计数据实践笔记(PHP Script) 环球视点-起点工程网

Mysql - 多张千万级统计数据实践笔记(PHP Script) 环球视点

腾讯云 2023-02-26 11:10:59

概述

项目场景是给做用户年报，项目属于活动类型，需要维持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和内存，提高吞吐量，词很贴切。
至此
后来在正式环境执行的时候，我们还是遇到了别的问题，等我有时间再整理，不要打断这一篇博客主题，我们下一篇见，谢谢您的阅读。