MySQL 性能优化——「Explain 分析实践」

最新推荐文章于 2024-08-17 11:26:03 发布

疯狂的bug

最新推荐文章于 2024-08-17 11:26:03 发布

阅读量2.8k

点赞数 2

分类专栏： mysql 文章标签： mysql 性能优化 sql

本文链接：https://blog.csdn.net/luzhenyu111/article/details/78725533

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文基于MySQL官方5.7版本的 MySQL 5.7 Reference Document.

DESC、DESCRIBE和EXPLAIN这三个关键字都是我们常用的，但是使用场景不同。从MySQL解析器的角度而言，他们的实际用法是一致的。
即实际使用过程中 DESC 等价于 DESCRIBE 等价于 EXPLAIN 。我们常常用于以下两种场景：

查看表结构信息
查看SQL语句执行计划

数据准备

建立两张数据表，用于后续的测试调用。

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '部门名称',
  `depart_desc` varchar(50) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', 'IT部', '程序猿');
INSERT INTO `department` VALUES ('2', '人事部', '很多美女');
INSERT INTO `department` VALUES ('3', '施工部', '糙汉子');
INSERT INTO `department` VALUES ('4', '财务部', '千万别惹他们！');
INSERT INTO `department` VALUES ('5', '行政部', '生活大管家');

-- ----------------------------
-- Table structure for user
-- ----------------------------
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `depart_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_index_1` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('2', 'Jack', '20', '1');
INSERT INTO `user` VALUES ('3', 'Mike', '25', '1');
INSERT INTO `user` VALUES ('4', 'Lucy', '22', '2');
INSERT INTO `user` VALUES ('5', 'Mircle', '30', '3');
INSERT INTO `user` VALUES ('6', 'Josn', '45', '4');
INSERT INTO `user` VALUES ('7', 'Sgodon', '30', '5');

查看表结构信息

1. 执行语法

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

2. 调用实例


mysql> DESC user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | NO   | MUL | NULL    |                |
| age       | int(11)     | YES  |     | NULL    |                |
| depart_id | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
-----------------------------------------------------------------
mysql> EXPLAIN user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | NO   | MUL | NULL    |                |
| age       | int(11)     | YES  |     | NULL    |                |
| depart_id | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

使用EXPLAIN和DESC的效果是一样的，相类似的语法有 SHOW CREATE TABLE.

查看SQL语句执行计划

1.执行语法

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

2.调用示例

mysql> EXPLAIN SELECT * FROM user WHERE id = 4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

#试一下用法-EXTENDED:多了一列 filtered
mysql> EXPLAIN EXTENDED SELECT * FROM user WHERE id= 4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

#试一下用法-PARTITIONS:多了一列partitions
mysql> EXPLAIN PARTITIONS SELECT * FROM user WHERE id=4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

#试一下用法-FORMAT = JSON:默认就为Traditional
mysql> EXPLAIN FORMAT = JSON SELECT id FROM user WHERE id = 4 \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "user",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows": 1,
      "filtered": 100,
      "using_index": true
    }
  }
}

3.EXPLAIN输出格式

mysql> EXPLAIN SELECT * FROM user WHERE id = 4 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

id：SELECT的唯一标识，这是查询中SELECT的序号，唯一。
select_type：查询类型
table：查询的表
partitions：查询调用到的分区信息
type：Join类型
possible_keys：指示了MySQL可以选择的索引，但是有可能不会使用。
key：MySQL真正使用的索引
key_len：MySQL决定用的索引的长度，和key对应
ref：将哪些列或常量与key列中命名的索引进行比较
rows：执行查询需要扫描的行数，预估值。
filtered：将被表条件过滤掉数据的百分比的预估值。
Extra：额外信息

select_type

SIMPLE：表示此查询不包含 UNION 查询或子查询
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT： UNION 的结果
SUBQUERY：子查询中的第一个 SELECT
DEPENDENT SUBQUERY：子查询中的第一个 SELECT, 取决于外面的查询即子查询依赖于外层查询的结果

大部分的查询都是SIMPLE，没有子查询和UNION连接。我们试一下 子查询和UNION。

#使用子查询输出结果如下：
mysql> EXPLAIN SELECT name,age FROM user WHERE depart_id = (SELECT id FROM department WHERE name='人事部门')\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: department
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
2 rows in set (0.00 sec)
----------------------------------------------------------------------------
#使用Union查询输出结果如下：
#union<1,2> 和使用union语句是相对应的,1、2指的是查询ID的序号
mysql> EXPLAIN SELECT name,age FROM user WHERE depart_id = 1 UNION SELECT name,age FROM user WHERE depart_id=2 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

table

有以下几种形式：
-

mysql> EXPLAIN SELECT u.name,u.age FROM (SELECT * FROM user WHERE depart_id=2) u \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
2 rows in set (0.00 sec)

type

system: 表中只有一条数据. 这个类型是特殊的 const 类型。

# full_3 只有一条数据，但是调用全表查询时还是使用的ALL  

mysql> SELECT * FROM full_3;
+----+------+------+-------------+
| id | pkid | name | create_time |
+----+------+------+-------------+
|  1 |    1 | 3    | NULL        |
+----+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM full_3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: full_3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

# 通过这样的子查询时可以的
mysql> EXPLAIN SELECT d.* FROM (SELECT * FROM full_3 fu WHERE fu.id=1) d \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: fu
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)

const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可。

mysql> EXPLAIN SELECT * FROM user WHERE id =3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.
例如下面这个例子中, 就使用到了 ref 类型的查询:

mysql> EXPLAIN SELECT name FROM user WHERE name = 'Jack' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ref
possible_keys: user_index_1
          key: user_index_1
      key_len: 152
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

mysql> EXPLAIN SELECT * FROM user WHERE name LIKE 'Jack'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: user_index_1
          key: user_index_1
      key_len: 152
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段会显示 Using index.

#如果用=就变成REF
mysql> EXPLAIN SELECT name FROM user WHERE name != 'Jack' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: index
possible_keys: user_index_1
          key: user_index_1
      key_len: 152
          ref: NULL
         rows: 6
        Extra: Using where; Using index
1 row in set (0.00 sec)

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

mysql> EXPLAIN SELECT name FROM user WHERE age = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

possible_keys

possibel_keys表示MySQL在查询过程中能够用到的索引，但是不一定会使用。具体使用的索引，由Key决定。

key

key这一列是MySQL真正使用的索引。

ken_len

key_len 表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到。

rows

rows列显示MySQL执行查询预计会覆盖的行数，这个值越小越好。

Extra

有很多额外信息，举三个例子简单说明下。

Impossible WHERE noticed after reading const tables

预示着Where列有逻辑问题，可能一直是FALSE，无法获取数据。例如：

mysql> EXPLAIN SELECT * FROM user WHERE id >3 AND id<2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

Using filesort

查询结果进行排序，例如：

mysql> EXPLAIN SELECT * FROM user WHERE id >3 ORDER By age \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Using index

覆盖索引，从辅助索引中就能够得到需要的数据，而不需要查询聚集索引中的记录。

mysql> EXPLAIN SELECT name FROM user WHERE name LIKE 'I%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: user_index_1
          key: user_index_1
      key_len: 152
          ref: NULL
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)