索引及explain 详解

索引检索原理：

建立索引相当于对某字段构建了一个b+树，从而使遍历速度大大加快

 

索引的分类：

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

 

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。 

2. 一个表中可以有多个唯一性索引，但只能有一个主键。
 
3. 主键列不允许空值，而唯一性索引列允许空值。 

4. 索引可以提高查询的速度。 

 

注：主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中

 

使用索引须知：

 

1.当一个字段拥有索引时，SELECT 该字段时会自动使用索引；

 

2.当多个字段拥有多个不同的索引时，SELECT 时，只会使用一个索引，DBMS会自动选出key_len最短的索引；

 

3.当索引包含了查询语句所有要查询的所有数据时，这时将索引称之为覆盖索引，

当索引为覆盖索引时且没有WHERE条件时将无法通过索引检索数据,possible_keys会为空，key为所建索引，extra为Using Index；

 

explain 详解

id：

　　当id相同，table栏的table执行顺序为由上到下
　　当id不同，id越大，table优先级越高，越先被执行

 

select_type

　　SIMPLE　　简单的select查询，查询中没有子查询或union

　　PRIMARY　　查询中拥有子查询时，最外层查询被称为主查询，即RRIMARY

　　SUBQUERY　　就是除主查询外的子查询

　　DERIVED　　执行完子查询时，mysql会将产生一个临时表中，在主查询时会用到，产生临时表的查询叫做derived(通常在有两个及以上的select语句中看到）

　　UNION　　组合查询，若第二个出现在union之后，称为union,若出现在子查询中，最外层被标记为derived

　　UNION RESULT　　从union表获取结果的select

 

type：

　　system : 表只有一行

　　eq_ref : 每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种， 特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

　　const : 表最多只有一行数据匹配，一般出现在主键和唯一索引

　　ref：非唯一性索引等值查询时至少有一条记录相匹配

　　range：模糊查询时

　　index：全索引扫描时

　　all：全表扫描时

 

possible_keys：

　　查询时可能会用到的索引，若检索字段中出现索引，将其列出，但不一定被查询实际使用

 

key：

　　查询中实际运用的索引

　　若索引出现覆盖索引，则该索引只出现在key中

 

key_len：

　　表示索引的最大可能字节长度，并非实际长度，在不损失精确度的情况下，越小越好

　　精确度与key_len相矛盾

 

ref：

　　表示哪些字段或常量在查询中被使用了

 

extra：

　　Using filesort：也叫文件排序，在order by不符合索引顺序时，会触发文件排序，这时mysql不会按照表内的索引顺序进行读取，会另起炉灶进行排序，通常这种情况必须优化

　　Using temporary：在group by时不符合索引顺序时触发，会产生一个临时表，会严重降低系统性能

　　Using index：说明该sql语句使用了覆盖索引，不需要回表查数据

 　　Using where：说明索引被用来执行索引键值的查找操作,需要回表查找数据

　　Using index && Using where : 使用了覆盖索引和where过滤

　　Using index condition : 使用了索引还需要回表查数据