mysql explain介绍

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

优化:

1思想的优化想出一种降低业务逻辑的实现方法。
2软件执行效率优化

MySQL环境优化:
1.如果order by 没有利用到索引,那么将会出现fileSort,如果sort_buffer不够大,fileSort过程则需要使用临时文件 ,fileSort优化,主要通过调整环境来达到,如下
2.设置参数,优化order by 时可能出现的file sort:
将sort_buffer_size = 1M read_rnd_buffer_size = 1M
修改为sort_buffer_size = 16M read_rnd_buffer_size = 16M
避免order by 过程 进行fileSort排序过程临时文件的产生。从3秒->0.7秒左右
3.去掉distinct,因为distinct加order by,mysql将自动使用临时表
distinct的优化方式详见:http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html
4.修改jdbc的url,增加参数useServerPrepStmts=false,使得query cache生效,
这个参数就是让参数与sql连接成整一个字符串,调试对参数中的单引号做了转义,应该
不用担心sql注入攻击了。另外,是否会导致服务端对查询重复的编译而导致的性能下降就不清楚了.

explain是用来分析sql语句,帮助优化的一个命令。
explain的语法如下:
explain [extended] select … from … where …
如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。

比如我们执行 select uid from user where uname=’scofield’ order by uid 执行结果会有

Java代码   收藏代码
  1. +—-+————-+——-+——-+——————-+———+———+——-+——+——-+  
  2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  
  3. +—-+————-+——-+——-+——————-+———+———+——-+——+——-+  

这些东西。
id: SELECT的识别符,这是SELECT的查询序列号。
select_type: SELECT类型,有以下几种不同的类型
(1).SIMPLE: 简单的SELECT(不使用UNION或子查询)
(2).PRIMARY: 最外面的SELECT,如果我们使用UNION或子查询,第一个查询将会是这个类型
(3).UNION: 使用UNION查询时,除第一个语句外的所有语句会返回这个类型
(4).DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询。
(5).UNION RESULT: UNION的结果。
(6).SUBQUERY: 子查询中的第一个SELECT。
(7).DEPENDENT SUBQUERY: 子查询中的第一个SELECT,取决于外面的查询。
(8).DERIVED : 衍生表会返回这个类型。如:select * from (select * from jos_content) as A;。

其中 table 表示是哪个表的数据。
type : 表示表的连接类型,性能由好到差的类型类型为
(System(表中仅有一行,即常量表),
const(单表中最多有一个匹配行),
eq_ref(对于前面的每一行,在此表中只查询一条记录),
ref(使用普通的索引),
ref_or_null(和ref类似,但是条件中包含对于NULL查询),
index_merge(索引合并优化),
unique_subquery(in的后面是一个查询主键字段的子查询),
index_subquery(类似unique_subquery,主要是in的后面是查询非唯一索引字段的子查询),
range(单表中的范围查询),
index(对于当前的每一行,都通过查询索引来得到数据),
all(对于当前的每一行,都通过全表扫描来得到数据))
type比较重要。表示链接的类型。链接类型由好到坏的,依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况,至少要达到 range 级别,最好是 ref 级别。否则可能会有性能问题
possible_keys 是指可以应用到该表的索引,如果为NULL则没有。
key 是指用到的索引。
key_len 是索引的长度,在不影响查询精度的情况下,值越小越好 。
ref 是指索引的那一列被使用了。一般会是个常数。
rows MYSQL认为必须检查的用来返回请求数据的行数 。
extra 是指额外的信息。也是比较重要的 。

但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢
(1).Distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
(2).Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了
(3).Range checked for each
Record(index map:#)
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
(4).Using filesort
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
(5).Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
(6).Using temporary
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
(7).Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
如果为 where used 说明使用了where语句。如果 type为 all 或者 index ,一般会出现这样的结果。这样的问题,一般是查询需要改进。
在一般稍大的系统中,基本尽可能的减少join ,子查询等等。mysql就使用最简单的查询,这样效率最高。至于 join 等,可以放在应用层去解决。

3. 使用EXPLAIN分析低效SQL的执行计划。
在查询到效率低的SQL语句后,那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息,包括在Select语句执行过程中表如何连接和连接的顺序。
例如你想计数xxxx年公司的销售额,那么需要操作sales和comapny table,并对money字段进行sum操作。看看怎么使用explain:

Java代码   收藏代码
  1. explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)    

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
SQL 缓存 关系型数据库
MySQL的万字总结(缓存,索引,Explain,事务,redo日志等)
MySQL的万字总结(缓存,索引,Explain,事务,redo日志等)
65 0
|
6月前
|
SQL 存储 关系型数据库
【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】
【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】
73 0
|
2月前
|
存储 关系型数据库 MySQL
MySQL技能完整学习列表6、查询优化——1、EXPLAIN命令的使用——2、索引优化
MySQL技能完整学习列表6、查询优化——1、EXPLAIN命令的使用——2、索引优化
22 0
|
2月前
|
SQL 存储 关系型数据库
MySQL - Explain详解
MySQL - Explain详解
|
3月前
|
SQL 关系型数据库 MySQL
MySQL SQL性能分析 慢查询日志、explain使用
MySQL SQL性能分析 慢查询日志、explain使用
90 0
|
3月前
|
SQL 机器学习/深度学习 关系型数据库
MySQL - Explain深度剖析
MySQL - Explain深度剖析
43 0
|
4月前
|
SQL 关系型数据库 MySQL
mysql explain 详解及sql优化指南
mysql explain 详解及sql优化指南
29 0
|
4月前
|
SQL 存储 关系型数据库
【MySQL】MySQL Explain性能调优详解
【MySQL】MySQL Explain性能调优详解
62 0
【MySQL】MySQL Explain性能调优详解
|
4月前
|
SQL 存储 关系型数据库
谈谈MYSQL中的Explain
谈谈MYSQL中的Explain
|
4月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN
MySQL EXPLAIN
29 0