SQL索引详解

一、什么是索引

    索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的特殊数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到需要的内容

二、索引类型

1. 唯一索引：表上一个字段或者多个字段的组合建立的索引，这些字段组合起来能够确定唯一，允许存在空值（只允许存在一条空值）
2. 非唯一索引：表上一个字段或者多个字段的组合建立的索引，可以重复，不需要唯一
3. 主键索引：（主索引）根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
4. 聚合索引：表中记录的物理顺序与键值的索引顺序相同
5. 非聚合索引：表中记录的物理顺序与键值的索引顺序无关
6. 全文索引：在某个字段设置全文索引后，根据特定语法查找满足条件的字段;
7. 普通索引：用表中的普通列构建的索引，没有任何限制
8. 组合索引：用多个列组合 构建的索引，但是在使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要
9. Hash索引（Memory存储引擎）是通过索引列的值计算出hashCode,之后在相应的物理位置存取索引列的值，由于hashCode的唯一性，因此Hash索引不能进行范围查找或者是顺序查找
    

三、聚集索引和非聚集索引

例子

• 一本字典的目录有字母目录和部首排序，汉字的顺序是按字母排序的，所以字母排序是物理的，为聚焦索引，而且汉字中排序只能是一种，所以聚焦索引也只能有一个；
• 部首排序可以按部首排序，这也是索引，可以跟部首加上笔画来查找字体，但是字典是按字母排序的，所以部首排序为非聚焦索引

区别：

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引是InnoDB，非聚集索引是MyIsam
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
• 非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

四、索引的优缺点

优点

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，减少IO次数，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时

缺点

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

五、索引的使用建议

• 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
• 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
• 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好
• 在where后使用or，导致索引失效（尽量少用or）
• 使用like ，like查询是以%开头，以%结尾不会失效
• 不符合最左原则
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 5使用in导致索引失效
• 使用mysql内部函数导致索引失效,可能会导致索引失效。
• 如果MySQL估计使用索引比全表扫描更慢，则不使用索引