Question

查看系统性能参数

SHOW STATUS LIKE '参数'

常用的性能参数:

Connections:连接 MySQL 服务器的次数
Uptime:MySQL 服务器上线事件
Slow_queries:慢查询次数
Innodb_rows_read:已 select 的行数
Innodb_rows_inserted:已 inserted 的行数 Innodb_rows_updated:已 updated 的行数 Innodb_rows_deleted:已 deleted 的行数
Com_select:查询操作的次数
Com_insert:插入操作的次数
Com_update:更新操作的次数
Com_delete:删除操作的次数

查看上一条 SQL 语句的查询成本

SHOW STATUS LIKE 'last_query_cost'

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论:

1. 位置决定效率 。如果页就在数据库 缓冲池 中，那么效率是最高的，否则还需要从内存或者 磁盘 中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2. 批量决定效率 。如果我们从磁盘中对单一页进行 随机读取 ，那么效率是很低的（差不多 10ms），而采用 顺序读取 的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到 I/O 并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到 缓冲池 中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

慢查询日志

MySQL 的慢查询日志，用来记录在 MySQL 中 响应时间超过阈值 （即 long_query_time ，默认为 10s ）的语句。

慢查询日志默认为关闭状态，需要手动开启，支持持久化到文件。

建议在测试环境中开启，正式环境中关闭，因为该功能会影响性能 。

补充说明:

除了 long_query_time 外，还有一个系统变量 min_examined_row_limit ，表示查询扫描过的最小记录数。

当一条查询扫描的记录数大于 min_examined_row_limit 同时查询的执行事件超过 long_query_time ，那么这个查询就会被记录到慢查询日志中。

这个值默认是 0 ，可以在 my.ini/my.cnf 中修改，也可用 SET 命令修改。

修改慢查询配置

查看慢查询是否开启

SHOW VARIABLES LIKE 'slow_query_log';

查看慢查询日志位置

SHOW VARIABLES LIKE 'slow_query_log_file';

开启慢查询

SET GLOBAL slow_query_log='ON';

查看慢查询阈值

show variables like '%long_query_time%';

修改慢查询阈值

#测试发现:设置 global 的方式对当前 session 的 long_query_time 失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql> set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

查看慢查询数量

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查询日志分析

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

参数:
-s 表示按照何种方式排序
    c 访问次数
    l 锁定时间
    r 返回记录
    t 查询时间
    al 平均锁定时间
    ar 平均返回记录数
    at  平均查询时间
-t 返回前面多少条数据
-g 后边搭配一个正则匹配模式，大小写不敏感

可能会出现的报错:

mysqldumpslow: command not found...

原因:系统默认去 /usr/bin 下查找命令

解决方法:找到 mysql 的安装目录下的 /bin/mysqldumpslow

执行 ln -s /www/server/mysql/bin/mysqldumpslow /usr/bin

重置慢查询日志

mysqladmin -uroot -p flush-logs slow

EXPLAIN 分析查询语句

参考: mysql explain 详解 - 天下没有收费的 bug - 博客园 (cnblogs.com)

语法

EXPLAIN 查询语句

EXPLAIN 语句输出的各个列的作用如下

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

• id

  SELECT 识别符（执行顺序的标识）。

  1. id 值越大优先级越高，越先被执行
  2. id 相同时，执行顺序由上至下
• select_type
  • SIMPLE(简单 SELECT，不使用 UNION 或子查询等)
  • PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的 select 被标记为 PRIMARY)
  • UNION(UNION 中的第二个或后面的 SELECT 语句)
  • DEPENDENT UNION(UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询)
  • UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有 select)
  • SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)
  • DEPENDENT SUBQUERY(子查询中的第一个 SELECT，依赖于外部查询)
  • DERIVED(派生表的 SELECT, FROM 子句的子查询)
  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
• table

  显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的 e，d，也可能是第几步执行的结果的简称

• type

  对表的访问方式，表示 mysql 在表中找到所需行的方式，又称访问类型。

  • ALL

    不用索引，遍历全表（效率最低）

  • index

    使用索引，遍历全表（效率还是很低）

    select count(*) from user;

  • range

    范围查询，用在 between/like/<= 等等

    select * from user where user_name like 'test';

  • ref

    通过普通的二级索引进行等值查询（普通指的是不唯一索引，因此查询结果可能有多条）

    select * from user where user_name = 'test';

  • ref_or_null

    与 ref 类似，条件中多了一个 is null 判断

    select * from user where address = 'test' or address is null;

  • eq_ref

    连表查询时，连接的条件是 主键 或 唯一二级索引 ，这是连表查询中连接效率最高的

    select * from user join department on department.user_id = user.id;

  • const

    根据 主键 或 唯一二级索引 与常数进行等值匹配

    select * from user where id = 100;

  • system

    const 的特例，当查询的表只有一条数据时，用 system（innodb 不支持）

  结果从好到坏: 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_key 表示可能用到的索引

  key 表示实际用到的索引

• key_len

  实际使用的索引的长度

• ref

  查询条件的类型

• rows

  预计需要读取的行数（越小越好）

• filtered

  某个表经过搜索条件过滤后剩余记录条数的百分比

• extra

  记录了一些额外的信息来帮助理解 MySQL 实际查询的过程

EXPLAIN 输出格式

EXPLAIN FORMAT=<xxx> ...

• 传统格式
• JSON 格式（最详细）
• TREE 格式（8.0.16+）
• 可视化输出（Mysql workbench）

分析优化执行计划:trace

# 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
# 设置大小
set optimizer_trace_max_mem_size=1000000;
# 使用
select * from student where id < 10;
select * from information_schema.optimizer_trace\G

MySQL 监控分析视图 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;