mysql进阶(二)14-30讲

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql进阶(二)14-30讲

黎明的烬 2018-07-26 23:09:28 浏览997
展开阅读全文

1.mysql索引:

索引是帮助mysql高效获取数据的数据结构;(排好序的快速查找数据结构)

 如果索引字段是char类型,那么在使用索引时,char类型的值必须加引号;如果索引字段时blob等大数据类型,必须指定长度。

 

 

 

 

2.mysql索引结构:

BTree 索引    Hash索引  full-text索引   R-Tree索引

3.哪些情况需要创建索引

4.不适合建立索引的情况

(1)表的记录太少

(2)经常需要增删改的表:提高查询的速度,同时会降低更新表的速度,因为更新表时,mysql不仅保存数据,还要保存索引文件。

(3)数据重复且分布均匀的表字段(比如性别,国籍等字段),因此应该只为最经常查询和最经常排序的数据建立索引。因为某个数据列包含许多的重复内容,为它建立索引效果不大。

5.性能分析:

(1)mysql查询优化分析器

(2)mysql的主要瓶颈:

(3)explain(查看执行计划):可以模拟优化器执行SQL查询语句,从而指导Mysql是如何处理SQL的;从而分析查询语句或者是性能瓶颈。

6.explain(查看执行计划)

(1)执行计划的功能?

(2)执行计划包含的信息

①id:

select查询的序列号,包含一组数据,表示查询中执行select子句或者操作表的顺序。id列为null的就表是这是一个结果集,不需要使用它来进行查询。包含三种情况:

第一种:id相同

第二种:id不同

 

 第三种:id不同相同,同时出现 derived2表示衍生的id为2的表,即先执行s1表,再执行t2;

   

②select_type

主要包含simple , primary ,subquery ,derived , union,union result;查询的类型,主要是用于区普通查询,联合查询,子查询等复杂查询。

③table

显示这一行数据是关于哪一张表的

备注:

partitions:该列显示的为分区表命中的分区情况。非分区表该字段为空(null)。

filtered:使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。

④type

访问类型排列,显示查询使用了何种类型,从最好到最差依次是:system>const>eq_ref>ref>range>index>all

 

例子1:const 因为表中只匹配一行,

例子2:eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。当只能匹配一行的情况就会出现,下面的情况。

例子3.非唯一性索引扫描,返回某个单独值的所有行。

例子4.range只检索给定范围的行,使用一个索引来选择行。

例子5.index,full index scan,index与al区别为index类型只遍历索引树。这通常比ALL要快,因为索引文件比数据文件小(即index是从索引中读取的,而all是从硬盘中读取的)

⑤possible_keys

显示可能应用在这张表中的索引,一个或者多个,查询涉及到的字段上若存在索引,则索引将被列出来,但不一定被查询实际使用。

⑥key

实际用到的索引,如果为null,则没有使用索引;查询中若使用了覆盖索引,则该索引仅出现在key列表中

⑦key_length

表示索引中使用的字节数,可以通过该列的计算查询中使用的索引长度。在不损失精度的情况下,长度越短越好。key_len显示的值为索引字段最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出来的。

⑧ref

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值

⑨rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录行数

⑩extra

包含不适合在其他列显示但十分重要的额外信息。

using filesort:说明mysql会对数据使用一个外部是索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为文件排序。

前者使用了useing filesort,而后者没有使用

using temporay:使用临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by;

第一个没有覆盖索引,使用了using tempoary,第二个覆盖了。,性能上面的sql大于下面的sql

using index:表示相应的select操作中使用了覆盖索引,避免访问表的数据行,效率较高!如果同时出现using where ,表明索引用来执行索引键值的查找。如果没有出现using where,表明索引用来读取数据而非执行查找动作。

 覆盖索引


using where:表明使用了where过滤

using join buffer: 使用连接缓存

impossible where:where 子句的值总是false,不能用来获取任何数据

select tables optimaized away;没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎count(*) 操作,不必等到执行阶段在进行计算,在执行计划生成阶段就完成优化。

distinct :优化distinct,在找到第一个匹配的元组之后即停止找同样值的动作

 

 

 

总结:查看sql的执行计划,理解各个字段的含义

网友评论

登录后评论
0/500
评论
黎明的烬
+ 关注