欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

mysql千万级数据怎么做优化

程序员文章站 2024-01-20 20:28:28
...
function insert(){		global $m;		if (!isset($_REQUEST['strs']) || !isset($_REQUEST['type'])			 || !isset($_REQUEST['hash'])){			echo 'param error';			return;		}		//strs为所有字符串		$poststr = $_REQUEST['strs'];        		$xstrs = json_decode(stripslashes($poststr), true);        		$type = $_REQUEST['type'];		$hash = $_REQUEST['hash'];				if (count($xstrs)  256){				$strtmp = substr($strtmp, 0, 256);			}            if (strlen($strtmp) >= 7)            {                array_push($strs, $strtmp);            }	    }                		//拼接所有字符串		$tmp = '("'.implode('","', $strs).'")';		//获取已存在的字符串		$sql = "select * from $table where str in $tmp";		$ret = mysql_query($sql, $m);		if (!$ret){			$msg = 'exec error:'.mysql_error($m).','.$sql;			DsLog::errLog($msg);			echo $msg;			return;		}		$exists = array();		$notexists = array();		$count = mysql_num_rows($ret);		for ($i = 0; $i  0){			//更新已存在字符串的count字段			$tmp = '("'.implode('","', $exists).'")';			$time = date('YmdHi');			$sql = "update $table set count=count+1 where str in $tmp";			$ret = mysql_query($sql, $m);			if (!$ret){				$msg = 'exec error:'.mysql_error($m).','.$sql;				DsLog::errLog($msg);				echo $msg;				return;			}			//更新已存在字符串的upd字段			$sql = "update $table set upd='$time' where str in $tmp";			$ret = mysql_query($sql, $m);			if (!$ret){				$msg = 'exec error:'.mysql_error($m).','.$sql;				DsLog::errLog($msg);				echo $msg;				return;			}		}						//插入新信息		if (count($notexists) > 0){			$time = date('YmdHi');			$sql = "insert ignore into $table (str,hash,count, upd) values";			for ($i = 0; $i   

我现在要对字符串做统计,统计每个字符串的数量,就是类似一个map, 这符串:数量,当然,数据库顺便还保存了一下字符中的crc等信息
insert函数接收post过来的strs,这是一个字符串数组,每次post过来的字符串数量平均为1500
现在数据库里的记录1200W条,每一次insert花费的时间平均为20秒(经常出现30秒超时)
请问下各位,该怎么做优化,现在只是统计了很小一部分,预计统计完数据量大概在1-10亿条


回复讨论(解决方案)

对了,str字段做了唯一索引,其它字段没做,mysql为MyISAM

你能确定瓶颈是在数据库做 查询 和 修改 的3条语句上吗?
你说 每次post过来的字符串数量平均为1500
那是指 1500 的单词吗?就算不是(是串长度)那么以平均每个单词20个字符计算,也有75个单词
你的过滤条件是 str in $tmp (str in ('xxx','xxx'....))也就是对表中的每一条记录都要做 1500(75)次字符串比较,而命中率至多是1/1500(1/75)你认为这样做合适吗?
数据库的强项在于记录间的比较,而你却在做弱势的列间比较
你可以用传入的数据构造出一个临时表(每行一个单词),再去与主表关联操作。这样才能发挥数据库的优势

再有,你的连续 2 条 update 语句不能合并在一起吗?

你能确定瓶颈是在数据库做 查询 和 修改 的3条语句上吗?
你说 每次post过来的字符串数量平均为1500
那是指 1500 的单词吗?就算不是(是串长度)那么以平均每个单词20个字符计算,也有75个单词
你的过滤条件是 str in $tmp (str in ('xxx','xxx'....))也就是对表中的每一条记录都要做 1500(75)次字符串比较,而命中率至多是1/1500(1/75)你认为这样做合适吗?
数据库的强项在于记录间的比较,而你却在做弱势的列间比较
你可以用传入的数据构造出一个临时表(每行一个单词),再去与主表关联操作。这样才能发挥数据库的优势

再有,你的连续 2 条 update 语句不能合并在一起吗?



1500是指字符数,字符数应该在5W左右

你的意思是先把1500条记录insert到一个临时表tmp_table,然后
然后 select* from table inner join tmp_talbe on table.str = tmp_table.str 这样么?

你能确定瓶颈是在数据库做 查询 和 修改 的3条语句上吗?
你说 每次post过来的字符串数量平均为1500
那是指 1500 的单词吗?就算不是(是串长度)那么以平均每个单词20个字符计算,也有75个单词
你的过滤条件是 str in $tmp (str in ('xxx','xxx'....))也就是对表中的每一条记录都要做 1500(75)次字符串比较,而命中率至多是1/1500(1/75)你认为这样做合适吗?
数据库的强项在于记录间的比较,而你却在做弱势的列间比较
你可以用传入的数据构造出一个临时表(每行一个单词),再去与主表关联操作。这样才能发挥数据库的优势

再有,你的连续 2 条 update 语句不能合并在一起吗?



1500是字符串数,大概3W个字符
你的意思是把1500个字符中插入到临时表tmp里,然后再 select* from white inner join tmp on white.str=tmp.str 么?
我刚测了下
select* from white inner join tmp on white.str=tmp.str 执行时间10S
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra1 	SIMPLE 	tmp 	index 	str 	str 	770 	NULL 	2531 	Using index1 	SIMPLE 	white 	eq_ref 	str 	str 	770 	bayes.tmp.str 	1 	 


直接执行 select * from white where str in(''xxx','xxx'...) 执行时间9S
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra1 	SIMPLE 	white 	range 	str 	str 	770 	NULL 	2531 	Using where


感觉效果差不多一样

1、mysql 有内存表,你并未使用
2、将两条 update 合并,应该能减少 10 秒

mysql_error这种库函数的效率也不高吧而且官方也不推荐,我几乎没有用过这种,我学PHP的时候,直接开始PDO的。

还有,你用了in这个关键字,好像它是不走索引的吧。

感觉效果差不多一样


应该是你测试环境的问题吧,联表查询大多数情况下是要比in快的
你测的结果差不多可能是如下原因
1.查询表的数据没有达到一定量
2.两种查询方式查出的结果集不同
3.查询字段未加索引或加的不对
4.被其他索引过多而影响
5.其他原因
我认为比较明显的就是第2个原因
你联表查时没有指定查询字段,联表出的字段肯定要比不连表的要多
还有联表查询会有一对多的情况,那样一条记录就会变成多条的,要加group by