Mysql - 百万级数据查询优化笔记 (PHP Script)
Mysql - 百万级数据查询优化笔记 (PHP Script) ②
说明:要处理的是在一个脚本中统计的年度账单,和上一篇的处理思路完全
不同,先把全量取出,再按字典形式拼接,10w条数据只需要668s!
- 1
- 2
数据:
测试服:17w 正式服:280w
- 1
1.全量查询,减少链接断开次数,使用PHP处理,性能更高
一次性取出1000条数据,放在循环处理快,还是一次处理100,用数组处理更快?
提前取出好数据,以字典的形式在数组使用中进行拼接,脚本中的使用应该尽量避免连接和断开的消耗,性能提高的非常明显,17w测试数据,从8条/秒 提升到 140条/秒!
1.1 首先全量取出需要的数据
Array
(
[0] => Array
(
[novel_id] => 67
[novel_name] => 还阳
[author_id] => 9
[novel_wordnumber] => 144334
)
[1] => Array
(
[novel_id] => 69
[novel_name] => 做媒
[author_id] => 19
[novel_wordnumber] => 135685
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
1.2 取出需要的数据,key是novel_id
,值是对应的novel_name
$novelNameDict = array_column($novelData, 'novel_name', 'novel_id');
//执行结果:
<pre>Array
(
[67] => 还阳
[69] => 做媒
[70] => 共业招感
[71] => 流心彩虹糖
[72] => 雪中燃灯
[73] => 王座
[74] => 不言而喻
[75] => 王以君倾
[76] => 踏雪寻泥
[77] => 有匪君子
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
1.3 获取Mysql中最小的数据,主要统计用户行为,所以对用户分组
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`
- 1
- 2
- 3
- 4
2.Mysql字符串需要转义
出于对安全的考虑,当mysql是字符串的时候,需要对字符串进行转义,保证sql语句的正常操作。
#面向对象风格
mysqli::real_escape_string ( string $escapestr ) : string
#过程化风格
mysqli_real_escape_string ( mysqli $link , string $escapestr ) : string
- 1
- 2
- 3
- 4
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";
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
4.避免重复数据
4.1 数据重复有两个方面,一个是PHP数组数据的重复,一个是Insert
语句重复,解决办法如下:
PHP数据重复使用array_unique( $array )
,注意键名保留不变,array_unique() 先将值作为字符串排序,然后对每个值只保留第一个遇到的键名,接着忽略所有后面的键名。这并不意味着在未排序的 array 中同一个值的第一个出现的键名会被保留。
4.2 Mysql Insert 去重
如果我们设置了唯一索引,那么在插入重复数据时,SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。
以下实例使用了 INSERT IGNORE INTO,执行后不会出错,也不会向数据表中插入重复数据:
INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas');
- 1
而 REPLACE INTO 如果存在 primary 或 unique相同的记录,则先删除掉。再插入新记录。
5.insert优化
INSERT INTO
可以拼接一次插入数据库,比单条插入的性能要快很多,尤其是多条的时候,也有一个小缺点,当其中一个字段有问题时整条语句一起失败。
INSERT INTO `annual_report_2020`(
`user_id` ,
`user_regtime` ,
)
VALUES ( 2 ,1500341346 ) ,
( 5 ,1502195321 ) ,
( 6 ,1502242604 )
- 1
- 2
- 3
- 4
- 5
- 6
- 7
5.执行结果
测试服: 173662 1296m 每次读取1000条 133row/s
- 点赞
- 收藏
- 关注作者
评论(0)