MySQL 性能分析

• 当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

1. 查看SQL执行频率

• MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

show status like 'Com_______';  -- 查看该session`Com`开头的指令的执行频率
show global status like 'Com_______';  -- 查看整个数据库`Com`开头的指令的执行频率

很神奇的是，我们关键的几个指令，都是六个字母的

show status like 'Innodb_rows_%';  -- Innodb存储引擎相关的操作数

2. 定位低效率执行SQL

• 可以通过以下两种方式定位执行效率较低的 SQL 语句。
• 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
• show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
  

id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db列，显示这个进程目前连接的是哪个数据库
command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接 （connect）
time列，显示这个状态持续的时间，单位是秒
state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一 个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info列，显示这个sql语句，是判断问题语句的一个重要依据

3. explain分析执行计划

• 重要方法！！！
• 可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

  explain select * from combinative_city_view;
  

3.2 explain 之 id

• id 相同表示加载表的顺序是从上到下

  explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;
  

• id 不同id值越大，优先级越高，越先被执行

  EXPLAIN
  SELECT *
  FROM t_role
  WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
  

先执行括号最里面的，然后执行括号里面的，最后执行外面的。一开始用了一个唯一索引，然后用了一个外键（捆绑其他索引），最后用了一个主键索引。效率很高，都分别只查了一行。

• id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

  EXPLAIN
  SELECT *
  FROM t_role r,
       (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a
  WHERE r.id = a.role_id;
  

最先执行括号里面的子查询，通过一个外键查询。之后，让主键索引和之前查询出来的结果的id进行一个匹配。

3.3 explain 之 select_type

3.4 explain 之 table

• 展示这一行的数据是关于哪一张表的

explain 之 type

• type 显示的是访问类型，是较为重要的一个指标，可取值为
  
• 性能好坏的排序是，从上到下越来越不好。
• 一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref

3.6 explain 之 key

• possible_keys : 显示可能应用在这张表的索引， 一个或多个。
• key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
• key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前 提下， 长度越短越好。

3.7 explain 之 rows

• 扫描行的数量

3.8 explain 之 extra

4. show profile分析SQL

• Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时
  帮助我们了解时间都耗费到哪里去了。
• 首先，通过 have_profiling 参数，能够看到当前MySQL是否支持profile
  
• select @@profiling查看是否开启，默认不开启
  
• 设置开启

  set profiling=1; //开启profiling 开关；
  

• 测试语句耗时

  SET profiling=1;
  
  SHOW DATABASES;
  SHOW TABLES;
  SELECT * FROM tb_user WHERE id < 5;
  SELECT COUNT(1) FROM tb_user;
  
  SHOW PROFILES;
  

  
• 根据Query_ID 查某个语句的每个部分耗时

  -- show profile for query query_id
  SHOW PROFILE FOR QUERY 6;
  

trace 分析优化器执行计划

• MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
• 打开trace，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示

  SET optimizer_trace="enabled=on",end_markers_in_json=on; 
  set optimizer_trace_max_mem_size=1000000;
  

• 执行sql语句

  select * from tb_item where id < 4;
  

• 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的

  select * from information_schema.optimizer_trace\G;