软件测试工程师 ISP macos cocoa encoding merge paypal icons Notify.js vue论坛 bootstrap后台模版 android项目实战 jq选择第一个子元素 css最后一个子元素 oracle删除字段sql c语言求和 完全去vm去虚拟化工具 idea格式化代码设置 python计算器 python中文文档 python写脚本 python循环10次 python写入txt文件 python服务器开发 java语言基础教程 java覆盖 java集合 java获取年份 java接口实现 java接口类型 java获取当前ip java日期类 java格式化字符串 java获取 linux文档 网络是怎样连接的 js数组移除指定元素 网络工程师教程 用流量打电话的软件 压枪软件
当前位置: 首页 > 学习教程  > 编程语言

Mysql执行计划和Mysql优化

2020/12/5 10:20:38 文章标签:

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

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. 分库分表
    每个微服务一个库
    单个表达可以分表

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


本文链接: http://www.dtmao.cc/news_show_450192.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?