一、数据库服务器的优化步骤

        整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使 用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

mysql 性能 分析 sql mysql性能分析命令_MySQL

 二、查看系统参数

SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。

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：删除操作的次数。

三、统计SQL的查询成本

SHOW STATUS LIKE 'last_query_cost'; #显示最后一次查询用了多少个页

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

#1. 开启slow_query_log
    set global slow_query_log='ON';

#2. 修改long_query_time阈值
    show global variables like '%long_query_time%';
    set global long_query_time = 1;

#3. 查看慢查询数目
    SHOW GLOBAL STATUS LIKE '%Slow_queries%';

#4. 使用慢查询日志分析工具：mysqldumpslow

mysqldumpslow 命令的具体参数如下：
    -a: 不将数字抽象成N，字符串抽象成S
    -s: 是表示按照何种方式排序：
        c: 访问次数
        l: 锁定时间
        r: 返回记录
        t: 查询时间
        al:平均锁定时间
        ar:平均返回记录数
        at:平均查询时间 （默认方式）
        ac:平均查询次数
    -t: 即为返回前面多少条的数据；
    -g: 后边搭配一个正则匹配模式，大小写不敏感的；

#举例
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log

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

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

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

#5.关闭慢查询日志
     set global slow_query_log='OFF';

#6.删除慢查询日志
        show variables like 'slow_query_log%'; #手动进入目录删除

五、查看SQL执行成本-SHOW PROFILE

#开启
    show variables like 'profiling';
    set profiling = 'ON';

#然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

    show profiles;

#如果我们想要查看最近一次查询的开销，可以使用：
    show profile;

#可以查看指定 query id 的开销，以及不同类型的开销，例如 query id 为 2的
show profile cpu,block io for query 2;

show profile的常用查询参数：

① ALL：显示所有的开销信息。

② BLOCK IO：显示块IO开销。

③ CONTEXT SWITCHES：上下文切换开 销。

④ CPU：显示CPU开销信息。

⑤ IPC：显示发送和接收开销信息。

⑥ MEMORY：显示内存开销信 息。

⑦ PAGE FAULTS：显示页面错误开销信息。

⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。

⑨ SWAPS：显示交换次数开销信息。

六、分析查询语句-EXPLAIN

EXPLAIN SELECT select_options

或者

DESCRIBE SELECT select_options

# MYSQL 5.6.3 以后就可以使用 EXPLAIN SELECT/UPDATE/DELETE xxx

 6.1 输出结构（略）  

  输出各列的结构：

mysql 性能 分析 sql mysql性能分析命令_mysql_02

 select_type:

        一个大的查询语句可能包含若干SELECT关键字，这个属性就表明每个SELECT子句在整个大查询中的角色。

mysql 性能 分析 sql mysql性能分析命令_mysql_03

 *type（访问方法）:

        system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见蓝 色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

6.2 四种输出格式

6.2.1 传统格式

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
s2.common_field IS NOT NULL;

mysql 性能 分析 sql mysql性能分析命令_mysql 性能 分析 sql_04

 6.2.2 JSON格式

EXPLAIN FORMAT=JSON SELECT ....

6.2.3 TREE格式

EXPLAIN FORMAT=tree SELECT ....

6.2.4 可视化输出

        可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图 标，即可生成可视化的查询计划。

七、分析优化器执行计划-trace

SET optimizer_trace="enabled=on",end_markers_in_json=on; #开启
set optimizer_trace_max_mem_size=1000000; #内存限制

开启后，可分析如下语句：

        SELECT

        INSERT

        REPLACE

        UPDATE

        DELETE

        EXPLAIN

        SET

        DECLARE

        CASE

        IF

        RETURN

        CALL

#执行语句后 ，查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：

select * from information_schema.optimizer_trace;

八、MySQL监控分析视图-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开头，展示等待事件的延迟情况。

#索引情况
    #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;