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

概述

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

技术方案

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

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

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

代码语言：txt复制

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

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

代码语言：txt复制

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

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

代码语言：sql复制

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

3.UNION ALL 处理分表业务

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

代码语言：php复制

$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相同的记录，则先删除掉。再插入新记录，再性能上会慢一些。

代码语言：txt复制

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

5.insert批量添加操作

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

代码语言：sql复制

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代表不限制。

代码语言：php复制

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和内存，提高吞吐量，词很贴切。

至此

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

php 数据库 sql 云数据库SQLServer 编程算法

0 人点赞