提：

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 批量入库

最后

读到这的朋友可以转发支持下，同时可以关注我，每周都有定期更新的精选内容分享给大家！