MySQL调优系列——MySQL常用的优化技巧🔥

Wesley13
• 阅读 673

MySQL调优系列——MySQL常用的优化技巧🔥

SQL优化原因:

性能低、执行时间太长、等待时间太长、SQL语句欠佳(连接查询)、索引失效、服务器参数设置不合理(缓冲、线程数)

system>const>eq_ref>ref>range>index>all   ,要对type进行优化的前提:有索引

其中:system,const只是理想情况;实际能达到 ref>range

system(忽略): 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询

1.通过show status 命令了解各种sql的执行效率

MySQL调优系列——MySQL常用的优化技巧🔥

2. 定位执行效率较低的SQL语句

开启慢查询记录(在相应的配置文件中添加慢查询记录sql最长时间阈值)

3、通过Explain分析低效率的SQL语句的执行情况

-- 实际SQL,查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引,前面加上explain即可
explain select * from emp where name = 'Jefabc'

MySQL调优系列——MySQL常用的优化技巧🔥

概要描述:

id:选择标识符,优先级
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引

key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

下面对这些字段出现的可能进行解释:

一、 id

SELECT识别符。这是SELECT的查询序列号

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时,执行顺序由上至下

2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

3. id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

-- 查看在研发部并且名字以Jef开头的员工,经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';

MySQL调优系列——MySQL常用的优化技巧🔥

二、select_type查询中每个select子句的类型

PRIMARY:包含子查询SQL中的 主查询 (最外层)
SUBQUERY:包含子查询SQL中的 子查询 (非最外层)
simple:简单查询(不包含子查询、union)
derived:衍生查询(使用到了临时表)

三、table

显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是简称,例如上面的e,d,也可能是第几步执行的结果的简称

四、type

对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)

all:查询全部表中的数据

index:查询全部索引中数据

range:检索指定范围的行 ,where后面是一个范围查询(between   ,> < >=,     特殊:in有时候会失效 ,从而转为 无索引all)

ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)

eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0)

const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引  (类型 与索引类型有关)

system(忽略): 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询

NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys中

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

不损失精确性的情况下,长度越短越好

八、ref

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

九、rows

 估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况:

Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by

Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”

-- 测试Extra的filesort explain select * from emp order by name;

Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。

Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

No tables used:Query语句中使用from dual 或不含任何from子句

-- explain select now() from dual;

总结:

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的,并非精确值
• EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。

注意:要尽量避免让type的结果为all,extra的结果为:using filesort

4.适当的位置加上索引【注意以下几种情况】

  • 较频繁的作为查询条件字段应该创建索引

    select * from order_copy where id = $id

  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

   select * from order_copy where sex=’女’

  • 更新非常频繁的字段不适合创建索引

    select * from order_copy where order_state=’未付款’

  • 不会出现在WHERE子句中字段不该创建索引

本文参考:

https://www.cnblogs.com/tufujie/p/9413852.html

https://www.cnblogs.com/itsharehome

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这