提：

mysql自己的參數生成html，不盡人意......

mysql -h$host -u$user -p$pass -H --skip-column-names $database -e "source get_query.sql" > /tmp/query.html

1、熱身一下，巧用awk構造mysql測試數據

#/bin/sh

echo "line" > file.dat

echo "" > insert.sql

awk 'BEGIN{

system("echo \"create table sysbench (col1 INT, col2 INT,col3 INT);\" | mysql -uroot -p123456 taobao")

for (i=1; i<=100; i++)

{

print " insert into sysbench values (" i "," i*i "," i*i*i ");" >> "insert.sql"

}

}

END{

system("mysql -uroot -p123456 -D taobao < insert.sql")

}' file.dat

2、

1）主要使用pt-query-digest來完成入庫操作

192.168.0.2為慢查詢入庫的機器

pt-query-digest --user=dba --password=123456 --review h=192.168.0.2,D=slow_query,t=global_query_review --history h=192.168.0.2,D=slow_query,t=global_query_review_history --no-report --filter=" $event->{Bytes}=length($event->{arg}) and $event->{hostname}=\"`ifconfig eth1|grep "inet addr"|awk '{print }'|awk -F':' '{print ":3306"}'`\" " /data/3306/slow_query.log

2）主要涉及到兩個表global_query_review和global_query_review_history,去percona官網copy創建或者pt工具自動創建

計算下慢查詢排行，根據Query_time_pct_95來排序，并且生成html格式

#/bin/sh

mysql -uroot -p123456 slow_query -N -e "select db_max as DBname,ifnull(sum(ts_cnt),0) as Ts_cnt,sum(cast(Query_time_sum AS SIGNED )) as Query_time_sum,avg(cast(Query_time_pct_95 AS SIGNED )) as Avg_Query_time_pct_95,sum(cast(Lock_time_sum AS SIGNED )) as Lock_time_sum,sum(cast(Rows_sent_sum AS SIGNED )) as Rows_sent_sum,sum(cast(Rows_examined_sum AS SIGNED )) as Rows_examined_sum,sum(cast(Rows_affected_sum AS SIGNED )) as Rows_affected_sum,sum(ifnull(cast(Tmp_table_sum AS SIGNED ),0)) as Tmp_table_sum,sum(ifnull(cast(Filesort_sum AS SIGNED ),0)) as Filesort_sum,sum(ifnull(cast(Full_scan_sum AS SIGNED ),0)) as Full_scan_sum from global_query_review a,global_query_review_history b where a.checksum=b.checksum and db_max not in ('information_schema') and b.Query_time_pct_95 >=1 group by db_max order by Avg_Query_time_pct_95 desc;" >slow.txt

echo "hello everybody:<table border=1><tr><td>Number</td><td>DBname</td><td>Ts_cnt</td><td>Query_time_sum</td><td>Avg_Query_time_pct_95</td><td>Lock_time_sum</td><td>Rows_sent_sum</td><td>Rows_examined_sum</td><td>Rows_affected_sum</td><td>Tmp_table_sum</td><td>Filesort_sum</td><td>Full_scan_sum</td>" >./query.html

awk '{ print FNR " " >awk '{ print FNR " " $0 }' ./slow.txt | awk '{if (NR % 2==1) print "<tr style=\"background:#58ACFA\"><td>" $1 "</td><td align=\"left\">" $2 "</td><td align=\"left\">" $3 "</td><td align=\"left\">" $4 "</td><td align=\"left\">" $5 "</td><td align=\"left\">" $6 "</td><td align=\"left\">" $7 "</td><td align=\"left\">" $8 "</td><td align=\"left\">" $9 "</td><td align=\"left\">" $10 "</td><td align=\"left\">" $11 "</td><td align=\"left\">" $12 "</td></tr>";else print "<tr style=\"background:#F3E2A9\"><td>" $1 "</td><td align=\"left\">" $2 "</td><td align=\"left\">" $3 "</td><td align=\"left\">" $4 "</td><td align=\"left\">" $5 "</td><td align=\"left\">" $6 "</td><td align=\"left\">" $7 "</td><td align=\"left\">" $8 "</td><td align=\"left\">" $9 "</td><td align=\"left\">" $10 "</td><td align=\"left\">" $11 "</td><td align=\"left\">" $12 "</td></tr>";}' >> ./query.html< }' ./slow.txt | awk '{if (NR % 2==1) print "<tr style=\"background:#58ACFA\"><td>" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td></tr>";else print "<tr style=\"background:#F3E2A9\"><td>" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td><td align=\"left\">" "</td></tr>";}' >> ./query.html

echo "</table>" >>./query.html

cat query.html:

hello everybody:<table border=1><tr><td>Number</td><td>DBname</td><td>Ts_cnt</td><td>Query_time_sum</td><td>Avg_Query_time_pct_95</td><td>Lock_time_sum</td><td>Rows_sent_sum</td><td>Rows_examined_sum</td><td>Rows_affected_sum</td><td>Tmp_table_sum</td><td>Filesort_sum</td><td>Full_scan_sum</td>

<tr style="background:#58ACFA"><td>1</td><td align="left">dba</td><td align="left">0</td><td align="left">18</td><td align="left">6.0000</td><td align="left">0</td><td align="left">0</td><td align="left">2999532</td><td align="left">2999532</td><td align="left">0</td><td align="left">0</td><td align="left">0</td></tr>

</table>

3）最后一步，發送郵件，大功告成。

/usr/bin/python sendmail.py aa@gmail.com Mysql Slowquery by Query_time_pct_95 >=1 ./query.html

這樣就可以發送html郵件了。

在前面

覺得不錯的朋友可以讀完關注下，每周都有定期更新的精選內容分享給大家，感謝您的支持！

一、為什么難

秒殺系統難做的原因：庫存只有一份，所有人會在集中的時間讀和寫這些數據。例如小米手機每周二的秒殺，可能手機只有1萬部，但瞬時進入的流量可能是幾百幾千萬。又例如12306搶票，亦與秒殺類似，瞬時流量更甚。

主要需要解決的問題有兩個：

1. 高并發對數據庫產生的壓力
2. 競爭狀態下如何解決庫存的正確減少（超賣問題）

對于第一個問題，已經很容易想到用緩存來處理搶購，避免直接操作數據庫，例如使用Redis。重點在于第二個問題，常規寫法：

查詢出對應商品的庫存，看是否大于0，然后執行生成訂單等操作，但是在判斷庫存是否大于0處，如果在高并發下就會有問題，導致庫存量出現負數

二、常見架構

流量到了億級別，常見站點架構如上：

1. 瀏覽器端，最上層，會執行到一些JS代碼
2. 站點層，這一層會訪問后端數據，拼html頁面返回給瀏覽器
3. 服務層，向上游屏蔽底層數據細節
4. 數據層，最終的庫存是存在這里的，mysql是一個典型

三、優化方向

1.將請求盡量攔截在系統上游：傳統秒殺系統之所以掛，請求都壓倒了后端數據層，數據讀寫鎖沖突嚴重，并發高響應慢，幾乎所有請求都超時，流量雖大，下單成功的有效流量甚小【一趟火車其實只有2000張票，200w個人來買，基本沒有人能買成功，請求有效率為0】

2.充分利用緩存：這是一個典型的讀多寫少的應用場景【一趟火車其實只有2000張票，200w個人來買，最多2000個人下單成功，其他人都是查詢庫存，寫比例只有0.1%，讀比例占99.9%】，非常適合使用緩存。

四、優化細節

4.1.瀏覽器層請求攔截

點擊了“查詢”按鈕之后，系統那個卡呀，進度條漲的慢呀，作為用戶，我會不自覺的再去點擊“查詢”，繼續點，繼續點，點點點。。。有用么？平白無故的增加了系統負載（一個用戶點5次，80%的請求是這么多出來的），怎么整？

• 產品層面，用戶點擊“查詢”或者“購票”后，按鈕置灰，禁止用戶重復提交請求
• JS層面，限制用戶在x秒之內只能提交一次請求

如此限流，80%流量已攔。

4.2.站點層請求攔截與頁面緩存

瀏覽器層的請求攔截，只能攔住小白用戶（不過這是99%的用戶喲），高端的程序員根本不吃這一套，寫個for循環，直接調用你后端的http請求，怎么整？

• 同一個uid，限制訪問頻度，做頁面緩存，x秒內到達站點層的請求，均返回同一頁面
• 同一個item的查詢，例如手機車次，做頁面緩存，x秒內到達站點層的請求，均返回同一頁面

如此限流，又有99%的流量會被攔截在站點層

4.3.服務層請求攔截與數據緩存

站點層的請求攔截，只能攔住普通程序員，高級黑客，假設他控制了10w臺肉雞（并且假設買票不需要實名認證），這下uid的限制不行了吧？怎么整？

• 大哥，我是服務層，我清楚的知道小米只有1萬部手機，我清楚的知道一列火車只有2000張車票，我透10w個請求去數據庫有什么意義呢？對于寫請求，做請求隊列，每次只透有限的寫請求去數據層，如果均成功再放下一批，如果庫存不夠則隊列里的寫請求全部返回“已售完”
• 對于讀請求，還要我說么？cache抗，不管是memcached還是redis，單機抗個每秒10w應該都是沒什么問題的

如此限流，只有非常少的寫請求，和非常少的讀緩存mis的請求會透到數據層去，又有99.9%的請求被攔住了

4.4.數據層閑庭信步

到了數據這一層，幾乎就沒有什么請求了，單機也能扛得住，還是那句話，庫存是有限的，小米的產能有限，透這么多請求來數據庫沒有意義。

4.5.mysql批量入庫提高INSERT效率

五、Redis

使用redis隊列（list），push和pop操作保證了原子性的實現。即使有很多用戶同時到達，也是依次執行。（mysql事務在高并發下性能下降很厲害）

先將商品庫存存入隊列：

<?php 
$store=1000; //商品庫存
$redis=new Redis(); 
$result=$redis->connect('127.0.0.1',6379); 
$res=$redis->llen('goods_store'); 
for($i=0; $i<$store; $i++){ 
 $redis->lpush('goods_store',1); 
} 
echo $redis->llen('goods_store'); 
?> 

客戶執行下單操作：

$redis=new Redis(); 
$result=$redis->connect('127.0.0.1',6379);
$count=$redis->lpop('goods_store');
if(!$count){
 echo '搶購失敗！';
 return;
} 

緩存也是可以應對寫請求的，比如我們就可以把數據庫中的庫存數據轉移到Redis緩存中，所有減庫存操作都在Redis中進行，然后再通過后臺進程把Redis中的用戶秒殺請求同步到數據庫中

六、總結

沒什么總結了，上文應該描述的非常清楚了，對于秒殺系統，再次重復下兩個架構優化思路

1. 盡量將請求攔截在系統上游
2. 讀多寫少經量多使用緩存
3. redis隊列緩存 + mysql 批量入庫

最后

讀到這的朋友可以轉發支持下，同時可以關注我，每周都有定期更新的精選內容分享給大家！