八、MySQL高级—索引优化分析（2.2）

已于 2024-09-06 23:47:48 修改
阅读量1k 收藏 12
点赞数 28
分类专栏： # 计算机（Java高级）高级 阶段 文章标签： mysql 数据库
于 2024-09-06 23:43:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42171159/article/details/141942290
版权

🌻🌻 目录

文章大纲 👇👇

在这里插入图片描述

一、性能分析（Explain）

1.1 是什么(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

官网介绍

在这里插入图片描述

1.2 能干嘛

  • 表的读取顺序
  • 哪些索引可以使用
  • 数据读取操作的操作类型
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被物理查询

1.3 怎么玩

  • Explain + SQL语句,例如下面： EXPLAIN SELECT * FROM t1,t2,t3,t4
  • 执行计划包含的信息

在这里插入图片描述

  • 建表脚本
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));


INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));

INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));

INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));

INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

在这里插入图片描述

1.4 各字段解释

1.4.1 ① id

  1. select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
  1. 三种情况
  • （1） id相同，执行顺序由上至下
EXPLAIN SELECT * FROM t1,t2,t3,t4 WHERE t1.id = t2.id AND t2.id = t3.id;

在这里插入图片描述

id相同，执行顺序由上至下

  • （2）id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
EXPLAIN SELECT t1.id FROM t1 WHERE t1.id IN 
	(	SELECT t2.id FROM t2 WHERE t2.id IN
		(	SELECT t3.id FROM t3 WHERE t3.content = '')
	);

在这里插入图片描述

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

  • （3）id相同，不同，同时存在
EXPLAIN SELECT t2.* FROM t2,(SELECT * FROM t3 WHERE t3.content = '') s3 WHERE s3.id = t2.id;

在这里插入图片描述

在这里插入图片描述

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 衍生 = DERIVED

  1. 关注点
  • id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

1.4.2 ② select_type

  • 有哪些

在这里插入图片描述

  • 查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询

(1) SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION

在这里插入图片描述

(2) PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

在这里插入图片描述

(3) DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。

在这里插入图片描述

(4) SUBQUERY：在SELECT或WHERE列表中包含了子查询

在这里插入图片描述

(5) DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外层

在这里插入图片描述

(6) UNCACHEABLE SUBQUREY

在这里插入图片描述

在这里插入图片描述

SHOW VARIABLES LIKE ‘%lower_case_table_names%’;

SELECT @@lower_case_table_names FROM DUAL;

(7) UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

在这里插入图片描述

(8) UNION RESULT：从UNION表获取结果的SELECT

在这里插入图片描述

1.4.3 ③ table

  • 显示这一行的数据是关于哪张表的

1.4.4 ④ partitions

  • 代表分区表中的命中情况，非分区表，该项为null

1.4.5 ⑤ type

(1)

在这里插入图片描述

(2) 访问类型排列

  • type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 

system>const>eq_ref>ref>range>index>ALL
  • 一般来说，得保证查询至少达到range级别，最好能达到ref。

(3) 显示查询使用了何种类型，

从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

  • system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  • const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快,如将主键置于where列表中，MySQL就能将该查询转换为一个常量

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述
在这里插入图片描述

  • range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

在这里插入图片描述
在这里插入图片描述

  • index :出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组

在这里插入图片描述

  • all:Full Table Scan，将遍历全表以找到匹配的行

在这里插入图片描述

  • index_merge:在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

在这里插入图片描述

  • ref_or_null:对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

在这里插入图片描述

  • index_subquery:利用索引来关联子查询，不再全表扫描。

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

  • unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引

在这里插入图片描述

  • 备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

1.4.6 ⑥ possible_keys

  • 显示可能应用在这张表中的索引，一个或多个。
    查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

1.4.7 ⑦ key

  • 实际使用的索引。如果为NULL，则没有使用索引
  • 查询中若使用了覆盖索引，则该索引和查询的select字段重叠

在这里插入图片描述

1.4.8 ⑧ key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'ab%';

在这里插入图片描述

如何计算
1 、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，
3 、varchar这种动态字符串要加2个字节
4、 允许为空的字段要加1个字节

在这里插入图片描述

第一组
key_len=age的字节长度+name的字节长度=4+1 + ( 20*3+2)=5+62=67
第二组
key_len=age的字节长度=4+1=5

在这里插入图片描述

  • key_len字段能够帮你检查是否充分的利用上了索引

1.4.9 ⑨ ref

  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

在这里插入图片描述

1.4.10 ⑩ rows

  • rows列显示MySQL认为它执行查询时必须检查的行数。

在这里插入图片描述
在这里插入图片描述

越少越好

1.4.11 ⑩① filtered

  • 这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

1.4.12 ⑩② Extra

  • 包含不适合在其他列中显示但十分重要的额外信息

(1) Using filesort
出现filesort的情况：

在这里插入图片描述

优化后，不再出现filesort的情况：

在这里插入图片描述

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

(2)Using temporary

优化前存在 using temporary 和 using filesort

在这里插入图片描述

优化前存在 using temporary 和 using filesort 不在，性能发生明显变化：

在这里插入图片描述

(3)Subtopic

(4)USING index

  • 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
    如果同时出现using where，表明索引被用来执行索引键值的查找;
    如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
  • 利用索引进行了排序或分组

(5)Using where

  • 表明使用了where过滤

(6)using join buffer

在这里插入图片描述

  • 使用了连接缓存：

(7)impossible where

在这里插入图片描述

  • where子句的值总是false，不能用来获取任何元组

(8) select tables optimized away

  • 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
    对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
    查询执行计划生成的阶段即完成优化。

在innodb中：

在这里插入图片描述

在Myisam中：

在这里插入图片描述

Daniel521-Spark
关注
专栏目录
MySQL(八) — 执行计划（Explain）详解
坏蛋呆呆的博客
07-07 1578
目录 执行计划 执行计划的作用 执行计划如何使用 执行计划内容 id select_type table type possible_keys key key_len ref rows extra 执行计划 执行计划的作用 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 执行计划如何使...
MySQL高级 数据库优化（基于Linux）索引优化 更新丢失
chengqingshihuishui的博客
03-02 236
1、什么是MySQL高级优化 数据库内部结构和原理 数据库建模优化 数据库索引建立 SQL 语句优化 SQL 编程(自定义函数、存储过程、触发器、定时任务) mysql 服务器的安装配置 数据库的性能监控分析与系统优化 各种参数常量设定 主从复制 分布式架构搭建、垂直切割和水平切割 数据迁移 容灾备份和恢复 shell 或python 等脚本语言开发 对开源数据库进行二次开发 2、Linux系统下安装Mysql 2.1 下载安装 （1）Mysql官网下载安装包 下载完成后，将安装包（一个服
8. MySQL之Explain
UnityAlvin
10-31 352
8.1 简介 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，分析查询语句或是表结构的性能瓶颈。 8.2 各字段解释 id* id作为select查询的序列号，包含一组数字，通常用来表示查询语句表的读取顺序 三种情况 id相同 执行顺序从上到下 id不同 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 id相同与id不相同，同时存在 衍生 = DERIVED 在所有组中，id值越大，优先级越高，越先执行； id如果相同，
MySQL数据库八（explain）
小吕的博客
07-23 179
explain的作用: 通过explain+sql语句可以知道如下内容： ①表的读取顺序。（对应id） ②数据读取操作的操作类型。（对应select_type） ③哪些索引可以使用。（对应possible_keys） ④哪些索引被实际使用。（对应key） ⑤表直接的引用。（对应ref） ⑥每张表有多少行被优化器查询。（对应rows） 1、id INSERT INTO n VALUES (5,'FXY',25); insert into n values(7,'SXY',24);//大小.
Mysql（8）——索引初步及explain
qq_44824574的博客
03-20 744
Mysql索引 当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。 由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！ 索引分类 索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！ 索引的优点： 提高查询效率 索引的缺
MySQL8 EXPLAIN 命令输出的都是什么东西？这篇超详细！
zhanyd的博客
04-08 1162
EXPLAIN的输出列 Column JSON Name Meaning 备注 id select_id The SELECT identifier 查询语句的序号 select_type None The SELECT type 选择类型（详见下表） table table_name The table for the output row partitions p...
mysql系列【你不知道的索引优化】(面试必问)
12-14
1.1、B+Tree结构了解一下1.2、索引到底有哪些1.3、索引是把双刃剑2、索引优化2.1、其中最重要的就是这个explain关键字2.2、使用索引的注意事项2.3、导致索引失效的原因哪些（索引优化方案）？ 1、什么是索引？ 索引...
Mysql 高级-day03.docx
07-07
MySQL 高级优化主要包括对应用层面的优化以及数据库自身的查询缓存优化。在实际生产环境中，为了应对数据库性能的局限，我们需要对前端应用进行优化，以降低数据库的访问压力。 **1. 应用优化** 1.1 **使用连接池*...
高级MySQL查询与索引优化
- 代码解释：使用EXPLAIN语句可以获取MySQL查询执行计划，帮助分析查询性能。 - 结果说明：执行EXPLAIN语句后可以查看查询的执行计划，包括使用的索引、表的访问顺序等信息。 ## 1.2 查询性能评估与优化目标 ```sql...
MySQL数据库索引优化：提升查询速度的利器（索引优化秘籍）
[MySQL数据库索引优化：提升查询速度的利器（索引优化秘籍）](https://img-blog.csdnimg.cn/img_convert/019dcf34fad68a6bea31c354e88fd612.png) # 1. MySQL索引基础** 索引是MySQL数据库中一种重要的数据结构，它...
mysql - 8.执行计划 explain - 上篇
qq_33199919的博客
04-14 881
Q: 我们在进行优化的过程中，需要明确的知道，现在优化到什么程度了，是否还能进一步优化，还有那些方面可以优化，这样才能保证大方向的正确性，那谁来保证呢？ A: 没错，执行计划 前面介绍慢查询的文章中，pt工具有介绍到执行计划的知识点，其实无论慢查询，索引，执行计划这些秀的操作目的就一个，加快sql执行速度，当然他们之间是互相调用互相协作的。 一.Exp...
10. MySql高级之应用优化
weixin_45267102的博客
07-29 251
文章目录MySql高级之应用优化1. 连接池2. 减少对MySQL的访问2.1 避免对数据进行重复检索2.2 增加cache层3. 负载均衡3.1 利用MySQL复制分流查询3.2 采用分布式数据库架构☆ MySql高级之应用优化 前面我们学习了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。 1. 连接池 对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立数据库连
Mysql执行计划与索引详解
Mark-Zoe的博客
04-23 441
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL。 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。 Explain分析示例 mysql>expla...
MySQL8.0分析查询语句EXPLAIN
小草的博客
11-17 581
MySQL8.0分析查询语句EXPLAIN
MySQL 8.0 的执行计划（EXPLAIN）
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-11 688
解读执行计划可以帮助数据库管理员（DBA）和开发者理解查询如何执行，识别潜在的性能问题，并据此优化查询。总之，解读 MySQL 8.0 的执行计划需要一定的经验和知识，但通过不断实践和分析，你可以提高查询性能并优化数据库的整体性能。分析表和索引: 使用 ANALYZE TABLE 命令更新表的统计信息，帮助优化器做出更好的决策。重写查询: 有时，通过重写查询逻辑或使用不同的 SQL 结构，可以获得更好的性能。添加或调整索引: 如果查询没有使用索引或使用了不合适的索引，考虑添加或调整索引。
mysql 8 EXPLAIN查询性能优化
weixin_34265814的博客
08-14 818
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL高级优化
weixin_41238665的博客
01-05 174
MySQL引擎 索引
MySQL入门指南：从基础到高级操作
- **7.3 优化查询语句**：分析查询性能，调整表结构和使用合适的索引来提升效率。 **8. 关系型数据库设计** - **8.1 范式概念**：关系数据库设计中的理论框架，用于确保数据的一致性和完整性。 - **8.2 数据库关系...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Daniel521-Spark

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值