Explain详解与最佳实践

Explain使用与详解

explain select * from (select * from user_info where id = 33) where id = 33;

返回字段解析:
id :sql查询的数据,下面图片中的sql分为三次查询,id是他们查询的顺序
select_type:查询类型  PRIMARY:复杂查询 
					 DERIVED:衍生查询,查询的表是film
					 SUBQUERY:子查询
					 他们查询的顺序为 先进行衍生查询select * from film where id = 1
					 				在进行 子查询 select 1 from actor where  id  = 1
					 				最后进行查询所有 
					 类型还有simple:简单查询
					
					 
table: 查询的表,id为1的table查询的是deriver3指向的表是id为3的衍生表,	deriver3中的3就是deriver衍生查询的id	
partitions:分区表,一般不用
type: system>const>eq_ref>ref> range>index>ALL   
		特殊情况会有null的参数,这是因为在查询的时候没有对索引进行查询,直接获取了对应的数据
				比如:select MIN(ID) from table 
		查询效率从System到ALL,前面的查询效率最高,后面的最低
		system:相当于const的变种,只有在数据只有一条的时候才可能是system
		const:常亮查询,查询的时候使用的字段为唯一索引或者主键索引的时候与常数比较的时候,所以表最多只有一次匹配,速度较快,
        eq_ref:primary key或者unique key索引的所有部分被引用,最多会返回一条数据记录的查询
        ref:没有用到唯一键或者主键查询,使用的是普通索引进行查询或者使用了唯一索引的部分前缀,索引要与某个值相比,找到多个符合条件			   的行
        range:返回查找,进行查询范围的数据,会出现当前类型,当前查询也是进行的索引查询.
        index:扫描全索引就能拿到结构,一般是扫描某个二级索引,这种扫描不是从根节点扫描,而是直接遍历整个叶子结点,这种查询一般为覆			  盖索引,二级索引一般比较小,这种通常比较快一点.
        ALL:全表扫描,扫描你的聚簇索引的所有叶子结点.
possible_keys:在sql运行时可能会使用到的索引
key:sql运行时使用的索引
key_len:索引的长度,在联合索引的时候更能体现的出来,如果是两个int类型的联合索引,只使用到了前面的索引那么就是4,连个索引都使用到了		    就是8,其他的字节长度根据情况来确定.下面表中的就是使用到了一个整形的索引长度4
ref:关联的类型,如果是int类型的是const,也可能是关联类型的名称
rows:预估扫描的行
filtered:搜索出来的记录行数的百分比。
Extra:展示的额外信息,常见的信息有以下几个
		Using index:覆盖索引mysql的explain执行计划中key使用到了索引,select后面的字段通过都可以从当前的索引树中获取到全部数据,可以说当前查询使用了覆盖索引,extra里面一般都有using index,覆盖索引一般都是指的辅助索引,整个查询通过辅助索引就可以获取到所有的数据,不需要在通过辅助索引获取到主键,在通过主键获取数据
		Using Where: 使用where语句来处理结果,并且查询的列没有被索引覆盖,就是普通的查询,字段还没有索引
		Using index condition: 查询的列不完全被索引覆盖,where条件中的列是一个前导列的范围.大概意思是,查询的这个列是联合索引中的其中一个索引的列,而且还是范围查找. select * from table where table_id > 1  这个时候table_id是联合索引中的一个索引,在这条语句执行的时候之使用了这一个列的索引,Extra中就会Using index condition
		Using temporary:mysql需要创建一个临时表来进行处理查询结果,这种情况一般要进行优化,
			优化:distinct去重
				select  distinct name from user 
				原理:将当前表数据进行全部查询出来然后存储在临时表中,然后进行去重处理
				优化方式:将name字段进行添加索引,这样在进行查询的时候,可以在通过索引直接去去重,不用在进行创建临时表,而且索引还				   是排好序的
		Using filesort:文件排序,在进行排序的语句中经常出现,需要进行优化,使用索引字段进行排序,就可以直接获取,因为索引字段是排				好序的索引树
		Select tables optimized away:难以置信的,最快的查询,select min(ID) from user ,不需要走索引能直接获取到数据.


index和ALL都是扫描所有的索引,但是为什么index的效率要比all高一点呢?
	因为index扫描的是非主键索引并且扫描的时候能够获取到所有的数据,非主键索引的体积比较小
	聚簇索引的全表扫描,他的叶子节点保存的是所有的数据,占用的体积比较大.所以比较慢
为什么index全表扫描不会进行回表呢?
	因为当前非主键索引上的数据能够保证是所有查询的数据,所以不需要进行回表操作,如果叶子节点上的数据不够,那么就会进行回表操作,

非主键索引上存储的是聚簇索引的下标,回表的时候会通过下标快速的定位到指定的数据位置,然后进行数据回显.

Mysql索引优化实践

• 全值匹配
• 是指联合索引,例如 (id,name,age) Using B+Tree的联合索引在进行查找的时候,使用到了全部联合索引的查询是效率最高的
  • select * from table where id = 1
  • select * from table where id = 1 and name = “aaa”
  • select * from table where id = 1 and name = “aaa” and age = 33 在联合索引中索引字段的顺序不对也可以,mysql会在底层做优化
  • 上面三个sql中的第三个是效率最好的,因为在进行匹配的时候可以更加精准的匹配到对应的数据,减少查询的数据
• 最左前缀法则
  • 如果索引的多列,需要遵守最左前缀法则,指的是查询索引的最左列开始并且不跳过索引中的列
• 不要再索引列上进行任何操作(函数,计算,自动或者手动的类型转换),会导致索引失效转而进行全表扫描
  • ,因为他在操作之后的数据可能不会存在在索引上,所以会失效
• 尽量使用覆盖索引,少用select*
• mysql中使用 != 或者<> 的时候会导致不走索引,从而走全表扫描
• is null,is not null一般情况下也无法使用索引
• like要遵守最左前缀原则,如果是 like(%aaa) 是不走索引的,因为忽略了前面的字符,在索引树里面就是无序的,无法走索引,如果是like(aaa%)是可以走索引的,因为可以通过aaa的排序获取到对应排序好的索引
  • 优化方式可以使用覆盖索引去查询,查询字段为指定的索引字段,查询的数据要少一点

[外链图片转存中…(img-IjaWjrBc-1705162882018)]
​

• 字符串不加引号会导致索引失效

  • 涉及到类型转换,导致索引失效

• 少用in或者or,用它查询的时候会根据内部优化器的检索比例,表大小等因素进行评估来确定是否使用索引

• 范围查询优化

  • 数据量较多的时候根据mysql内部优化器的计算可能会觉得走索引还没全表扫描来的快的时候就不会走索引
  • select * from table where id >= 1 and id <= 2000,这个时候可能就不会走索引,有可能是因为表的数据较小或者查询到的数据量比较大,内部优化器计算之后不如全表扫描来的快
  • 优化方式可以将范围缩小 id >= 1 and id <= 1000 第二个 分为 id >= 1001 and id <= 2000 分为两个查询
  • 走覆盖索引 select id from table where id >= 1 and id <= 2000

• 尽量使用覆盖索引,少用select*

• mysql中使用 != 或者<> 的时候会导致不走索引,从而走全表扫描

• is null,is not null一般情况下也无法使用索引

• like要遵守最左前缀原则,如果是 like(%aaa) 是不走索引的,因为忽略了前面的字符,在索引树里面就是无序的,无法走索引,如果是like(aaa%)是可以走索引的,因为可以通过aaa的排序获取到对应排序好的索引

  • 优化方式可以使用覆盖索引去查询,查询字段为指定的索引字段,查询的数据要少一点
    

• 字符串不加引号会导致索引失效

  • 涉及到类型转换,导致索引失效

• 少用in或者or,用它查询的时候会根据内部优化器的检索比例,表大小等因素进行评估来确定是否使用索引

• 范围查询优化

  • 数据量较多的时候根据mysql内部优化器的计算可能会觉得走索引还没全表扫描来的快的时候就不会走索引
  • select * from table where id >= 1 and id <= 2000,这个时候可能就不会走索引,有可能是因为表的数据较小或者查询到的数据量比较大,内部优化器计算之后不如全表扫描来的快
  • 优化方式可以将范围缩小 id >= 1 and id <= 1000 第二个 分为 id >= 1001 and id <= 2000 分为两个查询
  • 走覆盖索引 select id from table where id >= 1 and id <= 2000