毕业后进入“Common Log Team”，主要对服务器日志（用户上网日志）进行统计、存储及查询，主要使用SQLite，按天切分表，也就是一天一个文件。后来想把这个设计搬到AWS上，对很多服务器日志进行处理。期间用到了mysql数据库，随着数据量越来越大，对mysql的性能要求越来越高。因此优化SQL也是一项亟需提高的技能。本文尝试对SQL优化进行讨论，也算对SQL优化进行学习总结。
一次事故：mysql被搞死了
背景：AWS上有一套系统，我是唯一的Ops（同时也是Dev）。某天下午，leader突然找我说，系统登陆不上。一时吓坏，因为出了事故要被很多人围观的。
首先，leader说他在用phpadmin对某个mysql数据库进行统计，然后突然就感觉出了问题，phpadmin不显示结果了，接着发现系统的页面非常慢。
然后，通过AWS的cloudwatch页面查看mysql服务器的性能指标，发现某台机器的write IOPS非常高。但是为什么这么高呢？接着用命令行登陆mysql，通过show processlist，看到有个正在执行的SQL，状态是Sending data。
(1) Mysql的Write IOPS曲线：
(2) show process：
mysql> show processlist;
+----+----------+--------------------+----------+---------+------+--------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+--------------------+----------+---------+------+--------------+-----------------------------------------------+
| 11 | root | 172.31.10.47:58055 | Profiles | Query | 0 | init | show processlist |
| 36 | root | 172.31.10.47:58064 | Profiles | Query | 767 | Sending data | select * from a,b,c order by c.last_heartbeat |
+----+----------+--------------------+----------+---------+------+--------------+-----------------------------------------------+
文档说Sending data意思是在SELECT语句中读取、处理记录，并将数据发送给客户端。因为处于这个状态的操作往往是在执行大量的disk访问（读取），通常在一次给定查询的整个生命周期中，这是最长的状态。
看到这个Sending data状态的查询，猜测应该是罪魁祸首！如何中断，2个方法：（1）kill Id（show processlist结果中的第一列）；（2）重启mysql服务器。
为什么元凶是它？
在processlist可以看到，select * from a,b,c order by c.last_heartbeat这个SQL消耗了大量Write操作，为什么这样：
（1）首先explain一下：
mysql> explain select * from a,b,c order by c.last_heartbeat;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1030 | Using temporary; Using filesort |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2402 | Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 3624 | Using join buffer (Block Nested Loop) |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
- 对三张表进行全表遍历
- 需要用临时表存储结果（using temporary），需要对结果进行排序（using filesort）
执行结果会产生多少条数据？表关联的结果是所有记录的笛卡尔积，使用where条件进行筛选。这里没有where条件，这意味着结果是abc的笛卡尔积，共有1030x2402x3624约为60亿条结果。所以结果集很大，需要将结果写入临时表中，那么可想而知要把结果写入临时表中，需要花费大量的磁盘写入操作。
所以最好在生产环境执行SQL语句时，特别是有表关联的操作，或者排序操作时，先通过explain看一下mysql的执行流程，最是保险。接下来会对explain的输出内容进行简单的介绍，这些输出非常有用，可以让我们知道SELECT语句是否是慢查询，index是否被正确的使用，是否使用了临时表，是否需要排序等。而临时表、排序、扫全表（没有使用index）都是慢查询的几种情况。