小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

概念

索引是一种可以加快数据查询速度的数据结构，索引最常见的就是使用B+树索引或者哈希索引，主要从MYSQL数据库为例

创建索引语法

创建单个索引的语法：CREATE INDEX 索引名 on 表名（字段名）

索引分类

聚簇索引

聚簇索引既存储了主键值，也存储了行数据聚簇索引的构建通常按照以下原则：

建表时，指定了主键列，MySQL InnoDB会将主键作为聚簇索引列
没有指定主键，自动选择唯一键的列作为聚簇索引
以上都没有，生成隐藏的聚簇索引

辅助索引

辅助索引，InnoDB采用的方式是在叶子节点中保存主键值，通过主键值来回表查询到一条完整记录，因此按辅助索引检索会出现进行二次查询，所以效率没有聚簇索引高，如果查询时要的查询结果就是辅助索引，那么就不回回表查询。非聚簇索引的索引就是辅助索引，用来优化非聚簇索引列之外的查询条件的优化辅助索引可以分为单列索引和联合索引联合索引会存在最终匹配原则

索引最左匹配原则

如果创建了一个联合索引，顺序是name,age,phone,那么在进行sql语句查询时，如若只使用了name,age,MYSQL会利用name,age索引来查找元素，若是name,phone,MYSQL只会根据name索引来查，若根据age，phone,MYSQL是不会走索引的，没有符合最左匹配原则，还有一种情况，若sql查询语句三个字段顺序是age,phone,name,这种也会走联合索引，因为MYSQL有查询优化器，会进行匹配最合适的查询选择。

前缀索引

前缀索引是针对于我们所选择的索引列值长度过长，会导致索引树高度增高所以可以选择大字段的前面部分字符作为索引生成条件

B+tree 索引树高度影响因素

索引字段较长：前缀索引
数据行过多：分区表，归档表，分布式架构
数据类型：选择合适的数据类型

各个存储引擎索引的特点

在MYSQL中，MYISAM和InnoDB存储引擎的存储引擎默认都是B+树的数据结构，其中MYISAM不支持哈希索引，而InnoDB会自动根据表的情况加哈希索引，人为无法加哈希索引

索引底层原理

B树

说B+树，就要先谈下B树，B树一种平衡的多叉查找树也就是说最多可以开m个叉（M>=2）,称之为m阶b树，如图所示

每个节点至多可以拥有m棵子树
根节点至少有2个节点
非根非叶节点至少有m/2个子树
从根到叶子节点的每一条路径都有相同的长度。
所有节点存储一个关键字

B+树

B+树相当于B树的增强版

非叶子节点关键字不存储数据，只用来索引，所有数据都保存在叶子节点。

在MySQL中，B+树增加了顺序访问指针

如图所示，在B+tree的每个叶子节点增加了一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree.这样就提高了区间访问性能，例如如果查询18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点

B树和B+树区别

B+树非叶子节点不保存数据，所以磁盘页能保存更多节点元素，而B树非叶子节点会存储数据 B+树查询必须查找到叶子节点，B树只要匹配到即可，所以查找的数据可能还没遍历到叶子节点就可能找到。对于范围查找，B+树只需遍历叶子节点链表即可，B树需要重复中序遍历

为什么要使用B+树作为索引

红黑树等数据结构都可以用来实现索引，但MySQL采用B+Tree作为索引结构。一般来说，索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引查找过程就会产生磁盘IO消耗，相对于内存存取，I/0消耗要高的多，所以索引的组织结构要尽量减少查找过程中磁盘IO的存取次数。对于B+树来说，相对于红黑树，平衡二叉树，整颗树的高度很低，一个结点可以存储多个元素，所以可以提高磁盘IO效率，还可以提高范围查找效率

innoDB索引实现

对于聚簇索引，在B+树中结点保存的key是数据表的主键，叶子节点保存数据的完整记录

对于辅助索引，辅助索引的data域存储的是响应记录主键的值，所以辅助索引查询数据都是引用主键，通过主键值在聚簇索引中查找具体数据

MYISAM索引实现

MYISAM是使用B+树作为索引结构，叶节点存放的是数据记录的地址，而不是值，因为MYISAM的索引和数据四分别在两个独自的文件中。

在MYISAM中，主索引和辅助索引在结构上没有任何区别，只是聚簇索引要求key是惟一的，而辅助索引key可以重复。

索引优化

索引的优缺点

优点

通过创建唯一索引可以保证数据库表中每一行数据的唯一性
可以加快数据的查询速度
加快表与表之间的连接
在使用分组和排序子句进行检索时，可以减少查询中分组和排序时间

缺点

创建索引和维护索引会消耗时间，随着数据量的增加而增加
索引会占用物理内存
当对表数据进行新增、删除和修改时，索引也要动态维护，就会降低数据维护速度

建索引的原则

频繁作为查询条件的字段应该建立索引
查询中与其他表关联的字段，外键关系建立索引
单索引或者组合索引，这两者组合索引性价比更高
查询中的排序字段，排序字段通过索引去访问效率更高
查询中经常统计或分组的字段应该建立索引
表记录太少不适合建立索引
经常增删改的字段不适合建立索引
存在大量重复的字段数据不适合建立索引

索引数目多可能产生的问题

每个索引都需要占用磁盘IO空间，索引越多，需要的磁盘空间越大
修改表的时候，对索引的重构和更新很麻烦。越多的索引，会造成更新标浪费更多的时间
优化器负担会更重，可能会影响到优化器的选择。

索引优化具体说明

如果sql中使用了函数，加的索引无效
不要在索引列进行计算，进行计算会导致索引失效
查询的结果集超过了总数行数的25%，优化器会评估没有必要走索引，就不会去走索引。
表内容变化比较频繁，统计信息不准确，有可能会出现索引失效
！= <> is null,is not null 这几种情况是不走索引的
如果sql语句使用LIKE,但索引只能用在‘33%”，但不能用在‘%33%’‘’或‘’%33‘’
索引列如果有范围查询，其后面的字段不会走索引，所以尽量把范围查询的索引列移到最后
使用like进行模糊匹配的时候，其前面不能加% 例如 %123%,如果加了就不会走索引,%号加在后面还是会正常走索引。
减少使用or,使用union all 或者union来代替，查询的cpu消耗：or>in>union,用or前的条件中列有索引，而后面列没有索引，那么涉及到的索引都不会走索引。例如： SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96; 如果customer_id有索引，amount没有索引，那么就不会走索引，因为后面查询肯定要走全表扫描，就没必要建索引。
范围条件可以名字索引，例如>=,between等
建立索引的列,最好不要存在为null,如果列存在Null,可能会得到不符合预期的结果。

覆盖索引

对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，覆盖索引就是要建立的索引是要查询的数据，这就避免了回表查询，减少了很多随机IO操作，提高了查询效率

数据为空

当建立索引的字段数据有空值时索引是无效的

MySQL索引大揭秘

概念