MySQL调优 LuminousCL

03 Oct 2020 5150words 18min times 数据库
Thank you for your support :)
CC BY 4.0 （Attribution 4.0 International）

Respect the present, and control the reality.

​ - Christopher Nolan


影响MySQL性能的因素

  • 业务需求对于MySQL的影响
  • 存储定位对于MySQL的影响
  • 系统各种配置及规则数据
  • 活跃用户的基本信息数据和个性化定制信息数据
  • 准实时的统计信息数据
  • 二进制多媒体数据
  • 流水队列数据
  • 超大文本数据
  • 需要放入缓存的数据
  • Schema设计对系统的性能影响
  • 优化（减少）对数据库访问的请求
  • 优化（减少）无用数据的查询请求
  • 优化硬件环境


一、性能分析

MySQL Query Optimizer

MySQL Query Optimizer是在MySQL中专门负责优化SELECT 语句的优化器模块，它的主要功能为：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划。

当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimize r时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值；并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等；然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见性能瓶颈

CPU： CPU在饱和的时候一般发生在数据存入内存或葱磁盘上读取数据的时候

I/O： 磁盘I/O瓶颈发生在存入数据量远大于内存容量的时候

服务器硬件： 可以通过top, free, iostat和vmstat来查看系统性能的状态

MySQL性能下降原因分析

  • 查询语句写法造成性能下降
  • 索引失效（单值，复合）
  • 关联查询有太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲，线程数等）

MySQL常见性能分析手段

性能瓶颈定位

通过show命令查看MySQL状态及变量，找到系统瓶颈：

Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL语句，会被记录到慢查询日志中。

  • long_query_time的默认值为10，意思是运行时间超过10s以上的语句
  • 默认状态下MySQL数据库没有开启慢查询日志，需要手动设置参数开启

查看开启状态

SHOW VARIABLES LIKE '%slow_query_log%'

开启慢查询日志

mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;

永久配置修改配置文件my.cnf或my.ini, 在[mysqld]一行下面加入两个配置参数

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3

注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time = 2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。

Exlain关键字（执行计划）

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，进而分析查询语句或表结构的性能瓶颈，包括的功能如下：

  • 查看表的读取顺序
  • 查看数据读取操作的操作类型
  • 查看哪些索引可以使用
  • 查看哪些索引被实际使用
  • 查看表与表之间的引用
  • 查看每张表有多少行被优化器查询

步骤：

  • Explain + SQL语句
  • 执行计划包含的信息（如果有分区表的话还会有partitions）

各字段解释：

  • id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）:

    id相同，执行顺序从上往下;

    id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行;

    id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

  • select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

  • SIMPLE ：简单的select查询，查询中不包含子查询或UNION

  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY

  • SUBQUERY：在select或where列表中包含了子查询

  • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里

  • UNION：若第二个select出现在UNION之后，则被标记为UNION;

    若UNION包含在from子句的子查询中，外层select将被标记为DERIVED

  • UNION RESULT：从UNION表获取结果的select

  • table（显示这一行的数据是关于哪张表的）

  • type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

    一般来说，得保证查询至少达到range级别，最好到达ref

  • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现

  • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量

  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

  • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体

  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引

  • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

  • ALL：Full Table Scan，将遍历全表找到匹配的行

  • possible_keys: 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用

  • key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

  • Extra: 包含不适合在其他列中显示但十分重要的额外信息

Show Profile分析查询

Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。


二、性能优化

索引优化

索引优化准则

  • 尽量使用全值匹配
  • 最左匹配法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select
  • is null, is not null 也无法使用索引
  • like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)。like以通配符开头(‘%abc…‘)索引失效会变成全表扫描操作
  • 字符串不加单引号索引失效
  • 少用or，用它来连接时会索引失效
  • <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
  • 少用Hint强制索引

查询优化

永远小表驱动大表（小的数据集驱动大的数据集）

slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in优于exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`

order by关键字优化

  • order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
  • MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低
  • ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
  • 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
  • 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序
  • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序
  • 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
  • 优化策略
  • 增大sort_buffer_size参数的设置
  • 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

  • group by实质是先排序后进行分组，遵照索引建的最佳左前缀
  • 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
  • where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择：

  • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
  • 尽量避免NULL：通常情况下最好指定列为NOT NULL