MySQL索引

索引（index）：帮助MySQL高校获取数据的数据结构，可以理解为索引就是排好序的快速查找的数据结构。
记录超过三百万数量级的查询性能就开始下降，也需要开始建立索引优化。

一、以下情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• where条件里用不到的字段不创建索引
• 在高并发下倾向创建组合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

二、以下情况不需要创建索引

• • 频繁更新的字段不适合建立索引，因为每次更新不单单是要更新记录还要更新索引
• 经常增删改的表不适合建立索引
• 表记录太少
• 数据列包含许多重复的内容，为它建立索引没有太大的实际效果。

注意：如果一个表中有2000条记录，表索引列有1980个不同的值，name这个索引的选择性就是1980/2000=0.99，也就是说一个索引的选择性越接近1，这个索引的效率就越高。

三、性能分析

explain（查询执行计划）

执行计划包含的信息
id	select查询的序列号，表示查询中select子句或者操作表的顺序；如果id相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行。
select_type	包括SIMPLE,PRIMARY,SUBQUERY,DERIVED,UNION,UNION RESULT六大类。
table	显示这一行数据是关于哪张表的
type	访问类型排序从好到差依次为：system>const>eq_ref>ref>range>index>all
possible_keys
key
key_len
ref
rows
Extra

补充

• SIMPLE：简单的查询，查询中不包含子查询或者UNION
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记
• SUBQUERY：在select或where列表中包含子查询
• DERIVED：在from列表中包含的子查询被标记为- DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
• UNION：若第二个select出现在UNION之后，则会被标记为UNION；若UNION包含在from子查询中，外层select将被标记
• UNION RESULT：从UNION表获取结果的select

四、导致索引失效的原因

• 全值匹配：精度高
• 最佳左前缀法则：如果索引多个列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
  例如建立了索引名为index_nameAgePosition的复合索引，如果查询没有用到name索引，其他索引都会失效，另外，如果查询用到name和position索引，那么position索引会失效。
• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
  例如：还是复合索引index_nameAgePosition，在以下MySQL语句中：

EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='manager';

position索引会失效

• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
• MySQL在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
• is null , is not null 也无法使用索引
• like 以通配符（’%abc…’）开头，MySQL索引失效会变成全表扫描的操作
  如果一定要需要上述要求，则需要建立覆盖索引查询才能避免索引失效的问题。
• 字符串不加单引号，索引失效
• 少用or，用它来链接的时候索引会失效