SQL调优篇

大方向优化步骤

  • 整个流程分化为观察（Show status）和行动（Action）两个部分
    1. 观察服务器状态，如果存在周期性波动，则加缓存，
    2. 仍有不规则延迟或卡顿，则开启慢查询，EXPLAIN SHOW PROFILING
      1. SQL等待时间长
        1. 调优服务器参数
      2. SQL执行时间长
        1. 索引设计优化
        2. JOIN表过多，需要优化
        3. 数据表设计优化
    3. 都没解决
      1. 读写分离
      2. 分库分表

查看系统性能参数

  • 在MySQL中，可以使用 SHOW STATUS 语句查看一些MySQL数据库服务器的性能参数、执行频率
  • SHOW STATUS 语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
  • 一些常用的性能参数：
    • Connections：连接MySQL服务器的次数
    • Uptime：MySQL服务器的上线时间
    • Slow_queries：慢查询的次数
    • Innodb_rows_read：Select 查询返回的次数
    • Innodb_rows_inserted：执行INSERT操作插入的行数
    • Innodb_rows_updated：执行UPDATE操作更新的行数
    • Innodb_rows_deleted：执行DELETE操作删除的行数
    • Com_select：查询操作的次数
    • Com_insert：插入操作的次数。对于批量插入的INSERT操作，只累加一次
    • Com_update：更新操作的次数
    • Com_delete：删除操作的次数
    • last_query_cost：统计SQL的查询成本

定位执行慢的SQL：慢查询日志

  1. 开启慢查询日志参数
set global slow_query_log='ON';
  1. 查看慢查询日志是否开启，以及慢查询日志文件的位置
show variables like '%slow_query_log%';
  • 如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下，如果不指定文件，则默认为hostname-slow.log
  1. 查看long_query_time阈值
show variables like '%long_query_time%';
  1. 设置long_query_time阈值
set global long_query_time = 1;
  1. 查看慢查询数据
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
  1. 配置文件中一并设置参数（永久设置）
    1. 修改my.cnf文件
    2. 在[mysqld]下增加或修改参数，然后重启服务器
[msqld]
slow_query_log=ON #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/sry.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE ##输出到文件

慢查询日志分析工具：mysqldumpslow

  • MySQL提供了日志分析工具：mysqldumpslow
  • 查看mysqldumpslow的帮助信息
mysqldumpslow --help
  • 具体参数如下：
    • -a：不将数字抽象成N，字符串抽象成S
    • -s：是表示按照何种方式排序
      • c：访问次数
      • l：锁定时间
      • r：返回记录
      • t：查询时间
      • al：平均锁定时间
      • ar：平均返回记录数
      • at：平均查询时间（默认方式）
      • ac：平均查询次数
    • -t：为返回前面多少条的数据
    • -g：搭配正则表达式
  • 工作常用参考：
##得到返回记录最多的10个SQL
mysqldumpslow -s r -t 10 ../*.log

##得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 ../*.log

##得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" ../*.log

##建议配合 more 使用，防止爆屏
mysqldumpslow -s r -t 10 ../*.log | more

删除慢查询日志

  1. rm 删除该文件
  2. 重置日志文件
mysqladmin -uroot -p flush-logs slow

查看SQL执行成本：SHOW PROFILE

  1. 查看是否开启 PROFILE
show variables like 'profiling';
  1. 开启
set profiling = 'ON';
  1. 查看当前会话的profiles
show profiles
  1. 查看具体某个sql的profile
show profile cpu,block io for query 2;
  1. 常用参数
    1. ALL：显示所有的开销信息
    2. BLOCK IO：显示块 IO 开销
    3. CONTEXT SWITCHES：上下文切换开销
    4. CPU：显示CPU开销信息
    5. IPC：显示发收和接收开销信息
    6. MEMORY：显示内存开销信息
    7. PAGE FAULTS：显示页面错误开销信息
    8. SOURCE：显示和Source_function,Source_file,Source_line相关的开销信息
    9. SWAPS：显示交换次数开销信息

分析查询语句：EXPLAIN

基本语法

EXPLAIN SELECT select_options

EXPLAIN各个列作用

列名描述
id对应的唯一id
select_typeSELECT关键字对应的查询的类型
table表名
partitions匹配的分区信息
type针对单表的访问方法
possible_keys可能用到的索引
key实际上使用的索引
key_len实际使用的索引长度
ref当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows预估的需要读取的记录条数
filtered某个表经过搜索条件过滤后剩余记录条数的百分比
Extra一些额外的信息

table：

​ Mysql规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着表的表名

id：

  • id如果相同，可以认为是一组，从上往下顺序执行
  • 在所有组中，id值越大，优先级越高，越先执行
  • 关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好

select_type：

名称描述
SIMPLESimple SELECT（not using UNION or subqueries）
PRIMARYOutermost SELECT
UNIONSecond or later SELECT statement in a UNION
UNION RESULTResult of a UNION
SUBQUERYFirst SELECT in subquery
DEPENDEDT SUBQUERYFirst SELECT in subquery,dependent on outer query
DEPENDENT UNIONSecond or later SELECT statement in a UNION,dependent on outer query
DERIVEDDerived table
MATERIALIZEDMaterialized subquery
UNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and mast be re-evaluated for each row of the outher query
UNCACHABLE UNIONThe second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

partitions：

  • 分区

type:

  • 完整的访问如下：system，const，eq_ref,ref，fulltext，ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL
    • system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
    • const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
    • eq_ref： 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该驱动表的访问方法就是 eq_ref
    • ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref
    • ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值可以是NULL值，那么对该表的访问方式就可能是 ref_or_null
    • index_merge：将索引合并的方式进行查询
    • unique_subquery：是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列就是 unique_subquery
    • index_subquery：该类型类似于unique_subquery.
    • range：如果使用索引获取某些 范围区间 的记录，那么就可能使用到 range 访问方法
    • index：可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index
    • ALL：全表扫描
  • 总结：这些访问方法除了ALL这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法最多只能用到一个索引。
  • 小结：
    • 结果值从最好到最坏依次是：
    • system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    • SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是consts级别

possible_keys 和 key：

  • possible_keys：表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些
  • key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

key_len：

  • 帮你检查是否充分的利用上了索引，值越大越好，主要针对于联合索引，有一定的参考意义
  • 计算公式：utf8-三个字节，Null-1个字节，变长字段-2个字节

ref：

  • 当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows：

  • 预估的需要读取的记录条数

filtered：

  • 某个表经过搜索条件过滤后剩余记录条数的百分比
  • 对于单表查询来说，这个filtered列的值没有什么意义，我们更需要关注驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数

Extra：

  • 一些额外的信息，可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句
    • No tables used
    • Impossible WHERE
    • Using WHERE
    • No matching min/max row
    • Using index
    • Using index condition
    • Using join buffer(Block Nested Loop)：基于块的嵌套循环算法
    • Not exists
    • Using intersect(...)、Using union(...)和Using sort_union(...)
    • Zero_limit
    • Using filesort
    • Using temporay

EXPLAIN小结

  • EXPALIN不考虑各种Cache
  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • 部分统计信息是估算的，并非精确值

EXPLAIN进一步使用

  • 有四种输出格式
    • 传统格式
    • JSON格式：在EXPALIN和真正的查询语句中间加上 FORMAT=JSON。这个格式可以输出成本
    • TREE格式
    • 可视化输出

SHOW WAIRINGS的应用

  • 在使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息

分析优化器执行计划：trace

  • OPTIMIZER_TRACE 是 MySQL 5.6 引入的一项跟踪功能，它可以跟踪优化器做出的各种决策，并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中
  • 此功能默认关闭。
SET optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

MySQL监控分析视图-sys schema

  • 关于MySQL的性能监控和问题诊断，我们一般从performance_schema 中去获取想要的数据，新增了sys schema,它将performance_shcema 和 information_schema 中的数据以更容易理解的方式总结归纳为“视图”，其目的是为了 降低查询 performace_schema 的 复杂度

Sys schema 视图摘要

  1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
  2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
  3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
  4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
  5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
  6. 表相关：以schema_table开头的视图，展示了表的统计信息。
  7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
  8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
  9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
  10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

Sys schema视图使用场景

  • 索引情况
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;
  • 表相关
# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';
  • 语句相关
#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
  • IO相关
#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;
  • Innodb 相关
#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;
  • 风险提示：通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。生产不要频繁查询sys或者performace_schema、information_schema 来完成监控，巡检等工作。