Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to optimize the performance of the website in ThinkPHP. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Easily do not use multi-table query, the following code and simulated data test results can make you clearly understand the horror of multi-table query, join,left join,inner join, do not use it if you can

/ * get the total valuation of holding idol coins * @ param $uid * @ return mixed * @ author zzl zzl@ourstu.com * time: 2019.06.18 * / public function getHasCoinExpect ($uid) {$tag='_User_total_tnk_'.$uid; $total=S ($tag); if ($total===false) {$total=0 / / get the valuation of holding idol coins $page=1;// paging query to prevent a user from owning too many idol coins, such as administrator $row=5000; do {/ / test idol_coin_has table 40W records, user 10W records, a single cycle takes about 0.1s $idol_has_list=$idol_uids=$idol_price_list=array () $price=$has_num=0; $idol_has_list=$this- > where (array ('uid'= > $uid,'has_num'= > array (' gt',0))-> page ($page,$row)-> order ('has_num desc,idol_uid asc')-> getField (' idol_uid,has_num'); $idol_uids=array_keys ($idol_has_list) $idol_price_list=D ('Idol/Idol')-> where (array (' uid'= > array ('in',$idol_uids),' single_price'= > array ('gt',0),' status'= > 1))-> getField ('id,uid,single_price') / / the single_price field is added here, which is 0 at initialization, and change foreach ($idol_price_list as $vo) {$price=$vo ['single_price']; $has_num=$idol_has_list [$vo [' uid']]; $total=bc_add (bc_mul ($has_num,$price,4), $total,4) } unset ($vo); $page++;} while (count ($idol_has_list) = = $row); S ($tag,$total,60); / / Test 40W records in idol_coin_has table. To query the number of idol coins held by users, there are 10W records, which takes 1.5 to 2.5s} return $total. / / the following annotation scheme is used to test 40W records of idol_coin_has table by inner join, and 10W records of users, which crashes directly and has no response, so abandon the scheme / * if (1 | | $total===false) {$total=0; / / to get the idol holding valuation $page=1. / / pagination query to prevent a user from owning too many idol coins, such as administrator $row=500; G ('d') $sql= "select table1.uid,table1.idol_uid,table1.has_num,table2.single_price from _ _ PREFIX__idol_coin_has as table1 inner join _ PREFIX__idol_info as table2 on table1.uid= {$uid} AND table1.has_num > 0 AND table2.single_price > 0 AND table1.idol_uid = table2.uid ORDER BY table1.has_num desc,table2.single_price desc"; do {G ('a') $sql_do=$sql. "limit". ($page-1) * $row. ",". $row; $list=$this- > query ($sql_do); foreach ($list as $val) {$total=bc_add (bc_mul ($val ['has_num'], $val [' single_price'], 4), $total,4);} unset ($val) Dump ($total); dump ($page); $page++; G ('b'); dump (G ('axiom list');} while (count ($list) = = $row); dump ('e'); dump (G ('dumbprint journal)); exit S ($tag,$total,60); * /}

For large quantities of operations, do not have sql queries in foreach, as shown in the following code:

A save solution takes about 0.01s for each operation, and 100s for 10W bars, which is 10 times longer than the current scheme, so deleting old records is adopted here. Operation of batch adding new records / * recalculate ranking * @ return boolean * @ author Zheng Zhongliang (zzl@ourstu.com) * @ date slf * / private function _ calculateRanking () {$heatModel=D ('idol_heat') $map ['status'] = 1; $order='heat desc,uid asc'; $page=1; $row=5000; $rank=1; $this- > startTrans () Do {/ / Test with 9W items of data, if each item needs to be changed, it takes 0.55s for a single cycle, and about 11s for all execution. / / it takes about 9W pieces of data to complete the test. If there are 1W items of data that need to be changed, it takes 0.15s for a single cycle, and it takes about 3s to complete the execution. / / in the actual use environment, each change will not exceed 1/10. And the total number of users is difficult to reach 10W, so the scheme is feasible: $heatList=$heatModel- > where ($map)-> order ($order)-> page ($page,$row)-> select () $rank_list=$del_ids=array (); foreach ($heatList as $val) {if ($val ['last_rank']! = $val [' rank'] | | $val ['change_rank']! = $rank-$val [' rank'] | | $val ['rank']! = $rank) {$val [' last_rank'] = $val ['rank']; $val [' change_rank'] = $rank-$val ['rank'] $val ['rank'] = $rank; $rank_list [] = $val; $del_ids [] = $val [' uid'];} $rank++;} unset ($val) If (count ($del_ids)) {/ / A save solution, each operation takes about 0.01s. If 10W, it takes 100s, which is 10 times longer than the current solution, so here we delete old records and add new records in batches to operate $res=$heatModel- > where ('uid'= > array (' in',$del_ids))-> delete () $res1=$heatModel- > addAll ($rank_list);} else {$res=1; $res1=1;} if (! $res | |! $res1) {break;} $page++;} while (count ($heatList) = = $row) If ($res&&$res1) {if ($this- > commit ()) {return true;}} else {$this- > rollback ();} return false;} the above is the research on how to optimize the website performance in ThinkPHP. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.