MySQL day9(Explain)

最新推荐文章于 2024-01-29 21:00:06 发布
最新推荐文章于 2024-01-29 21:00:06 发布
阅读量200 收藏
点赞数
分类专栏： MySQL 文章标签： mysql explain
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40187062/article/details/108762185
版权

Explain 详解

  • Explain 作用

    • 查看表的读取顺序
    • 数据读取操作的操作类型
    • 查看哪些索引可使用
    • 查看使用使用的索引
    • 查看表之间的引用
    • 查看每张表有多少行被优化器执行

  • Explain 使用方法

    Explain + sql
e.g.
	Explain select * from test;
	
mysql> explain select * from test;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  • Explain 字段

    • id: select 查询的序列号， 包含一组数字: 标示查询中执行select自己或者操作表的顺序

      • id 相同, 执行顺序从上往下

        mysql> Explain select * from employee e, department d, customer c where e.dep_id =d.id and e.cus_id = c.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.e.dep_id |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

      • id 不同: 如果是子查询， id序号会递增， id值越大优先级越高， 优先被执行。

        mysql> explain select * from department where id = (select id from employee where id = (select id from customer where id = 1));
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | department | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | employee   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
|  3 | SUBQUERY    | customer   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

    • select_type: 查询类型， 主要区别普通查询， 联合查询， 子查询等复杂的查询

      • SIMPLE: 简单的select 查询， 不包含子查询或者union
      mysql> explain select * from department;
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
  | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
  |  1 | SIMPLE      | department | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
  +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)

    • PRIMARY: 查询中包含任何的复杂的子查询, 最外层被标记为primary

        mysql> explain select * from employee where dep_id = (select id from department where id = 1);
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  |  1 | PRIMARY     | employee   | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    8 |    12.50 | Using where |
|  2 | SUBQUERY    | department | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
  +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

      • SUBQUERY: 子查询

      • DERIVED: 代表着衍生表(虚拟表)， 把结果放在衍生表中

        mysql> explain select * from department d,(select * from(select dep_id from employee group by dep_id) t2 ) t where d.id = t.dep_id;
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref       | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL      |    5 |   100.00 | NULL                            |
|  1 | PRIMARY     | <derived3> | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | test.d.id |    2 |   100.00 | Using index                     |
|  3 | DERIVED     | employee   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL      |    8 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

      • UNION: 使用 union

      • UNION RESULT: 使用union , 最后显示的结果集

        mysql> explain select id from department union select id from employee;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | department | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index     |
|  2 | UNION        | employee   | NULL       | index | NULL          | PRIMARY | 4       | NULL |    8 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)

  • table: 显示这一样查询是关于哪张表的

  • partitions: 如果查询是基于分区表的话，会显示访问的分区

  • type: 访问类型排序(性能从上到下越来越差,)

    • system

      • 表中有一行记录(系统表， 表只有一行，MyISAM引擎), 这是 const 类型的特例, 平时不会出现

    • const

      • 表示通过索引一次就找到结果

      • 用于比较 primary 或者 unique 索引。

      • 常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时

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

    • eq_ref

      • 唯一性索引扫描

      • 对于每个索引键， 表中只有一条记录与之匹配

      • 常见于主键或者唯一索引扫描

      • 每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

        mysql> explain select * from employee e, department d where e.id = d.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | d     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    5 |   100.00 | NULL  |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

    • ref

      • 非唯一性索引扫描， 返回匹配某个单独值的所有行
      • 本质上也是一种索引访问
      • 返回所有匹配摸个单独值的行
      • 可能会找到多个符合条件的行
      • 如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键

    • range

      • 范围查询

    • index

      • 和all 是一种类型， 只不过是扫描了索引树

    • All

      • 扫描全表

  • possible_keys: 可能使用到的索引

  • key: 实际使用的索引

  • ken_len: 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要

  • ref: 哪些列或者常量被用做索引列上的值

  • rows: 根据表的统计信息和索引的使用情况，大致估算查询结果所需要读取记录的行数

  • filtered: 表示示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例

  • Extra:

    • using where:

      • SQL使用了where条件过滤数据。(需要借鉴 type 确定效率)

    • Using index:

      • SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录(效率高)

    • Using index condition:

      • 确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录(效率一般)

    • Using filesort:

      • 得到所需结果集，需要对所有记录进行文件排序(效率低)

    • Using temporary:

      • 需要建立临时表(temporary table)来暂存中间结果(效率低)

    • Using join buffer (Block Nested Loop):

      • 需要进行嵌套循环计算, 即内外层 type 都是 all. (效率低)
S1ls
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL组合索引与最左匹配原则详解
09-09
主要给大家介绍了关于MySQL组合索引与最左匹配原则的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Mysql具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
mysql索引最左匹配原则的理解
热门推荐
NerveMore
10-11 3万+
这是你的表结构，有三个字段，分别是id,name,cid CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_cid_INX` (`na
Mysql复合索引最左匹配原则以及索引失效条件
xiaoxiao48的博客
04-09 5556
Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。例如索引是key index （a,b,c）。可以支持a | a,b| a,...
MySQL进阶之路（十七）—— 复合索引及最左匹配原则探究
生命中有太多不确定
10-24 279
复合索引及最左匹配原则探究 文章目录复合索引及最左匹配原则探究一、概述二、联合索引是长什么样子的？三、如何建立和使用复合索引四、覆盖索引是什么？五、总结 一、概述 ​ 上一篇文章中介绍了B+树是如何根据索引构造出来的以及如何使用索引去查询数据的，不知道你有没有发现，那些例子都是某一列作为索引，比较好理解，而今天，我们将要说的是多个列组成的索引是什么样子的，以级其使用的规则。 二、联合索引是长什么样子的？ ​ 如果说只有一个列，那么匹配起来是非常简单的，我们只需要对一个列的值不断地进行比较就可以了，但是如果是
索引6：联合索引的最左匹配原则
qq_41627514的博客
02-13 9413
联合索引的最左匹配原则 什么是最左匹配原则？ 举例 索引列A和列B 建立联合索引 index(A,B)(A,B顺序有序) explain select X from XX where A=’‘and B=’’ 走 ab索引 explain select X from XX where A=’‘走 ab索引 explain select X from XX where B=’'不走 ab索...
MySQL高级Day01.rar
08-17
"MySQL高级Day01"的学习资料可能涵盖了MySQL的高级特性和最佳实践，旨在帮助用户深入理解并优化数据库性能。 首先，我们来探讨MySQL的存储引擎。MySQL支持多种存储引擎，其中最常见的是InnoDB和MyISAM。InnoDB是...
MySQL_day01上课资料.zip
03-08
【MySQL_day01上课资料.zip】是一份针对MySQL数据库初学者的教程资料，配合提供的视频链接（BV1tZ4y1P7ta），旨在帮助学习者快速掌握MySQL的基础知识和操作技能。以下是对这份资料中可能涵盖的知识点的详细说明： 1...
MySQL 高级 - day-03.rar
08-17
"MySQL 高级 - day-03"的压缩包文件很显然是一个关于MySQL深入学习的课程资料，可能包含讲义、笔记、练习题或者代码示例。在这个阶段，我们可能会探讨一些进阶的主题，比如存储引擎、触发器、视图、事务处理、索引...
MySQL高级-day-02.rar
08-17
MySQL提供了一些内置工具，如EXPLAIN、SHOW PROFILE和Performance Schema，用于分析查询性能和监控系统资源。通过对这些工具的熟练运用，可以定位性能瓶颈，并进行相应的优化。 八、复制与高可用 MySQL的主从复制...
MySQL 高级 - day-04.rar
08-17
另外，优化查询语句，避免全表扫描，使用JOIN操作的优化策略，以及使用EXPLAIN分析查询执行计划，都是MySQL高级查询优化的重要内容。 其次，我们可能探讨到存储引擎的选择。InnoDB和MyISAM是最常见的两个存储引擎，...
MySQL中IN子查询会导致无法使用索引
weixin_34236869的博客
06-28 328
  今天看到一个博客园的一篇关于MySQL的IN子查询优化的案例，一开始感觉有点半信半疑（如果是换做在SQL Server中，这种情况是绝对不可能的，后面会做一个简单的测试。）随后动手按照他说的做了一个表来测试验证，发现MySQL的IN子查询做的不好，确实会导致无法使用索引的情况（IN子查询无法使用所以，场景是MySQL，截止的版本是5.7.18） MySQL的测试环境 测试表如下 ...
mysql最左匹配原则与失效
狗蛋的博客
08-31 1980
mysql最左匹配原则
mysql复合索引(联合索引)用法以及最左原则
a541588991的博客
05-29 1385
1.准备表(含23w数据) SELECT COUNT(*) FROM index_test; 2.查看索引，只有一个主键索引 3.创建联合索引(abc) ALTER TABLE index_test ADD INDEX index_a_b_c(a, b, c); 4.能够触发索引的几种情况 1.单独用a做条件查询 EXPLAIN SELECT * FROM index_test WHERE a = 'H5158000'; 2.用a和b做条件联合查询 EXPLAIN SELECT * FROM
postgresql：复合索引的最左侧匹配原则
奇艺诶
12-13 7764
什么是复合索引 基于表的多列上创建的索引，也叫联合索引。 为什么使用复合索引 减少开销：建一个复合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！ 覆盖索引：对复合索引(col1,col2,col...
mysql数据库的左联合_mysql联合索引最左原则的理解
weixin_36079936的博客
01-20 259
小弟我今天突然用到了一种复合索引，也叫联合索引，查询数据。但对其原理却知之甚少。从网上查了一些资料，还能理解。赶紧记下来！如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；例如：组合索引(a,b)expl...
mysql组合索引之最左原则
weixin_30657999的博客
05-04 79
为什么在单列索引的基础上还需要组合索引？　　 　　select product_idfrom orderswhere order_id in (123, 312, 223, 132, 224); 　　　　我们当然每个字段都可以创建索引 在不建立组合索引的前提下 mysql只会用到其中一个最有效率的索引 ，效率平庸 　　　　但当我们建了组合索引后 (Btree索引结构下)当我们建立了...
【项目实战】MySQL的复合索引与最左匹配原则
最新发布
本本本添哥
01-29 123
最左匹配原则是指MySQL在查询时，会从复合索引的最左列开始匹配。在使用复合索引时，必须从索引的最左边列开始匹配。最左匹配原则意味着，当执行查询时，只有当SQL语句中包含了联合索引中最左边的列，并且这些列是以建立索引时的顺序来引用时，索引才会被使用。如果在查询中跳过了最左边的列，或者不以建立索引时的列顺序来引用，那么即便后面的列在索引中，也不会使用到这个索引。
Mysql 索引失效-最左匹配原则
java1473727528的博客
06-30 1389
Mysql 索引失效-最左匹配原则 我们在建立复合索引的时候，常常会出现索引失效的问题。这时，我们可能就违背了最左原则。 例如：我创建的学生表 创建了一个组合索引(N_STUDENT_ID,VC_STUDENT_CODE,VC_STUDENT_NAME) 执行查询，索引的三个列都有 EXPLAIN select * from t_student where N_STUDENT_ID=1 and VC_STUDENT_CODE=00001 and VC_STUDENT_NAME=‘王亮’ 查看查询执行
MySQL中in子查询会导致无法使用索引问题（转）
angou6476的博客
12-25 271
MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, pay_id int, pay_time datetime, other_col varchar(100) ) 建一个存储过程插入测试数据，测试数据的特点是pay_id可重复，这...
MySQL EXPLAIN深度解析与应用
"MySQL EXPLAIN解析PPT，包含详细的例子，用于工作分享，旨在解析和理解MySQL的执行计划，以优化数据库查询性能。" 在数据库管理中，MySQL的`EXPLAIN`命令是一个非常重要的工具，它可以帮助我们理解SQL查询的执行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值