Mysql - 百萬級資料查詢優化筆記 (PHP Script) ②
說明：要處理的是在一個腳本中統計的年度賬單，和上一篇的處理思路完全
不同，先把全量取出，再按字典形式拼接，10w條資料只需要668s!
資料：
測驗服：17w 正式服:280w
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 取出需要的資料，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.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`
2.Mysql字串需要轉義
出于對安全的考慮，當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.避免重復資料
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');
而 REPLACE INTO 如果存在 primary 或 unique相同的記錄，則先洗掉掉，再插入新記錄，
5.insert優化
INSERT INTO 可以拼接一次插入資料庫，比單條插入的性能要快很多，尤其是多條的時候，也有一個小缺點，當其中一個欄位有問題時整條陳述句一起失敗，
INSERT INTO `annual_report_2020`(
`user_id` ,
`user_regtime` ,
)
VALUES ( 2 ,1500341346 ) ,
( 5 ,1502195321 ) ,
( 6 ,1502242604 )
5.執行結果
測驗服： 173662 1296m 每次讀取1000條 133row/s
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/238541.html
標籤：java