MySQL数据库优化常规流程

  • 问题定位
  • 1.系统软件方面的问题
  • 2.系统硬件方面的问题
  • MySQL索引漫谈
  • 开启慢查询日志，让问题复现
  • 1.首先我们需要打开MySQL的慢查询日志功能
  • 2.设置慢查询阈值
  • 3.通过测试收集慢SQL
  • 通过explain关键词分析SQL
  • 1.id
  • 2.select_type
  • 3.table
  • 4.type
  • 5.possible_keys
  • 5.key
  • 6.key_len
  • 7.ref
  • 8.rows
  • 9.Extra
  • explain总结
  • 开启Profile
  • 谈谈锁的影响
  • 小结


问题定位

web应用变慢的原因存在很多，问题第一时间定位不应该直接锁定SQL有问题，以个人分析来说，主要有以下几点：

1.系统软件方面的问题

1.1 系统设计是存在一定的缺陷，导致系统存在内存泄漏，内存泄漏存在隐蔽性和累积性，长期运行的系统若存在内存泄漏现象必然会导致系统运行变慢，更严重会导致OOM（Out of Memory）；
1.2 系统代码编写时没有养成良好的习惯，存在很多IO或其他系统资源没有关闭，导致存在隐蔽的不可利用资源;
1.3 IP、端口等隐蔽信息天然暴露，没有做好安全措施，导致DOSS攻击等各类的网络攻击；
1.4 运行环境的虚拟内存设定不合理（本人使用的是java，则这里就特指JVM内存设定），导致频繁的GC或是fullGC，由于算法的不确定性和效率问题，导致系统变慢
1.5 SQL语句存在优化空间，存在大量的多表联查和索引失效现象

2.系统硬件方面的问题

2.1 系统变慢第一时间应该从最简单的切入点入手，应该及时联系运维工程师或登录服务器用top（此处特指linux）命令查看系统运行状况，判断CPU利用率、内存利用率、磁盘占用率以及网络带宽占用率等参数，参考各项参数优化当前进程，选择service restart、重启服务器（迫不得已）或申请硬件升级，集群扩容;
2.2 网络震荡导致微服务调用延迟或掉线；
2.3 最坏情况下是由于服务器所处环境或高负荷运行的原因导致硬件损坏（这也是为什么中小型企业选择云端开发的原因之一）；
就上述所示，导致web应用变慢的原因有很多，在此罗列的是一些可能性较高的因素，而其中SQL优化是我们可以比较容易介入的一方面。

MySQL索引漫谈

MySQL的索引是一种有序的数据结构，效果类比字典的目录，便于数据库快速查询条件数据。最常见的索引类型是基于Hash和Btree，虽然Hash能够实现更快速的查询，但是由于其不能进行范围查询，因此实际应用场景十分有限，日常生产我们一般使用由Btree维护起来的索引，如下是MySQL优化过后Btree的数据结构：

mysql with优化 mysql优化过程_SQL优化流程


原先在没有树结构的情况下，查询效率至多是普通查询算法的时间复杂度，从B+Tree的数据结构可以得知，在树结构上的查询速度由数据总量逐渐向树的高度和每一层树的广度决定。这里我们可以看到树的三层结构使得检索时的磁盘IO次数更少，每一层的数量合适也是决定MySQL支撑千万数据查询的关键，当然，这是MySQL数据库本身为我们带来的优点，我们所要做的大部分工作，就是在编写SQL语句时不要让索引失效。

开启慢查询日志，让问题复现

良好的SQL并不是一蹴而就的，即便是经验丰富的程序员在大量的SQL编写的时候也不免存在索引失效的现象，这就需要通过某种有序的方法去寻找问题的根源——出现问题的SQL语句。

1.首先我们需要打开MySQL的慢查询日志功能
  1. 登录MySQL
set global slow_query_log=1; //默认关闭，平常建议关闭因为损耗性能
  1. 可以设置日志位置，也可以使用默认位置
  2. 利用命令查看慢查询日志是否开启成功
show variables like 'slow_query_log%' //利用该命令可以查看日志是否开启以及日志位置

mysql with优化 mysql优化过程_数据库_02

2.设置慢查询阈值

慢查询日志开启后，日志自然会收集慢SQL，但多久才算慢SQL？这必须有个规定，MySQL慢查询时间默认值是10秒，我们可以通过命令查看

show variables like '%long_query_time%'

mysql with优化 mysql优化过程_mysql with优化_03


我们可以通过手动方式设置慢查询时间，具体命令如下

set long_query_time = 1
3.通过测试收集慢SQL

开启慢查询功能后，我们需要对系统进行完整时间的测试，尽可能将发生问题的场景再现，测试的时间不能太短，尽可能保证测试的可靠性。

发生问题的SQL会记录在慢查询日志当中，由此我们就有一定的依据去进一步分析SQL变慢的原因和进一步优化。

mysql with优化 mysql优化过程_数据库_04

通过explain关键词分析SQL

对于存在问题的SQL，我们对他进行进一步分析，那么分析的方法是什么呢？我们通常用MySQL自带的explain关键字进行分析，实现方法即在SQL语句前加explain关键词：

explain select * from `user`

查询结果如下图所示

mysql with优化 mysql优化过程_mysql with优化_05


接下来我们解释一下几个关键字段的含义

1.id

用来标志本句SQL中各类查询的执行顺序，判定原则有二

  • id不同，先执行大的
  • id相同，从上往下按顺序执行

下面我们示范一个SQL来观察：

EXPLAIN SELECT h.id FROM itrip_hotel h 
LEFT JOIN (SELECT * FROM itrip_comment) c 
ON h.id = c.hotelId

mysql with优化 mysql优化过程_Web_06


以上我们可以看到id分别是1、1、2，因此可以得出执行顺序是：第三句 -> 第一句 -> 第二句，到此我们可以详细地分析出SQL底层的执行顺序是否和我们写SQL的时候执行顺序一致。

2.select_type

该字段用于显示该SQL属于哪种类型的SQL，比如子查询、简单查询等等…

select_type

查询方式（释义）

SIMPLE

简单查询

PRIMARY

出现多重查询的最外层查询（主查询）

UNION

合并查询

DEPENDENT UNION

合并查询中的内层查询

UNIOIN RESULT

存在合并查询的合并结果集，无 id

SUBQUERY

查询结果中的子查询

DEPENDENT SUBQUERY

搜索条件中的子查询

DERIVED

用衍生表当作搜索表查询

以上罗列常见的查询类型，在此层次我们应该尽量避免条件子查询的出现。

3.table

顾名思义，table字段就是描述该SQL对应的搜索表是什么，若是DERIVED类型的查询，具体的表则为DERIVED[id]。

4.type

该字段是判定SQL索引依附条件的重要依据之一，他有很多的值，到mysql5.7为止中=type的类型达到了14种之多，但我们常见的也是可以达到的仅仅只有4种左右，因此我们对这些值进行学习

  • all：出现此关键字就意味着搜索的行为是全表扫描，没有利用到任何索引，可能是未添加索引，也可能是索引失效
  • index：这是另一种形式的全表扫描，但他利用到了有序的索引，理论上的搜索速度比all要好
  • range：该等级是比较常见的索引优化等级，意味着在索引有效的情况下对索引条件进行范围型的查询，例如 >,in,between,like 等关键词
  • ref：查找条件使用了除主键和唯一键(unique)外的索引，并且不是范围查询
  • eq_ref：优化到该连接条件已经是很好的SQL语句，但该等级需要查询条件是主键或唯一键的情况下，获取唯一结果的场景
  • const：主键查询被当作条件查询并且被optimizer转换为常量的情况
SELECT * FROM itrip_hotel WHERE id = 1  //const
  • system：单表中最多只有一条匹配行，类比于系统表

以上便是常见的查询等级，我们从优到劣排序：
system>const>eq_ref>range>index>all(未使用索引)
我们一般可以用type字段判断SQL语句的索引依赖情况以及它是否有进一步被优化的必要，生产中ref等级已经是很理想的SQL，range也是不可避免，但我们理应避免all等级的出现的。

5.possible_keys

MySQL系统初步判断该SQL可能用上的索引名称，当然这可以当作参考，实际情况可能和预测不服，例如出现了意料之外的索引失效等情况。

5.key

实际用到的索引名称，改字段是检查索引依赖有效性的显著条件。

mysql with优化 mysql优化过程_Web_07


如上图所示，MySQL分析器分析该SQL会利用到主键，真实情况也是如此。

6.key_len

该字段可能是这些字段中比较抽象的一个，它代表以字节为单位的索引长度，我们可以通过该指标来判断到底用了几处索引。
key_len计算公式可以参考链接: key_len的计算方式.

7.ref

ref字段是对type中出现的ref等级的具体描述，若type中不为ref，则为null，常见的是[column name]、const和null

8.rows

本此搜索的行数，该指标越小越好

9.Extra

该字段是对额外信息的描述，其中包含很重要的优化根据，常见的有以下几种

Extra

描述

using where

用到了where关键词进行条件查询

using index

查询内容为覆盖索引

using temporary

检索时MySQL自己内部创建临时表

using filesort

MySQL未使用索引排序，而是重新外部排序

当出现using index，则代表SQL效率比较高；切忌出现using temporary和using filesort，若出现这两个中的一个，则该SQL还需要被优化

explain总结

至此，我们已经把explain分析的SQL执行计划学习完毕，在优化方面，我们应该特别关注type、key、rows和Extra字段，其中Extra是对SQL状态的显示提醒，作为一条健康的SQL，我们应该坚决避免内部临时表和外部自排序！

开启Profile

到explain分析后，一般恶行的SQL都可以恢复健康，但仍然还有一些不为人知的隐蔽原因导致SQL运行变慢，比如连接时间和延迟阻塞等，我们要怎样去分析观察？答案就是利用MySQL的Profile功能，它可以完整的查询SQL运行的各阶段时间和资源利用情况，从而进一步定制优化方案

set profiling=1  //开启profile功能
show variables like '%PROFILING%' //查看功能是否开启

打开profile功能后，我们就可以利用它来观察SQL详细的运行状况

show profiles;  //默认15条

mysql with优化 mysql优化过程_MySQL_08


找到需要分析的SQL，利用id进一步分析查询

show profile for query [id]  //id为要分析的SQL语句id

mysql with优化 mysql优化过程_MySQL_09


这张profile表记录了该SQL运行的所有阶段消耗时间（上图未截完整），包括启动、授权、优化分析、执行、清除等等…

除了默认的Duration字段外，还有其他可参考的参数，此处不再一一举例，通过这张表，我们可以观察得到SQL执行速度发生异常的灰色地带，从而考虑新的优化策略。

谈谈锁的影响

MySQL数据库的锁机制是保全数据一致性和可靠性的有效手段，从MyISAM的表锁到InnoDB的行锁，都是为保障数据安全而生。但是加锁无疑是影响数据库查询或修改效率的，对于表锁由于其不会发生死锁，也不是一般优化的对象，因此我们仅对行锁讨论，锁使用不当导致SQL运行异常主要有一下几点：

  1. SQL查询范围内存在断层，导致其他SQL操作时阻塞而无法操作，直到本条SQL操作完毕后才释放行锁，此现象就是间隙锁（Gap Lock），这就要求我们条件查询尽量缩小范围，长期保持数据库断层维护；
  2. 索引失效导致的行锁升级为表锁，所有其他对此表的操作都形成阻塞队列，这种行为是是分隐蔽且具有杀伤力的，我们应该尽量避免索引失效，关于如果避免索引失效，我将在本人的其他文章中再述；
  3. 不同SQL对相同几行的操作时，加行锁不一致导致的死锁情况，这样的结果时SQL最糟糕的情况，他将使系统功能瘫痪，因此在编码设计时如需对数据库进行复杂操作需要谨慎设计和考虑。

基本上常见情况也就是这么多，关于页锁，其实并不常见，它是介于表锁和行锁之间的一种锁，性能中规中矩，也会引发死锁。当然我们可以考虑不上锁，利用乐观锁的思想，用版本号或CAS算法形成无锁的访问状态，但它并并不适合高并发情况，除非用队列等其他解决方案，因此性能和安全是矛盾的状态，正如算法中时间复杂度和空间复杂度不能兼优的概念是类似的。

小结

生产中我们或多或少会碰到系统优化的情况，如何优化？我们得理性得分析并从恰当得切点入手，SQL优化是软件优化的一部分，但并非所有的系统优化都要牵扯到SQL优化，毕竟测试都是要花费人力和物力的，合理的判断是节省资金的好办法。关于SQL优化，我们可以跟着常规的分析流程来，整理如下：
1.慢查询日志，生产问题复现 -> 2.explain分析SQL执行计划 -> 3.进行人工SQL优化（包括索引和锁） -> 4.开启profile功能，精准定位慢SQL -> 5.分析SQL各环节执行状态 -> 6.进一步优化连接、网络等等 -> 7.再次复查，直到系统健康运行