关于mysql索引的总结

什么是索引：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
如何选取索引字段：

1. 常作为查询条件；
2. 经常需要排序、分组和联查的字段
3. 占用存储空间少的字段更适合选作索引的关键字
4. 更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。
5. 复合索引的建立要符合最左前缀原则。

Mysql索引数据结构：

1. hash索引
   哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
2. b+tree索引
   B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索中，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。
   主要区别：
   （1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询；
   如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
   （2）如果是范围查询检索，这时候哈希索引就毫无用武之地了，B+树索引效率更高；
   因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；而b+tree有序，同层级的节点间有指针相互链接；
   （3）hash索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
   （4）哈希索引也不支持多列联合索引的最左匹配规则；
   Hash 索引不能利用部分索引键查询。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
   （5）B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

MyISAM引擎和InnoDB引擎
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
.MYD 用于存放数据，.MYI 用于存放表索引
下图是MyISAM索引的原理图：

可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复；

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的；
InnoDB的数据文件本身就是索引文件。表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

叶节点包含了完整的数据记录。这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。使用覆盖索引可以避免回表，覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助
例如InnoDB不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

MyisAM索引与InnoDB索引相比较
（1）MyisAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值；
（2）InnoDB支持事务，MyisAM不支持；
myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
Innodb支持事务和行级锁，是innodb的最大特色。
（3）MyISAM引擎不支持外键，InnoDB支持外键
（4）MyisAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持；
（5）MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高
行数查询，InnoDB不保存行数，也就是select的时候，要扫描全表，MyISAM只需读取保存的行数即可，这也是MyISAM查询速度快的一个因素。
（6）myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
复合索引
选择字段顺序：
（1） 选择性最高的，where条件中使用最频繁列放在索引的最前面
创建复合索引（k1,k2,k3）,相当于创建（k1），（k1,k2），（k1,k2,k3）三个索引
（2） 创建复合索引一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效; 窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引
（3） 对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;

1. 改变复合索引顺序不会改变explain的执行结果
2. 复合索引顺序有序的情况，范围查询右侧索引失效
3. 最左侧的索引失效，则后面的索引都失效
4. 中间索引没有使用，后面的索引失效
5. 全值匹配，最左前缀，索引列少计算，like %写最右，覆盖索引代替查*
6. 不等（<>,!=）、空值、还有or索引失效，隐式转换

查看命中索引
MySql中是通过 Explain 命令来分析低效SQL的执行计划。命令的使用很简单.
explain select * from adminlog
执行结果:


type 详细解读
type＝ALL　全表扫描
type＝index 索引全扫描，遍历整个索引来查询匹配的行
type=range 索引范围扫描，常见于　<,<=,>,>=,between,in等操作符。例如
explain select * from adminlog where id>0 ,
explain select * from adminlog where id>0 and id<=100
explain select * from adminlog where id in (1,2)
type=ref　使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
type=eq_ref 类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件
type=const/system 单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。
type=NULL　不用访问表或者索引，直接就能够得到结果 例如 EXPLAIN SELECT 1 from history_record;,类型type 还有其他值
ref_or_null : 与ref 类似，区别在于条件中包含对NULL的查询
index_merge : 索引合并优化
unique_subquery : in的后面是一个主键字段的子查询
ndex_subquery : 与unique_subquery 类似,区别在于in的后面是查询非唯一索引字段的子查询
Extra 详细解读
Not exists 没有找到合适的索引
using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。就是建议取索引列。这样就可以不要通过索引去实际表中找数据了。直接返回索引列的数据。一次查询。否则就是索引表查一次，实际表中查一次。
using temporary 为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
无效索引
数据变化不大的列。如某类型，是否有效，项目ID等列的索引都是无效的。这些无效索引还是影响Insert 、Update、Delete 语句的性能。因为这些语包的执行都要对索引表进行更新。又因为这些表的值变化不大，数据库很难为他们合理分配索引。所以影响语句的性能。

本文是对mysql索引的一个总结，如有问题请指正，因查询大量资料，如有侵权请联系笔者删除