sql优化:
如何分析sql:
1)了解sql执行流程
2)sql语句的错误使用
3)sql优化的思路
4)explain分析
5)profiling分析
所谓的sql优化指的是什么:
我们都知道mysql的优化说到底就是减少IO的操作,那么sql的优化呢?sql的优化其实就是指mysql中的sql语句基于优化器的规则来优化语句。
比如 select * from user和select id,name from user(具体下面在解释)
我们常说的优化sql语句主要是优化查询,但是修改,新增,删除当中有查询的时候也可能需要进行一定的优化
sql的执行流程
其中分析也叫解析器,我们进行的优化主要是在优化器这边、
解析器:
1)词法分析:把sql语句进行拆分,分解成小token(token的本质就是一个一个的字符串,会分解成一个一个的单位,连逗号都会分解出来)
2)语法分析:解析token,并将它们组合成解析树
**优化器:**明确查询的目标计划
1):确定使用的表(在多对多的情况下,会选择合适的表作为查询的依赖)
2):对于数据表查询的字段;确定好查询表的顺序(jion)
3):重写where从句,去掉无意义的查询操作,尽可能的更改限制条件,减少查询范围
4)判断索引的使用:
a:是否覆盖所有的字段
b:是否在where上使用,是否在group,order上使用,
5):(join)连接方式是内还是外
6):(join)视图简化子查询,——》合并视图
sql优化的思路:
1):根据目标找问题,在进行相对的优化。首先从业务角度来了解用户的真正需求,尝试用其他方式来满足需求,这些是需要在sql优化之前考虑的。
2):查询返回的数据如果太大了,根据用户的需求情况,去分析用户对项目的最低承受度(优化的最终目标)
原则:
1、明确需要优化的功能
2、定位需要优化目标的性能瓶颈在哪里
3、明确优化的目标
警告:
优化并不意味这整个项目能达到预期,只是牺牲局部地区的性能来换取最大的效果
explain:执行计划
在日常查询中我们会使用慢查询来查询运行较慢的语句,但是并不是找到了这些语句就完事了,这是想要优化好sql语句,查看执行计划是必不可少的。查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看,
可以看出来用了15秒多,
再看看加了explain
只用了1.5秒,快很多,
概要描述:
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
id:我的理解就是查询的顺序
select_type:
(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
(5) UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
**table:**表名,这个没什么说的
type:
常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
index: Full Index Scan,index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行,使用一个索引来选择行
ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
**possible_keys:**哪些字段使用了索引(没有索引表示为null)
**key:**mysql实际决定使用的索引
**key_len:**索引的字段最大的长度
**ref:**列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows:
估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数(可用于大致的统计上,效率更好些)
**Extra:**该列包含MySQL解决查询的详细信息,有以下几种情况:
1、Using filesort:说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
2、Using temporary:使用了临时表保存中间结果,mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3、Using index:表示相应的select操作用使用覆盖索引,避免访问了表的数据行。如果同时出现using where,表名索引被用来执行索引键值的查找;如果没有同时出现using where,表名索引用来读取数据而非执行查询动作。
4、Using where :表明使用where过滤
5、using join buffer:使用了连接缓存
6、impossible where:where子句的值总是false,不能用来获取任何元组
7、select tables optimized away:在没有group by子句的情况下,基于索引优化Min、max操作或者对于MyISAM存储引擎优化count(*),不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
8、distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。