mysql之索引和explain

索引的定义：
Mysql索引的建立对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度。
打个比方，如果合理的设计且使用索引的Mysql是一辆兰博基尼的话，那么没有设计和使用索引的Mysql就是一个人力三轮车。
拿汉语字典的目录页索引打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在sql查询语句的条件（一般作为where子句的条件）。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

客户端的各个层面

			连接层 - 提供与客户端连接的服务
			
			服务层 - 提供了各种用户使用的接口(select...)
客户端
			引擎层 - 提供了各种存储数据的方式(innodb...)

			存储层 - 存储数据

索引的优点：
1、类似图书馆的书籍目录是一样的，提高数据的检索效率，降低的是数据库的IO成本。
2、可以通过索引列对数据进行排序，降低了数据排序的成本，降低了cpu的消耗。
缺点：
1、索引本质上是一个数据结构，索引本质上也是一张表，保存的是主键和索引字段，并指向实体表的记录，所以索引也需要占用空间。
2、索引只是提高效率的手段之一，如果你的数据库有大数据量的表，就需要花费更多的时间去研究创建最优秀的索引或者优化语句。
3、如果出现了增加修改删除操作的时候，出来更新数据库的表之外，还需要更新索引列。
使用原则：
通过上面说的优点和缺点，我们应该可以知道，并不是每一个字段都设置索引就好，也不是索引越多越好，而是需要自己合理的使用。
1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引。
2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
　使用原则：
3、在同一值少的字段上（列上）不要建立索引，比如在学生表的“性别”字段上只有男，女两个不同的值。相反的，在一个字段上不同值较多可以建立索引。

索引的分类

单值索引：
索引只包含一个字段，一个表可以有很多个单值索引
唯一值索引：
索引字段的值必须是唯一的，大厦允许有空值
复合索引：
一个索引存在多个字段
主键索引：
id不能重复，而且不能为空值
#注 因为个人理解不同，有些人觉得主键索引应该包含在唯一值索引里面，有些人却不那么理解， 全看自己个人理解

创建和查看索引

create 索引类型 索引名 on 表(字段) #创建索引

show index from 表名  #查看索引

索引的添加

方式一
create index name_index on userinfo(name); #单值索引

create unique index phone_un_index on userinfo(phone); #唯一值索引

create index name_phone_index on userinfo(name,phone); #复合索引

方式二
alter table 表名 add 索引类型 索引名(字段)

alter table userinfo add index nameindex(name); #单值索引

alter table userinfo add unique index phoneindex(phone); #唯一值索引

alter table userinfo add index phone_name_index(phone,name); #复合索引

删除索引

drop index 索引名 on 表名

创建索引的情况
1、主键自动创建主键索引
2、频繁作为查询条件的字段应该设置成索引
3、查询中与其他表关联的字段，外键建立索引
4、单值索引/符合索引的选择问题
5、如果查询中需要进行排序，排序的字段建立索引，会提高排序效率
6、查询中统计或者分组的字段，创建索引。

不创建索引的情况
1、频繁更新的字段不适合创建索引（每次的更新不仅仅是更新了原始表还会更新索引，加重io负担）
2、where条件语句中用不到的字段不创建索引
3、如果数据重复的字段，没有必要创建索引
4、表的数据量不大的时候没必要

索引生效的问题
怎么查看索引生效
可以通过explain sql语句 模拟的是优化器执行sql查询语句的，分析查询语句的性能
explain展示的id的具体含义
表示的是select 查询的序列号，包含的是一组数字，表示的是查询中执行的select子句或者表的顺序

id相同 ，执行的顺序由上而下
id不同的时候，如果是子查询，id的序号会进行递增，id越大优先级越高，优先执行，也就是子查询会被先执行

select_type表示要查询的类型

simple 简单的select查询
primary 查询中包含任何负责的子部分，最外层的查询被标记为primary
subquery 在select或者where中包含了子查询的
union 第二个select语句出现在union之后，就会被标记为union
union result 从union表中获取到结果的
derived 在from后面的列表中包含子查询，mysql会执行这些子查询，union包含在from子句的子查询中，外层的select被标记为derived
uncacheable subquery 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

table
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，列如上面的e，d，也可能是第几步执行的结果的简称

type

对表访问的方式，表示mysql在表中找到所需行的方式，又称“访问类型”。
常用的类型有：all、index、range、ref、eq_ref、const、system、null（从左到右，性能越好）

all 将遍历全表找到匹配的行
range 只检索给定范围的行，一般情况下，where语句中出现< > between in 等查询
index 与all相比，遍历的是索引树，速度比all快一点
eq_ref 唯一性索引扫描 用于主键
ref 非唯一性索引扫描
const 通过索引，一次就找到了数据
system 只有一行数据，平常基本不会出现，可以忽略
null: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
一般情况下达到range就可以

possible_keys
指出mysql能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引则显示null）

key
key列显示mysql实际决定使用的键（索引），必然包含在possible_keys中

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

关于explain总结：
explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
explain不考虑各种cache
explain不能显示mysql在执行查询时所作的优化工作
部分统计信息是估算的，不是精确值
explain只能解释select操作，其他操作要重写为seelct后查看执行计划