mysql优化分析expalian和show profiles

mysql 对于大家来说都是不陌生的，也和我们的工作息息相关，一个好的程序员，mysql 优化也是其必备的知识之一。

首先，我们先来学习一下查看mysql 命令的执行的频率。show status 命令是我们必须了解的命令。这个命令·用来查看各种sql 执行的频率。
show [session|global] status
session : 表示当前连接会话的执行各种命令的频率
global:自数据库上次启动至今的统计结果。

左边就是执行的语句类型，value 表示执行的次数
常见的几种类型解析：
Com_xxx表示每一个xxx 语句执行的次数
Com_select :表示操作select 语句查询的次数
Com_insert:表示操作insert 语句的次数
Com_commit:表示事务提交情况
Com_rollback:表示事务回滚的情况
connections:表示试图连接Mysql 服务器的次数
Update:服务器工作的时间
Show_queries：慢查询的次数

一下存在几个只针对InnoDB 存储引擎的：
Innodb_rows_read : select 查询返回的行数
Innodn_rows_insert: 执行插入操作插入的行数
Innodb_rows_update :执行更新操作更新的行数
Innodb_rows_deleted:执行deleted 操作删除的行数

1、explain 命令可以查看执行的sql 语句使用的类型
使用explain 命令结果的参数：

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

type:表示的该查询的连接类型，他的类型有：
最为常见的扫描方式有：
system：系统表，少量数据，往往不需要进行磁盘IO；
const：常量连接；
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
ref：非主键非唯一索引等值扫描；
range：范围扫描；
index：索引树扫描；
ALL：全表扫描(full table scan)；

性能从上到下变一次变差；
system > const>er_ref > ref > range>index > all
2、当时用explain 还没分析出问题，需要使用show profiles 命令继续分许
（1）查看数据库是否支持show profile,mysql 5.0 版本之后就已经支 持了：

mysql> select @@have_profil
		ing;
		+------------------+
		| @@have_profiling |
		+------------------+
		| YES              |
		+------------------+
		1 row in set

（2）查看是否开启了profile ，默认是关闭的

mysql> select @@profiling;
	+-------------+
	| @@profiling |
	+-------------+
	|           0 |
	+-------------+
	1 row in set

（3）开启profile ：

mysql> set profiling=1;
Query OK, 0 rows affected

（4）使用show profiles 命令查看执行的sql 命令的情况，和查看sql 执行的时间：

mysql> show profiles;
+----------+----------+------------------------+
| Query_ID | Duration | Query                  |
+----------+----------+------------------------+
|        1 | 0.000294 | select @@profiling     |
|        2 | 0.000697 | select * from products |
+----------+----------+------------------------+

（5）如果需要分析query_id=2 的sql 语句：

mysql> show profile for query 2
    -> ;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000106 |
| checking permissions | 1.3E-5   |
| Opening tables       | 8.3E-5   |
| init                 | 1.2E-5   |
| System lock          | 1.7E-5   |
| optimizing           | 7E-6     |
| statistics           | 3.3E-5   |
| preparing            | 1.7E-5   |
| executing            | 5E-6     |
| Sending data         | 0.000208 |
| end                  | 9E-6     |
| query end            | 1.6E-5   |
| closing tables       | 1.3E-5   |
| freeing items        | 0.000134 |
| cleaning up          | 2.7E-5   |
+----------------------+----------+

15 rows in set

重点查看Sending data ,因为这个状态表示的是线程访问数据到将数据返回到客户端，
然后可以继续分许all、cpu 、block io、context switch 、page fault;

比如查看cpu:

mysql> show profile cpu for query 2
;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000106 | 0        | 0          |
| checking permissions | 1.3E-5   | 0        | 0          |
| Opening tables       | 8.3E-5   | 0        | 0          |
| init                 | 1.2E-5   | 0        | 0          |
| System lock          | 1.7E-5   | 0        | 0          |
| optimizing           | 7E-6     | 0        | 0          |
| statistics           | 3.3E-5   | 0        | 0          |
| preparing            | 1.7E-5   | 0        | 0          |
| executing            | 5E-6     | 0        | 0          |
| Sending data         | 0.000208 | 0        | 0          |
| end                  | 9E-6     | 0        | 0          |
| query end            | 1.6E-5   | 0        | 0          |
| closing tables       | 1.3E-5   | 0        | 0          |
| freeing items        | 0.000134 | 0        | 0          |
| cleaning up          | 2.7E-5   | 0        | 0          |
+----------------------+----------+----------+------------+
15 rows in set

有关es 对象池的
https://dandelioncloud.cn/article/details/1527180781894254593