Mysql索引总结

索引

概念：帮助Mysql高效获取数据的数据结构

索引结构

常见的四种索引

					MyISAM，InnoDB，Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本以后支持	支持	不支持

BTree结构

• 树中每个节点最多包含m个孩子
• 除了树根节点与叶子结点之外每个节点至少有[ceil(m/2)]个孩子
• 若根结点不是叶子结点，至少有两个孩子
• 所有叶子结点都在同一层
• 每个非叶子结点有n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1

B+Tree结构
B+Tree为BTree的变种，两者区别为

• n叉B+树最多含有n个key，而BTree最多含有n-1个key
• B+Tree的叶子结点保存所有的key信息，依key大小顺序排列
• 所有的非叶子节点都可以看做是key的索引部分
  
• 由于B+Tree只有叶子结点保存key信息，查询任何key都要从root走到叶子，所以B+Tree查询效率更加稳定

Mysql中的B+Tree
mysql索引数据结构对经典的B+Tree进行了优化，在原来的B+Tree的基础上，增加了一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间的访问性能

索引分类

• 单值索引：一个索引只包含单个列。一个表里面可以有多个单值索引
• 唯一索引：索引列的值必须唯一，但只能有一个空值
• 复合索引：一个索引包含两个或两个以上的列

索引相关操作

--创建索引
create index 索引名	on 表(索引列)
alter table 表名 add index 索引名(索引列)
--删除索引
drop index 索引名 on 表
--查看索引
show index from 表

索引的设计原则

• 对查询评率较高且数据量比较大的表建立索引
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多那么应当挑选最常用过滤效果最好的列的组合
• 使用唯一索引，区分度越高，使用索引的效率就越高
• 索引可以有效的提升查询数据的效率，但索引也不是一直能提高效率，当对表进行更新，删除等操作时，需要人工的对索引进行维护
• 使用短索引，索引创建之后是使用硬盘来存储的因此提升索引访问的I/O效率也可以提升总体访问效率
• 最佳左前缀原则，如果索引了多列，就要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

避免索引失效

• 全值匹配，对索引中所有的列都指定具体值
• 最佳左前缀法则：如果索引了多列，就要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
• 存储引擎不能使用索引中范围条件右边的列
• 不在索引列上做任何操作(计算，函数，类型转换)，会导致索引失效而转向全表扫描
• 字符串不加单引号索引失效
• is null ,is not null有时候也无法使用索引，如果null值比较多is not null会使用索引，null值比较少时 is null会使用索引
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
• mysql在使用不等于的时候无法使用索引会导致全表扫描
• like以通配符开头(’%abc’)mysql索引失效会导致全表扫描操作，可以通过覆盖索引解决
• 少用or，用它连接时会导致索引失效
• in会使用索引，not in不会