MySQL 性能调优

一. MySQL调优金字塔

往上走，成本越高；往下走，成本越低。

1. 架构调优

在系统设计时，先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去；然后考虑写的并发量有多大、是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。所以在进行优化时，首先需要关注和优化的应该是架构。如果架构不合理，即使DBA能做的事情其实也是比较有限的。

2. MySQL调优

需要确认业务表结构设计是否合理，SQL语句优化是否足够，索引设计是否规范，是否可以剔除无用的索引。

3. 硬件和OS调优

在这个层次，需要对硬件和OS有非常深刻的了解。

仅仅就磁盘一项来说，一般非DBA能想到的调整就是SSD盘比用机械硬盘更好。DBA级别考虑包括：

1. 使用什么样的磁盘阵列（RAID）级别。
2. 是否可以分散磁盘IO。
3. 是否使用裸设备存放数据
4. 使用那种文件系统（目前比较推荐的是XFS）。
5. 操作系统的磁盘调度算法选择
6. 是否需要调整操作系统文件管理方面比如atime属性等。

4. 小结

这里面的调优侧重于MySQL调优，特别是索引。SQL/索引调优要求对业务和数据流非常清楚。阿里巴巴内部，三分之二的DBA是业务DBA，从业务需求讨论到表结构审核、SQL语句审核、上线、索引更新、版本迭代升级。甚至哪些数据应该放到非关系性数据库中、哪些数据放到数据仓库、搜索引擎或缓存中，都需要这些DBA跟踪和复审。他们可以称为数据架构师（Data Architecher）

二. 查询性能调优

前面的章节我们知道如何设计最优数据库的表结构，如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够，还需要合理的设计查询。如果查询写的很糟糕，即使库表结构在合理，索引在合适，也无法实现高性能。

三. 慢查询

1. 概念

慢查询日志，顾名思义，就是查询花费大量时间的日志。指MySQL记录所有执行超过了long_query_time参数设定的时间阀值的SQL语句的日志。

-- 默认10s
show variables like 'long_query_time' 

该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

在后续慢查询配置中会提及如何配置。

2. 优化数据访问

查询性能效率低基本原因是访问数据太多。

大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

对于低效的查询，一般通过下面2个步骤来分析：

1. 确认应用程序是否在检索大量超过需要的数据。这意味着访问了太多的行，但有时候也可能是访问了太多了列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

3. 请求了不需要数据，怎么做

有些查询请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这回给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

比如：

1. 查询不需要的记录。

   一个常见的错误是常常会误以为MySQL会值返回需要的数据，实际上MySQL却是先返回全部的结果集在进行计算。
   
   就比如
   `select * from a limit 10000,10;`
   
   这个SQL会查询出全部的结果集10010条数据，客户端的应用程序会接受全部的结果集集数据，然后抛弃其中大部分数据。
   

2. 总是取出全部列。

   `select * from a`
   需要返回全部数据么，其实大部分情况是不需要的，取出全部列，对于非聚簇索引来说，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O，内存和CPU消耗。
   
   因此，一些DBA是严格禁止 `select*`写法的。
   
   - 什么时候可以使用`select *`？
   应用程序采用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能尤其好处。但代价要时刻铭记。
   获取并缓存所有列的查询，相比多个独立的只获取不分裂的查询可能就更有好处。
   

3. 重复查询相同的数据

   不断的重复执行相同的查询，然后每次都返回完全相同的数据。
   比较好的方案：缓存。
   

4. 是否在扫描额外的记录

在确定只返回需要的数据以后，接下来应该关注查询返回的结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

响应时间，扫描行数，返回行数。

1. 响应时间

   响应时间 = 服务时间 + 排队时间
   
   服务时间：数据库处理查询花费的时间。
   
   排队时间：服务器因为等待某些资源而没有真正执行查询的时间。
   比如等I/O操作，等行锁等。
   

2. 扫描行数和返回行数

   扫描行数能够说明该查询查找所需数据等效率高不高。
   
   理想情况下，
   扫描行数=返回行数。
   
   现实情况下，
   列如在做一个关联查询，服务器必须要扫描多行才能生成结果集中的一行。扫描行数和返回行数的比率通常非常低，一般在1:1和10:1之间，甚至会更大。
   

3. 扫描行数和访问类型

   	在评估查询开销的时候，需要考虑从表中找到某一行数据的成本。MySQL有很多种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才返回一行结果，也有些访问方式可能无序扫描就能返回结果
   
   在`EXPLAIN`语句中的`type`反应了访问类型。
   访问类型有很多种，下面的顺序方向，速度由慢到快
   全表扫描->索引扫描->范围扫描->唯一索引查询->常数引用等。
   
   如果查询没有办法找到合适的访问类型，解决的办法通常是增加一个合适的索引。
   
   - 为什么索引查询对于查询优化如此重要？
   索引让那个MySQL以最高效，扫描行数最少的方式查找所需记录。
   
   - 一般MySQL使用查询条件，从好到坏：
   1.利用索引过滤不匹配记录。这时在存储引擎层完成。
   `select * from table where a > 1 and a < 10`
   2.使用覆盖索引。这是在MySQL服务器层完成的，无需回表。
   3.从数据表中返回数据（存在回表），然后过滤不满足条件的记录。这也在MySQL服务器层完成，MySQL需要先从数据表读记录在过滤。
   
   好的索引可以让查询使用合适的访问类型，尽可能只扫描需要的数据行。
   
   - 如果发现查询扫描大量数据但返回少数行，怎么优化？
   1. 使用覆盖索引，存储引擎无需回表了。
   2. 该表库表结构，例如使用单独的汇总表。
   3. 重写复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。
   

5. 慢查询相关配置 & 日志位置

慢查询日志可以帮助定位可能存在问题的SLQ语句，从而进行SQL语句层面的优化。

但默认是关闭的。

show variables like 'slow_query_log'

现在进行手动开启慢查询日志

set global slow_query_log = on;
-- 或
-- 0-关闭 1-开启
set global slow_query_log = 1;

设置慢查询阀值时间

-- 超过5s都是慢sql
set global long_query_time = 5;

同时对于运行的SQL语句没有使用索引，MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，控制参数是

-- 默认是关闭的
show variables like 'log_queries_not_using_indexes'

-- 现在进行开启
set global log_queries_not_using_indexes = on;

慢查询日志路径

show variables like 'slow_query_log_file';

6. 小结

1. slow_query_log启动/停止慢查询日志。
2. slow_query_log_file指定慢查询日志的存储路径及文件（默认和数据文件放一起）。
3. long_query_time指定记录慢查询日志SQL执行时间的阀值（单位：秒，默认10s）。
4. log_queries_not_using_indexex是否记录未使用索引的SQL。
5. log_output日志存放的地方，值为TABLE或者FILE，默认FILE

四. 查询优化器

一条SQL执行过程如下

1. 如果是select语句，查询缓存是否已有相应结果，有则返回，无则下一步（其他语句也同样调到下一步）。

2. 解析查询，创建一个内部数据结构，也叫做解析树。解析树主要用来SQL语句语义与语法解析。

3. 优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询到，查询服务器优化器是如何进行优化到，便于用户重构查询和修改相关配置，达到最优。 这一阶段还涉及存储引擎。优化器会询问存储引擎。比如某个操作的开销信息。是否对特定索引有查询优化等。

五. 实现调优手段

可看之前我编写的文章博客

1. 执行计划
2. 高性能索引创建策略
3. 高性能索引使用策略