Mysql执行计划和Mysql优化

1. 执行计划

1.1. 执行计划概念

执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。
作用:分析你的查询语句或是表结构的性能瓶颈。
语法:Explain + SQL语句

执行计划输出内容介绍:表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。

执行计划包含的信息:
在这里插入图片描述

1.2. 执行计划各字段详解

1.2.1. 执行计划-ID

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

规则:
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同,同时存在

第一种情况 Id相同
在这里插入图片描述
第二种情况 Id不同
在这里插入图片描述
如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

第三种情况 Id相同又不同
在这里插入图片描述
id如果相同,可以认为是一组,从上往下顺序执行;
在所有组中,id值越大,优先级越高,越先执行

1.2.2. 执行计划-select_type

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

在这里插入图片描述
具体描述
在这里插入图片描述
例子:

Simple:简单的 select 查询,查询中不包含子查询或者UNION
在这里插入图片描述
PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为
SUBQUERY:在SELECT或WHERE列表中包含了子查询
EXPLAIN
select t1.,(select t2.id from t2 where t2.id = 1 ) from t1
在这里插入图片描述
在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里。
select t1.
from t1 ,(select t2.* from t2 where t2.id = 1 ) s2 where t1.id = s2.id
在这里插入图片描述

1.2.3. 执行计划-table

显示这一行的数据是关于哪张表的
在这里插入图片描述

1.2.4. 执行计划-type

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

**System:**表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计。

Const:表示通过索引一次就找到了。
Const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
如将主键置于where列表中,MySQL就能将该查询转换为一个常量
在这里插入图片描述
读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。
eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是常量或使用此表之前读取的表中的列的表达式。
在这里插入图片描述
Ref:非唯一性索引扫描(二级索引),返回匹配某个单独值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
在这里插入图片描述
Range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询。
这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

在这里插入图片描述
Index:当查询的结果全为索引列的时候,虽然也是全部扫描,但是只查询的索引库,而没有去查询数据。
在这里插入图片描述
All:Full Table Scan,将遍历全表以找到匹配的行。
在这里插入图片描述

1.2.5. 执行计划-possible_keys 与 key

possible_keys:可能使用的key索引。
Key:实际使用的索引。如果为NULL,则表示没有使用索引。

查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

在这里插入图片描述
在这里插入图片描述
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

根据key_len这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段是否都被查询用到。
char和varchar跟字符编码也有密切的联系,
latin1占用1个字节,gbk占用2个字节,utf8占用3个字节。(不同字符编码占用的存储空间不同)

1.2.6. 执行计划-key_len

在这里插入图片描述
字符类型
在这里插入图片描述
字符类型-索引字段为char类型+不可为Null时
在这里插入图片描述
字符类型-索引字段为char类型+允许为Null时
在这里插入图片描述
索引字段为varchar类型+不可为Null时
在这里插入图片描述
索引字段为varchar类型+允许为Null时
在这里插入图片描述
varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

在这里插入图片描述
日期和时间
在这里插入图片描述
datetime类型在5.6中字段长度是5个字节,
datetime类型在5.5中字段长度是8个字节

整数/浮点数/时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1(因为需要有是否为空的标记,这个标记需要占用1个字节)

datetime类型在5.6及其以后版本中字段长度是5个字节,datetime类型在5.5中字段长度是8个字节

key_len 总结:
变长字段需要额外的2个字节(VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节),所以VARCAHR索引长度计算时候要加2),固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL,因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合索引字段的索引长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用。

1.2.7. 执行计划-ref

Ref:显示索引的哪一列被使用了,如果可能的话,是一个常量。哪些列或常量被用于查找索引列上的值。
在这里插入图片描述
由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1,col2匹配了一个常量,即 ‘ac’。

1.2.8. 执行计划-rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
在这里插入图片描述

1.2.9. 执行计划-Extra

Extra:包含不适合在其他列中显示但十分重要的额外信息
在这里插入图片描述
Using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”
在这里插入图片描述
在这里插入图片描述
Using temporary
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

在这里插入图片描述
在这里插入图片描述
USING index
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;

在这里插入图片描述
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
在这里插入图片描述
USING index
Using where
表明使用了where过滤

2. 执行计划

2.1. sql优化实战

2.1.1. 尽量全值匹配

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

在这里插入图片描述

2.1.2. 最佳左前缀法则-最左匹配

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
在这里插入图片描述

2.1.3. 不在索引列上做任何操作

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
在这里插入图片描述

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

2.1.4. 范围条件放最后

存储引擎不能使用索引中范围条件右边的列
在这里插入图片描述

2.1.5. 覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
在这里插入图片描述

2.1.6. 不等于要甚用

mysql 在使用不等于(!= 或者<>)的时候有可能无法使用索引会导致全表扫描。
在这里插入图片描述

2.1.7. Like查询要当心

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
在这里插入图片描述

2.1.8. 字符类型加引号

字符串不加单引号索引失效。在这里插入图片描述

2.1.9. OR改UNION效率高

在这里插入图片描述

2.2. Mysql架构层面优化

2.2.1 硬件方面的优化

mysql服务器端增大并发的连接数。如果服务器端并发连接数不够,可以增加并发连接数的配置。

show variables like '%max_connections%'

在这里插入图片描述

2.2.2 集群

  1. 可以做基于主从复制
    在这里插入图片描述
    主从复制+读写分离原理:
    在这里插入图片描述
    在这里插入图片描述

  2. 基于mycat或者shading做都写分离
    基于mycat

cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="BLADEX" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex"/>
        <schema name="BLADEX_FLOW" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex_flow"/>
        <schema name="XXL_JOB" checkSQLschema="true" sqlMaxLimit="100" dataNode="xxl_job"/>
        <dataNode name="bladex" dataHost="bladex" database="bladex" />
        <dataNode name="bladex_flow" dataHost="bladex_flow" database="bladex_flow" />
        <dataNode name="xxl_job" dataHost="xxl_job" database="xxl_job" />

        <dataHost name="bladex" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="bladexM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladexS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="111.com"/>
                </writeHost>
                <writeHost host="bladexM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladexS2" url="jdbc:mysql://10.1.33.27:3306" user="root" password="123.com"/>
                </writeHost>
        </dataHost>
        <dataHost name="bladex_flow" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="bladex_flowM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladex_flowS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="123.com"/>
                </writeHost>
                <writeHost host="bladex_flowM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladex_flowS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="123.com"/>
                </writeHost>
        </dataHost>
        <dataHost name="xxl_job" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="xxl_jobM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="1111">
                        <readHost host="xxl_jobS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="1111"/>
                </writeHost>
                <writeHost host="xxl_jobM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="1111.com">
                        <readHost host="xxl_jobS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="1111.com"/>
                </writeHost>
        </dataHost>
</mycat:schema>

或者基于shading也是可以的。

  1. 分库分表
    每个微服务一个库
    单个表达可以分表

单个数据库或者表数据量过大,那么可以选择分库分表的方式去优化。
垂直分库
在这里插入图片描述
水平方向的的分库分表在这里插入图片描述

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:代码科技 设计师:Amelia_0503 返回首页
评论

打赏作者

前路无畏

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

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

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

打赏作者

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

抵扣说明:

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

余额充值