MySQL の数千万のデータを最適化する方法

mysql の数千万のデータを最適化する方法

この投稿は、demofere によって最終編集されました: 2015-02-11 13:56:31

<br />function insert(){<br />		global $m;<br />		if (!isset($_REQUEST['strs']) || !isset($_REQUEST['type'])<br />			 || !isset($_REQUEST['hash'])){<br />			echo 'param error';<br />			return;<br />		}<br /><br />		//strs为所有字符串<br />		$poststr = $_REQUEST['strs'];<br />        <br />		$xstrs = json_decode(stripslashes($poststr), true);<br />        <br />		$type = $_REQUEST['type'];<br />		$hash = $_REQUEST['hash'];<br />		<br />		if (count($xstrs) <= 0){<br />			$msg = 'str error';<br />			DsLog::errLog($msg.$poststr);<br />			echo $msg;<br />			return;<br />		}<br /><br />		if ($type == '0'){<br />			$table = 'white';<br />		}<br />		else if($type == '1'){<br />			$table = 'black';<br />		}<br />		else{<br />			$msg = 'type error';<br />			DsLog::errLog($msg);<br />			echo $msg;<br />			return;<br />		}<br /><br />        $strs = array();<br />        <br />		for($i = 0; $i < count($xstrs); $i++) {<br />            $strtmp = $xstrs[$i];<br />            $strtmp = trim($strtmp);<br />            $strtmp = strtolower($strtmp);<br />	    	$strtmp = addslashes($strtmp);<br />            if (strlen($strtmp) > 256){<br />				$strtmp = substr($strtmp, 0, 256);<br />			}<br />            if (strlen($strtmp) >= 7)<br />            {<br />                array_push($strs, $strtmp);<br />            }<br />	    }<br />        <br />        <br />		//拼接所有字符串<br />		$tmp = '("'.implode('","', $strs).'")';<br /><br />		//获取已存在的字符串<br />		$sql = "select * from $table where str in $tmp";<br />		$ret = mysql_query($sql, $m);<br />		if (!$ret){<br />			$msg = 'exec error:'.mysql_error($m).','.$sql;<br />			DsLog::errLog($msg);<br />			echo $msg;<br />			return;<br />		}<br /><br />		$exists = array();<br />		$notexists = array();<br />		$count = mysql_num_rows($ret);<br />		for ($i = 0; $i < $count; $i++)<br />		{<br />			$item = mysql_fetch_assoc($ret);<br />			if (!$item){<br />				break;<br />			}<br />			array_push($exists, $item['str']);<br />		}<br />		<br />		for ($i = 0; $i < count($strs); $i++){<br />			if (in_array($strs[$i], $exists)){<br />				continue;<br />			}<br /><br />			array_push($notexists, $strs[$i]);<br />		}<br /><br />		for($i = 0; $i < count($exists); $i++) {<br />	    	$exists[$i] = addslashes($exists[$i]);<br />	    }<br />	    for($i = 0; $i < count($notexists); $i++) {<br />	    	$notexists[$i] = addslashes($notexists[$i]);<br />	    }<br />		<br />		if (count($exists) > 0){<br />			//更新已存在字符串的count字段<br />			$tmp = '("'.implode('","', $exists).'")';<br />			$time = date('YmdHi');<br />			$sql = "update $table set count=count+1 where str in $tmp";<br />			$ret = mysql_query($sql, $m);<br />			if (!$ret){<br />				$msg = 'exec error:'.mysql_error($m).','.$sql;<br />				DsLog::errLog($msg);<br />				echo $msg;<br />				return;<br />			}<br /><br />			//更新已存在字符串的upd字段<br />			$sql = "update $table set upd='$time' where str in $tmp";<br />			$ret = mysql_query($sql, $m);<br />			if (!$ret){<br />				$msg = 'exec error:'.mysql_error($m).','.$sql;<br />				DsLog::errLog($msg);<br />				echo $msg;<br />				return;<br />			}<br />		}<br />		<br />		<br />		//插入新信息<br />		if (count($notexists) > 0){<br />			$time = date('YmdHi');<br />			$sql = "insert ignore into $table (str,hash,count, upd) values";<br />			for ($i = 0; $i < count($notexists); $i++){<br />				$str = $notexists[$i];<br />				$crc = sprintf("%u", crc32($str));<br />				$sql .= "('$str','$crc','1', '$time'),";<br />			}<br /><br />			$sql = substr($sql, 0, strlen($sql) - 1);<br />			$ret = mysql_query($sql, $m);<br />			if (!$ret){<br />				$msg = 'insert error:'.mysql_error($m).','.$sql;<br />				DsLog::errLog($msg);<br />				echo $msg;<br />				return;<br />			}<br />		}<br />		<br />		echo !!$ret;<br />	}<br />

これを実行したいですon strings now 各文字列の数をカウントする統計はマップに似ています。この文字列:数量。もちろん、データベースは文字内の crc やその他の情報も保存します。
挿入関数は、ポストされた strs を受け取ります。は文字列配列であり、毎回ポストされる文字列の平均数は 1500 です
データベースには現在 1200W のレコードがあり、各挿入に費やされる平均時間は 20 秒です (30 秒のタイムアウトがよく発生します)
言い訳私、皆さん、これをどうやって最適化しますか? まだほんの一部しか数えていませんが、統計後のデータ量は約 10 億から 10 億と推定されています。
-----ソリューションのアイデア-- ---------- ----------
ボトルネックがデータベース内のクエリと変更の 3 つのステートメントにあることを確認できますか?
毎回投稿される文字列の平均数は 1500 だと言いました
それは 1500 単語という意味ですか?そうでない場合でも (文字列の長さです)、単語あたりの平均 20 文字に基づくと、まだ 75 単語あります
フィルター条件は str in $tmp (str in ('xxx','xxx) です'....) ) つまり、テーブル内のレコードごとに 1500 (75) 回の文字列比較が必要ですが、ヒット率は最大でも 1/1500 (1/75) です。これは適切だと思いますか?
データベースの強みはレコード間の比較ですが、実行しているのは列間の弱い比較です。
受信データを使用して一時テーブル (行ごとに 1 ワード) を構築し、それをメインテーブルが動作します。この方法でのみデータベースを活用できます

さらに、2 つの連続する更新ステートメントをマージすることはできませんか?
------解決策のアイデア----------------------
1. Mysql にはメモリ テーブルがあります。
は使用されません。 2. 2 つの更新をマージすると、10 秒短縮されるはずです
------解決策のアイデア------ - ----
Mysql_error ライブラリ関数はあまり効率的ではないため、公式には推奨されていません。私は PHP を学習していたとき、PDO を直接起動しました。

また、 でキーワードを使用しましたが、インデックスに登録されていないようです。
------ソリューションのアイデア----------------------

引用:

効果はほぼ同じだと感じます

ほとんどの場合、結合テーブルクエリの方が速いです
結果。理由は次のとおりです
1. クエリ テーブル内のデータが一定量に達していない
2. 2 つのクエリ メソッドで検出された結果セットが異なります
3.クエリフィールドがインデックス付けされていないか、正しく追加されていない
4. 他のインデックスが多すぎるため影響を受けている
5. その他の理由
より明らかなのは 2 番目の理由だと思います
クエリを指定しない場合テーブルを結合すると、結合されたテーブルによって生成されるフィールドは、結合しないテーブルよりも確実に優れたものになります
が必要であり、結合テーブル クエリは 1 対多の状況になるため、1 つのレコードになります。複数になるので

でグループを追加する必要があります