MySQL的expalin实例与IO操作

一、内容

1. explain 分析简单实例
2. profiling 分析

1.1 explain 分析简单实例

explain 其实是一个用来检测SQL是否使用了索引，用来优化SQL最佳的选择
以下是实例的表结构，下面实例第一个使用了单表操作（也就是把两张表合并在一起）

# 表结构
单表
  CREATE TABLE `customers1` (
      -- 身份证
      `id` char(20) NOT NULL,
      -- 姓名
      `name` varchar(20) NOT NULL,
      -- 城市名
      `city` varchar(10) NOT NULL,
      -- 性别：M（男），G（女）
      `gender` tinyint(4) NOT NULL,
      -- 出生日期
      `birthdate` date NOT NULL,
      -- 手机号
      `mobile` char(11) DEFAULT NULL,
      -- 照片
      `photo` varchar(20) DEFAULT NULL,
      -- 月薪
      `monthsalary` decimal(10,2) NOT NULL,
      -- 年奖金额
      `yearbonus` decimal(10,0) DEFAULT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  分表
  CREATE TABLE `customers` (
    `id` char(20) NOT NULL,
    `name` varchar(20) NOT NULL,
    `city` varchar(10) NOT NULL,
    `gender` tinyint(4) NOT NULL,
    `birthdate` date NOT NULL,
    `mobile` char(11) DEFAULT NULL,
    `photo` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
​
  CREATE TABLE `salary` (
    `id` char(20) NOT NULL,
    `monthsalary` decimal(10,2) NOT NULL,
    `yearbonus` decimal(10,0) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

现在需求是需要（查询女生客户量和平均月薪）

1. 先查询默认的表是否使用了索引（show indexes from table_name）
   如果已经存在了索引请先删除（alter from table_name drop index 索引名称）

例如：以下这种情况就是有索引的情况下，请先删除

mysql> show indexes from customers1;
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers1 |          0 | PRIMARY           |            1 | id          | A         |     1305211 |     NULL | NULL   |      | BTREE      |         |               |
| customers1 |          1 | gender_monthsalar |            1 | gender      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| customers1 |          1 | gender_monthsalar |            2 | monthsalary | A         |      951340 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

出现以下情况在是没有索引的情况下

mysql> show indexes from customers1;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers1 |          0 | PRIMARY  |            1 | id          | A         |     1305211 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

单表操作：查询 女生客户数量以及平均月薪

# SQL语句
select count(*) from customers1 where gender = 0;  // 先查询女生数量     

没有使用聚合函数 平均查询的时候，执行秒数如下：执行消耗差不多6秒

mysql> select count(*) from customers1 where gender = 0;
+----------+
| count(*) |
+----------+
|   651457 |
+----------+
1 row in set (5.95 sec)

优化SQL操作：

1. 首先先使用explain 查询上面的SQL语句是否使用了索引情况，检测SQL的使用情况

explain select count(*) from customers1 where gender = 0;

mysql> explain select count(*) from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1305211 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对上面的查询结果解释：
以上使用了全表的扫描，没有使用到任何索引的查询，只使用了where 条件的查询。
从expalin 分析 type 可以看出来这个时候的申请了时进行了全表的扫描，key（查询实际用到的索引）与possible_keys（指出使用哪个索引），key_len （索引的长度），而extra为（useing where 简单的来说，这个查询没有使用到索引，只使用了where条件作为过滤条件，）

这个时候我们可以建立索引尝试着去优化
要根据索引的建立的规则 =》 把SQL分解，索引不是随便添加

索引应该创建在 where 、order、group等操作所设计的数据列上，皆在输出报告里出现的数据列不是好的筛选，简单点的说就是 where 子句，关联检索中的from后面的字句，不适合创建索引，order与group子句中出现的数据列最适合用来创建索引

select 
	count(*)
 from 
 	customers1 
 where 
 	gender = 0; //  这个where 后面的子句适合创建索引

SQL命令创建索引：

alter table table_name add 自定义索引名称(要创建的索引字段名称)

mysql> alter table customers1 add index gender(gender);
Query OK, 0 rows affected (9.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

在使用 explain 命令查询检测是否使用了刚刚创建的索引

mysql> explain select count(*) from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从分析来看：现在使用了刚刚创建的字段索引，并且时为常量的方式查找

测试在测试查询的执行时间

mysql> select count(*) from customers1 where gender = 0;
+----------+
| count(*) |
+----------+
|   651457 |
+----------+
1 row in set (0.14 sec)


mysql> select count(*) from customers1;
+----------+
| count(*) |
+----------+
|  1300000 |
+----------+
1 row in set (0.22 sec)

可想而知，加上索引明显执行效率提高了很多，刚刚上面没有添加索引的时候执行时6秒左右，而现在加上索引只需要 0.14，秒

刚刚我们只是查询出女生的数量，而并非查询出女生的数量以及平均薪资。
在这我们在加上查询平均薪资，看看会不会有什么变化

mysql> select count(*),avg(monthsalary) from customers1 where gender = 0;
+----------+------------------+
| count(*) | avg(monthsalary) |
+----------+------------------+
|   651457 |      5004.957393 |
+----------+------------------+
1 row in set (8.18 sec)

其实这里我们可以发现时加上聚合函数的时候导致查询速度变慢的。我们只需要把着两个优化就好了，而上面的语句仅仅只是根据 where gender = 0 过滤了查找的内容，但是在进行 数据 avg 的时候，就时需要进行IO获取具体的内容，MySQL在辅助索引操作的时候如果无法从辅助中获取数据这个时候就会再去查询一级索引，再做计算，所以为了更好的进行 monthsalary 的avg操作，我们应该也要给 monthsalary添加索引

mysql> alter table customers1 add index monthsalary(monthsalary);
Query OK, 0 rows affected (11.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时再使用 explain 检索以下是否使用了两个索引

mysql> explain select avg(monthsalary) from customers1;
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | index | NULL          | monthsalary | 5       | NULL | 1305211 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

再使用 where 添加查询检索SQL语句

mysql> explain select avg(monthsalary) from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而此时多加where 去检索，没有查找到刚刚创建的索引，这里还是使用了gender的索引，其实这就是MySQL对于一个表的索引的选择的关系，MySQL底层优化器如果有多个索引，它会默认选择最优的索引进行选择。

把之前的索引删除 alter table table_name drop index 自定义的索引名称

mysql> alter table customers1 drop index gender;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table customers1 drop index monthsalary;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

再添加一个联合索引

mysql> alter table customers1 add index gender_monthsalary(gender,monthsalary);
Query OK, 0 rows affected (12.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

再使用 explain 检索以下是否使用了刚刚创建的联合索引

mysql> explain select avg(monthsalary) from customers1 where gender = 0;
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys      | key                | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender_monthsalary | gender_monthsalary | 1       | const | 652605 |   100.00 | Using index |
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在执行SQL语句检测执行的速度

mysql> select avg(monthsalary) from customers1 where gender = 0;
+------------------+
| avg(monthsalary) |
+------------------+
|      5004.957393 |
+------------------+
1 row in set (0.20 sec)

测试只需要0.20秒就可以查询出来，速度大大的提升

profiling 分析

要想有画一条query，就需要清楚这条query的性能瓶颈到底在那里，MySQL的profier是一个使用非常有用的查询诊断分析工具，通过该工具可以获取一条查询在整个过程中多种资源的消耗情况，例如内存消耗，I/O消耗和CPU消耗等，（也就是可以看到一条查询SQL语句的执行消耗情况）

profile 的语法规则如下：

show profile [type[type]...],
[for  query  n]
[limit row_count [offset offset]]

其中type参数可选项含义如下：

all：显示所有信息
block io：限时输入输出操作阻塞的数量
content switches：显示自动或非自动context switches的数量
cpu：显示系统和用户cpu使用的时间
ipc：信息发送的接受和数量
momory：内存的信息
page fauts“：显示主要的page faults数量
source：显示函数的名称，并且函数所在文件的名字和行数
swaps”显示swap数量

首先开启profile

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

在开启了 query profiler 功能之后，MySQL就会自动记录所有执行的query的profile信息。

mysql> select count(*) from admin_user;
+----------+
| count(*) |
+----------+
|       51 |
+----------+
1 row in set (0.02 sec)


mysql> select count(*) from customers1;
+----------+
| count(*) |
+----------+
|  1300000 |
+----------+
1 row in set (0.22 sec)

在通过 show profiles 命令获取当前给系统中保存的多个query的profile的概要信息

SQL命令：

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.01674000 | select count(*) from admin_user |
|        2 | 0.22174425 | select count(*) from customers1 |
+----------+------------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

针对单个query 获取详细的 profile信息
在获取概要信息之后就可以根据概要信息中的 query_id 来获取 一个query 在执行过程中的 profile信息

SQL命令：

show profile for query 1;

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000119 |
| checking permissions | 0.000011 |
| Opening tables       | 0.016399 |
| init                 | 0.000023 |
| System lock          | 0.000011 |
| optimizing           | 0.000005 |
| statistics           | 0.000016 |
| preparing            | 0.000011 |
| executing            | 0.000002 |
| Sending data         | 0.000052 |
| end                  | 0.000002 |
| query end            | 0.000008 |
| closing tables       | 0.000006 |
| freeing items        | 0.000062 |
| cleaning up          | 0.000014 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

还可以查询一些其他的列段数据

SQL命令：

show  profile cpu,block io ,for query 1;

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000119 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.016399 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000023 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000016 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.000052 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000062 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)