【数据库】处理慢查询的个人理解
师兄问我如何处理慢SQL。我在学校做的小Demo里面数据量太小并没有涉及到性能优化问题，但是思考这个问题倒以把数据库优化给捋一捋。
传统的学生思维是很抽象的，比如一个SQL查询，看成是一个程序的执行，无非是想办法省内存，省磁盘空间，省CPU缓存，省调度，从这几个角度去想，这也是学生经常会回答的几条。
我之前的回答是这样的：
- 使用索引
- 优化SQL语句
- 优化数据库对象
- 硬件优化
- MySQL自身优化
上面说的对不对，大概来说，确实是有道理的，但这么说能解决问题吗，恐怕不能。实际情况中我们需要的首先不是解决方案，而是如何定位问题。一辆车子有异响，修理厂不去检查异响在哪里而是直接把四驱和引擎给换了一遍，这显然不对的。换个角度来说，我们首先要定位慢查询是原因，还是结果，而是不是上来就一顿优化。
什么是慢查询，从使用的角度来讲，感受直接的是时间维度的消耗，一个查询的时间超过了你做基准测试时的时间，或者是这个时间让使用者不能接受，都算慢查询。
如何定位慢查询，从整体来看，一个查询要经过这么几步：
- 从客户端发出，半双工的形式经过网络，进入服务器，由MYSQL服务执行。
- 在MYSQL中首先进入查询缓存，命中之后，检查权限，跳过解析，没有命中，进入查询语句解析。
- 解析后的语句进入预处理，如果可以优化进行查询优化，随后进入数据库引擎执行查询计划，获得数据返回给客户端。
那么可以简单从上归纳一下，慢查询的优化主要涉及：MYSQL自身的优化（Schema与数据类型的优化，创建高性能的索引，查询性能优化）和MYSQL的外部优化。在寻找问题的过程中，需要在各个层面设置追踪点，确定影响服务性能（我这里说的是整个服务而不仅仅是SQL）是在哪里。如果确定不是慢SQL的问题，测量的应该涉及服务器，客户端，网络这一块，慢查询的开始到结束的时间，如果是慢SQL的问题，那么测量的应该是SQL开始到结束的时间，而慢SQL的开始到结束的时间又分为开始到结束的时间又分为执行时间和等待时间。其中，执行时间由子任务时间，从每个子任务时间我们可以看到时间的占用情况，从而进行优化；等待时间则涉及系统间的影响和不同任务之间占用磁盘的CPU的影响。
回到我之前的回答，我们依次捋一下，我说的这些回答是怎么具体问题具体分析。
- 使用索引
- 优化SQL语句
- 优化数据库对象
- 硬件优化
- MySQL自身优化
硬件优化
这里说硬件优化可能过于狭隘，这一块应该不止是硬件优化，还有网络，应用层，操作系统。我就统一称为外部环境优化。这一部分涉及三条：
1.操作系统优化
2.硬件优化
3.应用层优化
4.网络配置
我主要讲一下硬件优化和网络配置，以后的再补。
硬件优化。许多不同的硬件都可以影响MYSQL的性能，但是最常见的俩个瓶颈应该是CPU和I/O资源。
CPU可以通过查看CPU占用来观察，我前面只讲了时间的消耗，那么要不要讲性能的消耗，我觉得可以，但是不是必要的，比如MYSQL对服务器CPU、内存的占用率，占用率很好是好事吗？我觉得是好事，这意味着服务器上更多的资源分配给了MYSQL，但是CPU的总利用率一直很高而且影响到了服务性能，就该考虑是不是要换CPU了。
I/O可以通过操作系统中的I/O统计指令，如iostat来观察。配置大量内存的最大原因并不是因为可以在内存中保存大量数据，而是为了避免磁盘I/O。虽然我们可以使用索引来避免随机I/O，但是使用缓存即使是随机读取也可以省更多的操作，一个设计良好的数据库缓存可以提高很好的工作效率。
需要注意的是，并不是足够的内存就可以完全避免磁盘的读取请求，在服务器重启以后有时会出现读取速度慢的时候，是因为服务器的缓存还没有“热”起来，而且虽然读写可以在内存中完成，但是写的持久化依然要进行磁盘I/O。
针对缓存，一般可以使用如下的方法：
1.多次写入，一次刷新（比如计数器）
2.I/O合并，（不同部分的数据在内存中修改，通过一次操作合并到一起完成持久化）
硬盘有延迟和吞吐量，网络也是。延迟和带宽通常是网络连接的限制因素，但更大的问题是出现在延迟上。因为TCP会尝试重发，因为它的安全机制，因为TCP会积压，因为它的连接队列（这里可以修改MYSQL的back_log）。一个应用程序通常会传输很多很小的网络包，最后每次传输的轻微延迟最终都会被累加起来，而不正常的网络造成的丢包也会造成同样的问题。
另一个问题出现在DNS的损坏，因为当MYSQL收到一个连接请求时，它同时需要做正向查找和反向DNS查找。当问题出现时，会导致连接拒绝，严重相当于遭到了DDOS攻击。因此在判断这一块的问题的时候，首先要监控网络性能和网络端口，如果DNS出现了问题，修改DNS，如果网络出现了问题，联系运营商或者修改网络设置。
使用索引
使用索引一定会提高查询效率吗，准确的说，应该是高性能的索引。要理解索引是如何工作的，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
索引涉及的东西好多，我过一阵子再写。
-------
下面是草稿
使用索引：
一般大家能想到的就是使用索引了。但是要避免全表扫描，首先考虑在where,order by,group by 涉及的列上建立索引。
优化SQL语句：
通过explain，可以看到SQL的执行效果，从而选择更好的索引和优化查询语句。比如：explain selext * from student。
查询的时候不要用 * 号，这样会返回你用不到的字段，最好是用具体的字段替代。
不在索引列上做运算或者使用函数。
查询的时候尽量使用 limit 减少返回的行数。
优化数据库对象：
使用 procedure analyse() 函数对表进行分析，这个函数可以对表中列的数据类型提出优化建议。表的数据类型的第一个原则是，能正确表示和存储数据的最短类型。能小就小。比如：select * from student procedure analyse()。
分表。如果一个表中有些列常用有些不常用，就垂直拆分，即把主键和一些列放在一个表中，把主键和另外一些列放在另一个表总。如果都常用，这个一个表又太大了，那就按行拆分，大表拆小表，也叫水平拆分。
中间表。如果要经常对一个表进行aggregation，那么我可以把这些结果用一个中间表存起来，这个也可以理解成另一种程度的分表。
分区。分区就是把一个表分成多个区块，这个区块可以在一个磁盘上，也可以
硬件优化：
这个时候就是能用钱解决的东西就用钱解决，更好的CPU，更大的内存，性能更强的SSD。
MySQL自身优化：
在安装Mysql的时候，里面还有一个my.cnf配置文件，里面可以对各项参数进行优化调整，比如加大MySql的查询缓存，加大数据库连接池。
Schema与数据类型优化的个人理解
良好的表的设计可以节省后期的维护成本。我在参与数据库开发的过程中数据量太小了，似乎也没有死扣细节以尽可能地提升查询地性能。
数据类型优化细节：
MySQL支持地数据类型特别多，但是通常我们要从这么几个方面考虑我们是要如何使用数据类型：
1.能正确存储数据地最小地数据类型。比如：如果字符串大小是一个定长的值，或者非常短的列，使用char会比varchar更有效率，比如存储一个Y/N值时，char只需要一个字节，而varchar(1)需要俩个字节，因为还需要以恶搞记录长度的额外字节。
2.简单地数据类型能存储地就不要用复杂地数据类型，比如：使用时间内建类型比如DATATIME或者TIMESAMP而不是字符串存储日期和时间，整形存储IP地址等。
3.避免NULL。比如：虽然很多表都可以包含NULL，但是如果是查询NULL列的时候可能会更复杂，比如当含NULL的列被索引时，每个索引需要一个额外的字节。
一定要完全范式化吗？
在课堂上老师教的我们建表需要满足三范式，即1.每列原子性 2.每列与主键相关 3.每列与主键列直接相关 。但是实际上建表的时候一定要保持范式化设计吗？我们来看看范式的优点：当数据比较好的范式的时候，很少有多余的数据，表小，更新查找速度快。看起来挺不错，但是范式化设计的schema的缺点通常在关联上，稍微复杂一点的查询语句在符合范式的schema上可能至少需要一次关联，这也可能使得一些索引策略失效，比如范式化可能将列存放在不同的表中，而这些列如果在一个表中本来可以数以一个索引。
举个例子，如果我要查看vip用户的最近的10条信息：
select message_text,user_name
from message
inner join user
on message.user_id = user.id
where user.account = vip
order by message.published desc limit 10
在这个查询中，Mysql首先需要扫描message中的published字段的索引，对于每一行找到的数据，都要在user表李检查这个用户是不是vip用户，如果只有一小部分用户的付费用户，那么效率是非常低下的。
