最近正好在阅读《高性能MySQL》，同时在公司做一次分享会，在这次分享会中我将会从几个方面开展
- 如何剖析性能
- 正确使用数据类型
- 如何创建正确的索引
- 优化查询
一、为什么需要性能优化
衡量性能一个重要原则：完成某件任务所需要的时间度量。性能即响应时间。对于数据库服务器，我们关注的是它查询或者其他操作语句（SELECT, UPDATE, INSERT）的响应时间。其次，我们有个误区，认为mysql占用CPU越低越好，这个其实不然，如果在有效的查询中，mysql能够很好的利用CPU资源，即时在100%的情况下，也能非常块的响应，那么我们需要考虑另外一方面的优化。另外，我们只把性能优化看作提升每秒查询量（即吞吐量），对查询的优化可以让服务器每秒执行更多的查询，因为每条查询所执行的时间更加的短了。
我们需要做优化，首先应该进行对数据有个精准的测量，要知道为什么会响应如此之慢。所以本文将具体探讨如何测量以及数据优化。
二、对应用程序进行性能剖析
我们不能只针对mysql服务器进行剖析，在很多情况下应用导致的性能问题也不少，其性能瓶颈可能有很多因素：
- 外部资源，比如调用了其他web服务
- 应用需要处理大量的数据，比如分析二进制数据
- 在循环里执行昂贵的操作。比如滥用正则表达式
- 使用了低效的算法
分析PHP性能的工具非常多，比如facebook开发的xhprof（https://github.com/facebook/xhprof），xdebug。
三、剖析MySQL查询
分析服务端的负载是很有价值的，因为在服务端可以有效地审计效率低下的查询。定位和优化“坏”的查询能够显著的提升应用性能。
第一种方法，慢查询日志最初只捕捉比较“慢”的查询，在5.1版本之后，做了增强，现在只要设置long_query_time为0就能捕获所有的查询。
第二种方法，通过TCP抓包，然后根据MySQL的客户端/服务端通信协议进行分析，在linux下可以使用tcpdump，windows下可以使用wiresharke。
在分析单条查询，我们可以借助系统自身的SHOW PROFILE、SHOW STATUS两种方式。
1. SHOW PROFILE
这命令是在5.1时候引用进来的，而且默认是禁用的，我们可以通过修改服务器变量启用它。
mysql>SET profiling = 1;
设置完毕后，将会检测其耗费的时间和其他一些查询执行状态变更相关数据。 当一条查询提交给服务器时，服务端将会建立一张临时表，将所有信息全部记录进去。
mysql>select * from pre_forum_post LIMIT 0, 3000;
3000 rows in set (0.16 sec)
然后使用SHOW PROFILES查看有什么结果。
mysql>SHOW PROFILES;
+----------+------------+--------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------+
| 1 | 0.01831450 | select * from pre_forum_post LIMIT 0, 3000 |
+----------+------------+--------------------------------------------+
1 rows in set, 1 warning (0.00 sec)
要具体看一条数据我们可以这样。
mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000068 |
| checking permissions | 0.000009 |
| Opening tables | 0.000037 |
| init | 0.000031 |
| System lock | 0.000015 |
| optimizing | 0.000007 |
| statistics | 0.000015 |
| preparing | 0.000011 |
| executing | 0.000004 |
| Sending data | 0.018052 |
| end | 0.000017 |
| query end | 0.000009 |
| closing tables | 0.000013 |
| freeing items | 0.000014 |
| cleaning up | 0.000015 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
EXPLAIN
mysql> EXPLAIN select * from pre_forum_post LIMIT 0, 3000;
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
| 1 | SIMPLE | pre_forum_post | ALL | NULL | NULL | NULL | NULL | 36873047 | NULL |
+----+-------------+----------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
mysql> EXPLAIN select * from pre_forum_post as a LEFT JOIN pre_forum_thread as b ON a.tid = b.tid GROUP BY a.author ORDER BY b.tid;
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 36873047 | Using temporary; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 3 | plu_dx.a.tid | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+----------+---------------------------------+
2 rows in set (0.02 sec)
四、诊断间隙性的问题
间隙性处问题是很难诊断的，它可能因为：
- 应用服务器与MySQL服务器出现短暂的网络延迟
- memcached或者redis中的数据过期，导致这些请求全部需要从MySQL中获取
- DNS查询偶尔会有超时
- 互斥锁争用，或者内部删除查询缓存的算法效率太低
- 并发超过一定的阀值
是因为查询所导致的问题还是服务器？我们可以使用SHOW GLOBAL STATUS、SHOW PROCESSLIST、查询日志等等手段。