「MySQL调优」大厂MySQL性能优化实战讲解

Why Performance

在1990s，人们还使用拨号接入互联网的时候，浏览一个网页或加入一个线上聊天室需要几分钟的时间去加载是一件很正常的事情。而2009年Akamai公司的报告显示，如果一个网页的加载时间超过3秒钟，那么40%的用户将会放弃访问。同时网页的加载速度也和很多情况下的网站收入正相关，2006年亚马逊报告显示每超过100ms的网页加载延迟将会降低1%的网站收入，同时2008年google也统计到如果搜索时间从400ms降低到900ms，那么谷歌将会失去20%的广告收入。由此可见，提升网站性能对于业务收入和用户留存将会产生重大的影响，这也就是为什么越来越多的人在关注网站性能。

MySQL Performance

一个网站或APP的性能取决于多种因素，请求从前台发送到后台，经过数据库查询，数据处理，多系统集成交互，返回到前台再通过渲染呈现给客户，这其中任意一环出现问题都将会对客户体验造成极大的影响。常见的影响诸如大量的5XX错误访问，页面加载超时，事务阻塞。而在部分情况下，我今天所要说的MySQL服务器性能，成为了瓶颈。比如说我当前的项目

How To Optimize MySQL

优化MySQL，就不得不说起MySQL优化模型，根据优化成本和优化效果，一个金字塔结构可以被绘制出来。

可以看到，从下到上的优化成本越来越高，而优化的效果反而越来越低。

比如针对一个没有索引的数据表进行数据查询，由于没有索引，所以查询任何一条数据时都需要进行全表扫描，我们可以通过使用更快的处理器，高速SSD等方式进行优化，这将会付出大量的金钱并且收效甚微。当然我们也可以更简单的，为查询筛选字段构建一条索引，以最低的成本实现常数级的查询速度（最优状况）。

在接下来的文章中，我将会逐层为大家带来优化的方法，同时也会讨论一些优化以外的原理。

SQL语句优化

对于SQL语句优化，是最常见也是效果最好、成本最低的一种优化方式。

通常情况下，优秀的SQL将会以最快的速度，最小的消耗返回最完整（且没有冗余）的信息。

原理

从原理上来看，我们如果要监控SQL语句的执行情况，可以通过MySQL自带的慢查询分析工具。

通过执行如下SQL：

-- 查看是否开启慢查询日志
SHOW variables like 'slow_query_log';
-- 查看是否在慢查询日志中记录未使用到索引的查询计划
SHOW variables like 'log_queries_not_using_indexes';
-- 开启非索引查询计划记录到慢查询
SET GLOBAL log_queries_not_using_indexes = ON;
-- 开启慢查询日志
SET GLOBAL slow_query_log=ON;
-- 慢查询记录本地保存位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 慢查询限定时间(s)，超过该时间认为是慢查询
SHOW VARIABLES LIKE 'long_query_time';
-- 设置慢查询限定时间为3(s)
SET GLOBAL long_query_time=3;
-- 日志存储方式，支持FILE和TABLE，可以同时共存
SHOW VARIABLES LIKE 'log_output';
-- 设置日志存储到本地文件，因为保存到mysql.slow_log表会消耗更多的系统资源
SET GLOBAL log_output='FILE';

  
 

同时需要注意的是，以上的所有修改都是只针对当前数据库生效，如果数据库发生了重启，以上配置都可能会失效。如果要使有关慢查询的相关配置永久生效，就必须修改数据库配置文件my.cnf（或其他配置文件，这里不展开赘述MySQL的多种配置方式）。

实践

用最简单的方式生成一个慢查询结果：

select sleep(4);

  
 

这时候直接查看慢查询日志输出结果：

/usr/sbin/mysq