Explain——MySQL性能优化

最新推荐文章于 2024-07-09 11:09:43 发布

沐乙·

最新推荐文章于 2024-07-09 11:09:43 发布

阅读量118

点赞数

分类专栏： sql 文章标签： mysql explain

本文链接：https://blog.csdn.net/qq_37206616/article/details/114189848

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

日常工作中经常会遇到sql优化的问题，随着数据量的增大，数据的查询速度越来越慢，这个适合对于数据库和sql的优化必不可少，优化就需要有一点的依据，EXPLAIN 命令可以对 SELECT 语句的执行过程进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。

一、数据准备

生成用户表和订单表，利用存储过程生成一万个用户和十万条订单

-- 用户表
CREATE TABLE `t_user` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `mobile` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

DROP PROCEDURE
IF
	EXISTS proc_initData;--如果存在此存储过程则删掉 
DELIMITER $
CREATE PROCEDURE proc_initData () BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i <= 10000 DO
			INSERT INTO `test_2`.`t_user` ( `id`, `name`, `age`, `mobile` )
		VALUES
			( i, CONCAT( 'name', i ), FLOOR( 10 + ( RAND() * 60 )), FLOOR( 11000000000 + ( RAND() * 199999999999 )));
		SET i = i + 1;
	END WHILE;
END $ CALL proc_initData ();

-- 订单表
CREATE TABLE `t_order` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `member_id` int(12) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL COMMENT '数量',
  `money` int(11) DEFAULT NULL COMMENT '订单金额',
  `status` int(1) DEFAULT NULL COMMENT '订单状态（0：待付款；1：已付款；2：已退款；3：已退货；4：已取消）',
  `order_code` varchar(255) DEFAULT NULL COMMENT '订单编号',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;

DROP PROCEDURE
IF
	EXISTS proc_initData;--如果存在此存储过程则删掉 
DELIMITER $
CREATE PROCEDURE proc_initData () BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i <= 100000 DO
			INSERT INTO `test_2`.`t_order` ( `id`, member_id, `amount`, `money`, `status`, `order_code`, `order_time` )
		VALUES
			(
				i,
				FLOOR(1 + ( RAND() * 10000 )),
				FLOOR(1 + ( RAND() * 10 )),
				FLOOR(10 + ( RAND() * 1000 )),
				FLOOR(0 + ( RAND() * 5 )),
				CONCAT('TE',FLOOR(11000000000 + ( RAND() * 199999999999 ))),
			DATE_ADD( '2021-01-01 00:00:00', INTERVAL FLOOR( 1 + ( RAND() * 1864000 )) SECOND )
	);
	SET i = i + 1;
END WHILE;
END $ CALL proc_initData ();


-- 会员身份
CREATE TABLE `t_member_type` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `member_id` int(12) DEFAULT NULL,
  `member_type_id` int(2) DEFAULT NULL COMMENT '会员身份：1：vip;2:vvip',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_2`.`t_member_type`(`id`, `member_id`, `member_type_id`) VALUES (1, 1, 1);

二、EXPLAIN 输出格式

mysql> explain select * from t_user where id < 10;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

其中：

id: 查询的序列号。
select_type: SELECT 查询的类型。
table: 显示这一行的数据是关于哪张表的。
partitions: 匹配的分区。
type: 类型（重要）。
possible_keys: 此次查询中可能选用的索引。
key: 此次查询中确切使用到的索引。
rows: 表示每张表有多少行被优化器查询。
filtered: 表示此查询条件所过滤的数据的百分比。
extra: 额外的信息。

三、详细解释

3.1、ID

id相同

当ID相同时，执行顺序由上到下，即先先执行t_order子查询，后执行t_user:

mysql> EXPLAIN SELECT
    -> * 
    -> FROM
    -> t_order 
    -> WHERE
    -> MEMBER_ID IN ( SELECT ID FROM t_user WHERE ID < 10 );
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                      | rows  | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     | 99867 |   100.00 | Using where |
|  1 | SIMPLE      | t_user  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_2.t_order.member_id |     1 |   100.00 | Using index |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)】

id不同

id 完全不同，如果是子查询 id 的序号会递增： id 越大优先级越高，越先被执行（正常由内而外执行）
id 有相同的有不同的，结合上面的情况，先执行优先级高的，优先级相同的回到上面的情况，先执行上面的；

EXPLAIN SELECT
	* 
FROM
	t_order 
WHERE
	member_id = (
	SELECT
		id 
	FROM
		t_user 
WHERE
	id IN ( SELECT member_id FROM t_member_type WHERE member_type_id = 1 ))

+----+--------------+---------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+-------------+
| id | select_type  | table         | partitions | type   | possible_keys | key     | key_len | ref                   | rows  | filtered | Extra       |
+----+--------------+---------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+-------------+
|  1 | PRIMARY      | t_order       | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  | 99867 |    10.00 | Using where |
|  2 | SUBQUERY     | <subquery3>   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |  NULL |   100.00 | Using where |
|  2 | SUBQUERY     | t_user        | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery3>.member_id |     1 |   100.00 | Using index |
|  3 | MATERIALIZED | t_member_type | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |     2 |    50.00 | Using where |
+----+--------------+---------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)

3.2、select_type

字面意思，查询的类型，类型包括：

SIMPLE：简单的查询，表示此查询不包含 UNION 查询或子查询；
PRIMARY：表示此查询是最外层的查询；
UNION：表示此查询是 UNION 的第二或随后的查询；
DEPENDENT UNION：UNION 中的第二个或后面的查询语句, 取决于外面的查询；
UNION RESULT：UNION 的结果；
SUBQUERY：在select或者where中有子查询；
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果；

3.3、table

比较简单，显示这一行的数据是关于哪张表的，（上面的代表子查询）

3.4、partitions

这个字段和使用的索引关系密切，如果在查询的时候，发现查询需要添加索引，或者添加的索引没有使用到，重新添加索引进行查询。

3.5、type

访问类型，对表访问方式，表示MySQL在表中找到所需行的方式。

system > const > eq_erf > ref > range > index > all（一般保证查询至少达到range级别，最好能达到ref）
system：表中仅有一行（=系统表）这是const联结类型的一个特例

const

通过索引一次就找到，const用于比较primary key或者unique索引，因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量

mysql> explain select * from t_member_type where id  = 1;
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_member_type | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_user where id  = 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_erf

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。

mysql> explain
    -> select *
    -> from t_user,t_member_type
    -> where  t_member_type.member_id = t_user.id
    -> ;
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                            | rows | filtered | Extra       |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
|  1 | SIMPLE      | t_member_type | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                           |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t_user        | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_2.t_member_type.member_id |    1 |   100.00 | NULL        |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

const 和eq_ref的区别

const是单表直接按主键或唯一键读取；
eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询。
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_eq_ref
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_const

ref

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体

-- 先给t_order的member_id添加一个索引：
create index t_order_member_id_index
	on t_order (member_id);

mysql> explain select * from t_order where member_id = 10;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | ref  | t_order_member_id_index | t_order_member_id_index | 5       | const |    7 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用的索引，通常为between、< 、>、in等关键词，扫描的效率大于全表扫描，扫描索引的一部分，不用扫描全部索引

下面的sql都是使用了基于主键提供查询范围，
mysql> explain select count(id) from t_user where id < 10;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(order_code) from t_order where id < 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这条语句相比较第二条，没有使用索引提供扫描范围，所以"type = all"
mysql> explain select count(order_code) from t_order where amount < 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99867 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index

index 与all区别为index类型只遍历索引树，通常比all快，因为索引文件比数据文件小很多

使用了t_user的主键索引
mysql> explain select count(id) from t_user;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | PRIMARY | 4       | NULL | 10157 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

all

遍历全表以找到匹配的行

没有使用到索引，所以type=all
mysql> explain select count(order_code) from t_order;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99867 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3.6、possible_keys

哪个索引在该表中找到行

3.7、key

当前查询时所真正使用到的索引.

possible_keys: 数据库内部分析出可能使用的索引
key: sql实际使用过程中使用的索引
通常通过比较两列的值，判断索引十分实现失效，除此之外，如果没有创建索引，但是实际执行过程中key!=null，即覆盖索引：
SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

可以调转前往查看：什么叫做覆盖索引？

这里只是简要的说明一下：
mysql> explain select id,mobile from t_user where mobile = '91368457063';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | const | t_user_mobile_uindex | t_user_mobile_uindex | 99      | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.8、rows

每张表有多少行被优化器查询，扫描的行数，我认为这是最直观的查看到sql执行效率的指标。

3.9、filtered

3.10、extra

包含不适合在其他列显示，但十分重要的信息

using filesort（文件排序，九死一生）

当mysql不能通过不能使用所以对待排序的数据进行排序，需要进行文件排序，如果待排序的数据量过于巨大，可能会占有大量的IO资源。

mysql> explain select * from t_order where member_id  = 1 order by amount desc;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t_order | NULL       | ref  | t_order_member_id_index | t_order_member_id_index | 5       | const |    9 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

using temporary（九死零生）

查询过程中使用到了临时表

mysql> explain select * from t_order group by order_time;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | t_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99867 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

using index (棒极啦)

直接从索引中获取数据

using where

使用了where条件

using join buffer

使用了连接缓存

这里推荐一篇好的文章，便于理解：MySQL 优化二（内部优化器以及 explain 使用）（高级篇）

沐乙·

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Explain——MySQL性能优化

日常工作中经常会遇到sql优化的问题，随着数据量的增大，数据的查询速度越来越慢，这个适合对于数据库和sql的优化必不可少，优化就需要有一点的依据，EXPLAIN 命令可以对 SELECT 语句的执行过程进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。一、数据准备生成用户表和订单表，利用存储过程生成一万个用户和十万条订单-- 用户表CREATE TABLE `t_user` ( `id` int(12) NOT NULL AUTO_INCREMENT, `nam.
复制链接

扫一扫